Stateline Shipping Company
Business
Assignment #4: Case Problem “Stateline Shipping and Transport Company”
1. In Excel, or other suitable program, develop a model for shipping the waste directly from the 6 plants to the 3 waste disposal sites.
White water Los Canos Duras Availability
Kingsport $12.00 $15.00 $17.00 35
Danville $14.00 $9.00 $10.00 26
Macon $13.00 $20.00 $11.00 42
Selma $17.00 $16.00 $19.00 53
Columbus $7.00 $14.00 $12.00 29
Allentown $22.00 $16.00 $18.00 38
Capacity 65 80 105 223
The objective of the problem is to develop a shipping schedule that minimizes the total cost of transportation. Suppose Xij denotes the number of barrels of wastes to be transported from the “i” plant to “j” site.
Then the total cost of transportation is:
Z = 12 X11 + 15 X12 + 17 X13 + 14 X21 + 9 X22 + 10 X23 + 13 X31 + 20 X32 + 11 X33 + 17 X41+ 16 X42 + 19 X43 + 7 X51 + 14 X52+ 12 X53 + 22 X61 + 16 X62 + 18 X63.
Thus the objective function of the problem is to minimize
Z = 12 X11 + 15 X12 + 17 X13 + 14 X21 + 9 X22 + 10 X23 + 13 X31 + 20 X32 + 11 X33 + 17 X41+ 16 X42 + 19 X43 + 7 X51 + 14 X52+ 12 X53 + 22 X61 + 16 X62 + 18 X63.
Constraints
Availability in plants:
X11 + X12 + X13 = 35
X21 + X22 + X23 = 26
X31 + X32 + X33 = 42
X41 + X42 + X43 = 53
X51 + X52 + X53 = 29
X61 + X62 + X63 = 38
Capacity of the sites:
X11 + X21+ X31+X41 + X51 + X61 ≤ 65
X12 + X22+ X32+X42 + X52 + X62 ≤ 80
X13 + X23+ X33+X43 + X53 + X63 ≤ 105
Non- Negativity restrictions
Xij ≥ 0 , i = 1,2,3,4,5,6 ; j = 1,2,3.
2. Solve the model you developed in #1 (above) and clearly describe the results
Target Cell (Min)
Cell Name Original Value Final Value
$B$22 Objective Function (Total cost) White water 2822 2822
Adjustable Cells
Cell Name Original Value Final Value
$B$13 Kingsport White water 35.00 35.00
$C$13 Kingsport Los Canos 0.00 0.00
$D$13 Kingsport Duras 0.00 0.00
$B$14 Danville White water 0.00 0.00
$C$14 Danville Los Canos 0.00 0.00
$D$14 Danville Duras 26.00...
The total cost of this
Assignment #4: Case Problem “Stateline Shipping and Transport Company”
1. In Excel, or other suitable program, develop a model for shipping the waste directly from the 6 plants to the 3 waste disposal sites.
White water Los Canos Duras Availability
Kingsport $12.00 $15.00 $17.00 35
Danville $14.00 $9.00 $10.00 26
Macon $13.00 $20.00 $11.00 42
Selma $17.00 $16.00 $19.00 53
Columbus $7.00 $14.00 $12.00 29
Allentown $22.00 $16.00 $18.00 38
Capacity 65 80 105 223
The objective of the problem is to develop a shipping schedule that minimizes the total cost of transportation. Suppose Xij denotes the number of barrels of wastes to be transported from the “i” plant to “j” site.
Then the total cost of transportation is:
Z = 12 X11 + 15 X12 + 17 X13 + 14 X21 + 9 X22 + 10 X23 + 13 X31 + 20 X32 + 11 X33 + 17 X41+ 16 X42 + 19 X43 + 7 X51 + 14 X52+ 12 X53 + 22 X61 + 16 X62 + 18 X63.
Thus the objective function of the problem is to minimize
Z = 12 X11 + 15 X12 + 17 X13 + 14 X21 + 9 X22 + 10 X23 + 13 X31 + 20 X32 + 11 X33 + 17 X41+ 16 X42 + 19 X43 + 7 X51 + 14 X52+ 12 X53 + 22 X61 + 16 X62 + 18 X63.
Constraints
Availability in plants:
X11 + X12 + X13 = 35
X21 + X22 + X23 = 26
X31 + X32 + X33 = 42
X41 + X42 + X43 = 53
X51 + X52 + X53 = 29
X61 + X62 + X63 = 38
Capacity of the sites:
X11 + X21+ X31+X41 + X51 + X61 ≤ 65
X12 + X22+ X32+X42 + X52 + X62 ≤ 80
X13 + X23+ X33+X43 + X53 + X63 ≤ 105
Non- Negativity restrictions
Xij ≥ 0 , i = 1,2,3,4,5,6 ; j = 1,2,3.
2. Solve the model you developed in #1 (above) and clearly describe the results
Target Cell (Min)
Cell Name Original Value Final Value
$B$22 Objective Function (Total cost) White water 2822 2822
Adjustable Cells
Cell Name Original Value Final Value
$B$13 Kingsport White water 35.00 35.00
$C$13 Kingsport Los Canos 0.00 0.00
$D$13 Kingsport Duras 0.00 0.00
$B$14 Danville White water 0.00 0.00
$C$14 Danville Los Canos 0.00 0.00
$D$14 Danville Duras 26.00...
The total cost of this