Sales Analysis Dashboard (Excel)

Introduction

Sales are one of the most used data sources in companies. But, with hundreds, thousands, hundreds of thousands of rows, it becomes almost impossible to draw objective insights. That's why visualizing data becomes a high-demanded skill of any analyst in particular and manager in general.

So, the idea here is:

To create a dashboard that shows the stakeholders the main insights per year.

In this project, I'm going to use Excel. The data is not real, but it represents well reality. This means that I can use the same process with real data.

You can download the Excel Document by clicking on the button below:

Sheet 1: Dataset

This is the dataset. It ranges from 2016 to 2019. Each row represents an order.

After cleaning and preparing, the dataset looks like this:

Sheet 2: Combo Box

This is where I created the combo box that would allow the Dashboard's user to switch between the years.

The combo box is created through form controls in the Developer Tab.

Sheet 3: Headline Stats - Calculation

This is where I calculated the Top Product, the Top Country, and the Top Year using the SUMIF() and the MAX() functions.

Sheet 4: Sales Tables - Calculation

In this sheet, I used the COUNTIFS() function to calculate the number of orders depending on the product/country and the selected year. I also used the SUMIFS() function to calculate the COGS and the Profit depending on the product/country and the selected year.

I added conditional formatting to show the status of each product/country:

• The Green icon is for any amount above or equal to $2,000,000.

• The Orange icon is for any amount between $1,000,000 and $2,000,000.

• The Red icon is for any amount below $1,000,000.

Sheet 5: Filled Map Chart

Using the COUNTIFS() function I calculated the number of orders depending on the country and the selected year. Also, I used an interactive title that shows the selected year. After that, I mapped out the number of orders per country:

Sheet 6: Discount Charts

Using the COUNTIFS() function I calculated the discount band by segment depending on the discount band, the segment, and the selected year. Also, I calculated the average discounts by segment using the AVERAGEIFS() function.

Again, I used interactive titles that show the selected year in each chart. Here are the charts:

Sheet 7: Sparklines

Using the SUMIFS() function I calculated the countries' gross sales for each month depending on the selected year.

I also added conditional formatting to show the monthly Top 5 gross sales in all countries.

Here is the table with the sparklines:

Sheet 8: Titles

In this sheet, I created the interactive titles (Sales Data for [Year], Discount Data for [Year], and Gross Sales by Country and Month for [Year])

Sheet 9: Dashboard

Here is how the interactive dashboard looks like:

Conclusion

Dashboards are now a necessity in any organization. There are easier and better ways to create and show a dashboard (Power BI, Tableau...) than in Excel. But as Excel is one of the most used tools in organizations, it's important to know how to create and maintain dashboards using Excel.