Chapter 06

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: between various city pairs, as shown below. Formulate and solve a network optimization model to find the quickest route from Seattle to Denver? Seattle Butte Billings 10 3 4 9 Portland 7 7 7 7 12 Boise 14 6 Cheyenne 7 Salt Lake City 1 5 Grand Junction 4 Denver This is a shortest path problem. To set up a spreadsheet model, first list all of the arcs as shown in B4:C11, along with their capacity (F4). Only the arc from F1 to WH1 is capacitated. Then list all of the nodes as shown in I4:I11 along with each node’s supply or demand (L4:L11). B 3 4 5 6 7 8 9 10 11 C From F1 F1 F2 WH1 WH1 WH2 WH2 WH2 To WH1 WH2 WH2 WS1 WS2 WS2 WS3 WS4 D E F G Capacity 250 Unit Cost $40 $35 $25 $60 $35 $55 $50 $65 H I Nodes F1 F2 WH1 WH2 WS1 WS2 WS3 WS4 JK L Supply/Demand 400 250 0 0 -200 -100 -150 -200 The changing cells are the amount of flow to send through each arc. These are shown in Flow (D4:D11) below, with an arbitrary value of 10 entered for each. The flow through the arc from F1 to WH1 must be less than the capacity of 250, as indicated by the constraint D4 <= F4. 10 B 3 4 5 6 7 8 9 10 11 C D From F1 F1 F2 WH1 WH1 WH2 WH2 WH2 To WH1 WH2 WH2 WS1 WS2 WS2 WS3 WS4 Flow 10 10 10 10 10 10 10 10 E F <= Capacity 250 G Unit Cost $40 $35 $25 $60 $35 $55 $50 $65 For each node, calculate the net flow as a function of the changing cells. This can be done using the SUMIF function. In each case, the first SUMIF function calculates the flow leaving the node and the second one calculates the flow entering the node. For example, consider the F1 node (I4). SUMIF(From, I4, Flow) sums each individual entry in Flow (the changing cells in D4:D11) if that entry is in a row where the entry in From (B4:B11) is the same as in I4 (i.e., F1). Since I4 = F1 and the only rows that have F1 in From (B4:B11) are rows 4 and 5, the sum in the ship column is only over these same rows, so this sum is D4+D5. B 3 4 5 6 7 8 9 10 11 C D From F1 F1 F2 WH1 WH1 WH2 WH2 WH2 To WH1 WH2 WH2 WS1 WS2 WS2 WS3 WS4 Flow 10 10 10 10 10 10 10 10 E F G <= Capacity 250 Unit Cost $40 $35 $25 $60 $35 $55 $50 $65 H I J Nodes F1 F2 WH1 WH2 WS1 WS2 WS3 WS4 Net Flow 20 10 10 10 -10 -20 -10 -10 K L = = = = = = = = Supply/Demand 400 250 0 0 -200 -100 -150 -200 J 3 4 5 6 7 8 9 10 11 Net Flow =SUMIF(From,Nodes,Flow)-SUMIF(To,Nodes,Flow) =SUMIF(From,Nodes,Flow)-SUMIF(To,Nodes,Flow) =SUMIF(From,Nodes,Flow)-SUMIF(To,Nodes,Flow) =SUMIF(From,Nodes,Flow)-SUMIF(To,Nodes,Flow) =SUMIF(From,Nodes,Flow)-SUMIF(To,Nodes,Flow) =SUMIF(From,Nodes,Flow)-SUM...
View Full Document

This document was uploaded on 01/31/2014.

Ask a homework question - tutors are online