Depreciation rate for recovery period Year 3-year 5-year 7-year 10-year 15-year 20-year 1 2. 3 33.33% 44.45 14.81 7.41 20.00% 32.00 19.20 11.52 11.52 14.29% 24.49 17.49 12.49 8.93 10.00% 18.00 14.40 11.52 9.22 5.00% 9.50 8.55 7.70 6.93 3.750% 7.219 6.677 6.177 5.713 5 5.76 6 7 8 9 10 8.92 8.93 4.46 7.37 6.55 6.55 6.56 6.55 6.23 5.90 5.90 5.91 5.90 5.285 4.888 4.522 4.482 4.461 11 3.28 12 13 14 15 5.91 5.90 5.91 5.90 5.91 4.462 4.461 4.462 4.461 4.462 2.95 16 17 18 19 20 4.461 4.462 4.461 4.462 4.461 21 2.231 51 52 Salvage value*(1 - marginal tax rate) (15000) 54 55 58 Payback Period 57 58 Year Count BO 0 1 2 3 4 Projected CF Cummulative CF $ 240,000 $ 240,000 $ 208,000 $ 448,000 $ 218,400 $ 666,400 $ 57,330 $ 723,730 $ 95.197 $ 818,927 Payback period 0.00 years 05 Discounted Payback Period Present Value Year Count Discount factor 1 Discounted CF $240,000 $0 AWNo Projected CF $ 240,000 $ 208,000 $ 218,400 $ 57,330 $ 95,197 Payback period $0 $0 $0 Cummulative CF $ 240.000 $ 240,000 $ 240,000 $ 240.000 $ 240,000 0.00 years Case and Template Sheet3 Sheet102 Sheet1 G H K M N 1 $ AWN $0 $0 $0 $0 $ 0% $ Salvage value*(1 - marginal tax rate) $ Payback= 0.00 Payback Period anted Payback = Count Year 0.00 1 2 3 4. Projected CF Cummulative CF $ $ $ $ $ $ $ $ $ Payback period 0.00 years Discounted Payback Period Present Value scount factor Discounted CF Cummulative CF NOILLILLI VILI es et contain uses. Unless you need to edit it's safer to stay in Protected View Enable Editing Sue f B E 1. Understand how to use EXCEL Spreadsheet (a) Develop proforma Income Statement Using Excel Spreadsheet 11 (b) Compute Net Project Cashflows, NPV, and IRR 12 (c) Develop problem-solving and critical thinking skills and make long-term investment decisions 15 1) Life Period of the Equipment = 4 years 182) New equipment cost 17 3) Equipment ship & install cost 18 4) Related start up cost 195) Inventory increase 206) Accounts Payable increase 21 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 25 ESTIMATING Initial Outlay (Cash Flow, CFO, T=0) 30 -70 CF1 CF4 CFO 0 CF2 2 CF3 3 1 EBIT = Sales - Costs - D 28 Year - Investments: 301) Equipment cost 31 2) Shipping and Install cost 32 3) Start up expenses Total Basis Cost (1+2+3) 344) Net Working Capital Total Initial Outlay A ESTIMATING Initial Outlay Cash Flow, CFO, TH 0) -70 CFO 0 CF1 1 CF3 Year CF2 2 CF4 4 EBIT = Sales - Costs - D 29 Investments: 301) Equipment cost 312) Shipping and Install cost 3) Start up expenses Total Basis Cost (1+2+3) 344) Net Working Capital 36 Total Initial Outlay $ 37 Operations: 38 Sales 30 Operating cost negative 40 Depreciation 41 EBIT 2 Taxes 13 Net Income 44 45 Add back Depreciation 40 Total Operating Cash Flow HA NA $ $ $ $ $ 49 Terminal values: 50 1) Change in net WC 512) Salvage value (after tax) Total 83 34 Project Net Cash Flows Case and Template Response Sheet3 Sheet1 (2) Sheet1 1 Type here to search OP negative 39 Operating Cost 40 Depreciation EBIT 2 Taxes 13 Net Income 11 1 = $ $ HA 69 - $ $ $ - $ 45 Add back Depreciation 48 Total Operating Cash Flow 28 49 Terminal values: 50 1) Change in net WC 512) Salvage value (after tax) Total $ $ $ $ $ 54 Project Net Cash Flows 55 50 NPV = $0.00 IRR = #NUM! Payback= 0.00 #DIV/0! 58 Profitability Index = 59 Discounted Payback = 0.00 GO B2 83 PLEASE RESPOND TO THESE QUESTIONS ON ANOTHER TAB Case and Template Response Sheet3 Sheet1 (2) Sheet1 an 1 Depreciation rate for recovery period Year 3-year 5-year 7-year 10-year 15-year 20-year 1 2. 3 33.33% 44.45 14.81 7.41 20.00% 32.00 19.20 11.52 11.52 14.29% 24.49 17.49 12.49 8.93 10.00% 18.00 14.40 11.52 9.22 5.00% 9.50 8.55 7.70 6.93 3.750% 7.219 6.677 6.177 5.713 5 5.76 6 7 8 9 10 8.92 8.93 4.46 7.37 6.55 6.55 6.56 6.55 6.23 5.90 5.90 5.91 5.90 5.285 4.888 4.522 4.482 4.461 11 3.28 12 13 14 15 5.91 5.90 5.91 5.90 5.91 4.462 4.461 4.462 4.461 4.462 2.95 16 17 18 19 20 4.461 4.462 4.461 4.462 4.461 21 2.231 51 52 Salvage value*(1 - marginal tax rate) (15000) 54 55 58 Payback Period 57 58 Year Count BO 0 1 2 3 4 Projected CF Cummulative CF $ 240,000 $ 240,000 $ 208,000 $ 448,000 $ 218,400 $ 666,400 $ 57,330 $ 723,730 $ 95.197 $ 818,927 Payback period 0.00 years 05 Discounted Payback Period Present Value Year Count Discount factor 1 Discounted CF $240,000 $0 AWNo Projected CF $ 240,000 $ 208,000 $ 218,400 $ 57,330 $ 95,197 Payback period $0 $0 $0 Cummulative CF $ 240.000 $ 240,000 $ 240,000 $ 240.000 $ 240,000 0.00 years Case and Template Sheet3 Sheet102 Sheet1 G H K M N 1 $ AWN $0 $0 $0 $0 $ 0% $ Salvage value*(1 - marginal tax rate) $ Payback= 0.00 Payback Period anted Payback = Count Year 0.00 1 2 3 4. Projected CF Cummulative CF $ $ $ $ $ $ $ $ $ Payback period 0.00 years Discounted Payback Period Present Value scount factor Discounted CF Cummulative CF NOILLILLI VILI es et contain uses. Unless you need to edit it's safer to stay in Protected View Enable Editing Sue f B E 1. Understand how to use EXCEL Spreadsheet (a) Develop proforma Income Statement Using Excel Spreadsheet 11 (b) Compute Net Project Cashflows, NPV, and IRR 12 (c) Develop problem-solving and critical thinking skills and make long-term investment decisions 15 1) Life Period of the Equipment = 4 years 182) New equipment cost 17 3) Equipment ship & install cost 18 4) Related start up cost 195) Inventory increase 206) Accounts Payable increase 21 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 25 ESTIMATING Initial Outlay (Cash Flow, CFO, T=0) 30 -70 CF1 CF4 CFO 0 CF2 2 CF3 3 1 EBIT = Sales - Costs - D 28 Year - Investments: 301) Equipment cost 31 2) Shipping and Install cost 32 3) Start up expenses Total Basis Cost (1+2+3) 344) Net Working Capital Total Initial Outlay A ESTIMATING Initial Outlay Cash Flow, CFO, TH 0) -70 CFO 0 CF1 1 CF3 Year CF2 2 CF4 4 EBIT = Sales - Costs - D 29 Investments: 301) Equipment cost 312) Shipping and Install cost 3) Start up expenses Total Basis Cost (1+2+3) 344) Net Working Capital 36 Total Initial Outlay $ 37 Operations: 38 Sales 30 Operating cost negative 40 Depreciation 41 EBIT 2 Taxes 13 Net Income 44 45 Add back Depreciation 40 Total Operating Cash Flow HA NA $ $ $ $ $ 49 Terminal values: 50 1) Change in net WC 512) Salvage value (after tax) Total 83 34 Project Net Cash Flows Case and Template Response Sheet3 Sheet1 (2) Sheet1 1 Type here to search OP negative 39 Operating Cost 40 Depreciation EBIT 2 Taxes 13 Net Income 11 1 = $ $ HA 69 - $ $ $ - $ 45 Add back Depreciation 48 Total Operating Cash Flow 28 49 Terminal values: 50 1) Change in net WC 512) Salvage value (after tax) Total $ $ $ $ $ 54 Project Net Cash Flows 55 50 NPV = $0.00 IRR = #NUM! Payback= 0.00 #DIV/0! 58 Profitability Index = 59 Discounted Payback = 0.00 GO B2 83 PLEASE RESPOND TO THESE QUESTIONS ON ANOTHER TAB Case and Template Response Sheet3 Sheet1 (2) Sheet1 an 1