Hi, after making some effort, we were able to come up with yearly cashflows of the Magical Bubblegum project. In this lecture, we will calculate the projects NPV, payback period, and IRR, and make investment decisions based on calculated values of them. In the Excel spreadsheet, we'll also show our decision using IF function. IF function requires three inputs, the first is the Logical test, the outcome after test has to be either true or false, so an example of a logical test is three is greater than five. We can answer whether this is true or false. The second input is value, or sometimes a formula that will be shown if the results of the logical test is true. The last input is value or formula, that will be shown if the results is false. To summarize, IF function has three inputs, A, B and C. And it has the structure of if A is true, show B, otherwise, show C. After making investment decisions, we also attempt to conduct a Sensitivity Analysis. When you compute a project's NPV, can you fully trust your results and stick to the single number? Remember, that your analysis is only as strong as its assumptions. Earlier, we have used a point estimate of the discount rate, which was 20%, for example. But, realistically, it might make more sense to use a range instead, such as from 19% to 21%, given the uncertainty underlying our assumption. For example, what if the true discount rate is 21%, not 20%? How does that affect our decision? What if the sales growth rate turns out to be 7% not 5%? We test the impact of possible changes in these assumptions on variation using sensitivity analysis. So, how we perform a sensitivity analysis in Excel? We're going to use the tool named What-if Analysis. It can be found in data tab in the menu, choose What-if Analysis, and then select Data Table. Now, let's go to the spreadsheet. First of all, we'd like to know the project's NPV, Payment period and IRR to make a decision. Since we have both cash flows in row 25, and a discount rate in cell B7, we can calculate the NPV of the project. Great, the NPV is $1,280. The number indicates that this is an acceptable project. We have learned how to calculate the payback period of a project before. I'm not going to repeat the time consuming process, but simply give you the result. The payback period of this project is 3.32 years. Finally, we can calculate the IRR of the project using IRR function. The IRR of the project is 25.53%. In other words, the project’s expected return on investment is 25.53% a year. Next, in column N, we will show our decision by simply printing Yes or No. First, we know that we say yes with the NPV greater than zero. Then, the logical test for the first IF function becomes the calculated NPV in cell K2 is greater than zero, if it is true, it will print yes, otherwise, it will print no. Since we have a positive NPV, the IF function shows us yes. Next, let's say our cut off period for the payback period rule is three years. Then, we accept the project only if the payback period is shorter than three years. Hence, the right logical test for the second IF function is the calculated payback period in cell "K3" is smaller than three. You can see that the second IF function is showing no, because the payback period is longer than three years. Next, do you remember the decision rule for IRR? A project is acceptable if the IRR is greater than the actual discount rate. So the right logical test here is the IRR in cell K4, is greater than the discount rate in cell B7. And of course, the outcome is Yes. To summarize, this project is acceptable, when we look at its NPV and IRR, but, it's not acceptable if we use the payback period method. But we know that NPV should be the primary capital budgeting technique in most cases. So overall, the project seems to be a good one. Now, we would like to perform a sensitivity analysis. Here, I made a five by five table to see how changes in two important variables impact the project’s NPV. When we have more than ten variables in the assumption table, actually, any variable can be a candidate for the subject of this analysis. However, this time, I'll choose the discount rate in B7 and the growth rate in cell B4 and see how the NPV is affected by changes in these two variables. The first step is to reference the cell, where NPV is calculated which is K2 in the green cell. Then you should see the same value in the green cell. Now, next, we write five different possible values of the discount rate by having the default value, 20%, in the middle. Here, we do not reference any other cells, but just enter those five numbers manually. Similarly, in column J, we write five possible values of the growth rate. Again, by having the default value, 5%, in the middle. The next step is to select the entire table by selecting from cell J10 to O15. With the sensitivity table selected, now go to the Data tab, and select what if analysis, and then select Data table. Then, you'll see a small window, as shown in this screen. In this window, we indicate the two variables whose values we are going to change during the analysis. First, select cell B7, which has the discount rate as row input cell. Next, select cell B4, which has the sales growth rate, as column input cell. If you click OK, you'll see that the sensitivity on this table is immediately filled with 25 numbers. The table tells us that, for example, even if the discount rate remains at 20%, if the growth rate is 3% not 5% the NPV of the project will be only $978. This sensitivity analysis is a very useful tool in practice, in that it shows how the value of your project can change as important variables in assumptions, such as the discount rate and the growth rate change. What does you have in your mind after reading this sensitivity analysis table? If I were the manager, I would be relieved to see that the project still has a positive MPV, even under a very harsh assumption that the discount rate is 21% and the growth rate is only 3%.