Question: The Coka company is a soft drink company. Until today the company bought empty cans from an outside supplier that charges Coka $0.20 per can. In addition the transportation cost is $1,000 per truck that transports 10,000 cans. The Coka company is considering whether to start manufacturing cans in its plant. The cost of a can machine is $1 million and its life span is 12 years. The terminal value of the machine is $160,000. Maintenance and repair costs will be $150,000 for every 3-year period. The additional space for the new operation will cost the company $100,000 annually. The cost of producing a can in the factory is $0.17. The cost of capital of Coka is 11% and the corporate tax rate is 40%.
a. What is the minimum number of cans that the company has to sell annually to justify self-production of cans? (Hint: start by guessing that annual production is 3 million cans and then use Solver to correct the guess by making the NPV equal to 0. Note the NPV here should be the differential cash flows between own production and outside supply.)
b. Advanced: Use data table functionality to show the NPV and IRR of the project as a function of the number of cans.?