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 Efficient Frontier is calculated for a portfolio of projects. 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.
My goal with this post is to describe how Microsoft Office Project Server 2010 works, not to get into a debate about whether or not the item described below qualifies as an Efficient Frontier (EF) or a Ranking Curve. As such I note that there is a vigorous online discussion around this topic, but intend to use the same terminology as Microsoft does.
Identifying the Solution Set
As in the previous examples, the first step lies in identifying the solution set. As established in those posts, for the sample portfolio of 5 projects, we have a solution set comprised of 2 to the 5th (or 32) potential solutions. In this notation, “NYNNY” indicates a portfolio including Projects 2 and 5, but not 1, 3 or 4.
Each of these potential solutions has a specific Proposed Cost and Strategic Value associated with them. I can plot each of those solutions in a scatter chart based on those two characteristics.
The Efficient Frontier represents the optimal use of resources for each incremental addition of a new project to the mix. A nice description of the Efficient Frontier, as paraphrased from the DecisionFrameworks site: “…portfolios plotted by cost and value delineate the efficient frontier. A portfolio is efficient if no other portfolio has more value for the same or less cost, and if no other portfolio has less cost for the same or more value.” In layman’s terms, that means the EF is the left most edge of the points plotted in the scatter chart. Look at the chart below, and assess if any other point off of the red highlighted line “has more value for the same or lower cost.”
If we take the following numbers as an example…
…we can generate a rough EF Curve for demonstration purposes. Each point on this curve represents the incremental addition of Cost and Value for a single new project in the solution. In this case, I am adding projects in the order of the Benefit / Cost Ratio (calculated as Strategic Value / Proposed Cost.) Based on this calculation, I should add Project 4 first, as it presents the greatest BCR, then followed by Project 2, 3, 5, and 1 – in that order.
That yields something like this:
That graph almost gets us where we want, but it is based on a simple plotting of 6 solution points:
Such a scenario with only 6 points would only happen if the projects were all estimated at identical costs. For instance, if I assign a proposed cost of $10,000 for each project, I end up with only 6 different price points for all of the potential 32 solutions.
….plotted in the Efficient Frontier curve, that would look like this:
In our modeled portfolio, where each project has a unique cost, the solution is not quite that simple. We end up with multiple combinations of projects that may impact how the curve is plotted. In the following example, we have additional solutions that represent the most efficient solution for a specific price point. Note that each labeled solution represents a potential 4 project selection scenario (YYYYN and NYYYY). We can see that NYYYY generates more value than YYYYN.
If I set a budget constraint of $243K, my optimal solution is YYYYN, with a Strategic Value of 81.27%. For only $5K more, I can achieve NYYYY, with a Strategic Value of 93.65%. In BCR terms, that represents an increase of .002 in Strategic Value per dollar spent. Similarly we can look at another combination of points and determine that moving from NYYYN to NYYNY would be a significant investment that would only nominally increase overall Strategic Value. In this case we would classify the incremental improvement as having a BCR of .00009, or an increase in Strategic Value of .00009 for every dollar spent. The implication here is that marginal spending generates more value in the places where the EF curve is steeper.
Defining the Plotted Points
So if we agree that the Efficient Frontier is the leftmost line of potential solutions, how do we plot that in Excel? It was surprisingly simple in fact. To generate that line in Excel, I had to take a couple of steps:
1) Create a Pivot Table listing the maximum Strategic Value for each Proposal Cost point. Depending on how the individual project costs are estimated, there may be multiple solution sets within the same price point. We want the one with the biggest bang for the buck, the biggest BCR, and the highest Strategic Value. In this example, where each project has a different cost, we end up with 32 unique cost points.
2) Identify the positive trend in the Max Score/Cost column, and eliminate any rows where the Strategic Value is lower than in any row above. This allows us to identify the points that meet our criteria of “…no other portfolio has more value for the same or less cost” and “no other portfolio has less cost for the same or more value.”
3) Take the remaining 12 data points and plot them on a scatter chart. We now have an Efficient Frontier.
When we map this to the total set of 32 solutions, we can see that this highlights the left most points, and thus satisfies the requirements for the Efficient Frontier,
Please note that I will be following up on this post with a later post. There’re a couple of interesting things about how Project Server 2010 displays the Efficient Frontier that I need to get my head around – which I plan to do in the next week or so.
Next up: Calculating Strategic Alignment