Network Models with Excel

1
Network Models with Excel

• Simple Structure
• Intuition into solver
• Numerous applications
• Integral data means integral solutions

2
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
• gt, lt, (others for later)
• RHS is a cell reference or number.

6
How the Solver works
7
A Basic Feasible Solution
8
Finding an Entering Variable
500
800
500
400
700
200
900
9
Finding an Entering Variable
10
Computing Reduced Cost
11
Computing Reduced Cost
12
Computing Reduced Cost
13
Computing Reduced Cost
Costs122 40 162 Saves100 90 190 Net
28
14
Finding a Leaving Variable
Red flows decrease. Green flows increase. Leavi
ng variable is first to reach 0
15
New Basic Feasible Solution
16
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....