Sensitivity table

A sensitivity table allows the run of a large number of scenarios by changing the values of up to 2 variables.

 

Excel modeling

 

1. Calculate the result you want to analyze (NPV, IRR, etc)

2. Prepare the table with the two variables that you want to have a sensitivity on (it is very important that the row and the column of the table never refer to the cells that are actually used in the model for the calculations, the cells must be independent, if this is not the case the table will not work)

3. The top right corner of the table must return the value that you wish to have the sensitivity on

4. Select to table

5. Go to Data / What-if Analysis / Data Table

6. In Row input cell, choose the variable that varies in the top row of your table

7. In Colum input cell, choose the variable that varies in the left column of your table

8. If the table shows only one value, press F9 to refresh, if this still does not work, check that the table never makes any reference to the input cells used by the model

 

In the example below, the user wants to analyze the change in NPV by modifying both the sale price of the product and the variable cost.

Financial Modeling Academy

  • LinkedIn App Icon
  • Wix Facebook page
  • Wix Twitter page