Question

ON EXCEL

The director of capital budgeting for Giant Inc. has identified two mutually exclusive projects, L and S, with the following expected net cash flows and a required rate of return of 10 percent: Expected Net Cash Flows Year Project S Project L ($210,000) 0 ($161,000) 90,000 10,000 - 0 20,000 60,000 80,000 20,000 60,000 + 90,000 90,000 10,000 Build an automatic spread sheet that calculates: 1. the NPV of both projects with 2 different methods (NPV of excel and formula). 2. The IRR of both projects using the solver function 3. The IRR using the IRR function of Excel 4. Do the NPV profiles cross? 5. Create a data table that shows how NPV changes when the required rate of return changes and plot the NPV profiles. 6. Find the crossover point if they cross; a. using the graph b. build a section in the spread sheet that calculates the crossover point using goal seek or solver 7. Build a section in the spreadsheet that calculates the MIRR for every project if the reinvestment rate is 6%, 10%, 15% and 20% 8. Explain how the director of this company can use your results to make an informed decision

Answer 1

Connections ALA ? Solver WorkingC (Autosaved) - Microsoft Excel File Home Insert Page Layout Formulas Data Review View Clear Show Detail A Properties Reapply Hide Detail From From From From Other Existing Refresh 21 Sort Filter Text to Remove Data Consolidate What If Group Ungroup Subtotal Access Web Text Sources Connections All Edit Links Advanced Columns Duplicates Valio Get External Data Connections Sort & Filter Solver Parameters C1027 fx =NPV(C1026,B1017:B1021)+B1016 А B Set Objective: $C$10271 1015 Year Project L Cash flow Present value of cash flow 1016 -210000 -2,10,000.00 To: O Max Min value of: 1017 90000 81,818.18 By Changing Variable Cells: 1018 60000 49,586.78 1019 80000 $C$1026 60,105.18 1020 20000 13,660.27 Subject to the Constraints: 1021 10000 6,209.21 1022 NPV 1,379.63 1023 NPV Using excel function -6,484.17 1024 1025 IRR Using solver function 1026 Discount rate 12.00% 1027 NPV -6,484.17 UwNO Add Change Delete Reset All 1028 Load/Save 1029 1030 1031 IRR Using excel function 10.34% ✓ Make Unconstrained Variables Non-Negative Select a Solving Method: 1032 GRG Nonlinear Options 1033 1034 1035 Year 1036 1037 Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Project S Cash flow 1038 Present value of cash flow -161000 -1,61,000.00 10000 9,090.91 20000 16,528.93 60000 45,078.89 Sheets Sheet Sheet11 Sheet10 Help Solve Close 1039 K M Point Sheet18 Calculate Ratio Sheet12 Sheet13 Sheet6 Sheet7 Sheet19 EM 100%-

A B Project L Cash flow 3 1015 Year 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 Present value of cash flow -210000 -2,10,000.00 90000 81,818.18 60000 49,586.78 80000 60,105.18 20000 13,660.27 10000 6,209.21 1,379.63 0.00 5 NPV NPV Using excel function IRR Using solver function Discount rate NPV 10.34% 0.00! IRR Using excel function 10.34% 1031 1032

Project L Cash flow 1014 1015 Year 1016 1017 1018 1019 1020 1021 uw NO Present value of cash flow -210000 -2,10,000.00 90000 81,818.18 60000 49,586.78 80000 60,105.18 20000 13,660.27 10000 6,209.21 1,379.63 1,379.63 1022 NPV NPV Using excel function 1023 1024 IRR Using solver function Discount rate NPV 10.34% 0.00 1025 1026 1027 1028 1029 1030 IRR Using excel function 10.34% 1031

A B Project L Cash flow UwNO 1014 1015 Year 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 Present value of cash flow -210000 =B1016 90000 =B1017/(1+10%)^A1017 60000 =B1018/(1+10%)^A1018 80000 =B1019/(1+10%)^A1019 20000 =B1020/(1+10%)^A1020 10000 =B1021/(1+10%)^A1021 =SUM(C1016:C1021) =NPV(10%,B1017:B1021)+B1016 NPV NPV Using excel function IRR Using solver function Discount rate NPV 10.34% =NPV(C1026,B1017:B1021) +B1016 IRR Using excel function =IRR(B1016:B1021)

1035 Year Project S Cash flow 1036 w NPO 1037 1038 1039 1040 1041 1042 1043 1044 Present value of cash flow -161000 -1,61,000.00 10000 9,090.91 20000 16,528.93 60000 45,078.89 90000 61,471.21 90000 55,882.92 27,052.85 27,052.85 u NPV NPV Using excel function 1045 1046 1047 1048 IRR Using solver function Discount rate NPV 14.70% -0.01 1049 IRR Using excel function 14.70% 1050 1051

C1059 Project S Cash flow O N w Present value of cash flow -161000 =B1036 10000 =B1037/(1+10%)^A1037 20000 =B1038/(1+10%)^A1038 60000 =B1039/(1+10%)^A1039 90000 =B1040/(1+10%)^A1040 90000 =B1041/(1+10%)^A1041 =SUM(C1036:C1041) =NPV(10%,B1037:B1041)+B1036 u NPV NPV Using excel function 1035 Year 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 IRR Using solver function Discount rate NPV 14.70% =NPV(C1046,B1037:B1041)+B1036 IRR Using excel function =IRR(B1036:B1041)