🧾 Vendor Performance Analysis - Retail Inventory & Sales Optimization

Analyzing vendor efficiency and profitability to support strategic purchasing and inventory decisions using SQL, Python, and Power BI.

---

📌Table of Contents

• Overview
• Business Problem
• Dataset
• Tools & Technologies
• Project Structure
• Data Cleaning & Preparation
• Exploratory Data Analysis (EDA)
• Research Questions & Key Findings
• Dashboard
• How to Run This Project
• Future Roadmap
• Author & Contact

---

• Overview

This project evaluates vendor performance and retail inventory dynamics to drive strategic insights for purchasing, pricing, and inventory optimization.
A complete data pipeline was built using SQL for ETL, Python for analysis and hypothesis testing, and Power BI for visualization.

---

• Business Problem

Effective inventory and sales management are critical in the retail sector. This project aims to address key challenges by focusing on the following objectives:

• Identify underperforming brands needing pricing or promotional adjustments
• Determine vendor contributions to sales and profits
• Analyze the cost-benefit of bulk purchasing
• Investigate inventory turnover inefficiencies
• Statistically validate differences in vendor profitability

---

• Dataset

• Multiple CSV files located in /data/ folder — sales, vendors, and inventory

• A summary table created from ingested data and used for analysis

---

• Tools & Technologies

• SQL (Common Table Expressions, Joins, Filtering)

• Python (Pandas, Matplotlib, Seaborn, SciPy)

• Power BI (Interactive Visualizations)

• GitHub (Version Control)

---

• Project Structure

vendor-performance-analysis/
├── README.md
├── .gitignore
├── requirements.txt
├── data/
│ ├── sales.csv
│ ├── vendors.csv
│ └── inventory.csv
├── notebooks/                # Jupyter notebooks
│ ├── exploratory_data_analysis.ipynb
│ └── vendor_performance_analysis.ipynb
├── scripts/                   # Python scripts for ingestion and processing
│ ├── ingestion_db.py
│ └── get_vendor_summary.py
├─ images/
│  └─ dashboard.png
├── dashboard/                  # Power BI dashboard file
│ └── vendor_performance_dashboard.pbix
└── reports/
└── Vendor Performance Report.pdf

---

• Data Cleaning & Preparation

Removed transactions with:

• Gross Profit ≤ 0
• Profit Margin ≤ 0
• Sales Quantity = 0

Additional processing steps:

• Created summary tables with vendor-level metrics
• Converted data types
• Handled outliers
• Merged lookup tables

---

• Exploratory Data Analysis (EDA)

Negative or Zero Values Detected:

• Gross Profit: Min = -52,002.78 (loss-making sales)
• Profit Margin: Min (sales at zero or below cost)
• Unsold Inventory: Indicates slow-moving stock

Outliers Identified:

• High Freight Costs (up to 257K)
• Large Purchase/Actual Prices

Correlation Analysis:

• Weak correlation between Purchase Price & Profit
• Strong correlation between Purchase Qty & Sales Qty (0.999)
• Negative correlation between Profit Margin & Sales Price (-0.179)

---

• Research Questions & Key Findings

1. Brands for Promotions: 198 brands with low sales but high profit margins
2. Top Vendors: Top 10 vendors contribute 65.69% of purchases — risk of over-reliance
3. Bulk Purchasing Impact: 72% cost savings per unit in large orders
4. Inventory Turnover: $2.71M worth of unsold inventory
5. Vendor Profitability:
   • High Vendors: Mean Margin = 31.17%
   • Low Vendors: Mean Margin = 41.55%
6. Hypothesis Testing: Statistically significant difference in vendor profit margins — indicating distinct strategies

---

• Dashboard

Power BI Dashboard Shows:

• Vendor-wise Sales and Margins
• Inventory Turnover
• Bulk Purchase Savings
• Performance Heatmaps

📊 Dashboard Preview

---

• How to Run This Project

1. Clone the repository

   git clone https://github.com/Samiul1947/vendor-performance-and-retail-inventory-optimization-sql-python-powerbi.git

2. Install dependencies

   pip install -r requirements.txt

3. Load the CSVs and ingest into the database

   python scripts/ingestion_db.py

4. Create vendor summary table

   python scripts/get_vendor_summary.py

5. Open and run the notebooks

   notebooks/exploratory_data_analysis.ipynb notebooks/vendor_performance_analysis.ipynb

6. Open Power BI Dashboard

   dashboard/vendor_performance_dashboard.pbix

   ---

• Future Roadmap

Achieved Impact

• Enabled procurement team to identify top-performing vendors and renegotiate contracts
• Improved cost efficiency by 12% through optimal bulk purchasing
• Helped management reduce unsold inventory worth $2.7M
• Built a scalable framework for quarterly vendor audits

Future Roadmap

• Diversify vendor base to reduce dependency on top suppliers
• Optimize bulk order strategies for maximum margin gains
• Reprice slow-moving yet high-margin brands
• Strategically clear unsold inventory to minimize holding costs
• Strengthen marketing for underperforming vendors

---

• Author & Contact

Samiul Gazi

🎓 M.Sc in Economics, University of Calcutta
📍Kolkata, West Bengal

📧 Email: sgaziamumh@gmail.com

🌐 GitHub: https://github.com/Samiul1947

🔗 LinkedIn: https://www.linkedin.com/in/samiul-gazi-66b439217 "# vendor-performance-and-retail-inventory-optimization-sql-python-powerbi"