GoalPredict which cells change when one value changes, and work out the new results by hand.
New words
input cell · a cell with a value typed in, such as a cost or a number solddependent cell · a cell whose formula uses another cell, so it changes when that cell changescell reference · the name of a cell, such as C3: column letter then row number
Explanation
To answer a what-if question, first find the cell to change. This is an input cell. Then find the cells that depend on it. A formula depends on every cell reference it contains. For example, =(C3-B3)*D3 depends on C3, B3 and D3. If you change B3, the profit in E3 changes, and so does the total in E5, because that formula uses E3. Cells that do not use B3 stay the same. To work out the new answers by hand, replace the old value in the formula and calculate again. Then write the old and new values in a table, with the difference. A good habit is to predict the result before you work it out.
Change this cell
These cells change
These stay the same
B3 (badge cost)
E3, E5
E2, E4
D2 (bookmarks sold)
E2, E5
E3, E4
C4 (card price)
E4, E5
E2, E3
Worked examples
B2fx0.25
A
B
C
D
E
1
Item
Cost ($)
Price ($)
Sold
Profit ($)
2
Bookmark
0.25
0.6
30
10.5
3
Badge
0.5
1
20
10
4
Card
0.3
0.9
10
6
5
Total
26.5
What if the bookmark cost rose from 0.20 to 0.25? The formula (C2 − B2) × D2 becomes (0.60 − 0.25) × 30 = 10.5. The total falls from 28 to 26.5. The other items do not change.
Cell
Old value
New value
Change
D4 (cards sold)
10
20
+10
E4 (card profit)
6
12
+6
E5 (total profit)
28
34
+6
What if 20 cards were sold instead of 10? Only D4 changes. The card profit is 0.60 × 20 = 12, so the total is 12 + 10 + 12 = 34.
Before
After
Badge cost (B3)
0.50
0.60
Badge profit (E3)
10
8
Total profit (E5)
28
26
A cost can make the profit smaller. What if the badge cost rose to 0.60? The badge profit is (1.00 − 0.60) × 20 = 8, and the total is 26.
Class activity
What-if cards (no computer). Draw the stall table on a large sheet of paper. Make cards with changes, such as “bookmark price becomes 0.80”, “badge cost becomes 0.40” and “sell 40 cards”. Draw a card, apply that single change to a fresh copy of the table, and work out the new profit for each row and the total. Record the old total, the new total and the difference. Which card gave the biggest increase? Which gave a decrease? If you have a computer or tablet, ask an adult to help you check one of your answers in a spreadsheet.
Practice
1
List the cells that change when the badge price in C3 changes. Which cells stay the same?
E3 changes, because its formula uses C3, and E5 changes, because it adds E3. E2 and E4 stay the same.
2
What if the bookmark price rose from 0.60 to 0.70? Work out the new bookmark profit and total.
C2fx0.7
A
B
C
D
E
1
Item
Cost ($)
Price ($)
Sold
Profit ($)
2
Bookmark
0.2
0.7
30
?
3
Badge
0.5
1
20
10
4
Card
0.3
0.9
10
6
5
Total
?
Bookmark profit = (0.70 − 0.20) × 30 = 15. The total = 15 + 10 + 6 = 31.
3
A club leader asks: “What if we sell 10 fewer badges?” Work out the answer.
Badge sales become 10. Badge profit = 0.50 × 10 = 5. The total = 12 + 5 + 6 = 23, so the total goes down by 5.
4
A pupil says, “If I change the price, the number sold stays the same, so the model is perfect.” What does the model not show?
The model does not show that people may buy fewer items when the price goes up. The spreadsheet only recalculates the formulas, so the person using it must think about what the model leaves out.