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, numerical inputs or calcuations are needed in order to complete the assignment. Your grade for this assignment will be determined by a combination of the correctness of your financial math, good use of Excel functionality and spreadsheet formatting, and the completeness of your answers to the questions. In this assignment, you are evaluating a potential capital investment project that has a 4 year life. The financial details are as follows: 4 1) Useful life of the equipment (years) 2) New equipment cost* $ (200,000) 3) Equipment ship & install costs* $ (35,000) 4) Related equipment start-up costs* $ (5,000) 5) Inventory increase $ 25,000 6) Accounts payable increase $ 5,000 7) Equipment salvage value (end of life) $ 15,000 8) Projected sales for year 1 $ 200,000 9) Projected sales growth per year Low Case 2% Base Case 5% High Case 8% 10) Operating cost (as a % of sales) -60% $ (120,000) 11) Depreciation (straight line) per year $ (60,000) 12) Corporate marginal tax rate (T) 21% 10% 13) Cost of capital or discount rate (r) * Note: Equipment cost, shipping and installation, and start-up costs are all depreciated with equipment 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(1) CF(0) = Cash Flow at Time 0 Year CF(0) 0 CF(2) CF(3) 2 3 CF(4) 1 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 Net Income Add back Depreciation XXXXX XXXXX XXXXX XXXXX $ $ $ 20,000 XXXXX XXXXX Taxes XXXXX 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 $ IRR = NPV (Base Case) = $ Payback 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, numerical inputs or calcuations are needed in order to complete the assignment. Your grade for this assignment will be determined by a combination of the correctness of your financial math, good use of Excel functionality and spreadsheet formatting, and the completeness of your answers to the questions. In this assignment, you are evaluating a potential capital investment project that has a 4 year life. The financial details are as follows: 4 1) Useful life of the equipment (years) 2) New equipment cost* $ (200,000) 3) Equipment ship & install costs* $ (35,000) 4) Related equipment start-up costs* $ (5,000) 5) Inventory increase $ 25,000 6) Accounts payable increase $ 5,000 7) Equipment salvage value (end of life) $ 15,000 8) Projected sales for year 1 $ 200,000 9) Projected sales growth per year Low Case 2% Base Case 5% High Case 8% 10) Operating cost (as a % of sales) -60% $ (120,000) 11) Depreciation (straight line) per year $ (60,000) 12) Corporate marginal tax rate (T) 21% 10% 13) Cost of capital or discount rate (r) * Note: Equipment cost, shipping and installation, and start-up costs are all depreciated with equipment 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(1) CF(0) = Cash Flow at Time 0 Year CF(0) 0 CF(2) CF(3) 2 3 CF(4) 1 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 Net Income Add back Depreciation XXXXX XXXXX XXXXX XXXXX $ $ $ 20,000 XXXXX XXXXX Taxes XXXXX 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 $ IRR = NPV (Base Case) = $ Payback