← Back to Curriculum
🧮
Year 9 • Lesson 5

Spreadsheet Modelling

What-if analysis and absolute references

Spreadsheets aren't for storing numbers — they're for answering "what if?"

🎯 Learning Objectives

  • ✓Use absolute cell references in formulas to build scalable models
  • ✓Create a what-if model with adjustable input cells
  • ✓Use IF statements to add conditional logic to a spreadsheet

📚 Key Vocabulary

Absolute reference
A cell address that doesn't change when copied — uses $ sign (e.g. $B$2)
Relative reference
A cell address that adjusts automatically when the formula is copied
IF statement
=IF(condition, value_if_true, value_if_false)
Input cell
A cell users change to test different scenarios in a model
What-if analysis
Changing input values to see how outputs change

⏱️ Lesson Timing (50 mins)

🪝
Hook
5 mins

Live demo: teacher breaks a spreadsheet by copying a formula that uses relative references when it should use absolute. "Has this ever happened to you? Here's why and how to fix it."

👨‍🏫
Teach
12 mins
  • •Relative vs. absolute references: when to use each and why
  • •The $ sign: $B2 (fixed column), B$2 (fixed row), $B$2 (fully fixed)
  • •Building a model with input cells: highlight in yellow, don't hard-code values
  • •IF statements: =IF(B3>50, "Pass", "Fail")
  • •Nested IF for multiple conditions
  • •IFERROR: handling mistakes gracefully
🛠️
Create
38 mins

Build a "Real World Model" — students choose from: (A) School Tuck Shop Profit Calculator: items, cost price, sell price, profit per item, total profit with a target profit cell (absolute ref). (B) Savings Goal Tracker: monthly income, savings %, months to reach goal (absolute ref for percentage). (C) Sports League Table: wins/draws/losses → points, conditional format top 3. Full IF statements and absolute references must be demonstrated.

Tools:
Excel Online
📢
Share
5 mins

Demo challenge: teacher changes one input cell in each student's model — does it update correctly everywhere? Instant quality check.

🔧 Tools & Resources

Excel Online
✅ Free
https://office.com

🤖 AI Angle

Use AI to explain a formula you don't understand — but test it before trusting it. AI often hallucinates Excel Online formula syntax.

📝 Evidence of Learning

Completed real-world model with absolute references, IF statements and input cells

📚 Homework

Build a personal monthly budget using what you learned. Include absolute references for tax rate or savings percentage. Screenshot and save to portfolio.

⏱️ 25 mins

♿ Differentiation

Support (for students who need help)

Skeleton model with structure ready — students complete the formulas

Extension (for advanced learners)

Research VLOOKUP or XLOOKUP and use it to look up values in your model from a separate table.

← Back to All Lessons📝 Take the Quiz