Bauer produces hundreds of products at their various manufacturing facilities in the United States and Canada.This past spring many of the plants had issues managing their production and inventory to meet weekly demands. Management would like you to develop an inventory model for the Irving, CA roller hockey facility that they can use to plan their internal production and external buying strategy. The following table describes the estimate weekly demand, production cost and external cost (outsourcing of production) per container.

week1 week2 week3 week4 week5 week6 week7

Demand (units) 4600 6280 5480 3520 7570 5130 5940

Production Cost/Unit 7.40 8.80 9.70 6.90 6.80 10.50 10.20

External Cost/Container 2183 2203 2230 1995 1986 2169 2019

They can produce up to 6,000 units per week internally and can order shipping containers of 250 units from an external supplier at the cost listed in last row of the table. Each week they order containers from the external supplier they must pay a $2,000 fixed cost to order any number of containers (no maximum.) They can store up to 1,800 units and start week 1 with 1,600 units in beginning inventory. The carrying cost is $2.25 per unit based on average inventory.

Develop and solve this problem using an Excel model to minimize their total cost and determine the optimum ordering and inventory strategy.