B C D E F Learning Objectives 1. Understand how to use EXCEL Spreadsheet 10(a) Develop proforma Income Statement Using Excel Spreadsheet 11 (b) Compute Net Project Cashflows, NPV, and IRR 72 (c) Develop problem-solving and critical thinking skills and make long-term investment decisions 3 - 1) Life Period of the Equipment = 4 years 62) New equipment cost 73) Equipment ship & install cost 3 4) Related start up cost 5) Inventory increase 6) Accounts Payable increase 7) Equip. salvage value before tax ($200,000) ($35,000) ($5,000) $25,000 $5,000 $15,000 8) Sales for first year (1) 9) Sales increase per year 10) Operating cost (60% of Sales) (as a percent of sales in Year 1) 11) Depreciation 12) Marginal Corporate Tax Rate (T) 13) Cost of Capital (Discount Rate) 200,000 5% (120,000) -60% Use 3-yr MACRIS 35% 10% Filling data in the cells colored only. Do not write in any other cell. Do not delete any row or column ESTIMATING Initial Outlay (Cash Flow, CFO, T= 0) -70 CFO 0 CF1 1 CF2 2 CF3 3 CF4 4 Year Investments: 1) Equipment cost 2) Shipping and Install cost 3) Start up expenses Total Basis Cost (1+2+3) EBIT = Sales - Costs - D Alignment Number CFO 0 CF1 1 CF2 CF4 CF3 3 EBIT = Sales - Costs - D $ 27 28 Year 29 Investments: 301) Equipment st 31 2) Shipping and Install cost 323) Start up expenses 33 Total Basis Cost (1+2+3) 344) Net Working Capital 35 Total Initial Outlay 28 3y Operations: 38 Sales 39 Operating Cost negative 40 Depreciation 41 EBIT 42 Taxes Net Income 44 45 Add back Depreciation 46 Total Operating Cash Flow $ $ 6 l $ GA $ s $ $ - $ 49 Terminal values: 501) Change in net WC 512) Salvage value (after tax) 52 Total Case and Template Response Sheet3 Sheet1 (2) Sheet1 4