Automated FIFO and Shelf-Life Order Planning
An Excel and Power Query planning system designed to allocate stock using FIFO logic, expiry-date rules, customer shelf-life requirements, and clearer picking workflows.
Problem
Planning orders manually can become difficult when different stock batches, expiry dates, article types, customer requirements, and available quantities all need to be considered at the same time.
Dressing stock needed to be picked based on expiry rules and customer-specific minimum remaining shelf life, while pumps needed to follow FIFO logic based on manufacturing date rather than expiry date.
Solution
I built an automated planning workflow in Excel and Power Query that connects order data, stock data, article logic, expiry information, and batch information into one structured planning output.
The system helps suggest suitable stock picks, avoid invalid batches, respect shelf-life requirements, and reduce the amount of manual checking needed before preparing an order.
Key Features
- FIFO allocation for pumps based on manufacturing date
- Expiry-date based allocation for dressings
- Customer-specific minimum shelf-life rules
- Automatic stock selection using Power Query logic
- Support for sets, multipacks, pumps, dressings, and foam
- Clear picking output with lot, quantity, remaining stock, and expiry date
- Reduced manual checking and fewer planning mistakes
Tools Used
- Excel
- Power Query
- Structured tables
- Formula logic
- Order planning workflows