Stimulus: Ysgol Cwm Branwen charity fun run spreadsheet printout

1. The scenario and the sponsorship rules

Seven Year 8 pupils at Ysgol Cwm Branwen completed a sponsored run around Llyn Brenig for the Wales Air Ambulance. Each pupil collected two per-kilometre sponsor rates from family and neighbours, plus a match-funding bonus from the Parent Teacher Association (PTA):

Column B (Base rate per km): money pledged for every kilometre the pupil completed.

Column C (Ridge-loop bonus per km): extra money pledged for every kilometre because the runners tackled the steep ridge trail.

Column D (Distance completed in km): checked by marshals at the end of the race. Note: Megan’s distance in cell D6 was updated from 5 km to 7 km after the final marshal sheet arrived.

Column E (Sponsor total): for each kilometre, add the Base rate (Col B) and the Ridge-loop bonus (Col C) together, then multiply that combined rate by the Distance completed (Col D).

Column F (PTA match bonus): the PTA adds £1.00 per kilometre (stored in cell B2) for every kilometre a pupil runs.

Column G (Final pupil total): Sponsor total (Col E) plus PTA match bonus (Col F).

2. Four spreadsheet rules to check during your audit

Never hardcode numbers inside a formula: use cell references (such as B6, C6, D6) so that if a runner’s distance is updated, the total updates automatically.
Use brackets when adding rates before multiplying: spreadsheets follow BIDMAS (order of operations). In =B7+C7*D7, the computer multiplies C7*D7 first and only adds B7 once. Writing =(B7+C7)*D7 forces it to add the two per-kilometre rates first.
Lock fixed cells with dollar signs ($B$2): when you drag a formula down a column, a relative reference like B2 changes to B3, B4, and so on. Writing $B$2 locks the reference to cell B2.
Check the start and end of every =SUM range: =SUM(G5:G11) adds every cell from G5 down to G11 inclusive.

3. Printout A: Normal view (what the screen displays)

Row	A	B	C	D	E	F	G
1	Ysgol Cwm Branwen: Year 8 Llyn Brenig Charity Fun Run
2	PTA bonus per km (£)	1.00
3	[blank row]
4	Pupil name	Base rate / km (£)	Ridge bonus / km (£)	Distance (km)	Sponsor total (£)	PTA bonus (£)	Final total (£)
5	Aled	2.50	1.50	6	24.00	6.00	30.00
6	Megan	2.50	1.50	7	20.00	7.00	27.00
7	Osian	2.00	2.00	7	16.00	7.00	23.00
8	Catrin	3.00	1.00	8	32.00	0.00	32.00
9	Rhys	2.50	1.50	9	36.00	9.00	45.00
10	Nia	3.00	1.50	10	45.00	10.00	55.00
11	Lowri	12.00	1.50	10	135.00	10.00	145.00
12	Grand Total raised (£)	212.00

Row

Ysgol Cwm Branwen: Year 8 Llyn Brenig Charity Fun Run

PTA bonus per km (£)

1.00

[blank row]

Pupil name

Base rate / km (£)

Ridge bonus / km (£)

Distance (km)

Sponsor total (£)

PTA bonus (£)

Final total (£)

Aled

2.50

1.50

24.00

6.00

30.00

Megan

2.50

1.50

20.00

7.00

27.00

Osian

2.00

16.00

7.00

23.00

Catrin

3.00

1.00

32.00

0.00

32.00

Rhys

2.50

1.50

36.00

9.00

45.00

Nia

3.00

1.50

45.00

10.00

55.00

Lowri

12.00

1.50

135.00

10.00

145.00

Grand Total raised (£)

212.00

4. Printout B: Formula view (what was typed into Columns E, F and G)

Row	Pupil	Column E (Sponsor total)	Column F (PTA bonus)	Column G (Final total)
5	Aled	`=(B5+C5)*D5`	`=D5*$B$2`	`=E5+F5`
6	Megan	`=(2.50+1.50)*5`	`=D6*$B$2`	`=E6+F6`
7	Osian	`=B7+C7*D7`	`=D7*$B$2`	`=E7+F7`
8	Catrin	`=(B8+C8)*D8`	`=D8*B3`	`=E8+F8`
9	Rhys	`=(B9+C9)*D9`	`=D9*$B$2`	`=E9+F9`
10	Nia	`=(B10+C10)*D10`	`=D10*$B$2`	`=E10+F10`
11	Lowri	`=(B11+C11)*D11`	`=D11*$B$2`	`=E11+F11`
12	Grand Total			`=SUM(G5:G10)`

Row

Pupil

Column E (Sponsor total)

Column F (PTA bonus)

Column G (Final total)

Aled

=(B5+C5)*D5

=D5*$B$2

=E5+F5

Megan

=(2.50+1.50)*5

=D6*$B$2

=E6+F6

Osian

=B7+C7*D7

=D7*$B$2

=E7+F7

Catrin

=(B8+C8)*D8

=D8*B3

=E8+F8

Rhys

=(B9+C9)*D9

=D9*$B$2

=E9+F9

Nia

=(B10+C10)*D10

=D10*$B$2

=E10+F10

Lowri

=(B11+C11)*D11

=D11*$B$2

=E11+F11

Grand Total

=SUM(G5:G10)