In PD322 and PD323 we have been exploring whether to use Net Present Value (NPV) or Benefit: Cost Ratio (BCR) in Benefit: Cost Analysis (BCA) when assessing and comparing projects. I’ve presented some simple rules to follow when the projects are separate and unrelated, or when the projects are mutually exclusive (i.e., when you can only choose to do one of them). But what if the decision maker is faced with choosing from multiple separate projects, and there are multiple versions of at least one of the projects?

Before getting into the details, I want to clarify that, in all the examples I’m presenting in this series, I’m assuming that the objective is to maximise the total NPV across all funded projects. I should have emphasised that earlier. A key message from the three related blog posts is that to get the highest total NPV, you should not necessarily choose the projects with the highest individual NPVs. Sometimes that’s the case but in other cases it’s not.

Now, suppose that a decision maker is faced with selecting from three different versions of a project to protect Lake Antelope (call them projects A1, A2 and A3) and four versions of a project to protect Lake Giraffe (projects G1, G2, G3 and G4). In this situation, there is no simple rule to follow, like just choose the projects with the highest BCRs or the highest NPVs. Instead, this situation requires the use of a constrained optimisation algorithm. Assuming that you are choosing whole projects (i.e. you can’t choose 0.7 of a project), the required algorithm is called integer programming.

Fortunately, integer programming is available in the Excel spreadsheet software, and it is not difficult to use it to select the optimal portfolio of projects in this complex situation. I’ll present the numbers for a relatively simple example of this complex decision problem, and then I’ll show you how to solve it in a brief YouTube video.

First the example. The present values of benefits (B) and costs (C) for the project versions for Lake Antelope and Lake Giraffe are as follows: project A1: B=$180, C=$40; project A2: B=$360, C=$100; project A3: B=$400, C=$200; project G1: B=$200, C=$10; project G2: B=$400, C=$60; project G3: B=$600, C=$160; project G4: B=$800, C=$310. The available budget is $300. You can choose at most one of the Lake Antelope project versions and at most one of the Lake Giraffe project versions. Which project versions (if any) should you choose to maximise the overall net benefits?

Watch the video to see how to use integer programming to solve this in Excel. Within the capacity of the software, this approach will work for any number of projects and any number of project versions.

Note that the information you get from a model like this is not a simple ranking of the projects. Instead, it tells you how the optimal combination of projects and project versions changes depending on the available budget. To get this information, you change the program budget in the model and re-solve it. For this example, the results look like this.

The two project versions in each row of the table come as a package. There is no point in ranking them. If the budget was to shrink, you would not just drop one of these, you would change which project version you selected, as shown in the table.

NPV/BCR Rule 4: If selecting and ranking multiple projects, and at least one of the projects is available in multiple versions, don’t use NPV or BCR. Instead, use integer programming to optimise the selection of projects and project versions simultaneously.

Having to create a constrained optimisation model like this might seem like more bother than is worthwhile, but it isn’t difficult, it doesn’t take long, and it may result in substantially higher benefits being generated by the program, compared with the use of a simpler rule-of-thumb.

If you’ve read all three of these Pannell Discussions on NPV and BCR, your head may be spinning at the complexity of all this. Apologies for that, but the complexity is real and needs to be understood if analysts applying BCA are to be sure of giving sound advice to decision makers.

Appendix

Another realistic complexity that might be relevant in some cases is that there might be two separate constraints on the funding of projects. For example, there could be a limited budget available for initial project implementation and a separate limited budget available for ongoing maintenance. As with the example above, neither BCR nor NPV is sure to rank the projects correctly in this situation, and you need to use a method like integer programming to be sure of getting it right. You could build an Excel model like the one in the video, and include separate constraints for implementation costs and maintenance costs, rather than a single constraint for costs overall, which is what I did in the video.

On the other hand, in the numerical examples I’ve looked at, it is usually not too terrible to assume that implementation costs and maintenance costs are drawn from one limited budget. In other words, using BCR to rank separate, unrelated projects can still be OK even if there are two constraints on funding. But it is an approximation and it might not give you the best possible solution.