r. Capital budgeting criteria 1 Video Excel Online Structured Activity: Capital budgeting criteria A company has a 12% 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 Project A Project B -$300 - $405 - $387 $134 -$193 $134 -$100 $134 $600 $134 $600 $134 $850 $134 -$180 $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. Open spreadsheet a. What is each project's NPV? Round your answer to the nearest cent. Do not round your intermediate calculations. y: Capital budgeting criteria a. What is each project's NPV? Round your answer to the nearest cent. Do not round your intermediate calculations. Project A: $ 200.41 Project B: $ 145.93 b. What is each project's IRR? Round your answer to two decimal places. Project A: 18.10 % Project B: 23.97 % 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, 15.10 % Project A: 17.03 % Project B: d. From your answers to parts a-c, which project would be selected? Project A LIS Capital budgeting criteria If the WACC was 18%, which project would be selected? Project B V 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% $ 890.00 $ 399.00 5 $ $ 10 $ $ 12 $ $ 15 $ $ 18.1 $ -0.08 23.97 $ $ 0.02 f. Calculate the crossover rate where the two projects' NPVs are equal. Round your answer to two decimal places. Do not y: Capital budgeting criteria 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 $ 890.00 NPV Project B $ 399.00 0% 5 $ 10 $ $ 12 $ 15 $ $ 18.1 $ -0.08 $ 23.97 $ $ 0.02 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. 14.53 % y: Capital budgeting criteria 15 > 3 33 18.1 $ -0.08 $ 23.97 $ $ 0.02 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. 14.53% 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: 18.05 % Project B: 20.49 % Excel template - Saved File Home Insert Formulas Data Review View Help Tell me what you want to do e comments Arial 10 - AA Ourrency XOR Copy formatie Am Char- yo Parte BTW $ - % insert Diet For So find B12 D E G H Capital budgetingcreria 12.00% 3 WACC 4 5 6 Project 2 $100 $387 $180 $100 4 WOO 5 1000 $850 Sind 540 $134 $134 5144 5134 $134 50 9 10 11 Project NPV Cat 12 NPVA Formulas NA NA 14 NPV, 15 16 Pre RR Cabutions 17 IRRA Sheet1 File Home Insert Formulas Data Review View Help Tell me what you want to do Comments Arial 10 - APA 28 ' Currency $. % Paste BIUD Insert Dette format X GR L low Format Painter Chboard - conditional forman Forgs Styles Cear Sort & Find Select C 312 D E G H ANA NA WNA 16 Project IRR Calculations 17 IRRA 18 19 IRR 20 21 Project MRR Calculations 22 MIRR 23 24 Alternatively, MRRC be called as: 25 26 Project 27 28 PV of Year 1 Outf 29 PV of Year 2 Outflow 30 PV of Year 3 Outflow 31 PV of Year 7 Outflow 32 Sheet1 4 5000 5 3000 187 $850 $100 Formas NIA ANA ANA ANA LOON Une Bookmar x Excel template Saved File Home Insert Formulas Data Review View Tell me what you want to do o comments Help == Arial - 10 AA 12 xon Dm copy Format Painter Y O Paste Currency $ - % BIUD. Font A conational forme For Table Styles Insert Delete Format Ce Sortid Fiter Select Ali 312 D E G H NA Formules 36 Sum of Outlow PVs 37 38 N 39 PV 40 PMT 41 FV 42 WYR - MIRRA 7 $0.00 0 NA NA sise $134 5134 44 MIRR 45 46 Alevwly MRR, can be calculated as 47 48 Project 49 50 51 52 $0 ch Home Insert formulas Formulas Data Review Review View Help Tell me what you want to do Comments Arial 10 - AA 1$ skuntatem 27 Pashe Currency $ - %%# X Cut Copy Format Painter Clipboard - BID. insert Delete Format ce Condsanal tomar Formas Tube Sordo Har Select Line de Hot Agent 2 D E G H 3 Surn of Outflow PV ANA Formulas ON 1 PV 2 PMT 3 FV VYR-MIRR 7 50.00 0 $0.00 ANIA 12.00 ANA 6 Project Acceptance 7 WACC - Accept 9 " WACC +1NPVA 2 NPV 3 Accept 24 Sheet1 18.00% $2.66 $636 NA File Home Insert Formulas Data Review View Help Tell me what you want to do comments -10 AA X Cut [Copy Format Painter Clipboard 31 Autosun 27 O $ - % Conditional Format Formatting Table Styles Insert Delse forma Clear Sort Find Und Tom Ant Celes B12 E F G H NPVA NPV, Discount Rates NPVA NPV $2.66 $63.68 $2.66 $63.68 500% 1000% 75 NPV Profiles: 76 Discount Rates 77 78 79 80 81 B2 83 84 85 86 87 88 $120 89 90 91 92 Sheet1 0% 5.00% 10.00% 12.00% 15.00% 18.10% 23.97% 12.00 15.00 18 10% 2397 ANA ANA ANA INA INA ANA ANA NA ANA ANIA UNA NA NA NPV Profiles Helpingowe Calculation Mode: Automatic Works File Home Insert Formulas Data Review View Help Tell me what you want to do Arial == - 10 A A 29 Currency Paste X Cut La Copy Format Painter Clipboard f BIUR -A $ - %%%%% Insert Delete Format Conditional Format Cell Formatting as Table Styles Tables Unde Font Alignment Number Cells B12 A B D E G NPV Profiles $1.20 $1.00 SO.BD 86 87 BB 89 90 91 92 93 94 95 96 97 98 99 100 101 $0.60 $0.40 $0.20 23.97% $0.00 OK 18.10 12.00% 10.00 15.00 5.OON 102 10 Sheet1 + M x Excel template . Saved File Home Insert Formulas Data Review View Help Tell me what you want to do Comme Ara 10 Album [Copy Format Painter Y O Paste BIURO Currency $ . % A Insert Delete Format Conditional for Tomos Tube Style Sort & Find F Select- Sot 312 D E F 103 104 Calculation of Crossover Rule 105 106 Project 107 108 Project 109 110 Project Delta $300 $100 $100 MOO Sood -$406 3134 $134 $134 $134 $134 $134 SNA NA KINA INA NA SNA WINA 112 113 Crossover Rate - IRR 115 Project MRR Calculations at WACC - 18 116 WACC 18.00 117 NA NA 118 MIRR 119 MIRR, Sheet1 Sheets room ON I J 6 $850 7 -$180 Formulas #N/A #N/A #N/A FV of Year 6 Inflow at Year 7 FV of Year 5 Inflow at Year 7 FV of Year 4 Inflow at Year 7 #N/A Sum of Inflow FVs 6 $134 7 $0 Formulas #N/A #N/A #N/A #N/A #N/A #N/A FV of Year 6 Inflow at Year 7 FV of Year 5 Inflow at Year 7 FV of Year 4 Inflow at Year 7 FV of Year 3 Inflow at Year 7 FV of Year 2 Inflow at Year 7 FV of Year 1 Inflow at Year 7 #N/A Sum of Inflow FVs Number Tables Font Alignment G H I J 18.10% 23.97% 4 $600 5 $600 6 $850 7 -$180 $134 $134 $134 $0 UNA #NA N/A #NA istics