B 15 16 1) Useful life of the equipment (years) 17 2) New equipment cost* 18...

Transcribed Image Text:

B 15 16 1) Useful life of the equipment (years) 17 2) New equipment cost* 18 3) Equipment ship & install costs* C 19 4) Related equipment start-up costs* 20 5) Inventory increase. 21 6) Accounts payable increase 22 7) Equipment salvage value (end of life) 23 8) Projected sales for year 1 24 9) Projected sales growth per year 25 Low Case 26 Base Case 27 High Case 28 10) Operating cost (as a % of sale 29 11) Depreciation (straight line) per year 30 12) Corporate marginal tax rate (T) 31 13) Cost of capital or discount rate A 11 Assignment Instructions: There are 3 parts to this assignment, which combines using Excel to perform calculations and answering eight questions on the "Additional Questions" tab. The use of formulas and cell references (rather than hard typing in numbers) should be used in all cases where they can. To guide you, the cells shaded gray are where formulas, 12 numerical inputs or calcuations are needed in order to complete the assignment. Your grade for this assignment will be determined by a combination or the correctness or your financial matn, good use of Excel functionality and spreadsheet formatting, and the completeness of your answers to the 13 questions. -60% 4 $ (200,000) $ (35,000) $ (5,000) In this assignment, you are evaluating a potential capital investment project that has a 4 year life. The financial 14 details are as follows: $ 25,000 $ 5,000 $ 15,000 $ 200,000 2% 5% D 8% $(120,000) $ (60,000) E 21% 10% F G H 32 * Note: Equipment cost, shipping and installation, and start-up costs are all depreciated with equipment 33 PART 1A: Use the Excel cells below to calculate all estimated project cash flows for the BASE CASE revenue growth. Then solve for the NPV, IRR and Payback period for the BASE CASE. CF(0) = Cash Flow at Time 0 Year Investments: 1) Equipment cost 2) Shipping and Install cost 3) Start up expenses Total Equipment Cost Basis (1+2+3) 4) Net Working Capital Total Initial Outlay Operations: Revenue Operating Cost Depreciation EBIT Taxes Net Income Add back Depreciation Total Operating Cash Flow Terminal Cash Flows (end of Year 4) 1) Change in net WC 2) Salvage value (after tax) [= Salvage value before tax*(1-T)] Total Project Net Cash Flows CF(0) 0 $ (200,000) $ (35,000) $ (5,000.00) $ (240,000) $ (20.000) $ (260,000) CF(1) 1 69 IRR = NPV (Base Case) = $51,193 Part 1B: Answer the PART 1B questions on the "Additional Questions" tab $ 200,000 $210,000 $220,500 $ 231,525 $ 120,000 $126.000 $132.300 $138.915 $ $ 60.000 $60.000 $60.000 $ 60.000 20,000 $ 24,000 $28,200 $ 32,610 4.200 $5.040 $ 5,922 $ 6.848 15,800 $ 18,960 $ 22,278 $ 25,762 $ $ $ 60,000 $ 60,000 $60,000 $ 60,000 $ 75,800 $ 78,960 $82,278 $ 85,762 CF(2) 2 I $(260,000) $ 75,800 $ 78,960 $82,278 21% CF(3) 3 CF(4) 4 = $ 20,000 $ 11,850 $ 31,850 $ 117,612 Payback 2.28 PART 3A: Use the Excel cells from Part 1 above to run a sensitivity analysis on revenue growth. Recall from above the high case has 8% revenue growth, base case is 5% and low case is 2%. Re-calculate NPV, IRR and Payback period for the high and low case and correctly fill out this table. Growth (%) NPV ($'s) 2% 5% 8% Sensitivity Analysis Table Low Growth Base Case (from part 1A) High Growth Part 3B: Answer the PART 3B questions on the "Additional Questions" tab IRR (%) Payback PART 3B Questions: Sensitivity Analysis Question 5: As the firm's CFO, how would you explain the sensitivity analysis on revenue growth? What insights do you see? Answer 5: B 15 16 1) Useful life of the equipment (years) 17 2) New equipment cost* 18 3) Equipment ship & install costs* C 19 4) Related equipment start-up costs* 20 5) Inventory increase. 21 6) Accounts payable increase 22 7) Equipment salvage value (end of life) 23 8) Projected sales for year 1 24 9) Projected sales growth per year 25 Low Case 26 Base Case 27 High Case 28 10) Operating cost (as a % of sale 29 11) Depreciation (straight line) per year 30 12) Corporate marginal tax rate (T) 31 13) Cost of capital or discount rate A 11 Assignment Instructions: There are 3 parts to this assignment, which combines using Excel to perform calculations and answering eight questions on the "Additional Questions" tab. The use of formulas and cell references (rather than hard typing in numbers) should be used in all cases where they can. To guide you, the cells shaded gray are where formulas, 12 numerical inputs or calcuations are needed in order to complete the assignment. Your grade for this assignment will be determined by a combination or the correctness or your financial matn, good use of Excel functionality and spreadsheet formatting, and the completeness of your answers to the 13 questions. -60% 4 $ (200,000) $ (35,000) $ (5,000) In this assignment, you are evaluating a potential capital investment project that has a 4 year life. The financial 14 details are as follows: $ 25,000 $ 5,000 $ 15,000 $ 200,000 2% 5% D 8% $(120,000) $ (60,000) E 21% 10% F G H 32 * Note: Equipment cost, shipping and installation, and start-up costs are all depreciated with equipment 33 PART 1A: Use the Excel cells below to calculate all estimated project cash flows for the BASE CASE revenue growth. Then solve for the NPV, IRR and Payback period for the BASE CASE. CF(0) = Cash Flow at Time 0 Year Investments: 1) Equipment cost 2) Shipping and Install cost 3) Start up expenses Total Equipment Cost Basis (1+2+3) 4) Net Working Capital Total Initial Outlay Operations: Revenue Operating Cost Depreciation EBIT Taxes Net Income Add back Depreciation Total Operating Cash Flow Terminal Cash Flows (end of Year 4) 1) Change in net WC 2) Salvage value (after tax) [= Salvage value before tax*(1-T)] Total Project Net Cash Flows CF(0) 0 $ (200,000) $ (35,000) $ (5,000.00) $ (240,000) $ (20.000) $ (260,000) CF(1) 1 69 IRR = NPV (Base Case) = $51,193 Part 1B: Answer the PART 1B questions on the "Additional Questions" tab $ 200,000 $210,000 $220,500 $ 231,525 $ 120,000 $126.000 $132.300 $138.915 $ $ 60.000 $60.000 $60.000 $ 60.000 20,000 $ 24,000 $28,200 $ 32,610 4.200 $5.040 $ 5,922 $ 6.848 15,800 $ 18,960 $ 22,278 $ 25,762 $ $ $ 60,000 $ 60,000 $60,000 $ 60,000 $ 75,800 $ 78,960 $82,278 $ 85,762 CF(2) 2 I $(260,000) $ 75,800 $ 78,960 $82,278 21% CF(3) 3 CF(4) 4 = $ 20,000 $ 11,850 $ 31,850 $ 117,612 Payback 2.28 PART 3A: Use the Excel cells from Part 1 above to run a sensitivity analysis on revenue growth. Recall from above the high case has 8% revenue growth, base case is 5% and low case is 2%. Re-calculate NPV, IRR and Payback period for the high and low case and correctly fill out this table. Growth (%) NPV ($'s) 2% 5% 8% Sensitivity Analysis Table Low Growth Base Case (from part 1A) High Growth Part 3B: Answer the PART 3B questions on the "Additional Questions" tab IRR (%) Payback PART 3B Questions: Sensitivity Analysis Question 5: As the firm's CFO, how would you explain the sensitivity analysis on revenue growth? What insights do you see? Answer 5: