When and How Much Inventory to Re-Order: Excel Template

This is a useful excel template (I built it in google sheets as well) to manage inventory re-ordering. Based on a few variable inputs per SKU, the logic in this model will display useful metrics to help you stay on top of inventory levels.


Pricing
After purchase, the template will be sent to you. When you open the google sheet link, be sure to hit File>Make a Copy in order to have your own copy on your own drive. This requires a free google account.

Each row represents a unique SKU that you are tracking and the following inputs will drive the model:
  • Current units on-hand at end of day.
  • Minimum reserve units.
  • Avg. sales per day (logic built for calculating this)
  • Lead time
  • Order multiple
Based on the above, you will be told:
  • The estimated date you will have to make a re-order for a given sku.
  • The inventory level at which you should make this order.
  • The amount to re-order
  • The number of days you have until you should make an order for a given sku.
There is conditional formatting involved that has three tiers for when you will have to re-order inventory i.e. if it is less than 5 days, you will see red, less than 30 days is yellow, and over 30 days is green. Advanced filter functions were also built for this in the google sheet version. In the excel version, I had to make 3 columns where you simply choose all '1' values in the filter at the top and it will display sku's that meet a given amount of days to re-order input.

Rounding was involved in the calculations as it is not useful to have half days / half units in the calculations.

Instructions come with the template.

You may also like the 12-month inventory cash flow planner that is based on inventory re-stocking logic.