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 Scatter 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:
…and for the worksheet that I will be referring to in the screenshots: Announcing the Catapult Portfolio Simulator v1.
Developing the Scatter Chart was relatively straightforward, but required two main intellectual exercises: defining the terms used, and defining the scenarios to be compared.
The first thing I point out is somewhat obvious, but perhaps indicative of cognitive bias on my part: the Scatter Chart is not a Bubble Chart. It is a Scatter Chart – hence the misleading name of “Scatter Chart” in the Ribbon interface.
Bubble charts have become kind of a personal joke for me when it comes to project portfolio management. I spent a couple years attending every demo I could find on portfolio management tools from any number of vendors. During these demos, I sometimes entertained myself by tracking how many minutes into the presentation the vendor would get before showing their first bubble chart. It was rarely more than 20 minutes. (These days, I play the same game with the term “folksonomy” in SharePoint 2010 presentations. Try it sometime.)
I’ve also seen people in the newsgroups refer to this view as a Bubble Chart. So let the records be very clear on this one…..the chart is not a Bubble Chart, as the individual solution points do not vary in size.
Here’s a look at the two chart types for the same data. You’ll note that the bubble chart is set to vary the size of the bubble based on the proposed cost of the project.
The next set of terms that I need to define are the legend terms used in the chart. Project Server flags four different elements in this chart:
It all seems straightforward, but when I started working through the model, I realized that this leads to a a key question. What about the collateral damage of forcing a project into the mix?
Let’s take an example from the following portfolio scenario:
What happens if I force in Project 1?
Project 5 gets moved out. Now the inherent question is how does Microsoft Project Server classify Project 5? Is that now an Unselected project or a Forced Out project? Technically, I didn’t force out Project 5, I forced in Project 1, thus pushing Project 5 out of the mix. After throwing a couple of scenarios into Project Server, my conclusion is that Project 5 would be classified as Unselected, and not Forced Out.
Once we have the terms defined, developing the Scatter Chart is a simple matter of comparing scenarios. In the companion Excel workbook, I have captured two scenarios in the data table on the OpSctrChart Worksheet:
1) The optimal scenario for a specific budgetary cost constraint.
2) The selected scenario based on project force in/out options.
Thus, the portfolio configuration illustrated above represents in the data table as:
Using Excel magic, I determine the appropriate label for each project where the Optimal Status does not equal the Selected Status. For more on that calculation, please refer to the Excel worksheet.
We then translate that into a scatter chart using some Excel gyrations and dynamically named ranges, netting the following result:
….and to misquote Forest Gump, that’s pretty much all I have to say about that. I may have one other post on this topic lined up, but intend to tackle the concept of project interdependencies and some of the other Portfolio Analysis features at some point in the future.