Spreadsheet Modeling Decision Analysis A Practical Introduction to

Spreadsheet Modeling & Decision Analysis: A Practical Introduction to Management Science, 3 e by Cliff Ragsdale 1

Chapter 5 Network Modeling Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 2

Introduction u. A number of business problems can be represented graphically as networks. u This chapter focuses on several types of network flow problems: – – – Transshipment Problems Shortest Path Problems Maximal Flow Problems Transportation/Assignment Problems Generalized Network Flow Problems u We also consider a different type of network problem called the Minimum Spanning Tree Problem Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 3

Characteristics of Network Flow Problems Network flow problems can be represented as a collection of nodes connected by arcs. u There are three types of nodes: – Supply – Demand – Transshipment u We’ll use negative numbers to represent supplies and positive numbers to represent demand. u Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 4

A Transshipment Problem: The Bavarian Motor Company +100 Boston 2 $50 +60 $30 Newark 1 -200 Columbus $40 3 $40 $35 +170 Atlanta 5 $30 Richmond +80 4 $25 $45 $35 +70 Mobile 6 $50 J'ville 7 -300 Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 5

Defining the Decision Variables For each arc in a network flow model we define a decision variable as: Xij = the amount being shipped (or flowing) from node i to node j For example, X 12 = the number of cars shipped from node 1 (Newark) to node 2 (Boston) X 56 = the number of cars shipped from node 5 (Atlanta) to node 6 (Mobile) Note: The number of arcs determine the number of variables in a network flow problem! Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 6

Defining the Objective Function Minimize total shipping costs. MIN: 30 X 12 + 40 X 14 + 50 X 23 + 35 X 35 +40 X 53 + 30 X 54 + 35 X 56 + 25 X 65 + 50 X 74 + 45 X 75 + 50 X 76 Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 7

Constraints for Network Flow Problems: The Balance-of-Flow Rules For Minimum Cost Network Flow Problems Where: Total Supply > Total Demand Total Supply < Total Demand Total Supply = Total Demand Apply This Balance-of-Flow Rule At Each Node: Inflow-Outflow >= Supply or Inflow-Outflow <=Supply or Inflow-Outflow = Supply or Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 8

Defining the Constraints u In the BMC problem: Total Supply = 500 cars Total Demand = 480 cars u So for each node we need a constraint of the form: Inflow - Outflow >= Supply or Demand u Constraint for node 1: –X 12 – X 14 >= -200 u This (there is no inflow for node 1!) is equivalent to: +X 12 + X 14 <= 200 Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 9

Defining the Constraints u Flow constraints –X 12 – X 14 >= – 200 +X 12 – X 23 >= +100 +X 23 + X 53 – X 35 >= +60 + X 14 + X 54 + X 74 >= +80 + X 35 + X 65 + X 75 – X 53 – X 54 – X 56 >= +170 + X 56 + X 76 – X 65 >= +70 –X 74 – X 75 – X 76 >= – 300 } node 1 } node 2 } node 3 } node 4 } node 5 } node 6 } node 7 u Nonnegativity conditions Xij >= 0 for all ij Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 10

Implementing the Model See file Fig 5 -2. xls Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 11

Optimal Solution to the BMC Problem +100 Boston 2 $50 Newark 1 120 20 +60 $30 Columbus 80 3 -200 $40 40 +170 Richmond +80 4 Atlanta 5 $45 +70 Mobile 6 210 70 $50 J'ville 7 -300 Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 12

The Shortest Path Problem u Many decision problems boil down to determining the shortest (or least costly) route or path through a network. – Ex. Emergency Vehicle Routing u This is a special case of a transshipment problem where: – there is a supply node with a supply of -1 – there is a demand node with a demand of +1 – all other nodes have supply/demand of +0 Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 13

The American Car Association +0 L'burg 9 11 2. 0 hrs 9 pts 1. 7 hrs 5 pts +0 2. 0 hrs 4 pts 4. 7 hrs 9 pts K'ville 5 2. 8 hrs 7 pts +0 3. 0 hrs 4 pts A'ville 6 Chatt. 3 +0 G'boro 1. 5 hrs 3 pts Charl. 7 2. 0 hrs 8 pts G'ville 4 Atlanta 1. 1 hrs 3 pts 8 1. 7 hrs 4 pts 3. 0 hrs 4 pts +1 Va Bch 5. 0 hrs 9 pts +0 3. 3 hrs 5 pts 2. 7 hrs 4 pts Raliegh 10 +0 2. 3 hrs 3 pts +0 1. 5 hrs 2 pts +0 2. 5 hrs B'ham 2 3 pts 2. 5 hrs 1 Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. -1 3 pts +0 14

Solving the Problem u There are two possible objectives for this problem – Finding the quickest route (minimizing travel time) – Finding the most scenic route (maximizing the scenic rating points) See file Fig 5 -7. xls Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 15

The Equipment Replacement Problem u The problem of determining when to replace equipment is another common business problem. u It can also be modeled as a shortest path problem. Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 16

The Compu-Train Company u u u Compu-Train provides hands-on software training. Computers must be replaced at least every two years. Two lease contracts are being considered: – Each required $62, 000 initially – Contract 1: v Prices increase 6% per year v 60% trade-in for 1 year old equipment v 15% trade-in for 2 year old equipment – Contract 2: v Prices increase 2% per year v 30% trade-in for 1 year old equipment v 10% trade-in for 2 year old equipment Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 17

Network for Contract 1 +0 2 +0 $63, 985 4 $30, 231 $28, 520 $33, 968 $32, 045 -1 1 $60, 363 3 $67, 824 5 +1 +0 Cost of trading after 1 year: 1. 06*$62, 000 - 0. 6*$62, 000 = $28, 520 Cost of trading after 2 years: 1. 062*$62, 000 - 0. 15*$62, 000 = $60, 363 etc, etc…. Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 18

Solving the Problem See file Fig 5 -12. xls Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 19

Transportation & Assignment Problems u Some network flow problems don’t have transshipment nodes; only supply and demand nodes. Supply 275, 000 Groves Distances (in miles) 21 Mt. Dora 1 Processing Plants Capacity Ocala 4 50 200, 000 40 400, 000 Eustis 2 35 30 Orlando 5 22 600, 000 55 300, 000 u 20 Clermont 3 Leesburg 25 6 225, 000 These problems are implemented more effectively using the technique described in Chapter 3. Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 20

Generalized Network Flow Problems u In some problems, a gain or loss occurs in flows over arcs. – Examples v Oil or gas shipped through a leaky pipeline v Imperfections in raw materials entering a production process v Spoilage of food items during transit v Theft during transit v Interest or dividends on investments u These problems require some modeling changes. Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 21

Coal Bank Hollow Recycling Process 1 Recycling Process 2 Material Cost Newspaper $13 Mixed Paper $11 White Office Paper Cardboard $13 Yield 90% 80% $9 75% Cost $12 $13 95% $14 Newsprint Pulp Source Recycling Process 1 Recycling Process 2 Demand Cost Yield $5 95% $6 90% 60 tons Yield 85% $10 85% Supply 70 tons 50 tons 90% 30 tons 40 tons Packaging Paper Print Stock Cost Yield $6 90% $8 95% 40 tons Cost Yield $8 90% $7 95% 50 tons Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 22

Network for Recycling Problem -70 Newspaper $13 1 $12 -50 Mixed paper 2 -30 White office paper 3 $11 80% 5 75% $9 85% $10 90% -40 Cardboard 85% Newsprint pulp +60 7 90% $6 $8 90% 95% 85% Packing paper pulp +40 Print stock pulp +50 8 $6 Recycling Process 2 6 $13 $5 Recycling Process 1 95% $13 95% +0 90% $8 90% $7 +0 95% 9 $14 4 Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 23

Defining the Objective Function Minimize total cost. MIN: 13 X 15 + 12 X 16 + 11 X 25 + 13 X 26 + 9 X 35+ 10 X 36 + 13 X 45 + 14 X 46 + 5 X 57 + 6 X 58 + 8 X 59 + 6 X 67 + 8 X 68 + 7 X 69 Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 24

Defining the Constraints u Raw Materials -X 15 -X 16 >= -70 -X 25 -X 26 >= -50 -X 35 -X 36 >= -30 -X 45 -X 46 >= -40 u } node 1 } node 2 } node 3 } node 4 Recycling Processes +0. 9 X 15 +0. 8 X 25 +0. 95 X 35 +0. 75 X 45 -X 57 -X 58 -X 59 >= 0 } node 5 +0. 85 X 16 +0. 85 X 26 +0. 9 X 36 +0. 85 X 46 -X 67 -X 68 -X 69 >= 0 } node 6 u Paper Pulp +0. 95 X 57 +0. 90 X 67 >= 60 } node 7 +0. 90 X 57 +0. 95 X 67 >= 40 } node 8 +0. 90 X 57 Decision +0. 95 X >= 50 } node 9 Spreadsheet Modeling and Analysis, 67 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 25

Implementing the Model See file Fig 5 -17. xls Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 26

The Maximal Flow Problem u In some network problems, the objective is to determine the maximum amount of flow that can occur through a network. u The arcs in these problems have upper and lower flow limits. u Examples – How much water can flow through a network of pipes? – How many cars can travel through a network of streets? Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 27

The Northwest Petroleum Company Pumping Station 3 Pumping Station 1 3 2 6 1 4 Oil Field Refinery 6 2 4 4 3 5 5 Pumping Station 2 Pumping Station 4 Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 28

The Northwest Petroleum Company Pumping Station 3 Pumping Station 1 3 2 6 1 4 Oil Field Refinery 6 2 4 4 3 5 5 Pumping Station 2 Pumping Station 4 Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 29

Formulation of the Max Flow Problem MAX: Subject to: X 61 +X 61 - X 12 - X 13 = 0 +X 12 - X 24 - X 25 = 0 +X 13 - X 34 - X 35 = 0 +X 24 + X 34 - X 46 = 0 +X 25 + X 35 - X 56 = 0 +X 46 + X 56 - X 61 = 0 with the following bounds on the decision variables: 0 <= X 12 <= 6 0 <= X 25 <= 2 0 <= X 46 <= 6 0 <= X 13 <= 4 0 <= X 34 <= 2 0 <= X 56 <= 4 0 <= X 24 <= 3 0 <= X 35 <= 5 0 <= X 61 <= inf Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 30

Implementing the Model See file Fig 5 -22. xls Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 31

Optimal Solution Pumping Station 1 3 3 2 5 2 6 Pumping Station 3 4 6 2 Oil Field 1 5 6 Refinery 2 4 4 3 Pumping Station 2 5 5 4 2 Pumping Station 4 Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 32

Special Modeling Considerations -100 1 2 $3 +0 3 $5 $4 $3 $4 $5 $5 4 $6 5 +75 6 +50 +0 Suppose the total flow into nodes 3 & 4 must be at least 50 and 60, respectively. One way to achieve this without side constraints is shown on the following slide. Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 33

Special Modeling Considerations -100 1 $3 +0 30 $4 +0 L. B. =50 3 2 $5 5 +75 6 +50 $3 $4 -100 $5 $5 40 +0 L. B. =60 4 $6 +0 Nodes 30 & 40 accumulate the total flow into nodes 3 & 4, respectively. Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 34

Special Modeling Considerations $8 -75 1 2 $6 +50 U. B. = 35 Two two (or more) arcs cannot share the same beginning and ending nodes. Instead, try. . . +0 10 $0 -75 1 $8 $6 2 +50 U. B. = 35 Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 35

The Minimal Spanning Tree Problem u For a network with n nodes, a spanning tree is a set of n-1 arcs that connects all the nodes and contains no loops. u The minimal spanning tree problem involves determining the set of arcs that connects all the nodes at minimum cost. Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 36

Minimal Spanning Tree Example: Windstar Aerospace Company $150 2 $100 4 $85 $75 $40 1 $85 $80 5 $90 3 $50 $65 6 Nodes represent computers in a local area network. Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 37

The Minimal Spanning Tree Algorithm 1. Select any node. Call this the current subnetwork. 2. Add to the current subnetwork the cheapest arc that connects any node within the current subnetwork to any node not in the current subnetwork. (Ties for the cheapest arc can be broken arbitrarily. ) Call this the current subnetwork. 3. If all the nodes are in the subnetwork, stop; this is the optimal solution. Otherwise, return to step 2. Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 38

Solving the Example Problem - 1 4 2 $100 $85 1 $85 $80 5 $90 3 6 Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 39

Solving the Example Problem - 2 4 2 $100 $85 1 $85 $75 $80 5 $90 3 $50 6 Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 40

Solving the Example Problem - 3 4 2 $100 $85 1 $75 $80 5 $85 3 $50 $65 6 Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 41

Solving the Example Problem - 4 4 2 $100 $85 $75 $40 1 $80 3 5 $50 $65 6 Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 42

Solving the Example Problem - 5 $150 2 4 $85 $75 $40 1 $80 3 5 $50 $65 6 Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 43

Solving the Example Problem - 6 4 2 $75 $40 1 $80 3 5 $50 $65 6 Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 44

End of Chapter 5 Spreadsheet Modeling and Decision Analysis, 3 e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning. 45