# Example 6 Non-logistics Network Models

Sizin üçün oyun:

Yüklə 445 b.
 tarix 18.08.2018 ölçüsü 445 b.

• ## The model can be formed with the following steps:

• Enter inputs. Enter the given flight information in the ranges B6:C12 and F6:G12. Because we plan to use this information with lookup functions later on, we have named the ranges A6:C12 and E6:G12 as C_Ltable and NY_Ltable. The labels in columns A and E serve only to identify the various flights.
• Find feasible assignments. To fill in the Chicago-based crews section, find each early flight leaving from Chicago that can be paired with a later flight leaving from New York such that there is at least one hour of downtime in between. Then enter the flight codes of all such pairs of flights in columns A and B. Then do the same for the pairs that could be handled by New York-based crews. Note that all this information is entered in manually – there are no formulas involved.

## Developing the Model – continued

• Downtimes for feasible assignments. Calculate the downtime for each feasible pair of flights by using lookup functions to extract the information form the flight schedules. Specifically, enter the formula =VLOOKUP(B17,NY_Ltable,2)-VLOOKUP(A17,C_Ltable,3) in cell C17 and copy it down for other flight pairs starting in Chicago. This simply subtracts the beginning time of the second flight in the pair from the ending time of the first flight in the pair. Similarly, enter the formula =VLOOKUP(B28,C_Ltable,2)-VLOOKUP(A28,NY_Ltable,3) in cell C28 and copy it down for other flight pairs in New York.
• Flows. Enter any flows in the Flows1 and Flows2 ranges in column D. Remember that these will eventually be set at 0’s and 1’s, indicating that an assignments is either made or it isn’t.

## Developing the Model – continued

• Flow balance constraints. There is a node in the network for each flight and a flow balance constraint for each node – hence 14 flow balance constraints. However, things get a bit tricky because each flight could be either the first or second flight in a given flight pair. For example, consider flight 3C. The network representation involving this flight appears here.

## Developing the Model – continued

• Flight 3C is the later flight for the top two arrows, and it is the earlier flight for the bottom arrow. Now comes the key observation for this particular model. We require that flight 3C be flown exactly once, so exactly one of these arrows must have flow 1 and the others must have flow 0. Therefore, we add this node’s total inflow to its total outflow and constrain this sum to be 1. To implement this in the spreadsheet, enter the formulas =SUMIF(Origins1,F17,Flows1) and =SUMIF(Dests2,F17,Flows2) in cells G17 and H17, and copy them to the range G18:H23 to take care of the flights leaving from Chicago. Then enter the formulas =SUMIF(Origins2,F24,Flows2) and =SUMIF(Dests1,F24,Flows1) in cells G24 and H24, and copy them to the range G25:H30 to take care of the flights leaving form New York.

## Developing the Model – continued

• Finally, add these inflows and outflows in column I. As the spreadsheet model indicates, we will eventually constrain these sums to be 1.
• Total downtime. Calculate the total downtime in the TotDowntime cell with the formula =SUMPRODUCT(Downtimes1,Flows1)+SUMPRODUCT(Downtimes2,Flows2).

• ## The model would grow larger and somewhat more complex, but it could be done.

Dostları ilə paylaş:
Orklarla döyüş:

Verilənlər bazası müəlliflik hüququ ilə müdafiə olunur ©muhaz.org 2017
rəhbərliyinə müraciət