A financial model is only as useful as the insight it provides. One of the most practical tools for understanding risk and valuation flexibility is sensitivity analysis. In investment banking, sensitivity tables are built into nearly every DCF, M&A, and LBO model. They allow users to test how changes in assumptions — like discount rates, margins, or growth — affect key outputs such as enterprise value, IRR, or EPS.
This article explains how sensitivity analysis works, how to structure it properly, and how to ensure it adds clarity, not confusion, to your model.
What Is Sensitivity Analysis?
Sensitivity analysis tests how the output of a model changes when one or more input variables are adjusted. In banking models, this usually means applying a range of assumptions to a specific metric — for example, showing how equity value changes across different WACC and terminal growth rate combinations.
It helps users:
- Understand the drivers of value
- Assess risk exposure
- Justify assumptions to clients or committees
- Frame valuation ranges in deal discussions
Common Inputs and Outputs
In most models, sensitivities are run on the variables that have the largest impact on results. Common input variables include:
- Discount rate (WACC)
- Terminal growth rate
- Exit multiple
- Revenue growth
- EBITDA margin
- Capex or working capital intensity
Common output metrics include:
- Enterprise value
- Equity value
- IRR (in LBO models)
- Accretion/dilution (in M&A models)
- Target share price
How to Structure a Sensitivity Table in Excel
In Excel, sensitivity tables are created using the Data Table function.
Here’s the typical layout for a two-variable table:
- Rows: One input variable (e.g., terminal growth rate)
- Columns: Another input variable (e.g., WACC)
- Center cell: The formula that calculates the desired output (e.g., equity value)
Steps:
- Build the output formula in a single reference cell.
- Set up a grid with one input series across the top and one down the left side.
- Highlight the entire table, including the blank center.
- Go to
Data > What-If Analysis > Data Table. - Assign the row input cell and column input cell.
- Excel populates the grid with the calculated results.
Each value in the table shows the result of running the model with the corresponding combination of inputs.
Best Practices for Clarity
To make sensitivity tables useful:
- Label inputs and units clearly
- Round outputs to whole numbers or two decimal places — avoid overprecision
- Use conditional formatting to highlight high and low values (optional)
- Place tables near outputs or in a dedicated “Sensitivity” tab
- Use consistent formats — avoid mixing valuation metrics with returns in the same table
Avoid cluttering the model with too many tables. Focus on 1–3 that best support your narrative.
One-Way vs Two-Way Sensitivities
A one-way sensitivity changes a single variable and tracks the result — for example, showing how equity value changes across a range of WACCs.
A two-way sensitivity shows the combined impact of two variables — often used for WACC and terminal growth, or exit multiple and EBITDA margin.
Both have value. One-way tables are easier to explain. Two-way tables provide a more complete view of interactions.
Scenario vs Sensitivity Analysis
Sensitivity analysis changes one variable at a time to isolate its impact. Scenario analysis changes multiple variables together to reflect different business environments — such as base, upside, and downside cases.
Both are useful. Sensitivities show which inputs matter most. Scenarios show how everything moves together under different assumptions.
Common Errors to Avoid
- Referencing the wrong cell in the formula — always test with a known result.
- Forgetting that data tables don’t auto-refresh — press F9 or recalculate manually.
- Leaving old assumptions in place after copying the table elsewhere.
- Running sensitivities on unstable or error-prone outputs — always validate the base case first.
Closing Thought
Sensitivity analysis is a communication tool. It shows how the model responds to change and where risk or upside is concentrated. In investment banking, a clear and functional sensitivity table can turn a complex model into a usable decision tool. When done properly, it helps bankers present their analysis with confidence and clarity.