Sensitivity Analysis Beyond 2 Variables:
The Complete Guide

What Sensitivity Analysis Actually Does in a Financial Model

Sensitivity analysis answers a specific question: if I change this input, how much does this output move? It is one of the most important tools in financial modeling because models are, by definition, wrong in ways you don't yet know. The inputs you assumed will be wrong. The question is not whether they are wrong — it is how wrong, and which ones matter most.

A properly structured sensitivity analysis tells you:

• Which assumptions are load-bearing. Change the entry multiple by 1x and IRR drops 400 bps. Change the capex assumption by 10% and IRR moves 30 bps. Now you know where to spend your diligence time.
• What the distribution of outcomes looks like. If your base case is 22% IRR, does a mild downside scenario still clear your hurdle rate? Does a mild upside scenario produce a return worth the risk?
• Where your model breaks. At what entry multiple does the deal stop working? At what revenue growth rate does the company run out of cash before the exit? These break-even points are decision inputs.

This is different from scenario analysis, though the two are often conflated. Sensitivity analysis is systematic — hold everything else constant, vary one (or two) inputs across a range, observe outputs. Scenario analysis is narrative — "what does the world look like if the bear thesis plays out?" Both matter; they answer different questions.

Excel's Data Table: How It Works and Where It Breaks

Excel's What-If Analysis → Data Table is the standard tool for sensitivity analysis in spreadsheets. It works by recalculating your model once for each combination of input values you specify, then filling a table with the resulting outputs. The mechanics:

• One-variable data table: A column of input values (e.g., entry multiples from 7x to 12x) plus a formula in the corner cell pointing to your output (e.g., IRR). Excel fills a single-column table of outputs.
• Two-variable data table: Input values along a row (entry multiples) and down a column (exit multiples). Excel fills a grid of outputs — the standard "sensitivity table" that appears in every PE deal memo.

For what it does, Excel's data table is genuinely good. It recalculates efficiently, it formats cleanly, and analysts know how to read it. The two-variable format is the industry standard presentation for a reason.

The problem is the ceiling: Excel's data table is hard-capped at two variables. You cannot add a third axis. There is no Excel-native way to create a three-variable sensitivity table. This limitation is not a bug or an oversight — it is an architectural constraint of the grid-based table format. A three-variable sensitivity matrix would require a three-dimensional structure that doesn't exist in a 2D spreadsheet.

The 2-Variable Ceiling: What It Costs You

When a sensitivity analysis is limited to two variables, analysts compensate in ways that create real risk:

Creating Multiple Disconnected Tables

The most common workaround is to build several two-variable tables, each testing a different pair of inputs. Entry vs. exit. Entry vs. revenue growth. Revenue growth vs. margin. The problem: each table assumes all other inputs are at base case. The interactions between variables are invisible. A scenario where entry multiple is high and revenue growth is low and margin is compressed — the exact combination that produces the worst outcome — never appears in any individual table.

Picking the "Two That Matter" and Hoping

Many analysts simply pick the two inputs they believe are most important and build a single table. This is a judgment call that may be wrong. If you chose entry multiple and exit multiple as your two axes, but the deal's actual vulnerability is to revenue growth (which your exit multiple is a proxy for), you have built a sensitivity table that obscures the real risk.

Manual Scenario Building

Some analysts give up on data tables entirely for multi-variable analysis and build named scenarios manually — one for each combination of interest. This is effective but slow: five scenarios with three variables each requires building fifteen separate cases. And it reintroduces the copy-sheet management problem.

Three Workarounds Analysts Actually Use (and Their Trade-offs)

What a True Sensitivity Sweep Looks Like

The right answer to multi-variable sensitivity analysis is not to fight the constraints of the two-variable table. It is to reframe the problem: instead of a grid of output cells for every combination of inputs, generate a set of scenario branches — each representing a specific point in the input space — and compare their outputs directly.

This is conceptually equivalent to what the VBA macro does, but natively integrated into the modeling tool. Define your input range, define the step size, and the tool creates one branch per step — each inheriting your full model except for the swept input. You can then compare any two branches, view all branches on the Decision Sheet, or identify the break-even point by scanning where the metric crosses your threshold.

This approach has several advantages over a static data table:

• Each sweep branch is a live model. You can drill into any individual scenario and inspect every formula, not just the output cells that appear in the table.
• Multiple sweeps compose. Run a sweep on revenue growth. Then run a second sweep on entry multiple within one of the revenue growth branches. The result is a tree of scenarios, not a flat table — but it covers the same combinatorial space with more transparency.
• The sweep updates when the base model changes. Fix a formula in the base model and all sweep branches inherit the fix automatically. A VBA macro output table would need to be re-run manually.
• You can diff any two sweep branches. The DiffPanel instantly shows what changed between them — which, for a sensitivity sweep, is exactly the swept input and its downstream effects.

The Sensitivity Sweep in TreBranch

TreBranch implements this natively. Right-click any input cell in your model → Sweep. Enter your range (e.g., Revenue Growth: 4%, 6%, 8%, 10%, 12%) — comma-separated values, or a min/max/step — and click Sweep. TreBranch auto-generates one branch per value, each inheriting everything from the current branch except the swept input.

The result looks like this in the scenarios panel:

Key Metrics (IRR, revenue, payback) appear inline for every branch — no need to open the model to read each scenario's outcome. Click Compare on any two branches and the DiffPanel shows exactly which cells differ and by how much.

For a deal team presenting to a credit committee, this is significantly more useful than a static two-variable table: the sweep is live, every branch can be drilled into, and the entire analysis recomputes automatically when the base model changes.

When to Use Sensitivity Analysis vs. Scenario Branching

These two tools answer different questions, and knowing when to use which one saves significant modeling time.

Use a sensitivity sweep when: you want to understand the relationship between one input and an output across a continuous range. "How does IRR change as revenue growth varies from 4% to 12%?" is a sensitivity question. The sweep is the right tool.

Use scenario branches when: you want to model a coherent, internally consistent narrative. "What does the model look like if management hits their aggressive growth target, we execute the add-on acquisition, and we exit at a premium multiple in year 4?" involves multiple assumptions changing together in a correlated, story-driven way. That is a scenario, not a sensitivity.

Use both together when: you have defined your narrative scenarios (base, upside, downside) and want to understand the sensitivity of each scenario to a key remaining assumption. Run the base case scenario, then sweep entry multiple from 7x to 12x within that base case. This tells you: "Given our base growth assumptions, what is the entry-multiple sensitivity?" — a question that has a different answer in your downside scenario than your base.

Getting Started: Your First Sensitivity Sweep in TreBranch

If you want to try the sensitivity sweep approach with your own models, here is the fastest path:

1. Import your model. TreBranch opens .xlsx and .csv files directly. Open your existing Excel model — no rebuilding required.
2. Mark your Key Metrics. Right-click any output cell (IRR, revenue, MOIC) and mark it as a Key Metric. It will appear in the scenarios panel for every branch automatically.
3. Create your base branch. Your imported model becomes the root branch — the base case everything inherits from.
4. Run your first sweep. Right-click your most important input cell → Sweep. Enter the range. TreBranch generates one branch per value, each fully live.
5. Compare branches. Click any two branches → Compare. The DiffPanel shows exactly which cells changed and by how much impact.

The entire setup takes under five minutes for an existing model. The 30-day free trial on the Microsoft Store gives you enough time to run a real deal model through it and see if the workflow fits.

FAQ: Sensitivity Analysis in Excel and TreBranch