As an owner or manager, you can feel the trends. You can tell that business is growing. Every year you get a little more guests and/or you are able to raise you price a tad more. When you think into the future, you have an idea where your numbers will be, but how are others able to forecast so precisely? Where do these numbers come from? The answer is they are either guesstimating – pulling these numbers out of thin air, or they are derived from good old fashioned statistics. Here we will show you a solid way on how to do the math.
First you need some material and some tools. Material wise, you will need at least three years of your occupancy data. You can modify it as you see fit, but there are some requirements to get the model working right. Tools wise, all you need is a good old fashioned Microsoft Excel with the data analysis toolpack installed. If you are trying to use Google Docs, you might need some add-ons to get the regression tools. We’ll show you the excel way, and leave a couple links on how to get regression working in Google Docs.
Ok, now back to the data. Somehow have your PMS system create a csv or excel file of this data going as far back as you can, again at least three years. You might have to export the data and manipulate it to get it into a format to start, and with all the systems out there, you’re on your own here to figure this part out. If you can do that, here is the basic of what you need per day:
These mandatory data fields are:
- Date
- Dorm ABR
- Private Room ADR
- Occupied Dorm Beds
- Occupied Private Rooms
Now you can make it more complex by breaking down the prices per channel, so you can also have. Please note abbreviations for to make the titles recognizable only. They have no bearing in the statistics involved:
- Average Hostelworld Dorm Bed Rate (AHWbR),
- Average Hostelworld Private Room Rate (AHPR),
- Average Booking.com Dorm Bed Rate (ABbR),
- Average Booking.com Private Room Rate (ABPR)
- other channels including direct and ‘Other’
You can also do the same with your occupancy numbers. You can break them down to:
- Occupied Dorms by Hostelworld (ODHW),
- Occupied Private Rooms by Hostelworld (OPHW),
- Occupied Dorms by Booking (ODB),
- Occupied Private Rooms by Booking (ODP),
- other channels including direct and ‘Other’
Please note, if you break down by channel, you must include all the channels or at least lump together the remaining channels into an ‘Other’ field so it reflects the true total of rates and occupancy. If you are breaking it down, you do not need the total occupied numbers because that can be extrapolated later. To keep things simple, we’ll just stick with the most basic of data using those fields first mentioned.
Being able to forecast down the the day is overkill and causes too much complexity for little value. Some holidays and events aren’t on the same day each year, but happen around the same time, so combining them into weekly stats is the way to go. This will help you forecast by the week. You can then get a cleaner sheet with more actionable data. How do you condense your data down to the week (or month, quarter)? Pivot Tables, that’s how.
We could write an article about how to group data by some kind of time interval, such as a week, month, quarter, but people already have. Just make sure you are taking an average of your bed rates and occupancy rates, not a sum.So here are some links:
- https://www.myexcelonline.com/blog/show-averages-with-an-excel-pivot-table/
- http://www.contextures.com/xlPivot07.html
Now we can write a step by step guide (and a link to videos that better explain them below). .
- Create a graph. It must be a blank Line Plot graph. Label it, and break the year down by quarter (or month if you have little data). You will have to format the Time Axis accordingly. Remember one cycle is one year. This original, or column data is also known as Yt if you really wanted to know.
- Create a column and calculate the moving average (title – MA(52) ) by writing an average function starting halfway through the cycle (so it starts after the first 6 months of data, taking that data into account). You also do not get a moving average of the last 6 months either because there is not data.
- Center the moving average in another column (CMA(52)) because 52 weeks is an even number, so you create a new column that is an average of the two uncentered moving average values adjacent to it. This way the average of two uncentered values would then be centered and this data can also be referred to as the baseline..
- Now select the CMA and add it to the chart on top of the Time Series Data. This gets rid of the seasonality and irregularity components.
- Now you extract the Seasonality and Irregularity Components from comparing the initial data with the averaged data. This is the Classical Multiplicative Model for time series. Create a column with St,It Here you just divide the original data by the centered average.
- Now you get rid of the irregularity and only calculate the seasonal component. You first create an averageif formula somewhere else on the sheet and that an average of the season (week number). Then use a vlookup formula to place the data into a column titled St.
- Now since you know the seasonality, you deseasonalize the original data by dividing the original data by St. Now you got rid of both the irregularity and seasonality.
- Now you calculate the trend component using linear regression using the deseasonalized data as the Y variable and the T variable as the X. Here you use Data Analysis tool kit and input that data somewhere out of the way on the worksheet.
- Now you add together the coefficients for the slope and multiply that by the time code. Now this data will give you the trend over the time, to help make predictions.
- Now use all these values to make predictions. Now create a forecast in a new column by multiplying the seasonality trend with the trend.
- Now add another year worth of time intervals (week, month, quarter) to the time. Now you extend the seasonal component and the trend component and the forecast to the end of the forecasted year. The forecast into the future is what you want. Now you can plot the forecast of the future right on top of the original data as new series in a different color.
Too much? Do you need pictures? How about some quality videos? videos in a row.
- https://www.youtube.com/watch?v=gHdYEZA50KE
- https://www.youtube.com/watch?v=5C012eMSeIU
- https://www.youtube.com/watch?v=kcfiu-f88JQ
Or view some slides:
https://www.slideshare.net/UniSrikandi/chap15-time-series-forecasting-index-number
This data helps you with predictions. This data can help you plan into the future. Especially if you are to take on some investment or debt. It is not perfect and who know what other factors can influence your business in time. Now you can get more complex here, like calculating the average difference in weekend rates to weekday rate, but please consider, the more complexity you wish for, the more you’re better off using a revenue management tool.
What revenue management companies do is this, and some. Instead of doing it with spreadsheets, they do it with more complex data science models with a bunch of code. They also use relevant benchmarking and other publicly accessible hostel data to improve their forecasts, and finally they add in proxy data to make their data models even more robust. Think,of what data outside of your business that can help your business? Local airport statistics? Weather predictions? Major event schedules? All these data sources already give a good manager a hunch to change the price, this kind software quantifies the hunch and gives you suggestions on how to best act on it.
Pivot Tables. Time Series Forecasting. Wow, you have some business intelligence now. Add that to your resume and get on forecasting. If you have any other tips (especially on what systems make it easy to export data like this), feel free to leave them in the comments.
