EMBA 2016, MSB, TUNISIA
Business Statistics
May 19th to 22sd
Personal Assignment
Prof. Kristín Friðgeirsdóttir
Student: Nader Ben Sheikh Brahim
Problem 1: Airline Overbooking
WOW air: new low cost airline, with planes (most) having 200 seats
Target is to manage the overbooking strategy (like all airlines, WOW is selling more tickets on a flight than seats).
No-Show Probability: 5%
We will use binominal distribution to help WOW managers decide
Our two possible outcomes would be: Show up and does not show up
We will approximate the binominal distribution with a normal distribution (number of passengers that show up is normally distributed) where:
- Mean = n*p
- SD = [pic 2]
- If we sell 209 seats
N= 209
P= 0.95 (P to show)
(1-P) = 0.05 (P to not show)
What is the Probability to have an overbooking situation?
It means, what is the probability to have customers showing up, more then 200
- P(X>=201)
Z = where [pic 3]
X = 201
Mean = n*p = 209 * 0.95
SD = [pic 4]
We notice that n*p = 198.55
n*p*(1-p) = 9.9275
We notice that both of them is bigger then 5, which mean that we can continue approximating a binominal with a nominal.
We get then:
Z = 0.78
P(Z>=0.78) = 0.218 (21.8%)
In another hand, we can just directly use excel with following function:
Norm.Dist (201;198,55;3,15;TRUE)= 0,218 (21.8%)
- If we sell 218 seats
N=218
Mean= 218*0.95= 207.1
SD= SD = =3.217[pic 5]
What is the probability of having empty seats?
→ it means that showing customers number is below 200.
P(Z<=199) = 0.006
We obtain same result with excel: Norm.dist(199;207.1;3.217;TRUE) = 0.006
- If the company target 2% probability of an overbooking situation, how many tickets should it sell?
- It means that the probability of getting customers showing up at the flight, must be 98%
- The probability of number of customer showing up is bigger then 200, is 2%
We can use the table and we will see that corresponding Z to 2% probability is: 2.06 with P= 0.197
Z= [pic 6]
We can check, by considering different values for n and calculating the probability see below:
n | mean | SD | Z | % |
201 | 190,95 | 3,08990291 | 2,92889462 | 1,8% |
202 | 191,9 | 3,0975797 | 2,61494482 | 4,7% |
203 | 192,85 | 3,10523751 | 2,30256139 | 1,1% |
204 | 193,8 | 3,11287648 | 1,99172696 | 2,3% |
I think selling 203 tickets is the answer, as if we go to 204 we have a probability over 2%.
- Missing data for a better understanding and forecasting:
- Exact number of planes the company owns? Exact number of seats in each one?
- More detailed historical data?
- The company overbooking policy: what happens when they have showing up people more than seats? What is the financial impact of that?
- Proportions of combined reservations? (sometimes we have 2, 3 or more people in one reservation)
Problem 2: Managing Subscriptions at London Today
- LT is giving trial subscriptions to potential customers.
- Target is to convert as much as possible trial subscriptions to regular one.
- Marketing department to forecast next month’s new regular subscriptions.
- Group of managers, forecasting are looking for 2 to 3 months ne subscriptions data
- JG, newly hired, suggested to look for factors that are helpful in predicting
- Some forecast in some months were inaccurate, because of time spent by telemarketers in training
- JG suggested getting number of new subscriptions and number of hours spent on telemarketing.
- I think it is useless to forecast based on 3 months only Data. The model would be for sure insignificant.
Besides logically speaking, we need sure more historical Data to better forecast.
- If we do the regression model, using the 24 months data, we obtain:
[pic 7]
As we can see, the chosen variable (number of hours spent in telemarketing), explain 85% of our number of new subscriptions variability, which is already good, but also, It means somewhere that we have some wiggle room to improve our model.