PLEASE SHOW EXCEL WORKING WITH ANALYSIS AND SOLVER IF APPLICABLE! NO SPAM!

Airports have always faced the problem of long waiting lines and waiting times at security gates. Waiting lines can be as long as hundreds of yards and waiting times can sometimes be hours. In their efforts to reduce waiting lines and times, or at least to not have them become longer as airline demand increases, airports have analyzed their existing security systems and sought quantitative solutions. One of the key components of any effort to operationally improve airport security procedures is forecasting passenger arrivals at security checkpoints to determine how many security checkpoints and staff are needed. At Berry International Airport (BEI), security analysts would like to forecast passenger arrivals for next July, the airport's busiest travel month of the year, for the purpose of determining how many security checkpoints they should staff during the month so that waiting lines and times will not be excessively long. Demand for airline travel has generally been increasing during the past 3 years. There are two main concourses at BEI, North and South, each serving different airlines. The attached table shows passenger arrivals at the South concourse for 10 days (selected randomly) in 2-hour segments from 4:00 a.m. to 10:00 p.m. for the month of July for the past 3 years. Part (a) Develop a forecast for daily passenger arrivals at the South concourse at BEI for each time period for one day in July of year 4. Part (b) Use the forecast developed to perform this type of waiting line analysis to determine how many security checkpoints are needed during each time segment. Assume that as passengers arrive at the South concourse security gate, they join a single line to have their boarding pass and identification checked at one of several stations. When passengers leave these stations, they again form a single line and are approximately equally distributed among the security checkpoints by security personnel before going through the various detection machines. For July the airport plans to staff six security checkpoints for each 2-hour time segment from 4:00 a.m. to 6:00 p.m., and then three checkpoints from 6:00 p.m. to 8:00 p.m., and two check-points from 8:00 p.m. to 10:00 p.m. Assume that the arrival rate at this point in the security system is Poisson distributed, with the forecasted passenger arrivals developed in Part (a) as the mean arrival rate. Further, assume that service times are exponentially distributed with a mean of 11.6 seconds. Determine whether the number of security checkpoints the airport plans to use for each 2-hour time segment is sufficient to keep passengers moving freely through the security system without excessive delays. The airport wants to keep the average time of a passenger in the system to be less than 5 minutes. If the planned system is not likely to be sufficient, determine the number of checkpoints that would be needed for each 2-hour segment in order for passengers to move quickly through the security checkpoints without excessive waiting times (the average time in the system to be less than 5 minutes). 3300 900 Day 46 A.M. 6-8 A.M. 810 A.M. 10-Noon Noon-2 P.M. 2-4 P.M. 46 P.M. 6-8 P.M. 8-10 P.M. Year One 1 2400 2700 3200 1400 1700 1800 1600 800 200 2 1900 2500 3100 1600 1800 2000 1800 900 300 3 2300 3100 2500 1500 1500 1800 1900 1100 200 4 2200 3200 3100 2200 1900 2400 2100 1200 400 5 2400 3400 1700 2200 2100 2000 1000 600 6 2600 2800 3500 1500 1700 1900 1500 1100 300 7 1900 2800 3100 1200 1500 2000 1400 400 8 2000 2700 2500 1500 2000 2300 1900 1000 200 9 2400 3200 3600 1600 2100 2500 1800 1400 200 10 2600 3300 3100 200 2500 2600 2400 1100 400 Year Two 11 3100 3900 4100 2200 2600 2300 2500 1100 300 12 2800 3400 3900 1900 2100 2500 2000 1200 300 13 2700 3800 4300 2100 2400 2400 2400 1200 400 14 2400 3500 4100 2400 3000 3200 2600 1200 700 15 3300 3700 4000 2600 2600 2700 2900 1000 300 16 3500 4000 3800 2300 2700 3100 3000 900 200 17 2900 3900 2400 3000 3200 2500 1100 500 18 3400 3800 4200 2000 2500 3000 2200 1000 19 3600 3600 4000 2300 2600 2800 2600 1200 200 20 3700 3700 4000 2200 2600 2700 2400 1200 200 Year Three 21 4400 4400 4500 2600 3300 3400 3000 1200 400 4200 4500 4300 2500 3400 3600 3100 1400 300 23 4500 4500 4700 2700 3400 3500 2900 1200 300 24 4600 4600 2500 3200 3500 2800 1300 300 4500 4300 4400 2900 3300 3300 3300 1500 400 26 4200 4300 4500 3000 4000 3400 3000 1500 600 27 4500 4500 5100 3300 4000 3700 3100 1200 300 28 4300 4200 4300 2800 3500 4000 3300 1100 400 29 4900 4100 4200 3100 3600 3900 3400 1400 500 30 4700 4500 4100 3000 4000 3700 3400 1200 500 4100 300 22 4600 25