Difficulty
Advanced
Estimated Time
70–95 minutes
The Problem
Executives rarely plan against a single outcome.
They ask:
- “What happens if growth slows?”
- “What if pricing or volume changes?”
- “How sensitive are results to our assumptions?”
In this challenge, you’ll build a scenario-based forecast model that shows how results change under different assumptions using what-if analysis and sensitivity tables.
Dataset
Use the monthly Actuals from Challenge #19 (or recompute them from SalesData):
| Month | Actual Sales |
Focus on a forward-looking forecast horizon (e.g., next 6–12 months).
Your Task
Part A — Base Forecast Model
Create a simple forward forecast:
Controls:
- Starting Value (last actual month)
- Monthly Growth Rate
- Forecast Months (e.g. 6 or 12)
Output:
- Monthly forecast values
- Total forecasted sales
Part B — Scenario Manager (Best / Base / Worst)
Define three scenarios:
| Scenario | Growth Rate |
|---|---|
| Best | +8% |
| Base | +4% |
| Worst | −2% |
Build logic so that selecting a scenario:
- Updates the forecast automatically
- Recalculates totals and KPIs
(Use a dropdown + lookup pattern.)
Part C — Sensitivity Table (1-Variable)
Create a data table showing how Total Forecast changes as Growth Rate varies:
| Growth Rate | Total Forecast |
|---|---|
| −5% | |
| 0% | |
| 2% | |
| 4% | |
| 6% | |
| 8% |
Use Excel’s Data Table (What-If Analysis).
Part D — Sensitivity Table (2-Variable) (Advanced)
Optional but recommended:
- Growth Rate (rows)
- Forecast Months (columns)
- Output: Total Forecast
This shows how both assumptions interact.
Part E — Insight Callouts
Add KPI callouts:
- Selected Scenario
- Total Forecast
- Upside / Downside vs Base
Requirements
- Use Excel what-if tools (Data Table)
- Use formulas only for calculations
- No Pivot Tables
- Model must update instantly when assumptions change
Learning Objectives
By completing this challenge, you will learn how to:
- Build scenario-based forecasting models
- Perform sensitivity analysis in Excel
- Communicate uncertainty to decision-makers
- Design robust what-if dashboards
Next Challenge
Excel Challenge #21:
🔗 View reference solution on GitHub
(After you’ve tried the challenge)
Want more practical Excel challenges?
Subscribe to the Solve With Excel newsletter and get new problems delivered to your inbox.
SolveWithExcel 