Headcount Forecast In Excel

Jerry Goldberg's Profile

headcount forecast in excelI have a headcount forecasting worksheet in workbook an ex-employee left behind that has some holes in it. I need a formula in Excel that populates a headcount grid by querying the cells for each employee's start date, and also a formula for allowing for percentage raises . Any brilliant Excel modellers have ideas?


Member's Profile

Not sure of the scope of it, but you can attack this in a minimum of 2 ways. One using Countif/Sumif and the other using PivotTables. In my mind, what you are looking for is pretty straightforward, so in that case I suggest CountIf/SumIf. PivotTables can be quirky and if the data analysis doesn't involve a ton of 'whatif' meaning the grid will be static I suggest the simpler approach.

Member's Profile

Whatever headcount forecasting spreadsheet you create in Excel, to be accurate, it should in some way take into account the metrics in this informative white paper:

"5 Must-Have Metrics to Create an HR Value Center"

It breaks it down into these five areas: Recruitment/Retention, Productivity/Goals, Employee Engagement, Compensation and Benefits.

Plus, this free "Excel Shortcuts Cheat Sheet"



Best... Sarah

Member's Profile

David, I would be interested in this resource and so may other members of the Proformative community. Proformative does allow the posting of resources. Resources is a main site navigation tab. You could post the resource and then share the link in a response to Jerry to get the resource out to the community and "close the loop" with Jerry in addition to sharing your resource with the Proformative community.

Member's Profile

David, I saw that you posted the resource, but I did not see the attachment.

Member's Profile


Try it again, hopefully it worked this time. Let me know if it helps at all.


Member's Profile

Thx David

Member's Profile


Just posted my headcount forecast model here. See if it works for you.



Member's Profile

Good day,

I am finishing HC Model creation for the Bank, (it has more than 20 Analysis Types & 8 Integrated DataBases) and it is interesting for me: why no one uses VBA Programming for Models Creation instead of using million of complicated/hard for viewing formulas?


Proformative Advisor
Member's Profile

Possibly because most Controllers/CFO's are not programmers... and they don't have a VBA programmer at their disposal and quite possibly don't want to incur the expense....