Question

Video Excel Online Structured Activity: NPV profiles A company is considering two mutually exclusive expansion plans. Plan A requires a $40 million expenditure on a large-scale integrated plant that would provide expected cash flows of $6.39 million per year for 20 years, Plan B requires a $11 million expenditure to build a somewhat less efficient, more labor-intensive plant with an expected cash flow of $2.47 million per year for 20 years. The firm's WACC is 10%. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. Open spreadsheet a. Calculate each project's NPV. Round your answers to two decimal places. Do not round your intermediate calculations. Enter your answers in millions. For example, an answer of $10,550,000 should be entered as 10.55. Plan A: $ million Plan B: $ million Calculate each project's IRR. Round your answer to Plan A: % Plan B: b. By graphing the NPV profiles for Plan A and Plan B, approximate the crossover rate to the nearest percent. c. Calculate the crossover rate where the two projects' NPVs are equal. Round your answer to two decimal places.

Answer 1

SEE THE IMAGE. ANY DOUBTS, FEEL FREE TO ASK. THUMBS UP PLEASE

projects (Autosaved) (Autosaved) (Autosaved) - Microsoft Excel (Product Activation Failed) Review File Home Insert Page Layout Formulas Data View Add-Ins Cut E AutoSum - AC Wrap Text Calibri 11 General Copy Fill Merge & Center Insert Delete Format Paste в IU* +.0 .00 .00 0 Conditional Format Cell Sort & Find & % Format Painter Clear Filter Select Formatting as Table Styles Clipboard Editing Font Alignment Number Styles Cells fox LR620 LU LK LL LM LN LO LP LO LR LS LT LU LV 606 YEAR PLAN A PLAN B 607 -40 -11 RATE 10% a 608 6.39 2.47 NPV $14.40 NPV(LR607,LL608: LL627) +LL607] $10.03 NPV(LR607,LM608:LM627) +LM607] 609 2 6.39 2.47 PLAN A 610 3 6.39 2.47 PLAN B 611 4 6.39 2.47 612 6.39 2.47 IRR 613 6 6.39 2.47 15.00% [ EIRR ( LL607:LL627)] 22.04% [EIRR (LM607:LM627)] 614 7 6.39 2.47 PLAN A 615 6.39 2.47 PLAN B 6.39 616 2.47 617 10 6.39 2.47 618 6.39 11 2.47 619 12 6.39 2.47 620 13 6.39 2.47 621 14 6.39 2.47 Horizontal (Value) Axis 2.47 622 15 6.39 623 16 6.39 2.47 624 17 6.39 2.47 625 18 6.39 2.47 626 19 6.39 2.47 627 20 6.39 2.47 628 IRRnpvbeta STO EEE 110% NPV RELEVANT RATION PBP ARR invest, EAA MI-np-ir REPLACEMENT S HPR GMAM EAC MACRS BETA LEVERED REPLACEMENT BOND OPTION TO WAIT Rea erences: NL296 II WA X 06:37 ENG 04-11-2019

projects (Autosaved) (Autosaved) (Autosaved) - Microsoft Excel (Product Activation Failed) Review File Home Insert Page Layout Formulas Data View Add-Ins E AutoSum - Cut AC Wrap Text Calibri 11 General ECopy Fill Merge & Center $ % Paste в IU* +.0 .00 Conditional Format Cell Insert Delete Format Sort & Find & Filter Select .00 0 Format Painter Clear Formatting as Table Styles Editing Clipboard Font Alignment Number Styles Cells LQ683 LN LO LP LQ LR LS LT LU LV LW LX LY 667 [=NPV(LO671,$LL$608:$LL$627) +$LL$607] b 668 $100.00 669 NPV PROFILE NPV (B) NPV(A) $ 87.80 $64.49 670 RATE $ 38.40 671 0% S 80.00 $29.39 672 2% $ 39.63 $27.70 $19.78 673 5% $15.17 674 7% $60.00 $14.40 $10.03 675 10% $7.73 $7.45 676 12% $ 4.46 $ 2.22 $0.02 $-0.00 15% 677 NPV(A) $40.00 $-5.80 678 18% NPV (B) $-11.50 679 22% 680 $20.00 681 APPROXIMATE CROSSOVER RATE = 12% 682 683 $0.00 O% 684 20% 5% 10% 15% 25% 685 686 $-20.00 687 RATE 688 689 IRRnpvbeta ST E 110% invest, EAA MI-np-ir NPV RATION PBP ARR REPLACEMENT S HPR GMAM EAC MACRS RELEVANT BETA LEVERED REPLACEMENT BOND OPTION TO WAIT Rea erences: SM219 II WA X 06:41 ENG 04-11-2019 NPV

projects (Autosaved) (Autosaved) (Autosaved) - Microsoft Excel (Product Activation Failed) Review File Home Insert Page Layout Formulas Data View Add-Ins E AutoSum - Cut AC Wrap Text Calibri 11 General Copy Fill B I U В IUT Merge & Center Paste +.0 .00 Conditional Format Cell Insert Delete Format Sort & Find & % .00 0 Format Painter Clear Filter Select Formatting as Table Styles Number Clipboard Font Alignment Styles Cells Editing fox LQ654 LU LK LL LM LN LO LP LQ LR LS LT LU LV LW 639 YEAR PLAN A PLAN B A-B C 640 -40 -11 -29 641 1 6.39 2.47 3.92 CROSSOVER RATE 12.15% EIRR(LN640:LN660)] 642 2 6.39 2.47 3.92 3.92 643 3 6.39 2.47 644 4 6.39 2.47 3.92 645 6.39 2.47 3.92 YES 646 6 6.39 2.47 3.92 NPV IS BETTER THAN IRR 3.92 647 7 6.39 2.47 NPV IS BETTER THAN IRR AS IT SHOWS HOW MUCH WEALTH IS ADDED FOR SHAREHOLDERS 648 6.39 2.47 3.92 SECONDLY, WHEN THERE ARE MORE THAN ONE OUTLFLOWS, IRR FAILS TO GIVE CORRECT ANSWER 649 6.39 2.47 3.92 NPV ASSUMES WACC AS REINVESTMENT RATE, WHICH IS MORE RELIABLE THAN IRR RATE ASSUMPTION 650 10 6.39 2.47 3.92 6.39 651 11 2.47 3.92 3.92 652 12 6.39 2.47 653 13 6.39 2.47 3.92 654 14 6.39 2.47 3.92 655 15 6.39 2.47 3.92 3.92 656 16 6.39 2.47 657 17 6.39 2.47 3.92 658 18 6.39 2.47 3.92 659 19 6.39 2.47 3.92 660 20 6.39 2.47 3.92 661 STO E 110% IRRnpvbeta NPV BOND RATION PBP ARR invest, EAA MI-np-ir REPLACEMENT S HPR GMAM EAC MACRS RELEVANT BETA LEVERED REPLACEMENT OPTION TO WAIT II Rea erences: KS336 WA 06:43 ENG 04-11-2019