When and How Much Inventory to Reorder: Excel Template

This is a useful Excel template to manage inventory reordering 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. A Google Sheet version is also provided. Staying on top of your inventory can improve the cash conversion cycle.

$45.00 USD

The template will be immediately available to download after purchase. 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. This is included in the Accounting Templates for Excel and Inventory bundle.

inventory reordering

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 reorder for a given sku.
  • The inventory level at which you should make this order.
  • The amount to reorder
  • 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.