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 cannot be an @RISK input distribution or an Excel formula. 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. Click the Save and Exit button to save a configuration without running an analysis.

Figura 1 - Goal Seek Window

The Goal Seek window (Figure 1, right) consists of two tabs - the Basic Setup tab and Advanced Options tab. The Basic Setup tab contains the configurations required to run a Goal Seek analysis:

  • 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.
    • Statistic - The output statistic to monitor for convergence to the target value.
    • Target Value - The desired value for the selected output statistic.
  • Changing Value
    • Cell - Identifies the cell the Goal Seek analysis should change to achieve the target statistical value.

    • The output cell must be dependent on this changing cell (through precedence in Excel formulas). If it is not, Goal Seek will not be able to find a solution.

    • Minimum - Set the lowest allowed value for the cell being changed during the Goal Seek analysis.
    • Maximum - Set the highest allowed value for the cell being changed during the Goal Seek analysis.

See Goal Seek Advanced Options for information on additional configurations.