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	-$300	-$387	-$193	-$100	$600	$600	$850	-$180
Project B	-$400	$131	$131	$131	$131	$131	$131	$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.

https:/ x Mail - H: X i Halley, S X D21 Manage X MindTap X Excel On x Homewc X C Each of t X C A legal c x y! AT&T In X + V X C about:blank * 0 8 : M ) Sign In - Zoom G Gmail y! Yahoo . Advanced Destination. Google Search StairArtist wix VFI - Victorious Fin... The Philadelphia Gr... A Advance-Destination.. Other bookmarks Excel template - Saved Search (Alt + Q) File Home Insert Draw Page Layout Formulas Data Review View Help Editing Comments Arial 10 v B Av ... Ev ab Currency .00 00 -+0 EV APV OV . . . B12 * X V f A B C D E F G H J K L M N Capital budgeting criteria WACC 12.00% 10 0O JO UI A W N - 1 2 3 4 5 6 7 Project A -$300 -$387 -$193 -$100 $600 $600 $850 -$180 Project B -$400 $131 $131 $131 $131 $131 $131 $0 10 11 Project NPV Calculations: Formulas 12 NPVA #N/A 13 14 NPVB #N/A 15 16 Project IRR Calculations: 17 IRRA #N/A 18 19 IRRE #N/A 20 21 Project MIRR Calculations: > Sheet1 + Calculation Mode: Automatic Workbook Statistics Give Feedback to Microsoft 100% + W 5:08 PM Search 12/3/2022https:/ x Mail - H: X i Halley, S X D21 Manage X MindTap X Excel On x Homewc X C Each of t X C A legal c x y! AT&T In X + V X C about:blank * 0 8 : M ) Sign In - Zoom G Gmail y! Yahoo . Advanced Destination. Google Search StairArtist wix VFI - Victorious Fin... The Philadelphia Gr... A Advance-Destination.. Other bookmarks Excel template - Saved Search (Alt + Q) File Home Insert Draw Page Layout Formulas Data Review View Help Editing Comments Arial 10 v B vavAv ... Ev ab Currency .00 00 EV APV OV . . . B12 * X V fx A B C D E F G H J K L M N 19 IRRE #N/A 20 21 Project MIRR Calculations: 22 MIRRA #N/A 23 24 Alternatively, MIRRA can be calculated as: 25 0 1 2 3 4 5 6 7 26 Project A -$300 -$387 -$193 -$100 $600 $600 $850 -$180 27 Formulas 28 PV of Year 1 Outflow #N/A 29 PV of Year 2 Outflow #N/A 30 PV of Year 3 Outflow #N/A 31 PV of Year 7 Outflow #N/A Formulas 32 #N/A FV of Year 6 Inflow at Year 7 33 #N/A FV of Year 5 Inflow at Year 7 34 #N/A FV of Year 4 Inflow at Year 7 35 36 Sum of Outflow PVs #N/A #N/A Sum of Inflow FVs 37 38 IN 7 Formulas 39 PV $0.00 Sheet1 + Calculation Mode: Automatic Workbook Statistics v Give Feedback to Microsoft 100% + Search OH ET W 5:08 PM 12/3/2022https:/ x Mail - H: X i Halley, S X D21 Manage X MindTap X Excel On x Homewc X C Each of t X C A legal c x y! AT&T In X + V X C about:blank * 0 8 : M ) Sign In - Zoom G Gmail y! Yahoo . Advanced Destination. Google Search StairArtist wix VFI - Victorious Fin... The Philadelphia Gr... A Advance-Destination.. Other bookmarks Excel template - Saved Search (Alt + Q) File Home Insert Draw Page Layout Formulas Data Review View Help Editing Comments Arial 10 v B vavAv ... Ev ab Currency 00 :00 -+0 . . . B12 " X V fx A B C D E F G H K M N 30 PV of Year 3 Outflow #NIA 31 PV of Year 7 Outflow #N/A Formulas 32 #N/A FV of Year 6 Inflow at Year 7 33 #N/A FV of Year 5 Inflow at Year 7 34 #N/A FV of Year 4 Inflow at Year 7 35 36 Sum of Outflow PVs #N/A #N/A Sum of Inflow FVs 37 38 IN 7 Formulas 39 PV $0.00 40 PMT 0 41 FV $0.00 42 I/YR = MIRRA #N/A 43 44 MIRR: #N/A 45 46 Alternatively, MIRR: can be calculated as: 47 0 1 2 3 4 5 6 7 48 Project B -$400 $131 $131 $131 $131 $131 $131 $0 49 Formulas 50 #N/A FV of Year 6 Inflow at Year 7 > Sheet1 + Calculation Mode: Automatic Workbook Statistics Give Feedback to Microsoft - 100% + 5:09 PM Search 12/3/2022https:/ x Mail - H: X i Halley, S X D21 Manage X MindTap X Excel On x Homewc X C Each of t X C A legal c x y! AT&T In X + V X C about:blank * 0 8 : M ) Sign In - Zoom G Gmail y! Yahoo . Advanced Destination. Google Search StairArtist wix VFI - Victorious Fin... The Philadelphia Gr... A Advance-Destination.. Other bookmarks Excel template - Saved Search (Alt + Q) File Home Insert Draw Page Layout Formulas Data Review View Help Editing v Comments Arial 10 v B Av ... Ev ab Currency 00 .00 -+0 EV APV OV . . . B12 4 X VA A B C D E F G H J K M N 46 Alternatively, MIRR: can be calculated as: 47 0 1 2 3 4 5 6 7 48 Project B -$400 $131 $131 $131 $131 $131 $131 $0 49 Formulas 50 #N/A FV of Year 6 Inflow at Year 7 51 #N/A FV of Year 5 Inflow at Year 7 52 #N/A FV of Year 4 Inflow at Year 7 53 #N/A FV of Year 3 Inflow at Year 7 54 #N/A FV of Year 2 Inflow at Year 7 55 #N/A FV of Year 1 Inflow at Year 7 56 57 58 Sum of Outflow PVs #NIA #N/A Sum of Inflow FVs 59 60 N 7 Formulas 61 PV $0.00 62 PMT 63 FV $0.00 64 I/YR = MIRRE #N/A 65 66 Project Acceptance: > Sheet1 + Calculation Mode: Automatic Workbook Statistics Y Give Feedback to Microsoft 100% + 5:09 PM Search W 12/3/2022https:/ x Mail - H: X i Halley, S X D21 Manage X MindTap X Excel On x Homewc X C Each of t X C A legal c x y! AT&T In X + V X C about:blank * 0 8 : M ) Sign In - Zoom G Gmail y! Yahoo . Advanced Destination. Google Search StairArtist wix VFI - Victorious Fin... The Philadelphia Gr... A Advance-Destination.. Other bookmarks Excel template - Saved Search (Alt + Q) File Home Insert Draw Page Layout Formulas Data Review View Help Editing Comments Arial 10 v B Av ... Ev ab Currency .00 .00 EV APV OV . . . B12 " X V fx A B C D E F G H J K L M N 66 Project Acceptance: 67 WACC 12.00% 68 Accept #N/A 69 70 WACC 18.00% 71 NPVA $2.66 72 NPVB $58.19 73 Accept 7#N/A 74 75 NPV Profiles: 76 Discount Rates NPVA NPVB Discoun NPVA NPVB 77 $2.66 558.19 $2.66 $58.19 78 0% 0% #N/A #N/A 79 5.00% 5.00% #N/A #N/A 80 10.00% 10.00% #N/A #N/A 81 12.00% 12.00% #N/A / #N/A 82 15.00% 15.00% #N/A #N/A 83 18.10% 18.10% #N/A #N/A 84 23.54% 23.54% #N/A #N/A 85 >= D Sheet1 + Calculation Mode: Automatic Workbook Statistics Give Feedback to Microsoft 100% + Search W 6:10 PM 12/3/2022V https:/ x Mail - H: X i Halley, S X D21 Manage X MindTap X Excel On x Homewc X C Each of t X C A legal c x y! AT&T In X + X C about:blank * 0 8 : M ) Sign In - Zoom G Gmail y! Yahoo . Advanced Destination. Google Search StairArtist wix VFI - Victorious Fin... The Philadelphia Gr... A Advance-Destination.. Other bookmarks Excel template - Saved Search (Alt + Q) File Home Insert Draw Page Layout Formulas Data Review View Help Editing v Comments Arial 10 v B Av ... Ev ab Currency .00 .00 . . . B12 * X Vfx A B C D E G H J K L M N 13.00 70 15.00 70 83 18.10% 18.10% #N/A #N/A 84 23.54% 23.54% #N/A #N/A 85 86 87 NPV Profiles 88 $1.20 89 90 $1.00 91 92 $0.80 93 94 $0.60 95 96 $0.40 97 98 So.20 99 100 $0.00 101 0% 5.00% 10.00% 12.00% 15.00% 18.10% 23.54% 102 103 > = D Sheet1 + Calculation Mode: Automatic Workbook Statistics Y Give Feedback to Microsoft 100% + 6:10 PM Search W 12/3/2022https:/ x Mail - H: X i Halley, S X D21 Manage X MindTap X Excel On x Homewc X C Each of t X C A legal c x y! AT&T In X + V X C about:blank * 0 8 : M ) Sign In - Zoom G Gmail y! Yahoo . Advanced Destination. Google Search StairArtist wix VFI - Victorious Fin... The Philadelphia Gr... A Advance-Destination.. Other bookmarks Excel template - Saved Search (Alt + Q) File Home Insert Draw Page Layout Formulas Data Review View Help Editing v Comments Arial 10 B Av ... Ev ab Currency .00 00 EV APV OV . . . B12 " X V f A B C D E F G H J K L M N 104 Calculation of Crossover Rate: 105 0 1 2 3 4 5 6 7 106 Project A -$300 -$387 -$193 -$100 $600 $600 $850 -$180 107 108 Project B -$400 $131 $131 $131 $131 $131 $131 $0 109 110 Project Delta 111 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 112 113 Crossover Rate = IRRA #N/A 114 115 Project MIRR Calculations at WACC = 18% 116 WACC 18.00% 117 118 MIRRA #N/A 119 MIRRE #N/A 120 121 122 123 124 >= 8 Sheet1 + Calculation Mode: Automatic Workbook Statistics Give Feedback to Microsoft 100% + Search W 6:11 PM 12/3/2022