Question

Use excel and show how formulas and such work. please follow every steps thorougly

rad oNI.Y) to do this. Show all your work. Print the spread sheet showing data and all your Analyses. your company is considering one of three alternatives to move part of their operations overseas. The company uses a MARR of 1 5%. Options 1: Equipment cost now- $1,000,000 l equipment purchase at the end of year 2 $500,000 Annual operation and maintenance (M & O)-$85,000 Life of Project 10 years Salvage value -Nil Options 2: Contract Production (sub-contracting production works) $300,000 for now and $250,000 from years 1 through 10 Annual operation and maintenance (M&O)-Nil Life of Project 10 years Salvage value-Nil Options 3: Equipment cost now $450,000 Annual operation and maintenance (M & O)-$40,000 in year l and increases by 10% every year till year 5. Maintenance at year 9 for $40,000 Life of Project- 10 years Salvage value $50,000 i. Determine the best Alternative using Present Analysis ii. Calculate the IRR for each alternative using Excel (ONLY) ii Use IRR incremental analysis to pick the best alternative

Answer 1

Present Value Analysis 4 Option 1 Option 2 Option Year (1,000,000) (85,000) (585,000) (85,000) (85,000) (85,000) (85,000) (85,000) (85,000) (85,000) (85,000) (300,000) (250,000) (250,000) (250,000) (250,000) (250,000) (250,000) (250,000) (250,000) (250,000) (250,000) (450,000) (40,000) (44,000) (48,400) (53,240) (58,564) 0 10 12 (40,000) 50,000 15 16 I 17 18 19 20 21 10 (1,554,692.16) -NPV(15%,E7:E16)+E6 (607,929.77) :NPV( 15%,F7:F16)+F6 NPV @ 15% (1,804,667.17) :NPV(15%,D7:D16)+D6 Best Alternative is Option 3 as it has minimum present cost

23 Incremental IRR Option 3 Option 2 Option 1- Option 3 Year 25 26 27 28 29 30 31 32 0 (150,000) 210,000 206,000 201,600 196,760 191,436 250,000 250,000 250,000 210,000 300,000 (550,000) (45,000) (541,000) (36,600) (31,760) (26,436) (85,000) (85,000) (85,000) (45,000) (135,000) 35 36 37 38 39 10 #NUM! -IRR(E25:E35,) IRR 138% IRR(D25:D35,) Clearly Option 3 is the superior alternative