Common formulas: headcount and payroll
Formulas that roll headcount forward and turn people into payroll cost, by department or by employee.
Payroll is usually the largest cost in a plan. A headcount formula counts the people in each period. A payroll formula turns that count into a monthly cost.
The formulas on this page come from the Headcount plan, Headcount by Department, and Advanced Headcount templates. To start from a template, go to Scenario planning → New model, type the template name, and press Enter.
Roll headcount forward
Headcount plan adds the hires of each month to the headcount of the month before.
| Variable | Formula |
|---|---|
| Headcount | if Headcount[previous] = blank then Starting headcount + Monthly hires else Headcount[previous] + Monthly hires |
| Monthly payroll cost | Headcount * Average annual salary / 12 |
With 10 starting people, 2 hires each month, and a salary of 120,000, month 1 has 12 people and costs 120,000.
The blank test works when Blanks in data is Treat as blank. That is the setting in the templates.
Payroll by department
Headcount by Department breaks Headcount, Average annual salary, Base payroll, and Fully-loaded payroll down by the Department dimension.
| Variable | Formula |
|---|---|
| Base payroll | Headcount * Average annual salary / 12 |
| Fully-loaded payroll | Base payroll * (1 + Taxes and overheads) |
| Cost per head | Fully-loaded payroll / Headcount |
Each department uses its own headcount and salary. Taxes and overheads has no dimension, so every department uses the same rate. Cost per head has no dimension either. It divides the total payroll by the total headcount.
Raises and bonuses for each employee
Advanced Headcount has one item for each employee in the Employee dimension.
| Variable | Formula |
|---|---|
| Monthly salary | Annual salary * (1 + Annual raise * timestep / 12) / 12 |
| Fully-loaded cost | (Monthly salary + Annual bonus / 12) * (1 + Taxes and overheads) |
| Annual run rate | Fully-loaded cost * 12 |
timestep counts the periods from 0. The salary goes up a small amount each month. For an annual salary of 150,000 and a 4% raise, the monthly salary is 12,500 in period 0 and 12,958.33 in period 11.
Pay a bonus in one month
The template spreads the bonus over 12 months. To pay it in December, add a Bonus paid variable with the Employee dimension:
if month = 12 then Annual bonus else 0Then use (Monthly salary + Bonus paid) * (1 + Taxes and overheads) for Fully-loaded cost. month gives the calendar month, from 1 to 12.
Without the dimension, Bonus paid reads the total bonus of all employees, and each employee gets that total.
Patterns in Formula templates
In the formula bar, select Formula templates and search for these names:
| Template | Example after you fill it |
|---|---|
| Payroll from headcount | Headcount * Average salary / 12 |
| Hiring ramp | ramp(timestep, 6, 24, 0, Target headcount) |
Hiring ramp gives 0 until period 6. From period 6 to period 24, the value grows in a straight line to Target headcount. After period 24, it stays at the target.