This project simulates a real-world demand planning workflow for a retail environment. Using Excel, I built a demand forecasting model, calculated inventory policy parameters, and ran scenario testing to Identify the most efficient reorder strategy.
The analysis mirrors the core responsibilities of a Demand Planner or Inventory Analyst — forecasting future demand, setting reorder points points, and balancing stockout risk against order frequency.
Retailers face a constant balancing act between holding too much stock (increasing costs) and too little (risking stockouts and lost sales). This project addresses that challenge by:
- Forecasting daily demand using historical sales data
- Calculating safety stock and reorder points based on lead time and demand variability
- Testing multiple inventory policies to find the most efficient approach
- Microsoft Excel — FORECAST.ETS, PivotTables, inventory simulation logic, scenario comparison
- Statistical methods — MAPE for forecast accuracy, safety stock formula, reorder point calculation
- Real Superstore retail dataset spanning 2018 to 2021
- 9,995 order records across multiple product categories, regions and shipping modes
- Split into 80% training (rows 2-991) and 20% test set (rows 992-1237) for forecast validation
Extracted and aggregated daily sales totals using PivotTables, creating a clean date-indexed dataset ready for forecasting.
Applied Excel's FORECAST.ETS function to predict daily sales across the test period, with a line chart comparing actual vs forecasted values to visualise forecast accuracy.
Calculated core inventory management metrics:
- Average Daily Demand: 1,712 units
- Average Lead Time: 3.96 days
- Safety Stock: 7,367 units (at 95% service level)
- Reorder Point (ROP): 14,215 units
Built a daily stock tracking model monitoring beginning inventory, forecasted demand, ending inventory, and Automatic reorder triggers when stock falls below ROP.
Tested three inventory policies to evaluate the impact of changing reorder parameters:
| Scenario | ROP | Reorder Qty | # of Reorders |
|---|---|---|---|
| Baseline | 14,215 | 3,424 | 107 |
| Lower ROP | 12,000 | 3,424 | 106 |
| Increased RQ | 14,215 | 5,000 | 73 |
- Increasing the reorder quantity from 3,424 to 5,000 units reduced total reorders by 32% (from 107 down to 73) — significantly lowering operational ordering costs
- Lowering the ROP had minimal impact on reorder frequency, confirming that reorder quantity is the more influential lever
- Zero stockouts occurred across the simulation period — minimum ending inventory held at 7,637 units, validating The safety stock calculation
- Lead time varied significantly by shipping mode — Standard Class averaged 5.0 days vs Same Day at 0.04 days, highlighting the importance of shipping mode selection in inventory planning
- Adopt the increased reorder quantity policy (RQ = 5,000) to reduce ordering frequency while maintaining stock security
- Maintain ROP at 14,215 to protect against stockout risk given current lead time variability
- With cost data available, apply an Economic Order Quantity (EOQ) model to further optimise the reorder quantity
The FORECAST.ETS model showed high error on peak demand days, reflecting the challenge of forecasting volatile daily sales without promotional or seasonal input data. In a real-world setting, this would be improved by incorporating external demand drivers such as promotions, seasonality flags, and product-level forecasting.
- Incorporate holding and ordering costs to calculate the true EOQ
- Add supplier performance data to model variable lead times
- Expand to SKU-level forecasting by product category
- Migrate the simulation and dashboard to Power BI for enhanced interactivity
Complete — open to feedback and collaboration