Hotel Bronx KPI Analysis
Tools Used: R and Tableau
PROJECT OVERVIEW:
Hotel Bronx is a fictitious name, however the dataset used is real hotel data, comprising of observations for a hotel’s two different categories: Resort Hotel and City Hotel. The data contains information from July, 2015 to August, 2017. It is a public dataset and can be found here.
The goal of this analysis was to look at the trends in some of the hotel's KPIs:
i. The Number of Rooms Booked per month
ii. The Average Daily Rate
iii. The Revenue generated from room occupancy
For this analysis, I selected the 2-year period from July, 2015 to June, 2017 for the Resort Hotel. I focused on July, 2016 to June, 2017 as the current year, and July, 2015 to June, 2016 as the previous year. Using R and Tableau, I calculated and visualized current year month-over-month changes in the 3 KPIs. I also compared the current year KPI values with values from same month the previous year.
ANALYSIS:
I used R for data wrangling, and Tableau for data visualization, creating calculated fields, data exploration and creating dashboards.
Find below my Jupyter Notebook (in PDF) containing the data wrangling steps I took in R. Please see the unabridged version here.
Hotel KPI Analysis - JN.pdfFind below the pace dashboard I created to visualize the trend in KPI values. The dashboard is interactive, so feel free to click on the “Last Year” button to see the comparison between the current year’s values and last year’s. Note that the dashboard is best viewed on a PC.
SOME OBSERVATIONS:
Current Year, Month over Month Analysis:
From a month over month perspective, the month with the highest jump in all 3 KPIs as compared to the previous month is August. It is also the month with the most Average Daily Rate and the month where the hotel grossed the highest revenue. Also, the 3 months with the highest revenues are June, July and August. This would be as a result of the fact that most holidays are taken in summer.
It is interesting to see that the month with the highest drop across all 3 KPIs is September. Thinking about it, it is the month that marks the end of summer and start of autumn, so most vacationers return to their base. This is the reason why there is a downward trend in revenue and rooms booked (except for rooms booked in October) starting immediately after summer.
The 3 months with the lowest revenues are November, January and February. This is no surprise, since less holidays are taken during winter and the cold months.
Current Year, as compared to same time last year:
When compared to same time last year, the current year has seen an increase in number of rooms booked across all months except March, April, May, July. Could there have been an issue or change of policies in the hotel during Spring that caused the drop in number of rooms booked, especially in March where the drop in rooms booked was 21%?
Despite the drops in number of rooms booked in the above mentioned months, these months still saw an increase in revenue (except March). This is because there was a corresponding increase in Average Daily Rate which cushioned the effect of the drop in rooms booked.
Finally, the month with the highest drop across all 3 KPIs as compared to same time last year is March. Perhaps there was a hitch in March this year that caused the hotel not to hit or surpass the same volumes as last year.
RELEVANT LINKS: