🧾 Vendor Performance Analysis – Retail Inventory & Sales

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
• Final Recommendations
• 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.

📄 For detailed study of this project: View Full Report

---

❗ Business Problem

Effective inventory and sales management are critical in the retail sector. This project aims to:

• 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, inventory)
• Summary table created from ingested data and used for analysis
• ~200K+ rows | 6 CSV files | Vendors, Sales, Inventory, Pricing

---

🛠️ Tools & Technologies

Tool	Usage
SQL	CTEs, Joins, Filtering, ETL
Python	Pandas, Matplotlib, Seaborn, SciPy
Power BI	Interactive Visualizations & Dashboard
GitHub	Version Control

---

🔄 Architecture / Data Pipeline

CSV Files (Raw Data)
        ↓
SQLite Database (ingestion_db.py)
        ↓
SQL ETL & Filtering
        ↓
Vendor Summary Table (get_vendor_summary.py)
        ↓
Python Analysis (Pandas · SciPy · Matplotlib · Seaborn)
        ↓
Power BI Dashboard (vendor_performance.pbix)

📁 Project Structure

vendor-performance-analysis/
│
├── README.md
├── .gitignore
├── requirements.txt
├── Vendor Performance Report.pdf
│
├── 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
│
├── dashboard/                  # Power BI dashboard file
│   └── vendor_performance_dashboard.pbix

---

🧹 Data Cleaning & Preparation

• Removed transactions with:
  • Gross Profit ≤ 0
  • Profit Margin ≤ 0
  • Sales Quantity = 0
• 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: Indicating slow-moving stock

Outliers Identified:

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

Correlation Analysis:

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

---

📊 Research Questions & Key Findings

#	Question	Finding
1	Brands for Promotions	198 brands with low sales but high profit margins
2	Top Vendors	Top 10 vendors = 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: 31.17% margin vs Low Vendors: 41.55% margin
6	Hypothesis Testing	Statistically significant difference in profit margins → distinct vendor strategies

---

📊 Dashboard

Power BI Dashboard shows:

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

---

▶️ How to Run This Project

1. Clone the repository:

git clone https://github.com/Sahajahanur/vendor-performance-analysis.git

2. Load the CSVs and ingest into database:

python scripts/ingestion_db.py

3. Create vendor summary table:

python scripts/get_vendor_summary.py

4. Open and run notebooks:

notebooks/exploratory_data_analysis.ipynb
notebooks/vendor_performance_analysis.ipynb

5. Open Power BI Dashboard:

dashboard/vendor_performance_dashboard.pbix

---

✅ Final Recommendations

• Diversify vendor base to reduce over-reliance risk
• Optimize bulk order strategies for maximum cost savings
• Reprice slow-moving, high-margin brands for better sales velocity
• Clear unsold inventory strategically to free up $2.71M
• Improve marketing support for underperforming vendors

---

📬 Contacts

Sahajahanur Rahman

I’m always open to discussing Data Analytics, Machine Learning, Streamlit Apps, and End-to-End Projects!