A while ago I saw a discussion on Proformative.com centered around the topic of headcount forecasting and people looking for advice on implementing a super-powerful model (in Excel, of course) to allow their finance and HR departments to forecast the need for employees in various areas of the business and when those employees would be needed.
HR needs this information in order to plan their recruiting efforts; when to advertise for open positions, when to schedule interviews and several other activities that must be planned in advance in order to successfully hire the most qualified persons for all open positions.
Finance, on the other hand needs this forecast in order to budget payroll and payroll related expenses. These forecasted expenses, an integral part of every corporate budget, must be reasonably accurate and reflect growth and any changes contemplated and communicated through planning and budgeting.
Designing and implementing a headcount forecasting model is no trivial task using a common spreadsheet application such as Microsoft Excel. In addition to the many global assumptions and a myriad of formulas, functions and links, the model must be capable of expanding as needed and be sufficiently documented so any reasonably skilled Excel user can take over and continue to maintain it. Another requirement, hardly ever present in most companies, is periodic audit of the model plus the need for a set of internal controls, designed to mitigate potential risks to the model resulting from formula errors, broken links and other assumption related errors.
This is no different than using spreadsheets to build other pieces of the corporate plan and various budgets, but may actually be even more challenging due to the dependency of the headcount on other areas of the plan, such as revenues, inventory requirements, etc.
For example, in a manufacturing environment where the direct labor force represents a large percentage of the total headcount, the number of production employees is directly dependent on sales budgets, or more accurately the making of inventory in the budget periods driving sales revenue, since making of inventory is the activity that requires the labor force.
Is there a reasonable approach to forecasting headcount?
The only reasonable approach is to abandon the use of spreadsheets for all kinds of forecasts that are incorporated into corporate budgets. Many organizations, even SMBs (Small and Medium size Business) have already come to the realization that use of spreadsheets for budget work is a bad idea. This has been covered here in this blog in much greater detail Replace Excel with a Dedicated Planning, Budgeting and Analysis Solution and Forecasting a Balance Sheet in a Spreadsheet World.
Forecasting headcount is no different. You need a system with built-in business logic, giving you the ability to make the headcount dependent on other factors (e.g., sales forecasts and their dependent inventory requirements).
By using existing, company developed KPIs, such as production employees per unit of revenue (e.g., number of production FTEs per $1MM in gross sales of manufactured inventory, number of FTEs per $1MM of inventory (at cost), produced in a period, etc.), you can link your needed manufacturing headcount to the sales forecasts. If required, inventory production can be made dependent on sales forecasts, allowing you to have a forecast of production FTEs in each period of your budget.
A popular SMB software solution, Budget Maestro from Centage Corporation can accommodate all these needs and will allow even a small organization to forecast its employee headcount requirements with tight dependency on inventory production needs or any other drivers applicable to its business. Of-course, the entire corporate budget should be prepared using this software solution, and there is not a single formula or link that must be provided by the users since all business logic and accounting rules are already built-in and ready to use Business Logic and Accounting Rules Built into the Budget.
With such compelling reasons to abandon spreadsheets in favor of a dedicated solution in the preparation of a corporate headcount forecast and all other budget elements, the whole process can be moved into an environment specifically designed for this purpose. Struggling to scale and maintain a set of unwieldy spreadsheets and with limited output usefulness will become a thing of the past.