Coffee Sales Analysis

OVERVIEW

The Coffee Sales Data Analysis Project addressed the need for a clear and actionable understanding of sales performance within a coffee business. By leveraging Excel's capabilities, the project transformed raw sales data into intuitive dashboards, enabling stakeholders to quickly grasp critical business metrics, identify trends, and make informed decisions to optimize sales and profitability. 

OBJECTIVES

The primary objectives of this project were to:

• Analyze coffee sales data to identify key trends and performance metrics.

• Develop an Executive Dashboard providing high-level KPIs and overarching sales trends for quick insights.

• Create a Manager Dashboard offering more granular visualizations, including sales and profit breakdowns by coffee type and insights into most-sold products.

• Showcase proficiency in data wrangling, calculated fields, and custom visualization techniques within Excel.

• Provide actionable insights to enhance sales strategies and improve profit margins.

KEY COLUMNS

The analysis drew upon three main datasets:

Orders Dataset:

• Order IDs

• Dates

• Customer Details

• Product Details

• Quantities

• Unit Prices

• Sales Figures

Customers Dataset:

• Customer Names

• Email Addresses

• Loyalty Card Information

Products Dataset:

• Coffee Type

• Roast Type

• Size

• Unit Price

• Profit Margins

TOOLS

The primary tool utilized for this data analysis and dashboarding project was:

• Microsoft Excel: Employed for all stages, including data wrangling, calculations, and the creation of interactive dashboards with custom visualizations.

APPROACH

The project followed a structured approach to transform raw sales data into insightful dashboards:

Phase 1: Data Acquisition & Wrangling

• Imported raw sales data into Excel from the provided source.

• Performed data wrangling tasks, specifically using XLOOKUP to populate missing data or integrate information across the "Orders," "Customers," and "Products" sheets, ensuring data completeness and accuracy.

Phase 2: Calculated Fields & KPI Creation

• Created calculated fields, such as 'Product Details', to generate more descriptive and useful product labels for analysis.

• Developed various Key Performance Indicators (KPIs) relevant to coffee sales, including total sales, total profit, average order value, etc., using conditional formulas where necessary.

Phase 3: Dashboard Design & Visualization

• Designed two distinct dashboards tailored to different stakeholder needs:

  • Executive Dashboard: Focused on high-level KPIs and overall sales trends, providing a quick snapshot for top management.

  • Manager Dashboard: Offered more granular insights, including detailed sales and profit breakdowns by coffee type, roast type, and size, as well as identification of most-sold products.

• Customized charts and graphs extensively to meet the unique visualization requirements of each stakeholder group, ensuring clarity and impact.

Phase 4: Skill Demonstration & Refinement

• Applied advanced Excel skills, including the use of conditional formulas for dynamic calculations and chart customization for effective data storytelling.

• Iteratively refined the dashboards based on usability and clarity, ensuring they provided maximum value.

KEY INSIGHTS

While specific numerical insights depend on the actual dataset, the dashboard's design enables the extraction of valuable information, such as:

• Overall Sales Performance: Quick overview of total sales revenue and profit margins, allowing executives to assess the business's financial health at a glance.

• Sales Trends: Identification of sales patterns over time (e.g., monthly, quarterly, seasonal trends), which can inform marketing campaigns and inventory management.

• Profitability by Product: Detailed breakdown of sales and profit by different coffee types, roast types, and sizes, highlighting which products are most lucrative.

• Top-Selling Products: Identification of the most popular coffee products, guiding inventory decisions and promotional efforts.

• Customer Loyalty: Insights into customer purchasing habits and loyalty card usage can inform customer retention strategies.

• Pricing Effectiveness: Analysis of unit prices and profit margins can inform pricing adjustments to maximize revenue.

IMPACTS

This project delivered significant impacts for the coffee sales business:

• Informed Decision-Making: Provided executives and managers with readily accessible, data-driven insights to make strategic decisions regarding sales, marketing, and product offerings.

• Optimized Sales Strategies: Enabled the identification of high-performing products and sales trends, leading to more effective sales campaigns and promotions.

• Improved Profitability: By highlighting profitable coffee types and areas for cost optimization, the dashboards contribute directly to enhancing the company's bottom line.

• Enhanced Operational Efficiency: Insights into sales patterns and product popularity can streamline inventory management and supply chain operations.

• Clear Performance Monitoring: Established a robust system for continuous monitoring of key sales and profit KPIs, allowing for timely adjustments and performance tracking.

DELIVERABLES

The key deliverables for this project included:

• Excel Workbook with Integrated Data: A well-structured Excel file containing the "Orders," "Customers," and "Products" datasets.

• Executive Dashboard (Excel): A high-level, custom-designed dashboard in Excel, presenting key sales and profit KPIs and trends.

• Manager Dashboard (Excel): A granular, custom-designed dashboard in Excel, offering detailed breakdowns of sales and profit by coffee type and identifying most-sold products.