Linear Programming Models: Graphical and Computer Methods

Transportation Problem Using QM In this tutorial, we will solve a transportation problem using linear programming problem with Excel QM. Finnish Furniture manufactures tables in facilities located in three cities: Reno, Denver, and Pittsburgh. The tables are then shipped to three retail stores in Phoenix, Cleveland, and Chicago. Management wishes to develop a distribution schedule that will meet the demands at the lowest possible cost. The shipping cost per unit from each source to each destination is shown in the following table:

From \ To Phoenix Cleveland Chicago

Reno 10 16 19

Denver 12 14 13

Pittsburgh 18 12 12

The available supplies are 120 units from Reno, 200 from Denver, and 160 from Pittsburgh. The demands of each retail store are: Phoenix has 140; Cleveland has 160; Chicago has 180. Now, let’s open Excel QM and solve our problem. Click on the Excel QM tab  Alphabetical  Transportation.

In the Spreadsheet Initialization window, be sure to identify that we have three origins and three destinations and we want to minimize our costs.

Click OK. A spreadsheet will display.

Enter the data shown above into the spreadsheet table.

Once you have the data entered correctly, click the Data tab and then Solver.

A Solver Parameters window will appear.

Click Solve and then OK in the Solver Results window. Our results are shown on the next page.

Data

COSTS Phoenix Cleveland Chicago Supply

Reno 10 16 19 120

Denver 12 14 13 200

Pittsburgh 18 12 12 160

Demand 140 160 180 480 \ 480

Shipments

Shipments Phoenix Cleveland Chicago Row Total

Reno 120 0 0 120

Denver 20 0 180 200

Pittsburgh 0 160 0 160

Column Total 140 160 180

480 \ 480

Total Cost 5700

The optimal solution found using computer software for the transportation algorithm is to ship 120 from Reno to Phoenix, 20 from Denver to Phoenix, 160 from Pittsburgh to Cleveland, and 180 from Denver to Chicago. The total cost is $5,700. Click here to download the completed spreadsheet table so you can compare it to yours. This concludes our tutorial on solving a transportation problem using linear programming problem with Excel QM.