Performance Calibration & Bonus Pool Math: Rating Curves, Multipliers & Payouts

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Count the year-end ratings per manager with COUNTIFS, compare the company distribution with the target curve in percentage points and flag the manager whose top ratings run far above target: the starting point of every calibration meeting.
• Calculate each team's mean and standard deviation, turn every raw score into a z-score within its own team with STANDARDIZE, and flag the managers whose scores sit far from the company mean.
• Turn calibrated z-scores into a percent rank with PERCENTRANK.INC, map it to a 1–5 rating with an approximate XLOOKUP, rank the group with RANK.EQ and find the z-score cut-off for the top quarter.
• Weight three company KPIs into one achievement figure, read the funding % off a threshold–target–maximum curve by linear interpolation, size the target pool from the salary bill and fund it for the year.
• Look up each employee's target bonus % by grade and the multiplier by rating, combine them with the company factor into the payout, and total the payouts in one SUMPRODUCT to compare them with the funded pool.
• Count each employee's days inside the plan year with DAYS, MAX and IF, turn them into a pro-rata factor, apply the FTE % of part-timers and set the bonus to zero for anyone below the minimum service days.
• Scale uncapped payouts down to the funded pool with one factor, round each payout to whole currency units, measure the rounding residual and hand it to the largest payout so the total matches the pool to the unit.
• Sum the final payouts per department with SUMIFS, measure how much of each department's pool is used, chart cost against pool and test whether payouts relative to target are fair across grades.