Determining Resource Allocation for Resource View
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?