Strategic Coffee Sales Report with Visualization

Project Overview

This interactive Coffee Sales Dashboard offers a real-time, data-driven overview of coffee sales performance across regions, timeframes, customer segments, and product types. Developed in Excel using advanced charting techniques and structured datasets, it enables sales leaders to monitor trends, evaluate customer loyalty, and optimize product strategies with clarity and precision.

This Excel dashboard provides a user-friendly way to analyze coffee sales data. Its interactive design allows users to filter the data by time period. These filters enable users to drill down into the data and explore specific trends and customer preferences.

This project showcases end-to-end data analysis using MS Excel, covering data cleaning, pivot tables, and visual dashboards. It demonstrates how Excel can be effectively used for business insights and decision-making.

Key Features

Sales & Profit Trends

  • Monthly Sales & Profit Tracking: Column charts display monthly sales and profits over time, with a 3-period moving average to highlight seasonal trends and performance stability.
  • Year-over-Year Performance: A comparative annual chart illustrates shifts in sales and profit shares from 2019 to 2022, showing peak performance in 2021 (31%).

Regional Sales Distribution

  • Sales by Country: Regional insights show the U.S. as the dominant market, contributing 79% of total sales and profit, followed by Ireland and the UK.

Customer Insights

  • Top 5 Customers by Sales: A horizontal bar chart highlights high-value customers, led by Allis Wilmore ($317) and Brenn Dundredge ($307) over all combine period.
  • Loyalty Card Impact: Loyalty members account for 46.35% of sales and 47.90% of total customers, reflecting positive ROI on loyalty engagement.

Product Analysis

  • Qty Sold by Coffee Size: A doughnut chart splits quantities sold across four package sizes (0.2kg to 2.5kg), with 0.5kg bags leading at 26%.
  • Coffee Type Breakdown: Stacked bars compare Arabica, Excelsa, Liberica, and Robusta by roast level (Dark, Light, Medium), showing Arabica leads in total quantity, with a strong preference for Dark roast (38%).

Technical Implementation

  • Excel Dashboarding: Utilizes dynamic visuals (column, bar, line, doughnut, and stacked bar charts) with slicers for year and month filters.
  • Structured Data Tables: Integrated from sheets like orders_details, products_details, and custs_details, cleaned and summarized in analytical views.
  • Formula & Calculations: Excel formulas like XLOOKUP, INDEX & MATCH, and nested IF are used to pull customer and product data. Sales are calculated by multiplying quantity and unit price.

Business Impact

  • Performance Monitoring: Enables monthly and yearly tracking of both revenue and profit, helping identify high and low-performing periods for promotional planning.
  • Customer-Centric Strategy: Insight into top spenders and loyalty card participation guides retention and upselling efforts.
  • Market Focus: Data highlights the dominance of the U.S. market, allowing for strategic expansion or tailored campaigns in underperforming regions.

Interactive Coffee Sales Dashboard