A B C D E F G H 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 grace for this assignment will be determined by a combination of the correctness of your financial matn, good use of Excel functionality and spreadsheet formatting, and the completeness of your answers to the 13 questions In this assignment, you are evaluating a potential capital investment project that has a 4 year life. The financial 14 details are as follows: 15 16 1) Useful life of the equipment (years) 4 17 2) New equipment cost* $ (200,000) 18 3) Equipment ship & install costs* $ (35,000) 19 4) Related equipment start-up costs* $ (5,000) 20 5) Inventory increase $ 25,000 21 6) Accounts payable increase $ 5,000 22 7) Equipment salvage value (end of life) $ 15,000 23 8) Projected sales for year 1 $ 200,000 24 9) Projected sales growth per year Low Case 2% 26 Base Case 5% 27 High Case 8% 28 10) Operating cost (as a % of sale -60% $(120,000) 29 11) Depreciation (straight line) per year $ 60,000) 30 12) Corporate marginal tax rate (T) 21% 31 13) Cost of capital or discount rate (1) 10% 32 * Note: Equipment cost, shipping and installation, and start-up costs are all depreciated with equipment 25 A B E F G H 36 PART 1A: Use the Excel cells below to calculate all estimated project cash flows for the BASE CASE 35 revenue growth. Then solve for the NPV, IRR and Payback period for the BASE CASE. 37 CF(0) = Cash Flow at Time 0 CF(0) CF(1) CF(2) CF(3) CF(4) 38 Year 0 1 2 3 4 39 Investments: 401) Equipment cost 41 2) Shipping and Install cost 42 3) Start up expenses 43 Total Equipment Cost Basis (1+2+3) 44 4) Net Working Capital 45 Total Initial Outlay 46 47 Operations: 48 Revenue 49 Operating Cost 50 Depreciation 51 EBIT 52 Taxes 53 Net Income 54 55 Add back Depreciation 56 57 Total Operating Cash Flow XXXX XXXXX XXXXX XXXXX 58 $ $ $ 59 Terminal Cash Flows (end of Year 4) 601) Change in net WC 61 2) Salvage value (after tax) [= Salvage value before tax * (1-1)] Total $ 20,000 xxxxx XXXXX 62 63 64 $ $ $ $ $ Project Net Cash Flows 65 66 NPV (Base Case) 67 IRR = Payback =