A company has a 11% WACC and is considering two mutually exclusive investments (that cannot be repeated) with the following cash flows: 0 1 2 3 4 5 6 7 -$387 -$193 -$100 $600 -$180 Project A Project B -$300 -$400 $600 $135 $850 $135 $135 $135 $135 $135 $0 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. X Open spreadsheet a. What is each project's NPV? Round your answer to the nearest cent. Do not round your intermediate calculations. Project A: $ Project B: $ b. What is each project's IRR? Round your answer to two decimal places. Project A: % Project B: % c. What is each project's MIRR? (Hint: Consider Period 7 as the end of Project B's life.) Round your answer to two decimal places. Do not round your intermediate calculations. c. What is each project's MIRR? (Hint: Consider Period 7 as the end of Project B's life.) Round your answer to two decimal places. Do not round your intermediate calculations. Project A: % Project B: % d. From your answers to parts a-c, which project would be selected? If the WACC was 18%, which project would be selected? e. Construct NPV profiles for Projects A and B. Round your answers to the nearest cent. Do not round your intermediate calculations. Negative value should be indicated by a minus sign. Discount Rate NPV Project A NPV Project B 0% $ $ 5 $ $ 10 $ $ 12 $ $ 15 $ $ 18.1 $ $ e. Construct NPV profiles for Projects A and B. Round your answers to the nearest cent. Do not round your intermediate calculations. Negative value should be indicated by a minus sign. Discount Rate NPV Project A NPV Project B 0% $ $ 5 $ 10 $ $ 12 $ $ 15 $ $ $ 18.1 $ 24.83 $ f. Calculate the crossover rate where the two projects' NPVs are equal. Round your answer to two decimal places. Do not round your intermediate calculations. % g. What is each project's MIRR at a WACC of 18%? Round your answer to two decimal places. Do not round your intermediate calculations. Project A: % Project B: % Excel template - Saved Search (Option - Single Line Ribbon Sign in 8 File Home Insert Formulas Data Review View Help Editing Comments Arial v 10 B CA PE EV 22 Currency ID D CHI: .. Ev B12 fi B C E F G H H 1 1 2 M N A Capital budgeting criteria 2 3 WACC 11.00% 1 2 4 0 -3300 3 - 3100 $600 5 $800 B $80 7 3100 $400 $135 $135 $135 $135 S135 $ 136 $0 Formulas INNA WNA #NA UNA 5 6 Project A 7 8 Project B 9 10 11 Project NPV Calculations: 12 NPVA 13 14 NOV 15 16 Project IRR Galculations: 17 IRRA 18 19 IRRE 20 21 Project MIRR Calculations: 22 MIRR 23 24 Alternatively, MIRRA CAN be calculated as: 25 26 Project A 27 28 PV of Year 1 Oullion 29 PV of Year 2 Outfi 30 PV of Year 3 Outllow 31 PV of Year 7 Outllow 32 33 34 35 = Sheet1 + + WNA 5 0 -$300 1 5387 2 $193 3 -$100 4 $600 Sann 6 6 $850 7 -510 Formulas UNIA #NIA UNIA WNIA Formulas ANIA ANA ANA FV of Year 6 Inflow At YAA 7 FV of Year 5 Inflow at Year 7 FV of Year 4 Inflow at Year 7 Calculation Mode: Automatic Workbook Statistics Give Feedback to Microsoft - 80% + Excel template Saved Search (Option - Q) Single Line Ribbon Sign in ( 8 File Home Insert Formulas Data Review View Help Editing Comments Arial 10 M 10 B = Currency Call BE Ev yv O 5 | II. B12 fi A D E F G H I 1 M N B #NIA #NIA 30 PV of Year 3 Outlo 31 PV of Year 7 Out 32 33 Formules #NA NA ANA FV of Year 6 Intion at Year 7 FV of Year 5 Inflow at Year 7 FV of Year 4 Infyw at Yaar 7 #NIA #NA Sum of Infow FVS Fomulas 35 36 Sum of Outflora PVS 37 39 N 39 PV 40 PMT 41 FV 42 VYR = MIRRA 43 44 MIRR 45 46 Alarnatively, MWR can be calculated as: 7 50.00 0 0 $0.00 ANA NINA 0 3400 $135 3186 2 S135 3 S135 4 $135 5 S135 8 $135 7 $0 48 Project 49 50 51 52 53 Formulas ANIA #NA #NA ONA #NA UNA FV of Year 6 Infon et Year 7 FV of Year 5 Intion at Yaar 7 FV of Year 4 Intion at Year 7 FV of Year 3 Inflow at Year 7 FV of YRA 2 Info At Yaar 7 FV of Year 1 Inflow at Year 7 55 #N/A ANA Sum of Inow FVA Formulas 57 595.im of Oulka PVA 59 60 N 61 PV 62 PMT 63 FV 61 VYR = MIRRA Sheet1 7 $0.00 0 $0.00 ANA + Calculation Mode: Automatic Workbook Statistics Give Feedback to Microsoft 80% + Excel template - Saved Search (Option - Q) Single Line Ribbon Sign in (8 File Home Insert Formulas Data Review View Help Editing Comments Arial 10 B Currency v 4 II. B12 fo B D E F G H 1 1 K M N Formulas 7 5000 0 $0.00 ANA 59 60 N 61 PV 62 PMI 63 FV 64 VYR-MIRRE 65 66 Project Acceptance: 67 WACC 68 Accept 69 70 WACC 71 NPVA 72 NPV. 73 Accept 11.00% ANA 18.00% $2.86 $72 18 ANA NPVA NPV. ST2.18 Discount Rates NPVA NPV. $2 66 $2.66 $72.18 75 NPV Profiles: 76 Discount, Rates 73 78 79 90 81 82 5.00% 10.00% 12.00% 15.00% 18.1095 24.03% 0% 500% 1000N 1200X 1500 ANA ANA ANA PNA PNA ANA ANA #NIA ANIA #N/A DNIA ONIA ONIA NIA 18 10% 84 24,83% Excel template - Saved Search (Option - Single Line Ribbon Sign in ( 8 File Home Insert Formulas Data Review View Help Editing Comments Arial v 10 B A Currency ID > .. CHI: B12 ft B E F G H 1 1 K M N 96 87 88 NPV Profiles $1.20 10.00% 12.00% . 15.00% 18.10% 2 24.63% 90 $1.00 91 92 $0.80 93 94 $0.60 95 95 $0.40 97 $0.20 99 100 $0.00 101 0% 5.00% 102 103 101 Calculation of Crossover Rate: 105 106 Project A 107 108 Project 109 110 Pro Act Delta 111 112 113 Crossover Rate - IRR 114 115 Project MRR Calculations at WACC - 18% 115 WACC 112 118 MIRRA 119 MIRR, 120 $300 1 S387 2 $193 $100 4 S800 5 3600 B $850 7 -180 -$400 $135 $135 3135 S135 3135 S135 SO NIA NA ANA NIA NIA NA NIA 18.00% WNIA ANAA Sheet1 + Calculation Mode: Automatic Workbook Statistics Give Feedback to Microsoft - 80% +