Chapter 9 Video: Sensitivity

In this Excel tutorial I'm going to be talking to you about a sensitivity analysis. Whenever you're doing these types of projects you’ll have information about cost and benefits and interest rate. And whenever you're curious about what alternative would I choose if something changed, that's a sensitivity analysis. In this particular example, we're going to be looking at what if our interest rate changes, which alternative would I go with? So we have under our information, we have our years of analysis to B5. We have alternative A, B and C with their cost and uniform annual benefit. Now in order to do an analysis by changing the interest rate, I just set up a table that had interest rate that went through increments just different increments to try it out. What I did here in cell D9 was I calculated the present value of alternative A or present worth.

That turns out to be, if you follow the formula, that's rate which was in C9, then it was followed my by my year, which was five. Never changes so I've got that locked. My payment is negative or is negative D5 and then I have to subtract off the initial cost to get my present value. And I hit return. Now the reason I did the..this way is so I can grab this corner and just pull the formula down and everything will increment to the various interest rates and make a calculation easy. I repeated this for the present worth of B and the present worth of C. Then all I did was made a graph. Now let me highlight the graph. What I did is I have the interest rates running across my x-axis. My y-axis is the present value and you can see the kind of green line is the present worth of C, the kind of red is B and the kind of blue is A.

So notice as the interest interest rate is changing notice how the lines are changing. Now in order to get an idea of you know, what's going on, if you look at this graph, you can see that C is in the lead up to a certain point and about right in here, it looks like C might drop off and you might be picking somebody else. And then that person seems to lead till you get about right here and then you're picking somebody else. Where are those break point? How do I make a decision on which alternative to pick based on whatever interest rate I might have? Well one thing we can do here is to calculate our IRR for each of the projects. So this would just be rate, then I'm going to have 5 comma my payment comma negative this and I have an interest rate or an IRR 15.24.

I'm going to lock that B2 cell by hitting the F4 key and then I can just grab this and come across. So what this is telling me is a project A, the IRR, that blue spot right here is 15.24. But what is this crossover Point here? Well, it looks like just by looking at the graph of that C and B competing with each other. So I'm going to put C minus B and that will equal my 4,000 minus my 3,000 and this would equal my F5 minus my E5. And then I can just grab my corner here and pull over and you can see that 4.06 is the interest rate there and then B seems to be in the lead and then down here looks like B and A are competing. So, let's see what B minus A looks like. This would equal B minus A, this would equal B minus A and then I can just grab the corner here and pull this over. And you can see these different interest rates. So now what this show..is showing you and let's kind of zoom in on this graph so you can see. I'm going to go up to about 150.

I hope this will show up better. Okay, let me get over here and we can take a look. Notice what this is saying is that up to 4.06, or about 4.1%, I would prefer C. It's making the most money for me. After that point I see that B is in the lead. So between the 4.06 and the about 7.93 B is my favorite. But at 7.93 notice that A takes over. I'm hoping you can see those break points in that graph. So let's go back to what we were originally at. And so what this is saying in this sensitivity analysis is depending on what interest rate it is, is what your alternative. If you're MARR is less than 4.1% you are going to go with C. If your MARR is between the 4.1 and the 7.9 you're going to go with B.

If the MARR is greater than the 7.93 but less than 15.24 you're going to go with A. And if you're MARR is greater than 15.24 you're going to do nothing because you're going to start losing money. So in this Excel tutorial, I've shown you how you can do a sensitivity analysis to help you determine which alternative I'm going..an I going to pick depending on the interest rate that is..ha..is being incurred.