• Using Excel for Inventory Management

We speak with many prospects who are using Microsoft Excel spreadsheets as a way to manage their inventory and other areas of their business, most often because they lack more sophisticated software systems.  This is not uncommon and can work for very small businesses just starting out – Excel is an excellent business tool and can be used in many situations.  However, like any tool, it works best when used appropriately and can negatively impact a business if relied on too much in situations where other tools would work better.

Although we have encountered businesses using Excel spreadsheets to essentially manage every aspect of their business, some of the more common uses include:

  • Manually updating spreadsheets when shipments arrive and when products are shipped out
  • Inventory management purposes such as determining what items are in stock, when to replenish and how much to order
  • Inventory costing purposes including landed costs like duty, brokerage and freight
  • Serial number tracking
  • Service management, including keeping track of labour, work-in-progress etc.
  • Reporting, where users must manually key in numbers from their other systems or from other spreadsheets

Using spreadsheets for the purposes above may seem like a good idea in theory, but in practice, any business owner will know that there are many inherent issues with relying solely on spreadsheets as a business management tool.  Using spreadsheets can result in information not being updated in a timely manner, skewed reporting and inventory information, human error encountered when manually counting stock items and updating information, and the potential for lost data when sharing spreadsheets. Relying on spreadsheets for tracking information often leads to more work double checking numbers and manually updating information.  One small mistake in a formula can drastically affect the rest of the numbers across multiple worksheets.  In addition, many companies have a single employee managing specific spreadsheets, which can lead to inefficiencies and confusion trying to decipher their spreadsheet logic, specially if they leave the business.

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 growing 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 ERP system with full inventory management capabilities as well as accounting and customer relationship management.  Excel should then be used as a complimentary tool when importing or exporting data as well as for managing certain reports.  A proper inventory management system is the best way to ensure accurate inventory information.

Inventory Management Techniques