0:00

You now know how to create a simulation model in the analytic software platform.

Â You also know how to run the model and interpret the output.

Â To create these models we have made a critical assumption

Â about the source of uncertainty.

Â We took the probability distribution of the uncertain

Â input variables as something that was given to us.

Â Remember the other booking problem that we have been discussing?

Â In that problem, there are two sources of uncertainty.

Â The demand for seats in the flight and

Â the numbers of booked passengers that actually show up.

Â Or assumptions were that the demand for

Â seats follows a Poisson distribution with an average of 150 passengers.

Â We also assume that the show-up process

Â could be modeled with a Binomial distribution.

Â 0:51

These are probably pretty good assumptions, but

Â you might be wondering how in general we could come up

Â with the right functions to model the uncertainty of key input variables.

Â That's what we're going to learn in this video.

Â As he have mentioned before, uncertainty comes from variability.

Â When we say that the average demand for a flight is 150 passengers,

Â we don't expect that on every single day the demand will be exactly 150 passengers.

Â This is the difference between an average value and a constant value.

Â So, what we're saying, is that the demand varies and

Â that it is possible to calculate an average.

Â An average value is good information but we want more.

Â We want to know how the demand varies.

Â 1:40

They key is to estimate the probability of distribution function for

Â critical inputs to a simulation model.

Â And the way we are going to do that is by analyzing historical data.

Â Locate and open the Excel file flight over booking data.

Â This workbook contains two years of data for

Â the flight that we are using as an example.

Â The first column has a date,

Â the second column the demand, the third column has the number of passenger's vote.

Â The fourth column has a number of passengers that actually showed up, and

Â the fifth column has a fraction of the book passengers that showed up.

Â You can see that the demand varies from day to day.

Â You can also see that the fraction of passengers that show up for

Â their flight also varies from one day to another.

Â Those are the two sources of variability and,

Â therefore, also the two sources of uncertainty.

Â In cell H-1 we have calculated the average demand, which is about 150 passengers.

Â The show up rate of 92% is in cell H-2.

Â If we look at the formula in H-2 we can see that the show up rate is simply

Â the average of the fraction of booked passengers that have shown up for

Â the flight in the two years of data.

Â Generally speaking, simulation models do not use historical data directly.

Â The idea is to use the data to create a so-called probability distribution

Â function for each variable and then use the function for the simulation model.

Â The process of identifying an appropriate probability distribution function for

Â a variable for which we have historical data is called Distribution Fitting.

Â The analytics of a platform includes a distribution fitting tool.

Â To use this tool we need to start by selecting the cells

Â where the historical data is.

Â So let just start with the demand and select cells B2 to B731.

Â 3:40

Know that in this case, I did select the name of the variable which is in cell B1.

Â Now, we go to the ASP tab and in the tools group we find fit.

Â Click on fit and the fit options dialogue appears.

Â There are two general types of this distribution functions,

Â continuous and discrete.

Â In this case we have discrete data and we choose the discrete option.

Â This is the only option that we're going to change.

Â We will leave the other options under their full values.

Â Click the fifth button and the results windows is displayed.

Â The results window shows the comparison chart.

Â This chart compares the frequency distribution of the historical data in

Â blue and the frequency of the proposed distribution function in pink.

Â The idea is to visually inspect how well the proposed function fits their values.

Â In this case the analysis is suggesting a Poisson distribution with a mean

Â value of 150.4 passengers.

Â Other alternatives are shown in the left panel of the window.

Â These alternatives are shown in order of fitness.

Â So, the analysis is suggesting that the Poisson is the best fit,

Â followed by the negative Binomial.

Â We're going to keep the Poisson distribution as it

Â looks like a good representation of the demand.

Â If we close this window,

Â we are asked whether we want to accept the fitted distribution.

Â Say yes,

Â the ASP then allows you to place the side function somewhere on the spreadsheet.

Â Place it in cell H4.

Â We can verify that H4 now contains this signed Poisson function

Â with an average of 150.4.

Â For the show up rate the processes are slightly different.

Â In our simulation we model the show up rate as a Binomial distribution.

Â The Binomial distribution makes a lot of sense because given the number of trials

Â and the probability of success in each trial.

Â The distribution represents the frequency of the number of successes.

Â The number of trials in our context is the number of booked passengers.

Â Success means that the passenger shows up for the flight.

Â Or best estimate of the show up rate is not 92% which we've found by

Â averaging the historical percentage of passengers showing up.

Â So we have a an estimate of the probability of success.

Â We also have the number of trials because the number of booked passengers is known.

Â That means that we just need to verify that the number

Â passengers that show up for the flight follow a binomial distribution.

Â We can use the fit tool to do this.

Â Select the data on column D and click on fit.

Â 6:34

ASB shows that the parameters of this distribution are at 339.

Â For the number of trials and point three nine for the probability for success.

Â However, these numbers are not meaningful because we have

Â already estimated the show up rate and for each flight we know the number of trials.

Â That is we know the number of booked passengers.

Â The distribution fitting process has helped us verify

Â that the Binomial distribution is appropriate for the purpose of modeling

Â the number of passengers that show up out of a number that are booked.

Â Distribution fitting is a very important tool to convert data into probability

Â distribution, for instance, that can be used in a simulation model.

Â It is possible to run what could also be considered as simulation

Â using historical data.

Â And this is sometimes done to test how a policy who have performed in the past.

Â But distribution fading along with simulation allows us to create

Â many scenarios based on what happen in the past.

Â This is the most effective use of data in the context of building simulation models.

Â