Welcome to the latest posting in my Resource Analysis Under the Hood series. My goal with these posts is to introduce the features of the Project Server Resource Analysis component of the Portfolio Analysis module with the help of an Excel mockup. Why Excel? Because this is the closest I could get to developing a database without bringing the typical IT opprobrium down on my mad Access skillz.
For previous postings in this vein:
Make sure to download the worksheet from that first post if you would like to play along at home….
In this post, I’ll be talking about one aspect of the What If Analysis options – the ability to incrementally add resources or cost to the portfolio. As a major caveat, I’ll point out that this is one of those posts that made assumptions about the calculation model used in the Resource Analysis module – assumptions which have since been proven false. Hence, the bulk of this post represents an alternate model for how incremental costs and resources are calculated. It’s close enough to provide some key concepts, and at some point, I plan to rewrite it to discuss how Project Server specifically performs these calculations. If you’re reading this post after November 2010, you may wish to skip to the bottom of to see if I’ve added a link to a revised post.
That being said, I think you’ll still get value out of this post, and hence I am publishing it as is with the above caveat.
Putting the “What If” in “Resource Analysis”
As I discussed in my post on What If Analysis, users have the option of adding resources to the scenario to assess. The controls for this feature reside on the Options tab, and are active when the Gantt Chart is displayed. Let’s quickly review those options:
Units: You may either toggle this to “FTE,” whereby you can then enter how many extra resources you plan to hire…..or toggle to “Cost,” whereby you can flag the amount of extra budget you’re willing to spend.
Cost Rate Table: Specifies which cost rate table to use when specifying cost and hiring resources. The cost calculated for a specific role is the average cost for each resource assigned to that role within the resource pool.
Type: This field allows you to choose between “Internal” or “External.” See more on that below.
Allocation Threshold: This sets the minimum percent of an external FTE that can be hired. For instance, we can only hire full time staff, or we can enlist a part time resource to staff the project. Setting the Allocation Threshold at 50% will allow the system to calculate additional resource requirements in units of .5 FTE. The Allocation Threshold field does not appear to impact calculations for internal resources.
So the big question with adding resources is determining how the tool decides to allocate resources. As I discussed before, I apparently totally miffed the optimization heuristic in the Resource Analysis module, so for the time being am using a different model. Based on that, I’ll do my best to guess what the actual model used is.
Before we get into this section, I would advise you to review the following two posts:
The first trick is to identify the deficit for each project. I use pretty much the same mechanism as documented in the Requirements Details View discussion, with a minor nuance. In this case, I round each deficit to the amount entered in the Allocation Threshold field.
Let’s back up a bit and review some of the calculations. (This incidentally, is where I veer a bit off of how Project Server actually performs the calculations. You see in the below calculations that I treat each project selection portfolio as an overall portfolio. My guess is that Server treats each project individually – more on that in a later post.)
First, I develop a resource profile for each of the 32 potential scenarios. The magic number 32 is derived from the fact that we have 5 projects, each project possessing two possible states: included or not included. Doing the math, that yields 2 to the 5th potential solutions, depicted below using the notation of “NYYNN” to indicate that Projects B and C are included, but A, D and E are not.
In the following screenshot, I have the profile defined for Role4 in four potential scenarios. Essentially, I total up the Role4 requirements for each of the projects as identified in the Summary field.
Then I compare that to my resource supply to identify the actual deficit for each of the potential scenarios. Here’s what the resource supply looks like.
You’ll see that Role4 has a supply of 1, but a demand of 1.7 in the scenarios pictured, hence I have a calculated deficit of –0.7. If I set the Allocation Threshold to 25%, I need to round that deficit to the closest .25, and end up with a revised calculated deficit of –0.75.
Let me show you what that looks like. In the following example, I have set the Allocation Threshold to 25%.
Here’s the same calculation with an Allocation Threshold set to 50%.
From there, I calculate the peak deficit for each of the identified scenarios and record that in the Max Deficit field. I also sum the deficit in each row to generate the Deficit field.
I then calculate the incremental cost for the new scenario: Deficit X 160 (hours/month) X Role4 Average Rate = Incremental Cost. From there, I develop a summary of the Max Deficit and the incremental cost for each of the 32 potential solutions. In the screenshot below, I summarize the Maximum Deficit and then sum it up in the DEF Total column.
To calculate the Total Incremental Cost, I calculate the total deficit (not Max Deficit) for each solution and multiply it times the relative resource cost. That’s why all of the solutions display the same Maximum Deficit above, but the incremental cost is slightly different.
To show you what that looks like, let’s take a look at the deficit calculation for each solution:
So while Option 12 represents an incremental cost of 6 Man Months X 160 Hours/Month X $50 Average Rate = $48,000; Option 16 represents an incremental cost of 13 Man Months X 160 Hours / Month X $50 Average Rate = $104,000. Yet the Maximum Deficit is the same for both solutions. This means that incrementally adding 1 resource could potentially yield any of the solutions listed above. With this calculation method however, adding one resource will select the solution set yielding the optimal strategic priority. More on that below.
The other important conclusion from this analysis is that the incremental cost doesn’t necessarily represent the incremental cost of adding another project to the mix. Rather, the incremental cost is the additional cost to the resource pool of adding a new resource – and not the entire cost of a project. The assumption, I guess, is that the cost of resources in the resource pool not already assigned to a project are not part of the calculation.
OK, so that’s how I developed it before I figured out my calculations were all wrong. Based on that, if I had to guess how Project Server handles this, it applies the same principles but without aggregating the projects into various scenario “buckets.” My guess (which will need to be validated in a subsequent post) is that each project deficit is then rounded to the the nearest Allocation Unit. Then the resources are applied to each project in the order of project priority.
I don’t think that will be too hard to model, so stay tuned for a revised worksheet to come out that addresses this.
Internal resource follow approximately the same logic, with one key exception. Instead of rounding the deficit to the allocation units as I do in the external resource example below, I have to apply slightly different logic. This is what the calculated deficit looks like for an external resource with an allocation unit set to 25%.
…and this is what the same scenario looks like when the option for internal resources is selected:
The same rules apply except that internal resources are allocated at a minimum of 100%. I can’t hire a permanent part time FTE. Similarly, once I hire the internal resource, I can’t fire the resource for the duration of the planning window. Hence, if I have a deficit of .7 in January 2011, that rounds to 1, and then is retained for the remainder of the planning horizon.
From there, I perform the same calculations as above to develop a cumulative deficit, maximum deficit, and incremental cost for each of the solutions.
You see that the internal resource ends up costing far more than the external resource.
Once we’ve developed these numbers, it’s relatively easy to run a portfolio selection/optimization scenario. The user enters the appropriate parameters in the Options tab. In the spreadsheet, the part of the Options tab is played by the Projects worksheet.
In this case, I entered 1 internal resource. Using the logic above, I calculate the total number of extra resources required for each of my scenarios. The calculation is a sum of the maximum deficit for each of the resource roles within each of the 32 potential solution sets.
From there, I assess the maximum strategic value where the deficit total is equal to or less than the amount of FTE’s entered in the Projects worksheet.
In the above screenshot, I reject any option which has a lower value than the baseline solution. From there, I compare the requirement for incremental resources against the additional resources entered in the Projects worksheet. That yields the solution set NYYYY with a strategic score of 90%.
Here’s the before picture:
…and here’s what it looks like after adding 1 internal FTE:
…for an additional cost of $192K
Next up…..calculating the impact of adding cost to a portfolio.