Learning Objectives Year 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 3-year 33.33% 44.45 14.81 7:41 Depreciation rate for recovery period 5-year 7-year 10-year 16-year 20.00% 14.29% 10.00% 5.00% 32.00 24.49 18.00 9.50 19.20 17.40 14.40 8.55 11.52 12.49 11.52 7.70 11.52 8.93 9.22 6.93 20-year 3.750% 7.210 6.677 6.177 5.713 5.76 5.285 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 B) Accounts Payable Increase 7) Equip. salvage value before tax 8.92 2.93 1.-16 ($200,000) (535,000) ($5,000) $25,000 $5,000 $10,000 $ 200.000 05 % $ [120,000) -60% % Use 3-yr MACRI 35% % 10% B) Sales for first year (1) 9) Sales increase per year 10) Operating cost (60% of Sales) (as a percent of sales in Year 11 11) Depreciation 12) Marginal Corporate Tax Rate 11 13) Cost of Capital (Discount Rate) only. Do not write in any other cell, Do not delete any row or column 7.37 6.55 8.55 8.5e 8.55 6.23 5.90 5.00 5.91 5.90 4.462 4.461 88646 1888: O*. ---- 3.28 5.91 6.00 5.91 5.00 5.91 14.40 4.461 4.46 4.461 14.402 Filing data in the cells colored ESTIMATING Initial Outlay (Cash Flow, CFO, TO) 2.95 4.461 CFO O CF3 CF1 1 CF2 2 CF4 4 4.461 4.400 4.461 21 2.231 EBIT = Sales Costs - D Year Investments: 1) 2) Shipping and Install cost 3) Start up expenses Total Basis Cost (1+213) 4) Nl Working Capital Total Initial Outlay $ $ Depreciation Calculation $ negative $ Operatione: Salles Operating Cost Depreciation FRIT Tiss Net Income S S S Depreciation Basis: # of years: Macrs 4 3 years $ $ $ $ S Year Basis Macrs % Acklack Depreciation AU Depreciation SO SO SO $0 1 2 3 4 S Total Operating Cash Flow $ S 0% Terminal values: 1) Change in net WC 2) Salvage value (after tax) Total $ Salvage value" (1 - marginal tax rate) Project Net Cash Flows $ $ S $ $ NPV = $0.00 IRR = #NUM: Payback 0.00 Payback Period Profitability Index = #DIV/0! Discounted Payback = 0.00 Year Coun D 1 2 3 Project cr $ $ $ Cumulativer $ $ $ $ $ nurnar Onun Tin Terminal values: 1) Change in no WC 2) Salvage value (after tax) Tolal $ Salvage value (1 - marginal tax rate) Project Net Cash Flows $ $ $ $ NPV = S0.00 IRR =' #NUMI Payback 0.00 Payback Period Profitability Index #DIV/0! Discounted Payback 0.00 Count Year 0 1 2 3 4 4 Prejected CF Cummulativa CF $ $ $ $ $ $ $ $ $ $ $ Payback period PLEASE RESPOND TO THESE QUESTIONS ON ANOTHER TAB 0.00 years Discounted Payback Period Q#1 Would you accept the project based on NPV, IRR? Would you accept the project based on Payback rule if project cut-off is 3 years? Q#2 Impact of 2017 Tax Cut Act on Net Income, Cash Flows and Capital Budgeting (Investment ) Decisions (a) Estimate NPV, IRR and Payback period of the project if lax Tale equals to 2195. Would you accept or reject the project? (b) b As a Cro of the firm, which of the above two scenarios (1) or (2) would you choose? Why? Q#3 How would you explain to your CEO what NPV means? Year Cummulative CF Count $ 0 1 2 3 Projected CF $ $ $ $ $ Payback period Present Vale Discount factor Discounted CF 1 SO SO SO SO SO $ $ $ $ 4 0.00 years Q#4 What are advantages and disadvantages of using only Payback method? O#5 What are advantages and disadvantages of using NPV versus IRR? # 1/(1+r)^n Q#6 Explain the difference between independent projects and mutually exclusive projects. When you are confrontext with Mutually Exclusive: Projects and have coflicts with NPV and IRR results, which criterion would you use (NPV or IRR) and why? for a First complete this spreadsheet Then night click on the tab Select Monos C Check the Create a Copybox in the lower left corner of the plot me Click "OK" a copy of the completed spreadsheet will be created. You can proceed to change the tax rate to 21% and adut the payback period: Adjust salvage value after tax You huse there were ques Learning Objectives Year 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 3-year 33.33% 44.45 14.81 7:41 Depreciation rate for recovery period 5-year 7-year 10-year 16-year 20.00% 14.29% 10.00% 5.00% 32.00 24.49 18.00 9.50 19.20 17.40 14.40 8.55 11.52 12.49 11.52 7.70 11.52 8.93 9.22 6.93 20-year 3.750% 7.210 6.677 6.177 5.713 5.76 5.285 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 B) Accounts Payable Increase 7) Equip. salvage value before tax 8.92 2.93 1.-16 ($200,000) (535,000) ($5,000) $25,000 $5,000 $10,000 $ 200.000 05 % $ [120,000) -60% % Use 3-yr MACRI 35% % 10% B) Sales for first year (1) 9) Sales increase per year 10) Operating cost (60% of Sales) (as a percent of sales in Year 11 11) Depreciation 12) Marginal Corporate Tax Rate 11 13) Cost of Capital (Discount Rate) only. Do not write in any other cell, Do not delete any row or column 7.37 6.55 8.55 8.5e 8.55 6.23 5.90 5.00 5.91 5.90 4.462 4.461 88646 1888: O*. ---- 3.28 5.91 6.00 5.91 5.00 5.91 14.40 4.461 4.46 4.461 14.402 Filing data in the cells colored ESTIMATING Initial Outlay (Cash Flow, CFO, TO) 2.95 4.461 CFO O CF3 CF1 1 CF2 2 CF4 4 4.461 4.400 4.461 21 2.231 EBIT = Sales Costs - D Year Investments: 1) 2) Shipping and Install cost 3) Start up expenses Total Basis Cost (1+213) 4) Nl Working Capital Total Initial Outlay $ $ Depreciation Calculation $ negative $ Operatione: Salles Operating Cost Depreciation FRIT Tiss Net Income S S S Depreciation Basis: # of years: Macrs 4 3 years $ $ $ $ S Year Basis Macrs % Acklack Depreciation AU Depreciation SO SO SO $0 1 2 3 4 S Total Operating Cash Flow $ S 0% Terminal values: 1) Change in net WC 2) Salvage value (after tax) Total $ Salvage value" (1 - marginal tax rate) Project Net Cash Flows $ $ S $ $ NPV = $0.00 IRR = #NUM: Payback 0.00 Payback Period Profitability Index = #DIV/0! Discounted Payback = 0.00 Year Coun D 1 2 3 Project cr $ $ $ Cumulativer $ $ $ $ $ nurnar Onun Tin Terminal values: 1) Change in no WC 2) Salvage value (after tax) Tolal $ Salvage value (1 - marginal tax rate) Project Net Cash Flows $ $ $ $ NPV = S0.00 IRR =' #NUMI Payback 0.00 Payback Period Profitability Index #DIV/0! Discounted Payback 0.00 Count Year 0 1 2 3 4 4 Prejected CF Cummulativa CF $ $ $ $ $ $ $ $ $ $ $ Payback period PLEASE RESPOND TO THESE QUESTIONS ON ANOTHER TAB 0.00 years Discounted Payback Period Q#1 Would you accept the project based on NPV, IRR? Would you accept the project based on Payback rule if project cut-off is 3 years? Q#2 Impact of 2017 Tax Cut Act on Net Income, Cash Flows and Capital Budgeting (Investment ) Decisions (a) Estimate NPV, IRR and Payback period of the project if lax Tale equals to 2195. Would you accept or reject the project? (b) b As a Cro of the firm, which of the above two scenarios (1) or (2) would you choose? Why? Q#3 How would you explain to your CEO what NPV means? Year Cummulative CF Count $ 0 1 2 3 Projected CF $ $ $ $ $ Payback period Present Vale Discount factor Discounted CF 1 SO SO SO SO SO $ $ $ $ 4 0.00 years Q#4 What are advantages and disadvantages of using only Payback method? O#5 What are advantages and disadvantages of using NPV versus IRR? # 1/(1+r)^n Q#6 Explain the difference between independent projects and mutually exclusive projects. When you are confrontext with Mutually Exclusive: Projects and have coflicts with NPV and IRR results, which criterion would you use (NPV or IRR) and why? for a First complete this spreadsheet Then night click on the tab Select Monos C Check the Create a Copybox in the lower left corner of the plot me Click "OK" a copy of the completed spreadsheet will be created. You can proceed to change the tax rate to 21% and adut the payback period: Adjust salvage value after tax You huse there were ques