Headcount Planning Model in Excel: How to Build One That Ties to Payroll

Most headcount plans are a number in a cell. Finance asks each department head how many people they need, collects 14, 9 and 22, adds them up, multiplies by an average salary, and drops the result into the comp line. Then March comes, two of the 14 start in August instead of February, one turns out to be a contractor, one never gets backfilled, and the comp line is off by six figures with no way to say which position caused it.

A headcount plan is not a count. It is a roster with dates, and the cost falls out of it. Build it that way and the plan explains itself, reconciles to payroll, and survives the third round of budget cuts without being rebuilt.

Here is the structure. About two hours the first time, then it runs monthly.

One row per position, never per person

The unit of a headcount model is a position, not a headcount number and not an employee. Filled positions, open requisitions, planned hires and backfills all get their own row on one tab called Roster.

Columns, in this order: Position ID, Department, Title, Status (filled, open, planned, backfill, leaving), FTE, Cost center, Start month, End month, Annual base salary (at 1.0 FTE), Bonus target percent, Level.

Two rules make it work later. Every row gets a Position ID you never reuse, because that ID is how you compare this version of the plan to last month's version. And End month stays blank for anyone you expect to stay, rather than holding a far-future date, so a departure is visible as a date instead of buried in a formula.

One row per position also means a person who moves departments in June is two rows: the old position ending in May, the new one starting in June. That looks redundant. It is what lets you show department detail without the company total double counting.

Rates live in one block, not inside formulas

Build a small Assumptions block of labeled input cells: employer payroll tax percent, benefits cost per FTE per month, bonus payout as a percent of target, merit increase percent, merit effective month, and recruiting cost per external hire if you carry it.

Fully loaded monthly cost per FTE for a position is then base salary divided by 12, grown by merit once the effective month passes, plus the monthly bonus accrual, multiplied by one plus payroll tax, plus benefits per FTE.

Put each of those in its own column rather than one long formula. A reviewer who can point at the benefits column and say that number is too low is a reviewer doing you a favor. A reviewer staring at a 200-character formula just approves it.

Some payroll taxes stop at a wage cap. In the United States, for example, federal and state unemployment taxes apply only to a first slice of each employee's wages, and the employer Social Security tax stops at an annual wage base that only higher-paid staff reach. So the effective employer rate is highest early in the year and falls as those caps are reached. It never falls to zero, because the employer Medicare tax has no wage cap. If that precision matters, calculate tax per position per month against cumulative wages. If it does not, use a blended annual rate and put the word blended in the cell label so nobody mistakes it for exact.

The month grid does the phasing

Across the top of a Monthly tab, put 12 or 18 months as real dates, first of the month. Active FTE for a position in a given month is:

=$E5*($L5<=M$4)*OR($H5=0,$H5>=M$4)

The Monthly tab repeats the Roster's columns in A to K and adds an Effective start in L, set out below, so E is FTE, L is effective start, H is end month, and M4 is the first month header. The OR keeps anyone with a blank end month active: Excel treats a blank end month as zero, and a formula that pulls a blank across from the Roster returns zero, so one test covers both. No nested IF, and it copies across the whole grid unchanged.

Cost per month is that active FTE times the position's loaded monthly cost per FTE. Whole months is the simplification worth taking. If mid-month starts genuinely matter, add a start-day column and prorate the first month by workdays, and expect to need it for a handful of positions rather than all of them.

Open roles get their own line and a start-date haircut

Filled positions are a cost you already have. Open and planned positions are a forecast, and a common, costly error in headcount plans is assuming every requisition fills on its plan date.

Do not bury that in optimism. Add one input cell called hiring slip, in months, and apply it only to rows that are not yet filled, so the effective start for an open or planned role is its start month plus the slip. Column L on the Monthly tab holds that date, with the hiring slip cell named slip:

=IF(OR($D5="filled",$D5="leaving"),$G5,EDATE($G5,slip))

Set it to one month and the plan is already closer to how hiring actually goes.

Then show two subtotals on the summary, filled and open, so a reader can see how much of the comp line depends on hires that have not happened. When someone asks what a hiring freeze saves, that number is already on the page.

The proof rows

Four checks at the top of the Monthly tab, conditionally formatted to turn red when they are not zero. The first two run across every month; the last two are single cells.

Ending headcount minus the count of positions active in that month. Proves the grid and the roster agree. Count heads on both sides, not FTE, and take start dates for the count from the effective start in column L, or every part-time or slipped role turns the check red.

Total monthly cost minus the sum of the department subtotals. Proves no department fell off the summary, which happens the first time somebody types a new department name into a new row.

Full-year total comp minus the full-year comp line in the budget file. Proves the plan and the budget are the same plan.

Count of rows that have an end month earlier than their start month. Should be zero, and it will not be the first time you build it.

The middle two matter most. A headcount model that does not tie to the comp line in the P&L is a side document, and side documents get ignored the moment the two disagree.

Reconcile to payroll once, then keep the exclusion list

Before anyone trusts the model, run the current month through it and compare the result to the actual payroll register. Expect a gap of a few percent. Then find out exactly what it is: overtime, commissions, a severance accrual, the employer retirement match, people charged to a capitalized project.

Each item is either a new column in the model or a documented exclusion on the Assumptions tab. That exclusion list is worth more than any formula in the file. It is the answer when somebody says the plan is wrong, and it is what stops you rebuilding the model every time a difference appears.

What it gives you back each month

Paste the new payroll register, update Status and actual start dates for anyone who joined or left, and the model answers three questions in one pass: the run-rate cost of the people you have, the incremental cost of the roles you have not filled, and the variance against plan by position ID rather than by department average.

That last one is what makes it defensible. When comp runs $180K over, the answer is not higher headcount. It is two roles that started earlier than planned, one backfill that was filled a level above the job it replaced, and a merit cycle that landed a month sooner than modeled. All three come straight off the roster, each with an ID next to it.

No macros and no add-ins. A date grid and one rule: every number on the Monthly tab arrives by formula.