Headcount Forecast In Excel

Jerry Goldberg's Profile
Jerry Goldberg
Profile
Title: Principal
Company: Strategic Capital Corp
(Principal at Strategic Capital Corp) | Jul 28, 2013

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?

Answers

Member's Profile
David Alexander
Profile
Title: Controller
Company: ITW
(Controller at ITW) | Aug 5, 2011

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
Sarah Jackson
Profile
Title: Associate Editor
Company: Proformative
(Associate Editor at Proformative) | Jan 3, 2014

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"
https://www.proformative.com/whitepapers/5-must-have-metrics-create-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"

https://www.proformative.com/whitepapers/excel-shortcuts-cheat-sheet

Enjoy!

Best... Sarah

Member's Profile
Tim Williams
Profile
Title: CFO
Company: Private
(CFO at Private) | Aug 5, 2011

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
Tim Williams
Profile
Title: CFO
Company: Private
(CFO at Private) | Aug 5, 2011

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

Member's Profile
David Alexander
Profile
Title: Controller
Company: ITW
(Controller at ITW) | Aug 5, 2011

Tim,

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

https://www.proformative.com/resources/employee-raises-grid-month

Member's Profile
Jerry Goldberg
Profile
Title: Principal
Company: Strategic Capital Corp
(Principal at Strategic Capital Corp) | Aug 9, 2011

Thx David

Member's Profile
Ben Murray
Profile
Title: Vice President and CFO
Company: Cartegraph
(Vice President and CFO at Cartegraph) | Apr 21, 2016

Jerry,

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

http://www.thesaascfo.com/how-to-create-headcount-forecasts/

Ben

Member's Profile
Maksym Dyma
Profile
Title: Business Modeling Program Manager (OTP B..
Company: OTP Bank
LinkedIn Profile
(Business Modeling Program Manager (OTP B.. at OTP Bank) | May 23, 2016

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?

thx.

Proformative Advisor
Member's Profile
Wayne Spivak
Profile
Title: President & CFO
Company: SBAConsulting.com
LinkedIn Profile
(President & CFO at SBAConsulting.com) | May 23, 2016

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....

  • 19799 views
Topics: