Salary Benchmark Analyzer
Compares employee compensation against market benchmarks by computing comp ratios, identifying underpaid and overpaid positions, and estimating the budget needed to bring all roles to target market position. Supports benchmarking by role, level, and location.
HR - Salary Benchmark Analyzer.xlsx
Excel (.xlsx) — No macros — Works in Excel, Google Sheets, LibreOffice
What This Spreadsheet Solves
- No structured way to compare internal pay to market rates
- Underpaid employees leave but the gap is not quantified
- Overpaid roles inflate costs without a flag to finance
- Compensation adjustments lack a data-driven budget estimate
- No comp ratio tracking across the organization
Who This Is For
- Compensation and benefits managers
- HR directors conducting annual pay reviews
- Finance partners budgeting for salary adjustments
- People analytics teams building comp dashboards
Inputs
- textEmployee / Role Title
- $Current Salary
- $Market Benchmark (50th Percentile)
- %Target Percentile Position
- textDepartment
Outputs
- Comp ratio per employee (salary / benchmark)
- Market position label (below, at, above market)
- Adjustment needed to reach target percentile
- Total adjustment budget by department
- Distribution of comp ratios across the organization
How Calculations Work
Comp ratio is Current Salary / Market Benchmark. A ratio below 1.0 means the employee is paid below the 50th percentile. The target adjustment is calculated by multiplying the benchmark by the target percentile factor and subtracting the current salary. Department totals sum all adjustments within each group. The distribution view shows how many employees fall in each comp ratio band.
Example Use Case
Scenario: A product manager earns $110,000 with a $120,000 market benchmark. A designer earns $95,000 with a $88,000 benchmark. Target position is 50th percentile.
Result: Product manager comp ratio: 0.92 (below market), needs $10,000 adjustment. Designer comp ratio: 1.08 (above market), no adjustment needed. Department total adjustment: $10,000.
What You Get — 5 Sheets
Technical Details
Frequently Asked Questions
Where do I get market benchmark data?
Use salary surveys from Radford, Mercer, Payscale, Glassdoor, or Levels.fyi. Enter the 50th percentile (median) for each role.
What is a good comp ratio?
A comp ratio of 1.0 means the employee is paid at the market median. Most organizations target 0.95-1.05. Below 0.90 signals a retention risk.
Can I target a percentile other than 50th?
Yes. Enter the target percentile in the INPUT sheet. The LOGIC sheet adjusts the benchmark accordingly using the formula: benchmark * (target percentile / 50).
How do I account for location differences?
Use location-adjusted benchmarks. If your survey provides geographic differentials, apply them before entering the benchmark value.
How often should I refresh benchmarks?
Annually, aligned with your compensation review cycle. Update more frequently in competitive markets with rapid salary inflation.
Download Salary Benchmark Analyzer
Ready to use immediately. Enter your data in the INPUT sheet, see results in OUTPUT.