Problem 1: can you do it on excel • You are a financial manager at a soft-drink
ID: 2783975 • Letter: P
Question
Problem 1: can you do it on excel • You are a financial manager at a soft-drink company. Until today the company bought empty cans from an outside supplier for $0.22/can. You are considering whether to begin manufacturing cans in-house to achieve cost savings. • The cost of purchasing a can machine is $850,000 and the lifespan of the machine is 10 years. At the end of 10 years, the company expects to sell the machine for $160,000. Assume the machine is depreciated on a straight-line basis to zero. • The cost of producing a can using the in-house machine is $0.17. • The machine would be purchased at year 0, and all subsequent cash flows occur in year 1-10. • Assume the discount rate is 10% and the corporate tax rate is 36%. Also assume that the project does not require an investment in Net Working Capital. • Assume that the company will produce and sell 3 million cans annually. Determine the NPV of moving to in-house production relative to the current situation. Should you accept or reject the project?
Explanation / Answer
Annual Savings if moved inhouse =$3,000,000x(0.22-0.17) =$150,000
Initial Cost of Machine = $850,000
Annual Depreciation = $850,000/10 =$85,000
Income Statement
Savings $150,000
Depreciation $ 85,000
Profit Before Tax $ 65,000 [ 150,000 - 85,000 =65,000]
Tax @ 36% $ 23,400 [ 0.36x65,000 =23,400]
Profit After Tax $ 41,600
Add Depreciation $ 85,000
OCF $ 126,600
NPV = -850,000 + 126,600x{(1-(1+0.10)-4)/0.10}
= -850,000 + 126,600x3.1699
= -850,000 + 401,304.97
= -448,695.03
Since, the NPV of project is negative, the capital investment in the project does not make sense.
Hence, the project should be Rejected.