1 / 22

Network Models with Excel

Network Models with Excel. Simple Structure Intuition into solver Numerous applications Integral data means integral solutions. Netherlands. Amsterdam. 500. *. 800. The Hague. *. Germany. 500. Tilburg. *. 700. *. Antwerp. Leipzig. *. Belgium. 400. *. Liege. 200. Nancy.

lilka
Download Presentation

Network Models with Excel

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. Network Models with Excel • Simple Structure • Intuition into solver • Numerous applications • Integral data means integral solutions

  2. Netherlands Amsterdam 500 * 800 The Hague * Germany 500 Tilburg * 700 * Antwerp Leipzig * Belgium 400 * Liege 200 Nancy * 900 Miles 0 50 100 PROTRAC Engine Distribution 500 800 500 400 700 200 900

  3. Transportation Costs Unit transportation costs from harbors to plants Minimize the transportation costs involved in moving the engines from the harbors to the plants

  4. A Transportation Model

  5. Model Components • Adjustables or Variables • By changing cells • selection ranges separated by commas • Objective • Target Cell • Min or Max • Constraints • LHS is a cell reference • >=, <=, = (others for later) • RHS is a cell reference or number.

  6. Netherlands 500 Amsterdam 500 * 800 The Hague * Germany 800 500 Tilburg 500 * 400 700 * Antwerp Leipzig 700 * Belgium 400 * Liege 200 200 Nancy * 900 900 Miles 0 50 100 How the Solver works

  7. Netherlands 500 Amsterdam 500 * 500 800 The Hague * Germany 800 0 500 Tilburg 500 * 400 700 * Antwerp 200 Leipzig 700 * 400 Belgium 400 * Liege 200 200 800 100 Nancy * 900 900 Miles 0 50 100 A Basic Feasible Solution

  8. Netherlands Amsterdam 500 * 800 The Hague * Germany 500 Tilburg * 700 * Antwerp Leipzig * Belgium 400 * Liege 200 Nancy * 900 Miles 0 50 100 Finding an Entering Variable 500 800 500 400 700 200 900

  9. Netherlands 500 Amsterdam 500 * 800 The Hague * Germany 800 500 Tilburg 500 * 400 700 * Antwerp Leipzig 700 * Belgium 400 * Liege 200 200 Nancy * 900 900 Miles 0 50 100 Finding an Entering Variable

  10. Netherlands Amsterdam 500 * $122 800 The Hague * Germany 500 Tilburg * 700 * Antwerp Leipzig * Belgium 400 * Liege 200 Nancy * 900 Miles 0 50 100 Computing Reduced Cost

  11. Netherlands Amsterdam 500 * $122 800 The Hague * Germany 500 Tilburg * 700 * Antwerp Leipzig * $100 400 * Liege 200 Nancy * 900 Miles 0 50 100 Computing Reduced Cost

  12. Netherlands Amsterdam 500 * $122 800 The Hague * Germany 500 Tilburg * 700 * Antwerp Leipzig * $100 400 * Liege 200 $40 Nancy * 900 Miles 0 50 100 Computing Reduced Cost

  13. Netherlands Amsterdam 500 * $122 800 The Hague * Germany 500 Tilburg * 700 * Antwerp Leipzig * $100 400 * Liege 200 $90 $40 Nancy * 900 Miles 0 50 100 Computing Reduced Cost Costs$122 $ 40 $162 Saves$100 $ 90 $190 Net$28

  14. Netherlands Amsterdam 500 * 0 800 The Hague * Germany 500 Tilburg * 700 * Antwerp Leipzig * 200 400 * Liege 200 800 100 Nancy * 900 Miles 0 50 100 Finding a Leaving Variable Red flows decrease. Green flows increase. Leaving variable is first to reach 0

  15. Netherlands 500 Amsterdam 500 * 800 The Hague * Germany 800 500 Tilburg Tilburg 500 * 400 700 * Antwerp Leipzig 700 * 400 * Liege 200 200 Nancy * 900 900 Miles 0 50 100 New Basic Feasible Solution

  16. Netherlands 500 Amsterdam 500 * 500 800 The Hague * Germany 800 500 Tilburg Tilburg 500 * 400 700 * Antwerp Leipzig 700 * 200 400 300 400 * Liege 200 200 600 Nancy * 900 900 Miles 0 50 100 New Basic Feasible Solution

  17. Quantity Discounts • Minimize Cost $3 Total Cost $4 Shipment Size

  18. Crossdocks and Warehouses

  19. Flow Balance • At the DCs • Flow into the DC - Flow out of the DC = 0 • At the Plants • Flow out of Plant - Flow into the Plant  Supply • At the Customers • Flow into the Cust. - Flow out of the Cust.  Demand

  20. A Solver Model

  21. Network Flow Models • Variables are flows of a single homogenous commodity • Constraints are • Net flow  Supply/Demand • Lower Bound  Flow on arc  Upper Bound • Theorem: If the data are integral, any solution solver finds will be integral as well.

  22. An Important Special Case • One unit available at one plant • One unit required at one customer • Minimizing the cost of shipping is....

More Related