Friday, November 9, 2012

A tool to select which projects bring the most benefit

How do you select the combination of projects that will bring the most benefit to your organization? If you have 2 projects, the decision has just 4 possible outcomes, so it's easy to find a way to compare them. But if you have 8 projects that you could do, there are quite a few more combinations (256, to be precise). This article, hopefully, will ease this pain.

The easy way out

You should start a selection process by making sure every candidate project fits the organization strategy, that you have the necessary conditions to start them (enough people or the right technology) and so on. But supposing that your candidate projects are all equal regarding everything else, it all goes down to numbers. What benefits do they bring? And how much will they cost? This is an ideal situation where you just have these 2 variables (benefits and costs) to drive your decision. The truth is this never happens - because this project takes too long, because this one is too risky or because there's not enough know how within the organization or whatever other reason.
Nevertheless, the benefits and the costs should play a role on this decision and with them alone, this is the easiest decision. If you also ignore the ratio between benefits and cost (you should prefer a project that gives you a ¨profit¨ (meaning the benefit minus the costs) of 100 with an investment of 200 over a project that gives you the same profit of 100 with an investment of 500), all it's left for you to do is to find the combination that gives the most profit without going over budget.
This is all about portfolio management and picking the right set of projects to drive your organization forward.

Binary linear programming

The mathematical tool that lets you solve this kind of problem is binary linear programming. Linear programming is when you want to maximize something, in this case the profit of the projects, by playing with some variables (the projects selection) and having some constraints (like not going over budget). When your variables are zeros and ones, then you have binary linear programming.
Odd thing though, the only tools I could find to do those are expensive enterprise project management tools or mathematical tools. Microsoft Excel does have a linear programming tool fairly easy to use but... It doesn't deal with these binary cases. I even tried to trick excel by putting the binary variables as constraints, but the model misbehaves. So the logical next step was...

Build the tool myself

How hard can it be to compute the results of all the possible outcomes? Not very. And on one hand, by sharing it I may save some time to some people. And on the other hand, this simple tool may be improved by others. The way it works is as follows:

1. Define the budget

Just fill in the value you have for your next year's budget, how much can you spend

2. Define the number of projects

Select the first 2 columns of the table and drag them to the right. That is the only way to make it and still be able to make calculations using the right number of projects.

3. Define the benefits

For each project, fill in the present value of its benefits. It is important, specially in long projects, to bring all money to today's value.

4. Define the costs

The same, but with costs.

5. And click the button!

The results will take a split second to show. The first part shows what was the optimal selection. Next, what will be the profit and finally what will cost you.

The Tool

This tools (a Microsoft Excel 2010 file with macros) is available for download from the Downloads section and directly from here - but it seems you have to get a Google account...
It works like this: instead of implementing a complex linear programming algorithm that would let you solve a problem with many thousands of projects, it just tries out every possibility, computes the results and gives you back the best result found - but it only work with 1000 projects or so. But that's enough for me. Probably, if you have 1000 projects to decide from, you have a full Enterprise Project Management tool in place that can do this and a lot more.


This is to solve the simplest of problems in project selection. But it can gives you hints at early stages and it can help you make a final selection when all the projects you have are all as good as the next one...
So have fun playing with this and make this tool better if you can!

Posted by

No comments: