Question

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

Expected Net Cash Flows                                                                                                             

                Time      Project A              Project B                                                                                             

                0              ($400)                 ($700)                                                                                   

                1              ($280)                   $201                                                                                     

                2              ($200)                   $202                                                                                     

                3              ($100)                   $203                                                                                     

                4              $600                      $204                                                                                     

                5              $600                      $205                                                                                     

                6              $925                      $206                                                                                     

                7              ($170)                   $207

In your report, identify which project would be selected (assuming they are mutually exclusive) for each investment criterion. 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 (10 points)

B) Construct NPV profiles (a data table of project NPVs relative to differing costs of capital) using the Excel NPV function for Projects A and B and graph them both in one plot (label the x-axis, y-axis, and title) (10 points)

Part 2: Internal Rate of Return

C) Use the Excel IRR function to calculate each projects IRR (10 points)

D) Use the Excel MIRR function to calculate each projects MIRR for each cost of capital (10 points)

Part 3: Payback

E) Calculate the cumulative cash flow and the regular payback for each project (10 points)

F) Calculate the profitability index for each project (10 points)

Cost of Capital is 12%

Answer 1

Part 1:-

Part 2

C) IRR

D) MIRR
Note: Since there is no mention of the reinvestment rate for MIRR function, I have assumed it to be 0%

E) Cumulative Cash Flow and Payback Period

F) Profitability Index