Salary Structure Pay Compression Analyzer

Purpose and model boundary

This calculator analyzes a salary structure and the pay of up to 10 employees against it. You enter your salary grades, your pay regions and the employees, each with a grade, a region, a full-time equivalent (FTE), a salary and, optionally, tenure, a manager and a comparator group such as a job family. The workbook places each employee in a band adjusted for region, on the full-time basis, and reports compa-ratio, range penetration and any gap to the band. It checks four relationships you define: the premium a manager should earn over the highest-paid direct report, what an experienced employee should earn against matched new hires, the pay gap between adjacent grades, and the structure itself (midpoint progression and band overlap).

In the correction actions it then finds the minimum correction: the smallest raises that bring pay up to the band minimum, if you choose, and give every manager the required premium over their reports, then tests them against band maximums, a raise cap and a budget. A third action adds an 11 by 11 grid of correction budget against required manager premium.

Every figure on this page is computed by the spreadsheet; the page adds nothing to it. The model is educational and applies the pay policy you enter. It is not a pay-equity audit, does not test any protected characteristic, and is not legal advice.

Inputs and units

• Band definition: mode A takes each grade's minimum, midpoint and maximum; mode B takes a midpoint and a range spread.
• Salary grades, up to 30 rows, sorted by rank: a grade code (a capital letter followed by up to 15 capital letters or digits), a whole-number rank from 1 to 999 where a higher rank is a higher grade, the full-time midpoint in the base region, and either the range spread (mode B, above 0 and at most 500%) or the minimum and maximum (mode A). Rows can be pasted from a spreadsheet and are put in rank order.
• Pay regions, up to 10 rows: a region code and a multiplier above 0 and at most 5, applied to the band minimum, midpoint and maximum alike. Use one region at 1.00 if pay does not vary by place.
• Currency, a display label only, and an optional structure effective date, shown with the results.
• Salaries entered as: actual annual pay at each employee's FTE, or the full-time annual rate. Comparisons always use the full-time rate, which is actual pay divided by FTE.
• Employees, up to 10 rows, sorted by employee ID: a synthetic ID, never a name; the grade and region codes; the FTE, above 0 and at most 1; the annual base salary, above 0; tenure in years from 0 to 70, optional; the manager's employee ID, blank at the top of a chain; and a comparator group code, optional. Rows can be pasted and are put in ID order.
• Required manager premium: how much more a manager should earn than the highest-paid direct report, from 0% to 100%. Blank turns the relationship off.
• Comparator check: the tenure below which an employee counts as a new hire, the tenure at or above which one counts as experienced, the premium an experienced employee should earn over the highest-paid matched new hire (0% means at least as much), whether matches must share the grade and the region as well as the comparator group, and the fewest matched new hires needed. Blank tenure limits or premium turn the check off.
• Structure checks, each optional: the minimum midpoint progression to the next grade, the maximum overlap with the next band as a share of this band's width, the minimum average pay gap between adjacent grades, and the fewest employees each grade needs for that gap.
• Correction rules: whether to raise pay below the band minimum, whether a raise may take pay above the band maximum, an optional raise cap as a share of current full-time pay, and an optional yearly budget at actual FTE.
• The action, and for the grid the budget step and the premium step.

Money is in your currency per year; rates are percentages.

Governing relationships

Bands. In mode B the band is built so that the range spread is (maximum − minimum) / minimum and the midpoint sits halfway:

minimum = 2 × midpoint / (2 + spread), maximum = minimum × (1 + spread)

A 100,000 midpoint with a 50% spread gives 80,000 to 120,000, not 75,000 to 125,000. In mode A the three values are yours. Each employee's band is the grade's band times the region multiplier.

Position in the band. With full-time pay P and the employee's band minimum, midpoint and maximum,

compa-ratio = P / midpoint, range penetration = (P − minimum) / (maximum − minimum)

Penetration is never clamped, so it is below 0 under the band and above 1 over it. The gap to the minimum and the amount above the maximum are reported in money.

Relationships. A manager's premium achieved is the manager's full-time pay over the highest full-time pay among direct reports, less 1. An experienced employee is compared with the highest-paid new hire who shares the comparator group, and the grade and region when you require it; with fewer matches than you require, the employee is listed as a small sample instead of a gap. For each grade with a next-higher grade, the midpoint progression is the next midpoint over this one, less 1, and the overlap is the part of this band that the next band also covers, divided by this band's width. The adjacent-grade gap compares the average full-time pay, adjusted back to the base region, of the two grades. Ratios are rounded to 10 decimal places before a threshold is tested, so a value exactly on a threshold is never tripped by floating-point noise.

Minimum correction. The reporting lines form a hierarchy. The raise x for each employee, in cents and on the full-time basis, is the smallest set that meets

x = max( floor raise, max over direct reports of [ (1 + premium) × (report's pay + report's raise) − own pay ] )

where the floor raise is the gap to the band minimum when that rule is on and 0 otherwise. The workbook starts from no raises and repeats this step up the hierarchy, as many times as the hierarchy can be deep, so each manager's raise accounts for the raises below. Raises are rounded up to the cent so that every relationship still holds after rounding. Because each requirement can only push pay up, this least solution is the smallest raise for every employee at once, and so has the lowest total cost; the result is marked optimal when the workbook's certificate check confirms it. Caps never change this point. If it would take anyone above the band maximum (when that rule is on) or past the raise cap, no correction can meet every rule, and the result is infeasible, with the employees named. If its yearly cost at actual FTE exceeds the budget, the result is infeasible with the minimum required budget and the shortfall.

Scenario grid. Rows are 11 correction budgets centred on your budget, or on the minimum required budget when the budget is blank, one budget step apart. Columns are 11 required manager premiums centred on yours, one premium step apart. For each premium the workbook recomputes the minimum correction; each cell then reads the budget less its cost (negative is a shortfall). A cell whose premium cannot be met within the caps, or whose budget or premium is below zero, has no figure; its reason is in the reasons table beside the grid, which has the same layout. The first column of the grid gives each row's budget, the column headings count premium steps from −5 to +5, and the premium of each step is listed below the grid. The centre cell equals the base case.

Calculation sequence

The spreadsheet first checks the inputs and each grade, region and employee row, including that every grade and region code exists and that no reporting chain loops. It then builds the bands, places each employee, measures the manager, comparator and grade relationships, and, in the correction actions, runs the minimum correction, its certificate and the cap and budget tests. In the grid action it repeats the correction for each premium. A block of consistency checks confirms the arithmetic, including that the grid's centre cell equals the base case, before the status is returned.

Outputs and interpretation

The headline counts the employees below and above their band and the managers short of the required premium, and gives the yearly cost of the minimum correction and the minimum required budget. The cards also show the employees analyzed, the actual payroll, the managers and experienced employees checked and the gaps found, the grade flags, the correction on the full-time basis, the budget unused or short, the employees raised, the employees over a cap, what the correction would cost if no cap applied, and payroll after the correction. The employee table gives each person's band, compa-ratio, penetration, gaps, manager and comparator checks, minimum and recommended raise and the reason it binds; the grade table gives each grade's band, spread, progression, overlap, headcount, average pay and adjacent-grade gap. The scenario grid shows the budget margin for each budget and premium.

Any warnings and findings appear under the results. The model version and the other run details are in the downloaded CSV and JSON files. The per-row check tables are returned to the API and MCP, and the first problem they find is named in the status. When a correction is infeasible, the analysis and the diagnostics stay on the page and the raises are withheld; a button copies the minimum required budget into the budget input.

Under the default inputs, five grades in mode B, two regions and ten employees in a reporting tree five levels deep: one employee is 1,979.60 below the band minimum, one is above the band maximum, and one manager earns 62,000 where 1.1 times the highest-paid report, 67,100, is required. Grade G2 overlaps G3 by 60.89% of its width, above the 60% limit. The minimum correction raises those two employees by 1,979.60 and 5,100.00, costs 7,079.60 a year, and leaves 2,920.40 of the 10,000 budget unused; it is optimal and the status is OK.

The first chart draws each employee's band with current and corrected pay. The second shows each manager's premium against the required one, and the third the correction cost by grade.

Validation and status logic

The workbook returns one status, chosen in this order: NOT VALID, then INFEASIBLE, then CHECK, then OK.

• NOT VALID names the first problem it finds: a scalar input outside its limits (including an experienced-tenure limit below the new-hire limit); then the grade rows (a gap, a blank code, rank or midpoint, the code form, a repeated code or rank, the band values the mode needs, and a band whose minimum, midpoint and maximum are out of order), the region rows and the employee rows (a gap, required blanks, the ID form, FTE and salary limits, tenure, a repeated ID, a grade or region that is not in its table, the manager ID form, and a reporting chain that loops); and rows not in rank or ID order. A loop in the reporting lines is refused in every action. NOT VALID is a refusal, not a numeric answer: every figure is withheld.
• INFEASIBLE means no correction meets every rule: an employee would need a raise above the band maximum or the raise cap, or the correction costs more than the budget. The raises are withheld; the analysis, the employees over a cap, the cost if caps were lifted, the minimum required budget and the shortfall stay visible.
• CHECK accompanies a computed result that needs the stated review: an employee names a manager who is not in the list and is treated as the top of a chain; mode B ignores minimum or maximum values you entered; or a correction action has no relationship selected, so the correction is zero.
• OK otherwise.

The findings (employees below or above band, manager and comparator gaps, and grade flags) and the small-sample notes appear in the warnings list. They are the analysis itself, so they do not change the status.

Assumptions and limitations

• Capacities are 10 employees, 30 grades and 10 regions; with 10 employees no valid reporting chain is deeper than 10 levels.
• One currency and one effective date apply to the whole structure, and nothing is converted.
• Only band floors, band maximums, the raise cap, the budget and the manager premium enter the correction. Comparator gaps and grade-structure findings are flagged, not corrected.
• The budget-limited mode that would trade off unmet relationships against a fixed budget is not offered; the scenario grid instead shows what each required premium costs against a range of budgets.
• Raises are cent amounts rounded up inside the correction, so the optimality holds over whole cents. Actual-pay figures round half away from zero.
• Tenure is entered as decimal years; no hire dates are used.
• No reduction in pay is ever proposed for an employee above the band maximum.
• Comparator groups are your job-family or peer-group codes. The calculator never uses a protected personal characteristic and does not test pay equity under any law.

Restrictions and non-computing states

The calculator enforces its numeric and choice limits before it accepts a result. A gap between rows, a repeated code or ID, a grade or region missing from its table and a reporting loop are refused by the workbook. In the Analyze action the correction outputs are left blank, and the grid is left blank unless that action is chosen.

Errors and warnings

A request rejected by the page's input limits is not a computed result. A transport or service failure, a timeout or a cancelled run cannot establish a fresh workbook answer, and the last result stays on the page marked as out of date. Review the workbook's References and Disclaimer, and your actual pay policies, before using a result for a real decision.

Sources and further reading

Your grades, regions, employees and pay rules come from the inputs; the calculator holds no statutory or market data. The public sources below show how a published pay structure is built and where to find market pay data, and set out the legal frame that a pay analysis sits in. They do not endorse this calculator.

• U.S. Office of Personnel Management, General Schedule describes a public pay structure of 15 grades, each with a range of step rates, plus locality payments by geographic area, the same idea as the calculator's grades and region multipliers (retrieved 2026-10-26).
• U.S. Office of Personnel Management, Salaries and Wages publishes the General Schedule and locality pay tables for each year (retrieved 2026-10-26).
• U.S. Bureau of Labor Statistics, Occupational Employment and Wage Statistics publishes yearly pay estimates for about 830 occupations by nation, state and metropolitan area, a public starting point for setting band midpoints and regional differences (retrieved 2026-10-26).
• U.S. Equal Employment Opportunity Commission, Equal Pay/Compensation Discrimination summarizes the federal laws that bar pay discrimination, which this calculator does not test (retrieved 2026-10-26).
• EEOC Compliance Manual, Section 10: Compensation Discrimination explains how similarly situated comparators are identified from job content and how factors such as experience can explain a pay difference (retrieved 2026-10-26).

This is an educational analysis of the pay policy you enter, not legal, tax or compliance advice. It does not certify pay equity or compliance with any employment law; review a result with a qualified professional before acting on it.