Inventory and Stock Monitoring - End - to - End Business Analysis

Power BI Dashboard

Executive Summary

Overstock Analysis

Reorder & Operations

Project Overview

This project analyzes inventory performance across products, categories and warehouses to identify inefficiencies in stock management, overstock exposure, and operational risks.

Tools and Technologies

• PostgreSQL(pgAdmin 4) → database setup and schema management
• SQL (VS Code) → data cleaning, transformation and analysis
• Power BI → dashboard visualization

Data Workflow

Data Loading (pgAdmin4)

• Loaded 730 raw inventory records (CSV) into PostgreSQL
• Created structured schemas:
  • raw → original data
  • clean → cleaned dataset
  • analytics → business-ready data

Data Exploration and Quality Checks (VS Code)

• Performed using SQL
• Row count validation
• Duplicate detection (primary key: record_id)
• Missing values analysis
• Data inconsistencies

Data Cleaning and Standardization (VS Code)

• Removed duplicate records
• Standardize product_id format
• Trimmed and normalized text fields
• Converted numeric and date columns to proper data types
• Handled null and invalid values
• Ensured consistency across categories, warehouses, and suppliers

Analytics Layer (Business Metrics)

Created analytics.inventory_metrics including:

• Inventory value (cost and price)
• Stock gap vs reorder level
• Units to reorder
• Days of cover
• Stock to sales ratio
• Stock risk levels (Critical, High, Medium, Low)
• Overstock and dead flags
• Lead time risk

Dashboard Overview

Executive Summary

Provides a high level overview of:

• Total inventario value
• % Overstock
• % Dead stock
• Overstock exposure
• Urgent restock needs

Overstock Analysis

Focuses on excess inventory

• Overstock items
• Overstock by category
• Overstock by warehouse
• Top 10 Overstock products by value
• Dead stock items requiring action

Reorder and Operations

Focuses on shortages and operational risk

• Total units to reorder
• Estimated reorder cost
• Units to reorder by category and warehouse
• Reorder action list

Key Insights and Businnes Issues

Overstock and capital inefficiency:

• Inventory inefficiency is driven primarily by excess stock rather than stock shortages. The majority of products are overstocked, indicating potential over-purchasing or weak demand forecasting, while only a small portion of items require replenishment

• Most inventory is low risk, but overstock exposure is extremely high, tying up significant capital.

• Despite excess inventory some products still experience shortages, this indicates an inventory distribution problem, not a supply problem.

-Inventory is not only excessive, but also poorly distributed across warehouses, suggesting inefficiencies in inventory allocation and replenishment strategy

This indicates:

• Inefficient demand forecasting
• Overstock purchasing decisions
• Poor inventory turnover

Business Impact

• Reduce cash flow
• Increased storage costs
• Higher risk of products becoming dead stock

Dead Stock Risk

Identified 33 dead stock items, representing $1.2M in non-moving inventory

Business Impact

• Direct financial loss risk
• Warehouse space inefficiency
• Indicates lack of liquidation or discount strategies

Data Quality Issues Identified

Missing / Unknown values:

• Some records contain:
• Unknown category
• Unknown warehouse

This indicates:

• Imcomplete data entry
• Weak data validation processes

Inconsistent naming conventions:

For example (SOUTH HUB vs WH-A)

This indicates:

• Lack of standardized master data
• Potential reporting inconsistencies

Impact on Analysis

Data inconsistencies can:

• Skew category-level insights
• Affect warehouse performance analysis
• Reduce trust in reporting

Recommendations

Inventory optimization:

• Implement demand forecasting models
• Adjust reorder strategies
• Monitor stock-to-sales ratios

Overstock reduction strategy:

• Run promotions or discounts for overstock items
• Reallocate inventory across warehouses
• Introduce inventory aging tracking

Data quality improvements

• Enforce mandatory fields (category, warehouse)
• Standardize naming conventions (master data table)
• Implement validation rules at data entry

Project Structure

INVENTORY_STOCK_MONITORING/
│
├── 01_data/
│   └── inventory_stock_raw.csv
│
├── 02_sql/
│   ├── 01_exploration.sql
│   ├── 02_cleaning_inventory.sql
│   ├── 03_validation.sql
│   ├── 04_inventory_metrics.sql
│   ├── 04_validation_metrics.sql
│   └── 05_inventory_analysis.sql
│
├── 03_powerbi/
│   └── inventory_stock_dashboard.pbix
│
├── 04_images/
│   ├── 01_inventory_executive_summary.png
│   ├── 02_overstock_analysis.png
│   └── 03_reorder_&_operations.png
│
└── README.md

Author

Yorman Gomez