Excel Sensitivity Analysis: How to Test Your Forecast Assumptions

Why Excel Sensitivity Analysis Matters

When I'm modeling different outcomes—whether it's a budget, an investment opportunity, or a simple reforecast—I need to understand the impact that individual drivers have on our outcomes.

Some people don't realize that one small change in one area might drive an outsized impact in another. That's what sensitivity analysis reveals.

The Scenario

We have a revenue forecast model with these assumptions:

Base price: $100/month (MRR)
Starting customers: 1,000
Proposed price increase: 10%
Base monthly churn: 1%
Base new customer growth: 2%/month

But here's the uncertainty: What happens to churn and new customer acquisition when we raise prices?

Assumptions we're testing:

For every 2.5% price increase, churn increases by 0.5%
For every 2.5% price increase, new customer growth decreases by 0.25%

Building the Data Table

Excel's Data Table feature lets you test multiple scenarios simultaneously.

Step 1: Define Your Output

Pick the number you care about. For us, it's full-year revenue.

Step 2: Create Your Axes

Churn impact (horizontal): 0%, 0.25%, 0.50%, 0.75%, 1.00%

New customer impact (vertical): -0.15%, -0.20%, -0.25%, -0.30%, -0.35%

Step 3: Set Up the Data Table

Highlight the entire matrix
Go to Data → What-If Analysis → Data Table
Row input cell: The churn impact assumption
Column input cell: The new customer impact assumption

Confusing tip: "Row input" means the variable that's displayed in a single row (horizontal). "Column input" means the variable displayed in a column (vertical).

Reading the Results

With conditional formatting, you can immediately see:

Green: Higher revenue scenarios
Red: Lower revenue scenarios

Our model shows about $400,000 fluctuation across the range of assumptions. That's significant.

The Real Insight

Looking at the sensitivity table:

If we're 1% off on churn impact, that's a ~$400K revenue swing
If we're 0.2% off on new customer impact, that's a ~$170K swing

Churn is more sensitive than new customer acquisition in this model. That tells us where to focus our attention.

What To Do With This

Here's where most analysts go wrong: they think they're supposed to have all the answers.

Don't present this with a recommendation. Present it as a conversation piece.

Call in the experts:

Sales leadership
Customer success
Marketing
The CEO

Get them in the room. Play with the model together. Let them debate the assumptions.

The Analyst's Role

You are not making the pricing decision. You are the guide.

Your job is to:

Build something people can touch and interact with
Surface the sensitivity of different assumptions
Facilitate better conversations
Iterate until everyone feels comfortable

Good data drives good questions. Good questions drive better data.

The Uncomfortable Truth

Ask 15 analysts to create a forecast and you'll get 15 different answers.

The only sure thing: they will all be wrong.

Forecasts aren't about accuracy—they're about precision. How well can you understand and demonstrate control over specific drivers?

Sensitivity analysis helps you see the elasticity around your assumptions so you can put fences around the unknowns.

The Bottom Line

Sensitivity analysis is the tip of the iceberg, but it captures the essence of good forecasting:

Be thoughtful about assumptions
Be open about uncertainty
Facilitate conversation
Iterate toward decisions people feel good about

That's what makes this field exciting.

Excel sensitivity analysis isn't just a technical skill—it's how you facilitate better conversations and build confidence in your forecasts.

Common Questions About Sensitivity Analysis

Q: Do I need to use Excel's Data Table feature or can I do this manually?

You can do it manually, but for anything more than 2-3 scenarios, Data Tables save massive time. Learn the tool—it's worth it.

Q: How many variables should I test in a sensitivity analysis?

Start with 1-2 key drivers. Don't try to test everything at once. Focus on the assumptions with the biggest impact or the most uncertainty.

Q: Should I present all these scenarios to executives?

No. Use sensitivity analysis to understand the model yourself. Then present 2-3 scenarios (best case, worst case, most likely) in the actual meeting. The full sensitivity table is for your prep work.