Worked example · Maths · Year 10
DCF: Data and computational thinking → Problem solving and modelling
A complete, secure Year 10 response, annotated so you can see which success criterion each part meets. It is also the answer key. It practises GCSE Mathematics and Numeracy (Double Award) Unit 1, section 1.8: wages and salaries, including payslips, and income tax and National Insurance, using the real 2026 to 2027 rules for a Welsh taxpayer paid monthly.
The 4 errors: D5 uses the wrong row’s rate; F4 taxes gross pay instead of taxable pay; G6 has the old 12% National Insurance rate typed in; H7 leaves Aisha out of the total. Correct totals: gross £7,864.80, income tax £570.72, National Insurance £228.29, net pay £7,065.79.
(a) Gross pay £1,177.60. Net pay £1,141.31.
(b) £25.92 + £10.37 = £36.29. Check: £1,177.60 − £36.29 = £1,141.31, the net pay on the payslip.
(c) The C means Ffion pays the Welsh rates of income tax, so HMRC knows the Welsh rates apply to her.
(d) Dylan’s gross pay is 64 × £12.80 = £819.20. That is less than £1,048, so all of it is below the tax-free amount and below the National Insurance threshold. The MAX(0, …) in the formulae turns his negative amounts into 0.
(e) Inputs: columns A, B and C (names, hours and hourly rates), which Ceri types in. Outputs: columns D to H and the totals in row 7, which are all worked out by formulae. Cells B9 to B12 hold the rules: the two thresholds and the two rates. Every formula refers to them, so when the rules change Ceri only has to change one cell, not every formula.
Each answer is a full sentence with £ signs, and (e) names the inputs, processes and outputs and explains why the rules are kept in their own cells. Evidences criteria 1 and 2.
(a) Osian
| Amount | Working | Answer |
|---|---|---|
| Gross pay | 110 × £12.80 | £1,408.00 |
| Taxable pay | £1,408.00 − £1,048 | £360.00 |
| Income tax | 20% of £360.00 = 0.2 × 360 | £72.00 |
| National Insurance | 8% of (£1,408.00 − £1,048) = 0.08 × 360 | £28.80 |
| Net pay | £1,408.00 − £72.00 − £28.80 | £1,307.20 |
(b) Megan’s taxable pay is £2,030.00 − £1,048 = £982.00. Income tax = 0.2 × 982 = £196.40. National Insurance = 0.08 × 982 = £78.56. Total taken off = £196.40 + £78.56 = £274.96. As a percentage of gross pay: 274.96 ÷ 2,030 × 100 = 13.5% (1 d.p.).
Megan is wrong because the 20% and the 8% are only charged on the part of her pay above £1,048. The first £1,048 has nothing taken off at all, so the total is 28% of £982, not 28% of £2,030.
Every step is set out with the calculation, the units and the answer, so a reader can follow the method. Part (b) explains the result in words, which is what the Unit 1 communicating and organising marks reward. Evidences criterion 1.
My answers for Osian do not match row 5: the spreadsheet shows gross pay £1,595.00, not £1,408.00. Megan’s income tax in F4 shows £406.00, not £196.40.
| Row | Gross = hours × rate? | Tax = 20% of taxable pay? | NI = 8% of (gross − 1048)? | Net = gross − tax − NI? |
|---|---|---|---|---|
| 2 Ffion | ✓ | ✓ | ✓ | ✓ |
| 3 Dylan | ✓ | ✓ | ✓ | ✓ |
| 4 Megan | ✓ | ✗ 406.00 is 20% of 2030, not of 982 | ✓ | ✓ |
| 5 Osian | ✗ 110 × 12.80 = 1408, not 1595 | ✓ | ✓ | ✓ |
| 6 Aisha | ✓ | ✓ | ✗ 8% of 1382 = 110.56, not 165.84 | ✓ |
Column H added up: 1141.31 + 819.20 + 1545.44 + 1441.84 + 1987.76 = 6935.55. H7 shows 4947.79. The difference is 6935.55 − 4947.79 = 1987.76, which is exactly Aisha’s net pay, so her pay is missing from the total.
Osian’s row passes three checks because only the gross pay in D5 is wrong. The formulae in E5, F5, G5 and H5 are correct, but they do correct calculations on a wrong number, so everything after D5 is wrong too. A check that a row agrees with itself cannot find an error in its input. Only my own calculation from the hours and the rate found it.
The checks are systematic: the same four tests on every row, with the evidence written in each cross. The final paragraph explains why a wrong value can pass a check. Evidences criterion 3.
| Cell | What the formula does wrong | Corrected formula | Other cells that were wrong because of it |
|---|---|---|---|
| D5 | =B5*C4 multiplies Osian’s hours by C4, Megan’s rate of £14.50, instead of his own rate in C5. | =B5*C5 | E5, F5, G5, H5, and the totals D7, F7, G7 and H7. |
| F4 | =D4*$B$10 takes 20% of Megan’s gross pay (D4). It should take 20% of her taxable pay (E4), so she was charged tax on her tax-free £1,048 too. | =E4*$B$10 | H4, F7 and H7. |
| G6 | =MAX(0,(D6-$B$11)*0.12) has 12% typed in. The rate is 8% and it is stored in B12. 12% was the main employee rate until January 2024. | =MAX(0,(D6-$B$11)*$B$12) | H6 and G7. (H7 did not show it only because H7 missed row 6 anyway.) |
| H7 | =SUM(H2:H5) stops at row 5, so Aisha’s net pay in H6 is left out. Aisha’s row was added in August and the total was never extended. | =SUM(H2:H6) | None, but it is the amount Ceri paid from the bank. |
Totals after the fixes: D7 = £7,864.80, F7 = £570.72, G7 = £228.29, H7 = £7,065.79. Check: 7864.80 − 570.72 − 228.29 = 7065.79. ✓
The G6 error happened because a number was typed into the formula. A cell reference is safer because the rate is stored in one place: when the rate changes, Ceri changes B12 and every formula updates. A typed number stays wrong without any warning, which is exactly what happened here.
Each error is found by comparing with the correct formula in row 2, explained in words, corrected in spreadsheet notation, and traced to every cell it affected. The totals are recalculated and checked. Evidences criteria 3 and 4.
(a) =B2*C2+I2*C2*1.5 (or =C2*(B2+1.5*I2)). It uses relative references, so when it is copied down to row 3 it becomes =B3*C3+I3*C3*1.5, and so on. A tidier version stores 1.5 in a rules cell such as B13 and uses =B2*C2+I2*C2*$B$13, which avoids the G6 mistake.
(b) Normal pay: 92 × £12.80 = £1,177.60. Overtime: 6 × £12.80 × 1.5 = £115.20. D2 should show £1,292.80.
Taxable pay: £1,292.80 − £1,048 = £244.80. Income tax: 0.2 × 244.80 = £48.96.
National Insurance: 0.08 × 244.80 = £19.584, which is £19.58 to the nearest penny.
Net pay: £1,292.80 − £48.96 − £19.58 = £1,224.26.
(c) 10 × £10 + 2 × £10 × 1.5 = £100 + £30 = £130. If the formula gives £130 for these inputs, it is doing the right thing.
The new formula is written so it can be copied down, and it is tested twice: once with real data worked out by hand, and once with simple numbers that can be checked mentally. Evidences criteria 1 and 5.
Aisha’s pay rise. £16.20 × 1.04 = £16.848, so £16.85 an hour. Gross: 150 × £16.85 = £2,527.50. Taxable: £1,479.50. Tax: £295.90. National Insurance: £118.36. Net pay: £2,113.24. Her net pay goes up by £2,113.24 − £2,043.04 = £70.20. It is less than £97.50 because 20% tax and 8% National Insurance are taken from every extra pound: £97.50 × 0.72 = £70.20.
Check cell. =IF(ROUND(F7+G7+H7-D7,2)=0,"OK","CHECK"). On 30 September it would have shown CHECK: 817.72 + 298.53 + 4947.79 = 6064.04, not 8051.80. But if Ceri had fixed only H7, it would show OK while the other three errors were still there, because those rows agree with themselves. A check cell catches some errors, not all of them.
A sixth person. Copy the formulae from D6:H6 into the new row 7. The totals move to row 8 and must become =SUM(D2:D7), =SUM(F2:F7), =SUM(G2:G7) and =SUM(H2:H7). To stop the error happening again: insert new staff above the last person so the SUM ranges stretch automatically, and keep the check cell.