Excel templates / Inventory with reorder alerts
Inventory template with reorder alerts
Stock on hand and its value, a Reorder flag when stock runs low, and how many items to order and what it costs.
Download the inventory with reorder alerts template (.xlsx). Free, no sign-up. Works in Excel 2010 and later, Excel for Mac, Excel on the web, Google Sheets and LibreOffice.
How to use it
- List your items in rows 4 to 15: a code, a name, a category, how many you have, the level that should trigger a reorder, how many to order and the unit cost.
- Update In stock as things come and go. Status turns to Reorder when stock falls to the reorder level.
- The totals show the value of your stock, how many items need reordering and what that order costs.
How it works
H4:H15=D4*G4: The value of the stock on hand: quantity × unit cost.I4:I15=IF(D4="","",IF(D4<=E4,"Reorder","OK")): Says Reorder when stock is at or below the reorder level. The outer IF keeps empty rows blank.B18=COUNTIF(I4:I15,"Reorder"): Counts the items that need reordering.B19=SUMPRODUCT((I4:I15="Reorder")*F4:F15*G4:G15): What the reorder will cost. (I4:I15="Reorder") is 1 for items to reorder and 0 for the rest, so SUMPRODUCT adds up quantity × cost for those items only.
Learn the skills behind it
Functions used: IF, COUNTIF, SUMPRODUCT, SUM.