The workbook turns 55 inputs on one sheet into a January to December 2027 budget for people cost and the HR function, then tests which input moves the total most. In the worked example, which uses invented figures for a 120-person company, the 2027 total is USD 13,897,046. Headcount and average base pay carry the money: a 10% change in either moves that total by USD 1,373,497 or USD 1,210,611, against USD 27,999 for a 10% change in the merit pool. Timing matters too: shifting the merit effective month by one month moves the example by about USD 30,000, more than a 10% change in the merit pool.

Each published input in the table further down names who published it and when, with its scope and next update, so a finance partner can trace a blue cell to a document. Figures are as of October 27, 2026. Pay budget forecasts are not repeated here; they sit in salary increase budgets for 2027.

What each sheet does

Blue text on a yellow cell is an input, black text is a formula, and the Checks sheet turns red when totals do not tie. The file has no hidden sheets and no macros, and it does not link to other files.

Benefits are split in two on purpose. Pay-linked benefits, such as a retirement match, are a percentage of base pay. Per-head benefits, such as the medical plan, start from the 2026 cost per head and grow by their own trend, because plan costs move with claims and carrier renewals while pay moves with merit. Merit and promotions start in their own effective months, so the Summary shows the 2027 cost and the part of the full-year cost that falls into 2028. Contingency is a separate line with a switch that leaves base pay out of it.

How to fill it, in order

Keep every input traceable to a document that finance can open.

1. Headcount. Enter year-end 2026 headcount, then planned average headcount for each month of 2027 from your workforce plan. The Checks sheet fails when December headcount less opening headcount is larger than the hires planned in any scenario.
2. Pay. Take average base pay per head from the payroll register, before any 2027 increase. Add the register’s latest monthly base payroll in the optional tie-out block, and Checks compares it with opening headcount times average pay divided by 12, failing above a 2% gap by default.
3. Employer costs. Divide last year’s employer payroll taxes by base pay for the tax rate. Do the same for pay-linked benefits, and divide carrier invoices by average headcount for per-head benefits. Statutory rates are a start, and the UK example below shows why.
4. Scenarios. Fill the three scenario columns, Low to High, for the six scenario drivers. Take merit from the salary budgets page, the benefits trend from your carrier renewal and the table below, and hires from your recruiting plan template.
5. HR spend. Enter training per head and HR technology fees (fixed and per head), then other HR costs from contracts and 2026 actuals.
6. 2026 actuals. Type a 2026 actual for every line, base pay to other HR costs (0 where a line had no cost; contingency may stay blank), and the 2026 average headcount on the Summary, or clear them all, including the contingency line, which ships as 0. A missing cost line reads CHECK, and the total comparison and the bridge show n/a. A missing 2026 headcount leaves only the bridge at n/a.
7. Checks. Work down the Checks sheet until it reads ALL OK, then share the Summary.

A worked example with invented numbers

The example company has 120 employees at the end of 2026 and an average base pay of USD 85,000. Headcount reaches 126 in December, with 24 hires planned at USD 4,000 each. Merit is 3.5% on the 90% of payroll that is eligible and takes effect in April. Promotions add 1.0% from July. Employer payroll taxes are 8.0% of pay and pay-linked benefits 4.0%. Per-head benefits are USD 11,000 and grow 8.0%, and contingency is 2.0% of every line except base pay. Other inputs are the defaults: training USD 900 per head, HR technology USD 36,000 plus USD 14 per head per month, other HR costs USD 24,000, hires phased 30%, 30%, 25% and 15% by quarter, and 6 employees at the wage base. The figures are invented to show the formulas at work, and none of them is a benchmark.

The total rises USD 1,580,046 on the invented 2026 actuals. The Summary bridge splits that into USD 778,661 from a higher average headcount (123.3 against 116) and USD 801,385 from a higher cost per head. The three scenarios give USD 13,693,087 (Low), USD 13,897,046 (Base) and USD 14,136,492 (High), which is 1.5% below and 1.7% above Base.

Merit and promotions cost USD 302,579 in 2027, made up of USD 249,454 for merit and USD 53,125 for promotions. Over a full 12 months at the same headcount they would cost USD 435,058, so about USD 132,480 of the full-year cost, 30%, falls into 2028 before taxes and benefits. A finance partner who sees only the 2027 line will not see that second number unless you show it.

Which assumptions move the total most

The Sensitivity sheet recomputes the 2027 total once for each input raised and once for each input lowered by the test size, which is 10% by default and one month for the merit month. That makes 32 recalculations, with a Rank column for the gap between the high and low result. Its calculation table ties to the Summary total, and a check fails if it stops tying. In the worked example the top seven are:

The other nine inputs each move the total by USD 11,322 or less. Training per head tops that group at USD 11,322, and the benefits trend follows at USD 11,070. Hires planned and cost per hire move it USD 9,792 each. Contingency moves it USD 6,100 and the promotion pool USD 5,963, while the HR technology and other HR cost inputs move it USD 3,672 or less.

Percentage points are easier to say in a budget meeting, so the sheet also prices one more point on each rate.

The ranking shows where the money sits. It says little about which input is most likely to be wrong, because every input moves by the same percentage. The published 2027 health figures in the table below run from 8.2% (Marsh, after planned cost-reduction measures) to 11% (WTW, before plan changes), 2.8 points apart, with PwC at 9% and Aon at 9.2% for North America between them. In the example 2.8 points of benefits trend cost about USD 38,700, so the trend earns its own scenario even though it ranks ninth.

Shifting merit from April to May lowers the example’s 2027 total by USD 30,553, which is more than a 10% cut in the merit pool saves. The saving is timing only, because from January 2028 the increase costs the same whichever month it started.

Published 2027 inputs worth sourcing

The rows below are published figures that feed the blue cells or sanity-check them. They are not interchangeable. The two BLS rows report measured costs. The Marsh, WTW, PwC and Aon rows are projections that rest on different bases.

The Social Security wage base is the one input here with its own block in the workbook. The SSA notice raises the base by USD 7,800, and at the 6.2% employer rate each employee at or above the new base adds USD 483.60 to the employer’s tax. Count those employees, type the count in Assumptions section 5, and the file multiplies it out. The US payroll tax and benefit limits for 2027 page covers the other US payroll and benefit limits for the year. IRS Topic no. 751 says only the Social Security tax has a wage base limit.

The workbook has one payroll tax rate, so a country with thresholds needs an effective rate. GOV.UK shows employer National Insurance of 15% above GBP 5,000 a year for 2026 to 2027. On a hypothetical salary of GBP 40,000 that is GBP 5,250, an effective rate of 13.1% before reliefs such as the Employment Allowance. The rate shown is for category letter A only, so take the effective rate from your own payroll.

BLS puts benefits at 30.0% of private industry employer costs. Its technical note counts five categories as benefits: paid leave, supplemental pay (including overtime and nonproduction bonuses), insurance, retirement and savings, and legally required benefits such as Social Security and Medicare. The workbook’s taxes and benefits are a narrower set, because pay for time off stays inside base pay and the file leaves out overtime and bonuses, so the 30.0% average counts categories the workbook does not. Divide your employer taxes plus benefits by base pay plus taxes plus benefits and compare the result with 30.0%. In the worked example that share is 20.4%, our own calculation (taxes of USD 865,775 plus benefits of USD 1,896,636, over people cost of USD 13,548,323), shown in row 49 of the Summary. The Summary’s “Share of 2027 total” column gives 19.9% for the same two lines, because it divides by the full budget, including HR function and contingency. We would check for double counting before accepting a share far above 30.0%. For the wider benefits picture, see compensation and benefits trends for 2027.

Where the workbook can mislead you

One average hides mix. A single average base pay and one payroll tax rate treat every employee alike, so the wage base ceiling, a grade mix that shifts during the year and two countries with different employer rates all blur together. We would run one file per country or legal entity and add them up outside the workbook.

New hires are paid the average base pay from their start month, and merit goes only to the eligible share. A hiring plan skewed toward senior or junior roles needs a different average for the hires, and the file has one average pay input.

The benefits trend applies from January 1 to the 2026 cost per head you enter. If your plan year renews in July, enter that cost at the December 31, 2026 rate and use about half of the July 2027 increase as the trend: 4% for an 8% renewal. A calendar-year average needs the growth between the 2026 and 2027 averages instead.

Contingency belongs on its own line. We would leave base pay out of it, which is the workbook’s default. A contingency hidden in the pay line is hard to defend, and a visible one is easy to cut, which is the point of showing it.

The file has no lines for bonuses or equity, and it does not model overtime. Add one-off items such as severance and relocation to other HR function costs, or keep a separate line outside the workbook.

What we could not confirm

• Marsh’s final health results. The preliminary results rest on over 1,800 employers through August 10, and Marsh says final results from more than 2,000 employers come later this year, with no date.
• UK employer National Insurance for April 2027 onward. The GOV.UK page covers 2026 to 2027 and shows no later figures.
• Whether Business Group on Health or Gallagher published a 2027 health cost projection. No page we could open showed one.
• A published reference for cost per hire or training spend per employee. SHRM’s benchmarking page states neither figure as we opened it. We found no other source we could read, so use your own 2026 actuals for both.
• WTW’s page shows a second figure, 11.1%, for healthcare cost growth without saying whether it comes before or after plan changes, so we left it out.

What to do in your 2027 plan

Replace every blue cell before you share a number. Start with the payroll register for pay and last year’s invoices for employer costs, then work down the Checks sheet until it reads ALL OK.

Set the three benefits trend scenarios on purpose. Marsh’s 8.2% is after planned cost-reduction measures, the two 11% figures are before plan changes or if employers took no action, PwC’s 9% assumes unchanged benefits, and Aon’s 9.2% for North America does not say whether it is before plan changes. We would put the figure that matches approved plan decisions in Base and a before-plan-changes figure in High.

Count the employees you expect to earn the 2027 wage base or more. Then diarize the next releases. BLS publishes its next Employment Cost Index on October 30 and the employer cost shares for September 2026 on December 16. Marsh says its final health results come later this year. Rerun the three scenarios when each one lands.

Before you type a merit pool, pick the statistic that matches your budget from salary increase budgets for 2027, enter it in the Base column, and spread it across grades and ratings with the merit increase matrix.

Written and reviewed by the HR Riant Editorial Team. General information, not legal advice.