GoalSay what goal seek does and find the answer by working backwards with inverse operations.
New words
goal seek · a spreadsheet tool that finds the value an input cell needs so that another cell reaches a goalset cell · the cell that holds the result you want to reachinverse operation · an operation that undoes another, such as dividing to undo multiplying
Explanation
Normal what-if analysis starts with a change and finds the result. Goal seek works the other way. You pick a target, for example a badge profit of 15, and ask which input value will give it. The goal seek tool needs three things: the set cell, which holds the result, the value you want it to reach, and the cell it is allowed to change. Without the tool, you can work backwards with inverse operations. If profit = 0.50 × sold and the goal is 15, divide: sold = 15 ÷ 0.50 = 30. Always check by putting the answer back into the formula. Some goals cannot be reached exactly, for example when you need part of an item. Then think about what makes sense.
Goal seek asks for
Meaning
Set cell
the cell that holds the result you want
To value
the target number for that cell
By changing cell
the input cell that is allowed to change
Worked examples
Set cell
To value
By changing cell
E3
15
D3
Goal: a badge profit of 15. The formula is (1.00 − 0.50) × D3 = 15, which is 0.50 × D3 = 15. Divide both sides by 0.50 to get D3 = 30. Check: 0.50 × 30 = 15.
Set cell
To value
By changing cell
E5
40
D2
Goal: a total profit of 40, by changing the number of bookmarks sold. The badge and card profits are 10 and 6, so the bookmarks must give 40 − 16 = 24. Each bookmark gives 0.40, so D2 = 24 ÷ 0.40 = 60. Check: 0.40 × 60 = 24, and 24 + 10 + 6 = 40.
Set cell
To value
By changing cell
E3
15
B3
Goal: a badge profit of 15, by changing the cost cell B3 instead. The formula is (1.00 − B3) × 20 = 15, so 1.00 − B3 = 0.75, and B3 = 0.25. Check: (1.00 − 0.25) × 20 = 15. Which cell you change decides the answer.
Class activity
Backwards puzzles (no computer). Use the stall table. On cards write goals such as “card profit 12 by changing D4” and “bookmark profit 20 by changing D2”. For each card, write the three goal seek items (set cell, to value, by changing cell) and then solve with an inverse operation. Check each answer by putting it back into the formula. Then find a goal that gives a part of an item, for example a card profit of 5, and decide what number of cards makes sense. If you have a computer or tablet, ask an adult to show you a goal seek tool.
Practice
1
Using the stall, find the number of cards sold (D4) that gives a card profit of 12. Show the check.
Fill in the goal seek items for this goal: “I want a bookmark profit of 20 by changing the number of bookmarks sold.” Then find the answer.
Set cell
To value
By changing cell
?
?
?
Set cell E2, to value 20, by changing cell D2. Bookmark profit = 0.40 × D2, so D2 = 20 ÷ 0.40 = 50. Check: 0.40 × 50 = 20.
3
The goal is a card profit of 5, by changing the number of cards sold. What does the inverse operation give, and what whole number of cards would you choose?
D4 = 5 ÷ 0.60 = 8.33…, which is not a whole number. 8 cards give a profit of 4.80, which is less than 5. 9 cards give 5.40, which reaches the goal. So 9 cards.
4
A pupil says goal seek always finds the right answer for the real world. Do you agree?
No. Goal seek only works with the numbers and formulas in the model. If the model leaves something out, such as people buying fewer items at a higher price, the answer may not be right in real life.