Cover lesson · Maths · Year 10 · Intermediate

Debugging a payslip spreadsheet: Caffi Glan Cynon

DCF: Data and computational thinking → Problem solving and modelling

Name: Class: Date:

Cover teacher: you need no subject knowledge

What this practises: GCSE Mathematics and Numeracy (Double Award), Unit 1: Financial Mathematics and Other Applications of Numeracy, section 1.8 Personal/household finance and enterprise: wages and salaries, including payslips, and taxation, including income tax and National Insurance. Unit 1 is a calculator paper, so calculators are allowed all lesson.

Print: this sheet, one per pupil, and the stimulus sheet “Caffi Glan Cynon: the pay spreadsheet”, one between two. No computers are needed: the spreadsheet is printed. Pupils need a calculator and a highlighter.

Run: 2 min read “What you are doing” aloud · 8 min Step 1, the payslip and the rules · 12 min Step 2, pay worked out by hand · 8 min Step 3, checking the values · 15 min Step 4, finding and fixing the 4 errors · 10 min Step 5, adding overtime to the model. Total 55 minutes.

Collect: this sheet. The class teacher has the answer key (the worked example). If pupils ask whether an answer is right, ask them to check it against the rules in Section 2 of the stimulus.

What you are doing

Ceri runs a café and uses a spreadsheet to work out her staff’s pay. A spreadsheet is a model: the hours and rates go in, formulae follow the tax and National Insurance rules, and the pay comes out. This month the model gave wrong answers. You will work out the correct pay by hand, use your answers to spot which cells are wrong, then read the formulae to find and fix the 4 errors. Finally, you will add a new rule to the model.

Step 1: read the payslip and the rules

Use Sections 2, 3 and 4 of the stimulus sheet.

(a) What is Ffion’s gross pay, and what is her net pay?

(b) How much is taken off Ffion’s pay in total? Show the addition.

(c) What does the letter C at the start of the tax code C1257L tell the employer?

(d) Look at Dylan in Section 5. Explain why he pays no income tax and no National Insurance.

(e) A model has inputs (typed in), processes (formulae) and outputs (answers). In the spreadsheet, which columns are inputs, and which columns are outputs? What are the values in cells B9 to B12 for?

Step 2: work out the pay by hand

(a) Osian worked 110 hours at £12.80 an hour. Work out his pay using the rules in Section 2. Show every step.

AmountWorkingAnswer
Gross pay
Taxable pay
Income tax
National Insurance
Net pay

(b) Megan’s gross pay is £2,030.00. Work out her correct income tax and National Insurance. Then find what percentage of her gross pay is taken off in total, to 1 decimal place. Megan thinks she loses 28% of her pay (20% plus 8%). Explain why she is wrong.

Step 3: check the values view

Compare your answers from Step 2 with rows 4 and 5 of the values view. Then use your calculator to run these checks on every row. Write ✓ or ✗ in each box, and highlight any cell on the stimulus sheet that you think is wrong.

RowGross = hours × rate?Tax = 20% of taxable pay?NI = 8% of (gross − 1048)?Net = gross − tax − NI?
2 Ffion
3 Dylan
4 Megan
5 Osian
6 Aisha

Now add up column H yourself. Does your total match cell H7?

Osian’s row passes three of the four checks, but his net pay is still wrong. Explain how that can happen.

Step 4: find and fix the 4 errors

Use the formula view in Section 6. Row 2 is correct, so compare every other formula with the one in row 2. For each error, write the cell, what is wrong, the corrected formula, and the other cells that showed a wrong answer because of it.

CellWhat the formula does wrongCorrected formulaOther cells that were wrong because of it

When all 4 errors are fixed, what will the totals in D7, F7, G7 and H7 be? Check that D7 − F7 − G7 = H7.

One of the errors happened because a number was typed into a formula instead of a cell reference. Which one? Why is a cell reference safer?

Step 5: add overtime to the model

From October, Ceri will pay overtime at time and a half: each overtime hour is paid at 1.5 × the normal hourly rate. She will type the overtime hours into a new column, column I.

(a) Write a new formula for cell D2 that works out gross pay including overtime. It must still work when it is copied down to rows 3 to 6.

(b) Test your formula. Ffion works 92 normal hours and 6 overtime hours. Work out by hand what D2 should show, then work out her new income tax, National Insurance and net pay.

(c) Choose a test with simple numbers that you can check in your head, for example 10 hours, £10 an hour and 2 overtime hours. What should your formula give?

Step by step

  1. 2 min Read “What you are doing”.
  2. 8 min Step 1: read the payslip and the rules, answer (a) to (e).
  3. 12 min Step 2: work out Osian’s and Megan’s pay by hand. Show your working.
  4. 8 min Step 3: run the four checks on every row and highlight the suspicious cells.
  5. 15 min Step 4: find and fix the 4 errors in the formula view.
  6. 10 min Step 5: add overtime to the model and test it.

Success criteria

If you finish early