Every business’s needs are different, but we found that you can’t go wrong creating these three main tabs on your Excel inventory tracker. We recommend entering all your orders, sales, and inventory counts on the following tabs.
The Products tab on your Excel doc should be your complete inventory list. This sheet will help you keep track of all your current and discontinued items, as well as the vital information you need to assess product performance, determine which items to reorder, and store product details.
To create this sheet, we recommend creating a table with the following data points as columns:
- Item number—List the serial numbers, SKU numbers, or product numbers you’re using to track each item on your list.
- Category—Include the sales category for each item so you can track performance and revenue on a category-by-category basis.
- Product name—Share the name of each product so you know which items correspond to the item numbers you’re using.
- Description—Include short descriptions of your products (just in case the product name doesn’t make it clear which item you’re dealing with).
- List price—Note the price you’re selling each item for.
- Stock location—List the warehouses, shelves, or bin numbers where your items are being stored. This makes it easier to track them down when you need them.
- Quantity in stock—Note the numbers of items currently on your shelf.
- Inventory value—Share the total values each of your inventory items represent (based on the cost per item and the number of items in your current stock)
- Reorder level—List the reorder points for each of your products.
- Cost per item—Include how much you pay per item.
- Vendor—Share the names of the vendors who supply each product.
- Date of last reorder—Note the most recent dates when you reordered your products.
- Days per reorder—List the average times between your reorder dates and the dates you receive each product.
- Stock waiting to be received—Include the numbers of items you’ve already ordered but haven’t yet received.
- Discontinued?—Note whether each item is discontinued or not.
Once you’ve got a basic table going, you can kick your Product tab up a notch with extra goodies. For starters, you may opt to have Excel calculate your inventory values by using a formula (which would automatically multiply the values in your “Quantity in Stock” and “Cost Per Item” columns). That way, you can know for sure that your inventory values are accurate as long as you input your data correctly.
For advanced customization, you could apply conditional formatting to this tab so any products with stock levels below your reorder point are highlighted in red. This would make it easier for you to quickly identify which products require your attention. Or you could use a complex formula to auto-calculate the amount of stock on its way based on the number of items entered in your Orders tab (see below).
If all that sounds overwhelming, just remember you can always use a template that’s preformatted and includes formulas for easy calculations.