Human Resources Planning Tool in Excel: Headcount Requirement Forecasting

A simple tool that makes forecasting headcount requirements easier for HR departments.

by Jason Varner
smarthelping.com - Financial Modeling for EveryoneFollow 156

1 review2,331 views|Start the discussion!|Bookmark Add to cart - $45.00

Description
This tool has a simple data entry structure so that expected hiring/firing/attrition can be defined as a transaction. That transaction will feed into a date-based structure so that the current, 30 day, 60 day, and 90 day projected headcount can be determined by department. This expected headcount is then compared against the model projections to figure out if there is expected to be a surplus or shortage of employees in a given department against the model. The 'model' figures would originate from somewhere else or come directly from some plan that exists already.

Color-coding has been implemented to more easily analyze the data. Additional, a visual stacked bar chart that shows net surplus/shortage by department by time-frame was implemented. The user interface is pretty friendly and editable cells are clearly defined from formulas.


This template works for startups or an established organization.


Data inputs:


1. Department
2. Add/Loss
3. Count
4. Date


The above four inputs will be queried by formulas so that headcounts can dynamically populate in up to 3 future buckets as well as the 'current' status as of the current day (today function is used to know what date 'current' is).


Also, if you have current employees, the first set of data inputs will just be 'Add' transactions with a date that is less than or equal to the current date.


The reason why this tool is so useful is that often times the planned hires and fires of a company can be hard to keep track of over time. You will now be able to track the net pipeline to see where headcounts may be relative to model projections and act accordingly.


Note, if you want this built in google sheets, I can do that for a rate of $120/hr. and with that comes some better formulas for summarizing the log of transactions.

This Best Practice includes
1 Excel template and 1 tutorial video