### Excellence with Excel

Master Excel navigation, formulas, formatting, graphs, data analysis, and VBA for workflow automation via case studies of Walmart and a customer due diligence file.

Learn moreIn this lesson, you’ll learn how to set up sensitivity tables in financial models, including the key requirements for inputs and outputs and the required steps, and you’ll get practice creating these tables in the Walmart valuation.

Sensitivity analysis in Excel

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.

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

Master Excel navigation, formulas, formatting, graphs, data analysis, and VBA for workflow automation via case studies of Walmart and a customer due diligence file.

Learn moreTo create this table, use the following steps:

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.

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.

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:

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.

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.

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.

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.

Brian DeChesare is the Founder of Mergers & Inquisitions and Breaking Into Wall Street. In his spare time, he enjoys lifting weights, running, traveling, obsessively watching TV shows, and defeating Sauron.