📊 Fruit & Produce Sales & Inventory Operations Analysis

An end-to-end Power BI Business Intelligence solution that transforms raw sales, inventory, and customer data into actionable insights for retail operations and inventory planning.

---

📌 Overview

Retail sales and inventory management require continuous monitoring of product margins, customer demographic spending habits, and top-performing inventory items. This project analyzes multi-region transaction data across fruits and vegetables to identify key revenue drivers, assess discount impact, and surface inventory velocity metrics for store management.

---

📑 Table of Contents

1. Dataset
2. Tools and Technologies Used
3. Business Objectives
4. Key Findings
5. Executive Summary
6. Recommendations
7. Limitations

---

📂 Dataset

The project utilizes the Fruit Sales Data - Inventory and Sales.xlsx dataset, structured across three core relational entities:

• Sales Table: Contains transactional records, quantities sold, total revenue, unit prices, discounts applied, and foreign key mappings.
• Products Table: Details product names, sub-categories (Fruit vs. Vegetable), and pricing matrices.
• Customers Table: Contains demographic metadata including customer names, country locations (Belgium, France, Nederland), birth dates, and gender identifiers.

Data Preprocessing (Power Query Transformations)

• Table Relationships & Merges: Merged Sales with Customer via FK_Customer = PK_Customer and Sales with Product via FK_Product = PK_Product.
• Data Standardisation: Replaced gender abbreviations (M → Male, F → Female) for improved visual reporting clarity.
• Feature Engineering: Extracted Birth Year from the Birthdate field to support demographic age-cohort analytics.

---

🛠 Tools and Technologies Used

• Power BI Desktop: Core platform for data modeling, DAX measures, visual representation, and dashboard design.
• Power Query Editor: ETL tool used for data transformation, field replacements, relationship mapping, and data type formatting.
• Data Analysis Expressions (DAX): Used to compute core KPIs including Total Sales, Volume Sold, and Total Discounts.
• Microsoft Excel: Raw data source repository.

---

🎯 Business Objectives

The analysis addresses ten specific business operational questions:

1. Identify the top-tier customer driving maximum purchase quantities.
2. Provide detailed cross-tabulation of Quantity, Revenue, and Unit Price grouped by Product Name and Category.
3. Evaluate unit pricing distribution across products to identify luxury/premium items.
4. Analyze revenue trends based on customer birth cohorts.
5. Determine the Top 3 Products by Total Revenue generated.
6. Determine the Top 3 Products by Total Sales Volume (Quantity).
7. Identify the Bottom 3 Products by Sales Volume to spotlight underperforming stock.
8. Measure global Total Amount (Gross Revenue).
9. Measure global Total Quantity Sold.
10. Quantify global Total Discounts granted to customers.

---

🔍 Key Findings

• Top Revenue Drivers: Asparagus, Avocado, and Apple generate the highest aggregate revenue, driven by a combination of high unit price and consistent volume.
• Volume Leaders: Fast-moving consumer produce such as Bananas and Apples dominate total units sold, serving as key traffic drivers.
• Premium Pricing Outlier: Asparagus stands as the single most expensive item in the inventory ($12.12/unit), generating high margins despite lower overall volume.
• Demographic Sweet Spot: Customers born between 1980 and 1995 represent the highest spending cohort, contributing significantly to overall gross revenue.
• Underperforming Produce: Items in the bottom tier by quantity demand re-evaluation regarding shelf life, promotional positioning, or stock reduction.

---

📊 Executive Summary

The Sales and Inventory Analysis dashboard reveals a healthy distribution between high-volume impulse fruits (Apples, Bananas) and high-margin specialty produce (Asparagus). While high unit pricing elevates Asparagus to the top spot for total monetary turnover, its sales volume remains among the lowest in the catalog—indicating price elasticity at play. Promotional discounts successfully drive volume sales in core fruit lines, but discount allocation on premium vegetables requires tighter yield management to prevent unnecessary margin erosion.

---

🚀 Recommendations

1. Inventory Stocking Strategy: Increase safety stock and inventory turnaround frequency for volume leaders (Apples and Bananas) to prevent stockouts during peak operational hours.
2. Targeted Pricing Strategy: Introduce bundled packaging for premium items like Asparagus alongside high-volume staples to boost units-per-transaction (UPT) without sacrificing unit margins.
3. Targeted Demographic Campaigns: Tailor promotional loyalty initiatives to the 30–40 age demographic (Birth Years 1985–1995), who generate the highest overall lifetime spending value.
4. Discount Optimization: Audit discounting tiers on low-volume items (Broccoli, Cherry) to evaluate whether promotional pricing stimulates sales volume or simply reduces gross profit margin.

---

⚠️ Limitations

• Temporal Depth: The dataset captures transactional snapshots without explicit multi-year historical tracking, limiting YoY (Year-over-Year) growth rate benchmarking.
• Stock Tracking: Inventory balance reflects point-in-time snapshot quantities rather than real-time warehouse stock feeds or supplier lead-time metrics.
• Demographic Scope: Customer locations are limited to three European countries (Belgium, France, Nederland), constraining global market extrapolations.

---

🧮 Some Calculated Columns & DAX Measures used in this Project

To maintain high report performance, calculated columns were strictly limited to dimensional context, while dynamic measures were leveraged for aggregated KPI calculations.

Calculated Columns (Data Model Context)

// Extract Birth Year for Demographic Grouping
BirthYear = YEAR(Customers[Birthdate])

// Is Weekend-Flags whether a specific date falls on a weekend for filtering purposes.
Is Weekend = IF(WEEKDAY('Calendar'[Date], 2) >= 6, "Weekend", "Weekday")

Core Calculated Measures (DAX)

// Total Sales Revenue
Total Amount = SUM(Sales[TotalAmount])

// Total Unit Volume Sold
Total Quantity = SUM(Sales[Quantity])

// Aggregate Discount Given
Total Discount = SUM(Sales[Discount])