AdvertisementYour brand could be hereYour brand hereOn every Darslab page · students, parents and teachersinfo@darslab.comAsk for pricesPricesinfo@darslab.com
☰ Year plan · Computing

What if we changed it?

Week 18 · 2.1 Modelling · 3 lessons
1

What if…?

Computing 8: Topic 2.1, pp. 197–211
GoalDescribe a spreadsheet model and use what-if analysis by changing one value and seeing what else changes.
New words
spreadsheet model · a spreadsheet whose formulas copy how a real situation workswhat-if analysis · changing a value and watching how the other values changeformula · a calculation in a cell that uses other cells, so it updates when they change
Explanation

A spreadsheet can be a model of a real situation, such as a school stall. Some cells hold values you type, like the cost and the price. Other cells hold formulas, which calculate from those values. If a typed value changes, every formula that uses it recalculates at once. This is what-if analysis. You ask a question such as “What if the badge price was higher?”, change that one cell and read the new results. It is cheap and safe, because the real stall is not touched. The answers are only as good as the model. If the numbers or the formulas are wrong, the results will be wrong too. Change one value at a time, so you know which change caused which effect.

Worked examples
E5fx=SUM(E2:E4)
ABCDE
1ItemCost ($)Price ($)SoldProfit ($)
2Bookmark0.20.63012
3Badge0.512010
4Card0.30.9106
5Total28
A fundraising stall for a school club. Profit for each item is (price − cost) × number sold, so E2 holds =(C2-B2)*D2. The total profit in E5 is the sum of E2 to E4. Right now it is 28.
C3fx1.2
ABCDE
1ItemCost ($)Price ($)SoldProfit ($)
2Bookmark0.20.63012
3Badge0.51.22014
4Card0.30.9106
5Total32
What if the badge price rose from 1.00 to 1.20? Change one cell, C3. The badge profit becomes (1.20 − 0.50) × 20 = 14, and the total becomes 32. Only the formulas that use C3 changed: E3 and E5.
BeforeAfter
Bookmarks sold (D2)3040
Bookmark profit (E2)1216
Total profit (E5)2832
What if 40 bookmarks were sold instead of 30? Change D2. The bookmark profit becomes 0.40 × 40 = 16, and the total becomes 32.
Class activity

Paper spreadsheet (no computer). Draw a grid with columns A to E and rows 1 to 5, and write the stall table into it. Write the profit formula under the table, in your own words. Your partner asks a what-if question, such as “What if the card costs 0.40 to make?”. Change that one cell on a copy, recalculate the cells that depend on it, and say how the total changed. Do three questions, then swap. Why do you change only one value at a time? If you have a computer or tablet, ask an adult to help you type the table into a spreadsheet.

Practice
1
Using the stall, what if the card price rose from 0.90 to 1.10? Work out the new card profit and the new total.
C4fx1.1
ABCDE
1ItemCost ($)Price ($)SoldProfit ($)
2Bookmark0.20.63012
3Badge0.512010
4Card0.31.110?
5Total?
2
What if 50 badges were sold instead of 20? Which cells change, and what is the new total?
3
A pupil changes the bookmark cost and the badge price in one go, and the total goes up by 1. Why is this a poor way to do what-if analysis?
4
What does a formula do that a typed number does not do?