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
3

Work backwards: goal seek

Computing 8: Topic 2.1, pp. 197–211
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 forMeaning
Set cellthe cell that holds the result you want
To valuethe target number for that cell
By changing cellthe input cell that is allowed to change
Worked examples
Set cellTo valueBy changing cell
E315D3
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 cellTo valueBy changing cell
E540D2
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 cellTo valueBy changing cell
E315B3
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.
Set cellTo valueBy changing cell
E412D4
2
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 cellTo valueBy changing cell
???
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?
4
A pupil says goal seek always finds the right answer for the real world. Do you agree?