Creating Sensitivity Charts

  To create a sensitivity chart:

  1. Close any spreadsheets that are currently open.

  2. Open the spreadsheet to analyze (or restore results).

  3. Select Run, then Run Preferences, and then Options. Run Preferences button

  4. Confirm that Store assumption values for sensitivity analysis is selected and click OK.

  5. Run a simulation (not necessary for stored results).

  6. Select Analyze, and then Sensitivity Charts. Sensitivity Charts button

    (In Microsoft Excel 2007 or later, select Analyze, then View Charts, and then Sensitivity Charts.)

  7. In the Sensitivity Charts dialog, click the New button.

  8. In the Choose Forecast dialog, select the forecast to include in the chart.

  9. Click OK to create a new sensitivity chart (Figure 37, Sensitivity Chart for the Selected Forecast).

    Note:

    The illustrated chart has a transparency effect applied using the chart preferences to make sensitivity values easier to read (Setting Special Chart Effects).

    Figure 37. Sensitivity Chart for the Selected Forecast

    This figure displays a sensitivity chart, showing the risk assessment for several assumptions.

    The assumptions are listed beside the bar chart, starting with the assumption with the highest sensitivity. If necessary, use the scroll bar to view the entire bar chart. You can drag the edges of the chart to resize it — make it narrower, wider, taller, or shorter. This often changes the tick labels along the top of the chart.

    Note:

    If you try to create a sensitivity chart but Store assumption values forsensitivity analysis is not selected in the Run Preferences dialog, select it, and then reset the simulation and run it again.

One or two assumptions typically have a dominant effect on the uncertainty of a forecast. In Figure 37, Sensitivity Chart for the Selected Forecast, the first assumption accounts for approximately 65% of the variance in forecast values and can be considered the most important assumption in the model. A researcher running this model would want to investigate this assumption further in the hopes of reducing its uncertainty and, therefore, its effect on the target forecast. The last assumption contributes the least to forecast variance (about 2%). This assumption has such a small effect, it could be ignored or altogether eliminated by clearing it from the spreadsheet.