Should you use MS Excel for Inventory Analysis?

Inventory and its causes…

In any wholesale distribution business, inventory is the byproduct of fluctuations in supply and demand and the mismatch between the two. A way to think of inventory is as the shock absorber that allows us to have a smoother ride on a bumpy road. Each change in demand and/or supply represents a bump, and some of that ends in increasing the inventory and some of it ends in decreasing the inventory.   If the inventory did not exist, one would feel every bump in the road by way of customer complaints.

In any business, the inventory in any period can be simply defined as inventory in the previous period plus any supplies coming in minus any demand going out. This can be represented as a simple equation:

    • Ending Inventory in current period = Ending Inventory from previous period (which is also the Starting Inventory in the current period) + Incoming Supplies in this period – Outgoing Demand in this period, where period can be any time bucket (for example:  day/ week/ month etc.).
    •          Examples of incoming supplies could include: purchases, transfer in from other company locations, production, returns, etc.
    •          Examples of outgoing demand could include: shipments to customers, transfer out to other customer locations, disposal of obsolete products, etc.

Most businesses have two types of inventories: planned and unplanned. Planned Inventories include:

  • Safety stock: to cover for variability in supply and demand.
  • Cycle stock: to cover for periods between reordering.
  • Anticipation stock: pre-build for planned outage or peak season spikes.
  • Pipeline stock: stock that could be in transit or work in process.
Unplanned Inventories usually represent excess or leftover stock from what was planned. The causes for this can include:Inventory Management Software
  • Overly high sales plan/forecast
  • Product returns
  • Wrong location
  • Wrong package
  • Dead or discontinued products
  • Off-spec material which can be converted to saleable material
  • Off-spec material which should be disposed of
  • Experimental or new product
  • Buying too much to get a volume discount
As unbelievable as it may sound, there was a time when large inventories were a sign of wealth and businessmen used to boast proudly of their extensive stocks (Jones, M.). Those times are indeed long gone. Any businessman making such a boast would very likely invite some ridicule. Today, most businesses want to carry as little inventory as possible without affecting the customer service levels. This change in the attitude has really picked up speed in the information age. The idea in the 21st century is to replace inventory with information so they can provide the same high level of service without incurring the extra costs of carrying inventory.

Too high?  Too Low?  Just Right?

The desire to have low inventory does not always result in perfect results. For example, when practitioners on LinkedIn were asked about the percentage of inventory represented by unplanned inventory in their suppychains, the response ranged from 15% to 70%. (These group discussions can be viewed here and here on LinkedIn). A variety of factors would influence such results. These could include industry sectors, skills of the business in inventory planning, ability or desire to get rid of excess inventories, etc. It could also be because of a difference in opinion in what constitutes excess inventory. After all, what seems excess to some might look like a bag of gold waiting for the right opportunity to others.

To illustrate the last point above, look at the results of a survey conducted by Cutwater Solutions.  (You can participate in the anonymous survey here.)  As you can see in Figure 1, while most confirm there is an inventory problem, 60% state it is a mixed bag of inventory being too high or too low. And about 25% said that it also depends on who you ask.

 

Cutwater Inventory Management Survey Results

Figure 1: Percentage response to the question: What is the typical inventory problem at your firm?

The typical response…

When a wholesale distribution company finds itself in a position where the inventory is not in the right place, the typical response is to create a quick spreadsheet to calculate which inventories are in excess, allowing the business to do something about the problem quickly. For example, a report can easily be created which shows the items that have not sold in the last 12 months, but still have inventory. Depending on the business, these can be included in a list for quick disposal via sales or other promotions. Spreadsheets have the obvious advantage of being ubiquitous and a high degree of user acceptance. This approach, while effective in a piecemeal way, has the following potential disadvantages:

  • This type of analysis is often done on an ad hoc basis.  The need is to set this up as an ongoing exercise to be repeated at regular intervals (to some extent, same limitations apply to a one-time analysis conducted by an outside consultant as well).
  • The analysis is limited to the knowledge and resourcefulness of internal resources;  in other words, it does not take into account the best practices available in the industry.
  • There is a tendency to run these analyses in a uniform way across the material-location network, meaning there is no emphasis on differentiating across products based on characteristics. For example, should very popular products be treated the same way as the not so popular ones?  Should one take into account the predictability of the product sales?

Other factors that make spreadsheets unproductive are the complexities created by amount of data, multiple users and need for connectivity to ERP systems.

What’s a practitioner to do?

If you decide your firm has outgrown the Excel-based inventory analysis, what is the next step? One option is to look at inventory analysis software based in a database management system (DBMS). Examples of DBMS include Microsoft SQL server, Oracle and DB2. Ideally, a practitioner chooses an inventory analysis system that runs on the same DBMS as the company’s ERP system (or integrates well with it). At the same time, these solutions should still be easy to use and integrate with the users’ desktop. Practitioners should also look for successes of the software with companies of similar size which are using same or similar ERPs. Functionality wise, these systems should be able to do the analysis using a nuanced approach, wherein they do not try and apply the same rule to all material-location combinations. Instead, the system should do a ‘structural’ analysis to identify areas of improvement with maximum impact while minimizing any downside.

These applications gather data automatically from an organization’s ERP not only at a preset frequency, but also on demand, and they hold it on a centralized server where it may be accessed by many users. More importantly, they offer true business functionality, including specialized support for a wide range of inventory analysis such as Pareto analysis, customer and order patterns, dead and excess inventory, too much or too little inventory, candidates for rebalancing, etc. This enables a company to truly understand the difference in the ‘DNA’ of different products and/or product families. They also provide multiple security levels, allowing access based on roles.

Warehouse Inventory Management

DBMS-based applications are not included with the purchase of a laptop; at the same time, most distributors have a few SQL servers running as part of their enterprise systems. These applications can provide business value far exceeding their cost. Compared with Excel, for example, solutions based on database management systems offer significant advantages:

  • Visibility: Compared to Excel, a DBMS-based system does a better job of sharing data across users. Data visibility is greatly improved along the supply chain and in the various groups such as sales, operations, finance, commercial, etc.
  • Related Data: Storing related data together in a single table or spreadsheet is unwieldy and invites errors. Databases easily link tables of related data, such as customers and their orders.
  • Safety: In Excel, any unsaved data may be lost if a system crashes. Databases write data to the hard drive immediately and are usually backed up at a corporate level.
  • Volume & Speed: High volumes of data bog down Excel; DBMS applications routinely manage high volumes of data.
  • Future Growth: A DBMS system is a foundation to further extend the supply chain processes because it enables other advanced tools like planning and scheduling.
By leveraging the functionality of a DBMS-based APS application, a company’s decision makers can immediately detect data and mapping errors. In addition, they can see how data relates across attributes.

Finally, they can detect patterns within the data itself, to make better-informed decisions about overall processes and individual projects.Warehouse Inventory Management Deciding to switch from a homegrown Excel application to a vendor-provided and DBMS-based tool is not easy. Depending on the size of the company, many people and departments (including planners and other users, IT personnel and management) need to be convinced. Next, the timing also needs to be right. A manager who thinks the business has outgrown Excel-based tools might still need to wait for the right political and economic climate. For example, the disruptions caused by a natural or a market event might make the case for a change, or the arrival of a senior executive with experience in these types of applications might also tip the scale. The practitioner should keep the end-goal (of right sized inventory) in mind as they navigate this obstacle course.