Splash Soft Drinks

Solver does not return a solution that makes sense to me. Particularly the highlighted parts of the worksheet. I don't know why, but Solver seems to ignore my "binary" constraint with regards to keeping plants open or closed and for some reason Solver does not return transportation cost values. Are you able to point me in the right direction with these two issues? Thank you.

***Additionally, how can this model be modified if the maximum distance between a plant and market cannot exceed 150 miles? Can you help me with the additional constraints required to model this?

Splash Soft Drinks Inc. (SSD) has recently achieved sales that exceeded its expectations after it introduced a new beverage that was greatly welcomed by their customers. The company is currently considering opening a new plant to which some of the production of the other plants may be shifted.

The management has decided to undertake a preliminary analysis in order to evaluate the minimum cost for the network configuration under the assumption that the network will be designed from scratch (i.e., as if no plants currently exist)

The annual fixed and production costs as well as the plant capacity at each location is given in the Table 1.

TABLE 1*.
Plant Fixed cost
(in $) Capacity
(in hectoliters+) Production cost
($/hectoliter+)
A 81400 22000 0.5
B 83800 24000 0.45
G 88600 28000 0.42
H 91000 30000 0.44
I 79000 20000 0.56
C 86200 26000 0.5
D 88600 28000 0.5
J 91000 30000 0.46
E 79000 20000 0.43
F 80200 21000 0.41
+ a hectoliter is 100 liters
* Excel data files are included

The distance in miles between the potential plants and markets is given in Table 2. The demand for markets A through F must be met and is given in the bottom of Table 2.

The trucks have capacity of 150 hectoliters and a cost of $0.92 per mile. Note that when a truck makes a delivery trip, it goes back to the plant empty. State any assumptions you make and address the following questions:

1) Formulate a linear/integer programming model to minimize the total cost for the network and to determine the number of plants to be opened and their locations. Make sure you clearly define your decision variable, objective function and constraints.

2) Implement the formulated model in Excel and use Solver to find the optimal plant locations when the total cost is minimized. Report a summary of your answer.

3) Suppose that the maximum distance between a plant and a market cannot exceed 150 miles. What additional decision variables and/or constraints do you need to add to your model in question (1) to accommodate this condition?

4) Modify your model in question (2) to take into account the new condition in question (3) and resolve the problem. Report the new locations and minimum cost.

TABLE 2*. Distance in miles between potential plants and markets
Markets
A B C D E F
A 0 76.1 30.4 139.4 72.6 11.7
B 76.1 0 71 77.2 144.5 83.7
G 20.8 92.9 47.2 156.1 47.5 11.7
H 54.7 113.3 52.9 187.2 93 45.2
Plants I 13.5 85.5 28 148.7 67.3 9.3
C 30.4 71 0 138.2 94.5 38.1
D 139.4 77.2 138.2 0 207.9 146.9
J 47.8 106.5 46.2 180.2 86.7 38.9
E 72.6 144.5 94.5 207.9 0 63.4
F 11.7 83.7 38.1 146.9 63.4 0

Demand 14000 10000 8000 12000 10000 9000

* Excel data files are included

Notes:
1) Refer to case guidelines when you write your report
2) You can assume that the transportation cost can be calculated for a fraction of a truck.