More General Logistics Models

1
Example 5.4

• More General Logistics Models

2
Background Information

• Excron Oil Company has a pipeline distribution
  network.
• Each node corresponds to a storage tank, and the
  numbers on the arcs represent flow capacities
  (per hour).
• Note that flows are allowed in both directions
  between some, but not all, of the tanks.

3
Background Information -- continued

• Excron wants to determine the maximum flow that
  can be sent from tank 1 to tank 8 per hour.
• The company is also considering doubling the
  capacity of all arcs leading out of tank 1 and
  all arcs leading into tank 8, and it wants to
  know whether this will allow it to double the
  maximum flow per hour from tank 1 to tank 8.

4
Solution

• Parts of this model are much like the general
  MCNFM.
• We must include the typical flow balance
  constraints (inflow equals outflow, except for
  the source and sink nodes), and we need the usual
  arc capacity constraints.
• But the difficulty is modeling the maximum flow
  objectivity appropriately.

5
Solution

• One approach is to note that the total flow
  through the network, for any set of flows, is the
  larger of the total outflow from node 1 and the
  total inflow to node 8.
• However, this would require us to use the MAX
  function in Excel, which Solver foes not handle
  well.
• An alternative method is to constrain the flows
  out of the source by the capacities on arcs
  leading out of the source, and then to maximize
  the sum of all flows into the sink. There are no
  costs in the model.

6
MAXFLOW.XLS

• The completed model and the corresponding Solver
  dialog box are shown on the next slide.
• This file can be used to create the model.
• Once you understand the role of the dummy arc,
  the rest of the model is very similar to the
  general MCNFM formulation, and can be completed
  with the steps that follow.

7
(No Transcript)
8
(No Transcript)
9
Developing the Model

• The steps are
• Enter arcs. Enter the information on all arcs in
  the network in the range A5B22.
• Flows. Enter any values for the flows in the
  Flows range.
• Capacities. Enter the given arc capacities in the
  range E5E22.
• Flows out of source and into sink. There are no
  flows into the source, so enter the total flow
  our of the source in cell H5 with the formula
  SUMIF(Origins,G5,Flows). Similarly, there are
  no flows out of the sink, so enter the total flow
  into the sink in cell H12 with the formula
  SUMIF(Dests,G12,Flows). This value is the total
  flow through the network that we will attempt to
  maximize.

10
Developing the Model -- continued

• Net outflows at intermediate nodes. Because there
  is flow balance at each intermediate node, we
  could set each nodes net outflow to 0 or its net
  inflow to 0. We do the former. Therefore, enter
  the formula SUMIF(Origins,G5,Flows)-SUMIF(Dests,G
  5,Flows) in cell H6 to calculate the net outflow
  for node 2, and copy this down for nodes 3-7.
  Note that the range named NetFlows corresponds to
  these intermediate nodes only.
• Using the Solver The Solver setup is
  straightforward. We maximize the total flow into
  the sink, with the Flows range as the changing
  cells, subject to the constraints
  FlowsltCapacities and NetOutflows0. Of course we
  also check the Assume Linear Model and Assume
  Non-Negative boxes.

11
Solution -- continued

• The optimal solution is represented graphically
  here, where only the arcs with positive flows are
  shown.
• In a sense, the intermediate arcs are not
  constraining. This is because the total flow of
  28, is the most we could hope to send into node
  8, given the capacities of the arcs leading into
  this node add to 28.

12
Sensitivity Analysis

• What if we could expand the capacities of the
  arcs leading out of the source and into the sink?
• Here, we actually answer a slightly more general
  question than Excron asked by using the
  SolverTable, as shown on the next slide.
• Instead of just doubling capacity, we allow the
  selected arcs capacities those in rows 5, 6,
  17, 20 and 22 to expand by any factor entered
  in the ExpFactor cell. For example the formula in
  cell E5 is ExpfactorModel!E5

13
(No Transcript)
14
Sensitivity Analysis -- continued

• Now we run SolverTable with cell ExpFactor as the
  single input cell, allowing it to vary from 1 to
  5 in increments of 1, and we keep track of the
  maximum flow.
• As we see, if capacity doubles in these arcs, the
  maximum flow also doubles. However, for larger
  expansion factors, other arc capacities prevent
  Excron from taking full advantage of the
  expansions.
• For example, if the arc capacities out of model 1
  and into node 8 triple, the maximum flow does not
  triple it increases only from 28 to 62. Also,
  any additional capacity expansion for these arcs
  has no effect whatsoever.