Use excel to compute Cash flows, NPV, IRR and Pay Back period Use Excel Spread S
ID: 2568057 • Letter: U
Question
Use excel to compute Cash flows, NPV, IRR and Pay Back period
Use Excel Spread Sheet to compute Cash flows, NPV, IRR and Pay Back Period $200,000 5% $(120,000) -60% $(60,000) 35% 10% 1) Life Period of the Equipment+ 4 years 2) New equipment cost 8) Sales for first year (1) 9) Sales increase per year 10) Operating cost: $(200,000) 3) Equipment ship & install cost $(35,000) $(5,000) $25,000 $5,000 ) Equip. salvage value after tax $15,000 (as a percent of sales in Year 1) 11) Depreciation (Straight Line)YR 12) Tax rate 13) WACC Related start up cost 5) Inventory increase ) Accounts Payable increase ESTIMATING CASH FLOW and MAKING CAPITAL BUDGETING DECISION ear Investments 1) Equipment cost 2) Shipping and Install cost 3) Start up expenses Total Basis Cost (1+2+3) 4)Net Working Capital Total Initial OutlaExplanation / Answer
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: ($120,000) 4) Related start up cost ($5,000) (as a percent of sale in year 1 -60% 5) Inventory increase $25,000 11) Depreciation (Straight Line)/YR ($60,000) 6) Accounts Payable increase $5,000 12) Tax rate 35% 7) Equip. salvage value after tax $15,000 13) WACC 10% Estimating cash Flow and Making Capital Budgeting Decision Year 0 1 2 3 4 Investments: 1) Equipment cost ($200,000) 2) Shipping and Install cost ($35,000) 3) Start up expenses ($5,000) Total Basis Cost (1+2+3) ($240,000) 4) Net Working Capital(6-5) ($20,000) Total Initial Outlay ($260,000) Operations: Revenue $200,000 $210,000 $220,500 $231,525 Opearting Cost (60% of sales) (120,000) (126,000) (132,300) (138,915) Depreciation (60,000) (60,000) (60,000) (60,000) EBIT 20,000 24,000 28,200 32,610 Taxes at 35% 7,000.00 8,400.00 9,870.00 11,413.50 Net Income (EBIT-Tax) 13,000.00 15,600.00 18,330.00 21,196.50 Add back Depreciation 60,000 60,000 60,000 60,000 Total Operating Cash Flow 73,000.00 75,600.00 78,330.00 81,196.50 Terminal: 1) Change in Net WC 20,000 2) Salvage value (after tax) 15,000 Total 35,000 Naming Values 1 Values 2 Values 3 Values 4 Values 5 Projeted Net Cash Flow ($260,000) $ 73,000.00 $ 75,600.00 $ 78,330.00 $ 116,196.50 Cumulative cash flow ($260,000) $ (187,000.00) $ (111,400.00) $ (33,070.00) $ 83,126.50 NPV = $ 7,057.24 IRR = 11.16% Payback = 3.28 Years Values 1+NPV(10%,Values2 to values 5) IRR(Values 1 to Values 5) 3+(33070/116196) Payback = No. of years before first positive cumulative cash flow + (Absolute value of last negative cumulative cash flow / cash flow in the year of first positive cumulative cash flow) Note: I have tried my best for correct solution , still find any difficulty, please ask in comment, hope a positive feedback.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.