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. This post represents my breakdown of the Compare Project Portfolios screen.
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….
Following is a mockup of the Compare Project Portfolio screen from the Big Honkin’ Spreadsheet (BHS) that I’ve been using to illustrate these calculations. Note that the mockup is relatively close to what the Compare screen looks like in Project Server, although you can actually display quite a few portfolio scenarios in Project Server. In my poor Excel mockup, I only reproduce two, the baseline and the current live what-if scenario.
The first thing you’ll note is that the portfolios are displayed in alphabetical order – not in the order created. For this reason, if you’re trying to keep all of your scenarios straight, you may want to consider a standardized naming convention, for instance something like “1-Scenario 1, 2-Scenario – 2, etc.”
The Strategic Value, Additional Resource Constraint (Cost), and Additional Resource Constraint (FTE) are relatively straightforward fields that pull data directly from the scenario selection. In the BHS, that data is pulled directly from the Projects worksheet. Similarly the Baseline and Selected Scenario fields are pulled more or less from the same source.
The key calculations in my opinion lie in the Additional Resources (Cost) and Additional Resources (Work) fields. It took me a while to figure out where those numbers are derived from, but they represent the additional cost to the resource pool of adding resources to support additional projects. These numbers do not represent the additional marginal cost of adding a new project to the mix.
To derive these numbers, I first have to calculate the sum of the deficit for each time period in the portfolio selection scenario, and multiply that times the number of hours in a given month. The formula looks roughly like this:
Additional Cost = Sum(Deficit) X 160 Hours/Month X Average Cost / Role (as defined in the Cost Rate Table selection).
Additional Work = Sum(Deficit) X 160 Hours/Month
The minor caveat to this statement is that as of this writing, Project Server might have a minor glitch in the calculations, and it seems to multiply the Additional Work result by 600, yielding a strangely large number. In the following screenshot, I have added 1 resource, and the system calculates additional work of 2,616 hours.
When I save this, and display the Compare screen, I see an Additional Work of 2,616 X 600 or 1,569,600.
Hopefully, that’s a minor fix in an upcoming patch. Otherwise get used to the fact that Additional Work is displayed in 6 second increments – or in units of .1 minutes. Maybe that’s the Mayan calendar.
A minor caveat perhaps of the Compare screen is that it cannot display the Efficient Frontier curve if you’ve saved a portfolio scenario using the Cost Analysis functionality, then switched over to the Resource Analysis and saved a second scenario. This makes sense to me, as essentially they’re two totally separate portfolios – and a strategic value of 100% in the Resource Analysis module (being a subset of the selected projects in the Cost Analysis module) does not map to a strategic value of 100% in the Cost Analysis module.
If you do manage to save both types of scenarios within the Compare screen, you will see a message as follows where the Efficient Frontier should be.
For more information about how the Efficient Frontier is calculated, please see my post from a couple of months ago on the topic.
…and that’s about all there is to say about the Compare Project Portfolio screen. Next up, the Deficit and Surpluses Report.