Project Overview:

AtliQ Grands owns multiple five-star hotels across India. They have been in the hospitality industry for the past 20 years. Due to strategic moves from other competitors and ineffective decision-making in management, AtliQ Grands are losing its market share and revenue in the luxury/business hotels category. As a strategic move, the managing director of AtliQ Grands wanted to incorporate “Business and Data Intelligence” to regain their market share and revenue. However, they do not have an in-house data analytics team to provide them with these insights.

Their revenue management team had decided to hire a 3rd party service provider to provide them with insights from their historical data.

Problem Statement:

Q.1.) What was Percentage of revenue generated in luxury and business category by all the hotels

Q.2.) Which property has the highest average rating?

Q.3.) Which city has the least revenue generation for Atliq Grands hotel chain?

Q.4.) Which city of Atliq Grands has the lowest average rating?

Q.5.) In 3 months duration (May, June and July), which week number had the lowest occupancy percentage?

Important Insights:

1.) 62% of the revenue came from Business category whereas the remaining 38% came from Luxury category.

2.) Atliq Exotica had the highest average rating of over 4.32 out of 5 star rating.

3.) Delhi city has the least revenue generation for Atliq Grands hotel chain which is Rs. 36 Million

4.) Bangalore city has the lowest average rating for Atliq Grands of over 2.37 out of 5 star rating.

5.) Week number 32 had the lowest occupancy percentage which is close to 46%.

For this dashboard, I worked on data cleaning, data modelling and formulated DAX measures to analyze key metrics such as:

• RevPAR (Revenue per Available Room) - Formula is Total Revenue/Total Rooms Available to Sell. A RevPAR of 95 means that out of 100 rooms, 95 rooms are available whereas the remaining are out of order for various reasons.
• Occupancy % - Total Rooms Occupied/ Total Rooms Available. If Occupancy is 100%, RevPAR and ADR will always be equal. This is calculated on the basis of available rooms, say 95. If 50 room booked, it would be 50/95
• SRN (Sellable Room Nights) - 30 * 100 = 3000 sellable room nights for the month of June
• DSRN (Daily Sellable Room Nights) - 3000/30 = 100 rooms to sell in the month of June. DSRN is the metric where the stakeholder is more interested in looking at.
• URN (Utilized Room Nights) - 100 available rooms, 50 stayed
• BRN (Booked Room Nights) - 100 available rooms, 60 booked, 50 stayed, 10 cancelled for some reason
• DBRN (Daily Booked Room Nights)
• DURN (Daily Utilized Room Nights)
• Realization % - Formula is URN/BRN
• ADR (Average Daily Rate) - byproduct of RevPAR, formula is Total Rooms Revenue/Number of Rooms Sold

Power Query:

Data Modelling: