Cosmos Bottling Case

1
Cosmos Bottling Case

• Henry C. Co
• Technology and Operations Management,
• California Polytechnic and State University

2
Alternatives

• In case you have not figured it out yet
• There are six feasible alternatives (i.e., with
  combined capacity greater than or equal to year
  2000 demand).
• Atlanta, Buffalo, Chicago, Detroit Cincinnati.
• Atlanta, Buffalo, Chicago, Detroit Richmond.
• Atlanta, Buffalo, Chicago, Detroit St. Louis.
• Atlanta, Buffalo, Detroit Cincinnati.
• Atlanta, Buffalo, Detroit Richmond.
• Atlanta, Buffalo, Detroit St. Louis.

3
Fixed and variable costs

• Exhibit IV gives the fixed cost and variable
  labor cost of each plant.
• Other variable costs include
• Shipping costs of finished goods
• Raw material costs (syrup)
• Shipping costs of syrup

4
Shipping costs of finished goods
5
Syrup labor costs
6
Total Unit Costs
7
Allocation Matrix
8
Setting Up the Allocation Matrix

• Column I Sum of Column B to Column H. For
  example, I69 SUM(B69H69)
• Line 94 Sum of Line 69 to Line 93. For example,
  B94 SUM(B69B93)
• The allocations are in thousand cases. Therefore,
  unit costs must be multiplied by 1,000. Cell B96
  Total cost of Atlanta SUMPRODUCT(B41B65,B69B93
  )1000
• Copy and paste Cell B96 onto Cells C96H96)
• Cell I96 Total system cost SUM(B96H96). This
  is the target to be minimized.

9
The annual fixed cost

• Line 95 shows the supply of each plant
• Supply 0, if the plant is not activated
• If suppy gt 0, there is an annual fixed cost
• Line 97 shows fixed cost of each plant
• Cell I97 sum of fixed cost, wherever supply gt0
• Cell I99 SUM(I96I97)

10
Run Solver for each alternative

• Starting with blank (zero) allocation.
• The target is to minimize total cost (look, it is
  cell I96!).
• By changing what?
• The columns corresponding to the facilities
  listed in the alternative.
• For example, in alternative 1, we are looking at
  the columns for Atlanta, Buffalo, Chicago,
  Detroit and Cincinnati.
• Constraints?
• Demand must be satisfied.
• Supply can not be exceeded.
• Option non-negative, auto scaling, linear model

11
Solver
12
Download Excel worksheet

• Click HERE.