Sensitivity Analysis for DCF Valuation
Executive Summary
Key Takeaways
- ✓ Sensitivity analysis in a DCF should always test the discount rate and the terminal growth rate or exit multiple together, as a two-way table, not each in isolation.
- ✓ A one-way sensitivity table varies a single assumption; a two-way table varies two assumptions jointly and is the standard format for presenting DCF value sensitivity to an investment committee.
- ✓ The sensitivity table should be built using Excel's Data Table feature (or an equivalent), with the output cell referencing the model's live enterprise or equity value, not a hardcoded formula recreated inside the table.
- ✓ A sensitivity table's structural integrity — whether it reflects the model's current calculation state — is a distinct and equally important concern, addressed on the Sensitivity Table Integrity guide.
- ✓ Presenting a DCF conclusion without its underlying sensitivity table is materially incomplete, given how much total value concentrates in the discount rate and terminal value assumptions.
Institutional Definition¶
Sensitivity analysis for a DCF tests how the valuation output changes as key assumptions are varied, most importantly the discount rate and the terminal value driver, given their combined and disproportionate effect on total value. This guide addresses how to build sensitivity tables specific to a DCF's structure; the mechanics of the Data Table feature itself and the general concept of sensitivity analysis are covered on their respective glossary pages.
The Priority Variable Pair: WACC and Growth Rate¶
Because terminal value frequently represents 60–80% or more of total DCF value, and terminal value is driven jointly by the discount rate and the perpetuity growth rate (or exit multiple), this pair is the single highest-priority sensitivity test in any DCF. The two assumptions interact multiplicatively — a growth rate near the top of a defensible range paired with a discount rate near the bottom can produce an aggressive terminal value that neither assumption tested alone would reveal.
One-Way vs. Two-Way Sensitivity Tables¶
A one-way sensitivity table varies a single assumption across a defined range and reports the resulting output (enterprise value, equity value, or value per share) for each value. This is useful for isolating the effect of a single driver but does not capture how two assumptions interact.
A two-way sensitivity table varies two assumptions simultaneously, across a grid — one variable's range across the top row, the other's range down the left column — reporting the output for every combination. This is the standard, expected format for presenting WACC and growth rate (or exit multiple) sensitivity in a DCF.
Building a Two-Way Sensitivity Table¶
- Set up a grid: the discount rate range across the top row, the terminal growth rate (or exit multiple) range down the left column
- Place a formula in the top-left corner cell of the grid that references the model's live output cell (e.g., enterprise value or value per share) — not a hardcoded recreation of the DCF formula inside the table
- Select the full grid range, including the row and column headers
- Use Excel's Data > What-If Analysis > Data Table function, specifying the row input cell (the assumption varied across the top row) and the column input cell (the assumption varied down the left column)
- Excel recalculates the model for every combination and populates the grid
Interpreting the Output¶
The resulting grid should be read as a range of plausible outcomes, not a single point estimate. Highlighting the base case cell within the grid, and disclosing the assumption range tested (and why that range was chosen — typically ±1–2% around the central discount rate estimate, and a growth rate range bounded by long-run GDP or inflation expectations), turns the sensitivity table into a decision-useful disclosure rather than a technical appendix.
A Distinct Concern: Sensitivity Table Integrity¶
Building the sensitivity table correctly is only half the requirement — the table must also continue to reflect the model's current calculation state as the model is updated. If automatic calculation is disabled for data tables, or the table's results are converted to static values, the sensitivity output can silently become stale. This structural integrity risk, and how it is detected in a model audit, is addressed in full on Sensitivity Table Integrity.
Structural Audit Checks¶
| Check | What It Confirms |
|---|---|
| A two-way sensitivity table exists for WACC (or cost of equity) against growth rate or exit multiple | The disproportionate effect of the terminal value assumption pair is disclosed, not hidden |
| The table's corner-cell formula references the model's live output, not a separately recreated calculation | The table reflects the actual model, not an approximation of it |
| Automatic calculation is enabled for data tables, and results have not been converted to static values | The sensitivity output is not silently stale (see Sensitivity Table Integrity) |
| The tested range is disclosed and justified | The reader can assess whether the range itself is reasonable |
Common Errors¶
| Error | Description | Risk |
|---|---|---|
| Testing WACC and growth rate independently only | Two one-way tables instead of a joint two-way table | Understates how aggressive the combined assumption set actually is |
| Recreated formula inside the sensitivity table | Table's corner cell does not reference the model's actual live output | Sensitivity results may not match what the model itself would produce |
| No disclosed range rationale | Sensitivity range presented with no explanation of why those bounds were chosen | Reader cannot assess whether the tested range itself is reasonable |
| Sensitivity table not included at all | DCF conclusion presented as a single point estimate | Decision-makers cannot see how much the conclusion depends on the underlying assumptions |
Continue Reading¶
Prerequisites¶
- Discounted Cash Flow (DCF) Valuation — the parent pillar
- Sensitivity Analysis
Related Technical Guides¶
- Scenario Analysis for DCF Valuation
- Tornado Analysis for DCF Valuation
- Sensitivity Table Integrity in Financial Models
Related Checklists¶
How OXXON tests thisRun a free structural check with FMAE
Frequently Asked Questions
What variables should a DCF sensitivity table test?
The discount rate (WACC or cost of equity) and the terminal value driver (perpetuity growth rate or exit multiple) are the highest-priority pair, given their combined, disproportionate effect on total value. Revenue growth rate and margin assumptions are the next most commonly tested pair for the explicit forecast period.
What is the difference between a one-way and a two-way sensitivity table?
A one-way table varies a single input across a range and shows the resulting output for each value. A two-way table varies two inputs simultaneously, across a grid, showing the output for every combination — the standard format for DCF discount rate and growth rate sensitivity.
How do you build a two-way data table in Excel for a DCF?
Set up a grid with one variable's range across the top row and the other variable's range down the left column, with the top-left corner cell referencing the model's live output (e.g., enterprise value). Select the full range and use Data > What-If Analysis > Data Table, specifying the row input cell and column input cell that correspond to the two varied assumptions.
Why is testing WACC and growth rate together more informative than testing each separately?
Because the two assumptions interact multiplicatively in their effect on terminal value — a growth rate near the top of a defensible range combined with a discount rate near the bottom can produce an aggressive value that neither assumption alone, tested in isolation, would reveal.
How should a DCF sensitivity table be presented to an investment committee?
As a clearly labelled grid showing enterprise or equity value (or value per share) across a realistic range of WACC and terminal growth rate or exit multiple combinations, with the base case highlighted, so the committee can see the range of outcomes the underlying assumptions actually support.
Related Articles
Sensitivity Analysis
Sensitivity analysis is the quantitative assessment of how much a financial model's output changes when a single input variable is changed by a defined amount, while all other variables are held at their base case values. It measures the responsiveness — or sensitivity — of outputs to individual assumption changes. Sensitivity analysis is distinct from scenario analysis, which changes multiple assumptions simultaneously to reflect a coherent alternative state. Sensitivity analysis isolates the effect of individual variables; scenario analysis tests the combined effect of assumption sets.
Data Table
In Excel, a data table is a range of cells that performs a series of what-if calculations by substituting a set of input values into one or two designated cells and recording the resulting output from a specified formula. Data tables are the standard mechanism for producing sensitivity matrices in financial models. A one-variable data table varies one input and shows the output for each value; a two-variable data table varies two inputs simultaneously. Data table results are stored as array formulas using the TABLE function and update automatically when the model recalculates, unless the workbook's calculation mode excludes data tables from automatic recalculation.
Perpetuity Growth Rate
The perpetuity growth rate (also called the terminal growth rate or Gordon growth rate) is the assumed constant rate at which a business's free cash flow is expected to grow indefinitely beyond the explicit forecast period. It is the key assumption in the Gordon Growth Model method of calculating terminal value, and it must be strictly less than the discount rate for the perpetuity formula to produce a finite, meaningful value. Because no business can outgrow the broader economy forever, the perpetuity growth rate is conventionally capped at or near the long-run expected growth rate of GDP or inflation in the business's operating geography.
WACC (Weighted Average Cost of Capital)
WACC (Weighted Average Cost of Capital) is the rate of return that a company must earn on its existing assets to maintain the value of its equity and satisfy both its debt holders and equity investors. It is calculated as the weighted average of the after-tax cost of debt and the cost of equity, with the weights determined by the proportion of each in the total capital structure. WACC is used primarily as the discount rate in a discounted cash flow (DCF) valuation, where it converts projected free cash flows into present value. It is also used as a return hurdle: a project or investment is value-creating if its expected return exceeds the WACC.
Discounted Cash Flow (DCF) Valuation
Discounted cash flow (DCF) valuation values a business, project, or asset as the present value of the cash flows it is expected to generate in the future. It is the most theoretically grounded of the major valuation methodologies, resting directly on the principle that a dollar of cash flow is worth more today than the same dollar received in the future, and that value is created when future cash flows exceed what capital providers require as compensation for the time value of money and risk. This page is the hub for the Knowledge Centre's DCF content: what DCF is and why it works, how free cash flow and discount rates are built, how terminal value is calculated and stress-tested, the method variants practitioners choose between, and — distinctively — how DCF failure modes map onto FMAE's existing structural audit rule taxonomy, since no generic valuation resource ties DCF mechanics to a named, testable audit standard.
Scenario Analysis for DCF Valuation
Scenario analysis tests a DCF's value output under a small number of internally consistent, named states of the world — typically base, upside, and downside cases — where every driving assumption changes together as a coherent set, in contrast to sensitivity analysis, which isolates the effect of one or two variables at a time. This guide sets out how to build a scenario switch mechanism in a DCF model, the discipline required to keep each scenario's assumptions genuinely internally consistent, and how scenario output should be presented alongside sensitivity analysis rather than as a substitute for it.
Tornado Analysis for DCF Valuation
A tornado chart ranks a DCF's key assumptions by the size of their individual effect on value, presenting each variable's output range as a horizontal bar sorted from largest to smallest impact, producing the characteristic tornado-shaped visual. This guide sets out how to construct a tornado chart from a DCF model's one-way sensitivity outputs, which variables are typically included, and how a tornado chart complements rather than replaces the two-way sensitivity tables and scenario summaries addressed elsewhere in this Knowledge Centre.