|
What to do
|
Evidence for
|
Details
|
Help
|
|
&
|
|
FIRST
you must read through the whole of this spreadsheet assignment BEFORE you
do anything else.
Look carefully at the data shown on the pricelist
page. This shows a small number of products that can be bought
from a supermarket.
|
|
|
!
or
:
|
P1
|
Write a description of how a family could use a spreadsheet model to
help them work out the cost of their shopping each week.
Explain
·
what data would be needed and
·
how this data could be used to perform
calculations that would help the family to work out the total cost of
shopping
Make sure your name is on the
paper then store the written notes safely in your work folder.
NOTE: These notes can be word-processed instead of
handwritten if you prefer. <
|
|
|
!
|
P1
|
Produce an initial design for your spreadsheet model.
Write this on the blank grid provided.
You can print your own blank copy by clicking HERE
You must show the column/row
data labels and any formulae that you intend to use.
Write your name on the sheet and
store it safely in your work folder.
|
(
|
|
M
|
M4
|
Finish your initial design and description by
Friday September 16 2005
|
|
|
$
|
P6, M3
|
At
all times you must check that the data you are entering into your
spreadsheet model is accurate, and that the formulae and functions you use
work as you intend.
|
|
|
:
|
P3, P4, M2
|
Create a spreadsheet model and enter all the data shown
in the pricelist.
Make sure your spreadsheet has a suitable title and headings for
each column (and row if necessary). <
|
(
|
|
$
|
P3
|
Check - Make sure the columns and rows are the right
size for your data. You MUST
include the “Cat” (short for category) column and the “cat” data
as this will be important later.
|
(
|
|
6
|
P5
|
Print a copy of your spreadsheet showing only this
data.
|
|
|
!
|
P5, M1, M2
|
Label this printout clearly and make sure you have used
headers and footers in your spreadsheet printout that clearly show that it
is YOUR WORK and what the work is about.
Store the printout safely in your work folder.
|
|
|
:
|
P6
|
Copy the whole worksheet and use the copy for the next
task. Do this throughout this
spreadsheet activity so that you will always have a record of your work at
each stage. Your spreadsheet
“workbook” can contain as many “worksheets” as you like.
To see how this is done click
here.
|
(
|
|
&
|
|
Look at the ‘Shopping
List’ page. It shows details of the quantity of each item to be
purchased by one family—the “Wright Family”. They usually spend the
same amount each week for these items.
|
|
|
7
|
P3, P4
|
Enter the shopping list
data into your spreadsheet. You
will see that they buy more than one of some items and for some other
items the quantity will be 0 as they do not buy these at all. <
|
|
|
:
|
P3, P4
|
Use formulae and functions to carry out calculations to work out the total
cost of the shopping list items for the customer. <
|
(
|
|
:
|
P4
|
Sort the data in your spreadsheet using the “Cat”
column. <
|
(
|
|
6
|
P5, M1
|
Print out the spreadsheet before and after sorting.
Label this printout clearly and make sure you have used
headers and footers in your spreadsheet printout that clearly show that it
is YOUR WORK and what the work is about.
|
|
|
$
|
P3, M1
|
Check that your formulae work correctly – you should
do this by checking them using a calculator.
|
(
|
|
M
|
M4
|
Target is to reach this point by Friday 23 September
2005
|
|
|
&
|
|
Look at this information:
During a one week “SALE” in
March the supermarket discounts everything by 15%. The Wright family want to use the spreadsheet model to help
them predict how much extra money they will have available to spend on
other things.
You
may find this presentation
helpful when doing this task
|
|
|
:
|
M1, D3
|
Enter the discount as 15% in a cell near or at the top
of the spreadsheet. Create two new columns (with suitable headings)
·
to show the new price charged for each of the
items when they are
discounted (use an absolute cell reference in your formulae in this
new column so that you can easily change the rate of discount).
·
to work out the new total
amount paid (use a formula - quantity multiplied by new price)
<
|
(
|
|
:
|
P3
|
Use the model to display how much money the Wright
family saves during one shopping trip in March when the SALE is taking
place. <
|
(
|
|
6
|
P3, M1, D3
|
Print a copy of your spreadsheet showing the formulae
and functions. Also
print a copy showing the figures.
You
may find this presentation
helpful when doing this task |
(
|
|
$
|
P4
|
Change the rate of discount from 15% to 10%.
Look at any changes that happened automatically when
you made this change. <
|
(
|
|
6
|
P4, P5, M1
|
Print a copy of the spreadsheet – clearly label
the cell that you have changed – and the cells that have changed
automatically as a result.
|
|
|
$
|
P4
|
Change the quantity of “Baked beans” to 4 instead of
8.
Look at any changes that happened automatically when
you made this change. <
|
(
|
|
6
|
P4, P5, M1
|
Print a copy of the spreadsheet – clearly label
the cell that you have changed – and the cells that have changed
automatically as a result.
|
|
|
M
|
M4
|
Target is to reach this point by Friday 30 September
2005
|
|
|
:
|
M2
|
Improve the appearance of your spreadsheet by making suitable
changes to fonts, shading, borders etc.
<
|
(
|
|
6
|
P5, M1, M2
|
Print a copy of your “improved” version clearly
labelling the improvements you have made.
|
|
|
6
|
|
Copy your entire spreadsheet model and paste it into a
blank worksheet in your workbook. <
|
(
|
|
:
|
P3, M1, D1
|
Use the sub-totals feature of the software to work
out the sub-totals spent on each category (cat) of shopping. <
|
(
|
|
:
|
P4, M2
|
Create a chart showing the proportion spent on each
category.
Use labels to make sure each
category is shown in full (not just “Cat A” etc) .
Make sure your chart has a
suitable title. <
|
(
|
|
6
|
P5, M1, M2
|
Print and label the chart.
|
|
|
:
|
P4, M2
|
Create a chart showing the actual amounts spent by the
Wright family on each category of their weekly supermarket shopping.
Give the chart a title and make
sure each axis is suitably labelled. <
|
(
|
|
6
|
P5, M1, M2
|
Print and label the chart.
|
|
|
M
|
M4
|
Target is to reach this point by Friday 7 October 2005
|
|
|
:
|
P3, P4
|
Add a new column to your spreadsheet model with a
suitable heading.
Use the lF...THEN function to display a message to
warn you if the purchases of any single item will cost more that £2.00. <
|
(
|
|
!
|
D1
|
Write or word process an evaluation of your work and
suggest how the spreadsheet model could be improved or extended to help
the Wright family with other spending. Also
describe any problems that you experienced when doing this
spreadsheet assignment. <
|
(
|
|
M
|
M4
|
Target is to reach this point by Wednesday 19 October
2005
Hand in the work for assessment – you will receive
feedback on how it can be improved further (where necessary)
|
|
|
:
|
All
|
Complete all required tasks and improve your work.
Make sure you show it to your teacher regularly so it can be
reassessed.
|
|
|
M
|
M4
|
Finish and hand in this spreadsheet activity by
Friday 4 November 2005
|
|
