This is the next post in a series describing how the Portfolio Analysis pieces of Project Server 2010 work under the hood.  This post will discuss how the Strategic Alignment chart is calculated and provide an Excel model for assessing how it works.  I wrote this post as part of an exercise to make sure that I understand what’s going on in Project Server – as well as making an initial attempt to lay the pieces out in a way that my clients would understand.  My goal is to develop an Excel-based model that takes the same inputs as Project Server and then generates approximately the same outputs.  As such, I do not guarantee that I have captured Project Server’s own heuristics exactly, but I feel that I have captured the background concepts well enough to explain them.

DISCLAIMER: The worksheet referenced below is not a competing product to Project Server 2010, and does not contain references to any proprietary code.  It was developed based on publicly available information as an Excel mock up of the server-side portfolio optimization calculations found in Project Server.  The goal of this post is for educational purposes only.

Now that we have that out of the way….for previous posts in this series, please refer to:

Pairwise Analysis Demystified

Pairwise Analysis Revisited

Developing the Solution Set/Assessing Cost Constraints

Forcing In/Out Projects

Calculating the Efficient Frontier

…and for the worksheet that I will be referring to in the screenshots: Announcing the Catapult Portfolio Simulator v1.

Strategic Alignment Calculations

To prepare the Strategic Alignment, chart, we need three pieces of information:

1) Our prioritized list of drivers:


2) Our mapping of projects to drivers:


3) And lastly, the estimated costs for each of the projects in the chosen portfolio solution:


Then we need to normalize the driver score for each project.  Let’s take Project 1 as an example.  The driver mapping for Project 1 yields results of 7.14, 0, 7.14, 7.14 and 4.55 for drivers 1-5 respectively.  That makes a total of 25.97.  To understand how much of the cost of the project is “allocated” to Driver 1, we divide 7.14 by 25.97, yielding .27, or 27%.  From this, we can conclude that approximately 27% of the project cost maps to Driver 1.

As the total project is estimated at $75,099, we can take 27% of that to get an estimated allocation of $20,276 to Driver 1.

Following that logic we take our original Project-Driver mapping…


….normalize it by row:


Then multiply the results by the proposed cost.


We then sum each column and divide the results by the amount of the total selected portfolio cost to give us the total % invested in a specific driver.  In the above example, we calculate for Driver 1 that $70,587/$323,207 yields a % allocation of 21.84%.

The final step is to compare the original driver priorities we started out with to the % allocated to each driver.


…which charted out, looks something like this:


And there you are.  Next up: developing the Scatter Chart.