favour-ig141/Inventory--Supply-Chain-Analytics

This project simulates a full data-analyst engagement for a fictional pharmacy chain, MedCare Pharmacy Ltd., operating 8 branches, 3 warehouses, 60 suppliers, and ~2,000 SKUs across Nigeria. The brief: identify why the business faces stock-outs, overstocking, drug expiries, supplier delays, and margin pressure, then recommend fixes. # MedCare Pharmacy Ltd. — Inventory & Supply Chain Analytics **Author:** Arigbo Favour ("The British Realtor™" — Data Analytics) **Tools:** PostgreSQL (or SQL Server) · Excel · Power BI · Python (synthetic data generation) **Type:** Simulated enterprise dataset & portfolio analytics project --- ## 1. Project Overview This project simulates a full data-analyst engagement for a fictional pharmacy chain, **MedCare Pharmacy Ltd.**, operating 8 branches, 3 warehouses, 60 suppliers, and ~2,000 SKUs across Nigeria. The brief: identify why the business faces stock-outs, overstocking, drug expiries, supplier delays, and margin pressure, then recommend fixes. > **Note on the data:** all figures are generated from a synthetic dataset built to be internally realistic in *structure and relationships* (SKUs link to sales, purchase orders link to suppliers, inventory reflects both), so the analytical methodology transfers directly to a real engagement. Absolute totals (e.g. total procurement spend vs. total revenue) are not calibrated to reconcile the way a live company's books would, since the sales and procurement volumes were generated independently. Treat percentage-based, ranked, and trend insights as the reliable output of this project; treat raw currency totals as illustrative rather than audited figures. ## 2. Business Questions Answered | Domain | Questions | |---|---| | Inventory | Stock-out frequency, overstock, expiry loss by category, fast vs. slow movers | | Procurement | Supplier delays, quality ranking, lead time, preferred-vendor scoring | | Sales | Revenue by drug/branch/category, payment method mix, age-group demand | | Warehousing | Turnover, expiry rate by warehouse, cold-chain utilization | | Branch Performance | Revenue, profit margin, customer satisfaction, wait times | | Employee Performance | Revenue per pharmacist, experience vs. output, salary efficiency | ## 3. Data Model 10 relational tables, ~470,000 rows total: ``` suppliers ──┐ ▼ purchase …