Flower Engineering is considering two mutually exclusive investments. The projec

Question

Flower Engineering is considering two mutually exclusive investments. The projects' expected net cash flows are as follows:

Note that cash outflows (costs) are given in parenthesis. Employ the excel file to answer the following questions:

Part 1: Net Present Value
A) Use the Excel NPV function to calculate the NPV for each project at each cost of capital
B) Construct NPV profiles (a data table of project NPVs relative to differing costs of capital) for Projects A and B and graph them in one plot (label the x-axis, y-axis, and title)

Part 2: Internal Rate of Return
C) Use the Excel IRR function to calculate each projects IRR
D) Use the Excel MIRR function to calculate each projects MIRR for each cost of capital

Part 3: Payback
E) Calculate the cumulative cash flow and the regular payback using the formula for each project
F) Calculate the discounted cash flow and the discounted payback using the formula for each project
G) Calculate the profitability index for each project if the cost of capital is 12%

Expected Net Cash Flows Time Project A Project B 0 ($375) ($575) 1 ($300) $200 2 ($200) $200 3 ($100) $200 4 $600 $200 5 $600 $200 6 $975 $200 7 ($200) $0

Explanation / Answer

Project A PV @ 10% ($) PV factor @ 10% Year Cash flow PV factor @ 12% PV @ 12% ($) PV factor @ 14% PV @ 14% ($) PV factor @ 19% PV @ 19% ($) PV factor @ 19.20% PV @ 19.20% ($) PV factor @ 19.25% PV @ 19.25% ($) -375 1 0 -375 1 -375.0000 1 -375 1 -375 1 -375 1 -375 -272.73 0.90909 1 -300 0.89286 -267.86 0.87719 -263.16 0.84034 -252.10 0.83893 -251.68 0.83857 -251.57 -165.29 0.82645 2 -200 0.79719 -159.44 0.76947 -153.89 0.70616 -141.23 0.70380 -140.76 0.70321 -140.64 -75.13 0.75131 3 -100 0.71178 -71.18 0.67497 -67.50 0.59342 -59.34 0.59043 -59.04 0.58969 -58.97 409.81 0.68301 4 600 0.63552 381.31 0.59208 355.25 0.49867 299.20 0.49533 297.20 0.49450 296.70 372.55 0.62092 5 600 0.56743 340.46 0.51937 311.62 0.41905 251.43 0.41555 249.33 0.41468 248.81 550.36 0.56447 6 975 0.50663 493.97 0.45559 444.20 0.35214 343.34 0.34861 339.90 0.34774 339.04 -102.63 0.51316 7 -200 0.45235 -90.47 0.39964 -79.93 0.29592 -59.18 0.29246 -58.49 0.29160 -58.32 341.94 NPV 251.79 171.59 7.11 1.45 0.04 IRR = 19.25% Project B Year Cash flow PV @ 10% ($) PV factor @ 10% PV factor @ 12% PV @ 12% ($) PV factor @ 14% PV @ 14% ($) PV factor @ 24% PV @ 24% ($) PV factor @ 25% PV @ 25% ($) PV factor @ 26.12 PV @ 26.12% ($) -575 1 0 -575 1 -575 1 -575 1 -575 1 -575 1 -575 181.82 0.91 1 200 0.89286 178.57 0.87719 175.44 0.80645 161.29 0.80000 160.00 0.79271 158.54 165.29 0.83 2 200 0.79719 159.44 0.76947 153.89 0.65036 130.07 0.64000 128.00 0.62838 125.68 150.26 0.75 3 200 0.71178 142.36 0.67497 134.99 0.52449 104.90 0.51200 102.40 0.49812 99.62 136.60 0.68 4 200 0.63552 127.10 0.59208 118.42 0.42297 84.59 0.40960 81.92 0.39487 78.97 124.18 0.62 5 200 0.56743 113.49 0.51937 103.87 0.34111 68.22 0.32768 65.54 0.31301 62.60 112.89 0.56 6 200 0.50663 101.33 0.45559 91.12 0.27509 55.02 0.26214 52.43 0.24813 49.63 0.00 0.51 7 0 0.45235 0 0.39964 0.00 0.22184 0.00 0.20972 0 0.19669 0.00 296.0521399 NPV 247.28 202.73 29.09 15.2848 0.05 IRR = 26.12% 2)   IRR Project A 19.25% Project B 26.12% 3) Regular payback PROJECT A PROJECT B Year Cash flow Cum.Cash flow Year Cash flow Cum.Cash flow 0 -375 -375 1 -300 -675 0 -575 -575 2 -200 -875 1 200 -375 3 -100 -975 2 200 -175 4 600 -375 3 200 25 5 600 225 4 200 225 6 975 1200 5 200 425 7 -200 1000 6 200 625 7 0 625 Cost/investment before +ve flows started 975 Payback(B) 575/200 ie. 2 yrs. &10.5 mths Positve inflow generated in the 5th year 600 Reqd. inflow to zero 375 Payback period (A) Initial investment/cum cash flows 4 years &(12/600)*375 mths. ie. 4 yrs.7&1/2 mths. Discounted Payback @IRR (A) Discounted Payback @IRR (B) Year PV @ 19.25% ($) cumulative Year PV @ 26.12% ($) cumulative 0 -375 -375 0 -575 -575 1 -251.57 -626.57 1 158.54 -416.46 2 -140.64 -767.21 2 125.68 -290.78 3 -58.97 -826.18 3 99.62 -191.16 4 296.70 -529.48 4 78.97 -112.18 5 248.81 -280.68 5 62.60 -49.58 6 339.04 58.36 6 49.63 0.05 7 -58.32 0.04 7 0.00 0.04 0.05 IRR = 19.25% IRR = 26.12% Positive cash flow starts in 6th year Positive cash flow starts @ end of 6th year Completed years 5 Completed years 6 (12 / 339.04 * 280.68 = 10 months So, payback period (B) = 6 years So, total payback period (A) = 5 years 10 months 4 Profitability Index @ 12% COC (A) Profitability Index @ 12% COC(B) = Present Value of Future Cash Flows = Present Value of Future Cash Flows Initial Investment Required Initial Investment Required = 1 + Net Present ValueInitial Investment Required = 1 + Net Present ValueInitial Investment Required 1 + (251.79 / 375) 1 + (247.28 / 575) 1 + 0.67 = 1.67 1 + 0.43 = 1.43

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.