Chapter 12
Chapter 12: Sensitivity and Scenario Analysis - How Robust Is the Decision When Assumptions Change?
Learning Objectives
By the end of this chapter, you should be able to:
-
explain why sensitivity analysis matters in case-solving
-
identify the assumptions that drive a financial recommendation
-
distinguish sensitivity analysis from scenario analysis
-
build one-variable and two-variable sensitivity analyses
-
use Excel data tables to test assumptions
-
use Scenario Manager to compare different scenarios
-
identify best-case, base-case, and worst-case outcomes
-
determine which assumptions create the greatest financial risk
-
identify the decision boundary for a recommendation
-
communicate uncertainty without weakening your recommendation
-
use sensitivity analysis to strengthen your credibility with judges
Why This Matters
One of the most dangerous numbers in a case competition is a number that looks too precise.
You might calculate:
NPV = $3.47 million
IRR = 18.6%
ROI = 34.2%
The spreadsheet may be correct.
But the question is:
How confident are you that the assumptions behind those numbers are correct?
What happens if:
-
sales are 10% lower?
-
costs are 15% higher?
-
growth is slower?
-
implementation takes longer?
-
customer adoption is lower?
-
the discount rate increases?
-
the investment is larger than expected?
If your recommendation falls apart when one assumption changes slightly, you don't have a robust recommendation.
You have an optimistic forecast.
Discover Your Mad Skills Principle
A strong recommendation should survive reasonable changes in its assumptions.
Sensitivity analysis helps you determine whether it does.
The Problem With Point Forecasts
A point forecast assumes that one particular set of assumptions will occur.
For example:
Revenue growth = 8%
Cost inflation = 3%
Customer adoption = 15%
Initial investment = $10M
The model then produces:
NPV = $3.5M
But why 8%?
Why 15%?
Why $10M?
These assumptions may be reasonable.
But they are still assumptions.
A better question is:
What happens if we're wrong?
Financial Analysis Is Built on Assumptions
Almost every financial model contains assumptions.
For an investment case, these might include:
Revenue Assumptions
-
number of customers
-
units sold
-
price
-
market share
-
growth rate
-
customer retention
Cost Assumptions
-
labour costs
-
material costs
-
operating expenses
-
technology costs
-
marketing costs
Investment Assumptions
-
initial investment
-
implementation costs
-
maintenance
-
capital expenditures
Timing Assumptions
-
launch date
-
ramp-up period
-
implementation time
-
useful life
Financial Assumptions
-
discount rate
-
tax rate
-
inflation
-
terminal growth
The goal is not to eliminate uncertainty.
The goal is to understand it.
Sensitivity vs. Scenario Analysis
These concepts are related but different.
Sensitivity Analysis
Sensitivity analysis changes one or two important assumptions to see how the financial result changes.
For example:
What happens to NPV if growth changes from 2% to 10%?
Or:
What happens if both growth and the discount rate change?
Sensitivity analysis helps answer:
"Which assumptions matter most?"
Scenario Analysis
Scenario analysis changes multiple assumptions together to create a realistic situation.
For example:
Worst Case
-
lower sales
-
higher costs
-
slower growth
-
delayed implementation
Base Case
-
expected sales
-
expected costs
-
expected growth
-
expected timing
Best Case
-
higher sales
-
lower costs
-
faster growth
-
earlier implementation
Scenario analysis helps answer:
"What might the overall outcome look like under different versions of the future?"
Discover Your Mad Skills Principle
Sensitivity analysis tests the model. Scenario analysis tests the story.
Part 1
Sensitivity Analysis
One-Variable Sensitivity
The simplest sensitivity analysis changes one assumption.
Suppose your base case produces:
NPV = $3.5M
You want to know how sensitive that NPV is to revenue growth.
| Revenue Growth | NPV |
|---|---|
| 0% | $0.9M |
| 2% | $1.5M |
| 4% | $2.2M |
| 6% | $2.9M |
| 8% | $3.5M |
| 10% | $4.2M |
Now you know something that the original NPV did not tell you.
The investment remains valuable even if growth is below the base assumption.
That strengthens the recommendation.
Two-Variable Sensitivity
Sometimes two assumptions interact.
For example:
-
growth rate
-
discount rate
You can create a two-variable sensitivity table.
| Growth / Discount | 10% | 12% | 14% | 16% |
|---|---|---|---|---|
| 2% | $0.8M | $0.4M | $0.1M | -$0.3M |
| 4% | $1.8M | $1.4M | $1.0M | $0.6M |
| 6% | $2.8M | $2.4M | $2.0M | $1.6M |
| 8% | $3.9M | $3.5M | $3.1M | $2.7M |
| 10% | $5.1M | $4.6M | $4.1M | $3.6M |
This provides much more insight.
You can now see how the investment behaves across combinations of assumptions.
What Should You Sensitize?
Not every assumption deserves a sensitivity table.
This is an important coaching point.
Teams sometimes create enormous tables containing:
-
tax rate
-
inflation
-
labour costs
-
rent
-
utilities
-
depreciation
-
working capital
-
exchange rates
-
discount rates
-
revenue growth
-
market share
-
customer acquisition
The result is technically impressive.
But strategically useless.
Instead ask:
Which assumptions could actually change our decision?
The Materiality Test
A useful test is:
High Impact
Could this assumption change the recommendation?
Test it.
Medium Impact
Could this assumption materially change the economics?
Consider testing it.
Low Impact
Would changing it have little effect?
Don't waste presentation space on it.
This is how you move from spreadsheet analysis to decision analysis.
Discover Your Mad Skills Principle
Don't sensitize everything. Sensitize what matters.
Finding the Key Drivers
Suppose your model depends on:
-
price
-
volume
-
marketing cost
-
labour cost
-
tax rate
-
discount rate
You can test each individually.
For example:
| Variable | Base | -10% Impact on NPV | +10% Impact on NPV |
|---|---|---|---|
| Price | $100 | -$1.2M | +$1.3M |
| Volume | 100K | -$1.0M | +$1.1M |
| Labour Cost | $20 | +$0.4M | -$0.4M |
| Marketing | $2M | +$0.1M | -$0.1M |
| Tax Rate | 25% | +$0.2M | -$0.2M |
Now the team can see:
Price and volume are the major drivers.
Marketing cost is comparatively unimportant.
This tells you where management attention belongs.
The Tornado Concept
A useful way to visualize sensitivity is with a tornado chart.
The largest bars represent the assumptions with the greatest impact on the outcome.
Conceptually:
Price
████████████████
Volume
██████████████
Labour Cost
███████
Tax Rate
████
Marketing
██
The message becomes immediately obvious.
Price and volume drive the economics.
This is much more useful than presenting a page of calculations.
Excel Skill
Two-Variable Data Tables
Excel's Data Table function can automate two-variable sensitivity analysis.
Suppose your model contains:
C2 = Discount Rate
C3 = Growth Rate
and your model calculates:
C12 = NPV
You can create a sensitivity table showing NPV across different growth and discount-rate combinations.
Basic Process
-
Build your financial model.
-
Identify the output you want to test.
-
Place the output formula in the top-left corner of the sensitivity table.
-
Enter the alternative values for Variable 1.
-
Enter the alternative values for Variable 2.
-
Select the entire table.
-
Go to Data → What-If Analysis → Data Table.
-
Select the appropriate row input cell.
-
Select the appropriate column input cell.
-
Review the resulting values.
The important point is not simply knowing how to click through Excel.
It is understanding what the table is telling you.
Reading the Table
Don't simply show the table.
Interpret it.
For example:
"Our recommendation remains value creating across the tested range of growth and discount rates. NPV only becomes negative when growth falls below approximately 2% while the discount rate exceeds 15%."
That is an insight.
The table is evidence.
Part 2
Scenario Analysis
Sensitivity analysis changes individual assumptions.
Scenario analysis combines assumptions.
This makes it particularly useful when several things could go right or wrong at the same time.
Building Three Scenarios
A simple approach is:
Worst Case
Reasonably pessimistic assumptions.
Base Case
Most likely assumptions.
Best Case
Reasonably optimistic assumptions.
Avoid making the worst case absurdly negative and the best case unrealistically positive.
The scenarios should represent plausible futures.
Example
Imagine a new product launch.
Worst Case
-
customer adoption: 5%
-
revenue growth: 2%
-
costs: 15% above plan
-
launch delayed six months
Base Case
-
customer adoption: 10%
-
revenue growth: 6%
-
costs: as budgeted
-
launch on schedule
Best Case
-
customer adoption: 15%
-
revenue growth: 10%
-
costs: 5% below plan
-
launch accelerated
The resulting financial outcomes might be:
| Worst | Base | Best | |
|---|---|---|---|
| Revenue | $18M | $25M | $34M |
| EBITDA | $2M | $5M | $9M |
| NPV | -$0.5M | $3.5M | $7.2M |
| IRR | 8% | 15% | 23% |
Now the decision is much more informative.
Scenario Manager in Excel
Excel's Scenario Manager can be useful when several input cells need to change simultaneously.
For example, you might create:
Scenario 1
Conservative
Scenario 2
Base
Scenario 3
Aggressive
Each scenario changes:
-
growth
-
volume
-
cost
-
timing
You can then generate a summary showing the financial outcome of each scenario.
The Most Important Question
Once you have created the scenarios, don't stop.
Ask:
What does this tell us about the decision?
Suppose:
| Scenario | NPV |
|---|---|
| Worst | -$0.5M |
| Base | $3.5M |
| Best | $7.2M |
The investment is attractive in the base case.
But the downside case destroys value.
That doesn't necessarily mean:
"Don't invest."
It means:
"What can we do to reduce the downside?"
Turning Sensitivity Into Strategy
This is where sensitivity analysis becomes powerful.
Suppose the model shows that the biggest risk is:
customer adoption
The recommendation could include a mitigation strategy:
Launch initially in two test markets before committing the full investment.
Now the financial analysis has influenced the strategy.
Or suppose the biggest risk is:
implementation cost
You might recommend:
Use a staged investment with a go/no-go decision after Phase 1.
Again, the analysis informs the recommendation.
The Value of Staging
Sensitivity analysis can reveal that an investment is attractive but uncertain.
Instead of choosing:
Invest
or
Don't Invest
consider:
Invest in stages.
For example:
Phase 1
$2M pilot
↓
Evaluate
Customer adoption
Cost performance
Operational feasibility
↓
Phase 2
$5M expansion
↓
Phase 3
Full rollout
This can reduce downside risk while preserving upside potential.
Discover Your Mad Skills Principle
When uncertainty is high, don't always eliminate the opportunity. Sometimes redesign the decision.
Decision Boundaries
One of the most useful outputs of sensitivity analysis is the decision boundary.
This answers:
At what point does our recommendation change?
For example:
The investment is attractive when:
Revenue growth > 4%
But below 4%:
NPV < 0
Now 4% becomes an important threshold.
You can tell management:
"The investment remains value creating as long as annual growth remains above approximately 4%. Below that threshold, we should reconsider the investment."
That is much more useful than simply presenting:
NPV = $3.5M
Finding the Break-Even Point
Sensitivity analysis can also help identify break-even conditions.
Examples:
Minimum Market Share
7%
Maximum Investment
$12M
Minimum Annual Growth
4%
Maximum Cost Increase
11%
These become important management thresholds.
Case Competition Insight
Judges love questions that expose assumptions.
For example:
"What if sales are 20% lower?"
"What if the investment costs more?"
"What happens if the market grows more slowly?"
"What if implementation takes another year?"
If your team has completed sensitivity analysis, you can answer:
"Our recommendation remains positive unless sales fall below approximately 82% of our base-case forecast."
That sounds very different from:
"We think the project will still work."
The first is analysis.
The second is hope.
Sensitivity Does Not Mean Weakening Your Recommendation
Some teams are afraid that showing uncertainty will make their recommendation look weak.
The opposite is often true.
Compare:
"We expect an NPV of $3.5 million."
with:
"Our base-case NPV is $3.5 million, and the project remains value creating across the tested range of 4–10% growth. The investment only becomes value destroying under a combination of very low growth and a higher discount rate."
The second recommendation sounds more credible.
Why?
Because you have demonstrated that you understand the risk.
Deciphering Cases
When you see a case with financial projections, look for assumptions.
Ask:
What must be true?
↓
How confident are we?
↓
What could change?
↓
Which assumptions matter most?
↓
Would the recommendation change?
↓
How can we mitigate the risk?
This is the mental process behind good sensitivity analysis.
Common Mistakes
Mistake 1 — Showing only the base case
A single forecast creates false precision.
Mistake 2 — Sensitizing everything
More analysis does not automatically mean better analysis.
Mistake 3 — Using unrealistic scenarios
A worst case of "everything goes wrong" isn't useful.
Mistake 4 — Changing assumptions without logic
If you use ±10%, explain why that range is reasonable.
Mistake 5 — Showing tables without insights
The judges don't need to interpret your spreadsheet.
You should interpret it for them.
Mistake 6 — Ignoring correlations
Some assumptions move together.
For example:
Lower sales may also create lower variable costs.
Be careful not to change assumptions independently when they logically interact.
Mistake 7 — Forgetting timing
A six-month delay can materially change NPV even if the ultimate cash flows remain the same.
Mistake 8 — Ignoring downside risk
A recommendation with tremendous upside but unacceptable downside may not be appropriate.
Mistake 9 — Treating uncertainty as a reason not to decide
Management still needs to make a decision.
Your job is to understand uncertainty and design a decision that manages it.
Presenting Sensitivity Analysis to Judges
Do not put your entire Excel sensitivity table on a slide.
Instead, identify the message.
For example:
Financial Robustness
Base NPV
$3.5M
Downside NPV
$1.1M
Upside NPV
$6.2M
Break-even growth
4%
Then state:
"The investment remains value creating across our reasonable downside range. The key risk is customer adoption, with the project becoming value destroying below approximately 4% annual growth."
That is enough.
The spreadsheet supports the conclusion.
The slide communicates it.
The Three Things Judges Need to Know
When presenting uncertainty, answer:
1. What could go wrong?
Identify the key risk.
2. How much does it matter?
Quantify the impact.
3. What will we do about it?
Provide a mitigation.
For example:
Risk: Customer adoption is lower than forecast.
Impact: NPV becomes negative below 4% growth.
Response: Launch a controlled pilot before committing the full investment.
This is a complete business argument.
Advanced Application
Sensitivity + Recommendation
The most powerful use of sensitivity analysis is when it changes how you implement the recommendation.
Imagine:
Base Case NPV = $5M
Worst Case NPV = -$2M
Best Case NPV = $10M
Instead of saying:
"The project is too risky."
consider:
"The project has significant upside, but the downside is driven primarily by customer adoption. We recommend a staged launch that limits the initial investment and establishes adoption thresholds before full deployment."
Now financial analysis has directly shaped the implementation strategy.
Mad Skills Drill
Take one investment recommendation from a previous case.
Identify the:
Three Most Important Assumptions
For each assumption, establish a reasonable range.
Then calculate:
-
NPV
-
IRR
-
ROI
across the range.
Now ask:
Does the recommendation change?
If it does, identify why.
If it doesn't, identify what makes the recommendation robust.
Advanced Mad Skills Drill
Create a decision boundary.
Complete this statement:
"We recommend __________ provided that __________ remains above/below __________."
Then identify a management action that could protect that threshold.
For example:
"We recommend the expansion provided that customer adoption remains above 7%. To protect against downside risk, we will stage the investment and expand only after the pilot achieves the 7% adoption threshold."
Now you've turned financial analysis into decision design.
Excel Toolkit
For case competitions, the core tools to know include:
Data Tables
Best for:
-
one-variable sensitivity
-
two-variable sensitivity
-
testing combinations of assumptions
Scenario Manager
Best for:
-
best/base/worst cases
-
changing multiple assumptions simultaneously
-
comparing defined scenarios
Goal Seek
Best for:
-
finding a break-even value
-
identifying the assumption required to achieve a target
For example:
What growth rate is required for NPV = $0?
Solver
Useful for more complex optimization problems where you are trying to determine the best combination of variables subject to constraints.
The important lesson:
Excel is the tool. Decision analysis is the skill.
Building the Financial Story
A strong financial story might follow this sequence:
Base Case
"Our investment creates $3.5M in NPV."
↓
Key Driver
"The result is primarily driven by customer adoption."
↓
Sensitivity
"NPV remains positive across our reasonable range of assumptions."
↓
Risk
"The primary downside occurs if adoption falls below 4%."
↓
Mitigation
"We will use a staged rollout with a 4% adoption threshold."
↓
Recommendation
"Proceed with the investment."
This is much stronger than simply showing:
NPV = $3.5M
Chapter Summary
Financial models are built on assumptions.
Those assumptions will never be perfectly accurate.
The purpose of sensitivity and scenario analysis is not to predict the future perfectly.
It is to understand how the decision behaves when the future is different from the forecast.
Sensitivity analysis helps you identify:
Which assumptions matter?
Scenario analysis helps you understand:
What happens when several assumptions change together?
Together, they help answer the most important question:
Is our recommendation robust enough to act on?
The strongest teams don't pretend uncertainty doesn't exist.
They understand it.
They quantify it.
They prepare for it.
And they build their recommendations accordingly.
Key Takeaways
✓ Never rely on a single point forecast.
✓ Identify the assumptions driving your financial model.
✓ Sensitize the assumptions that could change the decision.
✓ Don't waste time testing assumptions that don't materially matter.
✓ Use one-variable sensitivity to understand individual drivers.
✓ Use two-variable sensitivity to understand interactions.
✓ Use scenario analysis to test plausible combinations of assumptions.
✓ Build realistic—not catastrophic—worst and best cases.
✓ Identify the assumptions with the greatest financial impact.
✓ Find the break-even point or decision boundary.
✓ Use sensitivity analysis to identify risks that require mitigation.
✓ Consider staged investment when uncertainty is high.
✓ Present the insight, not the entire spreadsheet.
✓ Always connect uncertainty to a management action.
✓ A recommendation is stronger when you can explain not only why it works, but when it stops working.
Looking Ahead
We now know how to compare investments.
We also know how to test whether those investments remain attractive when assumptions change.
But uncertainty creates another challenge.
Sometimes we don't even know which outcome is most likely.
We may face:
-
multiple possible outcomes
-
different probabilities
-
incomplete information
-
competitive responses
-
market volatility
-
regulatory changes
-
strategic choices that affect future options
At that point, the question becomes bigger than:
"What happens if our assumptions change?"
The question becomes:
"How do we make good decisions when we don't know what the future holds?"
That takes us into the next stage of financial decision-making: Decision Making Under Uncertainty.
No Comments