Hotel Booking Optimization Dashboard

Description :

Maven Hotel Group(MHG) is a fictional Portuguese hotel chain with resorts in Lisbon and Algarve. They've had spikes in cancellation rates for the 3rd straight year, and need to quantify its impact on the business and find potential improvement opportunities. Need to build a Explanatory Dashboard that can show reason of loss in revenue and what decisions we can introduce to improve revenue.

Tool Used : Microsoft Excel

Key Objective of Dashboard :

1. Visualize each insight effectively.
2. Use color consistently & deliberately.
3. Leverage Dashboard layout & text to clearly communicate key insights and business recommendations.

Observation:

1. High cancellations/rates cost nearly $1 Million in revenue losses last year during July and August!
2. Reservations booked with more than one month in advance are causing most of the revenue loss.
3. Overbooking same-month reservations during the summer can:
   • Replace the bookings made in advance which are likely to be cancelled.
   • With the bookings that have a lesser risk of cancellation.
   • And they can be sold at a higher average price.

Data Source : Hotel Booking Demand, written by Nuno Antonio, Ana Almeida, and Luis Nunes for Data in Brief, Volume 22, February 2019.

Dimensions & Measures :

1. Dimensions : Booking ID, Hotel, Booking/Arrival Date, Distribution Channel, Customer Type, Country, Deposit Type, Status, Status Update, Cancelled (0/1)
2. Measures : Lead Time, Nights, Guests, Revenue, Loss

Concept Covered :

1. Pivot Charts & Tables.
2. Conditional Number formatting.
3. Explanatory Dashboard with Problems & Recommendations detail.
4. Combining dashboard objects and pasting same as an image.