1. Background
A mid-sized retail business specialising in consumer electronics faced significant challenges with inventory management. The company frequently experienced stockouts of high-demand products, leading to lost sales opportunities. At the same time, slow-moving items occupied valuable storage space, increasing holding costs and reducing profitability. The lack of a data-driven approach to inventory forecasting resulted in inefficient purchasing decisions and operational inefficiencies.
2. Analysis & Approach
To identify the root causes of the inventory challenges, a structured analysis was conducted using historical sales data, supplier lead times, and customer demand patterns. The approach involved:
- Sales Trend Analysis: Extracted and analysed three years of sales data using Excel Pivot Tables to identify seasonal trends and high-demand products.
- Inventory Turnover Analysis: Calculated stock turnover ratios for each product category to determine slow-moving and fast-selling items.
- Supplier Performance Evaluation: Assessed supplier lead times and order fulfilment accuracy to pinpoint delays contributing to stockouts.
- Customer Demand Forecasting: Used SQL queries to segment sales data and identify demand patterns across different product categories.
Findings revealed that 30% of storage space was occupied by slow-moving items, while popular products frequently ran out of stock due to inaccurate demand forecasting. Additionally, suppliers had inconsistent lead times, causing unexpected inventory shortages.
3. Solution & Implementation
A data-driven inventory management model was developed to optimise stock levels based on sales performance analysis and demand forecasting. The solution involved:
- Dynamic Reordering System: Implemented an Excel-based model that used historical sales data to forecast demand and automatically adjust reorder points.
- ABC Inventory Classification: Categorised products based on sales contribution (A: High, B: Moderate, C: Low) to prioritise stock replenishment efforts.
- Supplier Coordination Strategy: Negotiated new supplier agreements with clear delivery timelines to ensure better stock availability.
- A/B Testing for Stock Adjustments: Tested the impact of adjusting stock levels for key products by analysing sales data over a three-month period.
4. Results & Business Impact
The implementation of the new inventory management approach led to significant improvements in efficiency and profitability:
- Stockouts reduced by 20%, leading to increased revenue and improved customer satisfaction.
- Excess inventory reduced by 15%, cutting down storage costs and improving warehouse utilisation.
- Improved order accuracy, ensuring better alignment between customer demand and stock availability.