A company which manufactures product in five plants ships locally using its own transportation
system, but has orders which must be sent to locations too far to be serviced by the local fleet.
It therefore contracts with a middle distance carrier to complete its shipping. The locations of the
manufacturing plants and the amount of product available to be shipped per week are show in the
chart below.
Manufacturing
Locations
Columbia
Macon
Huntsville
Greensboro
Knoxville
Total Available

Units
Available
per Week
450
370
550
290
360
2020

The seven locations and their weekly demand are shown in the chart below.
Destination Cities

Weekly
Demand

Norfolk
Charleston
Gainesville
Mobile
Memphis
Louisville
Roanoke
Total Available

305
253
328
225
420
158
210
1899

Shipping costs per unit (in dollars) between plants and the destination cities are as follows:

Shipping Costs
Columbia
Macon
Huntsville
Greensboro
Knoxville

Norfolk
$27
42
42
28
47

Charleston
$13
27
31
25
31

Gainesville
$31
19
23
36
43

Mobile
$42
23
16
48
39

Memphis
$48
18
20
37
16

Louisville
$51
36
39
32
14

Roanoke
$44
43
36
17
34

The transportationcompany wants to identify its optimal shipping plan that will satisfy demand at the
lowest aggregate shipping cost.
Questions
What is the transportation company trying to optimize? Are they trying to maximize or minimize?
Write the objective function to support this analysis.
What inputs do you need to support your analysis?
Is there any extraneous data you have been given that you will not need?

What criteria has the transportation company given you to support the analysis?
Create a spreadsheet model that supports your analysis.
How would you change your model if one of the locations was temporarily unavailable due to severe
weather conditions? On a separate sheet in your Excel file, show how you might do this.…