dcf.cymru stimulus sheet · Maths · Year 10
The café, the people and their pay are invented. The income tax and National Insurance rules in Section 2 are the real rules for the 2026 to 2027 tax year for someone who pays Welsh rates of income tax and is paid monthly.
Ceri Watkins runs Caffi Glan Cynon, a small café in Mountain Ash. Five people work there and are paid once a month. Ceri built a spreadsheet to work out everyone’s pay, so she only has to type in the hours each month.
On 30 September 2026 she paid everyone using the spreadsheet. Two things worried her afterwards:
Ceri thinks there are 4 mistakes in the spreadsheet. Your job is to find and fix them.
These are the 2026 to 2027 rules for a Welsh taxpayer with the tax code C1257L who is paid monthly. The C tells the employer to use the Welsh rates of income tax. For basic rate taxpayers these are the same as in England.
| Deduction | Rule for one month’s pay |
|---|---|
| Income tax | The first £1,048 is tax-free. Pay above £1,048 is taxed at 20% (the basic rate). Nobody at the café earns enough to reach the higher rate. |
| National Insurance (NI) | Nothing is paid on the first £1,048. Pay above £1,048 is charged at 8%, up to £4,189 a month. Nobody at the café earns more than £4,189 a month. |
Where £1,048 comes from: the personal allowance is £12,570 a year. £12,570 ÷ 12 = £1,047.50, and HMRC’s monthly figure is £1,048.
Keeping it simple: pensions are left out of this spreadsheet. Real payroll software uses HMRC’s own tables and rounding, so a real payslip can differ from this model by a few pence.
| Word | Meaning |
|---|---|
| Gross pay | Pay before anything is taken off: hours × hourly rate. |
| Taxable pay | The part of gross pay that income tax is charged on: gross pay minus the tax-free £1,048. |
| Deductions | Money taken off gross pay, here income tax and National Insurance. |
| Net pay | Take-home pay: gross pay minus all deductions. |
| Cell reference | The address of a cell, such as C4 (column C, row 4). |
| Formula | An instruction in a cell that starts with =. The cell shows the answer, not the formula. * means multiply. |
| Absolute reference | A reference with dollar signs, such as $B$10. It stays pointing at the same cell when the formula is copied down to the next row. A reference without dollar signs, such as D2, moves down one row each time the formula is copied down. |
| MAX(0, …) | Gives the bigger of 0 and the value after the comma. It stops a result going below zero, so someone who earns less than £1,048 has 0 taxable pay, not a negative amount. |
| SUM(H2:H6) | Adds up every cell from H2 to H6. |
Caffi Glan Cynon, Mountain Ash · Payslip · Pay date: 30 September 2026 · Tax period: Month 6
Employee: Ffion Rees · Tax code: C1257L · NI number: QQ 12 34 56 C · NI category: A
| Payments | Amount | Deductions | Amount |
|---|---|---|---|
| Basic pay: 92 hours at £12.80 | £1,177.60 | Income tax | £25.92 |
| National Insurance | £10.37 | ||
| Gross pay | £1,177.60 | Total deductions | £36.29 |
| Net pay | £1,141.31 | ||
Money is shown to 2 decimal places. The grey letters and numbers are the column letters and row numbers.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Name | Hours | Rate (£ per hour) | Gross pay (£) | Taxable pay (£) | Income tax (£) | NI (£) | Net pay (£) |
| 2 | Ffion Rees | 92 | 12.80 | 1177.60 | 129.60 | 25.92 | 10.37 | 1141.31 |
| 3 | Dylan Price | 64 | 12.80 | 819.20 | 0.00 | 0.00 | 0.00 | 819.20 |
| 4 | Megan Hughes | 140 | 14.50 | 2030.00 | 982.00 | 406.00 | 78.56 | 1545.44 |
| 5 | Osian Morgan | 110 | 12.80 | 1595.00 | 547.00 | 109.40 | 43.76 | 1441.84 |
| 6 | Aisha Khan | 150 | 16.20 | 2430.00 | 1382.00 | 276.40 | 165.84 | 1987.76 |
| 7 | Total | 8051.80 | 817.72 | 298.53 | 4947.79 | |||
| 8 | ||||||||
| 9 | Tax-free pay per month (£) | 1048 | ||||||
| 10 | Income tax rate | 20% | ||||||
| 11 | NI threshold per month (£) | 1048 | ||||||
| 12 | NI rate | 8% | ||||||
Spreadsheet programs can show every formula instead of its answer. Columns A to C and rows 9 to 12 are typed in, so they are the same as in the values view.
| D | E | F | G | H | |
|---|---|---|---|---|---|
| 1 | Gross pay (£) | Taxable pay (£) | Income tax (£) | NI (£) | Net pay (£) |
| 2 | =B2*C2 | =MAX(0,D2-$B$9) | =E2*$B$10 | =MAX(0,(D2-$B$11)*$B$12) | =D2-F2-G2 |
| 3 | =B3*C3 | =MAX(0,D3-$B$9) | =E3*$B$10 | =MAX(0,(D3-$B$11)*$B$12) | =D3-F3-G3 |
| 4 | =B4*C4 | =MAX(0,D4-$B$9) | =D4*$B$10 | =MAX(0,(D4-$B$11)*$B$12) | =D4-F4-G4 |
| 5 | =B5*C4 | =MAX(0,D5-$B$9) | =E5*$B$10 | =MAX(0,(D5-$B$11)*$B$12) | =D5-F5-G5 |
| 6 | =B6*C6 | =MAX(0,D6-$B$9) | =E6*$B$10 | =MAX(0,(D6-$B$11)*0.12) | =D6-F6-G6 |
| 7 | =SUM(D2:D6) | =SUM(F2:F6) | =SUM(G2:G6) | =SUM(H2:H5) |
Ceri added Aisha’s row to the spreadsheet in August, when Aisha started work.