How the Goal Seek function is used
The Goal Seek function is found in the Data tab under Forecast group.
Click on What-if Analysis.
The Goal Seek window will ask you to define the cells you need to manipulate:
- Set cell: This refers to the cell that contains your target figure. In this example, cell B5 contains the Revenue.
- To value: This is the fixed target number. Since the target revenue it 2500, write 2500.
- By changing cell: This refers to the cell that you want to change in order to achieve the target. In this case, you want to figure out how many units need to be sold to achieve a revenue of 2500. Set it as $B$3.
After clicking OK, the Goal Seek Status window will indicate that a solution has been found.
Otherwise, it displays the closest value it has come up with.
For this example, in order to achieve a 2500 revenue, 658 units have to be sold.
The good thing about Goal Seek is that it doesn’t change the formulas that have been placed in the cells.
When you accept the results of the Goal Seek, all it does is to put that new number in the input field.
It doesn’t touch anything in your calculation field.
Featured Course
Black Belt Excel Package
Uses of Goal Seek
Its use is not limited to direct calculation purposes, but can be applied in cascading computations.
As another example, we have additional fields: Cost per unit, Total cost, and Income.
To give you an idea:
- Cost per unit: A fixed number indicating how much each unit costs.
- Costs: Total cost. This is derived by multiplying the number of units by the cost per unit.
Cell B9 = B3*B8- Income: Calculated field. This net income is obtained by subtracting the Total Cost from the Revenue.
Cell B11 = B5-B9If we want to figure out how many units need to be sold in order to achieve an Income of 2100, the Goal Seek function can be used as well.
To do this:
- Go to the Data tab
- Under the Forecast group, click on What-if Analysis.
- Select Goal Seek
- Set the parameters as follows:
- Set cell: Cell B11
- To value: 2,100
- By changing cell: $B$3
- Select OK.
The Goal Seek Status window will popup and indicate that it has found a solution.
The Units sold figure is now 913.
This means that 913 units have to be sold to achieve an income of 2100.
To accept the results, click on OK.
If Cancel is clicked, it disregards the result and reverts back to the original figures before the Goal Seek function was used.
Manipulating multiple cells using the Solver function
However, the Goal Seek window only allows you to change one parameter.
In cases where you want to manipulate multiple cells and constraints to get a certain result (such as finding both the units sold the unit price which should not exceed 4.00), the Solver function comes in handy.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
