Flight Connection Analysis - Data Science Assignment - Semester 1

This assignment solution details the process of analyzing flight connections using Excel. The solution begins by generating random values for the "Number of Connections" using a Poisson distribution. It then calculates "Allowed Layover Time" based on a random variable and the "Number of Minutes of Layover Time Allowed". The probability of a flight segment being late is computed using the binomial distribution. Random values for "Number of Minutes Late" are generated using the exponential distribution. Finally, the solution determines "Minutes Late" and calculates "Connections Missed" using an IF function, providing a comprehensive analysis of flight delays and connection probabilities. The assignment demonstrates the use of various Excel functions and statistical concepts to model and analyze real-world scenarios related to flight operations and passenger experience.

Thought Process: Steps Followed
The first step was establishing that I will need to generate random values for the variable
“Number of Connections”. I decided to use the random number generator function built into
excel. I went to the data tab then the data analysis ribbon and clicked on it. I selected random
number generator in the pop-up box; after which I selected; one variable, 100 values, Poisson
distribution, lambda=1, and set the output to cell B2. I finally clicked “OK” and the values for
the variable were generated.
The second step was computing the “Number of Minutes of Layover Time Allowed” by
generating a random variable through the same process as step one above except with a lambda
value of 2. After this variable was generated it was used to compute values for the variable
“Allowed Layover Time”. The values in the variable “Number of Minutes of Layover Time
Allowed” were increased by 1 and multiplied by 15 to get the values for allowed layover time for
each connection. Finally, the variable “Total Layover time” was computed by multiplying
“Allowed layover time” with the “Number of Connections”.
The third step was computing the probability of a flight segment being late using the Excel
function “BinomDist” where the number of success=number of connection, number of
independent trial=maximum number of connections (i.e. 5), probability of success=0.5 or 50%.
The exponential distribution was used to compute random values for the variable “Number of
Minutes Late” the following formula was used to generate the values:
x=−1
λ ln ( 1− y ) where y N ( 0,1 )∧λ=0.45
The final step shows that the values in the variable “Number of Minutes late” should be
increased by 1 and multiplied by 10; after which the final figure is round off to the nearest whole
number. This variable is referred to as “Minutes Late”. The final variable “Connections Missed”
is created using an IF function that outputs Missed if the value in “Minutes late” is greater than
the corresponding value for allowed layover time. The probability of not missing a connection is
given as a ratio of the total connections not missed divided by 100 (total values).