I recently visited a company that uses Excel for inventory management. This is pretty normal for many small businesses that don't have a purpose-built system to manage different aspects of their operations such as inventory and sales. But this visit in particular reminded me of how dangerous it can be to use Excel for certain types of business activity. To be clear: Excel is an outstanding business tool and I use it all the time. But like any tool, it works best when used for appropriate purposes.
At this company, their Excel spreadsheets are manually updated when shipments arrive, when they get around to it. And they're updated again when products are shipped out, some time after the fact. They use these spreadsheets for information about inventory: what's in stock, when to replenish and how much to order. They also use them for costing inventory, including landed costs like duty, brokerage and freight. So these are pretty important business tools, right? Now read this question and answer exchange with the business owner:
Q: How closely do the on hand quantities in your inventory spreadsheets agree with physical inventory?
A: Well, whenever we do a count there are many differences. In fact most products are incorrect. And we often cannot find products in the warehouse that the spreadsheets say are in stock.
Q: In that case, how can you place reliance on them?
A: We use them more as an indicator than for absolute availability - we usually do a physical check when we need to know for sure...
Q: How much time is spent updating the spreadsheet (daily)?
A: Not too much - perhaps an hour or so at the end of the day, on busy days a little more.
By my calculation, this company spends 25 - 30 hours per month updating an "inventory management system" that is of little or no use and cannot be relied upon. It's clearly a waste of time and effort, but is this also a limiting factor in terms of the company's growth potential? I believe that it is, and that many small companies limit their growth potential by using inappropriate business tools. It's the old "penny wise, pound foolish" trap.
If your inventory is managed as part of the overall business system ("ERP") software, and implemented properly, inventory is updated naturally as the underlying business transactions are processed. You're invoicing your customers and paying your suppliers for products anyway, so why not have the inventory updated automatically when you do so?
