0:00

In this video, we are going to discuss the situation when variables are correlated.

Â The correlation between two independent variables is zero or nearly zero.

Â And this is why we say that independent variables are not correlated.

Â The flight overbooking example that we have been analyzing has two sources of

Â uncertainty.

Â Demand and number of booked passengers that show up for their flights.

Â We have been treating these variables as independent of each other.

Â That is, in our model,

Â the demand values are generated independently of the show-up values.

Â This could very well reflect how things are in the real system.

Â However, there is an easy way of checking

Â whether these two variables are independent.

Â We just need to calculate their correlation.

Â So let's do it.

Â Open the Excel file Flight Overbooking Data,

Â which contains two years of data for the flight that we have been using as example.

Â We would like to determine whether demand and show-up rate are independent or not.

Â In cell D4, let's type Correlation.

Â In cell H4, we enter the correlation function C-O-R-R-E-L,

Â which goes from b2:b731,e2:e731.

Â This function returns a correlation between the variable demand and

Â the variable rate.

Â The result is a correlation of almost 0.7.

Â Without going into details of how correlation values are calculated,

Â we know that they can vary from -1 and 1.

Â As I mentioned earlier, for independent variables, the correlation is near 0.

Â So a value of 0.7 indicates a large positive correlation.

Â Positive correlation in our example means that when demand is high,

Â the show-up rate is also high.

Â We can think of these variables as pointing in the same direction.

Â This analysis tells us that our simulation model can be improved to reflect

Â more of what is really happening in our flight overbooking problem.

Â Adding correlation coefficients between uncertain variables is fairly

Â straightforward.

Â So let's go ahead and do it.

Â Locate and open the Excel file Flight Overbooking Output.

Â This file has the complete simulation model of the flight overbooking problem.

Â The uncertain variables are in cells B14 and B17.

Â B14 contains the psi Poisson function that simulates a demand.

Â B17 contains the psi binomial function that simulates the number of passengers

Â that show up for the flight.

Â At the moment, there is nothing that links the way the values are simulated

Â in both of these cells.

Â Click on the Analytic Solver Platform tab.

Â Click on the Correlations tool that is located in the Simulation Model group.

Â We want to create a new correlation matrix.

Â The right panel shows the two uncertain variables in our model.

Â Since we want to include both of them in the matrix,

Â we click on the double-arrow button that is pointing to the right.

Â The matrix is created with a default correlation of 0.

Â We change the correlation value from 0 to 0.7.

Â For the name, let's just type Demand and show up rate.

Â And for location, enter e4 and click on Save.

Â 3:27

In the second video of this module, we worked on the problem of finding

Â an overbooking policy that will result in a probability

Â of a least 95% that the number of bumped passengers is no more than two.

Â We found that an overbooking of nine accomplishes this goal.

Â However, remember that our analysis

Â was done with a model without correlation between demand and show-up rate.

Â To see the results change now that we have added our correlation matrix,

Â let's do the following.

Â First click on the simulation bulb to turn it on.

Â Also verify that the number of simulation trials is set to 10,000.

Â Then click on the green play button to run the simulation.

Â Double-click on B20 to open the empirical

Â distribution of bumped passengers that the simulation created.

Â Set the lower marker at 2 and observe that the probability of no more than

Â two bumped passengers is not quite 95%.

Â It's close, but under the desired limit we can verify that

Â without correlations the probability exceeds 95%.

Â This is done by clicking on the Correlations tool and

Â on checking the Use Correlations option.

Â The probability changes to 95.88%.

Â Therefore, to be sure that the policy side is bad and

Â taking into consideration that the two sources of uncertainty are correlated,

Â we need to consider a policy with a lower overbooking limit.

Â Let's use correlations again, by checking that option in the Correlations tool.

Â And then let's change the overbooking limit to 8.

Â The probability is now 97.44%,

Â and the number of bumped passengers does not exceed the desired limit of 2.

Â