Sensitivity Analysis Excel: How to Set It Up [Tutorial Video] (17:58)

Sensitivity analysis in Excel lets you vary the assumptions in a model and look at the output under a range of different outcomes.

All investing is probabilistic because you can’t predict exactly what will happen 5, 10, or 15 years into the future – but you can come up with a reasonable set of potential scenarios.

For example, if a company you’re analyzing exceeds growth expectations and grows at 15% per year rather than 5-10%, that might be one scenario.

If it grows in-line with expectations, that could be another scenario. And if it declines or grows at a negative rate, that could be a third scenario.

You can use sensitivity analysis to look at how this company’s valuation changes as you move from one scenario to the next.

Sensitivity Analysis Excel: Data Tables and How to Build Them

Internally in Excel, sensitivity analyses are known as “data tables,” and you can access them in the ribbon menu under the Data tab and “What-If Analysis”:

In PC/Windows Excel, the shortcut is Alt, D, T or Alt, A, W, T (there is no shortcut in Mac Excel):

A properly set-up and formatted sensitivity table looks like this (taken from the Walmart DCF, where we vary the Discount Rate and Terminal Growth Rate to assess the company’s implied value):

To create this table, use the following steps:

Sensitivity Tables, Part 1: Building an Appropriate Financial Model

Before you do anything else, you must have a financial model or other analysis where several key inputs or assumptions directly affect the output.

For example, in our setup for this Walmart DCF, it’s easy to see that assumptions such as the Discount Rate and the Terminal Multiple affect the company’s implied share price.

The Terminal Multiple affects its Terminal Value, and then the Discount Rate affects the Present Value (PV) of the Terminal Value and the PV of the Free Cash Flows in the projection period, and those flow into the Implied Enterprise Value and then the Implied Equity Value and Share Price:

If you do not have a setup like this, where a few key cells affect the output of your model, sensitivity analyses will not work.

Sensitivity Tables, Part 2: Formatting

Next, start by formatting the cells the way you want. We usually pick blue colors for the background/fill of the outer cells, with white font color on top, and then a standard white background with black font color for the middle area:

This is just formatting, so you should look at our tutorial on how to color code in Excel for more.

Sensitivity Tables, Part 3: Enter the Row and Column Numbers You Want to Sensitize

This step is very important: when you enter the numbers in the row and column of the table that you want to sensitize, you cannot link them directly to anything in the model!

So, you must hard-code all these numbers in Excel, or you must start the row and column with a hard-coded number and then add or subtract in each column or row after that.

Here they are for Walmart:

Sensitivity Tables, Part 4: Link to the Output Variable in the Top-Left Corner

Next, go to the top-left corner of the table – cell D102 here – and enter a direct link to the output variable that you want to display in the table.

In this case, it’s the company’s Implied Share Price under the Perpetuity Growth Rate method of calculating Terminal Value:

This is the only part of the sensitivity table that should be linked to something in the model.

Sensitivity Tables, Part 5: Select the Entire Range and Create the Table

Now, select the entire range and go to Alt, D, T or Data, What-If Analysis, and Data Table, and enter the row and column input cells (the Terminal Growth Rate and Discount Rate here):

If the table does not refresh right away, press F9 to force a spreadsheet update and see the results.

Sensitivity Tables, Part 6: Check Your Work

The results should look like this:

You should check your work by reviewing the table and looking at the following points:

-Do the numbers change in each cell? If not, you’re doing something wrong. You should not see rows or columns where the output is the same.

-In valuation tables, does the company’s implied value increase as its revenue, revenue growth, or margins increase? Higher numbers should mean a higher valuation.

-As the Discount Rate increases, the company’s implied value should decrease.

The list goes on: make sure you understand how each assumption should affect the output and then see if it works that way in your table.

Sensitivity Analysis Excel: Key Requirements to Set Up the Tables

To recap and summarize this article, here are the key requirements for Excel-based sensitivity analysis:

1) The input variables and output must be on the same spreadsheet as the table. You cannot use assumptions or drivers from other sheets, such as the 3-statement model, in this table.

2) The numbers in the input row and column cannot be linked to or from anything that’s in the model. Start each input row or column with a hard-coded number and then hard-code the rest or make them change by simple percentages or numbers.

3) The row and column inputs and the output must be related in some way. If the inputs do not affect the output, the table will show no changes as you vary the numbers.

4) Set “Workbook Calculation” in Options or Preferences to “Automatic except for data tables” or your spreadsheet will slow down, especially with many tables. You can then press F9 to refresh or update the tables.

5) Enter a direct link to the output you want to sensitize in the top-left-hand corner of the table. And then, select everything and go through the steps shown above. “Row Input Cell” should be a direct link for the input in the top row, and “Column Input Cell” should be a direct link for the input in the left column.

6) You cannot modify individual cells in the table once it has been created. If you want to change something or select different inputs or outputs, you must delete and re-enter the entire table.

You will see sensitivity tables in almost every financial model of intermediate complexity and beyond, so you must know how to use them.

They aren’t that difficult, but many students make mistakes with the points above.