This tutorial will cover how to perform a one at a time (OFAT) sensitivity analysis. We will start with a deterministic spreadsheet model for calculating project net present value (NPV).
We want to look at the sensitivity to changes in revenue and expenses so we will vary each year's revenue and expenses by +/- 20%. To start, click the OFAT Analysis button on the ribbon.
The sensitivity analysis form will appear. We are starting a new sensitivity analysis so new worksheet is selected.
Next, the output cell is selected. The output cell in our case is where NPV is calculated in the model. Click the minimize button next to the output cell box and select the output cell in the model worksheet.
After the output cell is selected, click OK to return to the sensitivity analysis form.
The next step is to add input variables whose values will be varied to determine sensitivity. Click the Add Variable button and the add variable form will appear.
Enter a variable name in the text box. The variable cell is the cell address where the variable is located in the model worksheet. Click the minimize button next to the variable address box and select the variable cell.
After the cell is selected, click OK to return to the add variable form. Enter the minimum, base case, and maximum values that the variable can assume in the boxes as shown below.
When finished, click the Save button to return to the sensitivity analysis form. The variable should appear in the variables pane.
Repeat the add variable procedure for the remaining 11 variables and the sensitivity analysis form will now have 12 variables.
Variables can be edited or deleted by selecting a variable and clicking either the Edit Variable or Delete Variable button.
We are now ready to run the analysis. Click the Start button. The new worksheet (Sheet2) will be populated with the range of output values obtained by varying each variable while holding all others at the base case. A tornado chart will also appear to summarize results.
The tornado chart data can be sorted to put the variable with the most impact on output at the top of the chart. Click on the data bars of the chart and then click the down arrow in the last column of the table. Select sort largest to smallest.
You may have to recalculate the sheet if auto calculation is turned off.
The tornado chart is now rearranged with Revenue 4 at the top which is the highest impact variable.