G PROJECT DUE MAY 10TH D Learning Objectives 1. Understand how to use EXCEL Spreadsheet 20 (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 13 and make long-term investment decisions 10 151) Lite Period of the Equipment 4 years 8) Sales for first year (1) 102) New equipment cost (5200,000) 9) Sales increase per year 173) Equipment ship & Install cost ($35,000) 10) Operating cost (60% of Sales) 184) Related start up cost (55.000) (as a percent of sales in Year 1) 106) Inventory Increase 325,000 11) Depreciation 206) Accounts Payable increase 55,000 12) Marginal Corporate Tax Rate (T) 217) Equip. salvage value before tax $15,000 13) Cost of Capital (Discount Rate) $ 200,000 8% $ (120,000) 60% Use Syr MACRIS 354 10% only Do not write in any other cell 23 Filling data in the cells colored Im Care and Template arigns Sheet1 Shes do A 10-AA G Conditioning Farmata Table 16 Paste B 12 A F3 One aromat C Sunda PS Mary OM Und Gebord EBIT Sales - Costs -D $ 26 Year Investments 201) Equipment cost 312) Shipping and Install cost 123) Start up expenses Total Basis Cost (1+2+3) 344) Net Working Capital 38 Total Initial Outlay 30 ST Operations 30 Sales 5 Operating Cost negative Depreciation EBIT Taxes Net income rutructions Sheet Case Template 14 e Map za Arla 10 - AA = A. Gered 49 Conditions forming lomas Cases les D- To Sot A fed Sed Duta Cylic Alg D Terminal values 501) Change in net WC 512) Salvage value (after tax) 12 Total $ $ 54 Project Net Cash Flows 55 56 NPV 50.00 IRRE MNUM Payback 0.00 Discounted Payback 0.00 sa profitability Index = OIVIO PLEASE RESPOND TO THESE QUESTIONS ON ANOTHER TAB Candle Sel Sheet Cell Styles - Styles Format Calle Sort & Find -- Select 15 63 67 *8.99 Undo Clipboard Font Algement Number fx B C D PLEASE RESPOND TO THESE QUESTIONS ON ANOTHER TAB 64 5 #1 Would you accept the project based on NPV, IRR? 66 Would you accept the project based on Payback rule if project cut-off is 3 years? 50 Q82 Impact of 2017 Tax Cut Act on Net Income, Cash Flows and 60 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? 75 (b) As a CFO of the firm, which of the above two scenarios (1) or (2) would you choose? Why? 75 Q#3 How would you explain to your CEO what NPV means? 78 77 004 What are advantages and disadvantages of using only Payback method? TO 79 #5 What are advantages and disadvantages of using NPV versus IRR? 74 80 Instruction Case and Template Response Sheet1 Sheet3 He Accessibility wigate L M N O Year 3-year 20-year 33.33% 44.45 14.81 7.41 Depreciation rate for recovery period 5-year 7-year 10-year 15-year 20.00% 14.29% 10.00% 5.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 OAN 3.75096 7.219 6.677 6.177 5.713 5.76 8.92 8.93 4.46 7.37 6.55 6.55 6.58 6.55 6.23 5.90 5.90 5.91 5.90 5.285 4.888 4.522 4.462 4.461 3.28 | & = o p 40 13 14 15 5.91 5.90 5.91 5.90 5.91 4.462 4.461 4.462 4.461 4.462 16 17 18 2.95 4.461 4.462 4.461 4.462 4.461 20 21 2.231 Case and Template Response Sheet1 Sheet3 K M o o Depreciation Calculation $ Depreciation Basis: # of years: Macrs 4 3 years Year Basis Macrs % 1 2. 3 4 $ $ $ A'B Depreciation $0 $0 $0 $0 0 0% Sheet3 Instructions Case and Template Response Sheet1 ccessibility: Investigate Corred G o THE Number Styles LS Cells M N O Q R Salvage value"(1 - marginal tax rate) salvage value - "Salvage value - book value) Payback Period Year 57 58 59 30 Count NO 1 2 3 4 Projected CF Cummutative CF S $ S S S S S Payback period 52 53 64 65 0.00 years 66 Sheet1 Sheet3 Instructions Case and Template Response Accessibility Investigate Ready P M o Payback period N 0.00 years Discounted Payback Period 54 65 66 67 68 69 70 71 72 73 74 Year. n Cummutative CF Count NO Present Value Discount factor Discounted CF SO $0 $O $0 SO Projected CF S $ $ $ S Payback period BU U U U $ 2 75 4 0.00 years 76 77 78 79 1/(1+r)^n 80 31 Response Sheet1 Sheet3 Instructions Case and Template Dk Accessibility Investigate Ready O 4 PLEASE RESPOND TO THESE QUESTIONS ON ANOTHER TAB 5 6 Q#1 7 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? 8 9 13 10 Q#2 Impact of 2017 Tax Cut Act on Net Income, Cash Flows and 11 Capital Budgeting (Investment ) Decisions 12 (a) Estimate NPV, IRR and Payback Period of the project if tax rate equals to 21%. Would you accept or reject the project? 15 (b) As a CFO of the firm, which of the above two scenario (1) or (2) 16 would you choose? Why? 17 Q#3 How would you explain to your CEO what NPV means? 14 18 19 Q#4 What are advantages and disadvantages of using only Payback method? 20 21 Q#5 What are advantages and disadvantages of using NPV versus IRR? 22 23 Q#6 Explain the difference between independent projects and mutually exclusive projects. When you are confronted with Mutually Exclusive Projects and have coflicts 25 with NPV and IRR results, which criterion would you use (NPV or IRR) and why? 24 26 27 28 an