hhhnb
You can Zoom in to see it more clear, I need the answer to yellow part, especially the count ?
Finance Project - Excel A - Insert Formulas Data Review View Help Search Share Comments File AutoSave R31 Home Of H Page Layout e rz : * fic G H J K 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 77 $200,000 5% 0.56 15 1) Life Period of the Equipment = 4 years 16 2) Ney equipment cost 17 3) Equipment ship & install cost 18 4) Related start up cost 19 5) Inventory increase 20 6) Accounts Payable increase 21 7) Equip. salvage value before tax ($200,000) ($35,000) ($5,000) $25,000 $5,000 $15,000 8) Sales for first year (1) 9) Sales increase per year 10) Operating cost (60% of Sales) ! (as a percent of sales in Year 1) 11) Depreciation 12) Marginal Corporate Tax Rate (T) 13) Cost of Capital (Discount Rate) -607 Use 3-yr MACRIS 35% 10% 591 205 Filling data in the cells colored' only. Do not rrite in other cell Do not delete any por or columns -70 CF1CF2CF3CF4 24 1 2 3 25 ESTIMATING Initial Outlay (Cash Flor, CFO, T= 0) 26 CFO 28 Year O 29 Investments: 301) Equipment cost 312) Shipping and Install cost 32 3) Start up expenses 33 Total Basis Cost (1+2+3) $ 34 4) Net Working Capital 35 Total Initial Outlay $ (200,000) (35,000) (5,000) (240,000) (20,000) (260,000) Depreciation Calculation $ 240,000 Depreciation Basis: # of years: Macrs 3 years 37 Operations: 38 Sales 39 Operating Cost negative 40 Depreciation 41 EBIT 42 Taxes 43 Net Income 44 45 Add back Depreciation 200,000 $ (120,000) S 45,000 $ 35,000 $ 12.250 $ 22,750 $ 210,000 $ 220,500 $ 231,525 (126,000) S (132,300) $ (138,915) 24,000 $ 54.000 $ 30.000 60,000 $ 34,200 $ 62,610 21,000 $ 11.970 $ 21.914 39,000 $ 22,230 $ 40,697 ( Year Basis $ 300,000 $ 300,000 Macrs % 15.00% 8.00% Depreciation $45,000 $24,000 45,000 $ 24,000 $ 54,000 $ 30,000 Case and Template Response Sheet3| @ 8:38 PM a J - . O l 1) 5/2/2020 Type here to search A 22 Finance Project - Excel A - Page Layout Formulas Data Review View Help Search Share Comments File AutoSave Home Insert Of H2 S R31 46 47 67,750 S Total Operating Cash Flow $ 63,000 $ 76,230 $ $ $ 300,000 300.000 70,697 N 18.00% 10.00% 51% $54,000 $30,000 153,000 49 Terminal values: 50 1) Change in net WC 512) Salvage value (after tax) 52 Total $ $ $ 20,000 9,750 29,750 Salvage value*(1 - marginal tax rate) 54 Project Net Cash Flows $ (260,000) $ 67,750 $ 63,000 $ 76,230 $ 100,447 56 NPV = ($20,463.94) IRR = 6.5545% Payback 3.19 Payback Period 58 Profitability Index = 0.92 Discounted Payback = 0.00 Year Count Projected CF Cummulative S (260,000) $ (260,000) 67,750 S (192,250) 63,000 S (129,250) 76,230 S (53,020) 100,447 $ 47,427 Payback period PLEASE RESPOND TO THE SE QUESTIONS ON ANOTHER TAB S88 3.19 years 69 65 Q#1 Would you accept the project based on NPV, IRR? Would you accept the project based on Payback rule it project cut-off is 3 years? 68 Q#2 Impact of 2017 Tax Cut Act on Net Income, Cash Flows and Capital Budgeting (Investment ) Decisions 70 (a) Estimate NPV, IRR and Payback Period of the project if tax rate equals to 21%. Would you accept or reject the project? 78 (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? 72 Discounted Payback Period Present Value Projected CF Discount fact Discounted C Cummulative C Count O S (260,000) ($260,000) $ (260,000) $ 67,750 0.9091 $61,591 S (198,409) $ 63,000 0.8265 $52,066 $ (146,343) $ 76,230 0.7513 $57,273 $ (89,070) 100,447 0.6830 $68,606 S (20,464) Payback period 76 years IT Q#4 What are advantages and disadvantages of using only Payback method? 79 Q#5 What are advantages and disadvantages of using NPV versus IRR? 1:11+r) 81 Q#6 Explain the difference between independent projects and mutually exclusive projects. + Case and Template Response Sheet3 + @ - + J D a Type here to search A 1) 8:39 PM 5/2/2020 E22 Finance Project - Excel A - Insert Formulas Data Review View Help Search Share Comments File AutoSave R31 Home Of H Page Layout e rz : * fic G H J K 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 77 $200,000 5% 0.56 15 1) Life Period of the Equipment = 4 years 16 2) Ney equipment cost 17 3) Equipment ship & install cost 18 4) Related start up cost 19 5) Inventory increase 20 6) Accounts Payable increase 21 7) Equip. salvage value before tax ($200,000) ($35,000) ($5,000) $25,000 $5,000 $15,000 8) Sales for first year (1) 9) Sales increase per year 10) Operating cost (60% of Sales) ! (as a percent of sales in Year 1) 11) Depreciation 12) Marginal Corporate Tax Rate (T) 13) Cost of Capital (Discount Rate) -607 Use 3-yr MACRIS 35% 10% 591 205 Filling data in the cells colored' only. Do not rrite in other cell Do not delete any por or columns -70 CF1CF2CF3CF4 24 1 2 3 25 ESTIMATING Initial Outlay (Cash Flor, CFO, T= 0) 26 CFO 28 Year O 29 Investments: 301) Equipment cost 312) Shipping and Install cost 32 3) Start up expenses 33 Total Basis Cost (1+2+3) $ 34 4) Net Working Capital 35 Total Initial Outlay $ (200,000) (35,000) (5,000) (240,000) (20,000) (260,000) Depreciation Calculation $ 240,000 Depreciation Basis: # of years: Macrs 3 years 37 Operations: 38 Sales 39 Operating Cost negative 40 Depreciation 41 EBIT 42 Taxes 43 Net Income 44 45 Add back Depreciation 200,000 $ (120,000) S 45,000 $ 35,000 $ 12.250 $ 22,750 $ 210,000 $ 220,500 $ 231,525 (126,000) S (132,300) $ (138,915) 24,000 $ 54.000 $ 30.000 60,000 $ 34,200 $ 62,610 21,000 $ 11.970 $ 21.914 39,000 $ 22,230 $ 40,697 ( Year Basis $ 300,000 $ 300,000 Macrs % 15.00% 8.00% Depreciation $45,000 $24,000 45,000 $ 24,000 $ 54,000 $ 30,000 Case and Template Response Sheet3| @ 8:38 PM a J - . O l 1) 5/2/2020 Type here to search A 22 Finance Project - Excel A - Page Layout Formulas Data Review View Help Search Share Comments File AutoSave Home Insert Of H2 S R31 46 47 67,750 S Total Operating Cash Flow $ 63,000 $ 76,230 $ $ $ 300,000 300.000 70,697 N 18.00% 10.00% 51% $54,000 $30,000 153,000 49 Terminal values: 50 1) Change in net WC 512) Salvage value (after tax) 52 Total $ $ $ 20,000 9,750 29,750 Salvage value*(1 - marginal tax rate) 54 Project Net Cash Flows $ (260,000) $ 67,750 $ 63,000 $ 76,230 $ 100,447 56 NPV = ($20,463.94) IRR = 6.5545% Payback 3.19 Payback Period 58 Profitability Index = 0.92 Discounted Payback = 0.00 Year Count Projected CF Cummulative S (260,000) $ (260,000) 67,750 S (192,250) 63,000 S (129,250) 76,230 S (53,020) 100,447 $ 47,427 Payback period PLEASE RESPOND TO THE SE QUESTIONS ON ANOTHER TAB S88 3.19 years 69 65 Q#1 Would you accept the project based on NPV, IRR? Would you accept the project based on Payback rule it project cut-off is 3 years? 68 Q#2 Impact of 2017 Tax Cut Act on Net Income, Cash Flows and Capital Budgeting (Investment ) Decisions 70 (a) Estimate NPV, IRR and Payback Period of the project if tax rate equals to 21%. Would you accept or reject the project? 78 (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? 72 Discounted Payback Period Present Value Projected CF Discount fact Discounted C Cummulative C Count O S (260,000) ($260,000) $ (260,000) $ 67,750 0.9091 $61,591 S (198,409) $ 63,000 0.8265 $52,066 $ (146,343) $ 76,230 0.7513 $57,273 $ (89,070) 100,447 0.6830 $68,606 S (20,464) Payback period 76 years IT Q#4 What are advantages and disadvantages of using only Payback method? 79 Q#5 What are advantages and disadvantages of using NPV versus IRR? 1:11+r) 81 Q#6 Explain the difference between independent projects and mutually exclusive projects. + Case and Template Response Sheet3 + @ - + J D a Type here to search A 1) 8:39 PM 5/2/2020 E22
