Managerial Decision Modeling with Spreadsheets

1. Managerial Decision Modeling with Spreadsheets Chapter 5 Transportation, Assignment, and Network Models

2. Learning Objectives • Structure special LP network flow models. • Set up and solve transportation models using Excel’s Solver to determine minimum cost shipping routes in a network. • Extend basic transportation model to include transshipment points. • Set up and solve facility location and other application problems as transportation models. • Set up and solve maximal-flow, shortest-path, and minimal-spanning tree network models.

3. 5.1 Introduction Chapter reviews following LP models and demonstrates set up, evaluation and optimal solution of following models using Excel’s Solver. • Transportation model. • Transshipment model. • Assignment model. • Maximal-flow model. • Shortest-path model. • Minimal-spanning tree model.

4. 5.2 Characteristics Of Network Flow Problems • Figure shows network with 5 nodes and 10 arcs. • Circles (numbered 1 to 5) in figure are called nodes. • A node may be defined as location of a specific point on the network. • An arc may be defined as line that connects two nodes to each other.

5. Transportation Model • Transportation problem deals with distribution of goods from several points of supply to a number of points of demand • Resources to be optimally allocated usually involve given capacity of goods at each source and a given requirement for the goods at each destination. • Most common objective of transportation problem is to schedule shipments from sources to destinations so that total production and transportation costs are minimized

6. Transshipment Model • Extension of transportation problem is called transshipment problem in which a point can have shipments that both arrive as well as leave. • Example would be a warehouse where shipments arrive from factories and then leave for retail outlets. • It may be possible for firm to achieve cost savings (economies of scale) by consolidating shipments from several factories at warehouse and then sending them together to retail outlets.

7. Assignment Model • Assignment problem refers to class of LP problems that involve determining most efficient assignment of: • People to projects, • Salespeople to territories, • Contracts to bidders, and • Jobs to machines, and so on. • Objective is to minimize total cost or total time of performing tasks at hand, although a maximization objective is also possible.

8. Maximal-flow Model • Network with specific starting point (called origin) and specific ending point (called destination). • Arcs in network have capacities that limit amounts of flow that can occur over them. Capacities can be different for different arcs. • A maximal-flow problem finds maximum flow that can occur from origin to destination through this network.

9. Shortest-path Model • Network with specified origin and specified destination. • Arcs in network are such that there are many paths available from origin to destination. • Shortest-path problem finds shortest path or route through this network from origin to destination.

10. Minimal-spanning Tree Model • Minimal-spanning tree problem determines path through network that connects all points. • Most common objective is to minimize total distance of all arcs used in path. • Example, when points represent houses in a subdivision, minimal-spanning tree model can be used to determine best way to connect all of houses to electrical power, water systems, and so on, in a way that minimizes total distance or length of power lines or water pipes.

11. 5.2 Characteristics Of Network Flow Problems Example of a Network Nodes Arcs • A node may be defined as location of specific point on network. (sources, destinations, or transshipment points ) • An arc may be defined as line that connects two nodes to each other.

12. Characteristics Of Network Flow Problems • In all network models, decision variables represent amount of flows (or shipments) that occur on unidirectional arcs in network • A flow balance constraint is written for each node in network: Net flow = (Total flow in to node) - (Total flow out of node) • At source nodes, total flow outof node will exceed total flow into node since goods are created at the node. • Coefficients for all flow balance constraints and most other problem-specific constraints in network models equal either 0 or 1. • If all supplies are at source nodes and all demands are at destination nodes, the solution results are integer values.

13. 5.3 Transportation Model LP Model of Executive Furniture’s Transportation Model Executive Furniture Corporation

14. 5.3 Transportation Model LP Model of Executive Furniture’s Transportation Model Executive Furniture Corporation Transportation Costs Per Desk

15. LP Transportation Model Formulation Executive Furniture Corporation Objective: minimize total shipping costs = 5 XDA + 4 XDB + 3 XDC + 3 XEA + 2 XEB + 1 XEC + 9 XFA + 7 XFB + 5 XFC Where: Xij = number of desks shipped from factory i to warehouse j i = D (for Des Moines), E (for Evansville), or F (for Fort Lauderdale). j = A (for Albuquerque), B (for Boston), or C (for Cleveland).

16. Supply Constraints Executive Furniture Corporation Net flow at Des Moines = (Total flow in) - (Total flow out) = (0) - (XDA + XDB + XDC) Net flow at Des Moines = -XDA - XDB - XDC = -100 (Des Moines capacity) and -XEA - XEB - XEC = -300 (Evansville capacity) -XFA - XFB - XFC = -300 (Fort Lauderdale capacity) Multiply each constraint by -1 and rewrite as: XDA + XDB + XDC = 100 (Des Moines capacity) XEA + XEB + XEC = 300 (Evansville capacity) XFA + XFB + XFC = 300 (Fort Lauderdale capacity)

17. Demand Constraints Executive Furniture Corporation Net flow at Albuquerque = (Total flow in) - (Total flow out) = (XDA + XEA + XFA) - (0) Net flow at Albuquerque = XDA + XEA + XFA = 300 (Albuquerque demand) and XDB + XEB + XFB = 200 (Boston demand) XDC + XEC + XFC = 200 (Cleveland demand)

18. Excel Layout and Solver Entries

19. The Optimum Solution SHIP: 100 desks from Des Moines to Albuquerque, 200 desks from Evansville to Boston, 100 desks from Evansville to Cleveland, 200 desks from Fort Lauderdale to Albuquerque, and 100 desks from Fort Lauderdale to Cleveland. Total shipping cost is $3,900.

20. Alternate Excel Layout for the Model

21. Solver Entries for Alternate Layout

22. Unbalanced Transportation Problems • If supplies do not equal demands, an unbalanced transportation model exists. • In an unbalanced transportation model, supply or demand constraints need to be modified. • There are two possible scenarios: • Total supply exceeds total requirement. • Total supply is less than total requirement.

23. Total Supply Exceeds Total Requirement Total flow out of Des Moines ( XDA + XDB + XDC) should be permitted to be smaller than total supply (100). The constraint should be written as: XDA + XDB + XDC <= 100 -XDA - XDB - XDC >= -100 (Des Moines capacity) -XEA - XEB - XEC >= -300 (Evansville capacity) -XFA - XFB - XFC >= -300 (Fort Lauderdale capacity)

24. Total Supply Less Than Total Requirement Total flow in to Albuquerque (that is, XDA + XEA + XFA) should be permitted to be smaller than total demand (namely, 300). This warehouse should be written as: XDA + XEA + XFA <= 300 (Albuquerque demand) XDB + XEB + XFB <= 200 (Boston demand) XDC + XEC + XFC <= 200 (Cleveland demand)

25. 5.4 Facility Location Analysis Hardgrave Machine Company - New Factory Location • Produces computer components at its plants in Cincinnati, Kansas City, and Pittsburgh. • Plants not able to keep up with demand for orders at four warehouses in Detroit, Houston, New York, and Los Angeles. • Firm has decided to build a new plant to expand its productive capacity. • Two sites being considered: • Seattle, Washington and • Birmingham, Alabama. • Both cities attractive in terms: labor supply, municipal services, and ease of factory financing.

26. Demand, Supply Data and Shipping Costs Hardgrave Machine Company

27. Shipping Costs Hardgrave Machine Company

28. Excel Layout and Solver Entries Hardgrave Machine Company

29. Excel Layout and Solver Entries Hardgrave Machine Company

30. 5.5 Transshipment Model Transshipment problem flows can occur both out of and into same node in three ways:  1. If total flow into a node is less than total flow out from node, node represents a net creator of goods (a supply point). - Flow balance equation will have a negative right hand side (RHS) value. 2. If total flow into a node exceeds total flow out from node, node represents a net consumer of goods, (a demand point). - Flow balance equation will have a positive RHS value. 3. If total flow into a node is equal to total flow out from node, node represents a pure transshipment point. - Flow balance equation will have a zero RHS value.

31. Executive Furniture Corporation - Revisited • Assume it is possible for Executive Furniture to ship desks from Evansville factory to its three warehouses at very low unit shipping costs. • Considering shipping all desks produced at other two factories (Des Moines and Fort Lauderdale) to Evansville. • Considering using new shipping company to move desks from Evansville to all its warehouses.

32. Executive Furniture Corporation - Revisited • Revised unit shipping costs are shown here. • Note Evansville factory shows up in both the “From” and “To” entries.

33. LP Model for Transshipment Problem Executive Furniture Company • Two new additional decision variables for new shipping routes. XDE = number of desks shipped from Des Moines to Evansville XFE = number of desks shipped from Fort Lauderdale to Evansville Objective Function: minimize total shipping costs = 5XDA + 4XDB + 3XDC + 2XDE + 3XEA + 2XEB + +1XEC + 9XFA + 7XFB + 5XFC + 3XFE

34. Constraints Executive Furniture Company • Relevant flow balance equations written as: (0) - (XDA + XDB + XDC + XDE) = -100 (Des Moines capacity) (0) - (XFA + XFB + XFC + XFE) = -300 (Fort Lauderdale capacity) • Supplies have been expressed as negative numbers in the RHS.  Net flow at Evansville = (Total flow in) - (Total flow out) = (XDE + XFE) - (XEA + XEB + XEC) • Net flow equals total number of desks produced (the supply) at Evansville. Net flow at Evansville = (XDE + XFE) - (XEA + XEB + XEC) = -100 • No change in demand constraints for warehouse requirements:  XDA + XEA + XFA = 300 (Albuquerque demand) XDB + XEB + XFB = 200 (Boston demand) XDC + XEC + XFC = 200 (Cleveland demand)

35. Excel Solution

36. 5.6 Assignment Model • Rows denote people or objects to be assigned, and columns denote tasks or jobs assigned. • Numbers in table are costs associated with each particular assignment. Fix-It Shop Example - • Received three new rush projects to repair: (1) a radio, (2) a toaster oven, and (3) a broken coffee table. • Three workers (each has different talents and abilities). • Estimated costs to assign each worker to each of the three projects.

37. 5.6 Assignment Model Fix-It Shop • Rows denote people or objects to be assigned, and columns denote tasks or jobs assigned. • Numbers in table are costs associated with each particular assignment.

38. Assignment Alternatives and Costs Fix-It Shop • Owner's objective is to assign three projects to workers in a way that result is lowest total cost.

39. Assignment Alternatives and Costs Fix-It Shop • Owner's objective is to assign three projects to workers in a way that results in lowest total cost.

40. Solving Assignment Models Fix-It Shop Formulate LP model - Xij = “Flow” on arc from node denoting worker i to node denoting projectj. Solution value will equal 1 if workeri is assigned to projectj : i= A (for Adams), B (for Brown), or C (for Cooper) j = 1 (for project 1), 2 (for project 2), or 3 (for project 3) Objective Function: minimize total assignment cost = 11XA1 + 14XA2 + 6XA3 + 8XB1 + 10XB2 + 11XB3 + + 9XC1 + 12XC2 + 7XC3

41. Constraints Fix-It Shop Constraints expressed using standard convention flow balance equations are as follows: -XA1 - XA2 - XA3 = -1 (Adams availability) -XB1 - XB2 - XB3 = -1 (Brown availability) -XC1 - XC2 - XC3 = -1 (Cooper availability) XA1 + XB1 + XC1 = 1 (Project 1 requirement) XA2 + XB2 + XC2 = 1 (Project 2 requirement) XA3 + XB3 + XC3 = 1 (Project 3 requirement)

42. Excel Layout and Solver Entries

43. 5.7 Maximal-flow Model • Maximal-flow model allows one to determine maximum amount of a material that can flow through a network. Example: Road Network in Waukesha, Wisconsin • Find maximum number of automobiles that can flow through a state highway road system. • In process of developing a road system for downtown area. • Planner wants to determine maximum number of cars that can flow through town from west to east.

44. Road Network in Waukesha, Wisconsin • Numbers by the nodes indicate maximum number of cars (in hundreds of cars per hour) that can flow (or travel) fromthe various nodes. • Example: number 3 by node 1 (on the road from node 1 to node 2) indicates that 300 cars per hour can travel fromnode 1 to node 2. • Traffic can flow in both directions down a road. • A zero (0) means no flow in that direction, or a one-way road.

45. Road Network in Waukesha, Wisconsin • Number 3 by node 1 (on road from node 1 to node 2) indicates that 300 cars per hour can travel fromnode 1 to node 2.

46. LP Model for Maximal-flow Problem Waukesha Road System • Replace each two-way (bi-directional) road with two one-way (unidirectional) roads with flows in opposite directions. • Some unidirectional roads (example, road from node 4 to node 1, road from node 6 to node 5, etc.) are not needed since maximum flow permissible in that direction is zero ( since it is a one-way road). • Revised network for Waukesha has 15 unidirectional roads (roads 1->2, 1->3, 1->4, 2->1, 2->4, 2->6, 3->4, 3->5, 4->2, 4->3, 4->6, 5->3, 5->6, 6->2, and 6->4).

47. LP Model Formulation Waukesha Road System Xij = Number of cars that flow (or travel) per hour on road from node ito node j , where - i = 1, 2, 3, 4, 5, or 6 (only roads that actually exist are defined). j = 1, 2, 3, 4, 5, or 6 (only roads that actually exist are defined). • Determine maximum number of cars that can originate at node 1 and terminate at node 6. • Node 1 is the source node in this model and node 6 is the destination node. • All others nodes (nodes 2 to 5) are transshipment nodes where flows of cars neither start nor end.

48. LP Model Formulation Waukesha Road System • Modify network to set up and solve the maximal flow model using LP. • There is neither a known quantity of "supply" of cars available at node 1, nor is there a known quantity of "demand" of cars required at node 6. • Modification consists of creating a unidirectional dummy arc (road) going from the destination node (node 6) to the source node (node 1). • Capacity of dummy arc is set at infinity (or any artificially high number such as 1000) for the Waukesha problem.

49. LP Model Formulation Waukesha Road System • Modification consists of creating a unidirectional dummy arc (road) going from the destination node (node 6) to source node (node 1). • The capacity of this dummy arc is set at infinity.

50. Objective Function Waukesha Road System • Assume an unknown number of cars flowing on dummy road from node 6 to node 1. • No supply at node 6 - entire number of cars flowing out of node 6 (on road 6->1) must consist of cars that flowed into node 6. • No demand at node 1 - entire number of cars on road 6->1 must consist of cars that originally flowed out of node 1 (to nodes 2, 3, and 4). • If one maximizes number of cars flowing on dummy road 6->1, equivalent to maximizing total number of cars flowing out of node 1 as well as the total number of cars flowing into node 6. • Objective: maximize total number of cars flowing into node 6 =Maximize X61