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.
Typed values: cost, price, number soldare used by→Formulas in the other cellsgive→Results: profit and total
Worked examples
E5fx=SUM(E2:E4)
A
B
C
D
E
1
Item
Cost ($)
Price ($)
Sold
Profit ($)
2
Bookmark
0.2
0.6
30
12
3
Badge
0.5
1
20
10
4
Card
0.3
0.9
10
6
5
Total
28
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
A
B
C
D
E
1
Item
Cost ($)
Price ($)
Sold
Profit ($)
2
Bookmark
0.2
0.6
30
12
3
Badge
0.5
1.2
20
14
4
Card
0.3
0.9
10
6
5
Total
32
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.
Before
After
Bookmarks sold (D2)
30
40
Bookmark profit (E2)
12
16
Total profit (E5)
28
32
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
A
B
C
D
E
1
Item
Cost ($)
Price ($)
Sold
Profit ($)
2
Bookmark
0.2
0.6
30
12
3
Badge
0.5
1
20
10
4
Card
0.3
1.1
10
?
5
Total
?
Card profit = (1.10 − 0.30) × 10 = 8. The total = 12 + 10 + 8 = 30.
2
What if 50 badges were sold instead of 20? Which cells change, and what is the new total?
The badge profit E3 changes to 0.50 × 50 = 25, and the total E5 changes to 12 + 25 + 6 = 43. The cells for the other items stay the same.
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?
With two changes you cannot tell which one caused the effect. One value should change at a time, so the effect can be linked to its cause.
4
What does a formula do that a typed number does not do?
A formula works out its value from other cells, so it updates by itself when those cells change. A typed number stays the same until someone changes it.