Question

4. A distributor received goods at five US ports, from which the goods are sent on to ten major customers in the following ten cities: Salt Lake City, Las Vegas, Denver, St. Louis, Houston, Atlanta, Detroit, Boston, Baltimore, and Albany. Each customer has monthly demand of 300 cases. The monthly availability of the goods at the ports is shown in the table below:

Ports = Seattle Oakland Norfolk Savannah New York

Monthly Availability (cases) = 600 1000 500 400 500

The shipping costs per unit of product from the production plants to the customers are shown below.

Shipping costs per case (cents):

From/to = Salt Lake City Las Vegas Denver St. Louis Houston Atlanta Detroit Boston Baltimore Albany

Seattle 85 110 130 210 230 260 240 300 270 290

Oakland 70 60 110 160 180 250 220 290 240 250

Norfolk 220 250 170 90 130 50 70 60 25 50

Savannah 190 200 160 80 90 25 90 100 60 90

New York 230 260 180 100 170 90 60 25 20 15

Solve a Transportation Problem in Excel to find the lowest cost delivery plan to meet the customers’ requirements

. a) Show the minimum cost and the optimal shipping pattern. Insert below the Excel spreadsheet that you used showing the optimal solution. Minimum cost = ______________ -- insert spreadsheet here --

b) How much does Oakland send to each destination?

c) From where does Detroit receive shipments?

Answer 1

a) The minimum cost = $2530

b)

c)