Chapter 1: Using Sensitivity Analysis

9 Topics | 1 Assessment Test
Chapter 2: Forecasting Structures

8 Topics
Chapter 3: Modelling Excercise And Assessment Test: Forecasting Calculations (I)

1 Topic | 1 Assessment Test
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:

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.

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).

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.

This website uses cookies to improve your experience. We'll assume you accept this policy as long as you are using this websiteAcceptView Policy

Scroll to Top

Login

Accessing this course requires a login. Please enter your credentials below!