Skip to main content

Frac CFO

By Jonas Tyrone Lobaton, MSc Quantitative Finance, FMVA®
Founder, Frac CFO · Updated November 19, 2026

A headcount forecast in Excel is a roster of current and planned people, a monthly timeline, and a few formulas that turn start and end dates into headcount, full-time equivalents (FTEs) and payroll cost. You need three tabs (assumptions, roster, monthly summary) and about a dozen formulas. Done well, it tells you not just how many people you will have in June, but what they will cost and whether your cash can carry them.

This guide is for owners, founders and managers who run their numbers in spreadsheets and want a hiring plan they can trust. We walk through the layout, the exact formulas, the cost side, scenarios and the habit that keeps the forecast accurate.

Fraccfo
Connect Hiring Plans to Cash Flow
Frac CFO helps spreadsheet-based businesses build financial models, forecasts and dashboards for clearer cash flow and more informed decisions.

Visit

TL;DR

  • Build one roster tab where every person, open role and planned hire has a start date, an end date, weekly hours and a salary.
  • Let formulas count headcount at each month end by comparing those dates to the month. Never type headcount by hand.
  • Add a fully loaded cost line: salary plus payroll taxes, benefits and recruiting, so the plan shows real cash impact.
  • Keep a base, a slow and a fast scenario, and compare plan with actual every month.
  • Link the result to your budget and cash flow forecast, because hiring is usually the biggest cost decision you make.

Table of Contents

Headcount forecast vs. headcount report

A headcount report looks backward. It counts who is on payroll today, usually by department, using a PivotTable on an employee list. A headcount forecast looks forward. It starts from the same list but adds open roles, planned hires, expected departures and changes in hours, then projects the totals month by month.

The forecast answers questions a report cannot:

  • How many people will we have at the end of each quarter?
  • When does the next hire have to start so the work gets done?
  • What does the team cost per month, including taxes and benefits?
  • How much cash do we need before the new person pays for themselves?

The same workbook does the work of a hiring plan template, a staffing plan and a workforce planning model. The names differ, the structure is the same.

What you need before you open Excel

Gather six inputs. If one is missing, the model will quietly guess, and guesses compound over twelve months.

  1. Current employees. Name or ID, role, department, start date, weekly hours, annual salary or hourly rate.
  2. Open and planned roles. Same fields, with a target start date. Add a status: Active, Open or Planned.
  3. Known departures. Resignations, retirements, contract end dates, seasonal staff.
  4. Hiring lead time. How many weeks pass between approving a role and the first day of work. Be honest here. It is the most common reason plans slip.
  5. Cost loads. Employer payroll tax, benefits, equipment, software seats and recruiting fees.
  6. A demand driver. Revenue, orders, billable hours or customers per employee. This is what justifies the hire in the first place.

The three-tab layout

Keep it simple so anyone on your team can follow it.

Tab 1: Assumptions

One cell per input, labeled, with the unit. For example: standard hours per FTE (40), payroll tax load, benefits load, one-time hiring cost per role, and the forecast start month. Every formula points here, so changing a number once changes the whole model.

Tab 2: Roster

One row per person or role. Suggested columns:

Column Field Example
A Role Customer Support Rep
B Department Operations
C Status Planned
D Start date 3/1/2027
E End date 12/31/2099 (placeholder if none)
F Hours per week 40
G Annual salary $52,000
H Scenario flag Base

The placeholder end date matters. Excel treats a blank cell as zero, which breaks date comparisons. Filling active people with a far-future date such as 12/31/2099 keeps every formula simple.

Format the roster as an Excel Table (Ctrl+T) so formulas expand when you add rows.

Tab 3: Monthly summary

Put month-end dates across the top, starting in B1. In B1 type your first month end, for example =EOMONTH(Assumptions!B2,0), then in C1 =EOMONTH(B1,1) and copy right for 12 or 24 months. Rows below hold headcount, hires, departures, FTEs and cost.

The formulas that do the work

The logic is the same in every headcount model: a person counts in a month if they have started on or before the month end and have not ended before it. With the roster above, and ranges running to row 200:

How roster dates drive headcount calculations

Ending headcount

=COUNTIFS(Roster!$D$2:$D$200,"<="&B$1,Roster!$E$2:$E$200,">"&B$1)

New hires in the month

=COUNTIFS(Roster!$D$2:$D$200,">"&EOMONTH(B$1,-1),Roster!$D$2:$D$200,"<="&B$1)

Departures in the month

=COUNTIFS(Roster!$E$2:$E$200,">"&EOMONTH(B$1,-1),Roster!$E$2:$E$200,"<="&B$1)

Check row: opening headcount plus hires minus departures should equal ending headcount. Add a row that subtracts the two and shows zero. If it ever does not, a date is wrong.

Full-time equivalents

=SUMPRODUCT((Roster!$D$2:$D$200<=B$1)*(Roster!$E$2:$E$200>B$1)*Roster!$F$2:$F$200)/Assumptions!$B$5

where B5 holds standard weekly hours (40). Two part-timers at 20 hours each show as two heads but one FTE. For capacity planning, FTE is the number that matters.

By department

Add a criterion to COUNTIFS: Roster!$B$2:$B$200,$A5 where A5 holds the department name. Now you can see where growth is concentrated.

By scenario

Add Roster!$H$2:$H$200,Scenario or filter on a dropdown cell, so one workbook produces several plans.

If you prefer to model partial months, so a person starting on the 20th counts for a third of the month, replace the count with a fraction of the month worked. It is more precise for cost, and the plain month-end count is usually enough for headcount.

A worked example

Say a 12-person online store plans four hires in 2027: a customer support rep in March, a marketing coordinator in May, a warehouse lead in July and a part-time bookkeeper (20 hours a week) in September. One current employee leaves in October.

The monthly summary would read:

Month end Opening Hires Departures Ending headcount FTE
Feb 2027 12 0 0 12 12.0
Mar 2027 12 1 0 13 13.0
May 2027 13 1 0 14 14.0
Jul 2027 14 1 0 15 15.0
Sep 2027 15 1 0 16 15.5
Oct 2027 16 0 1 15 14.5

Headcount ends the year at 15, up three from 12, yet FTE rises only 2.5 because the bookkeeper is part-time. That difference is why a plan built on heads alone overstates capacity and understates the cost per hour of work.

Add the cost side: salary is not the cost

A hiring plan without cost is a wish list. Add a cost block under the headcount block.

Monthly base pay

=SUMPRODUCT((Roster!$D$2:$D$200<=B$1)*(Roster!$E$2:$E$200>B$1)*Roster!$G$2:$G$200)/12

For hourly staff, multiply hourly rate by weekly hours by 52, divided by 12, in a helper column on the roster, then sum that column instead.

Employer payroll taxes. In the United States, employers pay 6.2% social security tax up to a wage base of $184,500 and 1.45% Medicare tax for 2026, per IRS Publication 15. That is 7.65% on most salaries, before federal and state unemployment taxes, which vary by state and employer history. Put the rate in Assumptions, not in the formula.

Benefits and paid leave. The Bureau of Labor Statistics reported that for private industry workers in June 2026, wages averaged $32.82 per hour and benefits $14.07, with benefits making up 30.0% of total compensation. That is roughly 43 cents of benefit cost for every dollar of wages, and it includes legally required benefits, paid leave and insurance. Your own load will differ with your size and plans, so use your actual insurance and retirement contributions, and use the national figure only as a sense check. The BLS series is released quarterly, so look up the latest release when you update your assumptions.

One-time hiring costs. Recruiter fees, job ads, a laptop, software licenses, onboarding time. Add them in the month of the start date, not spread across the year:

=COUNTIFS(...hires in month...)*Assumptions!$B$7

Fully loaded monthly cost = base pay × (1 + payroll tax load + benefits load) + one-time costs.

Now you can see that a $52,000 hire in a plan with a 7.65% tax load and a 20% benefits load costs about $66,300 a year before one-time costs. Show that number to anyone who approves the role.

Add the cost side: salary is not the cost

Model the timing of hires realistically

Three timing details change the answer more than any formula.

  • Approval date vs. start date. If a role takes eight weeks to fill, a hire needed in June must be approved by April. Add an “approved by” column calculated as =D2-7*Assumptions!$B$8, so the roster tells you when to open the search.
  • Ramp time. A new salesperson may not produce for two or three months. If your revenue forecast depends on that hire, delay the revenue, not just the cost. Our revenue forecast guide shows how to connect pipeline and capacity.
  • Vacancies. Existing roles that are open but unfilled belong in the roster with status Open. Otherwise the model assumes a full team and underspends against the budget, which hides a capacity gap.

Tie hiring to a driver

The strongest headcount forecasts do not start with “we want to hire six people.” They start with a ratio. Examples:

  • Support staff = orders per month ÷ orders one rep can handle (say 1,200).
  • Billable consultants = forecast billable hours ÷ (weekly hours × utilization × weeks).
  • Store staff = opening hours × minimum people on the floor ÷ hours per employee.

Then =ROUNDUP(driver/ratio,0) gives required headcount per month, and you compare it with the roster. The gap is your hiring need. This is the same approach as driver-based budgeting, and it makes hiring conversations about workload instead of opinions.

Scenarios: base, slow and fast

One forecast is a guess. Three are a plan.

  1. Base: the hires you expect if sales land near forecast.
  2. Slow: revenue comes in 15 to 20 percent lower. Which hires are delayed, and which are not made?
  3. Fast: demand arrives early. Which roles must be filled sooner, and what does the extra payroll do to cash?

Use the Scenario flag column on the roster and a dropdown on the summary tab (Data Validation, list: Base, Slow, Fast). Have each hire carry the scenarios it belongs to, or give each scenario its own start date column. Then add a short comparison table of ending headcount, annual payroll and lowest cash balance for each.

Pick trigger points too. For example: “We open the warehouse lead search when trailing three-month orders pass 4,000.” A trigger turns a scenario into a decision rule.

Keep the forecast honest: plan vs. actual

A headcount model decays quickly if nobody updates it. Build a short monthly routine:

  1. Paste the actual payroll headcount and cost from your payroll report into an Actuals tab.
  2. Add variance rows: actual minus plan, for heads, FTE and cost.
  3. Write a one-line reason for any variance over a threshold you set, such as one head or 5% of cost.
  4. Update start dates for hires that slipped, then look at what changed in the following months.
  5. Roll the forecast forward by one month so it always shows the next 12.

Two patterns show up again and again. Hires start later than planned, so payroll runs under budget while the work backlog grows. Or offers come in above the planned salary, so cost runs over. Both are visible within a quarter if you track variance.

Common mistakes

  • Typing headcount into cells instead of calculating it from dates.
  • Leaving end dates blank, which makes COUNTIFS skip active staff.
  • Counting heads when you need FTEs for capacity.
  • Forgetting taxes, benefits and one-time hiring costs.
  • Modeling hires with no revenue or workload behind them.
  • Keeping the plan in a separate file from the budget and cash flow forecast, so the numbers disagree.

Connect headcount to your budget and cash

Payroll is usually the largest line in a service or e-commerce budget, so the headcount tab should feed your budget directly. Link the loaded monthly cost to your annual budget in Excel, and from there to a rolling 12-month cash flow. Pay attention to timing: payroll goes out every two weeks or twice a month, while customer cash often arrives later. A hire that is profitable on paper can still squeeze cash for several months. If you need the near-term view, a 13-week cash flow forecast will show it week by week.

If you are building the payroll side from scratch, the payroll templates in our store and the salary calculator tools are a practical starting point.

Where Frac CFO fits

We build finance tools in Excel and Google Sheets for businesses that want to keep running their numbers in spreadsheets. For headcount and hiring plans, that means a workbook shaped around your roles, your departments and your cash position, with onboarding and email support so your team understands every formula.

There are a few ways to start:

  • Browse our Excel templates if you want to build on a ready-made structure.
  • Ask for a one-time project such as a budget build or KPI dashboard on our projects page if you want a custom model delivered once.
  • Choose an ongoing fractional CFO plan if you want someone to keep the forecast current. The Starter plan is $600 per month or $7,200 per year.

Payroll System Excel Template for small businesses

If you are not sure which fits, start from the Frac CFO homepage and tell us what you are planning.

Our perspective

Most hiring mistakes we see described by small business owners are not about picking the wrong person. They are about hiring without knowing what the role does to cash over the next twelve months.

We believe a headcount forecast should be a spreadsheet you fully understand. When the roster, the monthly timeline and the cost lines sit in the same Excel or Google Sheets file you already use, you can change a start date, see payroll move, and see the cash effect in the same minute. No new software to learn, no black box.

That is why we focus exclusively on spreadsheet-based finance. Our custom projects and fractional CFO plans are built so you keep complete control of your data, with hands-on onboarding and email support behind it.

frac

FAQ

What is the best Excel formula for forecasting headcount?

COUNTIFS is the most useful. It counts roster rows whose start date is on or before the month end and whose end date is after it. Pair it with SUMPRODUCT when you need FTEs or payroll cost by month.

How far ahead should a staffing forecast go?

Twelve months is the standard for a budget, refreshed monthly as a rolling forecast. Add a second year at quarterly detail if you are raising money or opening new locations. Short hiring lead times and seasonal staff may also justify a 13-week view.

Should I forecast headcount or FTEs?

Both. Headcount shows how many people you employ and drives per-person costs such as equipment and benefits. FTE shows how much work capacity you have, which matters whenever you use part-time, seasonal or reduced-hour staff.

How do I include employer costs in a hiring plan?

Add percentage loads in an Assumptions tab for payroll taxes and benefits, and multiply base pay by one plus those loads. Add one-time costs such as recruiting and equipment in the start month. Use your actual insurance and retirement contributions where you have them.

About the author

Jonas Tyrone Lobaton is the founder of Frac CFO. He holds a Master’s degree in Quantitative Finance and the FMVA® certification, with work spanning financial modeling, forecasting, optimization, dashboards, business finance, and spreadsheet automation.

Sources

Leave a Reply