Sensitivity Analysis in Finance: What-If Scenarios and Data Tables
A one-way sensitivity table for a DCF valuation might show that a 1% change in WACC changes the valuation by $5 per share, while a 1% change in terminal growth changes it by $3 per share. Sensitivity analysis identifies which assumptions matter most and quantifies the uncertainty around any point estimate.
Sensitivity analysis is the systematic study of how changes in input assumptions affect the output of a financial model. In valuation, the two most critical inputs to any DCF model are the discount rate (WACC) and the terminal growth rate. A small change in either can produce a dramatically different valuation. Sensitivity analysis quantifies this relationship, allowing analysts to present valuation as a range rather than a false-precision single number. The standard output of a DCF sensitivity analysis is a two-way data table with WACC across the top and terminal growth rate down the side, with the resulting valuation per share in the interior cells. This table immediately shows which combinations of assumptions produce a given valuation and reveals the sensitivity of the valuation to each input. For example, if the valuation ranges from $40 to $80 across reasonable WACC and growth assumptions, the analyst knows that the company is truly worth somewhere in this range. The art of valuation is not finding a single "right" number but determining which cells in the sensitivity table are most realistic and justifying those assumptions with fundamental analysis. Financial modeling best practices →
Types of sensitivity analysis: One-way sensitivity analysis varies one input at a time while holding all others constant. This reveals the individual impact of each assumption. A one-way table might show how valuation changes as WACC ranges from 8% to 12% in 0.5% increments. Two-way sensitivity analysis varies two inputs simultaneously, creating a matrix of outputs. This is the most common presentation in equity research reports and investment committee memos because WACC and terminal growth are the two most important and uncertain DCF inputs. Scenario analysis is a variant where the analyst defines discrete scenarios (base, bull, bear) with different sets of assumptions rather than systematically varying individual inputs. Monte Carlo simulation extends sensitivity analysis by defining probability distributions for each input and running thousands of random iterations to generate a probability distribution of outputs. Each type of analysis serves a different purpose. One-way analysis identifies the most sensitive assumptions. Two-way analysis shows interaction effects. Scenario analysis tells a narrative story about possible futures. Monte Carlo simulation provides a rigorous probabilistic framework. Most practitioners use a combination of these techniques. Terminal value: estimating perpetuity value →
Building Sensitivity Tables: Practical Guidelines
A sensitivity table requires a well-structured financial model with clearly separated inputs and calculations. In Excel, a data table uses the TABLE function to systematically substitute input values and record the output. The key is that the output cell must be a formula that references the input cells the analyst wants to vary. The data table creates a range of outputs based on a range of inputs without requiring manual recalculation. For one-way tables, place the input values in a row or column and reference the output cell. For two-way tables, place one set of inputs across the top row and the other set down the first column, with the output cell at the intersection. The sensitivity table should display the absolute output value (e.g., share price) rather than the percentage change, because percentage changes can be misleading when the base case is close to zero. Color coding (green for high values, red for low) helps readers quickly identify the range of outcomes. Tornado charts visualize one-way sensitivity by showing bars for each input, sorted by the magnitude of impact. The longest bars at the top are the assumptions with the greatest impact on the output. Tornado charts are particularly effective for communicating sensitivity results to non-technical audiences such as investment committees or board members. Risk-adjusted return measures →
Common Pitfalls in Sensitivity Analysis
The most common mistake in sensitivity analysis is varying assumptions beyond their realistic range. A sensitivity table showing valuation at a 2% terminal growth rate for a company in a declining industry is misleading because 2% growth is not achievable. The assumption ranges must be grounded in reality. The second common mistake is ignoring correlation between variables. WACC and terminal growth are correlated — higher growth typically implies higher risk and thus a higher WACC. Varying them independently in a two-way table can produce cells that represent impossible combinations. The third mistake is presenting sensitivity results without a base case or without identifying which cells are most likely. A sensitivity table with 20 cells is not useful if the analyst does not explain which assumptions are reasonable and which are not. The fourth mistake is using sensitivity analysis as a substitute for rigorous fundamental analysis. Sensitivity analysis shows how much valuation changes with assumptions, but it does not tell you which assumptions are correct. The analyst must still justify the base case and the range of reasonable assumptions through careful study of the company's business, industry, and competitive position. Sensitivity analysis is a tool for communicating uncertainty, not a tool for avoiding the hard work of forecasting. DuPont analysis for ROE decomposition →
FAQs
What is the difference between sensitivity analysis and scenario analysis?
Sensitivity analysis systematically varies individual inputs (often one or two at a time) to show the relationship between inputs and outputs. Scenario analysis defines complete sets of assumptions that tell a coherent story about the future — an optimistic scenario, a pessimistic scenario, and a base case. Sensitivity analysis is more granular and systematic; scenario analysis is more holistic and narrative. Most financial models include both: sensitivity tables for key inputs (WACC, growth rate) and scenario analysis for broader narratives about competitive dynamics, regulatory changes, or macroeconomic conditions.
How many inputs should be included in a sensitivity analysis?
Focus on the 3-5 assumptions that have the greatest impact on the output and the greatest uncertainty. For a DCF model, these are typically WACC, terminal growth rate, revenue growth rate, operating margin, and capital expenditure intensity. Including too many inputs creates information overload and obscures the key drivers. A good rule is to first run a one-way sensitivity analysis on all major inputs to identify the most impactful ones, then focus the detailed analysis on those top 3-5 drivers. Pareto's principle applies: 20% of the inputs drive 80% of the output variability.
Can sensitivity analysis be used for non-valuation models?
Yes. Sensitivity analysis applies to any financial model. In LBO models, analysts test how changes in entry multiple, exit multiple, debt financing terms, and EBITDA growth affect the IRR. In project finance models, sensitivity analysis tests how changes in construction costs, commodity prices, and interest rates affect project returns. In budgeting models, sensitivity analysis shows how changes in revenue and cost assumptions affect budget surplus or deficit. The fundamental principle is the same: identify the key uncertain inputs and systematically test how changes affect the output. Sensitivity analysis is a universal tool for decision-making under uncertainty.