Mr. Ward's Teaching Materials

080i - Digital Tools for Financial Modelling

7 min read

Lesson 80 — Using Digital Tools for Efficient Financial Calculation

Strand: Number | Descriptor: AC9M7N09 | Duration: 45 minutes

Room/equipment: computer lab or class set of devices with spreadsheet software.

Learning Intentions

  • To build a spreadsheet that models a financial situation.
  • To use formulas and cell references so the model updates automatically.

Success Criteria

I can:

  1. Enter data and label it clearly in a spreadsheet.
  2. Write formulas using cell references, *, +, -, / and SUM.
  3. Use the fill handle to repeat a formula down a column.
  4. Change an input and interpret how the outputs respond (“what-if” analysis).

Warmup

(6 minutes — spot the difference, projected)

Two versions of the same stall budget are projected:

  • Sheet 1: every total typed in by hand as a number.
  • Sheet 2: totals produced by formulas (=B2*C2, =SUM(D2:D6)).

Change the price of one item in each sheet.

  1. What happens in Sheet 1? In Sheet 2?
  2. Which sheet do you trust more, and why?
  3. When would a hand-typed number be dangerous?

Answers: 1. Sheet 1’s totals are now wrong and silently so; Sheet 2 updates everything instantly; 2. Sheet 2 — one source of truth per number; 3. Whenever inputs change — which in real budgeting is always.

The principle: type each fact once; compute everything else.

Activities

Activity 1 — Guided Build: the Sausage Sizzle Sheet (16 min)

Rebuild Lesson 77’s carnival model as a live spreadsheet, teacher leading, students following.

Layout to build:

ABCD
1ItemQtyUnit costCost
2Sausage packs219.50=B2*C2
3Bread loaves203.20=B3*C3
4Onions (kg)53.40=B4*C4
5Total=SUM(D2:D4)

Teach explicitly, in order:

  1. Labels first — row and column headings before any numbers.
  2. A formula starts with =. =B2*C2 means “this cell shows B2 times C2”.
  3. The fill handle — write =B2*C2 once, drag down to D4. Watch the row numbers adjust automatically.
  4. SUM for totals — =SUM(D2:D4), not =D2+D3+D4 (which breaks when rows are added).
  5. Sanity-check against the by-hand answer from Lesson 77: total should be 280.50$. If the sheet disagrees, the sheet is wrong — find the error.

Extend the sheet — revenue and profit:

AB
7Sandwiches sold420
8Price each3.50
9Revenue=B7*B8
10Profit=B9-D5

Check: profit 1189.50$ ✓ (matches Lesson 77’s extension).

Activity 2 — What-if Analysis (14 min)

Pairs, using their built sheet. Record each answer before moving on.

Use the model to answer — changing only input cells, never formula cells:

  1. Sausage packs rise to 11$. New total cost and profit?
  2. Reset. Attendance falls so only sandwiches sell (adjust B7; leave the shopping as bought). Profit?
  3. Reset. The P&C debates pricing: try 3$4$4.50$ per sandwich. Record the profit each time. What does each 50c step add?
  4. Find, by trial, the sale price that gives a profit of about 1400420$ sold.

Socratic scaffolding for Q3–4:

PromptPurpose
Before trying: predict what a 50c rise adds at sold.210$ — the model should confirm.
Does the sheet agree?Yes: profits step … check at 31260 - 280.50$… careful — record actual cells.
For Q4, bracket the target.3.501189.50$41399.50$1400$4$.
Why is trial fast here?Each trial is one keystroke — this is the point of building the model.
Looking backThe spreadsheet turned a calculation into an instrument: you play inputs and read outputs.

Answers: 1. Cost 312$115812601260 - 280.50 = $979.50$3979.504201260 - 280.50 = 979.50$41399.50$4.501609.50$210$4.00$1399.50 \approx $1400$).

Activity 3 — Independent Mini-build (9 min)

Individually. Build from scratch — no template.

The movie night (from Lesson 78): hall 120$60$2.50$7$, attendance in one input cell.

  1. Build the sheet with attendance . What is the profit?
  2. Use the sheet to find the break-even attendance by trial.
  3. Check the break-even against the algebra from Lesson 78.

Answers: 1. 270404.5n - 180 = 0 \Rightarrow n = 40$ exactly — the algebra and the spreadsheet are two views of one model.

Checks for Understanding

(5 minutes — exit ticket, collected)

  1. What does the formula =B3*C3 do, and why is it better than typing the product?
  2. What is the advantage of =SUM(D2:D6) over =D2+D3+D4+D5+D6?
  3. In a what-if analysis, which cells may you change?
  4. Your spreadsheet and your by-hand answer disagree. What should you do first?
  5. Reasoning. A spreadsheet gives an answer instantly. Why must you still estimate?

Answers: 1. Multiplies those two cells; it updates automatically when either input changes; 2. It survives inserted rows and is shorter and less error-prone; 3. Input cells only — never formula cells; 4. Re-check both, starting with the sheet’s formulas (a mistyped reference is the usual culprit) — disagreement means at least one is wrong; 5. The sheet computes what you asked, not necessarily what you meant — an estimate catches a wrong formula, which no amount of computing speed will.

Common Misconceptions

MisconceptionHow to pre-empt it
Typing computed values instead of formulas.The warmup’s broken Sheet 1; the “type each fact once” principle.
Overwriting formula cells during what-ifs.Name the rule: inputs only. Colour input cells if the software allows.
Forgetting the leading =.The text just sits there — show it once, name it, move on.
Believing the spreadsheet is authoritative.Exit ticket Q4/Q5 — a sheet automates your model, errors included.
Building without labels.Labels first is step 1 of the build, non-negotiable.
Dragging a fill down over a total row.Watch the SUM row while filling; undo is the friend.

Enrichment — Competition-Style Problems

E1 (Kangaroo style). A cell contains =B2*C2 where B2 and C2 . What does it display?

Answer

.

E2 (AMC Junior style). A spreadsheet column holds and a cell =SUM(A1:A4)/4. What does it display, and what is it computing?

Answer

— the mean of the four values.

E3 (Challenge). A profit model is =B1*B2-45-0.8*B1 with B1 items sold and B2 price. Write it as algebra, and find the break-even for B2 .

Answer

. At : , so items.

E4 (Challenge). Explain why =SUM(D2:D4) still gives the right total after a new row is inserted between rows 2 and 3, but =D2+D3+D4 does not.

Answer

The range D2:D4 stretches automatically to D2:D5 when a row is inserted inside it; the explicit sum keeps its three original references and silently omits the new row — the most dangerous kind of spreadsheet error, because nothing looks wrong.

Homework

(No devices assumed — paper spreadsheet reasoning.)

  1. This sheet models a cupcake stall. Fill in what each formula cell displays:

    ABCD
    1ItemQtyUnit costCost
    2Ingredients/batch46.50=B2*C2
    3Boxes300.40=B3*C3
    4Stall fee125.00=B4*C4
    5Total=SUM(D2:D4)

  2. The stall sells cupcakes at 2.50$: revenue cell =120*2.5 and profit =revenue-total. Find both values.

  3. Using your Q1–2 model, what happens to profit if the stall fee rises to 40$? Answer without rebuilding — reason about which cells change.

  4. Write the spreadsheet formula for: (a) of cell B6 (b) the mean of A1 to A10 (c) cell B2 marked up by .

  5. A sheet shows =D2+D3+D4 under a column where a new row was inserted at row 3. Explain the danger.

  6. Reasoning. “The spreadsheet says so” — why is this not a sufficient check of an answer?

  7. Challenge. Design on paper a sheet for Lesson 79’s laptop comparison (three stores, discounts and GST), with one formula per final price, so that changing any sticker price updates its final price automatically.

Answers: Q1 — D2 , D3 , D4 , total 63$300$237$78$222$15$15$. Q4 — (a) =0.08*B6 (b) =SUM(A1:A10)/10 (or =AVERAGE(A1:A10)) (c) =B2*1.6. Q5 — the new row’s value is silently excluded from the total. Q6 — the sheet faithfully computes whatever formula was typed, including a wrong one; only an independent estimate or by-hand check can catch a mis-specified model. Q7 — e.g. =1249*0.85, =1180*0.9, =999*1.1, each referencing an input cell for the sticker price.

Graph View

Backlinks