D30 G H J K L M N 0 P Q R S T U 7 Learning Objectives Year 3-year 20-year Depreciation rate for recovery period 5-year 7-year 10-year 15-year 20.00% 32.00 24.49 18.00 9.50 19.20 17.49 14.40 8.55 11.52 12:49 11.52 7.70 11.52 8.93 9.22 6.93 14.29% 10.00% 5.00% 33.33% 44.45 14.81 7.41 11 3.750% 7.219 6.677 6.177 5.713 5.76 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.592 4.462 4.461 11 3.28 $ 200.000 5% $ (120,000) -60% Use 3-yr MACRIS 35% 10% 5.91 5.90 5.91 5.90 591 4.462 4.461 4.462 4.461 4.462 15 2.95 18 4.461 4.462 4.481 4.462 4.461 20 21 2.231 9 1. Understand how to use EXCEL Spreadsheet 10 (a) Develop proforma Income Statement Using Excel Spreadsheet (b) Compute Net Project Cashflows, NPV, and IRR 12 (c) Develop problem-solving and critical thinking skills 13 and make long-term investment decisions 14 15 1) Life Period of the Equipment = 4 years 8) Sales for first year (1) 16 2) New equipment cost ($200,000) 9) Sales increase per year 17 3) Equipment ship & install cost ($35,000) 10) Operating cost (60% of Sales) 18 4) Related start up cost ($5,000) (as a percent of sales in Year 1) 19 5) Inventory increase $25,000 11) Depreciation 20 6) Accounts Payable increase $5,000 12) Marginal Corporate Tax Rate (T) 21 7) Equip. salvage value before tax $15,000 13) Cost of Capital (Discount Rate) 22 23 Filling data in the cells colored only. Do not write in any other cell. 24 Do not delete any row or column 25 ESTIMATING Initial Outlay (Cash Flow, CFO, T=0) 26 -70 CFO CF1 CF2 CF3 28 Year 0 1 2 3 29 Investments: 30 1) Equipment cost 31 2) Shipping and Install cost EBIT = Sales - Costs - D 32 3) Start up expenses 33 Total Basis Cost (1+2+3) $ 34 4) Net Working Capital Total Initial Outlay $ 36 37 Operations: 38 Sales 39 Operating Cost negative $ 40 Depreciation 41 EBIT $ $ 42 Taxes $ $ $ 43 Net Income $ $ 27 CF4 4 35 Depreciation Calculation $ Depreciation Basis: # of years: Macrs 4 3 years $ Year B Macrs % Basis 44 AB Depreciation $0 $0 $0 $0 45 Add back Depreciation 1 2 3 $ 45 47 Total Operating Cash Flow $ $ $ 4 48 0% 49 Terminal values: 50 1) Change in net WC 512) Salvage value (after tax) > Case and Template Response Sheet3 Sheet1 (2) Sheet1 + H 1 J K L M N o P Q R S $ Salvage value*(1 - marginal tax rate) $ - 0.00 Payback Period 0.00 Year Count 0 1 2 Projected CF Cummulative CF $ $ $ $ $ $ $ $ $ $ Payback period 3 4 0.00 years Discounted Payback Period D30 Xfx A B E F G 49 Terminal values: 50 1) Change in net WC 51 2) Salvage value (after tax) 52 Total 53 54 Project Net Cash Flows $ $ 55 56 NPV = $0.00 IRR = #NUM! Payback= 57 58 Profitability Index = #DIV/0! Discounted Payback = 59 60 61 62 63 PLEASE RESPOND TO THESE QUESTIONS ON ANOTHER TAB 64 65 Q#1 Would you accept the project based on NPV, IRR? 66 Would you accept the project based on Payback rule if project cut-off 67 is 3 years? 68 Q#2 Impact of 2017 Tax Cut Act on Net Income, Cash Flows and 69 Capital Budgeting (Investment ) Decisions 70 (a) Estimate NPV, IRR and Payback period of the project if 71 tax rate equals to 21%. Would you 72 accept or reject the project? 73 (b) As a CFO of the firm, which of the above two scenarios (1) or (2) 74 would you choose? Why? 75 Q#3 How would you explain to your CEO what NPV means? 76 77 Q#4 What are advantages and disadvantages of using only Payback method? 78 79 Q#5 What are advantages and disadvantages of using NPV versus IRR? 80 81 Q#6 Explain the difference between independent projects and mutually exclusive projects. 82 When you are confronted with Mutually Exclusive Projects and have coflicts 83 with NPV and IRR results, which criterion would you use (NPV or IRR) and why? 84 85 For Q2. 86 First complete this spreadsheet; 87 Then right-click on the tab 88 Select Move or Copy 89 Check the 'Create a Copy' box in the lower-left corner of the pull-out menu 90 Click "OK" a copy of the completed spreadsheet will be created. 92 You can proceed to change the tax rate to 21% and adjust the payback periods 93 Adjust salvage value after tax 94 You should use the results to answer the questions Case and Template Response Sheet3 Sheet1 (2) Sheet1 Year Count 0 1 Present Value Discount factor Discounted CF 1 $0 $0 $0 $0 $0 Projected CF $ $ $ S S Payback period Cummulative CF $ $ $ $ $ 2 3 4 0.00 years 1/(1+r)^n 91 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