Chapter 9 Video: What If

In this Excel tutorial, I'm going to be discussing a what-if analysis. Let's say you have some information about a new project that you're working on and you have your information presented here in our Excel spreadsheet. We have a first cost. We figure we could sell 800 units per year. Our net unit revenue is $45, project lasts six years and our interest rate is 11%. Sometimes when we're guessing or trying to project how our projects can do, sometimes we can be too optimistic. So to help you get a better idea of how things can be going you can use some adjusted values to see how it would change the answer that you're interested in.

In this case, we're going to look at benefit cost ratio. So in this project, we have our initial Investments, but we're thinking we might be too optimistic. So let's make some adjustments. We think our first costs will change by 5%, our units per year would increased by 20%, our net revenue or net unit revenues off by 10%. Life could be maybe four years, but we think we're pretty accurate on the I. So let's calculate what would happen if we adjusted our values for this. In this case our initial investment would equal our C3, which is our first cost times 1 plus our adjusted value of 5% that's in D3. And then we see that our we think our first cost adjusted would be more like $131,000 or a little bit more than that. Our units per year, this would equal C4 times 1 plus. The adjustment there. And then we could see that this could possibly go down to 640 units. Notice that I have that as a dollar sign and I'd like to change that to just a number.

So I'm going to click there. And I don't need any decimals for this, nor do I need any here so I'll just adjust those. Now here for our net unit Revenue, we are going to adjust that. We'd be C5 times 1 plus D5. And we can see how that changes to $40.50. This would simply equal C6 plus D6. And we'd see it'd be 4years and our final one would equal C7 times 1 oops! 1 plus D7. And we see that it doesn't change. Now we can with this information with our initial and our adjusted let's calculate our benefits cost ratio. First, let's calculate our benefits. We need to take the benefits which is the 800 units we sell per year, times the $45 we make for each one of those units and bring it back to the present with our 11% interest.

So this would equal present value. I need my rate which is in C7 comma, I need my years, which is in..ah..C6 and then I need my payment, and to make this formula work I'm going to have to go with a negative C5 times the number of units which is in C4 of 800. And we get a present value of our benefits of a little over $152,000. Let's also do that for our adjusted values. That would equal the present value. I'm doing the same thing except I'm doing it on our adjusted. So it's E6 or I mean, excuse me E7, I need rate first. So let me fix that. I need rate, then I need year, then. I need minus E5, which is what our units would sell for, times the number of units, which is in E4.

And we get that present value. Now to do our benefit cost ratio remember, it's just the ratio of benefits to cost. This would equal our benefits which are in C9 divided by our cost, which is located in C3. And you can see we get 1.22. Remember a benefit cost ratio over 1 is a good thing. Now let's look at our adjusted. This would equal E9 divided by our cost which is already at present of E3. And you can see we get more like 0.61. Which is under one and looking a little shaky for whether that's going to have enough benefits to justify the cost. But notice that you could do this with several different adjustment values to get a more comfortable feel on kind of the range or maybe what your project might actually be. So in this Excel tutorial, I've shown you how to do a what-if analysis.