Can You help solve MACRS and the pay back period and depreciation calculation, the small boxes on the right? thank you
Capital Budgeting Decisions CASE STUDY FINC 3310 - FALL 2019 MACRS TABLE Learning Objectives
1. Understand how to use EXCEL Spreadsheet (a)Develop proforma Income Statement Using Excel Spreadsheet (b)ComputeNet Project Cashflows, NPV,and IRR
(c) Develop problem-solving andcritical thinking skills and make long-term investment decisions 1) Life Period of the Equipment = 4 years
8) Sales for first year (1) $200,000
2) New equipment cost ($200,000) 9) Sales increase per year 5%
3) Equipment ship & install cost ($35,000) 10) Operating cost (60% of Sales) $(120,000)
4) Related start up cost ($5,000) (as a percent of sales in Year 1) -60%
5) Inventory increase $25,000 11) Depreciation Use 3-yr MACRIS
6) Accounts Payable increase $5,000 12) Marginal Corporate Tax Rate (T) 35%
7) Equip. salvage value before tax $15,000 13) Cost of Capital (Discount Rate) 10% Filling data in the cells colored only. ESTIMATINGInitial Outlay (Cash Flow, CFo, T= 0)
Capital Budgeting Decisions CASE STUDY FINC 3310 - FALL 2019 MACRS TABLE earning Objectives Understand how to use EXCEL Spreadsheet Year Depreciation rate for recovery period Develop proforma Income Statement Using Excel Spreadsheet 3-year 5-year 7-year 10-year 15-year 20-year b) Compute Net Project Cashflows, NPV, and IRR 33.33% 44:45 20.00% 14.29% 10.00% 5.00% .750% 14 81 32.0 24.49 18.00 9.50 7.219 c) Develop problem-solving and critical thinking skills 19.20 17.49 14.40 8.55 7.41 11.52 12.49 11.52 7.70 6.177 and make long-term investment decisions 11.52 8.93 9.22 6.93 5.712 5.76 8.92 7.37 5.285 8.93 5.90 4.888 Life Period of the Equipment = 4 years 8) Sales for first year (1) $ 200,000 4.46 6.55 6.55 6.56 5.90 4.522 ($200,000) 6.55 5.91 4.462 New equipment cost 9) Sales increase per year 5% 5.90 4.461 Equipment ship & install cost ($35,000) 10) Operating cost (60% of Sales) $ (120,000) 3.28 5.91 4.462 Related start up cost $5,000) (as a percent of sales in Year 1) 60% 5.90 5.91 4.461 Inventory increase $25,000 5.90 4.462 11) Depreciation Use 3-yr MACRIS Accounts Payable increase 12) Marginal Corporate Tax Rate (T) 5.91 $5,00 35% Equip. salvage value before tax $15,000 13) Cost of Capital (Discount Rate) 10% 2.95 4.46 4.462 4.461 4.462 Filling data in the cells colored only 4.461 2.231 STIMATING Initial Outlay (Cash Flow, CFo, T= 0) CFO CF1 CF2 CF3 CF4 ear 2 A vestments: Equipment cost (200,000) Shipping and Install cost 35,000) Start up expenses (5,000) Total Basis Cost (1+2+3) (240,000) Net Working Capital (20.000) Total Initial Outlay $ 260,000) Depreciation Calculation perations: evenue SAKA SAKA CA CA 200,000 $ 210,000 $ 220,500 $ 231,525 Depreciation Basis: $ 240,000 perating Cost (120,000) $ KA E (126,000) $ (132,300) SAKAE (138,915) # of years: 4 epreciation Macrs 3 years EBIT 80,000 $ 84,000 $ 88,200 92,610 axes 28.000 $ 29.400 $ 30.870 32.414 B A*B Net Income 52,000 54,600 57,330 60, 197 Year Basis Macrs % Depreciation $0 dd back Depreciation $ $ $ $ $0 $0 Total Operating Cash Flow $ 52,000 $ 54,600 $ 57,330 $ 60, 197 AWN erminal values: Change in net WC Salvage value (after tax) KA EA 20,000 15.000 Total 35,000 Salvage value*(1 - marginal tax rate)Revenue SAKA SAKA CA CA 200,000 $ 210,000 $ 220,500 $ 231,525 Depreciation Basis: $ 240,000 Operating Cost (120,000) $ KA (126,000) $ (132,300) $ KA E (138,915) # of years: Depreciation Macrs 3 years EBIT 80,000 $ 84,000 88,200 92,610 Taxes 28.000 29.400 30.870 32.414 B A*B Net Income 52,000 54,600 $ 57,330 60, 197 Year Basis Macrs % Depreciation $0 Add back Depreciation $ $ $0 $0 Total Operating Cash Flow $ 52,000 $ 54,600 $ 57,330 $ 60, 197 A W N . $0 Terminal values: 1) Change in net WC 20,000 2) Salvage value (after tax) 15.000 Total 35,000 Salvage value*(1 - marginal tax rate) Project Net Cash Flows (260,000) $ 52,000 $ 54,600 57,330 $ 95, 197 NPV = ($59 509 94) IRR = -0 1220% Payback= 0.00 Payback Period Profitability Index = 0.77 Discounted Payback = 0.00 Year Projected CF Cummulative CF Count 0 W N - $ (260,000) $ 260,000) 52,000 $ (208,000) 54,600 $ (153,400) 57,330 $ (96,070) PLEASE RESPOND TO THESE QUESTIONS ON ANOTHER TAB 95, 197 $ (874) Payback period years 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 Discounted Payback Period Capital Budgeting (Investment ) Decisions (a) Estimate NPV, IRR and Payback Period of the project if Year Projected CF Discount factor Discounted CF Cummulative CF Count tax rate equals to 21%. Would you 0 (260,000) accept or reject the project? 52,000 $0 (b) As a CFO of the firm, which of the above two scenario (1) or (2) 54,600 $0 would you choose? Why? 57,330 $0 Q#3 How would you explain to your CEO what NPV means? W N - 95, 197 Payback period years Q#4 What are advantages and disadvantages of using only Payback method? #5 What are advantages and disadvantages of using NPV versus IRR
