Master It! Excel Chapter

Question is in first image, please show how to solve areas highlighted blue. Please show excel functions as well thank you very much!

Chapter 9 - Master it! For this Master It! assignment, refer to the Conch Republic Electronics case at the end of Chapter 9. For your convenience, we have entered the relevant values in the case such as the price, variable cost, etc. on the next page. For this project, answer the following questions: 6a. What is the profitability index of the project? What is the IRR of the project? What is the NPV of the project? 11 How sensitive is the NPV to changes in the price of the new PDA? Construct a one way data table to help you answer this question. 12 d. 13 14 e. 15 How sensitive is the NPV to changes in the quantity sold? Master it! Solution Equipment $ 32,500,000 $ 32.500 Pretax salvage value $ 3,500,000 R&D $ 750,000 Marketing study $ 200,000 Year 3 108,000 Year 4 94,000 Year 5 57,000 Sales (units) Sales of old PDA Lost sales Depreciation rate Year 1 65,000 80,000 15,000 14.29% Year 2 82,000 60,000 15,000 24.49% 17.49% 12.49% 8.93% 500 Price VC FC 215 Tax rate NWC percentage Required return 4,300,000 35% 20% 12% Year 1 Year 2 Year 3 Year 4 Year 5 Sales VC Fixed costs Dep EBT Tax NI +Dep OCF NWC Beg End NWC CF Net CF Salvage BV of equipment Taxes Salvage CF Cash flow 55 a. Profitability index IRR C. NPV 61 d. Price per unit 450 460 480 en in en es es es es es es es es es es es us is us us us us in 570 590 600 ANPV/APrice To find the sensitivity of the NPV to changes in quantity, we need to change the quantity sold manually since the quantity sold is different each year. If we add 100 units to each year's qauntity sold, the NPV is: NPV ANPV/AQuantity Chapter 9 Section 9.3 Section 9.4 Section 9.6 Scenario Summary Setting a Bid Price M in. price Master it! Solution i Chapter 9 - Master it! For this Master It! assignment, refer to the Conch Republic Electronics case at the end of Chapter 9. For your convenience, we have entered the relevant values in the case such as the price, variable cost, etc. on the next page. For this project, answer the following questions: 6a. What is the profitability index of the project? What is the IRR of the project? What is the NPV of the project? 11 How sensitive is the NPV to changes in the price of the new PDA? Construct a one way data table to help you answer this question. 12 d. 13 14 e. 15 How sensitive is the NPV to changes in the quantity sold? Master it! Solution Equipment $ 32,500,000 $ 32.500 Pretax salvage value $ 3,500,000 R&D $ 750,000 Marketing study $ 200,000 Year 3 108,000 Year 4 94,000 Year 5 57,000 Sales (units) Sales of old PDA Lost sales Depreciation rate Year 1 65,000 80,000 15,000 14.29% Year 2 82,000 60,000 15,000 24.49% 17.49% 12.49% 8.93% 500 Price VC FC 215 Tax rate NWC percentage Required return 4,300,000 35% 20% 12% Year 1 Year 2 Year 3 Year 4 Year 5 Sales VC Fixed costs Dep EBT Tax NI +Dep OCF NWC Beg End NWC CF Net CF Salvage BV of equipment Taxes Salvage CF Cash flow 55 a. Profitability index IRR C. NPV 61 d. Price per unit 450 460 480 en in en es es es es es es es es es es es us is us us us us in 570 590 600 ANPV/APrice To find the sensitivity of the NPV to changes in quantity, we need to change the quantity sold manually since the quantity sold is different each year. If we add 100 units to each year's qauntity sold, the NPV is: NPV ANPV/AQuantity Chapter 9 Section 9.3 Section 9.4 Section 9.6 Scenario Summary Setting a Bid Price M in. price Master it! Solution