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).
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.