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.