Project Overview

This project focuses on optimizing procurement and supply chain resilience for an online storefront called Horizon Marketplace. The business faces challenges with shrinking profit margins, tied-up cash, and customer complaints due to stockouts. The analysis aims to identify unreliable suppliers, quantify revenue lost from empty shelves, and develop an automated system for reordering stock.

Data Infrastructure and Database Design

The analytical pipeline integrates raw database logs, sales ledger transactions, and daily warehouse inventory snapshots. The database schema includes tables for products, sales, purchase orders, and daily inventory, establishing relationships between them to track product flow and stock levels.

Key Findings and Analytical Insights

• Supplier Performance: Analysis revealed that suppliers have unreliable delivery timelines. Alpha Logistics shows a high Perfect OTIF rate, while Delta Corp has a significantly lower rate and frequent delays, impacting inventory availability.

• ABC Inventory Tiers: Products are categorized into tiers based on revenue contribution. Class A products (23 items) generate 80% of the revenue, while Class C products are slow-moving and consume valuable warehouse space.

• Stockout Impact: Significant revenue loss ($63,897.26 for one product) was attributed to stockouts caused by supplier delays, highlighting the critical need to maintain inventory for high-demand items.

• Restocking Engine: A predictive restocking matrix is proposed, calculating safety stock and reorder points based on sales volatility and supplier lead times to prevent stockouts.

• Warehouse Performance: Both regional warehouses (WH-EAST and WH-WEST) demonstrate similar efficiency, indicating that the core issues stem from upstream supplier performance rather than internal operations.

Strategic Recommendations

The project recommends the following actions:

1. Supplier Accountability: Implement performance improvement plans for unreliable suppliers like Delta Corp or reallocate their volume.

2. Automated Ordering: Automate the reordering process for top-selling products based on calculated safety stock limits and reorder triggers.

3. Inventory Liquidation: Reduce purchases of low-velocity Class C products and liquidate existing slow-moving stock to free up cash and warehouse space.