Sensitivity and Scenario Analysis in Financial Modeling

Every financial model is built on assumptions, and every assumption is wrong. Revenue might grow at 8% instead of 10%. The terminal multiple might be 9x instead of 11x. The cost of debt might shift by 50 basis points. The question is not whether your assumptions are exactly right; it is how much the answer changes when they are wrong.

Sensitivity and scenario analysis are the tools that answer that question. They transform a financial model from a single-point estimate into a range of outcomes that captures uncertainty and helps decision-makers understand the risks embedded in any valuation or investment thesis. In investment banking, these analyses are not optional add-ons. They appear in virtually every DCF valuation, LBO model, and merger analysis that goes into a pitch book or board presentation.

For interview candidates, this topic is tested both conceptually ("What would you sensitize in a DCF?") and practically (building data tables in Excel modeling tests). This article covers both types of analysis, when to use each, what variables to test in different model types, and how to present the results professionally.

These two terms are often used interchangeably, but they are distinct techniques that serve different purposes.

Sensitivity Analysis

A technique that examines how the output of a financial model (such as implied share price, enterprise value, or IRR) changes when a single input variable is adjusted while all other assumptions remain constant. Sensitivity analysis isolates the impact of individual variables and is typically presented using one-variable or two-variable data tables in Excel.

Scenario Analysis

A technique that changes multiple input variables simultaneously to model different potential states of the world. The most common framework uses three scenarios: a base case (management's best estimate), a bull case (optimistic assumptions), and a bear case (pessimistic assumptions). Each scenario represents a coherent, internally consistent set of assumptions rather than isolated variable changes.

In practice, most investment banking deliverables use both techniques together. A pitch book might present a DCF sensitivity table showing implied share price across different WACC and terminal growth rate assumptions, alongside a scenario analysis showing three complete valuation cases with different revenue growth, margin, and multiple assumptions.

The two-variable data table is the workhorse of sensitivity analysis in investment banking. It shows how a single output changes across a grid of two input variables.

How Two-Variable Data Tables Work

A two-variable data table in Excel uses the Data Table function (found under Data > What-If Analysis > Data Table) to rapidly calculate an output for every combination of two input ranges. The structure is:

Formatting Sensitivity Tables Professionally

A well-formatted sensitivity table is immediately readable:

One-Variable Data Tables

Sometimes you only need to test one variable. A one-variable data table shows a single column of input values and one or more output columns. This is useful for quick checks (for example, "How does implied share price change across a range of terminal multiples?") but is less commonly used in formal deliverables than the two-variable version.

The variables you test depend on the type of model and what the analysis is trying to answer. Choosing the right variables to sensitize is itself an important analytical judgment.

DCF Valuation

In a discounted cash flow analysis, the output is typically implied share price or enterprise value. The two most common sensitivity pairs are:

WACC vs. Terminal Growth Rate: This is the classic DCF sensitivity table and appears in almost every pitch book. Small changes in WACC and terminal growth rate have outsized effects on the terminal value, which often represents 60-80% of total enterprise value.

A 50 basis point change in either WACC or the terminal growth rate can swing the implied value by 10-20% or more, which is precisely why this sensitivity table exists: to show the client and the reader that the valuation is a range, not a point.

EBITDA Exit Multiple vs. WACC: When using the exit multiple method for terminal value instead of the Gordon Growth model, sensitize the exit multiple against WACC.

Revenue Growth vs. EBITDA Margin: For companies where operating performance drives the valuation more than discount rate assumptions, this pair shows how different growth and profitability combinations affect implied value.

LBO Model

In an LBO analysis, the primary output is IRR (internal rate of return) to the private equity sponsor. The most common sensitivity pairs are:

Entry Multiple vs. Exit Multiple: Shows how the purchase price relative to the sale price drives returns. This table directly answers the question "How much do we need multiple expansion to hit our target IRR?"

Leverage (Debt/EBITDA) vs. Exit Multiple: Shows the interplay between how much debt is used to finance the acquisition and the eventual sale price.

Revenue Growth vs. EBITDA Margin: For operationally focused LBOs, this pair shows how the company's operating performance affects returns independent of multiple dynamics.

A standard LBO pitch book might include two or three sensitivity tables showing IRR under different combinations of these variables, often with a highlighted band showing the range where IRR exceeds the fund's target return threshold (typically 20-25%).

Merger Model

In a merger model, the key output is accretion/dilution to the acquirer's EPS (earnings per share). Common sensitivity pairs include:

Purchase Price Premium vs. Synergies Realized: Shows how the trade-off between what the acquirer pays and what cost or revenue synergies are captured affects whether the deal is accretive or dilutive.

Purchase Price vs. Financing Mix (Debt vs. Equity): Shows how the method of payment affects the acquirer's post-deal EPS.

Master interview fundamentals: Practice 400+ technical and behavioral questions, including valuation and modeling topics. Download our iOS app for comprehensive interview prep.

While data tables are the standard format for sensitivity output in banking, tornado charts provide a powerful visual alternative that is increasingly used in presentations and investment committee materials.

A tornado chart ranks variables by their impact on the output. Each variable is shown as a horizontal bar extending left (downside) and right (upside) from the base case value. The longest bars represent the variables with the greatest influence on the result. The chart gets its name from the shape: wider bars at the top, narrowing toward the bottom, resembling a tornado.

Tornado charts are particularly useful when presenting to senior bankers, boards, or investment committees who want to quickly understand which assumptions drive the analysis without parsing a dense grid of numbers. They complement data tables rather than replacing them.

Scenario analysis takes a different approach by changing multiple variables simultaneously to create coherent pictures of different futures.

How to Structure Scenarios

Each scenario should tell a consistent story about the business environment:

Base case: Management's best estimate or consensus expectations. Revenue grows at historical trend rates, margins remain stable or improve modestly, and market conditions are normal. This is typically the starting point of any analysis and represents the "most likely" outcome.

Bull case: Optimistic but plausible assumptions. Revenue growth accelerates (new product launch succeeds, market share gains materialize), margins expand through operating leverage or cost initiatives, and market multiples are favorable. The bull case should be achievable, not fantasy.

Bear case: Pessimistic but realistic assumptions. Revenue growth slows or turns negative (recession, competitive pressure, customer loss), margins compress, and market conditions deteriorate. The bear case should represent a genuine downside risk, not the absolute worst-case scenario.

Probability Weighting

Some analyses assign probability weights to each scenario and calculate a probability-weighted expected value:

For example, if you assign 25% probability to the bull case ($60 per share), 50% to base ($45), and 25% to bear ($30), the probability-weighted value is $45 per share. This approach is common in fairness opinions and board advisory presentations where a single valuation conclusion is needed.

Implementing Scenarios in Excel

The cleanest implementation uses a scenario toggle in the assumptions section: a single dropdown cell (1 = Bull, 2 = Base, 3 = Bear) that drives all assumption cells through INDEX, CHOOSE, or IF functions. When you change the toggle, every assumption in the model updates simultaneously, and all three financial statements recalculate to reflect the selected scenario.

This approach keeps the model clean and avoids maintaining three separate copies of the model. The toggle can be linked to individual scenario assumption rows that sit side by side in the assumptions section, making it easy to review and adjust each scenario's inputs.

How you present sensitivity and scenario analysis matters as much as the analysis itself. Different audiences need different formats.

In Pitch Books

Sensitivity tables in pitch books follow a standard format: a cleanly formatted grid with the base case highlighted, conditional color formatting, and clear axis labels. The table is typically accompanied by a one-sentence interpretation (for example, "Implied share price ranges from $38 to $54 across reasonable assumptions for WACC and terminal growth rate").

Place the sensitivity table on the same page as or immediately after the corresponding valuation summary. Do not bury it in an appendix where it will be ignored.

In Investment Committee Memos

For internal PE investment committee presentations, sensitivity tables often include a highlighted band showing the range where returns meet or exceed the fund's target IRR. This immediately communicates whether the deal works under a range of assumptions or only under the most optimistic case.

In Board Presentations

For board-level presentations, scenario comparison tables (base/bull/bear side by side) are often more effective than dense sensitivity grids. Boards want to understand the range of potential outcomes and the key risks, not parse 50 cells of data. Tornado charts work well in this context to highlight which assumptions the board should focus on.

Get the complete technical framework: Download our comprehensive 160-page PDF covering valuation, modeling, and deal structure questions. Access the IB Interview Guide for in-depth preparation.

Sensitivity and scenario analysis are tested in several common interview formats.

Sensitivity and scenario analysis transform financial models from fragile single-point estimates into robust analytical frameworks that capture uncertainty. Every assumption in a model is an educated guess, and the value of your analysis lies not in pretending those guesses are precise but in showing how the conclusion changes when they are wrong.

Mastering these techniques requires both technical skill (building data tables quickly, structuring scenario toggles cleanly) and analytical judgment (choosing the right variables to test, interpreting the results, presenting them appropriately). In interviews, demonstrating that you think about models as ranges rather than point estimates signals the kind of analytical maturity that banks look for in their strongest candidates. In practice, sensitivity and scenario analysis will be part of virtually every deliverable you produce as an analyst, from your first DCF to the most complex merger analysis.