Difficulty
Intermediate
Estimated Time
25–35 minutes
The Problem
In many businesses, a small number of customers generate a large share of revenue.
This challenge introduces contribution analysis, often referred to as the Pareto (80–20) principle.
Your task is to calculate how much each customer contributes to total sales, compute cumulative contribution, and identify the customers that make up the top 80% of revenue.
This pattern is widely used in sales analysis, account management, and executive reporting.
Dataset
Create a worksheet named CustomerSales with the following data:
| Customer | Total Sales |
|---|---|
| Northwind BV | 2780 |
| Atlas NV | 1090 |
| Orion SA | 1060 |
| Delta Group | 1035 |
| Bluewave Ltd | 290 |
| Silverline Co | 220 |
| Apex Solutions | 180 |
| Horizon GmbH | 145 |
Your Task
Part A — Percentage Contribution
Add a column named Contribution %:
Contribution % = Customer Sales / Total Sales (All Customers)
Format as a percentage.
Part B — Sort by Impact
Sort customers by Total Sales (descending) so the highest contributors appear first.
Part C — Cumulative Contribution
Add a column named Cumulative % that:
- Adds each customer’s Contribution % to the sum of all previous customers
- Starts at the top and increases down the list
- Ends at (or very close to) 100%
Part D — Top 80% Flag
Add a column named Top 80% Flag:
"Yes"if the customer falls within the first 80% of cumulative contribution"No"otherwise
Requirements
- Use Excel formulas only
- No manual totals
- Calculations must update automatically if values change
Expected Insight
You should be able to clearly answer:
- Which customers generate most of the revenue?
- How many customers make up ~80% of total sales?
- Where diminishing returns begin
Hints (Optional)
- Use absolute references for total sales
- For cumulative totals, reference the previous row
- Keep formulas copy-friendly
Learning Objectives
By completing this challenge, you will learn how to:
- Perform contribution analysis in Excel
- Build cumulative calculations
- Apply the Pareto principle to business data
Why This Matters
Contribution analysis is used in:
- Key account management
- Revenue concentration analysis
- Pricing and discount strategies
- Strategic sales planning
This challenge teaches focus-driven analysis, not just totals.
Next Challenge
Excel Challenge #9: Monthly Sales Dashboard Summary (KPI Cards + Trend)
🔗 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 