1 / 8

作業研究

作業研究. Using Excel to Formulate and Solve Transportation Problems. Using Excel to Formulate and Solve Transportation Problems. To formulate and solve a transportation problem using Excel, two separate tables need to be entered on a spreadsheet The first one is the parameter table

lcossey
Download Presentation

作業研究

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

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

  3. Two separate tables

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

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

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

  7. Do not quite fit the model for a transportation problem • Violate the requirements assumption • It is possible to reformulate such a problem to fit this model buy introducing a dummy destination or a dummy source • 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

More Related