1. Understand how to use EXCEL Spreadsheet (a) Develop proforma Income Statement Using Excel Spreadsheet (b) Compute Net Project Cashflows, NPV, and IRR (c) Develop problem-solving and critical thinking skills and make long-term investment decisions 1) Life Period of the Equipment = 4 years 2) New equipment cost 3) Equipment ship & install cost 4) Related start up cost 5) Inventory increase 6) Accounts Payable increase 7) Equip. salvage value before tax Filling data in the cells colored ESTIMATING Initial Outlay (Cash Flow, CFO, T=0) Year Investments: 1) Equipment cost 2) Shipping and Install cost 3) Start up expenses Total Basis Cost (1+2+3) 4) Net Working Capital Total Initial Outlay Operations: Sales Operating Cost negative Depreciation EBIT Taxes Net Income Add back Depreciation Total Operating Cash Flow Terminal values: 1) Change in net WC 2) Salvage value (after tax) Total Project Net Cash Flows NPV = $0.00 $ Depreciation rate for recovery period Year 14 17 19 12345 6TBRO 2315 618022 3-year 33.33% 5-year 7-year 10-year 15-year 20-year 20.00% 14.29% 10.00% 5.00% 44.45 32.00 24.49 18.00 9.50 14.81 19.20 17.49 14.40 8.55 3.750% 7.219 6.677 7.41 11.52 12.49 11.52 7.70 6.177 11.52 8.93 9.22 6.93 5.713 5.76 8.92 7.37 6.23 5.285 8.93 6.55 5.90 4.888 4.46 6.55 5.90 4.522 6.56 5.91 4.462 6.55 5.90 4.461 3.28 5.91 4.462 5.90 4.461 5.91 4.462 5.90 4.461 5.91 4.462 2.95 4.461 4.462 4.461 4.462 4.461 2.231 ($200,000) ($35,000) ($5,000) $25,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 $ 200,000 9 10 5% $ (120,000) 11 -60% Use 3-yr MACRIS $5,000 $15,000 12) Marginal Corporate Tax Rate (T) 13) Cost of Capital (Discount Rate) 35% 10% only. Do not write in any other cell. Do not delete any row or column -70 CFO 0 CF1 1 CF2 CF3 2 3 CF4 4 $ $ SA SA SA SA EA $ IRR= #NLIMI SA SA SA GA $ EBIT = Sales - Costs - D Depreciation Calculation Depreciation Basis: # of years: Macrs Year 1 2 234 4 A 4 3 years A B A*B Basis Macrs % Depreciation EA SA SA A $0 $0 $0 $0 0% 0 Salvage value*(1 - marginal tax rate) salvage value - T*(Sal Payback= 0.00 Payback Period. $ $