Computing Full Time Equivalent Staffing

[Russ Parrish]

I am using Project to coordinate a staff of 50 people spread across 15
tasks. Most everyone on the team is assigned to multiple tasks
depending on their skills and the needs of the tasks. I ask the team
leads for each task to give me the percentage of time required for
each person in order to meet their scheduled delivery dates. I don't
hold our staff to 100% allocation so that I can determine how many
people short we are. I do this by exporting my data to excel then
computing the number of full time people required to perform the work
indicated by the percentages of time required according to the task
leaders. I can then compare the work required to our actual staff
size to find the short fall.

I would like to find a way in MS PRoject to roll up the number of
resources required to accomplish the work rather than the extra work
of importing the data into Excel then having to convert all of the
percentages from text to number (essentially requires re-entering)
before I can then insert the sum functions to calculate the totals for
each task then roll that up to a total number for the entire team.

Is there a way to create a column in Project that would do the
calculation I'm looking for. I know how to create custom fields
(columns) and enter formulas etc but I have not been able to figure
out how to reference other columns and specific rows. I'd like to
total the precentages to whole numbers for each task then total all of
the task totals to a team total.

I hope my explanation is clear I'm not a MS Project expert by any
stretch of the imagination and may not be using the correct
terminology.


Thanks

Russ

 

[Reid McTaggart]

I may not appreciate the fine points of what you are trying to accomplish,
but, to move the dialog along, how about this:

Assuming 1 FTE works 8 hours per day, FTE=Work/8/Duration

 

[John]

I am using Project to coordinate a staff of 50 people spread across 15
tasks. Most everyone on the team is assigned to multiple tasks
depending on their skills and the needs of the tasks. I ask the team
leads for each task to give me the percentage of time required for
each person in order to meet their scheduled delivery dates. I don't
hold our staff to 100% allocation so that I can determine how many
people short we are. I do this by exporting my data to excel then
computing the number of full time people required to perform the work
indicated by the percentages of time required according to the task
leaders. I can then compare the work required to our actual staff
size to find the short fall.

I would like to find a way in MS PRoject to roll up the number of
resources required to accomplish the work rather than the extra work
of importing the data into Excel then having to convert all of the
percentages from text to number (essentially requires re-entering)
before I can then insert the sum functions to calculate the totals for
each task then roll that up to a total number for the entire team.

Is there a way to create a column in Project that would do the
calculation I'm looking for. I know how to create custom fields
(columns) and enter formulas etc but I have not been able to figure
out how to reference other columns and specific rows. I'd like to
total the precentages to whole numbers for each task then total all of
the task totals to a team total.

I hope my explanation is clear I'm not a MS Project expert by any
stretch of the imagination and may not be using the correct
terminology.


Thanks

Russ

Russ,
First I'll answer your basic question. Using a custom field with a
formula in Project won't give you what you are after - Full Time
Equivalents (FTEs). You really need the data to be timescaled and custom
field formulas only operate on "static" data (i.e. not timescaled) and
on all task rows, not on single task rows.

The approach you are using sounds like a good start. You mention that
you are exporting the data to Excel - that's how I do FTEs. The
difference is that I do the whole thing in a VBA macro (part of a much
larger custom financial calendar macro) while your method is apparently
mostly manual.

I'm a little confused about the team leads giving the percentage
allocations. Is this verbal, entered in a spare text field, or are you
simply referring to the resource allocation value (i.e. percent or
units) normally used when assigning resource to tasks? If your end goal
is to check staffing levels, (daily, weekly, monthly, etc.), then one
approach is to take the Work field for each task in the desired period,
sum them all up, and divide by the total work hours available in the
period. For example, on a monthly basis, this would be all the Task Work
hours in each month divided by the total work hours in that month.

Another consideration. You mention that you don't require 100%
allocation. I assume this is to account for time off (e.g. sick days,
vacation, etc.). One rule of thumb I know is the 80% rule. This assumes
that 20% of total available resource labor time is "lost" due to time
off.

In conclusion, your basic approach sounds fine, it just needs to be
automated with some VBA code.

Hope this helps.
John
Project MVP

 

[Steve House [Project MVP]]

One thing to keep in mind is that contrary to popular belief, the percentage
allocation IS NOT the percentage of a resource's work time that is devoted
to a particular task. Rather it is the percentage of the time a resource
spends on a task that is converted into useful work output. Work doesn't
just occupy time, it also generates some concrete measurable output over the
course of that period of time. The allocation percentage describes the rate
at which that output being is produced, expressed as a percentage of the
maximum rate at which it could possibly be produced. If I take 8 hours to
generate the amount of output that I could have done in 4 hours had I been
working at full speed and not distracted by other things, I'm allocated to
that task at 50% effort units. If I take 4 hours to complete a task,
generating the amount of output that would have taken me 4 hours to generate
working full speed (ie, I worked at it full-tilt), for that task I was
allocated 100% even though I've only worked on it for 50% of my 8 hour
workday. It's easier if you think of physical objects, like if you max out
able to produce 10 widgets an hour and have to produce 100 widgets, how many
hours will it take? If the resource works 100% it will take 10 hours, if he
works 50% it will take him 20 to do the same 100 widgets. The same
principle applies to intangible work but it's a little harder to wrap your
brain around it.

 

[John]

John Thanks for the info. I was afraid that might be the answer. I
had hoped I could do what i wanted in place. I have considerable
programming experience however I am not very vamiliar with VBA
embedded in a MS Office application. I guess I will attack this as a
weekend project. Thanks for your help, I appreciate it.

Russ

uss

Russ,
You're welcome. If you need some help with VBA, let us know. Or, you
might want to go to the MVP website at:
http://www.mvps.org/project/links.htm
and check the link to a VBA tutorial at the bottom of the page (Project
98 Visual Basic Environment Training Materials). Even though it says
Paroject 98, it is equally applicable to all current versions of Project.

John
Project MVP

 

[Russ Parrish]

No, I fully understand the difference. My goal is to help a team of
relatively junior PMs understand the difference between the resources
they have been alloted by their manager and the actual need. In the
past they would just try to get by with whatever was given to them and
didn't recognize that with some thought to the actual work required
they might be able to make the case for additional resources. Sounds
like a simple enough concept but in their particualr environment was
not something that was obvious to them. I ask them each month for an
update to the percentage of specific skills they need to meet their
goals and then I update the MS Project Resources appropriate the to
various tasks I am tracking. Most of our team is overallocated and by
pointing this out to the managers we have been able to add staff to
compensate for the extreme over allocation.

I went to the MVP web site and found lots of great documentation for
VBA and plan to start working on it this week.

Thanks so much for all the great input.