Explore BrainMass

Transportation problem: Excel solver

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

Looking for assistance in setting up the problem. I am trying to establish the constraints and the table for solver. (See attached)

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.


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

© BrainMass Inc. brainmass.com October 16, 2018, 9:36 pm ad1c9bdddf


Solution Summary

This posting contains solution to following transportation problem using excel solver.

Similar Posting

Transportation problem excel solver

Bindley Corporation has a one-year contract to supply motors for all washing machines produced by Rinso Ltd. Rinso manufactures the washers at four locations around the country: New York, Fort Worth, San Diego and
Minneapolis. Plans call for the following number of washing machines to be produced at each location:

New York 50,000
Fort Worth 70,000
San Diego 60,000
Minneapolis 80,000

Bindley has three plants that can produce the motors. The plants and production capacities are:

Boulder 100,000
Macon 100,000
Gary 150,000

Due to varying production and transportation costs, the profit Bindley expects to earn on each 1,000 units depends on where they were produced and where they are shipped. The following table gives the accounting department estimates of the dollar profit per unit. (Shipment will me made in lots of 1,000.)
Shipped to
Produced at New York Ft. Worth San Diego Minneapolis
Boulder 7 11 8 13
Macon 20 17 12 10
Gary 8 18 13 16

Given profit maximization as a criterion, Bindley would like to determine how many motors should be produced at each plant and how many motors should be shipped from each plant to each destination. Also indicate which of the plants has excess capacity and how much

View Full Posting Details