324 – NPV versus BCR part 3

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.

Range of budget levelsOptimal version of A projectOptimal version of G project
$0 to $9nilnil
$10 to $49nilG1
$50 to $59A1G1
$60 to $99nilG2
$100 to $159A1G2
$160 to $259A2G2
$260 to $409A2G3
$410 and higherA2G4

 

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.

323 – NPV versus BCR part 2

In PD322 we looked at whether to use Net Present Value (NPV) or Benefit: Cost Ratio (BCR) in Benefit: Cost Analysis (BCA) when assessing and comparing separate, unrelated projects. What if they are not separate, unrelated projects?

The most common scenario where you have to go beyond the simple rules presented in PD322 is where you are comparing different versions of the same project (e.g. different scales or different actions, but addressing the same broad goal in the same region). They are not separate, unrelated projects – they are mutually exclusive. If you do one of them, it rules out doing any of the others.

It is good practice to assess multiple versions of the same project before settling on a particular version. Versions of a project with more ambitious targets can deliver greater benefits, but also incur greater costs, so it is usually not readily apparent how ambitious the project should be. (Project versions also vary on dimensions other than ambitiousness, such as their spatial targeting, or the specific actions they will include.) The first project version to be specified may or may not end up being the best version when several versions are compared.

However, if you are ranking projects, and the projects you are ranking consist of multiple versions of the same project, using BCR for the ranking process will probably not give you the correct result.

The following example should give you a feel for why BCR does not give you the correct project ranking in this situation.

Suppose that projects X1 and X2 are two versions of project X. If you did project version X2, you would have to bear the cost of doing X2, and in addition, you would bear the opportunity cost of not doing project X1 (i.e., you would miss out on the net benefits of doing X1). Similarly, the full cost of doing X1 should include the opportunity cost of not doing X2. That’s why the traditional BCRs of projects X1 and X2 do not provide a reliable ranking — there are additional costs that a BCR doesn’t capture.

The obvious solution is to include the opportunity cost of not doing the best alternative project when calculating the BCR. However, this is an inconvenient approach because the identity of the best alternative project depends on the available budget. Each time you generated results for a different budget level, you would have to recalculate all the BCRs based on different opportunity costs.

A much simpler approach (that gives the same answer) is to choose the project with the largest NPV that you can afford within the funds allocated for this project. For example, if you were faced with choosing from amongst the five project versions in the table below, and the available budget was $600, you could afford any of the projects and you would choose project 4, which provides the largest NPV of $490. If the budget was only $200, you would choose project 3, which provides an NPV of $440. (You could no longer afford project 4 because it costs $310.)

Note that projects 3 and 4 don’t offer the highest traditional BCRs, which is provided by project 1 (BCR = 20), but we don’t want project 1 because it has the lowest NPV (only $190), and the lowest adjusted BCR.

ProjectPV(Benefits)PV(Costs)NPVBCR
1$200$10$19020.0
2$400$60$3406.7
3$600$160$4403.8
4$800$310$4902.6
5$1000$560$4401.8

 

NPV/BCR Rule 3: If selecting from different versions of the same project, choose the project with the largest NPV that you can afford within the funds allocated for this project.

The above NPV rule is based on the assumption that you must choose one project version from a set of mutually exclusive project versions, and that the funding is committed to be used for one of these project versions and won’t be used for other projects.

The next Pannell Discussion looks at a more complex scenario where the decision-maker is faced with choosing from multiple separate projects, and there are multiple mutually-exclusive versions of at least one of the projects.