Chapter 9 Video: Benefit Cost

In this excel tutorial I’m going to be discussing a benefit cost analysis. As you can see under our information we have 3 alternatives that we’re looking at. Each one has initial cost, a uniform annual benefit. Notice they each have different lives, project A is 5, project B is 8 and project C is 6. The interest rate that we’ll be using for these calculations is 8%. Now whenever you’re doing a benefit cost analysis you have to have everything in the same time period. So, you can do benefit cost with present worth on top, present worth on bottom, but in this case we’re going to do benefit cost. The top will be equivalent uniform annual benefits and the denominator will be equivalent uniform annual costs. Notice our benefits are already in the..eh..in the annual form.

So, what I need to do is to get those initial costs into equivalent ah an EUAC. So, notice here in cell D7 my formula is equals payment, I’ve locked my interest rate in at 8%, which is B2 comma, then I’m going to go for my years, which is D6 and my initial cost was negative D4. The reason I locked the B2 cell is so I can grab the corner of this formula and pull it across for my remaining alternatives. So, got that there, now I can pull this across and you can see the equivalent uniform annual costs for each of the remaining alternatives. Now, to figure out our benefit cost it’s simply the annual benefit divided by the annual cost, or the EUAB divided by the EUAC. So, my annual benefit was D5 and I divided it by my annual cost which was D7, I get 0.96. Now if I grab the corner I can do this for my remaining 2.

Notice how it carried it on across. And as you can see, alternative A has a benefit cost less than 1. Therefor it’s out of the running and the only 2 alternatives I need to even consider are B and C. To compare B and C we’ll have to do an incremental analysis. It’s Very simple for these 2, it’s simply the delta cost, the delta benefit and the delta benefit cost. So, what you’re gonna do is you’re gonna take the project C, so this will be equals the cost for project C is here, minus the cost for project B, which is here. And you can see we get an answer. And then for benefits it’s going to equal the benefits of project C minus the benefits of project E ah…B, there. And then our benefit cost is simply equals benefit divided by cost. And you can see that we got 1.07. What that means is by having a value greater than 1 the additional cost of project C was worth it. So, in the end out of all these alternatives, project C is your best.