How I Create Detailed Headcount Forecasts

Headcount Forecasts in Excel

For a software company, personnel-related expenses are usually the largest operating expense by far on the P&L (Profit and Loss Statement).  As a large expense, you’ll want to forecast this as accurately as possible.  I’ll walk you through how I create detailed headcount forecasts.

You can download the Excel model I used in this example at the bottom of this post.

When forecasting operating expenses, I follow the 80/20 rule on where I spend my forecast energy.  If it is a large expense item, I’ll spend 80% of my time on those expenses or expense categories.   When setting up my model, I’ll take the time to build forecast automation and detailed calculations into my spreadsheet model.

 

Headcount Forecasting and Planning

I forecast headcount by position and name.  We are still of the size where this makes sense and it is not tedious to maintain.  However, if you have large labor pools, say Pilots or Mechanics in my former life or several hundred sales representatives, you’ll obviously not want to forecast at the named person level.  You would be in tears trying to track this.

Rather, you will forecast this labor in aggregate using average wage rates, headcount levels, productivity, and so on.  And you’ll want to include an open headcount assumption so that you don’t overstate your forecasted labor expenses.

Headcount Forecast Model Setup

I use one tab in my spreadsheet where I forecast all named positions by department.  This tab is my labor “HQ” and all wage and medical changes in this tab flow out through the entire forecast model.  I group each department together on rows for ease of use.  On the left side of the tab, I have my inputs.  On the right side, my outputs (wages & taxes, benefits, FTE’s physicals).

Headcount Forecasts Setup

Headcount Model Inputs

My columns on the left include Department, Roster (Name), Title, Full/Part-time, Wage Rate, Hours, Tax Rate, Benefits Rate, Start Date, and End Date.  I’ll explain these in detail below.

Department – the employee’s department

Roster – obviously, the name of your staff

Title – again, just their title

FT or PT – this field is in calculating the correctly monthly wage expense

Hours – number of hours worked per month for part-time employees

Payroll Taxes – current employer’s wage tax rate

Medical Rate – I forecast medical expenses by head.  Often, you see medical expense as a percent of wages which is not really accurate.  It usually doesn’t matter if that staff is earning 200K or 20K, your medical premiums are set under a fully-insured plan.  If self-insured, I take total medical expenses and the number of participants to determine an average rate per head.

Start Date – start date of the position.

End Date – end date of the position



Required Fields

The only required fields are Wage Rate, PT/FT, Hours, Taxes, Medical Rate, and Start and End Date.  I’ve built date logic into my model so that if an employee starts on the 17th of the month, for example, the model calculates the wages for the remaining days in the month.  Same goes for the end date.

Also, aggregate merit and wage inflation increases can be easily forecasted on its own row.

Headcount Model Output

With my inputs complete, my headcount model will forecast my monthly wages, physical headcount, FTE’s (full-time equivalent), and medical expenses.  FTE’s are determined by how many days in the month the employee worked.

Headcount Forecast Benefits Headcount Forecast FTEs Personnel Forecast Physicals Headcount Forecast Wages

At the very bottom of the model, I have a total row and use the SUMIF Excel function to sum these outputs by department.

Excel Formulas Used

SUMIF, IF, AND, EOMONTH

Conclusion

Headcount expenses often represent the largest expense on a software company’s P&L.  It’s important to spend the time to develop a detailed headcount forecast model so that not only your wage forecast is accurate but also your cash flow forecast.

In any software company, there are always new hires, terminations, and transfers so make your forecast life easier with an automated Excel model.  I can literally update wage expenses in ten minutes or less and be accurate to the day (assuming our hiring managers are not too optimistic!).

Please let me know if you have any questions or comments.  I would love to hear feedback?  How do you forecast headcount or personnel-related expenses?  Comment below.

UPDATE (Dec-16) – I added flexibility to the model so that you can forecast the same position multiple times on the same row.  Instead of a row for each hire, you can enter the number of positions for that role.  Much easier to scale.  This headcount forecast template is incorporated into my SaaS Financial Plan.

Download below.

Please enter your email address (no spam) below to download the model.

Want all of my models?  Download 30+ models with here.

14 Replies to “How I Create Detailed Headcount Forecasts”

  1. I thank you for this resource you’re willing to provide us for learning more about the SAAS business model.

    1. Thanks, Richard. Please let me know what topics you’d like me to cover.

      1. Joshua Gonet says: Reply

        Ben,

        Would it be difficult to add and manage overtime in this forecast or would you recommend utilizing a different spreadsheet to track and manage over time and utilize a master budget? A potential employer is informing me that the job position requires management of revenue, headcount budget, spending budget, and over time. I’m trying to eliminate the waste from their previous processes.

        1. Yes, you could definitely add the logic to forecast overtime. However, tracking actual overtime would be better on a separate tab.

  2. […] the past few months I have released various Excel models covering forecasting, SaaS metrics, and personnel expenses. I combined some of these models into one and developed version one of my SaaS Financial […]

  3. […] a previous post, I discussed how I forecast headcount expenses and the related physical heads and benefits. Of course, this is just one piece of the corporate […]

  4. […] headcount forecast model is based on a template you can find in this headcount forecast post.  It creates extremely accurate monthly wage forecasts, but you do have to manually add heads […]

  5. Ben:
    Thank you for the template. It has been very helpful in my forecasting. I am having an issue extending the forecast when I attempt to drag/copy the wages & taxes columns to the right (to add months). The formulas look right, but no data is appearing and no errors in the Fx field. Am I correct to assume this should be an area I can extend by dragging the cells as a column to the right?

    Thank you,

    JP

    1. Hi JP,

      Yes, you can definitely drag the formulas to the right to extend the forecast. You just need to add more columns in between all of the sections (physicals, FTEs, and benefits). There are some hidden rows at the top that you also have to extend so the formulas work properly.

      Ben

  6. Brittany Joyce says: Reply

    Hi!

    This spreadsheet is fantastic! How would you best incorporate annual raises into this model?

    Thanks!

  7. I like the approach, but quick question on a common use case – employee changing department or role. Am I correct in assuming that the source of your inputs is a monthly payroll file? If so, how do you handle instances where an employee has changed departments and you still need the logic to build out his/her work history in the old department? If the inputs are not collected every month, say from the payroll file, then are you building and maintaining your own static data table of employees?

  8. Thanks for the spreadsheet! Can you explain why the full-time employee salary totals go down every few months?

    1. Hi Hannelore,

      Yes, I calculate payroll based on the business days in the month. This aligns with the 26 pay period concept. But if you only pay twice a month (24 pay periods per year), your payroll will be level each month. Not much of a difference between either. Just a timing and accrual thing on the accounting side.

      Ben

      1. Hi Ben, is it really business days? Because if you unhide row 4, you calculate how many days in the month by doing EOMONTH(date,0) – date + 1. That is calendar days, isn’t it?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.