Find the required numbers to achieve a target value.
Excel Goal Seek Explained in Simple Steps
In cases where you have a target value that is derived from a formula and you need to find the required numbers to achieve it, Excel’s Goal Seek feature will come in handy.
An example of this would be if you want to know how many units need to be sold to achieve the target revenue.
There are many ways to approach this.
One would be to manually do a trial and error to find a number that gave you a revenue close to the target.
Another would be to do a back calculation and divide the target by the price per unit.
A third option is to use Excel’s Goal Seek function.
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.
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.
If 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.
The new Excel Dashboards course is here!
Visually Effective Excel Dashboards for your reports.
Redeem your coupon for 75% off below.