Reference no: EM13962625

Transportation System

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	Units Available per Week
Columbia	450
Macon	370
Huntsville	550
Greensboro	290
Knoxville	360
Total Available	2020

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

Destination Cities	Weekly Demand
Norfolk	305
Charleston	253
Gainesville	328
Mobile	225
Memphis	420
Louisville	158
Roanoke	210
Total Available	1899

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

Shipping Costs	Norfolk	Charleston	Gainesville	Mobile	Memphis	Louisville	Roanoke
Columbia	27	13	31	42	48	51	44
Macon	42	27	19	23	18	36	43
Huntsville	42	31	23	16	20	39	36
Greensboro	28	25	36	48	37	32	17
Knoxville	47	31	43	39	16	14	34

The transportation company wants to identify its optimal shipping plan that will satisfy demand at the lowest aggregate shipping cost.

Questions:

1. What is the transportation company trying to optimize? Are they trying to maximize or minimize?
2. Write the objective function to support this analysis.
3. What inputs do you need to support your analysis?
4. Is there any extraneous data you have been given that you will not need?
5. What criteria has the transportation company given you to support the analysis?
6. Create a spreadsheet model that supports your analysis.
7. 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.