As a Data Visualization Specialist, my task is to design a dashboard using Microsoft Excel to clearly communicate insights.
Grand Beach Resorts, a resort hotel chain, facing revenue loss due to advanced reservations. (Data Source: Maven Analytics)
This dashboard communicates key insights and business recommendations.
I will be designing a completely explanatory dashboard instead of tables to present business recommendations to the end user of this report to aid in decision-making.
Raw data is provided in a Microsoft Excel worksheet.
Data Analysis using Pivot Tables
While designing this dashboard, my key objectives are
- Effectively visualize each insight
- Maintain color consistency
- Clearly communicate key insights and business recommendations with dashboard layout and text
Visualizing Seasonality with a Combo Chart
To visualize two metrics on completely different scales, I have used a combo chart that shows a comparison across time and a seasonal pattern.
The following chart clearly shows that cancellation rates and average daily rates follow the same seasonality.
Contrasting Gains and Losses with a Column Chart
Here we are looking at revenue and revenue loss by month. Due to this seasonality, we can see alarming figures in July and August. I have used a column chart to pinpoint the values for July and August.
Using Stacked Bars instead of Pie Chart
Here is a breakdown of July and August cancellations by lead time. This is mainly due to reservations made more than a month in advance.
Comparing two lead times by two metrics
Telling a Story
Bringing all insights together to tell a compelling story that uses text, meaningful titles, and subtitles to drive the narrative and ultimately sell its recommendations.