Guidelines for doing an Excel sensitivity analysis
The Excel sensitivity analysis is used to calculate possible scenarios from which comparisons can be made. The Excel sensitivity analysis thus helps to identify what the effects on a model will be if certain factors change.
The following guidelines can be used to do an Excel sensitivity analysis:
- Click on tools in the menu.
- Select scenarios.
- Select the add option.
In the add scenario window do the following:
- Provide a scenario name to help distinguish one scenario from another. You can for instance, name it worst case and most optimistic.
- Select the cells that may change for a specific model.
- Type the cell address in the following window and press the ok option.
In the value dialog box for the scenarios do the following:
- Enter the value for each of the changing cells and once done for all press the ok button.
- This will return you to the manager dialog box.
- Press show to have the values re-calculated.
Create a summary table for all the scenarios entered. To do this you need to select the summary option in the scenario box and then select the relevant report type. You will also need to identify the results cell and then press the ok option.
We provide tips on a wide range of spreadsheet features. If you want to learn more about the Excel sensitivity analysis browse through our training category.