Difficulty
Intermediate
Estimated Time
20–30 minutes
The Problem
In real reporting, performance is rarely evaluated in isolation.
What matters is actuals versus targets—and how large the variance is.
Your task is to compare actual sales against monthly targets, calculate variances, and clearly identify under- and over-performance.
This is a core pattern used in finance dashboards, sales reporting, and management reviews.
Dataset
Create a worksheet named MonthlyPerformance with the following data:
| Month | Actual Sales | Target Sales |
|---|---|---|
| 2025-01 | 15100 | 15000 |
| 2025-02 | 14750 | 15500 |
| 2025-03 | 15900 | 15800 |
| 2025-04 | 16250 | 16000 |
| 2025-05 | 16800 | 16500 |
| 2025-06 | 17200 | 17000 |
Your Task
Part A — Calculate Variances
Add the following calculated columns:
- Variance (Absolute)
Variance = Actual Sales − Target Sales - Variance (%)
Variance % = (Actual − Target) / Target
Part B — Performance Flag
Add a column named Status:
"Above Target"if Actual ≥ Target"Below Target"if Actual < Target
Part C — Summary Insight
Create a small summary that answers:
- How many months were above target?
- How many months were below target?
Requirements
- Use Excel formulas only
- No manual tagging
- Calculations must update automatically
Expected Results (Example)
| Month | Variance | Status |
|---|---|---|
| 2025-01 | +100 | Above Target |
| 2025-02 | −750 | Below Target |
| 2025-03 | +100 | Above Target |
Hints (Optional)
- Use clear, readable formulas
- Format Variance % as a percentage
- Keep logic simple—avoid nested formulas where possible
Learning Objectives
By completing this challenge, you will learn how to:
- Compare actuals vs targets
- Calculate absolute and relative variances
- Build performance flags for reporting
Why This Matters
Variance analysis is used in:
- Budget tracking
- Sales performance reviews
- Financial dashboards
- Executive reporting
This challenge introduces performance accountability in Excel.
Next Challenge
Excel Challenge #8: Customer Contribution Analysis (Pareto / 80–20 Rule)
🔗 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 