Personnel Forecast Excel Model Template
  • Personnel Forecast Excel Model Template
  • Personnel Forecast Excel Model Template
  • Personnel Forecast Excel Model Template
  • Personnel Forecast Excel Model Template
  • Personnel Forecast Excel Model Template
  • Personnel Forecast Excel Model Template
Originally published: 19/02/2018 16:34
Publication number: ELQ-80553-1
View all versions & Certificate
certified

Personnel Forecast Excel Model Template

How to create detailed headcount forecasts using excel

employeesexcelexpensesforecastmodeloperating expensespersonnelstafftemplatewages

Description
For a software business, expenses related to staff are generally the biggest operating expense on the P&L statement. As it is a big expense, you will want to forecast this as accurately as you can.

For the forecasting of operating expenses, it's recommended that you use the 80/20 rule where you will spend 80% of your time on large expense items or categories.

Small or Large Company?

You can forecast headcount by name and position. When you're still small enough, it makes sense and is not too tedious or difficult to maintain. If you have very large labour pools, you obviously would avoid forecasting at named person level as this would be very hard and time consuming.

Instead, you should forecast this in aggregate where you use average headcount levels, wage rates, productivity, etc. You should also incorporate a headcount assumption so your forecasted labour expenses aren't overstated.

Headcount Model Setup

In this spreadsheet, one tab is used to forecast by department all named positions. This is referred to as labour 'HQ' where all medical changed and wages flow through the whole forecast model. Each department is grouped on rows to make it easier to use. The right side of the tab has outputs whereas the left side has inputs.

Headcount Model Inputs

Department – the department of the employee

Roster – the name of the member of staff

Title – their job title

FT or PT – this is for calculating wage expense monthly

Hours – the numbers of hours that have been worked each month by part-time staff

Payroll Taxes – the current wage tax rate for the employer

Medical Rate – these are calculated by head, and commonly you'll see the medical expense as a percentage of wages which isn't generally accurate. The wage of the staff isn't really relevant if the medical premiums fall under a plan that is fully insured. If self-insured, take the total medical expenses and no. of those participating to work out an average rate per person.

Start Date – the starting date of their position

End Date – the finishing date of their position

Required Fields

The only fields that are required are those stated above. Built into the model is logic that if an employee begins on the 15th of the month, the model will work out the wages for the rest of the days of that month, and the same applies for the end date.

Wage inflation and aggregate merit increases are able to be forecasted on their own row.

Headcount Model Output

When inputs are finished, the headcount model will forecast physical headcount, monthly wages, medical expenses, and FTEs. Full Time Equivalents are worked out by the number of days in the month worked by the employee.

At the bottom of the model, there is a total row, and the excel function SUMIF is used to sum up these outputs department by department.

Excel Formulas Used

SUMIF, IF, AND, EOMONTH

Conclusion

Headcount expenses are often representative of the most costly expense on the P&L statement of a software company. You should spend time developing a headcount forecast model that is detailed so that both your cash flow forecast and your wage forecast are accurate.

In a software company you should always expect new hires, transfers, and terminations, so an automated excel model should make your life a lot easier. Wage expenses can be updated in under 10 minutes and result in accuracy to the day.

This business tool includes
1 Personnel Forecast Excel Model Template

Ben Murray offers you this business tool for free!

download for free

Add to bookmarks

Discuss

Reviews


keyboard_arrow_leftkeyboard_arrow_right

More tools from Ben Murray

See all
keyboard_arrow_leftkeyboard_arrow_right

Any questions on Personnel Forecast Excel Model Template?

The user community and author are here to help. Go ahead!

please wait...