Category: Excel

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.