*Please provide answer using excel, not written*

*Please show calculation as well*

Forecasting Airport Passenger Arrival

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 airports 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 (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).

Data element is stated below for building table on excel sheet:

Day4-6 AM6-8 AM8-10 AM10-noonnoon-2 PM2-4 PM4-6 PM6-8 PM8-10 PM 1 2400270032001400170018001600800200 2 1900250031001600180020001800900300 3 23003100250015001500180019001100200 4 22003200310022001900240021001200400 5 24003300340017002200210020001000600 6 26002800350015001700190015001100300 7 1900280031001200150020001400900400 8 20002700250015002000230019001000200 9 24003200360016002100250018001400200 10 2600330031002002500260024001100400 11 31003900410022002600230025001100300 12 28003400390019002100250020001200300 13 27003800430021002400240024001200400 14 24003500410024003000320026001200700 15 33003700400026002600270029001000300 16 3500400038002300270031003000900200 17 29004100390024003000320025001100500 18 34003800420020002500300022001000300 19 36003600400023002600280026001200200 20 37003700400022002600270024001200200 21 44004400450026003300340030001200400 22 42004500430025003400360031001400300 23 45004500470027003400350029001200300 24 46004600460025003200350028001300300 25 45004300440029003300330033001500400 26 42004300450030004000340030001500600 27 45004500510033004000370031001200300 28 43004200430028003500400033001100400 29 49004100420031003600390034001400500 30 47004500410030004000370034001200500