Hospitality Dashboard: Uncovering Revenue Insights

Aim

To develop a comprehensive data-driven dashboard that provides actionable insights for AtliQ Grands, enabling them to regain market share and increase revenue in the luxury/business hotels category by leveraging historical data and business intelligence.

Download from GitHub!

Problem Statment

AtliQ Grands, a long-standing player in the hospitality industry, is losing market share and revenue due to strategic missteps and ineffective decision-making. Without an in-house data analytics team, they need to rely on external expertise to analyze historical data and provide insights that will inform better management decisions and help them regain their competitive edge.

Setup

  • Data Integration: The data was imported into Power BI, where relationships between the tables were established, primarily using 1-to-many cardinality to ensure data integrity and accurate analysis.
  • Custom Weekend Definition: In the hospitality industry, weekends are critical for revenue. To align with industry practices, DAX functions like "WEEKDAY" and "IF" conditions were used to redefine weekends as Friday-Saturday, ensuring accurate weekend-specific analysis.
  • DAX Measures: Advanced DAX formulas were employed to create key performance metrics such as revenue, total bookings, capacity, occupancy, RevPAR (Revenue per Available Room), ADR (Average Daily Rate), and more. These measures provided granular insights into the hotel's performance across different dimensions.
HR Data after transformation

Fig 1. This image shows the data model

These steps were essential to ensure that the data was properly formatted for analysis and would yield accurate results.

Dashboard Design and Creation

In this project, I have designed and created an interactive dashboard tailored to the needs of AtliQ Grands, a prominent hotel chain. The dashboard was built in Power BI and focused on visualizing key performance indicators (KPIs).

Design Approach

  • User-Centric Design: The dashboard was designed with a focus on user experience, allowing stakeholders to quickly access critical insights. Filters and slicers were prominently placed to allow users to drill down into specific data segments such as city, property, and booking platform. The layout includes various visual elements such as pie charts, line graphs, bar charts, and tables to present data in an easy-to-digest format.
  • Visual Clarity: Key metrics are displayed using KPIs and graphs to provide an at-a-glance understanding of performance. A trends section highlights weekly patterns in occupancy and ADR, enabling quick identification of performance dips or peaks. Revenue and occupancy by city are visualized to show regional performance disparities.
  • Custom Metrics and Measures: Dax was used to create custom measures, specifically for KPIs.
HR Analytics Dashboard

Fig 2. This image shows the Hospitality Dashboard.

Key Findings

    1. Revenue Concentration: A significant portion of revenue is generated by major cities like Delhi and Mumbai, contributing over 60% to the total revenue. This indicates the potential for further growth in these regions.
    2. Occupancy Rates: Delhi has the highest occupancy rate at 60.44%, while other cities like Bangalore and Hyderabad have lower rates, suggesting a need for targeted marketing efforts.
    3. RevPAR and ADR Trends: There are noticeable fluctuations in RevPAR and ADR across weeks, with some weeks showing significant dips, indicating potential issues with pricing strategies or external market factors.
    4. Booking Platform Performance: Realization % and ADR vary across booking platforms, indicating that some channels are more profitable than others.
    5. Category Revenue: The luxury segment contributes the most to revenue but has lower occupancy compared to the business segment, highlighting an opportunity to optimize pricing or offer packages to increase occupancy in the luxury category.

Recommendations

    1. Focused Marketing Initiatives: Implement targeted marketing campaigns in cities with lower occupancy rates, particularly in Hyderabad and Bangalore, to boost bookings.
    2. Optimize Pricing Strategies: Review and adjust pricing strategies, especially in the luxury segment, to enhance occupancy without sacrificing revenue.
    3. Channel Optimization: Strengthen partnerships with high-performing booking platforms and consider renegotiating terms with underperforming channels to maximize overall revenue.
    4. Address Seasonal Dips: Launch strategic promotions or special offers during weeks with identified revenue dips to stabilize and potentially increase RevPAR and ADR during these periods.
    5. Leverage Weekends: Utilize the redefined weekends (Friday-Saturday) to create attractive weekend packages that could drive higher occupancy and revenue.

Learnings

    1. Advanced DAX Usage: This project enhanced your proficiency in DAX, particularly in creating custom metrics and manipulating data to align with industry-specific requirements.
    2. Hospitality Industry Insights: Developed a stronger understanding of the hospitality industry's key metrics and how they influence business decisions, providing you with the knowledge to deliver impactful data-driven strategies.

Conclusion

The dashboard provided AtliQ Grands with actionable insights to optimize revenue and occupancy, empowering data-driven decisions for improved market performance. This project also enhanced my skills in Power BI and advanced DAX, enabling me to deliver impactful analysis tailored to industry-specific needs.

Contact Information