PROJECT 02Intermediate

Variable-based Bonus Calculator

Awaiting founder definition

The founder brief names a "VO DEP-based bonus calculator". The VO DEP rule is not defined, so this project uses a transparent, generic plan-attainment bonus scheme. The VO DEP variant will be added once its definition is supplied.

The scenario and data are provided, but tasks that depend on the undefined business rule are marked and must not be assumed.

Business scenario

Orion People's sales department pays a quarterly variable bonus. Managers want a calculator they can audit, adjust and explain to staff.

Problem statement

Build a bonus calculator that turns monthly plan attainment into a bonus amount per employee under a configurable scheme, and estimate total cost.

Learning objectives

Translate a compensation policy into exact formulas
Separate inputs (assumptions) from calculations
Test edge cases and estimate cost sensitivity

Instructions

Generic scheme (configurable): attainment = actual ÷ plan per quarter. Below 80% → 0. From 80% to 100% → bonus rises linearly from 20% to 100% of target bonus. Above 100% → +2 percentage points of target bonus per 1% over plan, capped at 150% of target bonus. Target bonus = 15% of quarterly salary. All thresholds must be input cells, not hard-coded.

Tasks

Aggregate sales_performance to employee-quarter in SQL.
Build an assumptions sheet with every threshold and rate.
Implement the payout curve in Excel (IFS/LET or lookup table) and test at 79%, 80%, 100%, 125% and 160% attainment.
Total the 2025 bonus cost by quarter and by job level.
Show the cost impact if the 80% threshold moves to 85%.

Deliverables

Excel calculator with assumptions sheet
SQL aggregation
One-page explanation for managers

Evaluation rubric

Criterion	Weight	Excellent	Proficient	Developing	Insufficient
Formula correctness	30%	Payout curve exactly matches the policy at every boundary; tested.	Correct and complete with minor gaps in rigour or presentation.	Partly correct; important steps missing, unvalidated or unclear.	Missing, incorrect, or not supported by the data.
Model structure	20%	Inputs separated, no hard-coded constants, auditable.	Correct and complete with minor gaps in rigour or presentation.	Partly correct; important steps missing, unvalidated or unclear.	Missing, incorrect, or not supported by the data.
Data preparation & validation	20%	Correct filters and grain; validation checks documented and totals reconciled.	Correct and complete with minor gaps in rigour or presentation.	Partly correct; important steps missing, unvalidated or unclear.	Missing, incorrect, or not supported by the data.
Sensitivity & cost	15%	Threshold scenario quantified correctly.	Correct and complete with minor gaps in rigour or presentation.	Partly correct; important steps missing, unvalidated or unclear.	Missing, incorrect, or not supported by the data.
Presentation	15%	Concise, well-labelled outputs a manager can act on without explanation.	Correct and complete with minor gaps in rigour or presentation.	Partly correct; important steps missing, unvalidated or unclear.	Missing, incorrect, or not supported by the data.