Inventory management is the operational backbone for any business with a product to sell. If you are just starting out with a small number of products without many variants, Microsoft Excel is a good tool for beginners to manage inventory.
However, efficient utilization of Excel for inventory management requires time and setting up your initial template accurately is crucial. In this post, we give you tips on building and using an Excel spreadsheet for basic inventory management.
Using Excel as a low-cost inventory management solution
Excel is a program that most of us are already familiar with, whether for accounting, or for forms, so it is a good option for other applications like inventory management. With integrated tools, features, and formulas to make spreadsheets more dynamic and interactive, Excel is also fully capable of handling basic inventory management. While not ideal for a large inventory, Excel is cost effective, or if you use it in OneDrive, even free.
Like anything that’s free or cheap, Excel isn’t perfect, but it has a lot of features that you can use. While it is extremely easy to make mistakes in Excel, and avoiding them will require time-consuming auditing, proper organization, and strict management, it is fully capable of handling small business inventory.
Setting up your Excel inventory management template
A basic template for your Excel inventory should include the product number, product name, item description, item price, item cost or value, item stock, quantity to reorder, and if you want to update sales quantity or value, quantity sold. Time to reorder, whether or not the product has been discontinued, total inventory value, and other functions are also very useful, but the more columns you have, the more difficult it is to keep up with everything.
If you would like to gain a deeper understanding of crucial inventory management concepts and how you can apply them to your business, download our free eBook Inventory Management: Everything you need to know.
You can then use Excel formulae to automatically calculate information for total sales, total profit, and organize your inventory based on products that sell the most using a few simple formulas. You can modify Microsoft's basic free template (available when you open Excel) to your needs, purchase a template, or create your own.
Formulae can increase the value of your Excel spreadsheet, while reducing the headache and stress involved with calculating numbers. While they may seem a little complex at first, don’t worry, most of them are easy to get the hang of.
You can use the SUM function to do math for any figure that you put into two cells. For example, you can create a running sales quantity using the SUM function, to automatically update how much revenue you've earned by doing math based on calculations you put in per line. This removes the need to go over each line with a calculator every night.
A SUM formula is written as =Cell*Cell. This can also work out to =[@[Name of Column]]*[@[Name of Column]] to simply multiply any two cells in a row. This is very useful for creating automatic value totals for the products in your inventory, but if you have 300 lines, you will have to add it to each one.
For example, if your products are worth $27 each, the SUM function would automatically update the 'inventory value' section to $1,755 when you update the in-stock number to 65.
Sort allows you to order your inventory by number size, or by color, so that you can see products based on sales, profitability, or remaining stock to quickly and easily go through products. Sort will be wrong if any of your data is wrong. If you use a subtraction formula (=B1-B2) when you input how many items you sold, it will automatically deduct that number from your current stock number, which you can then use to sort based on stock, so that you can see which items you have the least of. This also conveniently circumvents issues surrounding not ordering products because you don’t know which you have to order.
Rank allows you to rank items based on sales quantity, based on inventory quantity, or based on the number of products you have received. Because it makes the most sense to rank high sale items on the top, or rank items that you have to restock on the top, you can use it to organize your inventory based on either specification. Rank differs from sort because sort organizes your data in order at the push of a button, while rank does so automatically. Interestingly, it doesn’t automatically adjust the order, so your #1 could still be in the middle of the page. Your rank formula should be written like this: =Rank (Cell, Cell:Cell), where the cells mentioned include all of the cells you want to rank.
Now that you've become familiar with the formulae, here are some tips on how to manage your Excel spreadsheet to ensure you are tracking inventory movement accurately:
1. Avoiding pitfalls and mistakes with Excel inventory
Many professionals avoid using Excel for inventory management for the simple reason that humans are error prone, and Excel doesn't really have any way of combating this fact. Finding errors on sheets of numbers and hard data is also difficult, unless you know exactly what you're looking for, which is unlikely unless you added the mistake on purpose. The only way to avoid this is to create a best practice of self auditing, and daily review, to ensure that mistakes are not made.
2. Update immediately
Make sure that anyone handling orders updates the Excel document immediately to prevent issues. Excel is not a real time inventory management solution, so you will never be able to see the real inventory value at that moment, but you can make sure that it is updated as often as possible to circumvent issues like over-selling a product that you cannot backorder.
3. Use the cloud
Traditional offline Excel documents are severely limited because only one person can use the sheet at once. Using Excel in the Cloud (OneDrive) or Google's alternative ensures that you can invite multiple people to the sheet and edit it at once to improve self-auditing, input data from multiple sources, and speed up processes. It also comes with the added benefit of automatic backups and syncing across all of your devices so that you can edit on an iPad, or on your computer at home.
4. Take the time to consolidate data
Taking the time to consolidate your data monthly allows you to create charts that outline how much you sold in each period. You should make these per month, and per quarter, paying special attention to holidays and occasions.
One of the biggest issues you will have with Excel is not being able to easily analyze your historical data, but taking the time to add your entire month's data into an easy to read pivot table will fix this. It is time consuming, but it does allow you to track your sales based on period, and during different seasons, for better inventory optimization in the future.
- Click 'Insert', Choose 'Tables', select 'PivotTable'. (click the arrow below PivotTable to create a chart)
- Select the table range (I.E. 1A-21G) in the Create Pivot Table dialog box that pops up
- Choose the location of the new pivot table (new worksheet or existing one)
- Click 'OK'
- Customize your Pivot Table as needed.
5. Go over your data
Excel can be a valuable tool for tracking inventory, but it is up to you to make sure that you are tracking and calculating everything necessary. For example, if you're tracking time to re-order have you included lead times (time from manufacturer to warehouse or 3PL) in your calculations? Are you calculating profits based on sale, or based on your actual per-item profit? Are you using your inventory to calculate sales tax immediately? Is your total inventory value based on resale or purchase value? Going over the figures you use in your calculations to ensure that they are correct can save you expensive and time consuming mistakes in the future.
6. Audit and review
Excel relies on people to input data unless you have a barcode scanner connected to your system, and even then, not everything is automated. This will lead to human error, which can throw off your entire supply and demand when you re-order too much or not enough, or under or overestimate profits.
A policy of keeping track of everything on paper as well as in your Excel document allows you to go over your inventory at the end of each day to correct any mistakes. While time consuming, Excel is error prone, simply because nothing is automated, so strict audits and reviews are necessary to ensure the accuracy of your inventory tracking.
7. Know when to upgrade
Excel simply does not work with large inventory volume because hundreds of columns are too complex and too difficult to track and update, not to mention, a headache. At some point, you will have to upgrade to a dedicated inventory management system, and you should know when to do so. Solutions like TradeGecko start at $99 per month, and are well worth the money if you have a large amount of products, frequently move products from location to location (such as from a supplier to 3PL warehouse, to your physical store or to customers) and handle large volume and multiple location tracking in real time. You can consider the number of hours, the headache and stress, and mistakes you save when deciding whether or not to upgrade to a dedicated system.
Inventory management is an important part of business, and the more sales you are making, the more your need of management increases. Using Excel will allow you to cut costs at first, but eventually, a dedicated inventory management system will be necessary.
Inventory management helps you with everything from optimizing your inventory for better sales and return on investment, to growing your business and expanding to additional sales outlets as your consumer base grows, so your inventory management solution should grow with you. However, Excel is definitely a good option to get you started, especially if you need a low cost or completely free option.
Find more tips on using Excel for inventory management.