Sensitivity Analysis Excel: How to Create Sensitivity Tables in Excel (16:16)

Sensitivity Analysis in Excel lets you vary 1 – 2 assumptions in a model and see how a 3rd variable or output changes in response.

For example, what happens to a company’s implied value if its revenue grows at 10% rather than 5%? Or what if its profit margins decline from 20% to 15?

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

Therefore, sensitivity tables are critical in making investment decisions and advising clients.

Internally in Excel, sensitivity tables are known as “data tables,” and you can access them in the ribbon menu under the Data tab –> “What-If Analysis.” In PC Excel, the shortcut is Alt, D, T or Alt, A, W, T:

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):

Creating a sensitivity analysis is simple, but significant setup is required, and most people get it wrong because they do not prepare their models properly.

Therefore, this tutorial will present you with sample files and then explain the requirements and setup. You can also watch the video version above if you learn better by watching.

Video Table of Contents:

• 2:05: Inputs and Outputs
• 4:43: Model Tests
• 5:39: Row and Column Inputs
• 7:49: Output Direct Link
• 8:35: Workbook Calculations
• 9:04: Table Creation
• 9:57: Refresh the Table
• 10:40: Exercise: Create the First 2 Tables
• 14:20: Recap and Summary

Files & Resources:

• Sensitivity Tables – “Before” File (XL) | “After” File (XL)
• Sensitivity Tables – Presentation Slides (PDF)
• Sensitivity Tables – Video Transcript (PDF)

Sensitivity Analysis Excel: Requirements and Setup

Before you begin creating any sensitivity in Excel, please check the following points:

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.

In the example above, the Discount Rate and Terminal FCF Growth Rate cells are both directly on the same sheet as this table, so this setup will work.

If you want to use the Scenario (which is on a different sheet), you must CUT and paste the exact input box from the main sheet into this one and then set up Data Validation for the drop-down menu again.

2) Your basic model setup must still work. For example, before even creating the table above, change the Discount Rate and Terminal FCF Growth Rate, and make sure the Implied Share Price output in a single cell still works. If it does not work, you have a model problem that must be fixed first.

Once you’ve verified those bits, you can move on to the table creation process:

Sensitivity Analysis Excel: How to Enter the Numbers and Create the Table

3) Pick the row and column inputs and ranges, and ideally, hard-code all the numbers. For example, in the 4.0% to 6.0% range for the Discount Rates above, these numbers cannot be linked to or from anything in the model.

They should all be hard-coded or created with the Alt, E, I, S shortcut (on the Mac, go to Home, the Editing tab, and “Fill” under the “AutoSum” label):

4) Enter a direct link to the output you want to sensitize in the top-left-hand corner of the table. You can also anchor this if you’re planning to reuse the table elsewhere. In this case, it’s the “Implied Share Price” in cell N39 of the model.

5) Set “Workbook Calculation” in Options to “Partial” or “Automatic except for data tables,” or your spreadsheet will slow down, especially with many tables. You can then press F9 or Ctrl + S to create or refresh the tables:

6) Select the whole area and create the table. Here’s an example from this Walmart model (the “Row Input Cell” should be the Terminal FCF Growth Rate cell, and the “Column Input Cell” should be the Discount Rate cell):

You can use the Alt, D, T shortcut in PC Excel to do this; on the Mac, go to Data –> What If Analysis –> Data Table in the ribbon menu, similar to the image above.

7) Press Shift + F9, F9, or Ctrl + S to update the table. If you use these commands to refresh the calculations or save the file, the table will update and show all the numbers:

Sensitivity Analysis Excel: Interpretation

So, how can you interpret the specific tables used here for Walmart’s valuation?

The main takeaway is that, at the time of this valuation, Walmart seemed “appropriately valued.”

Its share price at the time was in the middle of most of these tables, indicating that it was neither overvalued nor undervalued.

Of course, we mostly used “market consensus views” to value the company, so this is unsurprising.

If we used assumptions that differed significantly from the Wall Street consensus view about revenue growth, margins, and cash flows, we would have seen a bigger difference here.

Sensitivity Analysis Excel: Limitations

Sensitivity tables in Excel are quite powerful, but they also have some limitations.

For one thing, they tend to be very “finicky” or “picky” with your Excel files and model setup; sometimes, simple formatting changes or improperly linked formulas will cause cascading errors that mess up everything.

Another issue is that if you want to change something in the table, you often have to recreate it from scratch.

It varies based on what this “something” is; for example, you can change the numerical ranges in the top row and left column, and the table will normally recalculate correctly.

But if you pick different variables to sensitize, or you pick a different output cell, you will have to enter the table all over again and update it.

Finally, sensitivity tables can be used to vary 1 assumption or 2 assumptions and see how the output of a formula changes, but you cannot make “3-dimensional” or “4-dimensional” tables that vary 3, 4, or more assumptions at once.

Similar to scenarios and other probability simulations, sensitivity analysis in Excel is simply a tool, and it’s up to you to use it properly.

It can’t give you the answers to life, love, and the universe, but it can at least tell you if a company’s valuation looks reasonable.