WALT
AO5 Create and use a simple spreadsheet
A B C D E F G H
1 Event Cost to SWS Profit VAT Selling Price
To calculate the total
2 Meal for two at a top restaurant 325.00 81.25 60.94 467.19
selling price use the SUM
3 Balloon trip over North Yorkshire Moors 125.00 31.25 23.44 179.69 function to add the Cost,
4 Prime seats at a premiership football match 210.00 52.50 39.38 301.88 Profit and VAT.
5 Centre court tickets for the final at Wimbledon 450.00 112.50 84.38 646.88
6 Flight around the British Coast in a jet plane 725.00 181.25 135.94 1,042.19
7 Top seats at the latest musical in the West End 180.00 45.00 33.75 258.75
8
Task 1 (PMD) Profit will be at VAT will be charged on
9 Task 2 (PMD)
Create a spreadsheet for the 25% so use the the total of the cost
10 sales and marketing team. and the profit
formula
11 =B2*25% =SUM(B2:C2)*15%
12 Use a function to work out the
Type in the data from columns cheapest (MIN) and most expensive
13 Cheapest 179.69
A and B. (MAX) events.
14 Most expensive 1,042.19
15 Use formulae to work out the Use a function (SUM) to work out
16 Profit, VAT and Selling Price Total raised 2,896.56 how much money will be donated to
17 Cancer Research by adding up all
18 the selling prices.
19 Average price 482.76
20
21 Use a function to work
22 out the average price of
23 the events (AVERAGE)
24
25
26
27
Make sure the spreadsheet is set up for
28 'landscape', print it out page 1 only and annotate
29 to show the formulae used
30
A B C D E F G
1 SWS Charity Sale Column
2 Event Cost to SWS Profit VAT Selling Price headings
3 Meal for two at a top restaurant £325.00 £81.25 £60.94 £467.19
4 Balloon trip over Nort Yorkshire Moors £125.00 £31.25 £23.44 £179.69
5 Prime seats at a premiership football match £210.00 £52.50 £39.38 £301.88
6 Centre court tickets for the final at Wimbledon £450.00 £112.50 £84.38 £646.88
7 Flight around the British Coast in a jet plane £725.00 £181.25 £135.94 £1,042.19
8 Top seats at the latest musical in the West End £180.00 £45.00 £33.75 £258.75
9
Task 3 (PMD)
10 Delete three rows from
Format the spreadsheet to show the here leaving just two
11 Cheapest £179.69
numbers as currency like this.
12
13
Format the column and row headings. Most expensive £1,042.19
row
14
headings
15 Total raised £2,896.56 Add a row here
between Cheapest
16
and Most
17 Average price £482.76 expensive
Task 4 (MD only)
18
19
Do some screen shots to show 'before' and
20 'after'. Edit the spreadsheet by adding and
21 deleting rows so it looks better. Insert a row and
22 add a title at the top. Show screen shots before
23 and after making changes
24
25
26 Make sure the spreadsheet is set up for
27 'landscape', print it out and annotate to explain
28 the formatting used.
29
30
A B C D E
1 SWS Charity Sale
2 Event Cost to SWS Profit VAT Selling Price
3 Meal for two at a top restaurant £325.00 £130.00 £68.25 £523.25
4 Balloon trip over Nort Yorkshire Moors £125.00 £50.00 £26.25 £201.25
5 Prime seats at a premiership football match £210.00 £84.00 £44.10 £338.10
6 Centre court tickets for the final at Wimbledon £450.00 £180.00 £94.50 £724.50
7 Flight around the British Coast in a jet plane £725.00 £290.00 £152.25 £1,167.25
8 Top seats at the latest musical in the West End £180.00 £72.00 £37.80 £289.80
9
10
11 Cheapest £201.25
12 Task 5 (PMD)
13 Alter the spreadsheet to show what would Most expensive £1,167.25
14 happen if SWS increased the profit to 40%
15 Total raised £3,244.15
16
17 Average price £540.69
18 Make sure the spreadsheet is set up for
19 'landscape', print it out and annotate by hand to
20 show the changes made to the formulae.
21
A B C D E F G
1 SWS Charity Sale
2 Event Cost to SWS Profit VAT Selling Price
3 Meal for two at a top restaurant £325.00 £130.00 £0.00 £455.00
4 Balloon trip over Nort Yorkshire Moors £125.00 £50.00 £0.00 £175.00
5 Prime seats at a premiership football match £210.00 £84.00 £0.00 £294.00
6 Centre court tickets for the final at Wimbledon £450.00 £180.00 £0.00 £630.00
7 Flight around the British Coast in a jet plane £725.00 £290.00 £0.00 £1,015.00 VAT at 0%
8 Top seats at the latest musical in the West End £180.00 £72.00 £0.00 £252.00
9
10 Task 6 (PMD)
11 Change the spreadsheet to show what Cheapest £175.00
12 would happen if VAT was rated at 0%
13 because the event is for charirty. Most expensive £1,015.00
14
15 Total raised £2,821.00
Task 7 (MD only)
16 Print out the spreadsheet in 'Landscape' and
17 annotate changes to formulae. Average price £470.17
18 Check print preview first. Make sure it fits
19 one page.
20
21
Print out the spreadsheet showing the
22
23
formulae. Check print preview.
24