1.8 Using GoalSeek
Introduction: Goal Seek as a Reverse Sensitivity
Excel’s GoalSeek functionality will find the value that a particular model input would need to take in order that a calculated item in the model (a single output) would have a specified “target” value. (In this sense, it is a “reverse” sensitivity analysis, since when using Goal Seek the user specifies a value for the output, whereas when using sensitivity analysis, it is the input value that is specified). In general, the required value of the input cannot be directly calculated, but must be found by “trial and error” (more correctly: by using iterative search techniques). Of course, once this input value has been found, then the model’s output would be equal to the target value that the user has set.
The GoalSeek menu can be accessed using Data>What-If Analysis>Goal Seek:
Example
The functionality can be demonstrated using the dialog box that is presented then needs to be completed. In the case shown, we are aiming to make the value of cell C11 (the Profit) equal to 5000 by changing cell C3 (the Price).
When the OK button is pressed, GoalSeek uses an iterative search method (that is not visible to the user) to find an input value which meets those conditions. The solution will be shown in the Excel workbook:
If the OK button is pressed, the worksheet will use this new value (i.e. in this case, the Price is 10.875 and the Profit is 5000).
In that sense GoalSeek is like a “reversed” sensitivity analysis.
A frequent application of GoalSeek is to the case where the target for profit is zero: That is, one aims to find the value of some input (price, volume, cost…) so that the business will breakeven. This can help to give a decision-maker a simple reference point or base for further reflection.
Essential Tips for Efficient Usage
Note that when the GoalSeek dialog box is being completed, the target value needs to be typed in as hard-coded figure; it is not possible to use a cell reference. This is inconvenient if the target value is not a simple figure to input by typing.
It is generally better to have the target value input into a cell, and to subtract the value of this from the model’s output, to create a “difference” calculation. Then, one can apply GoalSeek so that this difference is required to be zero.
An example is shown in the image below, in which one desires to achieve a Profit of 5452. This has been input into cell C14, and the difference calculated in cell C15, which becomes the target cell with a value of zero.
The result would be as shown below.
The use of this “difference” approach is most efficient when the values of the target which are not simple to input, or where one may wish to run GoalSeek several times as the target value itself is altered.
(Note that – although we do not show it here – one could now re-run the one-way sensitivity analysis using the figure 11.44 as one of the values to use for the input – and the DataTable would then show the corresponding (target) value of 5452 for that case).
Further Comments (I): Solutions and Methods
Regarding the solution that GoalSeek finds:
- It is possible that the model is such that no solution exists. In this case, GoalSeek cannot find one!
- In some models, multiple solutions could exist. GoalSeek could find a solution, but in theory there could be several (for example, if the model’s output behaves as a U-curve as the input varies). One would not be made aware of additional solutions. However, if one suspects their presence, they can be searched for by using a range of values for the initial value of the input that is to be varied (such as a very large negative or large positive value) as the initial value is the starting point for the search process.
- The iterative method will stop when a “sufficiently” good answer has been found. The value may be accurate to a few decimal places but may not be exact. More accuracy can often be obtained by “magnifying” the difference (e.g. by multiplying the difference calculation by 10).
Further Comments (II): Using Constraints
The GoalSeek menu allows one only to set a target value or objective. GoalSeek cannot be used if one wishes to apply constraints. For example, in practice one may wish to:
- Find the value of an input that would achieve or maximise the target value (objective), whilst also respecting that the value(s) of other inputs (or of other calculations) are below or above some other values that one has specified.
- Allow for several input values to be varied whilst searching for a solution (i.e. find input combinations that achieve or maximise an objective), also with some forms of constraints on other calculations or values.
The Excel Solver add-in can be used to overcome the limitations highlighted by the last two items above. This is covered in later Levels.
Further Comments (III): Using GoalSeek within Macros
In Level VI, we discuss how to use Goal Seek within a VBA macro. This can be useful to run it repeatedly or to form a type of sensitivity analysis. For example, one may wish to find – for a set of the values of the Volume variable – to find the value of the Price that would make the business breakeven (i.e. that Profit be zero). This type of sensitivity analysis could not be done with a DataTable (since the model requires Goal Seek to be run after each change in the value of the Volume variable, and therefore the model is not “dynamic” in the sense discussed in the last section).