In my previous post, I announced that I would spend the next couple of weeks examining the Resource Analysis component of the Portfolio Analysis module in Project Server 2010.  The general goal is to use a big honkin’ Excel spreadsheet I developed, which I will henceforth refer to as “The BHS” to demonstrate the various calculations. 

“Why an Excel sheet?” you may ask.  Honestly, I am not sure, but I figured it would be entertaining and a worthwhile exercise to develop, so here we are.  (Perhaps it could be said that Excel lurks at the intersection of Dadaism and Portfolio Analysis?)

This post will address specific considerations in how the Enterprise Resource Pool should be configured within Project Server to support some of the Resource Analysis features.  I will be using an Excel spreadsheet because it’s easier to tweak that for screenshots then messing with a full blown resource pool.


In the BHS, I populated the Resource Pool in the Resource Pool worksheet.


I set Max. Units appropriately, and then populate the other key fields. 

As in all things Project Server related, you should ensure that the resource calendars are updated, as that may affect availability calculations – depending on the granularity with which you are performing your Resource Analysis.  (On the other hand, vacations typically are logged with a pretty short planning horizon, and may not make it into your resource calculations for the next quarter’s portfolio analysis exercise.)

I added Hire Date and Fire Date fields to my Resource Pool simulation simply because I felt that performing these calculations without any element of timephased capacity modeling would be cheating.  In the “real” world, those calculations would be handled within the resource calendars.

I then translate those availability numbers to a timephased resource capacity calculation in the CALCTimePhaseResCap worksheet.  For the purpose of this exercise, I am using a 24 month look ahead period from January 2011 through December 2012.  The granularity is set to monthly, a topic I will visit in the next posting.


Role Fields 

For the Resource Analysis feature to work properly, you will then need to assign each resource to a specific project role.  This requires the creation of an Enterprise Custom Resource Field tied to a lookup table.  In this case, I have imaginatively created five roles, Role1-Role5, and assigned my resources accordingly.

The specific role field used in the Analysis is defined when the Analysis is created.  This may give opportunities to slice and dice different Analyses using different custom fields, perhaps one by location, and one by simple project role.


It took me a little while to figure this out, but if you plan to use the incremental FTE option within the Resource Analysis, i.e. to calculate whether or not additional projects may be included with the addition of resources, you’ll need to note that standard rates for each role are calculated as the average for all resources within the Resource Pool assigned to that specific role.  Thus when, you tell the tool to calculate the additional cost for a Role1 resource, the system will calculate the required hours and multiply them by the average cost for all Role1 resources within the Resource Pool. 

As a result, you might take care to map resources in different countries with significantly different salary structures to different roles.  Otherwise, you might end up averaging a German and Chinese resource assigned to the Business Analysis Role to come up with the standard cost for a specific role.  Presumably this issue could be resolved by using an Outline field within the Resource Pool for role definition (i.e. Germany.Analyst and China.Analyst), but I haven’t tested that specific functionality and the ramifications thereof.

Standard Rates (And Cost Tables)

As discussed above, at one point in the Resource Analysis module we will use the average rate for each resource within the Resource Pool to assess the cost of additional hired FTEs.  This is not functionality that is strictly required to perform Resource Analysis, but if you want to assess the incremental costs of  adding resources to supplement a given portfolio, you will need to populate the Standard Rate field for each resource.  Note that for analysis purposes, you can always toggle between the 5 Cost Rate Tables (A-E).

In this example, I provide the option to toggle between two cost rate tables, A and B.


For display then, I calculate the average cost for each resource role underneath the Resource Pool.  Note that the average cost is never actually displayed within the Portfolio Optimizer, but can be deduced from reviewing the results of adding incremental FTEs to the calculations.


Developing the Resource Demand Profile

This topic doesn’t technically fall under preparing the Resource Pool, but since I didn’t have any other place to stick it, I’m tacking it on to this post.  Project Server allows two separate methods to define the resource demand profile:

1) Bottom Up – assigning resources to specific tasks and then publishing the schedule within PWA or the Project Professional client.

2) Top Down – assigning resources to a Resource Plan and then attaching that to a schedule and publishing.

Both approaches work just fine, and are well documented in any number of sources.  The one nuance I identified in my testing was that manually scheduled tasks have a certain distinct behavior, which is particularly evident when used to capture the resource demand profile for a specific project.  Manually scheduled tasks require the population of three fields for the resource work to appear in the Resource Analysis feature.  Specifically, those three fields consist of Start, Finish, and Duration.  If you simply create a new task, assign a resource, assign duration, and neglect to set a Start date, the project will show up without resources in the Portfolio Analysis module.

Hence, it’s important to at least set two of those three fields.  Once two of the fields have been set, Project Professional will calculate the third.

And that’s it for the prelude.  Next up: Configuring the Resource Analysis Parameters