Linear Programming Models: Graphical and Computer Methods
Hooknem
GBA334_Facility_Location_Problem_Using_QM.pdf
Facility Location Problem Using QM In this tutorial, we will solve a facility location problem using linear programming with Excel QM. Don Levine Corporation is considering adding a plant to its existing three facilities in Decatur, Minneapolis, and Carbondale. Both St. Louis and East St. Louis are being considered. Evaluating only the transportation costs in the tables below, which site is best?
To \ From Decatur Minneapolis Carbondale Demand
Blue Earth 20 17 21 250
Ciro 25 27 20 200
Des Moines 22 25 22 350
Capacity 300 200 150
To \ From East St. Louis St. Louis
Blue Earth 29 27
Ciro 30 28
Des Moines 30 31
Capacity 150 150
To make our decision, we need to run a transportation problem twice, once with the information for East St. Louis, and once with the information for St. Louis and see our lowest cost option. 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 four 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. As noted the first time, we will use the data for East St. Louis.
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.
First Option (East St. Louis):
Data
COSTS Blue Earth Ciro
Des Moines Supply
Decatur 20 25 22 300
Minneapolis 17 27 20 200
Carbondale 21 20 22 150
East St. Louis 29 30 30 150
Demand 250 200 350 800 \ 800
Shipments
Shipments Blue Earth Ciro
Des Moines
Row Total
Decatur 50 0 250 300
Minneapolis 200 0 0 200
Carbondale 0 150 0 150
East St. Louis 0 50 100 150
Column Total 250 200 350
800 \ 800
Total Cost 17400
Click here to download the completed spreadsheet table so you can compare it to yours.
Now, to do the second option with Saint Louis, replace the information for East Saint Louis with that of Saint Louis as shown here.
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 here.
Data
COSTS Blue Earth Ciro
Des Moines Supply
Decatur 20 25 22 300
Minneapolis 17 27 20 200
Carbondale 21 20 22 150
St Louis 27 28 31 150
Demand 250 200 350 800 \ 800
Shipments
Shipments Blue Earth Ciro
Des Moines
Row Total
Decatur 0 0 300 300
Minneapolis 150 0 50 200
Carbondale 0 150 0 150
St Louis 100 50 0 150
Column Total 250 200 350
800 \ 800
Total Cost 17250
From the results, we can see the total costs for East St. Louis are $17,400 and from St. Louis is $17,250. Our best option would be to build the St. Louis plant. Click here to download the completed spreadsheet table so you can compare it to yours. This concludes our tutorial on solving facility location linear programming problems using Excel QM.
