Data Discovery : Crafting Excel Sales Dashboard for Supermarket Sales

admin0Tagged , , , ,

Dataset :

The dashboard centers around a comprehensive dataset containing supermarket sales data. This dataset encompasses a wide range of variables including sales period, sales amount, product line details, city-wise ratings, and payment methods. The dataset serves as the foundation for the analysis, providing rich insights into the performance of supermarket sales over time.

Invoice ID, Branch, City, Customer type, Gender, Product Line, Unit Price, Quantity, Tax 5 %, Total, Date, Time, Payment, COGS, Gross Margin Percentage, Gross Income, Rating.

datasets

Problem Statement:

The primary challenge to address is the need for effective analysis and visualization of supermarket sales data. Many businesses struggle to derive meaningful insights from their sales data due to its complexity and volume. The project aims to streamline this process by providing a structured approach to analyzing and visualizing sales data, ultimately enabling stakeholders to make informed decisions.

Analytics Questions:

To guide the analysis, here are several key analytics questions:

  • What are the total sales, quantity products sold, average rating, and total COGS in 2019 ?
  • Which product lines are driving the highest sales revenue?
  • What are the preferred payment methods among customers?

Resources:

The following are a variety of resources :

  • Excel for data manipulation and visualization
  • Advanced Excel functions and formulas for data analysis
  • PivotTables and PivotCharts for dynamic data summarization

Transformation

  1. Format Raw Data into Tables:
    • Import or input raw data into Excel.
    • Ensure dataset is formatted as a table by selecting the data and going to the “Insert” tab > “Table”.
    • Review the table for any inconsistencies or errors.
    • Use Excel’s filtering and sorting tools to identify and remove null or erroneous values.
    • Double-check data consistency and accuracy.
  2. Create Pivot Tables:
    • For each field (Sales Date, Product Line, Payment Method, City Details), create a separate pivot table.
    • Select the corresponding table, go to the “Insert” tab > “PivotTable,” and choose where to place the pivot table.
    • Drag the desired field to the rows or columns section to organize the data.
  3. Pair Sales Amount with Pivot Tables:
    • Add the Sales Amount field to each pivot table’s values section to analyze sales data effectively.
  4. Transform Pivot Tables into Visualization Charts:
    • For each pivot table, create visualization charts (line, bar, pie) to represent the data visually.
    • Customize chart elements such as titles, axes labels, and legends for clarity.
  5. Insert Key Metrics in the Top Header:
    • Add Total Sales, Quantity of Products Sold, Cost of Goods Sold (COGS), and Average Rating by City as major focus data.
    • Calculate these metrics using appropriate Excel formulas and place them in the top header of the dashboard for quick reference.
  6. Add Clickable Slicers:
    • Insert four clickable slicers for City, Customer Type, Product Line, and Payment Method.
    • Go to the “Insert” tab > “Slicer” and select the fields to create slicers.
    • Customize slicers’ appearance and size for better usability.
  7. Utilize Filter Connections:
    • Check and establish filter connections between slicers and pivot tables/charts to ensure synchronized filtering.
  8. Arrange Positions of Slicers and Charts:
    • Organize the layout of slicers and charts for better viewing and usability.
    • Place slicers and charts strategically on the dashboard to optimize space and clarity.
    • Adjust the size and position of each element as needed.
  9. Enhance Visual Presentation:
    • Apply colors, styles, and formatting to slicers and charts to improve visual appeal.
    • Ensure consistency in color schemes and fonts for a cohesive dashboard design.

Analysis Results:

Based on our analysis of the entire 2019 dataset, here are the key findings :

  1. Total Sales: The total sales for the year 2019 amounted to $322,967.
  2. Quantity Products Sold: A total of 5,510 products were sold throughout the year.
  3. Cost of Goods Sold (COGS): The total cost of goods sold (COGS) for the year 2019 was $307,587.
  4. Average Rating: The average rating across all cities for the year 2019 was 6.97.

Additionally, in terms of product lines:

  • Highest Sales Revenue: The product lines driving the highest sales revenue were “Food” and “Beverages.”

Regarding payment methods:

  • Preferred Payment Methods: Customers predominantly used e-wallets as their preferred payment method throughout 2019.

6. Recommendations for Stakeholders:

  1. Leverage High-Performing Product Lines:
    • Stakeholders should focus on leveraging the popularity of the “Food” and “Beverages” product lines, which are driving the highest sales revenue. This could involve strategic marketing campaigns, promotions, or product expansions to capitalize on consumer demand.
  2. Optimize E-Wallet Payment Options:
    • Given that customers predominantly used e-wallets as their preferred payment method in 2019, stakeholders should prioritize optimizing e-wallet payment options. This could involve streamlining the checkout process for e-wallet users, offering incentives or discounts for e-wallet payments, or partnering with e-wallet providers to enhance payment integration.
  3. Monitor and Maintain Customer Satisfaction:
    • With an average rating of 6.97 across all cities, stakeholders should continue to monitor and maintain high levels of customer satisfaction. This could include implementing measures to address any areas for improvement identified in customer feedback, ensuring consistent product quality and service standards, and providing ongoing training and support for frontline staff.
  4. Strategic Planning for Growth:
    • Based on the total sales, quantity of products sold, and COGS data, stakeholders should engage in strategic planning for sustainable growth. This could involve setting achievable sales targets, optimizing inventory management to minimize costs, and identifying opportunities for expansion into new markets or product lines.
  5. Invest in Data-Driven Decision-Making:
    • Finally, stakeholders should recognize the value of data-driven decision-making and invest in tools and resources to support ongoing analysis and insights generation. This could involve implementing advanced analytics solutions, providing training for staff on data interpretation and visualization, and fostering a culture of data-driven decision-making throughout the organization.

Features :

  • Sales Period Analysis
  • Monthly Sales Visualization
  • Product Line Performance Tracking
  • Average Rating per City Display
  • Payment Method Breakdown
  • Clickable Slicers for Data Exploration (City, Customer Type, Product Line, Payment Method)

Github

Related Posts