More General Logistics Models

1
Example 5.3

• More General Logistics Models

2
Background Information

• The RedBrand Company produces tomato products at
  three plants.
• These products can be shipped directly to their
  two customers or they can first be shipped to the
  companys two warehouses and then to the
  customers.
• A network representation of RedBrands problem
  appears on the next slide.

3
Background Information -- continued
4
Background Information -- continued

• We see that nodes 1, 2, and 3 represent the
  plants(suppliers, denoted by S for supplier),
  nodes 4 and 5 represent the warehouses
  (transshipment points, denoted by T), and nodes 6
  and 7 represent the customers (demanders, denoted
  by D for demander).
• Note that we allow the possibility of some
  shipments among plants, among warehouses and
  among customers. Also note that some arcs have
  arrows on both ends. This means that flow is
  allowed in either direction.

5
Background Information -- continued

• The cost of producing food at each plant is the
  same, so RedBrand is concerned with minimizing
  the total shipping cost incurred in meeting
  customer demands.
• The production capacity of each plant (in tons
  per year) and the demand of each customer are
  shown in the network representation.
• The cost of shipping a ton of food (In thousands
  of dollars) between each pair of points is given
  in the table on the next slide, where a dash
  indicates that RedBrand cannot ship along that
  arc.

6
Background Information -- continued

• We also assume that at most 200 tons of food can
  be shipped between any two nodes.
• RedBrand wants to determine a minimum-cost
  shipping schedule.

7
Solution

• We need to keep track of the following
• Amount shipped along each arc of the network
• Total amount shipped into each node (the inflow)
• Total amount shipped out of each node (the
  outflow)
• Total shipping cost.

8
REDBRAND1.XLS

• To set up the spreadsheet model, proceed as
  follows using this file as a starting point.
• See the figure on the next slide for a look at
  the model. Also, refer to the network
  representation shown earlier.

9
(No Transcript)
10
Developing the Model

• The steps are
• Input data. Enter the unit shipping cost (in
  thousands of dollars) in the CostMatrix range,
  the common arc capacity in the ArcCapacity cell,
  the supply capacities in the Capacities range,
  and the demands in the Demands range. Note that
  we have shaded the cells in the CostMatrix range
  that do not correspond to arcs in the network. No
  costs are entered in these cells.
• Origin and destination indexes. Enter the indexes
  (1 to 7) for the origins and destinations of the
  various arcs in the range A18B43.

11
Developing the Model -- continued

• Shipping costs on arcs. To transfer the cost data
  in the CostMatrix range to the UnitCosts range,
  enter the formula INDEX(CostMatrix,A18,B18) in
  cell C18 and copy it down column C.
• Flow on arcs. Enter any initial values for the
  flows in the range D18D3. These flows are the
  changing cells.
• Arc capacities. To indicate a common arc capacity
  for all arcs, enter the formula ArcCapacity in
  cell F18 and copy it down column F.
• Flow balance constraints. Nodes 1, 2, and 3 are
  net suppliers, nodes 4 and 5 are transshipment
  points, and nodes 6 and 7 are net demanders.

12
Developing the Model -- continued

• Therefore, set up the left sides of the flow
  balance constraints appropriately for these three
  cases. Specifically, enter the net outflow for
  node 1 in cell I19 with the formula
  SUMIF(Origins,H19,Flows)-SUMIF(Dests,H19,Flows)
  and copy it down to cell I21. Note how this
  formula subtracts flows into node 1 from flows
  out of node 1 to obtain net outflow for node 1.
  Next, copy this same formula to cells I25 and I26
  for the warehouses. Finally, enter the net inflow
  for node 6 in cell I30 with the formula
  SUMIF(Dests,H30,Flows)-SUMIF(Origins,H30,Flows)
  and copy it to cell I31. This formula subtracts
  flows out of node 6 from flows into node 6 to
  obtain the net inflow for node 6.

13
Developing the Model -- continued

• Total shipping cost. Calculate the total shipping
  cost (in thousands of dollars) in the TotCost
  cell with the formula SUMPRODUCT(UnitCosts,Flows)
  .
• Using the Solver The Solver dialog box should
  be filled in as shown on the next slide.

14
Developing the Model -- continued

• We want to minimize total shipping costs, subject
  to the three types of flow balance constraints
  and the arc capacity constraints

15
Solution -- continued

• In the optimal solution shown we see that
  RedBrands customer demand can be satisfied with
  a shipping cost of 3,260,000.
• This solution appears graphically here.

16
Solution -- continued

• Note in particular that plant 1 produces 180 tons
  and ships it all to plant 3, not directly to
  warehouses or customers.
• Also, note that all shipments from the warehouses
  go directly to customer 1. Then customer 1 ships
  180 tons to customer 2.
• We purposely chose unit shipping costs to produce
  this type of behavior, just to show that it can
  happen.
• As you can see, the costs of shipping from plant
  1 directly to warehouses or customers are
  relatively large compared to shipping directly to
  plant 3.

17
Solution -- continued

• Similarly, the costs of shipping from plants or
  warehouses directly to customer 2 are
  prohibitive.
• Therefore, we ship to customer 1 and let customer
  1 forward some of its shipment to customer 2.

18
Sensitivity Analysis

• How much effect does the arc capacity have on
  optimal solution?
• Currently, we see that three of the arcs with
  positive flow are at the arc capacity of 200.
• We can use SolverTable to see how sensitive this
  number and the total cost are to the arc
  capacity.
• In this case the single input cell is the
  ArcCapacity cell. We will vary it from 150 to 300
  in increments of 25, and we will keep track of
  two outputstotal cost and the number of arcs at
  arc capacity.

19
Sensitivity Analysis -- continued

• When we want to keep track of an output that does
  not already exist, we simply create it with an
  appropriate formula in a new cell before running
  SolverTable.
• This is shown in the table below.

20
Sensitivity Analysis -- continued

• The formula in cell C47 is COUNTIF(Flows,ArcCapac
  ity) which counts the arcs with flow equal to arc
  capacity.
• The SolverTable output shows what we could
  expect.
• As the arc capacity decreases, more flows bump up
  against it, and total cost increases.
• But even when the arc capacity is 300, two flows
  are constrained by it. In this sense, even this
  large an arc capacity costs RedBrand money.

21
Variations of the Model

• There are many variations of the RedBrand
  shipping problem that can be handled by a network
  formulation. We consider two possible variations.
• First, suppose RedBrand ships two products along
  the given network. We assume that the unit
  shipping costs are the same for either product,
  but the arc capacity represents the maximum flow
  of both products that can flow on any arc.
• In this sense the two products are competing for
  arc capacity. Each plant has a separate
  production capacity for each product and each
  customer has a separate demand for each product.

22
REDBRAND2.XLS

• This file is used for setting up the variation on
  the spreadsheet model. The figure on the next
  slide shows the spreadsheet model variation.
• Very little needs to be changed from the original
  model even the Solver dialog box stays the
  same.
• We need to
• have two columns of changing cells
• apply the previous logic to both products
  separately in the flow balance constraints
• apply the arc capacities to the total flows in
  column F.

23
(No Transcript)
24
REDBRAND3.XLS

• A second variation of the model is appropriate
  for perishable goods.
• This file is used for setting up the second
  variation on the spreadsheet model. The figure on
  the next slide shows the second spreadsheet model
  variation.
• We again assume that there is a single product,
  but that some percentage of the product that is
  shipped to warehouses perishes and cannot be sent
  on to customers.
• This means that the total inflow to a warehouse
  is greater than the total outflow from the
  warehouse.

25
(No Transcript)
26
Variations of the Model -- continued

• The shrinkage factor in the Shrinkfactor cell,
  the percentage that does not spoil in the
  warehouses, becomes a new input.
• It is then incorporated into the warehouse flow
  balance constraints by entering the
  formulaSUMIF(Origins,H25,Flows)-
  ShrinkFactorSUMIF(Dests,H25,Flows)in cell I25
  and copying it to cell I26.
• This formula says that what goes out is 90 of
  what goes in. The other 10 disappears. Of
  course, shrinkage results in a larger total cost
  about 50 larger than in the original
  RedBrand model.

27
Variations of the Model -- continued

• Interestingly, however, some units are still sent
  to both warehouses, and the entire capacity of
  all plants if now used.
• Finally, you can check that a feasible solution
  exists even for a shrinkage factor of 0.
• The solution then is to send everything directly
  from plants to customers at a steep cost.