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.