More spreadsheet complexity results in bigger inventory problems

Previously in this series, we identified the hidden costs associated with using spreadsheets to plan your inventory, and we explored how important it is to have accurate, synchronised data and appropriate inventory planning inputs.

Today’s post deals with the ever-increasing complexity in businesses and supply chains, and how that affects planning spreadsheets.

The spreadsheet evolves

The first version of the spreadsheet often has simple objectives: collate disparate data from multiple sources so that a simple order can be “automatically” generated using a basic forecast. An individual in the business, usually the inventory planner or forecaster, is tasked with its creation.

While the spreadsheet might be fairly basic at first, it evolves over time to become something much more complex and time consuming. This evolution is the result of:

• Dealing with larger data sets, as the business and product range grows
• Time-phasing of incoming and outgoing purchases, internal transfers, and sales
• Requiring more granular computations that deal with different types of items based on defined rules, often with “special” rules for discrete sets of items
• Incorporating more sophisticated safety or buffer stock computations, to get closer to being able to recommend optimal purchase orders
• Building a more sophisticated forecast that is more responsive to changing trends and deals with seasonality, intermittent or sporadic demand and one-off buys.

Increasing complexity exponentially adds risk

Every change that is made to the spreadsheet only makes it more complex and more likely to have errors.

Every time the product range is extended by adding more rows to the spreadsheet, there is a risk that the formulae are not copied to these new rows and any totals may not include the new rows in the formula. If the spreadsheet has calculations that are dependent on other calculations (or other linked spreadsheets), the risk is magnified.

Attempting to cater for the time-phasing of ingoing and outgoing inventory adds huge complexity to the model, and each additional “special” rule added increases the dependency of calculations on each other.

Simple safety stock calculations are easily found and can be incorporated. However, dealing with the reasons why that safety stock is actually required is far more complex. Safety stock on its own is another series of posts, as it is a very complex subject. More on this to come in future posts.

Creating sophisticated forecasts is a similarly complex topic, with an entire suite of applications developed by vendors specifically for this purpose.

Dealing with more complex requirements

Extending the functionality of your planning spreadsheet to include more advanced functionality adds significantly more complexity. In some cases, it just can’t be done.

• Consider businesses who purchase into a central warehouse and then transfer to subsidiary warehouses or stores. In this case, the order at the central warehouse should be recommended based on a combination of the requirements from subsidiary locations and the central warehouse.
• In industries where product ranges change frequently, linking products in a supersession enables the “new” product to use the sales history of the “old” product(s) when producing a forecast.
• In manufacturing environments that have bills of materials, inventory planning needs to be done on both the demand for a component from the finished good and the direct sales of that component.
• Similarly, if a business has any kitting of products, this adds to the complexity required to suggest appropriate recommended orders.
• Highlighting sales to forecast exceptions early (and allowing manual overrides) stops expensive mistakes from being made but adds significant complexity.

So what are optimal order recommendations?

The single biggest requirement of any planning system - whether it’s a spreadsheet, app, or ERP - is for it to recommend optimal purchasing. When optimal orders are recommended, every single purchase order that you place is driving your inventory towards an optimal level, which is the lowest possible inventory value providing the highest possible service to your customers.

This ensures that you purchase enough of the items that are selling, while not purchasing those items that are not selling. A balanced inventory is the outcome, with higher margins and less cash invested.

All of the complexity highlighted above is before you have even begun to add optimal order recommendations to your spreadsheet. The calculations employed for safety stock are unlikely to reflect “best practices” and your spreadsheet is unlikely to cater for all scenarios.

The next blog post in this series will highlight the risks of having one individual who is responsible for creating, maintaining, populating, extending, and validating the spreadsheet before it can be used by anyone else.