This template is designed to keep track of many inventory SKUs. The main benefit is in organization and logic to look at lead times / current inventory / expected sales / safety stock and then spit out a date in the future that inventory is likely to need to be reordered by so that you don’t run out.
There is as much automation around the calculations as possible and based on the user inputs, this template lets the user easily manage various frequencies that things need to be reordered and what is or should be upcoming based on the current state of inventory and expected sales.
There is a Google Sheet version (link on the ‘instruction’ tab) that has a little more automation in the filtering of data and formulas.
One of the features that both the Excel and Google Sheet version have is that you can define the number of days until reorder is triggered to happen and see all SKUs that meet that criteria. This makes it easy to see what is coming up in the next 5 days, 10 days, 30 days, etc.. and you can define the number of days that each filter runs for.
The following is a data input:
- On-Hand Units
- Reorder in ‘x’ days (formula)
- Inventory Level to reorder (formula)
- Required Reserve (safety stock)
- Start Date / End Date / Total Sales (for forecasted sales per day expectation)
- Lead Time (days)
- Current Date (auto-populated with formula)
- Expected Order Date (formula)
- Order ‘x’ of Reorder Level
- Amount to Order (formula)