Fair Pay Architecture: Job Grading, Range Design, Compa-Ratios & Merit Budgets

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.

• Score eight jobs on four weighted factors with SUMPRODUCT, place each job in a grade with an approximate-match lookup and check how much of a senior job's score comes from one factor.
• Bring three salary surveys with different effective dates to one target date with compound aging, then blend them with quality weights into one market rate per benchmark job.
• Build six grade midpoints from a market anchor and a progression rate, derive each range's minimum and maximum from its spread and measure how much neighbouring grades overlap.
• Measure where each employee sits in their grade with the compa-ratio and range penetration, label everyone below the minimum or above the maximum with IFS and highlight the red circles.
• Calculate the raw gender pay gap, then the gap inside each grade with AVERAGEIFS, flag the grades above a review threshold and see why the two numbers tell different stories.
• Look up each employee's merit increase in a two-way matrix of performance rating and compa-ratio band, total the spend, compare it with the budget pool and find the factor that brings the matrix exactly on budget.
• Cost every green circle up to the range minimum and measure every red circle above the maximum with MAX(0, …), total both by department and express the fix as a share of payroll.
• Summarise the structure per grade (headcount, average compa-ratio, people below the minimum) and draw every salary against its grade in a scatter chart, so outliers are visible at a glance.