Cover lesson · Maths · Year 10 · Intermediate
DCF: Data and computational thinking → Problem solving and modelling
Name: Class: Date:
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.
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.
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?
(a) Osian worked 110 hours at £12.80 an hour. Work out his pay using the rules in Section 2. Show every step.
| Amount | Working | Answer |
|---|---|---|
| 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.
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.
| Row | Gross = 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.
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.
| Cell | What the formula does wrong | Corrected formula | Other 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?
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?