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:
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.
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).
Regarding the solution that GoalSeek finds:
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:
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.
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).