Excel gets a lot of flak and most of us probably have our own share of Excel jokes we bring out on occasion, but opening up a spreadsheet doesn’t have to be as painful as your taxes. If you’re using it properly, don’t have 250 columns of data, and aren’t trying to manage everything in one spreadsheet, it’s actually not that bad!
Download your Free Inventory Spreadsheet now!
Learn how to manage your inventory, save time and kick-start your business in Excel with this free inventory spreadsheet.
Microsoft Excel is one of the most versatile business tools available, and one that most of us already have, even if some of us typically avoid it like the plague. Therefore, utilizing it for inventory management 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 large businesses, or anyone with a few thousand different items in their inventory, Excel is excellent for 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. The following include five of the best practices for managing inventory in Excel.
1. Organize Based on Sales Quantity
The easiest way to use Excel for tracking your stock is to organize your data based on sales quantity. This allows you to create a flexible inventory sheet that updates you when you need to order items because you can see sales right at the top. 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, starting with manual organization on a daily or weekly basis, which can be a pain (no really, please don’t even think about this, your brain will hurt). The easiest way that won’t result in carpal tunnel from hours of manual data entry is the sort function built into Excel. However, 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. (This means no merging two cells in one column and three cells in another, design isn't that important). 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. (As you may have guessed, the sales numbers represented in the image are purely fictional and do not represent hard data of any kind)
You can use the same SUM function with Sort to generate income reports by adding a value for the item. Then, when you update your sold items box, Excel automatically does the math for you (yes, it's that easy) to tell you how much inventory you've sold. You can further expand on this by adding sections for profit margins, taxes, and even smaller details like packing and shipping costs, but as you use the same SUM function here rather than advanced quantum mechanics, we won't go into detail on the process. Excel uses * as the multiplication symbol. Your sum should look like this: =Cell*Cell
The Rank function allows you to take this process a step further by adding a specific rank (I.E. 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 still not rocket science (if it is, we want a raise). The formula goes like this: the first number is the cell you want to use, and the second numbers are the cells you want to compare your first cells with. So =Rank (Cell, Cell:Cell). 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 see, at a glance, which items have sold the most.
2. Using Excel to Manage Outgoing Orders with a USB Barcode Scanner
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, things happen. Most of us don’t have robots to enter our Excel data, so human error is inevitable. For that reason, a barcode scanner is the next best thing (to robots). 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 simplicities sake, we're using an extremely basic SKU, which you would not see in real life because the capacity for error is quite high when using a simple number. You can use anything you like, but you should avoid letter and number combinations that Excel will read as a string (i.e. as text instead of a number), unless you want to spend hours fixing code errors with “directs to empty cell” messages (fun, we know). If you have your scanner set to input the number of items scanned + the SKU number, you can easily generate running totals of ingoing and outgoing items, and all without selling your firstborn son for a magic solution.
In order to do this properly, we have to make two assumptions. The first is 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 =SUMIF (C5:C7,ES25,D5,D5:D7). This may sound 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 product remaining, you use the same SUM function used to calculate sales in the previous model. Once you get the hang of it, it’s fairly simple.
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 if you want. 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; otherwise your inventory will be a mess anyway.
3. Use Office 365 Excel in the Cloud
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, but 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. An online version is especially useful if you have multiple people, multiple locations, or several computers for updating the inventory sheet. The most valuable function is, of course, the automatic updates. 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.
4. Generating Inventory Reports
Inventory reports and analysis charts are relatively easy to generate, providing you have your inventory organized properly. 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. You can also generate charts using your data, but it’s up to you to decipher them at a later date.
5. Create Running Inventory Totals
Creating running inventory totals is a must if you want to easily keep track of your remaining stock. There are two ways to do it. 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, unless going through over 1,000 sheets to see individual product reports sounds like fun.
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 example 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.
Excel can be an extremely versatile tool for businesses, especially if you know how to utilize it to its best effect. 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 allow 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.
Need something more dynamic than the Excel inventory template?
Check out our inventory management software - a one stop for all your forecasting needs!