planning workload of departmental resources, e.g. FTE

[Bjorn Sternberg]

I wondered if anyone has some helpful templates or knows of good resources (websites, books, etc) for planning departmental resources, such as by using Full Time Equivalent (FTE). What I had in mind was a spreadsheet that looks at what activities each resource is working on and assigning an FTE to to each resource to see what spare capacity there is in the department or determine if additional resources are required. So rather than looking at individual project requirements, I had in mind a tool a department manager might use to plan resource requirements.

I had a look online, all I found so far is this: https://www.orientpoint.com/FTE.htm

[Daryl Deffler]

Bjorn;
I don’t have any specific recommendations, sources, or books to point you at. But I can provide a very simple staff planning model
I’ve used in the past and it works very well on a smaller scale.
Below is a sample spreadsheet. Weekly dates across the top and then for each resource(Bob), there are a block of rows. These define their total hours per week, all the projects the manager has assigned them to, and the weekly hourly commitment for each project.
In this example, Bob’s manager committed Bob at 10 hours a week to work on Project 1 for the next 6 weeks, Project 2 at 5 hours per week for the next 3 weeks, and so on.

Name Hours/Wk 03/20/2017 03/27/2017 04/03/2017 04/10/2017 04/17/2017 04/24/2017 05/01/2017
Bob 40
Project 1 10 10 10 10 10 10
Project 2 5 5 5
Project 3 20 30 25 20 20

Total by Week 35 45 40 30 30 10 0
Avail by Week 5 -5 0 10 10 30 40

The chart also shows that on week 1, Bob’s allocated 35 total hours, week 2 he’s over allocated by 5 hours, and that Bob starts to become free in about 6 weeks. You can also add conditional formatting so negative hours show in red or other colors, for example, if more that 50% of Bobs time is available.
It’s manual, but really only needs to be updated by the manager as the resource is being allocated to a new project. It provides a nice summary view of each resource, the commitments to each project, and when they have available time.
These resource rows can then be summarized at the department level.

The key to this is that the numbers are not detailed project task usage. Instead they are the amount of time per week the resource has been provided/committed for work on a project.

Hope that helps.

[Author]

Posts