A complete, executive-ready compensation planning system that helps you build and manage a structured salary framework in Excel—covering pay grades, job mapping, market benchmarking, compa-ratio analysis, merit planning, budget variance, pay equity, bonus design, and total rewards.
This workbook is built for real HR + Finance workflows. All sheets are connected using standard Excel formulas (no VBA, no macros). You enter data once in the input tables, and the dashboard and analytics update automatically—so you can make faster, more defensible compensation decisions and present them clearly to leadership.
Who this template is for
HR Managers, HRBPs, Compensation & Benefits teams, Total Rewards specialists, Finance leaders, and People Ops teams who need a structured way to control pay decisions across departments and grades.
What’s inside (Sheet-by-Sheet Content)
1) Instructions
A quick-start guide that explains how the workbook flows, the recommended setup sequence, and where to enter inputs first (grades → jobs → employees → benchmarks → analysis).
2) Dashboard
An executive overview page that summarizes your compensation picture at a glance.
Includes KPI cards and visuals for:
- Total Employees
- Total Base Payroll
- Average Salary
- Average Compa-Ratio
- Budget Variance
- Charts show: headcount by department, performance rating distribution, market positioning, compensation cost by department, compa-ratio distribution, and pay equity status.
- Also includes a “Key Action Items & Recommendations” panel that highlights what needs attention.
3) Chart Data
A structured backend sheet that feeds all dashboard charts and KPI visuals. It consolidates totals, distributions, and chart-ready tables so the dashboard stays clean and fast.
4) Pay Grade Matrix
Your salary range design engine. Defines pay bands for each grade, including:
- Grade and grade level
- Minimum, midpoint, maximum salary
- Range spread %
- Midpoint progression (midpoint diff %)
- Hourly min / hourly midpoint / hourly max
- Annual hours assumptions
- Notes per grade (role guidance)
5) Job Classification Catalog
A job architecture and mapping database that links roles to the salary structure.
Typically includes:
- Job code and job title
- Pay grade and grade level
- Department and job family
- FLSA status (exempt/non-exempt)
- Employment type (full-time/part-time/contract)
- Location tier (if you use geo-based pay logic)
- Effective date
- This sheet ensures every role is governed by a consistent framework.
6) Employee Roster
The centralized employee database used to run all compensation analysis.
Includes fields like:
- Employee ID, first/last name
- Job title, department, pay grade, grade level
- Current salary
- Hire date
- Performance rating
- FLSA status, employment type
- Employee status (active, etc.)
- Manager
- All downstream calculations pull from this roster.
7) Salary Benchmarking — Market Data
Your market comparison sheet for salary surveys and benchmarking inputs.
Captures:
- Job code / benchmark title
- Pay grade
- Survey source and survey year
- P25, P50 (median), P75
- Your midpoint (from Pay Grade Matrix)
- Market ratio (your midpoint vs market)
- Market positioning label (At Market / Below Market / Above Market)
- Data date
- This sheet powers market positioning analysis on the dashboard.
8) Compensation Analysis
The core analytics sheet that turns employee pay into decision-ready insights.
Auto-calculates and displays:
- Grade minimum / midpoint / maximum (pulled from grade matrix)
- Compa-Ratio
- Range penetration %
- Compa-ratio status (Within range / Above range / Below range)
- Performance rating visibility
- Action needed (On Track / Monitor / Review)
- This is the sheet you use for adjustments, promotions, and market corrections.
9) Merit Increase Matrix
A structured merit planning model that recommends increase percentages based on:
- Performance rating
- Compa-ratio quartile position
- Includes editable budget assumptions such as:
- Overall merit budget %
- Promotion budget %
- Market adjustment reserve %
- Total comp increase budget %
- The matrix is designed to guide fair, consistent increases aligned with market and internal positioning.
10) Salary Bands by Department
A department-level summary view that converts the roster into leadership insights.
Typically summarizes:
- Department headcount
- Average salary, median salary
- Min/max salary by department
- Total payroll and % of total payroll
- Average compa-ratio
- Average tenure
- Average performance score
- Useful for department reviews and workforce planning discussions.
11) Compensation Budget Summary
A full employer cost model by department to support budgeting and forecasting.
Includes:
- Base salary total
- Bonus pool (target)
- Benefits cost
- Payroll taxes
- Total compensation cost
- Cost per employee
- Budget allocated
- Variance (positive/negative)
- This sheet directly supports budget planning and budget variance tracking in the dashboard.
12) Pay Equity Analysis
A focused pay equity view (shown as gender pay equity in your screenshots) that highlights where deeper review is required.
Includes:
- Male/Female headcount by department
- Male average salary vs female average salary
- Pay gap ($) and pay gap (%)
- Equity status (Equitable / Monitor / Review Required)
- Action required
- Also includes an overall equity summary section to quantify how many departments require review.
13) Bonus & Variable Compensation Structure
A grade-based bonus framework that sets clear rules for variable pay.
Includes:
- Target bonus %, threshold %, maximum % by grade
- Midpoint salary and bonus $ calculations
- Eligibility rules by grade
- Performance payout multipliers (individual, company, combined)
- This sheet helps standardize variable compensation across the organization.
14) Benefits & Total Rewards Overview
A total rewards presentation layer that converts base pay into full compensation value.
Includes:
- Base midpoint
- Health & medical
- 401(k) match
- PTO value
- Life & disability
- Other benefits
- Total benefits and benefits as % of base
- Total rewards value by grade
- Great for leadership presentations and employee-level rewards communication.
15) Lookup Tables
A controlled list library used for dropdowns and validations across the workbook (departments, job families, grades, statuses, rating scales, tiers). This reduces input errors and keeps reporting consistent.
Key outcomes you get from this template
- A consistent, scalable salary structure with clear governance
- Market-aligned midpoint comparisons (P25/P50/P75)
- Employee-level insights (compa-ratio, range penetration, action flags)
- Merit increase planning that supports fairness and budget control
- Department payroll, total comp cost, and variance visibility
- Pay equity monitoring and priority review targeting
- Bonus framework and total rewards view for leadership reporting
