How do I build a headcount model? 👥 (starter edition)

[heidi_causal]

Finance teams are often tasked with forecasting their headcount and headcount-related costs like salary/wages, benefits, bonuses etc. This is often the biggest expense area for businesses, so is an important piece to get right! We’ve put together a detailed step-by-step post on how to build headcount in Causal. Feel free to also look at our template model here.

Please note that this is the “Starter edition” and therefore only includes fairly basic headcount modelling. Causal is capable of much more, so stay tuned for the “Advanced edition”, which will includes things like variable pay (e.g. quotas, commissions), pay types (salary vs. hourly), pay periods (biweekly), employee types (% FTE), payroll taxes (e.g. by state) etc.

Step 1: Connect to data (three options)

• Connecting directly to your payroll provider (e.g. Justworks, ADP Workforce Now) - see here
• Connecting to a Google Sheet (where you push data to from your payroll provider, or where you keep your own running database) - see here, and a template google sheet here.
• Inputting the data directly into Causal, and having Causal be the source of truth that you update.

Step 2: Create categories

Note that if you were connected to a data source, then you would follow the steps here for bringing in categories, as you would just need to import them, not create them from scratch.

1. Bring up the Data Table section (in the spreadsheet workspace) & create a new category Employee.
   

2. Once you’ve added employees in, you can then link to a new category called Title, and add titles.
   

3. We then want to link Employee with Title, and then map each employee to their title.
   

4. The last step is creating a new category Department, adding in the different departments, linking Title with Department, then mapping each title to a department.
   

Step 3: Inputs

1. In the same Data Table section (in the spreadsheet workspace), navigate to the Employee category tab, and add a new variable Start date. Format it as a date, and add start dates.
   

2. Follow the same process as above, for the following:

   1. End date (simply leave blank if no End date)
   2. Annual base salary (formatted as $)

Note that for steps 1 & 2, if you’re connected to data that has this information already, you would just need to import these variables into the model, and not create them from scratch. More info on that here.

3. You can also add other variables as appropriate to you. These would be added in the Inputs section of the spreadsheet. For example:
   1. Bonus (e.g. 10% of salary), Bonus month (e.g. ‘12’, for pay out in December each year)
   2. Pay increase (e.g. 5% increase), Pay increase month (e.g. ‘6’, for pay increase in June each year)
   3. Benefits (e.g. % of salary)

It is worth nothing that input variables can be broken down by any of the categories mapped to each employee. For example, you could breakdown Bonus by department, if they differed. Causal then automatically understands, when calculating bonuses for each employee, to apply the department-level bonus % that is relevant!

Step 4: Calculations

1. First, we need to calculate the Headcount, allowing for new joiners and recent leavers in each month.

   if date >= start date and (date ≤ End Date or is_Empty(End Date)) then 1 else 0

   

2. Next, we need to calculate our payroll costs each month, which consists of the base salaries, and (if appropriate to you) pay increases, bonuses, and benefits/tax/401k.

   • Adjusted base salary: we need to calculate the current salary for each employee in any given month. Create a variable Adjusted base salary whose formula will be:

   if month = Pay increase month then Adjusted base salary previous * (1+ Pay increase %) else Adjusted base salary previous

   Note: the first month has no previous month to reference, so the formula will return an error. You’ll need to edit that first month only (single-click) to reference the Annual base salary input from your data. If you’re connected directly to your payroll provider via Finch (see here) - you’ll want to connect Adjusted base salary to the “Salary (annual)” data range, which pulls each employees’ historical annual salary.

   

   • Then you’ll want to create a Monthly salaries variable as follows

   Adjusted base salary / 12 * Headcount

   

3. If you are allowing for bonuses and/or benefits, you might add the following variables in too.

   1. Bonus: if month = Bonus month then Bonus (%) * Monthly salaries * 12 else 0 (if accruing evenly throughout the year, then remove the if statement, and the *12)
   2. Benefits: Monthly salaries * Benefits %

Step 5: Output

1. Last, we can calculate Total payroll each month. Include bonuses and benefits if applicable.

   Total payroll = Monthly salaries + Bonus + Benefits

   

2. We can then group by Total payroll by department, and create a chart to display this, summarizing by month, quarter, or year, as you see fit.
   

Step 6: Future planning
You may also want the ability to add in future hires, and see how that affects your payroll costs. The best way to do this is as follows:

1. Create a new category in Causal called Status (if this isn’t already a category from your payroll data), and link it to Employee.
   

2. Fill in the Status of each employee as either ‘Hired’ or ‘Planned’.

3. When you add future hires into Causal, you will add them in as an employee in the data table section, the only difference being their start date is in the future, their Status is ‘Planned’, and their name will probably just be a title, e.g. “Financial Analyst #2”, until they’re hired.

   

4. You can then filter Total Payroll by Status, and easily chart this to see how it will grow over time. You can do the same for Headcount (group by Status).
   

Well done! You should have all you need to model your headcount & associated payroll costs going forward. If you have any additional questions - leave a comment on this post, or get in touch with us via our live chat. Remember that all of these steps are captured within our headcount template model here. Feel free to clone it and have a play.

Happy modelling & stay tuned for our Advanced edition