only need written report

Objective: Students demonstrate ability to 1. Utilize several capital budgeting concepts. 2. Complete required calculations. 3. Present written and oral recommendation to senior management based on resulting data. Scenario: HW Enterprises is considering purchasing a new warehouse or expanding overseas. Cost will be $20,000 million for either option. Discount rate for both projects is 9%. Cash inflows for each options are below. Shown in millions: Year 1 2 3 4 Option A: New Warehouse $ 5,750 7,250 8,500 10,000 Option B: Expand Overseas $ 2,000 6,000 12,000 12,000 Directions: Utilizing cell references and formulas, use the data in the Excel file provided to prepare A. Calculations - use the tabs provided for details of calculations - move final values to summary tab using cell references. 1. Payback period calculation. Textbook page 384 has example. You need to use Excel to make your calculations. 2. Net Present Value - work the process step-by-step then utilize the NPV formula under the Formulas, Financials, tab. *See Table 12.4 in text page 386 for example. Note: you will be calculating the NPV using the two methods shown on Table 12.4. Assume 9% as discount rate. 3. Internal Rate of Return and Modified Internal Rate of Return-utilize the IRR and MIRR formulas under the Formulas, Financial, tab in Excel. *See Table 12.5 in text page 388 for example. Assume 2% as the reinvestment rate. B. Prepare written report of your team's recommendation to senior management of HW Enterprise. Be ready to discuss recommendation in class. 1. State need for project. 2. Recommendation of option to solve need. 3. Support your recommendation with facts based on your Excel calculations. Refer to computations in Excel file provided as attachment. 5. Close statement by re-stating recommendation. C. Submit Word document and Excel file to Blackboard. New Warehouse: Year Expand Product Line: Year Rate 2 4 Rate 0 ($20,000,000) 1 5,750,000 2 7,250,000 3 8,500,000 4 10,000,000 0 ($20,000,000) 3 $12,000,000 Cash Flows 9% Cash Flows $2,000,000 $6,000,000 $12,000,000 9% 0 3 0 1 2 4 -20000000 Payback Cash Flows Cum. Cash Flows Payback (in years) 1 5750000 -14250000 2 7250000 -7000000 -20000000 8500000 1500000 10000000 11500000 Payback Cash Flows Cum. Cash Flows Payback (in years) 2000000 -18000000 6000000 -12000000 3 12000000 0 12000000 12000000 2.82 3.00 4 Discounted Payback Year PV of Cash Flows Cum. Cash Flows Payback (in years) 0 -20000000 1 $5,275,229.36 ($14,724,770.64) 2 $6,102,179.95 ($8,622,590.69) 3 $6,563,559.58 ($2,059,031.11) Discounted Payback Year PV of Cash Flows Cum. Cash Flows Payback (in years) 0 -20000000 $7,084,252.11 $5,025, 221.00 1 $1,834,862.39 ($18,165,137.61) 2 $5,050,079.96 ($13,115,057.66) 3 $9,266,201.76 ($3,848,855.89) 4 $8,501,102.53 $4,652,246.64 3.29 3.45 3 2 4 Net Present Value Year Cash Flows PV of Cash Flows Sum of Inflows NPV Using Excel Wizard 1 5750000 $5,275,229.36 $25,025,221.00 2 7250000 $6,102,179.95 8500000 $6,563,559.58 4 10000000 $7,084,252.11 Net Present Value Year Cash Flows PV of Cash Flows Sum of Inflows NPV Using Excel Wizard 0 -20000000 -20000000 -20000000 $5,025, 221.00 $5,025,221.00 6000000 $5,050,079.96 2000000 $1,834,862.39 $24,652,246.64 3 12000000 $9,266,201.76 12000000 $8,501,102.53 0 -20000000 -20000000 -20000000 $4,652,246.64 $4,652,246.64 Internal Rate of Return (IRR) Using Excel Wizard Internal Rate of Return (IRR) Using Excel Wizard 18.96% 16.98% Reinvest 2% Reinvest 0.02 1 2 4 1 3 4 12000000 Modified Internal Rate of Return (MIRR) Year 0 Cash Flows -20000000 FV of Cash Inflows Sum of FV inflows -20000000 MIRR using IRR 12.74% MIRR using MIRR 12.74% 5750000 $6,101,946.00 0 7250000 $7,542,900.00 0 3 8500000 $8,670,000.00 0 10000000 10000000 $32,314,846.00 Modified Internal Rate of Return (MIRR) Year 0 Cash Flows -20000000 FV of Cash Inflows Sum of FV inflows -20000000 MIRR using IRR 13.00% MIRR using MIRR 13.00% 2000000 $2,122,416.00 0 2 6000000 $6,242,400.00 0 12000000 $12,240,000.00 0 12000000 $32,604,816.00 Profitability Index PV inflows/PV outflows Profitability Index PV inflows/PV outflows 1.25 1.23 HW Enterprises' senior management is trying to determine if they should purchase a new warehouse or expand overseas. Both require a $20,000 million investment. Calculate payback period, net present value, IRR, and MIRR. Then develop a recommendation, with explanation, to senior management. The recommendation should bein email-format. Submit Excel file and Word document. Purchase warehouse Expand overseas Initial Investment - Year O $20,000,000 $20,000,000 Cash Flow Year 1 5,750,000 2,000,000 Cash Flow Year 2 7,250,000 6,000,000 Cash Flow Year 3 8,500,000 12,000,000 Cash Flow Year 4 10,000,000 12,000,000 Total Cash Inflows 31,500,000 32,000,000 9% WACC - both options Reinvestment rate 2% Results of Capital Budgeting Analysis Tool Build New Expand Line Payback Net Present Value (NPV) Internal Rate of Return (IRR) Modified Internal Rate of Return (MIRR) Objective: Students demonstrate ability to 1. Utilize several capital budgeting concepts. 2. Complete required calculations. 3. Present written and oral recommendation to senior management based on resulting data. Scenario: HW Enterprises is considering purchasing a new warehouse or expanding overseas. Cost will be $20,000 million for either option. Discount rate for both projects is 9%. Cash inflows for each options are below. Shown in millions: Year 1 2 3 4 Option A: New Warehouse $ 5,750 7,250 8,500 10,000 Option B: Expand Overseas $ 2,000 6,000 12,000 12,000 Directions: Utilizing cell references and formulas, use the data in the Excel file provided to prepare A. Calculations - use the tabs provided for details of calculations - move final values to summary tab using cell references. 1. Payback period calculation. Textbook page 384 has example. You need to use Excel to make your calculations. 2. Net Present Value - work the process step-by-step then utilize the NPV formula under the Formulas, Financials, tab. *See Table 12.4 in text page 386 for example. Note: you will be calculating the NPV using the two methods shown on Table 12.4. Assume 9% as discount rate. 3. Internal Rate of Return and Modified Internal Rate of Return-utilize the IRR and MIRR formulas under the Formulas, Financial, tab in Excel. *See Table 12.5 in text page 388 for example. Assume 2% as the reinvestment rate. B. Prepare written report of your team's recommendation to senior management of HW Enterprise. Be ready to discuss recommendation in class. 1. State need for project. 2. Recommendation of option to solve need. 3. Support your recommendation with facts based on your Excel calculations. Refer to computations in Excel file provided as attachment. 5. Close statement by re-stating recommendation. C. Submit Word document and Excel file to Blackboard. New Warehouse: Year Expand Product Line: Year Rate 2 4 Rate 0 ($20,000,000) 1 5,750,000 2 7,250,000 3 8,500,000 4 10,000,000 0 ($20,000,000) 3 $12,000,000 Cash Flows 9% Cash Flows $2,000,000 $6,000,000 $12,000,000 9% 0 3 0 1 2 4 -20000000 Payback Cash Flows Cum. Cash Flows Payback (in years) 1 5750000 -14250000 2 7250000 -7000000 -20000000 8500000 1500000 10000000 11500000 Payback Cash Flows Cum. Cash Flows Payback (in years) 2000000 -18000000 6000000 -12000000 3 12000000 0 12000000 12000000 2.82 3.00 4 Discounted Payback Year PV of Cash Flows Cum. Cash Flows Payback (in years) 0 -20000000 1 $5,275,229.36 ($14,724,770.64) 2 $6,102,179.95 ($8,622,590.69) 3 $6,563,559.58 ($2,059,031.11) Discounted Payback Year PV of Cash Flows Cum. Cash Flows Payback (in years) 0 -20000000 $7,084,252.11 $5,025, 221.00 1 $1,834,862.39 ($18,165,137.61) 2 $5,050,079.96 ($13,115,057.66) 3 $9,266,201.76 ($3,848,855.89) 4 $8,501,102.53 $4,652,246.64 3.29 3.45 3 2 4 Net Present Value Year Cash Flows PV of Cash Flows Sum of Inflows NPV Using Excel Wizard 1 5750000 $5,275,229.36 $25,025,221.00 2 7250000 $6,102,179.95 8500000 $6,563,559.58 4 10000000 $7,084,252.11 Net Present Value Year Cash Flows PV of Cash Flows Sum of Inflows NPV Using Excel Wizard 0 -20000000 -20000000 -20000000 $5,025, 221.00 $5,025,221.00 6000000 $5,050,079.96 2000000 $1,834,862.39 $24,652,246.64 3 12000000 $9,266,201.76 12000000 $8,501,102.53 0 -20000000 -20000000 -20000000 $4,652,246.64 $4,652,246.64 Internal Rate of Return (IRR) Using Excel Wizard Internal Rate of Return (IRR) Using Excel Wizard 18.96% 16.98% Reinvest 2% Reinvest 0.02 1 2 4 1 3 4 12000000 Modified Internal Rate of Return (MIRR) Year 0 Cash Flows -20000000 FV of Cash Inflows Sum of FV inflows -20000000 MIRR using IRR 12.74% MIRR using MIRR 12.74% 5750000 $6,101,946.00 0 7250000 $7,542,900.00 0 3 8500000 $8,670,000.00 0 10000000 10000000 $32,314,846.00 Modified Internal Rate of Return (MIRR) Year 0 Cash Flows -20000000 FV of Cash Inflows Sum of FV inflows -20000000 MIRR using IRR 13.00% MIRR using MIRR 13.00% 2000000 $2,122,416.00 0 2 6000000 $6,242,400.00 0 12000000 $12,240,000.00 0 12000000 $32,604,816.00 Profitability Index PV inflows/PV outflows Profitability Index PV inflows/PV outflows 1.25 1.23 HW Enterprises' senior management is trying to determine if they should purchase a new warehouse or expand overseas. Both require a $20,000 million investment. Calculate payback period, net present value, IRR, and MIRR. Then develop a recommendation, with explanation, to senior management. The recommendation should bein email-format. Submit Excel file and Word document. Purchase warehouse Expand overseas Initial Investment - Year O $20,000,000 $20,000,000 Cash Flow Year 1 5,750,000 2,000,000 Cash Flow Year 2 7,250,000 6,000,000 Cash Flow Year 3 8,500,000 12,000,000 Cash Flow Year 4 10,000,000 12,000,000 Total Cash Inflows 31,500,000 32,000,000 9% WACC - both options Reinvestment rate 2% Results of Capital Budgeting Analysis Tool Build New Expand Line Payback Net Present Value (NPV) Internal Rate of Return (IRR) Modified Internal Rate of Return (MIRR)