ONLY IN EXCEL, NO HARD CODED FORMULA. I will rate and comment but only excel.

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.

F G H I J K L M N O B C D E COKA COMPANY: MAKING CANS 11% 40% 2 Cost of capital 3 Tax rate Current cost per can 6 Each can Transportation per 10,000 cans 0.20 1,000 1,000,000 9 Can machine 10 Cost of machine 11 Life span (years) 12 Annual depreciation 13 Estimated salvage value, year 12 14 Maintenance/repair, every 3 year 15 Marginal cost of producing a can 160,000 150,000 0.17 16 17 Annual can production 3,000,000