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 any Office application with a song written about it can’t be all bad.

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….

Overview

The Requirements Details view provides further information on the calculated what-if scenario. You can access the interface by selecting Requirements Details in the toolbar.

You’ll note that many of the toolbar options are greyed out in the Requirements Details view, so you will have to toggle back to the Gantt Chart view to recalculate or to revised any of the scenario options. And no, that fact certainly has never caught me fumbling in a live demo or presentation.

The view essentially looks like this. I typically like to check the Highlight Deficit button there in the top right.

You may have to drag the different divider bars over to expose more or less data. In the Excel model we’re using, the part of the Requirements Details view is played by the Requirements Details worksheet.

Resource Availability

The Resource Availability section at the top is pulled from our resource capacity figures, which I’ve discussed before. It’s critical to note that projects not included in the analysis will be decremented from any availability numbers. This may result in the existence of what I call the phantom project, a project which is not included in the analysis but appears to be driving overallocation calculations.

You’ll also note that the roles identified within the Resource Availability section are dynamic. If new roles are added to the lookup table driving the selected roles field, those roles will be added to this screen when it is refreshed – although any other new resource calculations will not be dynamically refreshed unless the Reload button is activated.

If the option is selected to display deficits, various cells will be highlighted in red. The deficit is not displayed in the Resource Availability view, but rather the supply is displayed with the areas where the deficit exists highlighted.

At this point, I should point out that I learned far more about conditional formatting in Excel than I ever wanted when trying to figure out how to get that deficit highlighting to work – which it may not do in Excel 2007 as apparently controlling conditional formatting from data in another worksheet was one of the improvements of Excel 2010 over Excel 2007. If you’re curious, I tied the conditional formatting to the numbers in the CALCDeficit worksheet.

Wherever those numbers are greater than zero, the Resource Availability numbers will be highlighted.

Project Requirements

The Project Requirements section shows the results of the calculation along with some key metrics:

Requirements

The Requirements field represents the total number of required man-months (person-months in the public sector) or man-quarters (person-quarters) per the granularity chosen way back at the beginning when we picked the key analysis parameters.

Essentially, I take a total of the row, and display it in the Requirements field.

I highlight the deficit using pretty much the same mechanism as in the previous section – with the key exception that the deficit is calculated in the order that the resources are allocated to a project. Hence, you’ll see that this view sorts projects by priority, with the deficit only displayed on less priority projects. The other key fact worth mentioning is that under normal circumstances, in the example above, Project B would be unselected as it is showing a resource deficit. Based on the fact that it is indeed selected, we can conclude that additional resources have been added to this specific portfolio selection scenario.

Moral of the story: the data behind the Requirements Details is not affected by the addition of incremental people or cost. To review the revised calculations with additional resources, you’ll need to refer to the Hired Resources report.

Deficit

The Deficit field is the total deficit in man-months (person-months – ok, you get the idea at this point) for the duration of the project.

The deficit is calculated even when the project is excluded from the analysis. Essentially, that means the Requirements Details view provides an overview of the baseline calculation, but won’t change as specific variables are modified, or as projects are forced in or out.

Calculating the Deficit

Let’s examine how the deficit is calculated.

The Deficit field represents the sum of the shortfall for each time period being calculated. For each role, we assess whether or not it exceeds the organizational supply, then sum the results. I do this in the CALCDeficit worksheet.

The deficit for the above example would be –2.8, or the total of .7, .6, .2, .5, .2, .3, and .3 (X –1).

How did I arrive at this calculation? To do that, I first had to calculate the required resource load for each project in the CalcDeficit worksheet. You’ll note that I have prioritized the projects in the order of strategic value, where Project E is the most valuable and Project A is the least valuable.

Then I had to return to my calculation of the organizational resource supply.

From there, it’s a relatively simple matter of decrementing each successive project from the overall total. For instance, in the case of January 2011, I have a total of 1 Role4 in my resource pool. Projects C & B both require that resource for that same time period, for a demand of .8 and .9 FTEs respectively. In the example below, I subtract .8 from 1 to get .2 remaining – then subtract .9 from .2 and get a deficit of .7.

Then, I sum up all of the deficits for each role…

…and sum up the deficits for each role within the project to identify the number that goes into the Requirements Details Deficit field.

Next up….incrementally adding new resources or cost.

RAUTH: The Requirements Details View