Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Most of this question is already answered, I just need help on the questions I left blank. I have included screenshots of the questions as
Most of this question is already answered, I just need help on the questions I left blank. I have included screenshots of the questions as well as the excel work sheet that was provided with some of the work I've done. Thank you!
Excel Activity: Capital Budgeting Tools Question 1 7.14/10 Excel Activity: Capital Budgeting Tools Submit Start with the partial model in the file Ch12 P25 Build a Model.xlsx. Gardial Fisheries is considering two mutually exclusive investments. The projects' expected net cash flows are as follows: Year 0 Expected Net Cash Flows Project A Project B -$365 -$565 -330 180 -220 180 1 2 3 -110 180 4 180 660 660 5 180 6 180 958 -220 7 180 The data has been collected in the Microsoft Excel file below. Download the spreadsheet and perform the required analysis to answer the questions below. Do not round intermediate calculations. x Download spreadsheet Ch12 P25 Build a Model-8ee6c2.xlsx a. If each project's cost of capital is 11%, which project should be selected? Round your answers to the nearest cent. NPV (Project A): $ 311.38 g NPV (Project B): $ 283.20 Project A should be selected. If the cost of capital is 17%, what project is the proper choice? Round your answers to the nearest cent. NPV (Project A): $ 76.96 a. If each project's cost of capital is 11%, which project should be selected? Round your answers to the nearest cent. NPV (Project A): $ 311.38 NPV (Project B): $ 283.20 Project Ay should be selected. If the cost of capital is 17%, what project is the proper choice? Round your answers to the nearest cent. NPV (Project A): $ 76.96 NPV (Project B): $ 141.03 Project By should be selected. b. Construct NPV profiles for Projects A and B. Choose the correct graph. A. B. NPV Profiles NPV Profiles $1,200 $1,200 $1,000 $1,000 $800 $800 $600 $600 $400 5400 $200 $200 SO 0% -$200+ 5% 10% 15% 20% 30% SO 0% -$200 5 10% 15% 20% 25% 30% D. NPV Profiles NPV Profiles $1,200 $1,200 $1,000 $1,000 $800 $800+ $600 $600 5400 5400 $200 $200+ SO 0% -$200 5% 10% 15% 20% 25% 30% SO 0% -$200 5% 10% 15% 250 20% 30% -$400 Cost of Capital - Project A -Project B -$4001 Cost of Capital Project A -Project B The correct graph is graph Dva c. What is each project's IRR? (Hint: Using the Excel IRR function, set the guess parameter to be 10%.) Round your answers to two decimal places. IRR (Project A): 19.52 % IRR (Project B): 25.28 % d. What is the crossover rate, and what is its significance? (Hint: Using the Excel IRR function, set the guess parameter to be 10%.) Round your answer for the crossover rate to two decimal places and for the NPV to the nearest cent. The crossover rate is 12.58 %. The crossover rate represents the cost of capital at which the two projects have the NPV of $ 241.59 IRR (Project B): 25.28 % d. What is the crossover rate, and what is its significance? (Hint: Using the Excel IRR function, set the guess parameter to be 10%.) Round your answer for the crossover rate to two decimal places and for the NPV to the nearest cent. The crossover rate is 12.58 %. The crossover rate represents the cost of capital at which the two projects have the NPV of $ 241.59 e. What is each project's MIRR at a cost of capital of 11%? At r = 17%? Round your answers to two decimal places. Project A 15.28 % MIRR at r = 11% Project B 17.63% 20.78 %% MIRR at r = 17% 18.31 % f. What is the regular payback period for these two projects? Round your answers to two decimal places. Regular payback period (Project A): years Regular payback period (Project B): years g. At a cost of capital of 11%, what is the discounted payback period for these two projects? Round your answers to two decimal places. Discounted payback period (Project A): years Discounted payback period (Project B): years h. What is the profitability index for each project if the cost of capital is 11%? Round your answers to three decimal places. Profitability index (Project A): Profitability index (Project B): Check My Work Reset Problem $660 1 Capital Budgeting Tools 2 3 Expected Net Cash Flows 4 Time Project A Project B 5 0 -$365 $565 1 -$330 $180 7 2 -$220 $180 3 3 $110 $180 2 4 $180 0 5 $660 $180 1 6 $958 $180 2 7 $220 $180 3 4 a. Finding each project's NPV at two different costs of capital 5 WACC 11% 6 WACC2 17% 7 Project A Project B 8 NPV at WACC1 $311.38 $283.20 9 NPV at WACC2 $76.96 $141.03 CO 1 b. Constructing NPV profiles for Projects A and B -2 NPV Cost of capital -3 Project A Project B -4 B15 $311.38 $283.20 -5 0% $1.033.00 $695.00 -6 2% $863.03 $599.96 7 4% $713.08 $515.37 -8 6% $580.53 $439.83 -9 8% $463.15 $372.15 0 10% $359.01 $311.32 -1 12% $266.46 $256.48 Sheet1 Formulas Project A =NPV(11%,B6:B12)+B5 =NPV(17%,B6:B12)+B5 Project B =NPV(11%, C6:C12)+C5 =NPV(17%,C6:C12)+C5 Project A $311.38 =$B$5+NPV(A25 $B$6:$B$12) =$B$5+NPV(A26 $B$6:$B$12) =$B$5+NPV(A27,$B$6:$B$12) =$B$5+NPV(A28,$B$6:$B$12) =$B$5+NPV(A29,$B$6:$B$12) =$B$5+NPV(A30,$B$6:$B$12) =$B$5+NPV(A31,$B$6:$B$12) NPV Project B $283.20 =$C$5+NPV(A25 $C$6:$C$12) =$C$5+NPV(A26,$C$6:$C$12) =$C$5+NPV(A27,$C$6:$C$12) =$C$5+NPV(A28,$C$6:$C$12) =$C$5+NPV(A29,$C$6:$C$12) =$C$5+NPV(A30, $C$6:$C$12) =$C$5+NPV(A31, $C$6:$C$12) b. Constructing NPV profiles for Projects A and B NPV Cost of capital Project A Project B B15 $311.38 $283.20 0% $1.033.00 $695.00 2% $863.03 $599.96 4% $713.08 $515.37 6% $580.53 $439.83 8% $463.15 $372.15 10% $359.01 $311.32 12% $266.46 $256.48 14% $184.08 $206.89 16% $110.66 $161.94 18% $45.11 $121.07 20% -$13.47 $83.83 22% -$65.91 $49.79 24% -$112.88 $18.62 26% -$155.02 -$10.00 28% -$192.84 -$36.34 30% -$226.84 -$60.62 Project A $311.38 =$B$5+NPV(A25 $B$6:$B$12) =$B$5+NPV(A26,$B$6:$B$12) =$B$5+NPV(A27,$B$6:$B$12) =$B$5+NPV(A28,$B$6:$B$12) =$B$5+NPV(A29,$B$6:$B$12) =$B$5+NPV(A30,$B$6:$B$12) =$B$5+NPV(A31,$B$6:$B$12) =$B$5+NPV(A32,$B$6:$B$12) =$B$5+NPV(A33,$B$6:$B$12) =$B$5+NPV(A34, $B$6:$B$12) =$B$5+NPV(A35,$B$6:$B$12) =$B$5+NPV(A36,$B$6:$B$12) =$B$5+NPV(A37,$B$6:$B$12) =$B$5+NPV(A38, $B$6:$B$12) =$B$5+NPV(A39,$B$6:$B$12) =$B$5+NPV(A40,$B$6:$B$12) NPV Project B $283.20 =$C$5+NPV(A25, $C$6:$C$12) =$C$5+NPV(A26,$C$6:$C$12) =$C$5+NPV(A27 $C$6:$C$12) =$C$5+NPV(A28,$C$6:$C$12) =$C$5+NPV(A29, $C$6:$C$12) =$C$5+NPV(A30, $C$6:$C$12) =$C$5+NPV(A31,$C$6:$C$12) =$C$5+NPV(A32, $C$6:$C$12) =$C$5+NPV(A33,$C$6:$C$12) =$C$5+NPV(A34, $C$6:$C$12) =$C$5+NPV(A35,$C$6:$C$12) =$C$5+NPV(A36,$C$6:$C$12) =$C$5+NPV(A37,$C$6:$C$12) =$C$5+NPV(A38,$C$6:$C$12) =$C$5+NPV(A39,$C$6:$C$12) =$C$5+NPV(A40,$C$6:$C$12) Chart Title $1,200.00 $1,000.00 $800.00 $600.00 $400.00 30% -$226.84 -$60.62 =$B$5+NPV(A40,$B$6:$B$12) =$C$5+NPV(A40, $C$6:$C$12) Chart Title $1,200.00 $1,000.00 $800.00 $600.00 $400.00 $200.00 $0.00 0% 2% 4% 6% 8% 10% 12% 14% 16% 18% 20% 22% 240 26% 28% 30% -$200.00 -$400.00 Series1 Series2 c. Finding each project's IRR (Using the Excel IRR function, set the guess parameter to be 10%.) Project A Project B Project A IRR 19.52% 25.28% =IRR(B5:B12) Project B =IRR(C5:C12) d. Finding the crossover rate (Using the Excel IRR function, set the guess parameter to be 10%.) Project ACF = Year CFA- CFB 0 $200 =B5-C5 IRR 19.52% 25.28% =IRR(B5:B12) =IRR(C5:C12) d. Finding the crossover rate (Using the Excel IRR function, set the guess parameter to be 10%.) Project ACF = Year CFA - CFB 0 $200 =B5-C5 1 -$510 =B6-C6 2 -$400 =B7-C7 3 -$290 =B8-C8 4 $480 =B9-C9 5 $480 =B10-C10 6 $778 =B11-C11 7 -$400 =B12-C12 Crossover rate NPV of Project A at the crossover rate NPV of Project B at the crossover rate 12.58% $241.59 $241.59 =IRR(B70:B77) =NPV(B79,B6:B12)+B5 =NPV(B79, C6:C12)+C5 e. Finding each project's MIRR at two different costs of capital WACC1 11% WACCZ 17% Project A Project B MIRR at WACC1 15.28% 17.63% MIRR at WACC2 18.31% 20.78% Project A =MIRR(B5:B12, 11%, B84) =MIRR(B5:B12,385,385) Project B =MIRR(C5:C12, B84,B84) =MIRR(C5:C12,885,885) f. Finding the regular payback period for these two projects Project A Cumulative cash flow Intermediate calculation for payback Year 0 Intermediate calculation for payback Cumulative cash flow #N/A Cash flow -$365.00 f. Finding the regular payback period for these two projects Project A Cumulative cash flow Intermediate calculation for payback Intermediate calculation for payback Year 0 1 2 3 4 5 6 Cash flow -$365.00 -$330.00 -$220.00 $110.00 $660.00 $660.00 $958.00 -$220.00 Cumulative cash flow #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 7 Regular payback period (years) #N/A Project B Cumulative cash flow Intermediate calculation for payback Intermediate calculation for payback Year 0 1 2 Cumulative cash flow #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Cash flow -$565.00 $180.00 $180.00 $180.00 $180.00 $180.00 $180.00 $180.00 3 4 #N/A #N/A #N/A #N/A #N/A #N/A #N/A 5 6 7 Regular payback period (years) #N/A a Finding the discounted navback neriod for these two proiects 11% 8 WACC 9 Project A Discounted cash flow Cumulative discounted cash flow Intermediate calculation for payback Discounted cash flow Interme 0 Year 1 0 2 1 3 2 4 3 5 4 6 5 7 6 8 7 9 o Discounted payback period (years) 1 2 Project B Cash flow -$365.00 -$330.00 -$220.00 $110.00 $660.00 $660.00 $958.00 -$220.00 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Cumulative discounted cash flow #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Discounted cash flow Cumulative discounted cash flow Intermediate calculation for payback Discounted cash flow Interme 3 Year 4 0 5 1 6 2 7 3 8 4 9 5 0 6 1 7 2 3 Discounted payback period (years) 4 Cash flow -$565.00 $180.00 $180.00 $180.00 $180.00 $180.00 $180.00 $180.00 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Cumulative discounted cash flow #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A h. Finding the profitability index for each project WACC 11% Project A PV of future cash flows Profitability index Project B Project A Project B #N/A #N/A #N/A #N/AStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started