Mathematics Standard • Year 11 • Module 1 • Lesson 6
Formula Modelling with Spreadsheets
Practise HSC-style writing on spreadsheet modelling — three multi-mark short answers and one extended response with marking criteria.
1. Short-answer questions
1.1 A spreadsheet formula is =B2*C2+D2 where B2 = quantity, C2 = price per item, D2 = delivery. Calculate the total when B2 = 18, C2 = 3 and D2 = 12. State the order of operations you used. 3 marks Band 3
1.2 Write a spreadsheet formula for total pay if B2 is hours worked, C2 is hourly rate and D2 is a fixed allowance. Then write the equivalent algebra formula using letters P, h, r, a. 3 marks Band 3-4
1.3 A row uses =B2*C2 to find total cost from B2 (quantity) and C2 (price per item). In one row, a user enters C2 in cents instead of dollars.
(a) Describe in one sentence what goes wrong with the output.
(b) Explain how to fix the formula so it still works when one column is in cents. 4 marks Band 4
2. Extended response
2.1 A small farm sells produce at a Saturday market. The owner builds a spreadsheet to add up each row's total revenue, with these columns:
B = quantity sold (kg), C = price per kg ($), D = stall fee for the day ($), E = total revenue ($).
The total cell uses =B2*C2−D2 (the stall fee is subtracted because it is a cost, not income).
(a) Calculate the total in E2 when B2 = 42 kg of tomatoes, C2 = $6.50/kg, D2 = $35.
(b) Calculate the total in E3 when B3 = 18 kg of strawberries, C3 = $12.00/kg, D3 = $35.
(c) Write the formula in algebraic form using letters R, q, p, f.
(d) The owner is worried she's losing money on slow market days. Write down the inequality that says "Row 2 revenue is positive", solve it to find the minimum kilograms that must be sold at $6.50/kg to break even on a $35 stall fee, then write a one-sentence conclusion the owner could put on her planning notes. 7 marks Band 5-6
Explicit marking criteria
Part (a) — 1 mark
• 1 mark — correct total $238.00 (42 × 6.50 − 35).
Part (b) — 1 mark
• 1 mark — correct total $181.00 (18 × 12.00 − 35).
Part (c) — 2 marks
• 1 mark — correct symbols R = qp − f (or equivalent).
• 1 mark — clearly labels what each letter means.
Part (d) — 3 marks
• 1 mark — writes the inequality 6.50q − 35 > 0 (or 6.50q > 35).
• 1 mark — correctly solves to q > 5.38, so at least 5.4 kg (or 6 kg if whole-kg only).
• 1 mark — clear conclusion sentence naming the minimum sale and what it means for the owner.
Your response:
Stuck on (d)? Break-even means revenue = stall fee. Set 6.50q = 35 first, then "> break-even" gives the inequality.How did this worksheet feel?
What I'll revisit before next class:
1.1 — Spreadsheet total (3 marks)
Sample response.
Total = 18 × 3 + 12 = 54 + 12 = $66.
Order of operations: I did the multiplication first (B2 × C2 = 54), then added the delivery (D2 = 12). The * is always done before the + in a spreadsheet formula.
Marking notes. 1 mark — correct substitution shown. 1 mark — correct $66 with units. 1 mark — order-of-operations statement clear.
1.2 — Pay formula in two forms (3 marks)
Sample response.
Spreadsheet version: =B2*C2+D2.
Algebra version: P = hr + a (where P = pay, h = hours, r = hourly rate, a = fixed allowance).
Marking notes. 1 mark — correct spreadsheet formula. 1 mark — correct algebra formula. 1 mark — letters clearly defined.
1.3 — Unit error and fix (4 marks)
(a) Sample response. The output for that row will be 100 times larger than it should be. If quantity is 10 and price was meant to be $4 but is entered as 400 cents, =B2*C2 gives 4000 instead of 40 — the cell silently treats 400 as dollars.
(b) Sample response. Change the formula to =B2*C2/100 for that row only (or add a separate "convert to dollars" column and feed that into the total). The safer long-term fix is to require all rows in column C to use dollars.
Marking notes. 1 mark — clear description of what goes wrong. 1 mark — quantifies the error (e.g. "100 times too large"). 1 mark — proposes /100 (or column-wide rule). 1 mark — explicitly recommends unit consistency for the column.
2.1 — Saturday market revenue (7 marks): sample Band-6 response with annotations
Sample Band-6 response.
(a) Tomato row.
E2 = 42 × 6.50 − 35 = 273 − 35 = $238.00. [1 mark.]
(b) Strawberry row.
E3 = 18 × 12.00 − 35 = 216 − 35 = $181.00. [1 mark.]
(c) Algebra version.
R = qp − f, where R = revenue ($), q = quantity sold (kg), p = price per kg ($/kg), f = stall fee ($). [1 mark formula, 1 mark for clear letter definitions.]
(d) Break-even inequality.
Revenue must be positive: 6.50q − 35 > 0. [1 mark — correct inequality.]
Add 35 to both sides: 6.50q > 35. Divide by 6.50: q > 5.38 (to 2 d.p.). Since tomatoes are sold by the kg, the owner must sell at least 5.4 kg (or 6 kg if she only sells whole kilograms). [1 mark — correct cut-off.]
Conclusion: To break even on the $35 stall fee at $6.50/kg, the owner must sell more than 5.4 kg of tomatoes. Anything below this and the stall costs her money. [1 mark — clear, contextual conclusion.]
Total: 7/7.
Band descriptors for marker.
Band 3: Substitutes correctly for one row but does not subtract the stall fee. ≈ 2 marks.
Band 4: Both rows correct in (a) and (b), and writes a formula but does not label the letters. ≈ 4 marks.
Band 5: All four parts attempted; inequality set up correctly but solved with an arithmetic slip or weak conclusion. ≈ 5-6 marks.
Band 6: Complete, correct; conclusion sentence ties the maths back to the owner's planning decision. 7/7.