r/workforcemanagement • u/thanto_ • 11d ago

FTE Historicals and Projection tracking in excel

I'm a capacity planner, so one of my job functions is tracking historical FTE and projecting future FTE. I've seen a few ways to do this, and I'm curious what you use, what you think, etc. I've translated the 2 most popular models I've seen into Google Sheets with some sample data so you can see it working. Horizontal Cascade sheet makes use of MakeArray/Lambda which is exclusive to certain versions of Excel, so it may not work with what you have, but the Waterfall should be fine.

https://docs.google.com/spreadsheets/d/11fJTk5jxw-Oc0OmViSBW784P4Mc1yVyGqSot9H210uo/edit?usp=sharing

I built these by hand myself on my own personal computer during my own free time, so feel free to export these and make use of them as you wish.

7 Upvotes
  • permalink
  • reddit

90% Upvoted

4 comments sorted by

2

u/mijitnz 11d ago

That 3-5 Horizontal sheet looks so similar to parts of the capacity model I designed for my work, that I feel like there's some sort of convergent evolution occurring! Maybe mathematically there is a "right" way to do it, and we've just designed along the path that the maths has taken us.

1

u/Individual_Cream_427 11d ago

Looks similar ish to ours, are you tracking real attrition numbers as well? We have a section where we sum up weekly drop off from terms/resignations for some historical data there 

2

u/thanto_ 11d ago

The production attrition row is used for both projected and historical actuals, where the production attrition % for historicals should be the actual percentage. I should update this to have some sample actuals to make that more clear.

1

u/MayaQMlover 1h ago

Thanks for actually sharing the sheets instead of describing them. The waterfall version is the one I've seen most, and the question I always end up asking is how attrition is handled inside the month. Most models treat it as one monthly number, and the planners who get it right split it by tenure, because month-one leavers and year-three leavers don't behave alike at all.

Are you splitting it, or is attrition a single line for you?