Student Name: DATE: Instructor: Tunda Aberyada-Cola Capital Budgeting Decisions CASE STUDY FINC 3310 - FALL 2020 MACRTABLE Learning Objectives 3-year 1 2 44.45 14: Depreciation for record 9-year 10-yre 90.00 32.00 24449 00 19.20 1.30 17.49 3.55 11 32 12.49 11.32 7.70 11 52 7.219 BTT 3 5718 2.70 33 AS 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 8) Sales for first year (1) 2) New equipment cost ($200,000) 9) Sales increase per year 3) Equipment ship & Install cost ($35,000) 10) Operating cost (80% of Sales) 4) Related start up cost ($5,000) (as a percent of sales in Year 1) 5) Inventory increase $25,000 11) Depreciation 6) Accounts Payable increase $5,000 12) Marginal Corporate Tax Rate (T) 7) Equip. salvage value before tax $15,000 13) Cost of Capital (Discount Rate) NO 2,48 61 54 G. G . 4.5 4.410 0.001 10 $ 200,000 5% $ (120,000) -60% Use 3-yr MACRIS 35% 10% 11 12 13 14 13 S.DI 600 5.DI 5.00 5.DI 4.400 4.401 4.462 4,401 4.4 16 2.DS 4.461 4. 4.461 4.462 Filling data in the cells colored only. Do not write in any other coll. not delete any row or column 21 ESTIMATING Initial Outlay (Cash Flow, CFO, T=0) -70 CF3 CFO 0 O CF1 1 CF2 2 CF4 4 EBIT -Salee - Costs - D Year Investments: 1 1) Equipment cost 2) Shipping and Instal cost 3) Start up expenses Total Basis Cost(1+2+3) 4) Net Working Capital Total Initial Outlay $ s $ ($200,000) (35.000) (5,000) (240,000) (20,000) (260,000) $ Depreciation Calculation S 200,000 (120,000) negative S S Operations: Sales Operating cost Depreciation EBIT Taxes Net Income $ $ $ $ S $ Depreciation Basis: # of years: Macrs 240.000 4 3 years 3 S 90,000S 20,000 S 52,000 S S S S Year Basis Macrs % Add back Depreciation S 1 2 3 4 Depreciation $0 $0 SO $0 Total Operating Cash Flow $ 52,000 $ CFO CF3 CF1 1 CF2 2 CF4 4 EDIT -Sales-Costs - D Year Investments: 1) Equipirent cost 21 Shipping and Install cost 2) Start up expersus Tatal Basis Cast (1+2+3) 4) Net Working Capital Total Initial Oulay $ $ $ $ 3 $ IS200.000 135,000 (5,000) (240,000) (20,000) (260,000) Depreciation Calculation $ 240.000 4 negative Operations: Sales Operating Cost Depreciation | EBIT Taxas Net Income Depreciation Basis: # of years: Macrs 200,000 (120,000) $ 80,000 $ 28.000 52,000 $ S S IS S S S S 3 years Year Basis Macrs % And back Depreciation S A'n Depreciation $0 $0 $0 $0 1 2 3 4 Total Operating Cash Flow S 52,000 $ 0% Terminal values: 1 Change in net WC 2) Salvage value (after tax) Tatal $ . Salvage valuc (1 - marginal tax rate) Project Net Cash Flows $ (260,000) $ 52,000 $ $ NPY (S212 727.27) IRR - -80.00000 Payback 0.00 Payback Period Profitability Index = 0.18 Discounted Payback = 0.00 Yest Court 0 1 2 3 4 Projected CF Cumulative CF $ (250,00015 (250,000) $ 52,000 $ 3 (2080001 $ 3 (208.000 $ S 3 (208.000 $ 3 (200 000 Payback period PLEASE RESPOND TO THESE QUESTIONS ON ANOTHER TAB 0.00 years ON1 Would you accept the project based on NPV, IRR? Would you accept tha miect hasad an Pavhack nila il miect at all 3 4 $ $ PLEASE RESPOND TO THESE QUESTIONS ON ANOTHER TAB $ (208,000) $ (208,000) Payback period 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 tax rate equals to 21%. Would you accept or reject the project? b) As a CFO 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 Present Value Discount factor 1 Count Projected CF (260,000) 52,000 0 1 2 3 Discounted CF ($260,000) $0 $0 $0 $0 Cummulative CF (260,000) $ (260,000) $ (260,000) $ (260,000) (260,000) 4 Payback period 0.00 years Q#4 What are advantages and disadvantages of using only Payback method? Q#5 What are advantages and disadvantages of using NPV versus IRR? Q#6 Explain the difference between independent projects and mutually exclusive projects. When you are confronted with Mutually Exclusive Projects and have coflicts with NPV and IRR results, which criterion would you use (NPV or IRR) and why? For 02 First complete this spreadsheet; Then right-click on the tab Select 'Move or Copy' Check the 'Create a Copy' box in the lower-left corner of the pull-out menu Click "OK" a copy of the completed spreadsheet will be created. You can proceed to change the tax rate to 21% and adjust the payback periods Adjust salvage value after tax You should use the results to answer the questions PLEASE RESPOND TO THESE QUESTIONS ON ANOTHER TAB 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 tax rate equals to 21%. Would you accept or reject the project? (b) As a CFO of the firm, which of the above two scenario (1) or (2) would you choose? Why? Q#3 How would you explain to your CEO what NPV means? Q#4 What are advantages and disadvantages of using only Payback method? Q#5 What are advantages and disadvantages of using NPV versus IRR? Q#6 Explain the difference between independent projects and mutually exclusive projects. When you are confronted with Mutually Exclusive Projects and have coflicts with NPV and IRR results, which criterion would you use (NPV or IRR) and why? MACRS TABLE 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 4 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.462 4.461 3.28 11 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 Student Name: DATE: Instructor: Tunda Aberyada-Cola Capital Budgeting Decisions CASE STUDY FINC 3310 - FALL 2020 MACRTABLE Learning Objectives 3-year 1 2 44.45 14: Depreciation for record 9-year 10-yre 90.00 32.00 24449 00 19.20 1.30 17.49 3.55 11 32 12.49 11.32 7.70 11 52 7.219 BTT 3 5718 2.70 33 AS 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 8) Sales for first year (1) 2) New equipment cost ($200,000) 9) Sales increase per year 3) Equipment ship & Install cost ($35,000) 10) Operating cost (80% of Sales) 4) Related start up cost ($5,000) (as a percent of sales in Year 1) 5) Inventory increase $25,000 11) Depreciation 6) Accounts Payable increase $5,000 12) Marginal Corporate Tax Rate (T) 7) Equip. salvage value before tax $15,000 13) Cost of Capital (Discount Rate) NO 2,48 61 54 G. G . 4.5 4.410 0.001 10 $ 200,000 5% $ (120,000) -60% Use 3-yr MACRIS 35% 10% 11 12 13 14 13 S.DI 600 5.DI 5.00 5.DI 4.400 4.401 4.462 4,401 4.4 16 2.DS 4.461 4. 4.461 4.462 Filling data in the cells colored only. Do not write in any other coll. not delete any row or column 21 ESTIMATING Initial Outlay (Cash Flow, CFO, T=0) -70 CF3 CFO 0 O CF1 1 CF2 2 CF4 4 EBIT -Salee - Costs - D Year Investments: 1 1) Equipment cost 2) Shipping and Instal cost 3) Start up expenses Total Basis Cost(1+2+3) 4) Net Working Capital Total Initial Outlay $ s $ ($200,000) (35.000) (5,000) (240,000) (20,000) (260,000) $ Depreciation Calculation S 200,000 (120,000) negative S S Operations: Sales Operating cost Depreciation EBIT Taxes Net Income $ $ $ $ S $ Depreciation Basis: # of years: Macrs 240.000 4 3 years 3 S 90,000S 20,000 S 52,000 S S S S Year Basis Macrs % Add back Depreciation S 1 2 3 4 Depreciation $0 $0 SO $0 Total Operating Cash Flow $ 52,000 $ CFO CF3 CF1 1 CF2 2 CF4 4 EDIT -Sales-Costs - D Year Investments: 1) Equipirent cost 21 Shipping and Install cost 2) Start up expersus Tatal Basis Cast (1+2+3) 4) Net Working Capital Total Initial Oulay $ $ $ $ 3 $ IS200.000 135,000 (5,000) (240,000) (20,000) (260,000) Depreciation Calculation $ 240.000 4 negative Operations: Sales Operating Cost Depreciation | EBIT Taxas Net Income Depreciation Basis: # of years: Macrs 200,000 (120,000) $ 80,000 $ 28.000 52,000 $ S S IS S S S S 3 years Year Basis Macrs % And back Depreciation S A'n Depreciation $0 $0 $0 $0 1 2 3 4 Total Operating Cash Flow S 52,000 $ 0% Terminal values: 1 Change in net WC 2) Salvage value (after tax) Tatal $ . Salvage valuc (1 - marginal tax rate) Project Net Cash Flows $ (260,000) $ 52,000 $ $ NPY (S212 727.27) IRR - -80.00000 Payback 0.00 Payback Period Profitability Index = 0.18 Discounted Payback = 0.00 Yest Court 0 1 2 3 4 Projected CF Cumulative CF $ (250,00015 (250,000) $ 52,000 $ 3 (2080001 $ 3 (208.000 $ S 3 (208.000 $ 3 (200 000 Payback period PLEASE RESPOND TO THESE QUESTIONS ON ANOTHER TAB 0.00 years ON1 Would you accept the project based on NPV, IRR? Would you accept tha miect hasad an Pavhack nila il miect at all 3 4 $ $ PLEASE RESPOND TO THESE QUESTIONS ON ANOTHER TAB $ (208,000) $ (208,000) Payback period 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 tax rate equals to 21%. Would you accept or reject the project? b) As a CFO 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 Present Value Discount factor 1 Count Projected CF (260,000) 52,000 0 1 2 3 Discounted CF ($260,000) $0 $0 $0 $0 Cummulative CF (260,000) $ (260,000) $ (260,000) $ (260,000) (260,000) 4 Payback period 0.00 years Q#4 What are advantages and disadvantages of using only Payback method? Q#5 What are advantages and disadvantages of using NPV versus IRR? Q#6 Explain the difference between independent projects and mutually exclusive projects. When you are confronted with Mutually Exclusive Projects and have coflicts with NPV and IRR results, which criterion would you use (NPV or IRR) and why? For 02 First complete this spreadsheet; Then right-click on the tab Select 'Move or Copy' Check the 'Create a Copy' box in the lower-left corner of the pull-out menu Click "OK" a copy of the completed spreadsheet will be created. You can proceed to change the tax rate to 21% and adjust the payback periods Adjust salvage value after tax You should use the results to answer the questions PLEASE RESPOND TO THESE QUESTIONS ON ANOTHER TAB 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 tax rate equals to 21%. Would you accept or reject the project? (b) As a CFO of the firm, which of the above two scenario (1) or (2) would you choose? Why? Q#3 How would you explain to your CEO what NPV means? Q#4 What are advantages and disadvantages of using only Payback method? Q#5 What are advantages and disadvantages of using NPV versus IRR? Q#6 Explain the difference between independent projects and mutually exclusive projects. When you are confronted with Mutually Exclusive Projects and have coflicts with NPV and IRR results, which criterion would you use (NPV or IRR) and why? MACRS TABLE 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 4 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.462 4.461 3.28 11 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