Discovering Sensitivity Tables

Excel has a built-in functionality for sensitivity analysis, hidden under the unclear name of “Data tables” (“tables” in Excel 97-2003).

Two options are available in using these “Data tables”:

• Evaluate the impact of one variable on one or more resulting elements (with 2 possible layouts)
• Evaluate the impact of two variable on one resulting elements

In my next post, I’ll you show how to overcome the limit on the number of variables.

Let’s first look at the following simple model that calculates the net present value (NPV) and internal rate of return (IRR) of a stream of cash flows.

One Variable Sensitivity Table

Say we want to evaluate the impact of the growth rate on NPV and IRR. We could change the assumption directly in the input cell and note the results. This would have the serious drawback of not being adjusted automatically should we change another assumption (e.g. the cash flow in period 1).

Instead, we’ll build a table with different possible values that will automatically populate those results.

Layout 1: data in columns

The previous picture shows you the layout of the table. On the left, you enter the different values from which you’ll calculate sensitivity and on the right, the resulting formulas.

Let’s build a table that will demonstrate the impact of different growth rates on the NPV and IRR.

To get the sensitivity table

1. Fill in the data as layed out on the “Table Structure” picture.
   • On the left, enter different possible values for growth
   • On top, enter a simple link to the result you want to evaluate (e.g. =D24)
2. Select the table (green and yellow portions in the previous image)
3. In Excel 2003: Click the menu “Data \ Table”
   In Excel 2007: In the “Data” tab, click “What-If Analysis”, then “Data Table…”
4. Enter the location of the input cells in the “Column Input Cell” section.
5. Leave the other section empty and click “Ok”

This is the result you should get. The bold row is not part of the table feature, but I like to highlight what’s currently in the model.

Sensitivity tables with data in columns results

Layout 2: data in rows

This one is exactly the same as the previous example except that the formula and value table positions are switched.

To get the sensitivity table

1. Fill in the data as layed out on the “Table Structure” picture.
   • On the left, enter a simple link to the result you want to evaluate (e.g. =D24)
   • On top, enter different possible values for growth
2. Select the table (green and yellow portions in the previous image)
3. In Excel 2003: Click the menu “Data \ Table”
   In Excel 2007: In the “Data” tab, click “What-If Analysis”, then “Data Table…”
4. Enter the location of the input cells in the “Row Input Cell” section.
5. Leave the other section empty and click “Ok”

Sensitivity tables with data in columns results

Two Variables Sensitivity Table

Now we might want to see the cross sensitivity of two variables (e.g. growth and discount rate) on one particular result, NPV in our example. The next picture shows the layout needed to accomplish this task.

The previous picture shows you the layout of the table. On the left, you enter the different values from which you’ll calculate sensitivity and on the right, the resulting formulas.

To get the sensitivity table

1. Fill in the data as layed out on the “Table Structure” picture.
   • On the left, enter different possible values for growth
   • On top, enter different possible values for growth
   • On the top left corner, enter a simple link to the result you want to evaluate (e.g. =D24)
2. Select the table (green and yellow portions in the previous image)
3. In Excel 2003: Click the menu “Data \ Table”
   In Excel 2007: In the “Data” tab, click “What-If Analysis”, then “Data Table…”
4. Enter the location of the input cells in the “Column Input Cell” section. (This is for the variable on the left of your table)
5. Enter the location of the input cells in the “Row Input Cell” section. (This is for the variable on top of your table)

Sensitivity tables with two variables results

The grey cell contains the formula on which you perform your sensitivity analysis, or a simply a link to that formula.

One litttle constraint to remember…

The assumption cell (i.e. the column or row input cell in the dialog) has to be in same sheet as the sensitivity table.

To circumvent this limitation, add a new assumption cell in the same sheet as the sensitivity table and have your original assumption cell linked to it.

A note on calculation modes

The presence of many tables in a workbook can significantly increase recalculation time.

You can set the calculation mode to “Automatic except for data tables” to avoid slower recalculation time and press F9 when ready to recalculate the sensitivity table.