Finding highly volatile products

Finding highly volatile products (Hotel Suites)

When you have hundreds of product in your portfolio then it can be tricky to find out products with unpredictable demand. For our calculation we will take an example of occupancy ratio of hotel rooms. Let's say you are a manager of a hotel and you are managing 5 different suites of room; Embassy, Honeymoon, Presidential, Apartment, Classic. Being a manager, it is your responsibility to find out which suite has highly unpredictable demand. In other words, you will need to calculate demand volatility of hotel suites.  

Hypothesis 1. :- None of the hotel suites is recently introduced. (If hotel suites are recently introduced then we need to tweak our calculation, therefore, for example purpose we are going to assume that none of the suites is newly created)

Hypothesis 2. There are 20 rooms assigned for each suites. (20*5=100 total rooms )

We will first start calculating occupancy ratio.

Example to calculate occupancy ratio for a week :-  

Here is a simple example of occupancy room nights for a suite:
20 rooms available for a suite
10 rooms sold on Monday = 50% occupancy rate for that day
15 rooms sold on Tuesday = 75%
16 on Wednesday = 80%
20 on Thursday = 100%
19 on Friday = 95%

20 on Saturday =100%

10 on Sunday =50%
Total available room for the 7 day period = 140 (7 * 20=140)
Room nights sold in a week =  10+15+16+20+19+20+10 = 110
110/140 = 80% occupancy rate for a week

Based on above example we came up with following occupancy rate for each suites for each month for last year.

Room /Date	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Embassy	10	12	13	24	34	0	0	0	0	23	76	76
Honeymoon	12	23	34	54	56	67	100	100	100	10	12	14
Presidential	0	2	3	4	5	6	7	8	9	10	11	12
Apartment	1	2	3	4	5	6	7	8	9	10	11	12
Classic	10	10	10	11	10	12	10	11	10	12	12	10

Now it is time to calculate volatility of suites. In order to find our answer we need to calculate coefficient of variation (CV) of occupancy rate. 

CV :- Standard Deviation of Occupancy rate for last year / Average Occupancy rate for last year

Room /Date	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	AVG	ST DEV	CV
Embassy	10	12	13	24	34	0	0	0	0	23	76	76	22.3	27.371	1.23
Honeymoon	12	23	34	54	56	67	100	100	100	10	12	14	48.5	36.411	0.75
Presidential	0	2	3	4	5	6	7	8	9	10	11	12	6.4	3.753	0.58
Apartment	1	2	3	4	5	6	7	8	9	10	11	12	6.5	3.606	0.55
Classic	10	10	10	11	10	12	10	11	10	12	12	10	10.7	0.888	0.08

Highly unstable items are the one with high CV. Actually, anything more than 1 CV is highly unstable, greater than .50 is medium and less than .50 is stable.

Please note that, decision making purely based on Coefficient of variation is not the correct approach. The first thing we need to consider is cost factor. I will write more on this topic (combining ABC stratification and Coefficient of variation ) in my next blog. Also, I will show how to code them using SQL.