Maven Hotel Group - Insights and Recommendations Data Visualization

This is my portfolio project done for fictitious Maven Hotel Group (credit to Maven Analytics and Enrique Ruiz for the Advanced Excel Dashboard Design course). I played the role of a Data Visualization Specialist for MHG, a Portuguese hotel chain with resorts in Lisbon and Algarve. One of my colleagues had identified some interesting insights from two year's worth of hotel booking data, but needed help bringing the data to life in a one-time explanatory dashboard.

Key Objectives

• Visualize each insight effectively
• Use color consistently & deliberately
• Leverage dashboard layout and text to clearly communicate key insights and business recommendations

---

The following image is what we started with (open the image in a new tab to view in larger resolution). As we can see, my fictional colleague at this point only has a bunch of PivotTables in a workbook. As it is, his insights are not clear and his presentation to the leadership team will likely fall flat.

---

Starting from left-most PivotTable, my colleague had identified seasonality with a heat map. While this might do when presenting to an audience that is particularly interested in all the details, considering that he is presenting to an audience more likely to be interested in a high-level view, I designed the following line charts:

Starting with a combo chart, in my usual tune of not enjoying charts with dual axes, I decided to split the combo chart into two line charts with a shared horizontal axis. I removed the y-axis and in doing so, where I sacrifice in granularity I make up in focus on the seasonality of cancellation rates and average daily rates (further reinforced with a grey and white sections). At the same time, I pointed out the peaks and added context with the lows.

With this, we can easily visualize the seasonality and confirm that cancellation rate and average daily rate follow a similar spike in summer months.

---

For the second PivotTable, I researched different ways to visualize gain and loss and found interesting ways but ultimately decided to stick with a column chart as I found that effectively represented revenue loss as opposed to cost.

In his series, Enrique Ruiz goes over awesome pro tips such as conditional custom number formats. To avoid awkward numbers such as "$1,000K," consider using the custom number format: [>=1000000]$#,##0,,"M";]$#,##0,"K"

As we can see now, there is great loss of opportunity in July and August. When combined with the previous visual (and the title of this visual), we identify the reason as being a combination of high cancellation rates and average daily rates.

---

It is easy to want to use different types of charts for variety, but remember that there is a reason why common chart types are common—they are effective! By using colour and font size strategically, I was able to use a 100% stacked bar (instead of a pie chart) and two simple bar charts in the above image to communicate the opportunity that my colleague discovered.

With all of this combined, my project transitioned seamlessly to its conclusion and recommendation: we need to overbook same-month reservations during July & August.

---

Created by: Mars Huynh | Data Analyst