Goal Seek Simulations
When a desired statistic value for an output is known, but the input value needed to obtain that value is not, the Goal Seek feature can be used to determine the necessary input values. The Goal Seek command of the Simulate menu will run an analysis of how to achieve the value for a simulated statistic of an output by adjusting the value of another cell. The desired statistic could be the mean, the standard deviation, a specific percentile, or others. The setup of an @RISK Goal Seek is very similar to Excel’s Goal Seek; they share the same desired outcome. However, unlike Excel’s Goal Seek, @RISK's Goal Seek utilizes multiple simulations to find the adjustable cell value that achieves the specified target.
The changing cell can be any cell in an Excel workbook - but the changing cell should never be an @RISK input distribution. This is due to the relationship between the changing cell and the output, and the results of running a Goal Seek analysis; the changing cell should be a standard value, not a variable one (i.e. an input distribution), as the results of a Goal Seek will set the changing cell to a single value (overwriting an input distribution function if one is selected).
An output is any cell that contains a RiskOutput function; if the selected target cell does not include a RiskOutput function, one will be added. The changing cell should be a precedent (meaning its value should in some way affect the output) of the target cell. During the Goal Seek simulation, @RISK varies the value in the changing cell and runs a full simulation to sample the possible output results. This process is repeated until the target statistic for the output equals, or approaches, the desired value.
To run a Goal Seek analysis, select Goal Seek from the Simulate menu, configure the goals and inputs (see below), and click Analyze.
Figure 1 - Goal Seek Window
The Goal Seek window (Figure 1, right) contains the following configurations:
- Goal - settings for the desired output goal.
-
- Cell - A cell reference to the output cell. If the cell does not contain a RiskOutput function, designating it as an @RISK Output, a prompt will open to add one.
-
Use the Model button (
) to open a list of outputs in the current model and quickly select one! - Statistic - The output statistic to monitor for convergence to the target value.
- Value - The desired value for the selected output statistic.
- By Changing