In my previous post, I gave an example of how Pairwise Analysis works, and presented a conceptual model in Excel of the driver and project prioritization engine within Microsoft Project Server 2010. That model worked well with simple driver prioritization scenarios, but seemed to display a wide variance with Project Server calculations in more complex scenarios. This left me unsatisfied, and so I decided to play with the model a bit more to see if I could reasonably emulate the Project Server calculations.
The good news is that I was successfully able to reproduce the server side driver calculations. The Excel workbook I used to develop this is available here, and continue reading for more information on how I did it. I must admit that working through these calculations did at times make me wish I’d spent less time translating classical Chinese poetry in college, and perhaps picked up a calculator once or twice.
I’ll point out that from a training and education standpoint, I am all in favor of keeping the model simple. There’s no need to throw complex statistical manipulations at a PMO learning how to use the system. A simple but inaccurate model as described in the previous post would suffice from a “behind the scenes” perspective. The following model is more appropriate if you are looking to exactly simulate Project Server results in an offline environment.
Attempt #1: KISS
In my first attempt, I simply totaled each of the driver scores, and then normalized the results to get my driver rankings. That approach is well documented, and gives us a reasonable approximation of Project Server calculations.
When checked against calculations performed on the server, I got the following results:
Not bad, and not too far off. The maximum variance is Driver 5 at about 4 points off of what the server calculated. I note that the ranking of drivers is a bit off. The server calculated the sequence of drivers from top to bottom as 4 – 5 – 1- 3 – 2, while my calculations deliver a result of 4 – 1 – 5 – 3 – 2, in other words, swapping out drivers 5 and 1 in sequence. I would contend that the difference in ranking is of negligible significance, and that the weighting is still approximately the same.
Attempt #2: MATRIX.XLA
Feeling somewhat like a blindfolded monkey pounding on a typewriter, I eventually figured out the correct solution.
For this attempt, I decided to break out the big guns, and downloaded the free MATRIX.XLA (v2.3) add-in for Microsoft Excel. (Click here to download, and grazie to the team from Foxes for making this really slick tool available.)
The MATRIX.XLA tool adds a couple of extra functions to Excel, most notably the MEIGENVALMAX and the MEIGENVEC functions. Each of those functions are described in the add-in support documentation. Using those two functions, the calculations were a simple matter:
Exposing the formulas, here’s how it looks…
All I had to do was normalize the eigenvector to achieve the appropriate ranking for each driver. This calculation seemed to match Project Server’s results almost exactly.
For more information on eigenvalues and eigenvectors, I encourage you to consult with your search engine or local statistician.
I also made some minor tweaks to the Project/Driver scoring values and the Project prioritization calculations. Nothing major, but this brings them into line with the Ignite slide deck on Portfolio Management and produces more accurate results. As the formulas are relatively simple, I would encourage you to consult the Excel workbook for more details.
Finally, if you are one of the 10 people out there who find this topic fascinating, follow these simple instructions to get the spreadsheet working with the Matrix add-in:
1) Download the MATRIX.XLA (v2.3) add in from here. Unzip and store in an accessible folder.
2) Open Excel. In Excel 2007 and 2010, go to File > Options > Add Ins. Select Manage Excel Add-Ins in the dropdown at the bottom, and click on the “Go” button.
3) Select Browse in the Add-Ins dialog box, and browse to the folder where the unzipped MATRIX.XLA files are. Select the MATRIX.XLA file.
4) Open the Portfolio Analysis simulation spreadsheet. If prompted, enable external links.
5) In the Ribbon, under Data, select Edit Links. Select the option to Change the Source, and point the new Source at the unzipped file. Click Check Status to confirm that Excel sees the file.
6) Save the spreadsheet.
The spreadsheet will now work fine unless you move the MATRIX.XLA reference file. At that point, it’s a simple matter of updating the links to point to the new location again. Note that this worked in a Vista/Excel 2010 environment, and on a Vista/Excel 2007 environment, but did not work in an XP/Excel 2007 environment. I haven’t tested in Windows 7.