Example 5.3

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 company’s two warehouses and then to the customers. • A network representation of RedBrand’s 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.

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 A18:B43.

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 D18:D3. 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 RedBrand’s 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 outputs:total 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,ArcCapacity) 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.

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.

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 formula=SUMIF(Origins,H25,Flows)- ShrinkFactor*SUMIF(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.