Inventory list with reorder highlighting Excel

How to Manage Your Inventory in Excel

Every businesss needs are different, but we found that you cant 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 numberList the serial numbers, SKU numbers, or product numbers youre using to track each item on your list.
• CategoryInclude the sales category for each item so you can track performance and revenue on a category-by-category basis.
• Product nameShare the name of each product so you know which items correspond to the item numbers youre using.
• DescriptionInclude short descriptions of your products (just in case the product name doesnt make it clear which item youre dealing with).

Once youve 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 thats preformatted and includes formulas for easy calculations.

Next, we recommend creating a tab to track all your purchase orders. This helps you monitor your supply chain and keep up with any outstanding orders.

Like in the Product tab, we recommend you create a table. List each product from each order on a separate line with the following columns as headers:

• Order numberUse the numbers youre using to track your purchase orders with your vendor.
• Item numberInput the SKUs, serial numbers, or product numbers for each product. Make sure they match the item numbers you used on your Product tab. And again, make sure you list each item as a separate row in your Orders tab (even if theyre on the same purchase order as another item).
• CategoryList the product categories for each item youve ordered.
• Product nameShare the names of your products.
• Purchase dateShow the date you placed the order for each item.
• Stock received dateNote the dates when you received the ordered products.
• VendorInput the names of the vendors you ordered each item from.
• QuantityShow the quantities you ordered of each item.
• Cost priceList the prices you paid for each item.
• AmountInput the total values of the items ordered.

Again, you can choose to make this tab as simple or complex as you like. We think the data points listed above should be sufficient for most businesses, but youre free to add more columns if you want your inventory tracker to monitor other factors (like bills of materials). Some of these data points will also be the same as in the Products tab above. This helps keep your business organized and ensures the right information is present for your tracking.

If youre big on the conditional formatting, you could set up a rule that automatically highlights any items in your worksheet that havent been delivered on time. Or you could create a dropdown list for your product categories so you dont have to manually type them in every time. Theres no single right answer for how to set up your inventory management and control, so feel free to personalize your Excel sheet (or template) to fit your needs.

Last but not least, we think no inventory management solution is complete without a way to track your sales.

Once again, we recommend setting up a table with the following columns:

• Sales order numberWrite in the numbers youre using to identify and track each customer order or purchase. If youre selling inventory on multiple channels (such as in person and via your e-commerce site, for example), you may opt to list order numbers for only online orders.
• Item numberAs with your Product and Order tabs, list the SKUs, serial numbers, or product numbers youre using to track your items. And again, be sure to list each product on a separate line (even if its part of a larger order with other items).
• Product nameShare the names of your products.
• QuantityNote the numbers of items each client ordered.
• Sale dateInput the date when the sale was made.
• Ship deadlineShow the dates when each item must be shipped to get to your customers on time.
• Ship dateList the dates when each item was actually shipped.
• Tracking numberIf you have it, share the tracking numbers for each shipped item (so you can track it en route).
• ClientList the names or customer loyalty numbers associated with the clients on each order.
• Retail priceNote how much your customers paid for each item they ordered.
• AmountShow the total dollar amounts you received from each customer for the items they ordered.

We know we sound like a broken record, but from here you can customize your inventory and sales tracking even further. In our template, we added conditional formatting to highlight outstanding online orders that didnt get shipped in time. Or you could create a formula to automatically calculate the Amount value on each order (by multiplying your Retail Price and Quantity values).

Excel offers tons of functionality, so with a little work, you could definitely bring your Excel spreadsheet up to the same level as a basic inventory software.

One way to get your spreadsheet to that point would be to create another tab for reporting. Since your inventory list, your Order tab, and your Sales tab are all formatted as tables, you can pull data from those tabs to create custom reports that can be adjusted according to your needs. These can be really helpful for quarterly reports and month-over-month tracking.

You could also add a tab just for doing reorder point calculations. That way, you dont have to guess at the ideal inventory levels for reordering each of your productsyou can use a formula to do precise calculations for you.

Another option? If youre not interested in doing manual counts of your inventory every day, you could create another tab just for conducting physical inventory. This tab would list all your products by item number, as well as a place to record physical counts of each item. Once you have this data in place, you could use an advanced formula to rig your Product tab to auto-calculate your current stock for each item based on the number of sales and purchases youve completed since your last physical inventory count.

And as weve mentioned already, there are plenty of free templates available online that would deliver these features with minimal work on your part. So if youre feeling intimidated by the sheer amount of Excel knowledge needed to execute any of the ideas above, a template could be a big help.

Excel is a good tool, but it certainly isnt infallible. Here are our top tips and tricks to make Excel work for your business:

• Keep things simple. While Excel is certainly capable of tracking every minute detail, you dont want to spend all day every day doing data entry. The more things you have to track, the more work it is for you and the harder it is to keep accurate tabs on the state of your inventory. Plus, Excel spreadsheets with hundreds of columns are hard to navigate, so chances are small youll ever be able to find what you need.
• Update, update, update. Excel cant track incoming shipments and outgoing sales automatically, so its on you to keep your spreadsheet up to date. Try to update your Excel sheet immediately after receiving new shipments or processing a customer order. Itll help you stay confident that your inventory tracker is accurate, and itll keep you from forgetting that you have 50 extra items sitting on the shelf in your back room.
• Put it in the cloud.Storing your Excel sheet in the cloud allows access to the document from multiple devices. That way, everyone who needs to can view and update your inventory tracker. Just make sure you have the co-authoring feature turned on, or youll wind up with two different versions of your spreadsheet anytime multiple people try to make changes at once.
• Audit yourself.A single data entry error on your spreadsheet could have serious effects that ripple through your business for months afterward. So be extra careful to review your inventory management document often to screen for errors. You should also go over your spreadsheet regularly and try to evaluate whether your methods for tracking products, orders, and sales are realistically working for you.
• Consider adding hardware.For a really robust inventory management tracker in Excel, try integrating barcode scanners. Many of the top barcode scanners on the market can upload data directly to Excel, so they may be able to save you some time and improve accuracy on your data entry.
• Work with your data. If youre new to Excel, you can take pictures of analog spreadsheets and easily convert the picture to a fully editable Excel spreadsheet. Once the data is in, use formatting, sparklines, and tables to get a better read on the data and see it in a new light.
• Use an inventory control template. You can find literally hundreds of Excel inventory management templatesboth online and in your Excel template list. Some (like list templates) focus on just one aspect of your inventory tracking, while others are more comprehensive. Still other templates are designed specifically for restaurants, research and development businesses, and other niche industries. Using a template is a great way to get your inventory tracking system set up with minimal effort, so take advantage of the options at your disposal.
• No seriously. Use a template. If youre reasonably sure youll upgrade to inventory management software at some point in the future, you may want to scope out your favorite platforms. Some inventory management software providers actually offer Excel templates that come preformatted for uploading to your inventory software of choice whenever youre ready to upgrade. Depending on the provider (and the quality of the template they provide), that could make your life a whole lot simpler down the road when youre ready to move up to a paid inventory management software.

Microsoft Excel offers a lot of possibilities for business owners who want to track their inventory on a budget. The learning curve for Excel is steep, but your inventory manager can be as easy or complex as you feel comfortable with. And if it comes down to it, there are tons of Excel templates for inventory management that include formulas, conditional formatting, pivot tables, and other tools for effective tracking.

Not sure if Excel is really the right choice for managing your inventory? Inventory management software may not be a bad way to go. Check out all the ways your small business stands to benefit from using software to track inventory to see if it sounds appealing to you.

Disclaimer

At Business.org, our research is meant to offer general product and service recommendations. We don't guarantee that our suggestions will work best for each individual or business, so consider your unique needs when choosing products and services.