Headcount Planning Model in Excel: FP&A Guide (2026)

August 5, 2026 · Dezzmond Team
Productivity Financial Modeling Excel

People cost is 60–70% of operating expense at most software and services companies — and it is the single line item that FP&A gets wrong most often. Not because the salaries are unknown, but because a headcount planning model in Excel usually collapses under three things at once: mid-month start dates, a fully loaded cost multiplier applied to the wrong base, and a roster that nobody reconciled to the org chart.

The result is familiar. You present a $14.2M personnel budget in November, HR adds four requisitions in December, three people resign in January, and by March the plan is off by 8% with no way to explain which part of the variance was rate, which was timing, and which was headcount that never showed up.

This guide builds the model properly: one roster, one cost engine, one bridge that proves the count. Every formula below works in Excel 365 as written.

What Is a Headcount Planning Model in Excel?

A headcount planning model is a workbook that converts a position-level roster — current employees, open requisitions, and planned hires — into monthly personnel cost, FTE counts, and a hiring calendar that feeds the P&L and cash forecast. It answers two questions at once: how many people will we have and what will they cost, month by month.

The distinction that matters: it is position-based, not person-based. Each row is a seat, and a seat can be filled, open, planned, or backfilled. Model seats, and the plan survives resignations. Model people, and every departure breaks it.

The four layers

graph LR
    A[Roster Tab<br/>one row per position] --> B[Cost Engine<br/>loaded cost + proration]
    A --> C[Count Engine<br/>FTE + bridge]
    B --> D[Department Rollup<br/>SUMIFS by cost center]
    C --> D
    D --> E[P&L: Comp Expense]
    D --> F[Cash Forecast + Runway]
    D --> G[Variance vs Actuals]

Each layer reads the layer above it and never writes back. That one-directional flow is what makes the model auditable — the same discipline that keeps a three-statement financial model from turning into a circular mess.

How Do You Structure the Roster Tab?

One row per position, with hard-typed fields and no formulas in the input columns. Include a unique Req ID, department, location, level, status, start date, end date, base salary, bonus target, and FTE fraction. Everything downstream — cost, count, rollup — derives from these columns, so getting the schema right is 80% of the build.

Here is the minimum viable schema:

Column Field Type Why it exists
A Req ID Text (unique) Join key to HRIS/ATS; makes reconciliation possible
B Employee / TBH Text "TBH-Sales-04" for unfilled seats
C Department List Cost center rollup
D Location List Drives benefits load and employer tax rate
E Level List Drives salary bands and equity
F Status List Active, Offer, Open Req, Planned, Backfill
G Start Date Date Actual or planned; the proration anchor
H End Date Date (blank = ongoing) Termination, contract end, or planned exit
I Base Salary Number (annual) Full-time equivalent rate, not prorated
J Bonus % Number Target bonus as % of base
K Commission Number (annual) Quota-carrying roles only
L FTE Number (0–1) 0.5 for half-time, 1.0 for full-time

💡 Pro Tip: Set Status, Department, Location, and Level as dropdown lists driven by a hidden Lists tab. Free-typed department names ("Sales" vs "sales" vs "Sales ") are the number one cause of rollups that do not tie. See our guide to data validation in financial models for the pattern.

Status codes are not cosmetic

The Status field is what lets you run scenarios without deleting rows. A committed plan includes Active + Offer + Open Req. A stretch plan adds Planned. A downside case includes Active only.

=SWITCH($F5,
  "Active",   1,
  "Offer",    1,
  "Open Req", IF($Scenario>=2, 1, 0),
  "Planned",  IF($Scenario>=3, 1, 0),
  "Backfill", IF($Scenario>=2, 1, 0),
  0)

Put that switch in a helper column called Include, then multiply every cost and count formula by it. One cell now flips the entire plan between conservative, committed, and stretch.

How Do You Calculate Fully Loaded Cost Per Employee?

Fully loaded cost is base salary plus bonus and commission, plus employer payroll taxes, plus benefits, plus allocated overhead like software and equipment. For a US employee it typically runs 1.25x to 1.40x base. The critical detail: taxes are a percentage of cash compensation with a wage cap, while benefits are a fixed dollar amount per head.

Most models get this wrong by applying a flat 30% multiplier to everything. That over-costs a $300K VP (whose Social Security tax caps out early and whose health plan costs the same as everyone else's) and under-costs a $60K coordinator.

The correct component build

Component Basis Typical rate Capped? Common modeling error
Social Security (OASDI) Cash comp 6.20% employer Yes — SSA wage base, indexed annually Applied uncapped to senior salaries
Medicare Cash comp 1.45% employer No Adding the 0.9% surtax (employee-only)
FUTA / SUTA Cash comp 0.6% + state rate Yes — low state wage base Modeled as a full-year percentage
Health & welfare Per head $900–$1,800 / month N/A Modeled as % of salary
401(k) match Cash comp 3–4% Often capped Ignoring vesting and take-up rate
Software / equipment Per head $150–$400 / month N/A Buried in G&A, double-counted

Build it in one readable formula using LET — the technique explained in depth in our LET function guide for financial formulas:

=LET(
  base,     $I5 * $L5,
  bonus,    base * $J5,
  comm,     $K5 * $L5,
  cash,     base + bonus + comm,
  ss,       MIN(cash, WageBase) * 0.062,
  medicare, cash * 0.0145,
  unemp,    MIN(cash, StateWageBase) * StateRate,
  match,    MIN(cash, MatchCap) * MatchPct * TakeUpRate,
  benefits, XLOOKUP($D5, LocList, BenefitsAnnual) * $L5,
  overhead, PerHeadOverhead * $L5,
  cash + ss + medicare + unemp + match + benefits + overhead
)

⚠️ Warning: WageBase and StateWageBase are indexed every year by the SSA and by each state agency. Put them in a labeled assumptions cell with the plan year next to them, never hard-coded inside the formula. A stale wage base is a silent 1–2% error on a senior-heavy roster.

Sanity-check the multiplier

Divide loaded cost by base salary for every row and eyeball the distribution:

=LET(mult, LoadedCost / (Base*FTE),
  IF(OR(mult<1.15, mult>1.55), "REVIEW", ""))

Anything below 1.15x or above 1.55x is either a data error or a genuinely unusual role — a contractor, an international employee, or a commission-heavy seat. Both deserve a look before the plan goes to the CFO.

Start-Date Proration: The Formula That Fixes Half the Errors

A hire starting March 18 does not cost a full month in March. Most models either round to the nearest month (creating a systematic 2–4% overstatement across a 40-person hiring plan) or use a IF(start<=month, cost, 0) toggle that is off by up to 30 days per hire.

The correct approach counts overlapping days between the employment window and the calendar month:

=LET(
  m_start,  EOMONTH(N$4,-1)+1,
  m_end,    EOMONTH(N$4,0),
  emp_from, $G5,
  emp_to,   IF($H5="", DATE(2099,12,31), $H5),
  days,     MAX(0, MIN(m_end, emp_to) - MAX(m_start, emp_from) + 1),
  days / (m_end - m_start + 1) * ($LoadedCost5/12) * $Include5
)

Reading it line by line:

  1. m_start and m_end derive the first and last calendar day of the column's month from a single month-end header in row 4.
  2. emp_to converts a blank end date into a far-future sentinel so ongoing employees never zero out.
  3. days is the overlap: zero if the windows do not intersect, otherwise the inclusive day count.
  4. The final line converts the overlap to a fraction of the month and applies the monthly loaded cost.

ℹ️ Note: Use calendar-day proration for salary and benefits, but not for bonus accrual. Bonus is usually earned on a service-period basis and paid in a specific month — model it as a separate row in the cost engine so the cash timing is right in your 13-week cash flow forecast.

Payroll-calendar proration (when finance insists)

If your company runs semi-monthly or bi-weekly payroll and the controller wants the plan to match the payroll register, swap the denominator for actual pay periods:

=LET(
  periods_in_month, COUNTIFS(PayDates, ">="&EOMONTH(N$4,-1)+1, PayDates, "<="&EOMONTH(N$4,0)),
  periods_worked,   COUNTIFS(PayDates, ">="&MAX(EOMONTH(N$4,-1)+1, $G5),
                             PayDates, "<="&MIN(EOMONTH(N$4,0), IF($H5="",DATE(2099,12,31),$H5))),
  IFERROR(periods_worked / periods_in_month, 0) * ($LoadedCost5/12)
)

This produces the 3-paycheck-month effect that bi-weekly payroll creates twice a year — a real cash swing that calendar-day proration smooths away.

How Do You Count FTEs and Build a Headcount Bridge?

Point-in-time headcount counts positions active on the last day of the month; average FTE weights each position by the fraction of the month worked. Both are needed: the bridge reconciles to point-in-time, while cost-per-head ratios must use average FTE or they will look artificially high in heavy hiring months.

Point-in-time count

=COUNTIFS($G:$G, "<="&N$4, $H:$H, ">"&N$4, $M:$M, 1)
 + COUNTIFS($G:$G, "<="&N$4, $H:$H, "",     $M:$M, 1)

Two terms because COUNTIFS cannot match "blank OR greater than" in one criterion. Column M is the Include flag.

Average FTE for the month

=SUMPRODUCT(
   MAX(0, MIN(EOMONTH(N$4,0), IF($H$5:$H$400="", DATE(2099,12,31), $H$5:$H$400))
        - MAX(EOMONTH(N$4,-1)+1, $G$5:$G$400) + 1)
   / DAY(EOMONTH(N$4,0)) * $L$5:$L$400 * $M$5:$M$400
)

Enter as a normal formula in Excel 365 — dynamic arrays handle the element-wise MAX/MIN without Ctrl+Shift+Enter.

The bridge that proves it

Line Formula basis
Beginning headcount Prior month ending
(+) New hires COUNTIFS(StartDates, ">="&m_start, StartDates, "<="&m_end)
(+) Backfills started Same, filtered to Status = "Backfill"
(−) Voluntary attrition COUNTIFS(EndDates, ">="&m_start, EndDates, "<="&m_end) on reason code
(−) Involuntary / RIF Same, separate reason code
(=) Ending headcount Sum of the above
Check Ending − Point-in-time count = 0

That last row is not optional. Put it in a visible check cell:

=IF(ROUND(BridgeEnding - PointInTimeCount, 4)=0, "OK", "BRIDGE BREAK")

If it breaks, you have a position with an end date before its start date, a duplicate Req ID, or a status change that was edited mid-quarter without a corresponding row. All three are worth catching before the board meeting.

Driver-Based Hiring: When Should Roles Be Tied to Ratios?

Tie a role to a driver whenever the headcount requirement scales mechanically with a business metric — quota-carrying sales reps to new ARR, customer success managers to account count, support agents to ticket volume. Leave leadership, finance, and most engineering roles as explicit named hires; forcing them into a ratio produces plans nobody believes.

The capacity formula

=LET(
  target,     XLOOKUP(N$4, MonthEnds, NewARRTarget),
  quota,      AnnualQuota / 12,
  productive, target / quota,
  required,   ROUNDUP(productive / (1 - AttritionBuffer), 0),
  MAX(0, required - CurrentProductiveReps)
)

required is the productive rep count. The gap versus current productive reps is the hiring need — but a rep hired today is not productive today.

Working backward through ramp and time-to-hire

=EDATE(ProductiveByDate, -(RampMonths + TimeToHireMonths))

With a 4-month ramp and a 2.5-month time-to-hire, a rep who must carry quota in January has to be sourced in mid-July. This single formula is why sales plans miss: the hiring calendar was built backward from the start date instead of the productive date.

graph TD
    A[New ARR target set] --> B{Role scales with a driver?}
    B -->|Yes| C[Compute required productive heads]
    B -->|No| D[Named hire, explicit start month]
    C --> E[Subtract current productive heads]
    E --> F[Apply attrition buffer]
    F --> G[Back off ramp months]
    G --> H[Back off time-to-hire]
    H --> I[Requisition open date]
    D --> I
    I --> J[Roster row: Status = Planned]

💡 Pro Tip: Model the attrition buffer as a monthly rate converted from the annual assumption, not annual divided by twelve. The correct conversion is =1-(1-AnnualAttrition)^(1/12). At 18% annual, that is 1.63% monthly, not 1.50% — a 9% understatement of replacement hiring over a year.

Attrition, Backfills, and Merit Increases

Three mechanics separate a plan that survives Q2 from one that gets rebuilt from scratch.

Unnamed attrition

You cannot name who will resign, so model it at the department level as a negative headcount line and a positive backfill line offset by time-to-fill:

Attrition:  =-ROUND(BeginningHC * MonthlyAttritionRate, 1)
Backfill:   =-XLOOKUP(EDATE(N$4, -TimeToFillMonths), MonthEnds, AttritionRow)

The backfill row pulls the attrition figure from TimeToFillMonths earlier and flips the sign. The net effect is a temporary dip in headcount and a permanent cost gap — which is where a surprising amount of "favorable comp variance" actually comes from.

Merit cycle

Do not inflate salaries with a compounding growth rate. Apply the increase on the effective date, once:

=$I5 * (1 + IF(N$4 >= MeritEffectiveDate, MeritPct * EligibilityFlag, 0))

EligibilityFlag should be zero for anyone hired within the last 6–12 months, depending on policy. Skipping that check typically overstates the merit pool by 10–15% in a fast-growing team.

Promotions and level changes

Handle these as two rows: end-date the old position on the promotion date, start a new row at the new level. It preserves the audit trail and lets the bridge show a transfer in and out rather than an unexplained cost step.

⚠️ Warning: Never edit a historical row's salary in place. Once a month is closed and reported, its roster is a record. Overwriting it makes your budget vs actual variance analysis unreproducible — the plan you compare against no longer exists.

Rolling Up to the P&L and Testing Runway

The rollup is straightforward once the cost engine is right. One SUMIFS per department, per month:

=SUMIFS(CostRow_Jan, DeptCol, $B12, IncludeCol, 1)

In Excel 365, GROUPBY collapses the whole thing into a single spilled formula — covered in our walkthrough of the GROUPBY and PIVOTBY functions:

=GROUPBY(Roster[Department], Roster[Jan_Cost], SUM, 3, 1)

Splitting comp across the P&L

Personnel cost does not land in one line. Map it by department to R&D, S&M, G&A, and cost of revenue, and remember the capitalization question:

=SUMIFS(CostRange, DeptCol, "Engineering", CapexFlagCol, "Capitalized")

Engineering salaries attributable to internal-use software development may be capitalized under ASC 350-40. If your company does that, the flag belongs on the roster row, not in a manual journal entry at quarter-end.

Solving for runway with Goal Seek

Put a single HiringDelayMonths cell upstream of every planned start date:

Effective start: =IF($F5="Planned", EDATE($G5, HiringDelayMonths), $G5)

Then run Goal Seek: set the minimum monthly cash balance cell to your target, by changing HiringDelayMonths. It answers the question every founder asks — how much do we have to slow hiring to get to 18 months of runway? — in about four seconds, and it works because the delay flows through proration, cost, and cash automatically.

What Breaks Most Headcount Models?

The failure modes are consistent enough to make a checklist. Run these five checks before every plan submission:

  1. Bridge check — ending headcount equals point-in-time count, every month.
  2. Duplicate Req IDs — =IF(COUNTIF($A:$A,$A5)>1,"DUP",""). Duplicates double-count silently.
  3. Date sanity — =IF(AND($H5<>"", $H5<$G5), "END BEFORE START", "").
  4. Orphan departments — every roster department must exist in the P&L mapping table: =IF(ISNA(XLOOKUP($C5, DeptList, DeptList)), "UNMAPPED", "").
  5. Multiplier outliers — loaded cost ÷ base outside the 1.15x–1.55x band.

Five formulas, one check column, and roughly 90% of the errors that make it into a board deck get caught before they leave your workbook. The same audit-first mindset applies to the whole model — the rolling forecast process is where these checks earn their keep month after month.

Example: A 120-person company modeled 34 planned hires with month-rounded start dates and a flat 1.30x multiplier. Rebuilt with day-count proration and component-based loading, the same plan came in $412K lower for the year — 3.1% of total comp — almost entirely from partial first months and the payroll tax cap on eleven senior roles.

Frequently Asked Questions

What is a good fully loaded cost multiplier for headcount planning?

For US-based employees, 1.25x to 1.40x base salary is the normal range. The multiplier is lower for highly paid roles (payroll taxes cap out) and higher for junior roles (fixed per-head benefits are a larger share of a small salary). Build it from components rather than assuming a single rate, and recalculate it whenever benefits renew.

How do you forecast headcount for a department without a clear driver?

Use named hires with explicit start months and a written justification per requisition. Departments like finance, legal, and executive leadership scale in steps, not ratios. A useful discipline: cap each support function as a percentage of total headcount (for example, finance at 1.5–2% of company headcount) and flag any plan that breaks the cap for review.

Should headcount planning live in Excel or an FP&A tool?

Excel is the right home below roughly 500 employees or wherever plan logic changes frequently — the flexibility outweighs the governance cost. Above that, dedicated planning tools win on workflow, approvals, and HRIS integration. Many teams run both: the tool holds the system of record while Excel handles scenarios and board-level analysis.

How do you handle contractors and part-time staff in the model?

Keep them on the same roster with an FTE fraction below 1.0 and a distinct status code. Contractors carry no employer payroll tax or benefits load, so give them their own loaded-cost path — usually 1.0x with a markup already in the rate. Report headcount and FTE separately so the count is not overstated.

How often should a headcount plan be re-forecast?

Monthly for the current quarter, and a full rebuild at the end of Q1 and Q2. Reconcile four dimensions each month: headcount, fully loaded cost, open requisitions, and attrition. Plans drift fastest through timing — hires slipping two to six weeks — which is invisible unless you compare planned versus actual start dates by requisition.

Build It Once, Then Let It Run

A headcount plan is not a forecasting problem so much as a bookkeeping problem with dates. Get the roster schema right, prorate on day counts, build loaded cost from components, and prove the count with a bridge — the rest is rollup arithmetic.

If you would rather describe the logic than write it, Dezzmond generates the proration, capacity, and bridge formulas directly in your workbook from a plain-English description of your plan structure, and flags the date and mapping errors above before they compound.

Start with the roster. Every hour spent on the schema saves three in reconciliation, and it is the only part of the model you cannot fix later.