Read the “Stateline Shipping and Transport Company” Case Problem on pages 273-274 of the text. Analyze this case, as follows: 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. 2. Solve the model you developed in #1 (above) and clearly describe the results. 3. In Excel, or other suitable program, Develop a transshipment model in which each of the plants and disposal sites can be used as intermediate points. 4. Solve the model you developed in #3 (above) and clearly describe the results. 5. Interpret the results and draw conclusions that address the question posed in the case problem. What are the limits of the study? Write at least one paragraph.

There are two deliverables for this Case Problem, the Excel spreadsheets and an accompanying written description/explanation. Please submit both of them electronically via the dropbox.

1) The model for the transportation problem consists of 18 decision variables, representing the number of barrels of wastes transported from each of the 6 plants to each of the 3 waste disposal sites: [pic]= Number of Barrels transported per week from plant ‘i’ to the j-th waste disposal site, where i = 1, 2, 3, 4, 5, 6 and j = A, B, C. The objective function of the manager is to minimize the total transportation cost for all shipments. Thus the objective function is the sum of the individual shipping costs from each plant to each waste disposal site: Minimize Z = 12[pic]+ 15[pic]+ 17[pic]+ 14[pic]+ 9[pic]+ 10[pic]+ 13[pic]+ 20[pic] +11[pic]

+17[pic] +16[pic] +19[pic] +7[pic] +14[pic] +12[pic] +22[pic] +16[pic] +18[pic] The constraints in the model are the number of barrels of wastes available per week at each plant and the number of barrels of wastes accommodated at each waste disposal site. There are 9 constraints- one for each plant supply and one for each waste disposal site’s demand.

The six supply constraints are:

[pic]+ [pic]+ [pic] = 35

[pic]+ [pic]+ [pic] = 26

[pic]+ [pic] +[pic] = 42

[pic] + [pic] +[pic] = 53

[pic] +[pic] +[pic] = 29

[pic] + [pic] +[pic] = 38

As an example, here the supply constraint [pic]+ [pic]+ [pic] = 35 represents the number of barrels transported from the plant Kingsport to all the three waste disposal sites. The amount transported from Kingsport is limited to the 35 barrels available. The three demand constraints are:

[pic]+ [pic]+ [pic]+[pic]+ [pic]+[pic] ≤ 65

[pic]+ [pic]+ [pic]+ [pic]+[pic] +[pic] ≤ 80

[pic]+ [pic]+[pic]+ [pic]+[pic] +[pic] ≤ 105

Here the demand constraint [pic]+ [pic]+ [pic]+[pic]+ [pic]+[pic] ≤ 65 represents the number of barrels transported to the waste disposal site Whitewater from all the six plants. The barrel of wastes that can accommodate in the waste disposal site Whitewater is limited to 65 barrels. The demand constraints are ≤ inequalities because the total demand (65+80+105) = 250 exceeds the total supply (26+42+53+29+38) = 223. The linear programming model for the transportation problem is summarized as follows: Minimize Z = 12[pic]+ 15[pic]+ 17[pic]+ 14[pic]+ 9[pic]+ 10[pic]+ 13[pic]+ 20[pic] +11[pic]

+17[pic] +16[pic] +19[pic] +7[pic] +14[pic] +12[pic] +22[pic] +16[pic] +18[pic] Subject to

[pic]+ [pic]+ [pic] = 35

[pic]+ [pic]+ [pic] = 26

[pic]+ [pic] +[pic] = 42

[pic] + [pic] +[pic] = 53

[pic] +[pic] +[pic] = 29

[pic] + [pic] +[pic] = 38

[pic]+ [pic]+ [pic]+[pic]+ [pic]+[pic] ≤ 65

[pic]+ [pic]+ [pic]+ [pic]+[pic] +[pic] ≤ 80

[pic]+ [pic]+[pic]+ [pic]+[pic] +[pic] ≤ 105

[pic]

2) Because the transportation model is formulated as a linear programming model, it can be solved with Excel Solver. The spreadsheet solution is shown in the following table. |Estimated Shipping Cost ($ per barrel) | | | |...