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.

Explanation / Answer

Dear Student,

Surely I shall try to answer your question to solve your doubts.

To solve the above problem I would make some assumptions as below:-

1) I am using the 7 year tax depreciation schedule on a Straight Line Basis and not on Reducing Balance Method.

2) I am not compunding the cost of capital on a yearly basis and so the Cost of Capital of15% would me maintained throught the year upto year 10 on the initial investment of 150,000

3) I am not taking into account the initial investment of additional 30,000 USD into working capital as an opportunity cost since it is recoverable at the end of 10 years

4) I am also assuming that the tax rate remains same at 35% and direct cost of manufacturing the lids also remain the same at 1.50 USD per lid over the next 10 years.

Also In the caluclation following below there is a difference of USD 45 (21,435-21,390) in the 7th year for rounding of the total depreciation to match the intial investment of USD 150,000 of the Plant and Machinery.

Please find the solution below:

Dear Student

Unfortunately I am not able to insert the spreadsheet which I have made for this working but i am sending the calculation here for your reference.

I Will try and send you the spreadsheet again with the help of administrators.

Thanks

Current Cost of the lids purchased USD Quantity Cost Cost per unit 2                  200,000            400,000 Therefore total cost for purchasing lids for 10 years would be 10*2*200000 i.e. 4,000,000 USD For inhouse manufacturing lets calculate the following as per the given cots attributes Initial investment 150,000 (For Plant & Machinery) Depreciation Rate 14.29% (Since we are using the 7 year drawdown method so it is to depreciate 100% we divide 100 by 7 to get a rate of 14.29% per year on Straight Line Method) Annual Tax Benefit Opportunity TOTAL COST Year Demand Direct Cost Depreciation on Depreciation Cost @ 15% FOR THE YEAR 1 200000 300000 21,435 7502 22500 336,433 2 200000 300000 21,435 7502 22500 336,433 3 200000 300000 21,435 7502 22500 336,433 4 200000 300000 21,435 7502 22500 336,433 5 200000 300000 21,435 7502 22500 336,433 6 200000 300000 21,435 7502 22500 336,433 7 200000 300000 21,390 7487 22500 336,404 8 200000 300000 0 0 22500 322,500 9 200000 300000 0 0 22500 322,500 10 200000 300000 0 0 22500 322,500 3,322,500 Therefore there is a total saving of 677,500 USD over a period of 10 years (4,000,000-3,322,500) Therefore it is feasible to manufacture the Lids in house.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

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