Answered step by step
Verified Expert Solution
Question
1 Approved Answer
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
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
PLEASE INCLUDE ALL FORMULAS FOR EXCEL
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started