Download this excel inventory template for free, to help you organize and run your organization, business or hobby. You can manage inventory for the following:
Pizza, retail, warehouse, liquor and beer, food, computer, food, clothing, coin, chemical, books car dealerships, office supplies, school supplies.
Excel Spreadsheet Pictures and Formulas
The spreadsheet contains ten columns but can be expanded for your particular needs.
The following is a description of them and formulas:
Title – This is simple enough, the name of you item.
Attr 1, Attr 2, Attr 2 – These are the attributes of the particular item. In this case, the item happens to be pepperoni for pizza making inventory which might have attributes like food type (attr 1), unit of measure (attr 2), and storage location (attr 3). You can rename the columns to something more specific like type, uofm, location and you can add as many as you want.
Sku – This is label that is usually short and unique per item. It is generated by the following formula:
=IF(LEN($B2) > 2, LEFT($B2, 3), LEFT($B2)) &
IF(LEN($C2) > 2, LEFT($C2, 3), LEFT($C2)) &
IF(LEN($D2) > 2, LEFT($D2, 3), LEFT($D2))
Each if statement corresponds to a attr column and it takes no more than the first three characters of each attr column and adds them together to form the sku. If you add more attr columns simply add another ‘& IF(LEN($D2) > 2, LEFT($D2, 3), LEFT($D2))‘ or not, it’s up to you.
Quantity – a numeric value which is used in the formula in the Reorder column which informs you which items need to be reordered.
Reorder Level – The quantity amount at which point the item should be reordered. It is used in the Reorder column
Reorder – This is a formula, IF($G2/F2 <= 0.2, “ORDER”, $G2/F2), which informs you of the percentage left before the item needs to be reordered. If the percentage of items left falls below 20%, .2, then it displays a red formatted ORDER. You can the percentage level from .2 to whatever is needed.
Description – Description of the item.
Supplier – Information about the supplier for that item.
Inventory Maintenance
When adding a new item, make a copy of an existing row to insert the new one. Copying an existing row will copy all the formulas as well.