PLEASE INCLUDE ALL FORMULAS FOR EXCEL
3 1. Assume that Sidney Johnson is confident of her estimates of all the variables that affect the project's cash flows except unit sales and sales price: If product acceptance is poor, unit sales would be only 900 units a year and the unit price would only be $160; a strong consumer response would produce sales of 1,600 units and a unit price of $240. Sidney believes that there is a 25% chance of poor acceptance, a 25% chance of excellent acceptance, and a 50% chance of average acceptance (the base case). (1.) What is the worst-case NPV? The best-case NPV? See table below (2.) Use the worst-, most likely, and best-case NPVs and probabilities of occurrence to find the project's expected NPV and standard deviation. See table below We could find the NPV by entering the value of unit sales and price for each scenario and then recording the NPV (this is what we did for the table below). Alternatively, we could use Tools, Scenarios to define the inputs for each scenario, which we did and show in the Scenario Summary Tab below. In fact, you could even use Tools, Scenarios, and then click the Summary button on the dialog box, and it will automatically create a table similar to the one below. 5 5 7 8 9 0 1 2 3 .4 55 56 57 8 59 70 71 72 73 74 75 76 77 78 79 80 81 82 Scenario Analysis Scenario Probability Unit Sales Unit Price NPV Best Case Base Case Worst Case 2596 5096 2596 1,600 1.250 900 $240 $200 $160 $278.940 $88,010 ($48.527) Expected NPV - Standard Deviation- Coefficient of Variation - Std Dev / Expected NPV - 3 1. Assume that Sidney Johnson is confident of her estimates of all the variables that affect the project's cash flows except unit sales and sales price: If product acceptance is poor, unit sales would be only 900 units a year and the unit price would only be $160; a strong consumer response would produce sales of 1,600 units and a unit price of $240. Sidney believes that there is a 25% chance of poor acceptance, a 25% chance of excellent acceptance, and a 50% chance of average acceptance (the base case). (1.) What is the worst-case NPV? The best-case NPV? See table below (2.) Use the worst-, most likely, and best-case NPVs and probabilities of occurrence to find the project's expected NPV and standard deviation. See table below We could find the NPV by entering the value of unit sales and price for each scenario and then recording the NPV (this is what we did for the table below). Alternatively, we could use Tools, Scenarios to define the inputs for each scenario, which we did and show in the Scenario Summary Tab below. In fact, you could even use Tools, Scenarios, and then click the Summary button on the dialog box, and it will automatically create a table similar to the one below. 5 5 7 8 9 0 1 2 3 .4 55 56 57 8 59 70 71 72 73 74 75 76 77 78 79 80 81 82 Scenario Analysis Scenario Probability Unit Sales Unit Price NPV Best Case Base Case Worst Case 2596 5096 2596 1,600 1.250 900 $240 $200 $160 $278.940 $88,010 ($48.527) Expected NPV - Standard Deviation- Coefficient of Variation - Std Dev / Expected NPV
