Mathematics Standard • Year 11 • Module 1 • Lesson 6
Formula Modelling with Spreadsheets
Apply spreadsheet formulas to real Australian contexts — canteen orders, payroll, hire trips and tradie quotes.
Problem 1 — School canteen order sheet
A canteen uses a spreadsheet with columns: B = quantity, C = price per item, D = delivery fee. The total cell uses =B2*C2+D2.
Set up: What are we solving for?
(i) Calculate the total when B2 = 25 pies, C2 = $3.20, D2 = $8.00. 2 marks
(ii) Calculate the total when B2 = 40 sandwiches, C2 = $4.50, D2 = $8.00. 2 marks
(iii) Explain what would happen to the total in (ii) if the delivery fee in D2 changed to $0. 1 mark
Stuck? Revisit lesson § Worked Example 1 — Interpret a spreadsheet formula.Problem 2 — Weekend payroll sheet
A cafe pays casual staff using =B2*C2+D2, where B2 = hours, C2 = hourly rate, D2 = weekend bonus.
Set up: What are we solving for?
(i) Tara works 6 h at $26/h with a $40 weekend bonus. Calculate her pay. 2 marks
(ii) Jay works 8.5 h at $24.50/h with no bonus (D2 = 0). Calculate his pay. 2 marks
(iii) The owner copies the same formula down for row 3 (Jay). Write the formula that appears in row 3's total cell. 1 mark
Stuck on (iii)? Revisit lesson § Worked Example 2 — every row number in the formula bumps up by one.Problem 3 — Comparing outputs when one input changes
A hire shop models total cost with T = 8n + 15, where n is the number of chairs hired.
Set up: What are we solving for?
(i) Calculate T when n = 10 and when n = 14. 2 marks
(ii) By how many dollars did T change between n = 10 and n = 14? 1 mark
(iii) Without recalculating from scratch, find T when n = 20 by adding 6 more chairs to your n = 14 answer. 2 marks
Stuck? Revisit lesson § Worked Example 3 — Compare outputs when one input changes. Each extra chair adds $8.Problem 4 — Plumber's quote builder
A plumber uses a spreadsheet to build quotes: column B = parts cost, column C = labour hours, column D = hourly rate, column E = callout fee. The total cell uses =B2+C2*D2+E2.
Set up: What are we solving for?
(i) A job has B2 = $145 (parts), C2 = 3 (hours), D2 = $95 (rate), E2 = $80 (callout). Calculate the quote total. Show the order of operations. 3 marks
(ii) A second job has B2 = $60, C2 = 1.5, D2 = $95, E2 = $80. Calculate the total. 2 marks
(iii) Translate =B2+C2*D2+E2 into a single sentence in plain English. 2 marks
Stuck on order of operations? * (multiply) is always done before + (add), even without brackets.Problem 5 — Spotting the input error
A small bakery records flour use in a spreadsheet. Column B is mass per loaf (in grams), column C is number of loaves, and the total cell uses =B2*C2 to give total flour in grams.
Set up: What are we solving for? What units must B2 use?
(i) Row 2: B2 = 450 g, C2 = 30 loaves. Calculate the total flour used. 1 mark
(ii) Row 3 was entered as B3 = 0.5 kg (instead of 500 g), C3 = 24 loaves. Calculate what the spreadsheet would output, then state what it should have been. 3 marks
(iii) Explain in one sentence why mixing grams and kilograms in column B is dangerous in a spreadsheet. 1 mark
Stuck? Revisit lesson § Keep Units and Inputs Consistent — the formula has no way of knowing one row used different units.How did this worksheet feel?
What I'll revisit before next class:
Problem 1 — Canteen order
Set up. Substitute the row values into =B2*C2+D2. Multiply first, then add the delivery.
(i) Total = 25 × 3.20 + 8.00 = 80 + 8 = $88.00.
(ii) Total = 40 × 4.50 + 8.00 = 180 + 8 = $188.00.
(iii) If D2 = 0, the delivery fee adds nothing. The new total would drop by $8 to $180.00.
Problem 2 — Payroll
Set up. Pay = hours × rate + bonus, then substitute the row values.
(i) Tara: 6 × 26 + 40 = 156 + 40 = $196.
(ii) Jay: 8.5 × 24.50 + 0 = 208.25 + 0 = $208.25.
(iii) Row 3 formula: =B3*C3+D3 (every row reference bumps from 2 to 3).
Problem 3 — Comparing outputs
Set up. Substitute n into T = 8n + 15. Each extra chair adds $8 because that is the coefficient of n.
(i) T(10) = 8(10) + 15 = $95. T(14) = 8(14) + 15 = $127.
(ii) Difference = 127 − 95 = $32 (4 extra chairs at $8 each).
(iii) From n = 14 to n = 20 is 6 more chairs at $8 each = +$48. New total = 127 + 48 = $175. (Check: 8(20) + 15 = 175.)
Problem 4 — Plumber quote
Set up. Total = parts + labour hours × rate + callout. * runs before +.
(i) 145 + 3 × 95 + 80 = 145 + 285 + 80 = $510.
(ii) 60 + 1.5 × 95 + 80 = 60 + 142.50 + 80 = $282.50.
(iii) "Add the parts cost, plus labour hours multiplied by the hourly rate, plus the callout fee."
Problem 5 — Unit error
Set up. B2 must be in grams for the formula to give total grams.
(i) Row 2: 450 × 30 = 13 500 g (= 13.5 kg).
(ii) Row 3 as entered: 0.5 × 24 = 12 (spreadsheet says 12 g). What it should be: 500 g × 24 = 12 000 g. The output is 1000 times too small.
(iii) The spreadsheet treats every cell as a plain number; it has no way to know 0.5 means kilograms while 500 means grams, so mixing units silently produces wildly wrong totals.