i want it in Excel( formula)

2 2 Chapter 9 - Master it! 4 5 60 7 Bb 9 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: What is the profitability index of the project? What is the IRR of the project? What is the NPV of the project? 10 c 11 12 d. 13 How sensitive is the NPV to changes in the price of the new smartphone? Construct a one way data table to help you answer this question How sensitive is the NPV to changes in the quantity sold? 14 e 15 16 17 18 Draw Insert Formulas Page Layout Data View Review Home Help Calib 12 A A == > 2 Wrap Text Merge & Center General $ % 8-28 pasto BIU A Conditional Format as Call Formatting Table Styles Styles Clipboard Number Font 15 Alignment A1 H B E D 1 2 Master It! Solution $ 5 Equipment Pretax salvage value R&D Marketing study $ $ $ 38,500,000 5,800,000 1,200,000 250,000 6 7 8 9 Year 3 87,000 Year 4 78,000 Year 5 54,000 Sales (units) Sales of old smartphone Lost sales Depreciation rate Year 1 64,000 80,000 15,000 14.29% 10 11 12 13 14 15 16 Year 2 106,000 60,000 15,000 24.49% 12.49% 17.49% 8.93% Price Vc $ $ $ 17 515 210 5,300,000 22% 20% 12% FC Tax rate NWC percentage Required return 18 19 20 Year 1 Year 2 Year 3 Year 4 Year 5 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 Sales VC Fixed costs Dep Tax NI +Dep OCF NWC Beg End NWC CF 38 39 Net CF 40 41 42 43 Salvage BV of equipment Taxes 44 45 Salvage CF 46 47 Cash flow Time 0 48 49 1 50 2 3 4 5 51 52 53 54 55 a. 56 57 b. 58 Profitability index IRR 59 NPV 60 61 d. Price per unit NPV 62 63 $ 65 $ S 66 67 $ 68 69 70 71 72 73 74 75 76 77 78 79 400 410 420 430 440 450 460 470 480 490 500 510 520 530 540 550 560 570 580 590 600 $ $ $ $ $ 80 $ $ $ $ 81 82 83 84 85 86 ANPV/APrice