Supply teacher deck · Maths · Year 8

Auditing Formulas in a Charity Fun Run Spreadsheet

Slide 1 of 7
05:00

Slide 1 of 7 · Settling starter · Maths · Year 8

5 minutes

Auditing Formulas in a Charity Fun Run Spreadsheet

Year 8 at Ysgol Cwm Branwen have completed a charity fun run around Llyn Brenig. The spreadsheet printout shows a grand total of £212.00, but the cash tin holds £385.00.

While the register is called: write your name, class and date on your worksheet, then look at Row 7 (Osian) on the stimulus sheet.

Starter question: Osian gets £2.00 per km base rate plus £2.00 per km ridge bonus, and he ran 7 km. How much sponsor money should he have in cell E7, and why did the formula =B7+C7*D7 give £16.00 instead?

DCF: Problem-solving and modelling · LNF: Calculation No computers needed · work on your sheet

Slide 2 of 7 · Today’s brief and key words · Maths · Year 8

5 minutes

Your job today: audit and fix the model

A spreadsheet is only as reliable as its formulas. Today you will spot 4 formula bugs on the printout, work out the true totals by hand, and compare the mean and median sponsorship.

Cell references

Relative (B2): changes row when dragged down.
Absolute ($B$2): locks cell B2 so it never shifts.

Order of operations

Spreadsheets calculate brackets first, then multiplication, then addition: use =(B5+C5)*D5.

Mean vs median

Mean: total divided by count.
Median: middle number when ordered.

DCF: Problem-solving and modelling · LNF: Calculation Read sections 1 and 2 of the stimulus sheet

Slide 3 of 7 · Step 1 · Spot and fix the 4 broken formulas

10 minutes

Step 1: Audit the 4 broken cells

Check the formula view on your stimulus sheet against the four rules in section 2. Complete the Step 1 table on your worksheet.

Cell What was typed What kind of bug is it? Correct formula
E6 (Megan) =(2.50+1.50)*5 Hardcoded numbers (used old 5 km distance instead of cell references) =(B6+C6)*D6
E7, F8, G12 See stimulus sheet Identify the bug and explain what went wrong Write on your sheet
DCF: Problem-solving and modelling · LNF: Calculation Start every corrected formula with =

Slide 4 of 7 · Step 2 · Recalculate the true totals

10 minutes

Step 2: Calculate the true values by hand

Show your working for each corrected cell and find the true Grand Total in cell G12. Remember that cell B2 holds the PTA match bonus of £1.00 per kilometre.

Rule for Column E (Sponsor total): (Base rate + Ridge bonus) × Distance

Rule for Column F (PTA bonus): Distance × £1.00

Rule for Column G (Final pupil total): Column E + Column F

DCF: Problem-solving and modelling · LNF: Calculation Check: your 7 pupil totals should add up to £385.00

Slide 5 of 7 · Step 3 · Mean, median and the £145 outlier

12 minutes

Step 3: Compare the mean and the median

Lowri raised £145.00 because her aunt’s company sponsored her £12.00 per kilometre. In Step 3, work out the mean, median and range of all 7 corrected totals, and the mean of the other 6 pupils without Lowri.

Measure How to calculate it
Mean (all 7 pupils) Total raised (£385) ÷ 7 pupils
Median (all 7 pupils) Put the 7 totals in order from smallest to largest and pick the 4th value
Mean without Lowri (6 pupils) (£385 − £145) ÷ 6 pupils
DCF: Problem-solving and modelling · LNF: Calculation Show all division and ordering steps on your sheet

Slide 6 of 7 · Step 4 · Audit note and self-check

5 minutes

Step 4: Write your recommendation for the newsletter

The head of year wants to write in the school newsletter what a “typical” runner raised. Complete Step 4 by explaining whether she should quote the mean (£55) or the median (£40), and why.

Check your work against the success criteria:

1. Did you start every spreadsheet formula with = and include $B$2 in F8?
2. Did you quote both the mean (£55) and the median (£40) and explain the effect of the £145 outlier?

DCF: Problem-solving and modelling · LNF: Calculation Tick the success criteria box on your worksheet

Slide 7 of 7 · Plenary · Final answer check

3 minutes

Plenary: Check your headline answers

Check your answers against the summary below before handing in your worksheet.

Question Correct answer
4 fixed formulas E6: =(B6+C6)*D6 · E7: =(B7+C7)*D7 · F8: =D8*$B$2 · G12: =SUM(G5:G11)
Corrected pupil totals Megan G6 = £35.00 · Osian G7 = £35.00 · Catrin G8 = £40.00
True Grand Total & averages G12 = £385.00 · Mean = £55.00 · Median = £40.00 · Range = £115.00
DCF: Problem-solving and modelling · LNF: Calculation Make sure your name and class are on your worksheet