How does one track resource allocation? What does resource “allocation” even mean? These are the sorts of questions that project managers often struggle with when mandated to account for resources within an enterprise project management tool. On the surface, this would seem like a simple problem: I simply define the tasks in sufficient detail to support resource estimates, slap a couple of resources on said tasks, and call it a day.
The question arises however….what about consultants? What if I have consultants that are dedicated to my project that charge a specific daily or hourly rate? In that case, I can pretty much assume that I will be paying these consultants for 40 hours / week over the lifetime of the project. The question these PMs typically ask me is how do we track these consultants? Do we track them at a set 40 hours / week – because that’s what we’re budgeting them at? Or do we track them at the actual number of hours we’ve got them assigned to tasks in any given week?
The correct answer is that you should be tracking both. This is not a one or the other situation. Instead, we’re looking at two different dimensions of the resource puzzle. One dimension is the budget for the resources – either in dollars or in hours. The other dimension is the allocation of these resources – which is typically measured in hours.
Think of the question more in these terms: Given that I’ve budgeted for a 100% of this consultant, why do I only have them allocated for the next four weeks at 65% of their availability? Does this mean that they’re really working on unplanned work for the remaining 35% of their time or does this mean that they’ll be sitting around waiting for the work to proceed through the queue? Why would an expensive consultant be working on unplanned work in the first place?
For employees that are not dedicated to a project, this may or may not be a problem – as it is typically assumed the employee is returning to their day job to support the organization. Hence, their resource allocation really does represent the true cost of the project. If the employee’s costs are carried in whole or part by the project though, this could become an issue.
The Resource Plan as the Budgeted Dimension
Project Server gives us the ability to track budgeted work through the much maligned resource plan. To use the resource plan, I simply have to navigate to the project within PWA and add resources. (Microsoft has posted a lot of guidance on the use of resource plans, so I won’t rehash it here.)
In the example above, I am assigning resources by FTE by quarter. That’s the cost that will hit my project budget.
The Project Plan as the Allocated Dimension
I then allocate the resources to specific tasks within the project plan. This allocation may or may not target 100% allocation. In fact, I would generally recommend an 80% target, as that allows for some schedule buffer.
Comparing the Two
Unfortunately, there’s nothing native that compares the resource plan and project plan values within Project Server. There are however some quick reports that can be generated with relatively simple SQL code, such as the query below:
SELECT P.ProjectName, TBD.TimeByDay, R.ResourceName, ABD.AssignmentResourcePlanWork AS Budgeted, ABD.AssignmentWork AS Allocated, CASE WHEN ABD.AssignmentResourcePlanWork > ABD.AssignmentWork THEN ABD.AssignmentResourcePlanWork - ABD.AssignmentWork ELSE 0 END AS Unallocated FROM dbo.MSP_EpmAssignmentByDay_UserView ABD INNER JOIN dbo.MSP_EpmAssignment_UserView A ON ABD.AssignmentUID = A.AssignmentUID INNER JOIN dbo.MSP_EpmResource_UserView R ON A.ResourceUID = R.ResourceUID INNER JOIN dbo.MSP_EpmProject_UserView P ON A.ProjectUID = P.ProjectUID RIGHT OUTER JOIN dbo.MSP_TimeByDay TBD ON ABD.TimeByDay = TBD.TimeByDay WHERE R.ResourceName IS NOT NULL AND TBD.TimeByDay > GETDATE()
Throw that into an ODC and open up Excel to generate something like the following report:
…which if I were managing that PMO would indicate that we’re either underutilizing expensive external labor or my project managers aren’t adequately planning the tasks that the resources will be performing.
Throwing Financials into the Mix
That accounts for the hours assigned to a resource. How do we convert all of that back into dollars for incorporating into the larger financial picture? Check out this feature from UMT’s flagship product, UMT 360. I can import my resource plan back into my budget to map my financial estimates that much closer to my resource estimates.