Challenge Scenario
In sales performance management and corporate compensation planning, sales representatives receive a base commission percentage on their sales plus milestone bonuses when exceeding quota thresholds.
Building a clear, tiered compensation model ensures full transparency and accuracy in monthly rep earnings.
As a Sales Compensation Analyst, your tasks are to calculate monthly payouts for 4 sales reps:
- Base Commission (Column D): 5% (
0.05) of Total Sales in Column C (=C2 * 0.05).
- Tier Bonus (Column E): If Total Sales reach $10,000 or more (
C2 >= 10000), award a flat $500 bonus; otherwise award $0.
- Total Payout (Column F): Add Base Commission and Tier Bonus together (
=D2 + E2).
- Grand Total Payout (Cell F7): Sum all rep earnings using
SUM(F2:F5).
Your Sheet Layout
Understanding how the sales compensation model is organized:
- Sales Performance (Columns A to C): Shows Sales Rep name, territory Region (North, South, East, West), and monthly Total Sales ($8,500 to $15,400).
- Commission & Bonus Calculations (Columns D to F): Calculates 5% base commission, checks the $10,000 quota for $500 bonus, and combines total rep payout.
- Executive Payroll Summary (Cell F7): Grand total compensation expenditure across all sales representatives ($3,255).
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:C5 |
Sales Rep, Region, Total Sales |
Sales Performance |
Monthly sales performance figures |
Read-only representative sales volume. |
D2:D5 |
Base Commission |
Calculation |
=C2 * 0.05 |
Calculates 5% commission on Total Sales. |
E2:E5 |
Tier Bonus |
Incentive Bonus |
=IF(C2 >= 10000, 500, 0) |
Awards $500 bonus for sales >= $10,000, else $0. |
F2:F5 |
Total Payout |
Calculation |
=D2 + E2 |
Combines Base Commission and Tier Bonus. |
F7 |
TOTAL PAYOUT |
Payroll Summary |
=SUM(F2:F5) |
Adds up total payout for all salespeople ($3,255). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In Column D (D2:D5), calculate 5% Base Commission (Total Sales * 0.05).
2
In Column E (E2:E5), calculate Tier Bonus using IF (If Total Sales >= 10000, grant 500, else 0).
3
In Column F (F2:F5), calculate Total Payout by adding Base Commission and Tier Bonus.
4
In cell F7, calculate grand Total Payout using SUM.
Solution & Formula Breakdown
Let's examine how each calculation and tiered incentive is determined step by step:
1. Calculating 5% Base Commission (D2:D5)
=C2 * 0.05
Multiply Total Sales in Column C by 0.05:
- Sarah Connor ($12,000):
12,000 * 0.05 = 600 → $600
- John Miller ($8,500):
8,500 * 0.05 = 425 → $425
- Emily Davis ($15,400):
15,400 * 0.05 = 770 → $770
- Michael Chang ($9,200):
9,200 * 0.05 = 460 → $460
2. Awarding Tier Milestone Bonus with IF (E2:E5)
=IF(C2 >= 10000, 500, 0)
The IF function checks if Total Sales is at least $10,000:
- Sarah Connor ($12,000 ≥ 10,000): Earns $500 bonus.
- John Miller ($8,500 < 10,000): Earns $0 bonus.
- Emily Davis ($15,400 ≥ 10,000): Earns $500 bonus.
- Michael Chang ($9,200 < 10,000): Earns $0 bonus.
3. Combining Total Payout (F2:F5)
=D2 + E2
Add Commission + Bonus:
- Sarah Connor:
600 + 500 = 1,100
- John Miller:
425 + 0 = 425
- Emily Davis:
770 + 500 = 1,270
- Michael Chang:
460 + 0 = 460
4. Grand Total Compensation Summary (Cell F7)
=SUM(F2:F5)
Adding all four payouts gives the company payroll total of $3,255.
Separating Base Commission (Column D) from Bonus (Column E) makes your financial models easy to audit, explain to sales reps, and adjust if bonus rules change in future quarters!