Please help!!
G X L M N D P Q R S 5 T V W Year 13 18 ya per Toys BAN SMC 14 4445 12.00 45 61 T41 11 13.4 11.08 3. 650 6.35 9.30 119 1000 11.5 315 822 HEN 45 4 9.30 4491 4432 4421 4432 4431 44 3. 41 14 241 4493 4411 151 A B C D E H H 1 7 Learning Objectives PROJECT DUE MAY 10TH 8 9 1. Understand how to use EXCEL Spreadsheet 10 (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 14 15 1) Lite Period of the Equipment 4 years a Sales for first year (1) $ 200,000 16 2) New equipment cost (5200.000) 91 Sales ingroase per year 5% 17 3) Equipment ship & Install cost ($35,000) 101 Operating cost (60% of Sales) $ (120,000) 18 4 Related start up cost ($5,000 as a percent of sales in Year 1) -60% 19 51 Inventory increase $25,000 11) Depreciation Use 3-yr MACRIS 206) Accounts Payable increase $5,000 12Marginal Corporate Tax Rate (1) 35% 217) Equip, salvage value before the $15.000 13) Cost of Capital (Discount Rate) 10% 32 23 Fwing detail the cells colored only. Do not wie in any other co Do not delete any row or column 75 ESTIMATING Initial Outlay (Cash Flow, CFO, T-0) 26 -70 CFO CF1 CF2 CF3 CF4 28 Year 1 2 4 29 Investments 301) Equipment cost (200.000 21 2) Shipping and Instal cos! 135.000 EBIT - Sales - Costs -D 32 3) Start up expenses (5.000 33 Total Basis Cost (1+2+3) 1+) (240,000) 24 4) Net Working Capital 5 120.000) 35 Total Intial Outlay $ (260.000) 36 27 Operations: 200,000 $ 210,000 $ 220,500 $ 231,525 29 Operating cost negative (120,000) $ (126,000) S (132,300) $ (138.915) 40 Depreciation 179.992) S (105.680) S (35,544) $ (17.784) EBIT $ 9 $ 22,690) $ 52,656 $ 74,826 ( 42 Taxes 5 35 (7.938) S 18.430 $28.189 [) 43 Net Income $ 5 S (14,742) S S 34,228 $ 48,637 $ 44 15 Add back Depreciation $ 79.992 S 106.680 S 36,544 $17.784 . S 46 47 Total Operating Cash Flow $ 79.992 S 91.938 S 69.770 $68,421 . 48 49 Terminal values: 201) Change in net WC S0 11 Charoe 5 $ 20,000 51 2) Salvage value (after tax) Total $ 20,000 53 54 Project Net Cash Flows $ (260,000) $ 79,997 $ 91,938 $ 69,770 $ 86,421 $ Depreciation Calculation 38 Sales Depreciation Basis: # of years: Macre $ 240,000 4 3 years Year 1 2 3 4 Basis $ 240,000 $ 240,000 $ 240,000 $ 240,000 Macrs % 33.33% 44.45% 14.81% 7.41% 100% A'R Depreciation $ 79,992 $106,680 $35,544 $17,784 240,000 Salvage value"(1 - marginal tax rate) salvage value - T(Salvage value - book value) Project Net Cash Flows $ (260,000 $ 79,997 $ 91,938 $ 69,770 $ 86,421 NPV = $152.72 IRR = 10.0272% Payback= 2.69 Payback Period Profitability Index = 1.00 Discounted Payback = 2.69 Year Count 0 1 2 3 4 Projected CF Cummulative CF $ (260,000) $ (260,000) $ 79,997 $ (180,003) $ 91,938 $ (88,065) $ 69,770 $ (18,294) $ 86,421 $ 68,127 Payback period 1 1 0.69 PLEASE RESPOND TO THESE QUESTIONS ON ANOTHER TAB 2.69 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 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, n Discounted Payback Period Present Value Projected CF Discount factor Discounted CF Cummulative CF Count 0 $ (260,000) 1 ($260,000 $ (260,000) 1 $ 79,997 0.9091 $72,725 $ (187,275) 1 2 $ 91,938 0.8264 $75,978 $ (111,297) 1 3 $ 69,770 0.7513 $52,419 $ (58,878) 0.69 4 $ 86,421 0.6830 $59,025 $ 147 Payback period 2.69 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? 1/(1+r)^n 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 Q2. 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 n G X L M N D P Q R S 5 T V W Year 13 18 ya per Toys BAN SMC 14 4445 12.00 45 61 T41 11 13.4 11.08 3. 650 6.35 9.30 119 1000 11.5 315 822 HEN 45 4 9.30 4491 4432 4421 4432 4431 44 3. 41 14 241 4493 4411 151 A B C D E H H 1 7 Learning Objectives PROJECT DUE MAY 10TH 8 9 1. Understand how to use EXCEL Spreadsheet 10 (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 14 15 1) Lite Period of the Equipment 4 years a Sales for first year (1) $ 200,000 16 2) New equipment cost (5200.000) 91 Sales ingroase per year 5% 17 3) Equipment ship & Install cost ($35,000) 101 Operating cost (60% of Sales) $ (120,000) 18 4 Related start up cost ($5,000 as a percent of sales in Year 1) -60% 19 51 Inventory increase $25,000 11) Depreciation Use 3-yr MACRIS 206) Accounts Payable increase $5,000 12Marginal Corporate Tax Rate (1) 35% 217) Equip, salvage value before the $15.000 13) Cost of Capital (Discount Rate) 10% 32 23 Fwing detail the cells colored only. Do not wie in any other co Do not delete any row or column 75 ESTIMATING Initial Outlay (Cash Flow, CFO, T-0) 26 -70 CFO CF1 CF2 CF3 CF4 28 Year 1 2 4 29 Investments 301) Equipment cost (200.000 21 2) Shipping and Instal cos! 135.000 EBIT - Sales - Costs -D 32 3) Start up expenses (5.000 33 Total Basis Cost (1+2+3) 1+) (240,000) 24 4) Net Working Capital 5 120.000) 35 Total Intial Outlay $ (260.000) 36 27 Operations: 200,000 $ 210,000 $ 220,500 $ 231,525 29 Operating cost negative (120,000) $ (126,000) S (132,300) $ (138.915) 40 Depreciation 179.992) S (105.680) S (35,544) $ (17.784) EBIT $ 9 $ 22,690) $ 52,656 $ 74,826 ( 42 Taxes 5 35 (7.938) S 18.430 $28.189 [) 43 Net Income $ 5 S (14,742) S S 34,228 $ 48,637 $ 44 15 Add back Depreciation $ 79.992 S 106.680 S 36,544 $17.784 . S 46 47 Total Operating Cash Flow $ 79.992 S 91.938 S 69.770 $68,421 . 48 49 Terminal values: 201) Change in net WC S0 11 Charoe 5 $ 20,000 51 2) Salvage value (after tax) Total $ 20,000 53 54 Project Net Cash Flows $ (260,000) $ 79,997 $ 91,938 $ 69,770 $ 86,421 $ Depreciation Calculation 38 Sales Depreciation Basis: # of years: Macre $ 240,000 4 3 years Year 1 2 3 4 Basis $ 240,000 $ 240,000 $ 240,000 $ 240,000 Macrs % 33.33% 44.45% 14.81% 7.41% 100% A'R Depreciation $ 79,992 $106,680 $35,544 $17,784 240,000 Salvage value"(1 - marginal tax rate) salvage value - T(Salvage value - book value) Project Net Cash Flows $ (260,000 $ 79,997 $ 91,938 $ 69,770 $ 86,421 NPV = $152.72 IRR = 10.0272% Payback= 2.69 Payback Period Profitability Index = 1.00 Discounted Payback = 2.69 Year Count 0 1 2 3 4 Projected CF Cummulative CF $ (260,000) $ (260,000) $ 79,997 $ (180,003) $ 91,938 $ (88,065) $ 69,770 $ (18,294) $ 86,421 $ 68,127 Payback period 1 1 0.69 PLEASE RESPOND TO THESE QUESTIONS ON ANOTHER TAB 2.69 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 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, n Discounted Payback Period Present Value Projected CF Discount factor Discounted CF Cummulative CF Count 0 $ (260,000) 1 ($260,000 $ (260,000) 1 $ 79,997 0.9091 $72,725 $ (187,275) 1 2 $ 91,938 0.8264 $75,978 $ (111,297) 1 3 $ 69,770 0.7513 $52,419 $ (58,878) 0.69 4 $ 86,421 0.6830 $59,025 $ 147 Payback period 2.69 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? 1/(1+r)^n 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 Q2. 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 n
