Determining Resource Allocation for Resource View

Hello!

I am trying to find a way to determine resource allocation in project plans so that they more accurately reflect the availability of my team in the resource view.

We have individual project plans for each project, which all feed into the main status sheet. Each project plan is broken down into tasks with assignments, duration, and allocation percent. Team members are assigned to multiple projects, so having them 100% allocated to a task creates a resource view that might show someone as 500% allocated on a single day and renders it useless.

Our ultimate goal is to have more accurate allocation percentages so that the resource view can be our go-to tool for resource availability.

Every task has a duration in days, with several tasks being broken down further by subtasks which are sometimes assigned to different team members (A designer makes a prototype, the PM is arranging a presentation for it). I have timecard data from Q3 and Q4 of last year which I'm attempting to use in order to create that more accurate allocation percentage I'm after. I'm trying to do this by dividing the average hours it took by the duration we have set in the project plans to show how much of that time was spent on each task and then use that as the allocation percentage. I feel confident in the integrity of the timecard data but less confident in my ability to use it to generate this allocation percentage.

Any suggestions, formulas, tips, tricks, ideas on how to make this work? Am I on the right track? Is there a smartsheet tool I'm not using that I should be?

Thank you!

Aaron

Find more posts tagged with

Forms

Sheets

Formulas

Services

Project Management

Comments

Shaine Greenwood

Hi Aaron,

I like to think of % allocation as the level of attention that someone has on a project, with %100 being the full energy of their focus. Meaning that if someone is working on 4 projects over the span of a week, 25% of their attention is given to each project.

Resource allocation automatically assigned 100% if no other value is added. You might consider going to the individual projects and assigning a smaller %, then looking at your resource views (and the red icons on the left side of the rows to indicate whether someone is overallocated or not) to help get it down to 100%

ABogle

Thank you Shaine!

I had a bit of good luck last week and came across a formula on the forum that might've solved my problems.

=IF(Duration1 <> 0, [# of Hours for Task]1 / (Duration1 * 8), 0)

I was able to use this formula with our current project plan template. I took timecard data from the last two quarters, along with our task durations, and was able to create a plan that showed % allocation as a little more accurate chunk of time for my team.

Now the tricky part - getting everyone else on board!

Best,

Aaron

Sign in to join the conversation!

Quick Links

Trending discussions

Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.
Join distinct collect with contains
Can someone help with this formula? Its not working as-is, but I also would like to use the "contains"/"has" for the leader column as I combined a bunch of contact columns. Here is my attempt to use has - Heres the references (other sheet) Month, all Leads, and R&B prog is the first one (project)