Excel Dashboard

Objective: The primary objective of this project was to develop an interactive sales dashboard for a US Ecommerce company. The dashboard is intended to serve as a one-stop solution for tracking, analyzing, and visualizing sales data across various segments.

This project involved creating an interactive sales dashboard using Excel to analyze a dataset from an Ecommerce company. The data consisted of various details about each order, including but not limited to Order ID, Year, Order Date, Ship Date, Ship Mode, Customer ID, Customer Name, Segment, Country, City, State, Postal Code, Region, Product ID, Category, Sub-Category, Product Name, Sales, Quantity, Discount, and Profit.

Initially, data cleaning was performed to prepare the dataset for further analysis. The cleaned data was then fed into pivot tables to draw insightful summaries. The dashboard utilized different filters, allowing users to view data segmented by Consumer, Corporate and Home Office, as well as by Region (Central, East, South, and West) and Year (2011-2014).

Parameters: The dashboard covers sales data divided across several parameters for a comprehensive analysis:

  • Segments: Three segments namely Consumer, Corporate, and Home Office were included to evaluate sales patterns among different types of customers.
  • Years: Yearly sales data from 2011 to 2014 were used to understand yearly performance.
  • Product Categories: Three product categories, Furniture, Office Supplies, and Technology, were analyzed to evaluate performance across different product lines.
  • Regions: Sales data from four regions (Central, East, South, and West) provided insights into geographical sales trends.
  • Monthly Sales: Monthly sales data for each year was represented to observe seasonal sales patterns.

Dashboard Design:

The dashboard was designed to be interactive and visually engaging, making it easy for stakeholders to interpret and derive insights:

  • A pie chart presents the sales share across the three product categories, allowing users to understand the contribution of each category to overall sales.
  • The line charts represent monthly sales, profits, quantities, number of orders, and profit margins respectively for each year, providing a clear view of sales trends across months and facilitating a comparative analysis between the years. It also includes the Year-Over-Year (YOY) growth for these metrics.
  • An interactive field map visually displays sales across different states and regions in the US for each year. This gives a spatial dimension to the sales data, enabling users to identify region-wise sales performance and potential areas for improvement.
  • A bar chart presents the top 5 sub-categories based on sales, allowing users to quickly identify the best performing product sub-categories.
  • A custom dual-axis chart combining a clustered column and area chart, illustrates the sum of sales alongside the sum of profits.

Outcomes:

The interactive dashboard provides several benefits:

  • Enhanced Visibility: The dashboard offers an immediate snapshot of the company’s sales performance across various metrics.
  • Informed Decision-Making: By recognizing trends, patterns, and potential problem areas, the dashboard aids stakeholders in making data-driven decisions.
  • Improved Sales Strategies: Analysis of sales by segment, region, product category, and year can inform better marketing and sales strategies, ultimately driving business growth.

In conclusion, this project aims to simplify and improve the sales analysis process for the Ecommerce company, allowing it to make strategic decisions quickly and effectively based on real-time data.