A data-driven strategy to solve real-world inventory challenges for a family-run supermarket, using one year of ledger data to create a production-ready optimization plan.
This project tackles the critical operational challenges of Agarwalla Masala, a residential supermarket. The core goal is to transition the business from reactive decision-making to a proactive, data-driven strategy to enhance profitability and ensure sustainable growth despite significant physical and logistical constraints.
The analysis confirmed a strong Pareto distribution in sales, identified significant inefficiencies in inventory health, and quantified the extreme seasonality of demand.
The top 20% of products (Class A & B) generate 90% of the total revenue, confirming a heavy reliance on a small core of items.
27 SKUs were identified as "Dead Stock," tying up ₹20,930.72 in capital while generating zero sales.
Niche categories like "Puja Items" exhibit extreme volatility, with sales spiking over 3 times their January levels during festivals, while core FMCG sales remain relatively stable.
The initial dataset was plagued with inconsistent entries, with over 280 raw "brand" names for just 222 products due to misspellings and data entry errors. Solution: I developed a rule-based Python script to systematically clean and consolidate 62 ambiguous entries. This meticulous process was crucial for creating an accurate and analyzable master dataset.
My initial plan to merge multiple files failed due to "insurmountable data integrity failures" that produced incorrect financial metrics. Solution: I made the strategic decision to pivot to a "Ledger-Only" methodology. This ensured 100% consistency by deriving all key metrics—sales, cost, and stock flow—from a single, reliable source of truth.
Standard forecasting models were impractical because they don't account for physical storage limits. Solution: I built a custom inventory planning model that calculates a monthly Uplift_Factor for demand and translates it into a concrete Target_Inventory level that respects the store's strict 15-day stock cover, making the recommendations realistic and actionable.
The store owner is not a data analyst and needed simple, actionable tools, not complex code. Solution: The final deliverables included not just a report, but a user-friendly Forward Inventory Plan.xlsx file and a recommendation for a simple daily staff checklist to improve future data quality, ensuring the project's impact would last.