Find the optimal solution using excel solver. may start at 12:01 A.M., 4:01 A.M., 8:01 A.M., 12:01 P.M., 4:01 P.M., and 8:01 P.M. Thus, the vari-ables may be defined asx1 = number of buses starting at 12: 01 A.M.x2 = number of buses starting at 4:01 A.M.X3 = number of buses starting at 8:01 A.M.x4 = number of buses starting at 12:01 P.M.number of buses starting at 4:01 P.M.x6 = number of buses starting at 8:01 P.M.%3DWe can see from Figure 2.8 that because of the overlapping of the shifts, the number of buses forthe successive 4-hour periods is given asTime periodNumber of buses in operation12:01 A.M. - 4:00 A.M.X1 + x6X, + x2Xz + x3X3 + X4X4 + X'sXs + X64:01 л.м. - 8:00 л.м.8:01 A.M. – 12:00 noon12:01 P.M. - 4:00 P.M.4:01 Р.м. - 8:00 Р.м.8:01 A.M. – 12:00 A.M.The cormplete model is thus written asMinimize z = x1 + x2 + x3 + x4 + xs + X6subject to+ x6 2 4 (12:01 a.M.-4:00 A.M.)2 8 (4:01 A.M.-8:00 A.M.)z 10 (8:01 A.M.-12:00 noon)2 7 (12:01 P.M.-4:00 r.m.)2 12 (4:01 P.M.-8:00 P.M.)x + x2X2 + x3X3 + X4X4 + x5xs + x6 2 4 (8:01 P.M.-12:00 P.M.)Xj 2 0, j = 1,2,..., 6%3DSolution:The optimal solution calls for using 26 buses to satisfy the demand with x, = 4 buses to start at12:01 A.M., X2 = 10 at 4:01 A.M., x4 = 8 at 12:01 P.M., and xs = 4 at 4:01 P.M. Example 2.3-8 (Bus Scheduling)Progress City is studying the feasibility of introducing a mass-transit bus system that will allevi-ate the smog problem by reducing in-city driving. The study seeks the minimum number of busesthat can handle the transportation needs. After gathering necessary information, the city engi-neer noticed that the minimum number of buses needed fluctuated with the time of the day andthat the required number of buses could be approximated by constant values over successive 4-hour intervals. Figure 2.8 summarizes the engineer's findings. To carry out the required dailymaintenance, each bus can operate 8 successive hours a day only.Mathematical Model: Determine the number of operating buses in each shift (variables) thatwitl meet the minimum demand (constraints) while minimizing the total number of buses in op-eration (objective).You may already have noticed that the definition of the variables is ambiguous. We knowthat each bus will run for 8 consecutive hours, but we do not know when a shift should start. If wefollow a normal three-shift schedule (8:01 A.M.-4:00 P.M., 4:01 P.M.-12:00 midnight, and 12:01A.M.-8:00 A.M.) and assume that x, X2, and x3 are the number of buses starting in the first, sec-ond, and third shifts, we can see from Figure 2.8 that x, 2 10, x2 z 12, and x, 2 8. The corre-sponding minimum number of daily buses is x, + x2 + x3 = 10 + 12 + 8 = 30.The given solution is acceptable only if the shifts must coincide with the norma) three-shiftschedule. It may be advantageous, however, to allow the optimization process to choose the"best" starting time for a shift. A reasonable way to accomplish this is to allow a shift to startevery 4 hours. The bottom of Figure 2.8 illustrates this idea where overlapping 8-hour shifts12108712:00AM12:00Noon4:008:004:008:0012:00MidnightX6Number of buses8.

The excel model of linear programming contains the formulas used in the mathematical model. Based on…

may start at 12:01 A.M., 4:01 A.M., 8:01 A.M., 12:01 P.M., 4:01 P.M., and 8:01 P.M. Thus, the vari- ables may be defined as X1 = number of buses starting at 12: 01 A.M. X2 = number of buses starting at 4:01 A.M. x3 = number of buses starting at 8:01 A.M. x4 = number of buses starting at 12:01 P.M. x3 = number of buses starting at 4:01 P.M. x6 = number of buses starting at 8:01 P.M. We can see from Figure 2.8 that because of the overlapping of the shifts, the number of buses for the successive 4-hour periods is given as Time period Number of buses in operation 12:01 A.M. - 4:00 A.M. 4:01 A.M. -8:00 A.M. 8:01 A.M. - 12:00 noon 12:01 P.M. - 4:00 P.M. 4:01 P.M. - 8:00 P.M. X1 + X6 X, + x2 X3 + X4 X4 + X's Xs + X6 8:01 A.M. – 12:00 A.M. The complete model is thus written as Minimize z = x, + x2 + x3 + *4 + xs + X6 subject to + xo 2 4 (12:01 A.M.-4:00 A.M.) 2 8 (4:01 A.M.-8:00 A.M.) x + x2 X2 + X3 2 10 (8:01 A.M.-12:00 noon) Xz + x4 2 7 (12:01 P.M.-4:00 P.M.) X4 + x3 2 12 (4:01 P.M.-8:00 P.M.) xs + x6 4 (8:01 P.M.-12:00 P.M.) x, 2 0, j= 1,2,.., 6 Solution: The optimal solution calls for using 26 buses to satisfy the demand with x, = 4 buses to start at 12:01 A.M., X2 = 10 at 4:01 A.M., x4 = 8 at 12:01 P.M., and xs = 4 at 4:01 P.M.

6th Edition

ISBN:9781337406659

Author:WINSTON, Wayne L.

Publisher:WINSTON, Wayne L.

Chapter2: Introduction To Spreadsheet Modeling

Section: Chapter Questions

Problem 20P: Julie James is opening a lemonade stand. She believes the fixed cost per week of running the stand...