Sensitivity Table Excel: Pressure-Test Model Assumptions

Key Takeaways

• A sensitivity table Excel data table varies one or two model inputs across a grid and computes the output, typically implied share price or IRR, for every input combination without requiring manual recalculation.
• In a standard DCF, shifting WACC from 9 to 8 percent and terminal growth from 2.5 to 3.5 percent can move implied share price from 106 to 146, a 38 percent swing from two plausible assumption changes.
• The most common misuse is picking wide ranges that imply false precision; the table should span plus or minus 100 to 200 basis points around the base case, not extreme outliers that would not survive a committee review.
• Sensitivity tables reveal which input drives value most, focusing diligence where it has the highest payoff and exposing models that are dangerously leveraged to a single assumption.

What It Is

Sensitivity tables in Excel come from the Data > What-If Analysis > Data Table menu. They come in two flavors. A one-variable data table holds every other input in the model constant and varies a single input across a list, producing one output column per scenario. A two-variable data table varies two inputs against each other in a grid, computing the output for every combination.

In a discounted cash flow, the classic two-variable table sensitizes the weighted average cost of capital (WACC) against the terminal growth rate, with implied share price as the output. In an LBO, it is typically entry multiple against exit multiple, with internal rate of return (IRR) as the output. The structure is the same.

The Intuition

A model produces a single output number. That number is only as good as the assumptions behind it. If a DCF outputs a fair value of 100 per share, but changing the WACC by 50 basis points moves the number to 80, the single-point answer is misleading. A sensitivity table displays that uncertainty as a grid the reader can actually see.

The table also reveals which inputs matter. If moving revenue growth across a wide range barely changes the output while moving margin by a small amount moves it a lot, the model is really a margin story. Knowing which driver dominates focuses the diligence work where it pays off.

How It Works

For a one-variable data table, set up a list of input values in a column. In the cell one row up and one column to the right of the list, type an equals sign and link to the output cell (for example =SharePrice). Select the entire block. Go to Data > What-If Analysis > Data Table. Leave the row input cell blank; in the column input cell, point to the input driving the scenarios (e.g. WACC). Excel fills the column with the output for each input value.

For a two-variable data table, the list of first-input values runs across the top row, the list of second-input values runs down the left column, and the top-left cell holds the formula linking to the output. Select the full grid and use the same Data Table menu. This time supply both a row input cell and a column input cell.

Related tools serve different purposes. Scenario Manager (also under What-If Analysis) runs full scenarios that change many inputs at once (Base, Upside, Downside). A tornado chart takes a list of single-variable sensitivities and ranks them by absolute output impact, making the dominant drivers obvious at a glance.

Worked Example

A DCF outputs an implied share price of 100 at a WACC of 9 percent and a terminal growth rate of 2.5 percent. The analyst wants to see how robust the 100 value is to WACC and terminal growth assumptions.

The two-variable table might look like this (output = implied share price):

               Terminal growth
             1.5%   2.0%   2.5%   3.0%   3.5%
WACC  8.0%   112    118    126    135    146
      8.5%   104    109    115    122    130
      9.0%    96    100    106    112    119
      9.5%    89     93     98    103    109
     10.0%    83     86     90     95    101

The base case of 100 sits in the middle. A WACC 50 basis points higher brings the value to 98 at the same terminal growth, which is not a large move. A WACC 100 basis points lower and a terminal growth 100 basis points higher brings the value to 135, a much bigger move. The reader can see the asymmetry immediately. A single "implied price is 100" answer would hide it.

Common Mistakes

1. Wide ranges that imply false precision. A table that shows outputs from 40 to 300 across a wide WACC range looks impressive but tells the reader nothing useful. Pick ranges tight enough to reflect realistic uncertainty, typically plus or minus 100 to 200 basis points around the base case.

2. Sensitizing the wrong variables. A two-variable table is only useful if both inputs matter. Sensitizing revenue growth and SG&A growth when the DCF is really driven by WACC and terminal value is a waste of grid space.

3. Breaking the link from the table formula to the model. If the formula cell points at a stale cell or a hardcode, every entry in the grid will be identical (or wrong). Spot-check the base-case corner of the table against the model's native output.

4. Forgetting to recalculate after changing inputs. Data tables update on full recalc (F9 by default), not on every cell change. Turn manual calculation off only with care, and check that the grid reflects current model logic before sharing.

5. Reading the table as a probability distribution. A sensitivity grid shows what would happen if the input took that value. It does not say anything about how likely each value is. Presenting the extremes as "bull" and "bear" cases without reference to probability oversells the precision.

Frequently Asked Questions

Q: What is a sensitivity table in simple terms? A sensitivity table is an Excel data table that automatically recalculates a model output, such as implied share price, for every combination of two input assumptions you specify, displaying all results in a readable grid without manual toggling.

Q: How does a sensitivity table affect investment decisions? It replaces a single "fair value" number with a range of plausible outcomes, showing investors how much the conclusion depends on specific assumptions and which inputs they need to get right to feel confident in a position.

Q: What is a real-world example of a sensitivity table in use? A DCF base case shows $100 per share at a 9 percent WACC and 2.5 percent terminal growth. The two-variable table reveals that a WACC of 8 percent and terminal growth of 3 percent produce $126, while the opposite combination produces $89, a $37 range from two reasonable changes.

Q: How can investors use or avoid sensitivity table errors? Investors should confirm the formula cell in the top corner of the table is live-linked to the model output, not a hardcode. A table whose center cell does not match the model's base-case output has a broken link and every cell in the grid is wrong.

Q: How is a sensitivity table different from scenario analysis? A sensitivity table varies one or two inputs while holding everything else constant, isolating the effect of specific drivers. Scenario analysis changes many inputs simultaneously to reflect coherent economic conditions like a recession or a boom, producing a full alternative forecast rather than a ceteris paribus grid.

Sources

1. Wall Street Prep. "Sensitivity Analysis (What-If) | Excel Tutorial Lesson." https://www.wallstreetprep.com/knowledge/financial-modeling-techniques-sensitivity-what-if-analysis-2/
2. Financial Edge. "DCF – Sensitizing for Key Variables." https://www.fe.training/free-resources/valuation/dcf-sensitizing-for-key-variables/
3. Wall Street Mojo. "Sensitivity Analysis in Excel | One & Two Variable Data Table." https://www.wallstreetmojo.com/sensitivity-analysis-in-excel/
4. ExcelDemy. "One and Two Variables Sensitivity Analysis in Excel." https://www.exceldemy.com/sensitivity-analysis-in-excel-using-one-or-two-variables-data-table/

Disclaimer

This article is educational content only and is not financial advice. Nothing here is a recommendation to buy, sell, or hold any security. Consult a licensed advisor before making investment decisions.