Excel (Formula)

41 D 1 2 Chapter 9 - Master It! 3 For this Master itl 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: 4 5 What is the profitability index of the project? What is the IRR of the project? 60 7 8 b. 9 10 C 11 12 d. What is the NPV of the project? 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. 13 How sensitive is the NPV to changes in the quantity sold? 14 e 15 16 17 18 B 1 2 Master It! Solution 3 $ 4 5 6 Equipment Pretax salvage value R&D Marketing study $ $ $ 38,500,000 5,800,000 1,200,000 250,000 7 B Year 3 87,000 Year 4 78,000 Year 5 54,000 9 10 Year 1 64,000 80,000 15,000 14.29% Sales (units) Sales of old smartphone Lost sales Depreciation rate Year 2 106,000 60,000 15,000 24.49% 11 12 13 8.93% 12.49% 17.49% 14 $ $ $ 15 16 17 18 19 20 Price VC FC Tax rate NWC percentage Required return 515 210 5,300,000 22% 20% 12% 21 22 23 24 25 26 27 28 29 Sales VC Fixed costs Dep Tax NI +Dep OCF Year 1 $32,960,000 13,440,000 5,300,000 5,501,650 $8,718,350 1,918,037 $6,800,313 5,501,650 $12,301,963 Year 2 $54,590,000 22,260,000 5,300,000 9,428,650 $17,601,350 3,872,297 $13,729,053 9,428,650 $23,157,703 Year 3 $44,805,000 18,270,000 5,300,000 6,733,650 $14,501,350 3,190,297 $11,311,053 6,733,650 $18,044,703 Year 4 $40,170,000 16,380,000 5,300,000 4,808,650 $13,681,350 3,009,897 $10,671,453 4,808,650 $15,480,103 Year 5 $27,810,000 11,340,000 5,300,000 3,438,050 $7,731,950 1,701,029 $6,030,921 3,438,050 $9,468,971 30 31 32 33 34 35 36 37 38 39 NWC Beg End NWC CF $0 6,592,000 ($6,592,000) $6,592,000 10,918,000 ($4,326,000) $10,918,000 8,961,000 $1,957,000 $8,961,000 8,034,000 $927,000 $8,034,000 0 $8,034,000 $27,483,703 $1,434,971 $16,087,703 $14,553,93 $18,893,963 Net CF 40 Salvage BV of equipment Taxes S 42 43 5,800,000 44 45 Salvage CF 46 Cash flow 47 Time 0 48 49 50 2 3 51 4 5 52 53 54 55 g Profitability index IRR 56 57 b. 58 59 C NPV bo 61 d. NPV Price per unit 62 63 64 65 66 67 68 69 $ $ $ $ $ $ $ $ $ $ $ s $ $ 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 400 410 420 430 440 450 460 470 480 490 500 510 520 530 540 550 560 570 580 590 600 $ S $ $ $ $ ANPV/APrice 87 e. 88 NPV 89 90 ANPV/AQuantity 91 92 93