Powerful Sensitivity Tables

In my previous post, I showed you how to do sensitivity analysis with Excel’s data tables. However the functionality allow us to do sensitivity on 2 variables only. Today I’m following up with a way to overcome this limitation.

As an example, an investment might be sensitive to many variables (e.g. lauch dates, set-up costs, marginal costs, sales, etc…). These can be summarized in scenarios like in the following table.

The last column in the table is the active scenario. It includes an in-cell drop down of the different scenario names and formulas that adjust automatically with the chosen scenario. The formula in H9 looks like this

=INDEX(D9:F9,1,MATCH($H$6,$D$6:$F$6,0))

• D9:F9 is the range of possible values (same row)
• $H$6 is the cell with the active scenario name (i.e. the one with the in-cell drop-down)
• $D$6:$F$6 is the range of the different scenario names (top header row)

Now I only have to build a one-variable sensitivity table with the variables like what you see on the next picture

Sensitivity table layout

Now, follow the steps detailed in the “One Variable Sensitivity Table – Data in Rows” section of my previous post

Here’s the result you should get. Notice that the formula column has been hidden.

Sensitivity table result

You can download the file used in the example here. (file size: 232 KB)