Reference no: EM132467413

Point 1: 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

Point 2: 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.

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