Challenge Scenario
In quarterly compensation reviews, corporate bonuses are tied to weighted performance evaluations (40% Technical Execution, 30% Teamwork & Culture, 30% Delivery Quality). Employees qualify for tiered bonus percentages based on their overall score:
- Score >= 90: 15% of Base Salary (
F2 * 0.15).
- Score >= 80: 10% of Base Salary (
F2 * 0.10).
- Score >= 70: 5% of Base Salary (
F2 * 0.05).
- Score < 70: 0% Bonus (
0).
As a Compensation & Benefits Lead, you are finalizing the quarterly bonus pool:
- Weighted Score (Column E): Calculate using
=(B2*0.4)+(C2*0.3)+(D2*0.3).
- Bonus Payout $ (Column G): Apply tiered bonus percentage using nested IF:
=IF(E2>=90, F2*0.15, IF(E2>=80, F2*0.10, IF(E2>=70, F2*0.05, 0))).
- Top Performance Score (Cell E7): Find the highest individual score using
MAX(E2:E5).
- Total Bonus Pool Distributed (Cell G7): Sum total bonus payouts using
SUM(G2:G5).
Your Sheet Layout
Understanding the compensation review table:
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:D5 |
Employee, Scores (Tech, Team, Delivery) |
Performance Review |
Category ratings (1 to 100) |
Read-only review ratings. |
E2:E5 |
Weighted Score |
Composite Rating |
=(B2*0.4)+(C2*0.3)+(D2*0.3) |
Calculates overall weighted score. |
F2:F5 |
Base Salary ($) |
Base Pay |
Annual base employee compensation |
Read-only salary data. |
G2:G5 |
Bonus Payout ($) |
Incentive Allocation |
=IF(E2>=90, F2*0.15, IF(E2>=80, F2*0.10, IF(E2>=70, F2*0.05, 0))) |
Calculates dollar bonus earned. |
E7 |
Top Score |
Summary |
=MAX(E2:E5) |
Highest individual performer score (92.6). |
G7 |
Total Bonus Pool |
Summary |
=SUM(G2:G5) |
Combined bonus distribution ($16,400.00). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In cells E2:E5, calculate weighted score (= (Tech * 0.4) + (Team * 0.3) + (Delivery * 0.3)).
2
In cells G2:G5, calculate bonus payout (15% for >=90, 10% for >=80, 5% for >=70, 0% otherwise).
3
In cell E7, find the highest individual performance score using MAX.
4
In cell G7, sum the total bonus pool distributed across all employees.
Solution & Formula Breakdown
Let's review the weighted scoring and bonus allocation math:
1. Weighted Score Calculation (E2:E5)
=(B2*0.4)+(C2*0.3)+(D2*0.3)
- Sophia: (95 × 0.4) + (90 × 0.3) + (92 × 0.3) = 38 + 27 + 27.6 = 92.6.
- Marcus: (80 × 0.4) + (85 × 0.3) + (80 × 0.3) = 32 + 25.5 + 24 = 81.5.
- Amira: (70 × 0.4) + (75 × 0.3) + (70 × 0.3) = 28 + 22.5 + 21 = 71.5.
- Lucas: (65 × 0.4) + (60 × 0.3) + (65 × 0.3) = 26 + 18 + 19.5 = 63.5.
2. Tiered Bonus Payout (G2:G5)
=IF(E2>=90, F2*0.15, IF(E2>=80, F2*0.10, IF(E2>=70, F2*0.05, 0)))
- Sophia (92.6): >= 90 → $60,000 × 15% = $9,000.00.
- Marcus (81.5): >= 80 → $50,000 × 10% = $5,000.00.
- Amira (71.5): >= 70 → $48,000 × 5% = $2,400.00.
- Lucas (63.5): < 70 → $0.00.
3. Summary Metrics (E7 & G7)
- Cell E7 (Top Score):
=MAX(E2:E5) → 92.6.
- Cell G7 (Total Bonus Pool):
=SUM(G2:G5) → $16,400.00.
Always multiply weights that sum to 1.0 (0.40 + 0.30 + 0.30 = 1.00). This guarantees your weighted score remains on the standard 0–100 scale!