Chapter 16: Benefit Cost

In this Excel tutorial I'm going to be talking to you about incremental benefit cost analysis. As you may remember we looked at benefit-cost previously, but in this one we're going to bump it up a notch by having more alternative to select from if you only have two it's pretty straightforward, but when you get three or more this becomes a little more complex and that's what we're going to be talking about here. So in our information, we have four projects; A, B, C and D. They have a first cost and annual benefit, annual operating and maintenance, salvage value, project Life and an interest rate. So the first thing we need to do is calculate the benefit cost for each alternative. Any alternative that has a benefit cost less than one is eliminated from the pool. So the first thing you have to do is see is everybody still in the hunt or is there somebody I can eliminate.

So let's review the formula for benefit-cost. Basically you're going to do the present worth of the benefits and divide it by the present worth of the cost. So as you can see here in my formula bar, that’s your interest, so I am bringing back our benefits first. Our benefits are annual benefits and salvage. So this first part before the divider is doing the present value of those two things. Then I'm going to divide by negative C..C3 because first cost is already seen there and it's money that leaves you and then I'm bringing back the present value of the O & M cost and that's your formula. Now notice that some things have the dollar signs around them. That means I've locked those cells.

So once I have this formula in I can drag it across and easily calculate the remaining ones. So let's just grab the corner here and drag it across and what you can see is alternative B does not have a benefit-cost ratio greater than 1 so this one is eliminated from further evaluation. Now those that are left. We have A, C and D. So what we are gonna do is figure out the present worth of the cost. So I've simply taken C 3 plus the present present value of the O & M costs here and I've repeated that for project C and project D. Now, let's rank order our project based on that. Remember in any scenario do nothing is still an alternative. It's on the table. So do nothing is our first option because it doesn't cost us anything followed by project A, followed by project D, followed by project C.

Okay, now we know that we can start to do our incremental calculations. The first comparison we're going to do is do nothing versus A, which you can see here. So we're going to do the Delta first cost. So this is simply do nothing cost us nothing minus C 3 which is our first cost and I've repeated this all the way down so it's 0 minus whatever it cost for project A. Then I repeated the formula for benefit cost ratio notice that it's less than 1 which means the cost of A is not being justified. So is A Justified? No, so do nothing still hanging out. Now we're going to take do nothing versus Project D, which was the next one in our list. In this case, we're going to take 0 minus F3 which is where the Project D information is, for repeated for each of these and then I recreated continued the benefit cost ratio.

Notice here it's 1.09, which means that additional cost of D is worth it. So in this case, yes D is worth it. So now D is our last is our current lead candidate and we're going to now take a look at D versus C. So here we've taken the first cost for D, which is F3 minus the first cost for C which was E3 and we've got the values here and we've repeated this on down. When we get here and do our benefit cost analysisit turns out it's 1.48, which means yes that that additional cost has been Justified that initial present worth cost has been Justified. So in our final analysis we select project C, because it turns out to have the best benefit cost when compared to all alternatives.