Sensitivity analysis is one of the most practical and commonly used tools in investment banking. It answers a simple but critical question: how do the model’s outputs change when key assumptions change? Whether you are presenting a DCF valuation to a client, showing LBO returns to a PE firm, or stress-testing a three-statement model, sensitivity analysis gives you a structured way to show a range of outcomes rather than a single point estimate.
This guide explains how to build sensitivity analysis using Excel’s data table feature, including one-way and two-way tables, and covers best practices for presenting the results professionally.
Why Sensitivity Analysis Matters
No model prediction is exact. Revenue growth could come in above or below the base case. The discount rate might shift depending on market conditions. An acquisition target’s margins could expand or contract. Sensitivity analysis acknowledges this uncertainty by showing how the output metric responds to changes in the underlying assumptions.
In practice, sensitivity analysis serves several purposes:
- Risk communication. It shows clients and decision-makers the range of potential outcomes, not just the base case.
- Assumption testing. It helps you identify which assumptions have the biggest impact on the output: these are the assumptions that deserve the most diligence.
- Negotiation support. In M&A, sensitivity tables help frame price discussions. A buyer might see what price they could pay at different growth rate assumptions, or a seller might demonstrate that the deal terms work across a range of scenarios.
- Model validation. If changing an assumption by a small amount causes the output to swing wildly, something might be wrong with the model’s structure.
One-Way Sensitivity Analysis
A one-way sensitivity analysis (also called a one-variable data table) shows how a single output changes when you vary one input assumption.
Example: DCF Implied Share Price vs. WACC
Suppose your DCF model produces an implied share price of $50.00 using a WACC of 10.0%. You want to show how the implied share price changes as WACC varies from 8.0% to 12.0%.
Step-by-Step: Building a One-Way Data Table
- Step 1: Set up the layout. In an empty area of your spreadsheet, create a column of WACC values (8.0%, 8.5%, 9.0%, …, 12.0%). In the cell directly above and one column to the right of the first WACC value, enter a formula that links to your model’s output cell (the implied share price).
- Step 2: Select the data table range. Select the entire range, from the output formula cell down through all the WACC values, and across the output column.
- Step 3: Insert the data table. Go to Data > What-If Analysis > Data Table. Since you are varying a column input (WACC values are in a column), enter the cell reference for your model’s WACC input cell in the “Column input cell” field. Leave “Row input cell” blank.
- Step 4: Press OK. Excel will populate the table by substituting each WACC value into the model and recording the resulting share price.
The result is a clean table showing implied share price at each WACC assumption. This is straightforward to present in a pitch book or board presentation.
Two-Way Sensitivity Analysis
A two-way sensitivity analysis varies two assumptions simultaneously and shows the output at every combination. This is the most common format in banking because it captures the interaction between two key drivers.
Example: Implied Share Price vs. WACC and Terminal Growth Rate
The classic DCF sensitivity table shows implied share price as a function of both WACC and the terminal growth rate (or exit multiple). This captures the two assumptions that typically have the largest impact on DCF output.
Step-by-Step: Building a Two-Way Data Table
- Step 1: Set up the layout. Place WACC values in a column (going down) and terminal growth rates in a row (going across). In the top-left corner cell, where the row and column headers intersect, enter a formula linking to the model’s output cell (implied share price).
- Step 2: Select the entire range. Highlight the full table including the row of growth rates, the column of WACC values, and the corner cell with the output formula.
- Step 3: Insert the data table. Go to Data > What-If Analysis > Data Table. Enter the cell reference for the terminal growth rate input as the “Row input cell” and the WACC input cell as the “Column input cell.”
- Step 4: Press OK. Excel calculates the implied share price for every combination of WACC and terminal growth rate.
The result is a matrix (typically 5 to 7 rows and 5 to 7 columns) that shows the full range of implied values. The base case assumption sits in the middle of the table and is usually highlighted or bolded for reference.
Common Input-Output Pairs for Sensitivity Analysis
Different model types call for different sensitivity tables. Here are the most common combinations:
DCF Model
- WACC vs. Terminal Growth Rate: The classic DCF sensitivity.
- WACC vs. Exit Multiple: Used when the terminal value is based on an exit multiple rather than a Gordon Growth perpetuity.
- Revenue Growth vs. EBITDA Margin: Shows how operating assumptions drive value.
LBO Model
- Entry Multiple vs. Exit Multiple: Shows IRR and MOIC across different purchase and sale price assumptions.
- Leverage (Debt / EBITDA) vs. Exit Multiple: Shows how the capital structure affects returns.
- Revenue Growth vs. Exit Multiple: Captures the impact of operating performance on returns.
Merger Model
- Offer Price vs. Synergies: Shows accretion / dilution at different price and synergy levels. See our merger model guide for more detail.
- Exchange Ratio vs. Synergies: Relevant for stock-for-stock mergers.
Formatting Sensitivity Tables for Presentation
A sensitivity table that is mathematically correct but poorly formatted misses the point. The goal is to make the information immediately digestible for the reader. Follow these formatting standards:
- Highlight the base case. Bold or shade the cell that represents the base case assumptions. This anchors the reader.
- Use conditional formatting. Apply a color gradient (e.g., green for high values, red for low values) so that the reader can quickly see the pattern. Use muted colors, not neon.
- Label clearly. The column and row headers should state the assumption name and units (e.g., “WACC (%)” and “Terminal Growth Rate (%)”).
- Show the output metric name. Above the table, label what the body of the table represents (e.g., “Implied Share Price ($)” or “IRR (%)”).
- Consistent number formatting. Share prices to two decimal places, IRRs to one decimal, multiples to one decimal with “x” suffix.
- Keep the table compact. Five to seven data points per axis is usually sufficient. Too many rows and columns make the table hard to read.
Tornado Charts as a Complement
A tornado chart (or tornado diagram) is a horizontal bar chart that ranks assumptions by their impact on the output. Each bar shows the range of the output when a single assumption is varied between its low and high values, holding all other assumptions constant.
Tornado charts are useful because they visually communicate which assumptions matter most. If the WACC bar spans $15 of share price range while the working capital assumption bar spans only $1, it is immediately clear where the uncertainty lies.
To build a tornado chart, run a one-way sensitivity on each key assumption, record the output range, and then chart the results as a stacked horizontal bar chart sorted by range width. In banking, tornado charts are less common than sensitivity tables but are increasingly used in client presentations for their visual clarity.
Scenario Analysis vs. Sensitivity Analysis
It is worth distinguishing between sensitivity analysis and scenario analysis, as they are related but different:
- Sensitivity analysis varies one or two assumptions independently while holding everything else constant. It shows the mathematical relationship between inputs and outputs.
- Scenario analysis changes multiple assumptions simultaneously to represent a coherent alternative future (e.g., a “Recession Case” where revenue growth drops, margins compress, and the discount rate increases together).
Both are valuable. Sensitivity analysis is better for understanding individual assumption impact. Scenario analysis is better for telling a complete story about what could happen under different market conditions. Most banking models include both.
Technical Tips for Data Tables in Excel
A few practical considerations when working with Excel data tables:
- Data tables slow down large models. Excel recalculates every cell in the data table every time the model updates. In complex models, this can cause significant lag. To manage this, set calculation mode to “Automatic Except for Data Tables” (Formulas > Calculation Options). Then press Ctrl + Shift + F9 when you want to force the data table to recalculate.
- You cannot edit individual cells in a data table. The body of a data table is an array formula. You can format individual cells, but you cannot change the formula in any single cell without deleting the entire table.
- Place data tables on a separate tab. To keep your model organized and to isolate the performance impact, place sensitivity tables on their own “Sensitivity” or “Output” tab rather than embedding them within the financial statements.
- Use absolute references for input cells. When setting up the corner formula that links to the output cell, make sure it correctly references the final output (e.g., the implied share price or IRR cell).
Common Mistakes in Sensitivity Analysis
- Testing unrealistic ranges. If your WACC sensitivity goes from 1% to 25%, the results at the extremes are meaningless. Use ranges that are plausible given the company and market conditions.
- Forgetting that assumptions interact. A one-way sensitivity on revenue growth ignores the fact that high-growth companies often require more capex and working capital investment. Be aware of what a one-way table does and does not capture.
- Not highlighting the base case. A sensitivity table without the base case marked forces the reader to figure out which cell represents the current assumptions. Always make it obvious.
- Building the table manually. Some analysts type in values by hand rather than using Excel’s data table feature. This is error-prone and defeats the purpose. Use the built-in tool.
Final Thoughts
Sensitivity analysis is not optional in investment banking: it is expected in virtually every model and presentation. Learning to build clean, well-formatted sensitivity tables is a core skill that you will use from your first week as an analyst through your entire career. Master the mechanics of one-way and two-way data tables, understand which input-output pairs matter for each model type, and always present the results in a way that helps the reader make better decisions.
To practice building models that incorporate sensitivity analysis, start with our free resources and Technical Cheatsheet.
Want Personalized Interview Coaching?
If you are serious about breaking into investment banking, the best thing you can do is work with someone who has been through the recruiting process and knows exactly what top banks are looking for. At Wall Street Mastermind, we have helped over 2,500 students land offers at every bulge bracket and elite boutique bank on Wall Street. Book a free strategy call to learn how we can help you prepare for your interviews and maximize your chances of landing the offer.
Related Articles
- Financial Modeling Best Practices for Investment Banking
- Terminal Value in DCF: Gordon Growth and Exit Multiple
- How to Calculate Unlevered Free Cash Flow
- LBO Returns Analysis: How to Calculate IRR and MOIC
- Merger Model: Accretion / Dilution Analysis
- Circular References in Financial Models: How to Handle Them
Follow Wall Street Mastermind
Stay up to date with the latest investment banking recruiting tips, technical guides, and career advice:
- YouTube
- TikTok
- X (Twitter)
- Podcast: Apple Podcasts | Spotify | SoundCloud
