HTTPS | 10 minute read
Excel may get a bad rap, but opening up a spreadsheet doesn’t have to be as painful as your taxes. In fact, using a spreadsheet as an inventory tracker can be extremely useful with the right approach.
We've made it easy for you to get started managing your inventory in Excel or Google Sheets with this free inventory spreadsheet.
Download now!
Microsoft Excel is one of the most versatile business tools available, and one that most of us already have, even if some of us avoid it like the plague. Utilizing it for inventory tracking and stock control is an easy way to cut costs, save time, and organize inventory, providing you’re willing to learn a few new tricks.
While not a perfect fit for growing SMEs or large businesses, or anyone with a few hundred different items in their inventory, Excel is excellent for startup businesses requiring inventory tracking, stock management, sales, data, ordering, and more than a dozen other functions. If you know how to utilize it correctly, you can generate a variety of highly valuable inventory formulas that will help you keep track of stock, sales, orders, and more.
Here are just five ways our free inventory tracking spreadsheet can help you manage your business:
The easiest way to use Excel as a stock management system is to organize your data based on sales quantity. This allows you to create a flexible inventory tracker that updates you when you need to order items. It also keeps your most sold items on the top for easier tracking, so you won’t be scrolling through pages of data to find what you need.
There are a number of ways to do this, but the easiest method is using the Sort function pre-built into Excel. You can also use the Rank function, which is slightly more sophisticated.
The Sort function only requires that all of your cells are the same size, that you select all of them, and that you can find the Sort function on the top of the page. Don’t forget, you’ll have to re-sort each time you update your numbers. You typically want to use Descending order if you want to rank sales from highest to lowest.
You can use the same SUM function with Sort to generate income reports by adding a value for each item. Then, when you update your sold items box, Excel automatically does the math for you to track how much inventory you've sold. You can also expand on this by adding sections for profit margins, taxes, and even smaller details like packing and shipping costs.
The Rank function allows you to take this process a step further by adding a specific rank (1, 2, 3, 4, and so on) to your items. It's also more dynamic because it updates automatically as you adjust numbers. This means that you won't have to manually rank your items each time you add a new sale, so you won’t forget to update your sorting and accidentally purchase all of the wrong items.
This formula is slightly more complicated, but is still not rocket science.
=Rank (Cell, Cell:Cell)
The first number is the cell you want to use, and the second set of numbers are the cells you want to compare your first cells with.
This gives you a very clear numerical ranking for most sold items that automatically updates without you having to sort. This makes it easier to track inventory effectively and see which items have sold most.
We've made it easy for you to get started managing your inventory in Excel or Google Sheets with this free inventory sheet.
Download now!
The main problem with the above function is that you have to manually enter all of your incoming and outgoing products. When you have a great deal of products, several people entering values, or a busy day, errors happen.
For that reason, a barcode scanner is a useful tool for tracking inventory with Excel. Most USB and wireless barcode scanners are easy to program to integrate directly into Excel, and you can do so on separate lines or updating a single line. Programming for barcode scanners varies slightly by brand.
The standard function for a scanner is to press 'Enter' or ‘Tab’ after adding information to a cell. This means that it will add each new scanned item to the next row or the next column. You can program it differently, usually by finding a scan code for changing the function, but we'll go over a relatively easy way to program using the original multiple line code.
How it works:
Each time you scan a code with the USB barcode scanner, it automatically inputs the items SKU into your Excel document. For simplicity’s sake, we're using an extremely basic SKU in this example.
Keep in mind that unless we assign additional values to the barcode scanner, it won't put in other items such as value, product description, etc. You can most likely program this fairly easily using your barcode scanner codes, but this will differ depending on your software.
However, using a barcode scanner is usually a good way to track incoming and outgoing inventory, rather than values, so you don’t necessarily need this information.
You can use the SUMIF function to calculate running totals for the same item on different lines. This uses a code that looks like this:
=SUMIF (C5:C7,ES25,D5,D5:D7)
This may look more complicated than your high school trigonometry test at first glance, but it is essentially =SUMIF (Cell Range (usually all the way down the page), SKU To Look For (in this case, ES25), Sales To Add Up).
For products remaining, you use the same SUM function used to calculate sales in the previous model.
The best practice is to set the document up with your original SKUs on the top, with your current totals, and use the most right bars for sold and remaining items. You could put them at the bottom, but you’ll end up having to scroll anyway.
You can also use a macro function to grab your data and move it to a separate page. The barcode scanner will enter items on the bottom of the sheet, but you will immediately see running totals on the top. You do, of course, have to program your barcode scanner to add scanned inventory into the column of your choice.
If you use two different barcode scanners, you can use the same computer for both incoming and outgoing inventory; you just have to program the scanner to add to the "current stock column", rather than the "sales column". This allows you to quickly and easily update your running totals, whether you’re getting shipments, or making sales. Just remember to label which scanner is which!
One of the main limitations of the traditional Microsoft Excel program is that there isn't really a feasible way for multiple people to add to the same document at once, unless you count backseat driving. While you can solve this with multiple Excel documents, today's technology presents another solution: the cloud.
Microsoft has their own cloud solution with Office 365, but you can also go for Google Drive or a number of other free online solutions (keep in mind that free isn’t always better). Office 365 allows for multiple users, real time updates, live backup, and, of course, you can have several users editing the same document at once.
A cloud inventory management system is especially useful if the inventory sheet is updated by multiple people or from multiple devices. The most valuable function is the automatic synchronization. Plus, if you download a copy of Microsoft's OneDrive to your computer, you can keep a backup copy directly on your computer to ensure that you can access it in case of an Internet outage.
Inventory tracker reports and analysis charts are relatively easy to generate, providing you are diligent about inventory tracking. You can choose to create reports on a daily, weekly, bi-weekly, or monthly basis, and create quarterly and yearly reports.
The easiest way to do this is by setting up your basic parameters for your inventory and then dragging and dropping the sheet to a new Excel workbook. This automatically copies all of your work to the new one, which gives you a clean inventory page for each day, week, month, or however long you choose to keep records.
You can also drag and drop all of your monthly records into one file to set up a yearly inventory, with individual sheets per month for easy organization.
Creating running inventory totals is a must if you want to track inventory effectively. For smaller businesses, or those with only a few large ticket items to keep track of, single inventory pages are the most convenient, because they allow you to allocate one page to each product.
For larger businesses, or for those with more products, you can create a running inventory for sales, how much you've made, and how much product you have.
The simplest example is deducting sales from your original stock figure e.g. SUM(D8-E8), replacing the D8 and E8 with specific cell locations that you want to subtract from each other. The best option here is to create an original stock line and use that to deduct your sales from. This gives you an instant total that updates whenever you change the number in either cell. Importantly, you can update the cell using either a manually entered number or using a scanner.
Don’t forget you can use your good old friend Autofill to populate cells using the same formula. Select the cells that contain the data that you want to fill into adjacent cells, then drag the fill handle across the cells that you want to fill. This will stop you having to repeat the formula in multiple boxes (for multiple products).
If you want to keep better track of a few large ticket items, you might want to use individual sheets per item, which uses the same functions, except you set up one sheet per item and set your scanner to input different SKUs on specific sheets.
We've made it easy for you to get started managing your inventory in Excel or Google Sheets with this free inventory sheet.
Download now!
Excel can be an extremely versatile inventory tracking system for startup businesses, especially if you know how to use it to your advantage. While some of the formulas may seem a bit complex if you aren't familiar with using them, it's relatively easy to learn, and allows you a lot more flexibility when managing your inventory in Excel.
Plus, they enable you to reduce the manual labor and mind-numbing data entry by making Excel and machines like barcode scanners do most of the tedious work for you.
To recap, utilizing Excel as an inventory tracker can help with:
While Excel can be a great starting point for inventory tracking, it isn’t a one-size-fits-all solution, especially for businesses looking to grow and scale up.
As the demands and complexity of your business grow, you will need to utilize a more sophisticated order management system to ensure you are well-equipped to handle an increase in stock and sales.
TradeGecko’s inventory tracking system allows businesses to manage inventory, sales orders, purchase orders, customers, and more from a single dashboard. It can also easily be integrated with accounting, shipping and fulfillment, and eCommerce systems to streamline all your business operations.
