I need an actual excel spreadsheet to see how you got your answers with cash flo

Question

I need an actual excel spreadsheet to see how you got your answers with cash flow chart if possible.

A widget manufacturer currently produces 200,000 units per year. It buys widget lids from an outside supplier at a price of $2 a lid. The plant manager believes that it would be cheaper to make these lids rather than buy them. Direct production costs are estimated to be only $1.50 a lid. The necessary machiinery would cost $150,000 and last 10 years. This investment could be written off for tax purposes using the seven-year tax depreciation schedule. The plant manager estimates that the operation would require additional working capital of $30,000 but argues that this sum can be ignored sice it is recoverable at the end of 10 years. If the company pays tax at a rate of 35% and the opportunity cost of capital is 15%, would you support the plant managers proposal. State your assumptions that you need to make.

I know the answers are supposed to be below, but I cannot figure out how to get those in excel.

NPV of purchase = -1,304,880

NPV of make = -1,118,328

Explanation / Answer

Year 0 1 2 3 4 5 6 7 8 9 10 Investment -150000 Working Capital -30000 Direct production costs -300000 -300000 -300000 -300000 -300000 -300000 -300000 -300000 -300000 -300000 Depreciation % 14.29 24.49 17.49 12.49 8.93 8.92 8.93 4.46 Depreciation -21435 -36735 -26235 -18735 -13395 -13380 -13395 -6690 Total -321435 -336735 -326235 -318735 -313395 -313380 -313395 -306690 -300000 -300000 After tax -208933 -218878 -212053 -207178 -203707 -203697 -203707 -199349 -195000 -195000 ADD Depreciation 21435 36735 26235 18735 13395 13380 13395 6690 0 0 Working Capital 30000 TOTAL -180000 -187498 -182143 -185818 -188443 -190312 -190317 -190312 -192659 -195000 -165000 PV Factor 15% 0.869565 0.75614 0.657516 0.571753 0.497177 0.432328 0.375937 0.326902 0.28426 0.247185 Discounted cash flow -180000 -163042 -137726 -122178 -107743 -94618.6 -82279.3 -71545.2 -62980.4 -55431 -40785.5 NPV = -1118329 Year 0 1 2 3 4 5 6 7 8 9 10 Purchase Cost -400000 -400000 -400000 -400000 -400000 -400000 -400000 -400000 -400000 -400000 After Tax -260000 -260000 -260000 -260000 -260000 -260000 -260000 -260000 -260000 -260000 PV Factor 15% 0.869565 0.75614 0.657516 0.571753 0.497177 0.432328 0.375937 0.326902 0.28426 0.247185 Discounted cash flow -226087 -196597 -170954 -148656 -129266 -112405 -97743.6 -84994.5 -73908 -64268 NPV = -1,304,880

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.