Spreadsheet Modeling Decision Analysis A Practical Introduction to
- Slides: 50
Spreadsheet Modeling & Decision Analysis A Practical Introduction to Management Science 5 th edition Cliff T. Ragsdale © 2007 South-Western College Publishing 1
Chapter 5 Network Modeling © 2007 South-Western College Publishing 2
Introduction § A number of business problems can be represented graphically as networks. § This chapter focuses on several such problems: – – – Transshipment Problems Shortest Path Problems Maximal Flow Problems Transportation/Assignment Problems Generalized Network Flow Problems The Minimum Spanning Tree Problem © 2007 South-Western College Publishing 3
Network Flow Problem Characteristics § Network flow problems can be represented as a collection of nodes connected by arcs. § There are three types of nodes: – Supply – Demand – Transshipment § We’ll use negative numbers to represent supplies and positive numbers to represent demand. © 2007 South-Western College Publishing 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 © 2007 South-Western College Publishing $50 J'ville 7 -300 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 # of cars shipped from node 1 (Newark) to node 2 (Boston) X 56 = the # of cars shipped from node 5 (Atlanta) to node 6 (Mobile) Note: The number of arcs determines the number of variables! © 2007 South-Western College Publishing 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 © 2007 South-Western College Publishing 7
Constraints for Network Flow Problems: The Balance-of-Flow Rules For Minimum Cost Network Apply This Balance-of-Flow Problems Where: Rule At Each Node: Total Supply > Total Demand Inflow-Outflow >= Supply or Demand Total Supply < Total Demand Inflow-Outflow <=Supply or Total Supply = Total Demand Inflow-Outflow = Supply or © 2007 South-Western College Publishing 8
Defining the Constraints § In the BMC problem: Total Supply = 500 cars Total Demand = 480 cars (Supply >= Demand) § For each node we need a constraint like this: Inflow - Outflow >= Supply or Demand § Constraint for node 1: –X 12 – X 14 >= – 200 (Note: there is no inflow for node 1!) § This is equivalent to: +X 12 + X 14 <= 200 © 2007 South-Western College Publishing 9
Defining the Constraints § 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 § Nonnegativity conditions Xij >= 0 for all ij © 2007 South-Western College Publishing 10
Implementing the Model See file Fig 5 -2. xls © 2007 South-Western College Publishing 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 © 2007 South-Western College Publishing J'ville 7 -300 12
The Shortest Path Problem § Many decision problems boil down to determining the shortest (or least costly) route or path through a network. – Ex. Emergency Vehicle Routing § This is a special case of a transshipment problem where: – There is one supply node with a supply of -1 – There is one demand node with a demand of +1 – All other nodes have supply/demand of +0 © 2007 South-Western College Publishing 13
The American Car Association +0 L'burg 9 11 2. 0 hrs 9 pts 1. 7 hrs 5 pts +0 -1 2. 8 hrs 7 pts +0 3. 0 hrs 4 pts A'ville 6 Chatt. 3 G'boro 1. 5 hrs 3 pts +0 Charl. 7 2. 0 hrs 8 pts 2. 5 hrs 3 pts 2 +0 2. 7 hrs 4 pts Raliegh 10 +0 2. 3 hrs 3 pts +0 1. 5 hrs 2 pts G'ville 4 Atlanta 1. 1 hrs 3 pts 8 1. 7 hrs 4 pts 3. 0 hrs 4 pts B'ham 1 2. 0 hrs 4 pts 4. 7 hrs 9 pts K'ville 5 +1 Va Bch 5. 0 hrs 9 pts +0 3. 3 hrs 5 pts +0 © 2007 South-Western College Publishing 14
Solving the Problem § 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 © 2007 South-Western College Publishing 15
The Equipment Replacement Problem § The problem of determining when to replace equipment is another common business problem. § It can also be modeled as a shortest path problem… © 2007 South-Western College Publishing 16
The Compu-Train Company § Compu-Train provides hands-on software training. § Computers must be replaced at least every two years. § Two lease contracts are being considered: – Each requires $62, 000 initially – Contract 1: ØPrices increase 6% per year Ø 60% trade-in for 1 year old equipment Ø 15% trade-in for 2 year old equipment – Contract 2: ØPrices increase 2% per year Ø 30% trade-in for 1 year old equipment Ø 10% trade-in for 2 year old equipment © 2007 South-Western College Publishing 17
Network for Contract 1 +0 +0 $63, 985 2 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…. © 2007 South-Western College Publishing 18
Solving the Problem See file Fig 5 -12. xls © 2007 South-Western College Publishing 19
Transportation & Assignment Problems § Some network flow problems don’t have transshipment nodes; only supply and demand nodes. Supply 275, 000 400, 000 Groves Distances (in miles) Processing Plants 21 Mt. Dora 1 50 Capacity Ocala 4 200, 000 40 These problems are implemented more effectively using the technique 35 30 Eustis described in Chapter 3. Orlando 600, 000 2 5 22 55 300, 000 20 Clermont 3 25 © 2007 South-Western College Publishing Leesburg 6 225, 000 20
Generalized Network Flow Problems § In some problems, a gain or loss occurs in flows over arcs. – Examples ØOil or gas shipped through a leaky pipeline ØImperfections in raw materials entering a production process ØSpoilage of food items during transit ØTheft during transit ØInterest or dividends on investments § These problems require some modeling changes. © 2007 South-Western College Publishing 21
Coal Bank Hollow Recycling Process 1 Material Cost Newspaper $13 Mixed Paper $11 White Office Paper Cardboard $13 Yield 90% 80% $9 75% Process 2 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 © 2007 South-Western College Publishing 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 4 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 © 2007 South-Western College Publishing 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 © 2007 South-Western College Publishing 24
Defining the Constraints-I § Raw Materials -X 15 -X 16 >= -70 -X 25 -X 26 >= -50 -X 35 -X 36 >= -30 -X 45 -X 46 >= -40 } node 1 } node 2 } node 3 } node 4 © 2007 South-Western College Publishing 25
Defining the Constraints-II § 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 © 2007 South-Western College Publishing 26
Defining the Constraints-III § 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 + 0. 95 X 67 >= 50 } node 9 © 2007 South-Western College Publishing 27
Implementing the Model See file Fig 5 -17. xls © 2007 South-Western College Publishing 28
Important Modeling Point § In generalized network flow problems, gains and/or losses associated with flows across each arc effectively increase and/or decrease the available supply. § This can make it difficult to tell if the total supply is adequate to meet the total demand. § When in doubt, it is best to assume the total supply is capable of satisfying the total demand use Solver to prove (or refute) this assumption. © 2007 South-Western College Publishing 29
The Maximal Flow Problem § In some network problems, the objective is to determine the maximum amount of flow that can occur through a network. § The arcs in these problems have upper and lower flow limits. § Examples – How much water can flow through a network of pipes? – How many cars can travel through a network of streets? © 2007 South-Western College Publishing 30
The Northwest Petroleum Company Pumping Station 3 Pumping Station 1 3 2 6 1 4 Oil Field Refinery 6 2 4 4 3 5 Pumping Station 2 5 Pumping Station 4 © 2007 South-Western College Publishing 31
The Northwest Petroleum Company Pumping Station 3 Pumping Station 1 3 2 6 1 4 Oil Field Refinery 6 2 4 4 3 Pumping Station 2 5 5 Pumping Station 4 © 2007 South-Western College Publishing 32
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 © 2007 South-Western College Publishing 33
Implementing the Model See file Fig 5 -24. xls © 2007 South-Western College Publishing 34
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 Pumping Station 4 © 2007 South-Western College Publishing 4 35
Special Modeling Considerations: Flow Aggregation -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. How would you model this? © 2007 South-Western College Publishing 36
Special Modeling Considerations: Flow Aggregation -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 aggregate the total flow into nodes 3 & 4, respectively. © 2007 South-Western College Publishing 37
Special Modeling Considerations: Multiple Arcs Between Nodes $8 -75 1 $6 2 +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 © 2007 South-Western College Publishing 38
Special Modeling Considerations: Capacity Restrictions on Total Supply -100 +75 $5, UB=40 1 3 $4, UB=30 $6, UB=35 2 -100 $3, UB=35 4 +80 Supply exceeds demand, but the upper bounds prevent the demand from being met. © 2007 South-Western College Publishing 39
Special Modeling Considerations: Capacity Restrictions on Total Supply -100 1 $999, UB=100 +200 +75 $5, UB=40 3 $4, UB=30 0 $6, UB=35 $999, UB=100 2 -100 $3, UB=35 4 +80 Now demand exceeds supply. As much “real” demand as possible will be met in the least costly way. © 2007 South-Western College Publishing 40
The Minimal Spanning Tree Problem § 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. § The minimal spanning tree problem involves determining the set of arcs that connects all the nodes at minimum cost. © 2007 South-Western College Publishing 41
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. © 2007 South-Western College Publishing 42
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. © 2007 South-Western College Publishing 43
Solving the Example Problem - 1 4 2 $100 $85 1 $85 $80 5 $90 3 6 © 2007 South-Western College Publishing 44
Solving the Example Problem - 2 4 2 $100 $85 1 $85 $75 $80 5 $90 3 $50 6 © 2007 South-Western College Publishing 45
Solving the Example Problem - 3 4 2 $100 $85 1 $75 $80 5 $85 3 $50 $65 6 © 2007 South-Western College Publishing 46
Solving the Example Problem - 4 4 2 $100 $85 $75 $40 1 $80 3 5 $50 $65 6 © 2007 South-Western College Publishing 47
Solving the Example Problem - 5 $150 2 4 $85 $75 $40 1 $80 3 5 $50 $65 6 © 2007 South-Western College Publishing 48
Solving the Example Problem - 6 4 2 $75 $40 1 $80 3 5 $50 $65 6 © 2007 South-Western College Publishing 49
End of Chapter 5 © 2007 South-Western College Publishing 50
- What is a spreadsheet model
- Cliff ragsdale spreadsheet modeling solutions
- No decision snap decision responsible decision
- Dividend decision in financial management
- Developing spreadsheet-based decision support systems
- Modeling role modeling theory
- Relational vs dimensional data modeling
- Electronic spreadsheet
- Introduction to spreadsheet ppt
- Sequential decision analytics
- Modeling logic with decision tables
- Modeling logic with decision tables
- Modeling logic with decision tables
- Explain the decision tree modeling for capacity expansion
- Decision tree approach
- Introduction to decision analysis
- Decision tree and decision table examples
- Introduction to the unified modeling language
- Introduction to modeling and simulation
- Pengertian pemodelan dan simulasi
- Introduction to unified modeling language
- Mercer oneview login
- Kinicki management a practical introduction
- Read management: a practical introduction
- Practical research
- Management a practical introduction
- Management a practical introduction 3e
- Kinicki: management: a practical introduction 3rd edition
- Simulation modeling and analysis law kelton
- Dfd ch5
- Requirements modeling in system analysis and design
- Competency modeling vs job analysis
- Manufacturing systems modeling and analysis
- Strategic staffing 4th edition pdf free download
- Practical meta-analysis
- Microsoft threat modeling tool 2016
- Why is sucrose not a reducing sugar
- Spreadsheet development
- Application of spreadsheet
- Qa spreadsheet
- Spreadsheet concepts
- Gdsr roche
- Microsoft excel merupakan aplikasi … *
- Fmla rolling calendar tracking spreadsheet
- Cost segregation spreadsheet
- Cost segregation spreadsheet
- Camping spreadsheet
- Mouse colony management software
- Distributive politics spreadsheet
- Database management system vs spreadsheet
- Spreadsheet vocabulary