Using Excel to Formulate and Solve Transportation Problems

作業研究 Using Excel to Formulate and Solve Transportation Problems

Using Excel to Formulate and Solve Transportation Problems l To formulate and solve a transportation problem using Excel, two separate tables need to be entered on a spreadsheet l The first one is the parameter table l The second is a solution table, containing the quantities to distribute from each source to each destination

Two separate tables

Functional constraints -1 l The two types of functional constraints need to be included in the spreadsheet l For the supply constraints, the total amount shipped from each source is calculated in column H of the solution table l The supply at each source is included in column J l the cells in column H must equal the corresponding cells in column J

Functional constraints -2 l The total amount shipped to each destination is calculated in row 18 of the spreadsheet l The demand at each destination is then included in row 20 l The total cost is calculated in cell H 18 l This cost is the sum of the products of the corresponding cells in the main bodies of the parameter table and the solution table

Solver dialogue l Excel File l All shipment quantities must be nonnegative l Transportation problem is a linear programming problem l The Solver simply uses the general simplex method to solve a transportation problem rather than a streamlined version

Do not quite fit the model for a transportation problem Violate the requirements assumption l It is possible to reformulate such a problem to fit this model buy introducing a dummy destination or a dummy source l When using the Excel Solver, it is not necessary to do the reformulation since the simplex method can solve the original model there the supply constraints are in form or the demand constraints are in form l