Real talk? It’s hard to track your inventory completely using a diary. Fortunately, there’s a better way. Also, it doesn’t involve you spending money per month on inventory management software. Microsoft Excel is an excellent tool for managing all kinds of data. With Excel, you can create everything from simple to complex spreadsheets that contain automatic calculations, conditional formatting, and even pivot tables that present your data in a better way. This is your guide on how to use excel for Inventory Management.
The fact is that Excel isn’t the easiest program to use if you’re not familiar with all its features and formulas. So we’ve broken down the basics for this guide to help you create a simple Excel document to track your business’s inventory.
The Products tab on your Excel document should have your complete inventory list. This sheet will help you keep a track of all your current and discontinued items. It will also contain vital information you need to assess product performance, determine which items to reorder, and store product details.
To create this sheet, create a table with the following data points as columns:
- Item number—List the serial numbers, SKU numbers, or product numbers.
- 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 unique name of each product.
- Description—Include short descriptions of your products (just in case the product name is not clear.
- List price—Note the selling price of each item.
- Stock location—List the warehouses, shelf numbers where your items are being stored.
- Inventory value—Share the total values each of your inventory items represent ( cost per item * number of items )
- 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.
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 number—Use the numbers you’re using to track your purchase orders with your vendor.
- Item number—Input 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 they’re on the same purchase order as another item).
- Category—List the product categories for each item you’ve ordered.
- Product name—Share the names of your products.
- Purchase date—Show the date you placed the order for each item.
- Stock received date—Note the dates when you received the ordered products.
- Vendor—Input the names of the vendors you ordered each item from.
- Quantity—Show the quantity you ordered of each item.
- Cost price—List the prices you paid for each item.
- Amount—Input the total values of the items ordered.
Again, you can choose to make this tab as simple or detailed as you like. We think the data points listed above should be sufficient for most businesses, but you’re free to add more columns if you want your inventory tracker to monitor other factors (Like BOM).
If you’re big on conditional formatting, you could set up a rule that automatically highlights any items in your worksheet that haven’t been delivered timely. Or you could create a dropdown list for your product categories so you don’t have to manually type them in every time. There’s no single right answer for how to set up your inventory management and control, it can be different for different businesses.
Last but not least, we think no inventory management solution is complete without a way to track your sales.
Once again, set up a table with the following columns:
- Sales order number—Write in the numbers you’re using to identify and track each customer order or purchase.
- Item number—As with your Product and Order tabs, list the SKUs, serial numbers, or product numbers you’re using to track your items. And again, be sure to list each product on a separate line
- Product name—Share the names of your products.
- Quantity—Note the number of items each client ordered.
- Sale date—Input the date when the sale was made.
- Ship deadline—Show the dates when each item must be shipped to get to your customers on time.
- Ship date—List the dates when each item was actually shipped.
- Tracking number—If you have it, share the tracking numbers for each shipped item (so you can track it en route).
- Client—List the names or customer loyalty numbers associated with the clients on each order.
- Retail price—Note how much your customers paid for each item they ordered.
- Amount—Show the total dollar amounts you received from each customer for the items they ordered.
Other tips and tricks
Excel is a good tool, but it certainly isn’t infallible. Here are our top tips and tricks to make Excel work for your business:
- Keep things simple. While Excel is certainly can be used to track every minute detail, you don’t want to spend all day every day doing data entry. The more things you have to track, the more work it’ll be for you, and the harder it’ll be to keep tabs on your inventory.
- Update, update, always update. Excel can’t track incoming shipments and outgoing sales automatically, so it’s on you to keep your spreadsheet up to date. Try to update the sheet immediately after receiving new shipments or processing a customer order.
- Put it in the cloud. Storing your Excel sheet in the cloud allows access to the document from multiple devices. That way, everyone in your team can view and update the inventory tracker. Just make sure you have the co-authoring feature turned on, else you’ll have two versions of your spreadsheet whenever two people will make changes at the same time
- 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 and review your inventory management document often to screen for errors.
- 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 the accuracy of your data entry.
Microsoft Excel offers a lot of possibilities to business owners who want to track their inventory on a budget. The learning curve for Excel is steep, but your inventory sheet 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.
Although only using Excel for inventory management is not the best use of the tool, certain Excel features work very well in conjunction with an appropriate inventory and accounting ERP software package. Excel Pivot Tables, for example, are a powerful tool useful for analyzing and reporting on data from multiple data sources. Having a back-end system that can live-link to Excel through different data sources makes it easy to manipulate live data for reporting, and can provide a quick snapshot of business health.
As a small company, using Excel as an inventory management tool can hamper company growth and limit the amount of timely, accurate data available. Instead, it is best to invest in a proper digital platform with full inventory management capabilities as well as accounting and customer relationship management. A proper inventory management system is the best way to ensure accurate inventory information.
Not sure if Excel is really the right choice for managing your inventory? Inventory management software may not be a bad way to go.