Task
What to do
Evidence for
Details
Help
1
&
AO1 (PMD)
It is essential that you read the whole assignment first before you begin any work.
2
&
AO1 (PMD)
Read this scenario:
John and Sue Wright have three children. John and Sue both work full-time. Their youngest child, Jordan (aged 3) is looked after by a childminder during the day. Their other two children, Tom and Jamie, go to primary school.
They often order their food shopping online and have it delivered. They find that this saves them some time and gives them more quality time to spend with their children.
John wants to use a spreadsheet to calculate the cost of the weekly shopping order. The spreadsheet will list all of the items that they regularly order along with the price of each item. Each week the quantity of each item will be different, but they have found that the prices rarely change.
Your task is to design the spreadsheet which is likely to include more than one actual worksheet.
John and Sue’s requirements are:
- It must be possible to change the price of one or more items
- It must be possible to copy the prices into a new worksheet for the following week
- They will change the quantity of each item ordered each week – so formulas and functions must be used to calculate the total cost of each item and the total cost of the shopping each week
- The spreadsheet must be easy to use – so that anyone who hasn’t used it before will be able to see help messages or other types of help when using it.
3
&
AO1 (PMD)
Look carefully at the data shown on the pricelist page. This shows the range of products that John and Sue regularly order from a supermarket. Also you must look at the shopping list page to get some ideas about the data you will be using for the first week’s shopping order.
4
!
AO1 (P)
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
Your design must include:
- column/row data labels
- cell formats
- formulae – including at least 2 of +, -, * and /
- functions that you intend to use (functions of at least 2 different types – see note)
Write your name on the sheet and store it safely in your work folder.
4a
!
AO1 (M)
The design must include:
- Column/row data labels
- Cell formats
- Formulae including all of +, -, * and /
- functions that you intend to use (functions of at least 3 different types – see note) – including one IF function
4b
!
AO1 (D)
The design must include:
- Column/row data labels
- Cell formats
- Formulae including all of +, -, *, / and brackets
- functions that you intend to use (functions of at least 4 different types – see note) – including one IF function
- relative and absolute cell referencing must be used
5
M
AO1 (PMD)
Finish your initial design
6
$
All AOs
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.
7
:
AO2 (PMD)
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). Save your work.
8
$
and
:
AO2 (PMD)
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.
It is essential that you merge cells somewhere in your spreadsheet as well as adjusting column width and row height. Also you must change the direction of text in at least one cell.
Use screenshots of doing this to show what you have done.
9
6
AO2 (PMD)
Set the headers and footers before you print your spreadsheet worksheet.
Print a copy of your spreadsheet showing only the pricelist data.
10
!
AO2 (PMD)
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.
11
:
AO2 (PMD)
Change the tab name of the worksheet to a suitable name – then make a copy of 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. Use screenshots of doing this to show what you have done. To see how to do this click here.
12
&
AO2 (PMD)
Look at the ‘Shopping List’ page. It shows details of the quantity of each item to be purchased by the family this week.
13
7
AO2 (PMD)
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.
14
:
AO2 (PMD)
Use formulae and functions to carry out calculations to work out the total cost of each of the shopping list items. Then use a suitable function to calculate the full cost of shopping for the week.<
15
6
AO2 (PMD)
Print a copy of your spreadsheet worksheet showing the totals have been automatically calculated. Make sure you have used suitable headers and/or footers. Label your printout if necessary. Store it in order in your work folder.
16
:
AO3 (P)
Sort the data in your spreadsheet using the “Cat” column. Use screenshots of doing this to show what you have done.
17
:
AO3 (MD)
Sort the data in your spreadsheet using both the “Cat” column (in ascending order) and the “Total” column (in descending order). Use screenshots of doing this to show what you have done.
18
6
AO3 (PMD)
Print out the spreadsheet 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.
19
:
AO3 (P)
Filter the data in your spreadsheet to show only the items where the total cost is more than £2.00. Use screenshots of doing this to show what you have done.
20
:
AO3 (MD)
Filter the data in your spreadsheet to show only the items where the total cost if more than £2.00 and the category is “F”. Use screenshots of doing this to show what you have done.
21
6
AO3 (PMD)
Print out the spreadsheet after filtering. 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.
22
$
AO2 (PMD)
Check that your formulae work correctly – you should do this by checking them using the accessory application called “calculator. Make a handwritten note of your calculations on the printout showing the total cost of all the shopping.
23
:
AO2 (PM)
Improve the appearance and user friendliness of your spreadsheet by making suitable changes to fonts, shading, borders etc. For example, you could make all price cells display with one colour background, whilst all quantity cells may have a different colour background. Use screenshots of doing this to show what you have done. Save your worksheet.
23a
:
AO2 (MD)
To achieve M or D you must use text and background colour and cell borders to distinguish between different types of cell (eg. Cells to input data, cells which automatically calculate.) Use screenshots of doing this to show what you have done.
24
:
AO2 (P)
You MUST change the direction of text in at least one cell. Use screenshots of doing this to show what you have done.
25
:
AO2 (M)
You MUST set text to wrap in at least one cell. Use screenshots of doing this to show what you have done.
26
:
AO2 (PM)
Add at least one cell comment, plus a validation message to help a new user of the spreadsheet. Use screenshots of doing this to show what you have done.
26a
:
AO2 (D)
The help you add to the spreadsheet must be sufficient to enable a beginner to use it with ease. The help must include suitable cell comments and validation with useful feedback to users. Use screenshots of doing this to show what you have done.
27
:
AO2 (M)
Use conditional formatting somewhere in the worksheet – for example to display cells with a different coloured or different sized font where the quantity ordered is more than one. Use screenshots of doing this to show what you have done.
28
:
AO2 (M)
Hide the “Cat” column. Use screenshots of doing this to show what you have done. When you have the screenshot “unhide” it again.
29
&
AO2 (PMD)
Look at this information:
During a one week “SALE” in March the supermarket discounts everything by 15%. John and Sue want to use the spreadsheet model to help them predict how much extra money they will have available to spend on other things.
30
:
AO2 (MD)
Change the tab name of the worksheet to a suitable name – then make a copy of the whole worksheet and use the copy for the next task. Use screenshots of doing this to show what you have done.
31
:
AO2 (PMD)
Insert two or three extra rows at the top of the new worksheet. You will use these rows for the next task. Use screenshots of doing this to show what you have done.
32
:
AO2 (D)
Enter a label “Discount” in one cell near the top of the new worksheet and the discount figure as 15% next to the “Discount” label near the top of the spreadsheet.
Create two new columns (with suitable headings)
· One column will need to display the new discount price of the item – rounded down to the nearest penny (you will need to use formulas which use absolute cell references to calculate this for each item)
· The second column will be used to calculate the new total cost of each item ordered. You will need to use a suitable formula to calculate the cost of each item)
You should also use a function to calculate the new cost of the shopping list during this sale.
Use screenshots of doing each step to show what you have done.
33
:
AO2 (D)
Use the spreadsheet model to display how much money John and Sue save (compared to the normal prices) for one shopping order when the SALE is taking place. <
34
6
AO2 (PMD)
Print a copy of your spreadsheet showing all of the formulae and functions. Also print a copy showing the figures.
35
!
AO4 (PMD)
Predict what changes will happen to the spreadsheet calculations if you change the rate of discount from 15% to 10% (this will mean that each item is reduced in price but by less than before) You can do this by using a screenshot and annotating it with what you think will happen.
36
6
AO4 (PMD)
Now change the rate of discount from 15% to 10%.
Look at any changes that happened automatically when you made this change. Comment on your findings and if your predictions were correct. You can do this by using a screenshot and annotating it.
36a
6
AO4 (D)
To achieve Distinction for AO4 you MUST use the spreadsheet model you have developed to make predictions and decisions. This means that you should make changes to some of the variables in the worksheet (like quantity, prices of individual items, rate of discount etc) and then write about these investigations and the results you find.
37
6
AO4 (PMD)
Print a copy of the spreadsheet – clearly label the cell that you have changed – and the cells that have changed automatically as a result.
38
!
AO4
(P only)
Predict what changes will happen to the spreadsheet calculations if you change the quantity of baked beans to 4 instead of 8. You can do this by using a screenshot and annotating it with what you think will happen.
40
6
AO4
(P only)
Change the quantity of “Baked beans” to 4 instead of 8.
Look at any changes that happened automatically when you made this change. Comment on your findings and if your predictions were correct. You can do this by using a screenshot and annotating it.
41
6
AO2 (PMD)
Change the tab name of the worksheet to a suitable name – then make a copy of the whole worksheet and use the copy for the next task. Use screenshots of doing this to show what you have done.
42
:
AO2 (MD)
Use the sub-totals feature of the software to work out the sub-totals spent on each category (cat) of shopping. <
43
:
AO5 (PMD)
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.
44
6
AO5 (PMD)
Print and label the chart.
45
:
AO5 (PMD)
Create a column 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.
46
6
AO5 (PMD)
Print and label the chart.
47
:
AO2 (MD)
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.
48
:
AO6 (PMD)
Record a macro which is run by a keyboard shortcut OR a button on the toolbar or worksheet – to print the worksheet when the button is clicked.
49
:
AO6 (PMD)
Print the macro code and label it to show what it does.
50
:
AO6 (MD)
Record a macro which can be run by both a keyboard shortcut AND a button on the toolbar or worksheet which does both of the following:
 | Copies the entire worksheet to a new sheet and deletes the quantity of items ordered in the new worksheet |
51
:
AO6 (MD)
Print the macro code and label it to show what it does.
52
AO6
(D only)
Record another macro which can be run by both a keyboard shortcut and a button on the worksheet or toolbar which automates a series of tasks.
53
:
AO6 (D)
Print the macro code and describe what it does and how the macro can be run.
54
:
All
Complete all required tasks and improve your work. Make sure you show it to your teacher regularly so it can be reassessed.