Problems with your inventory planning spreadsheet start with your data

In the first blog in this series on the hidden costs associated with using spreadsheets to plan your inventory, we covered how Excel has become the “planning tool of choice” for many businesses to handle all or part of their inventory forecasting, planning, and purchasing.

Today’s post will deal with some of the challenges around the data required to drive the planning spreadsheet, including:

• The accuracy of the data
• The synchronization of the data
• The inventory planning inputs

Data accuracy

The first critical challenge when using spreadsheets for inventory planning is getting accurate data into the spreadsheet. Having inaccurate stock balances, for example, makes it nearly impossible to plan effectively, and any resulting order recommendations would be a guess at best.

The data that is required to drive a planning spreadsheet includes (but is not limited to):

• Accurate stock balances, allowing for easy calculation of the net available stock
• Up-to-date sales data (including sales within the current month), enabling a forecast to be generated, reviewed, and overridden
• In-progress transactions such as outstanding purchase orders, customer orders and internal transfers, preferably time-phased, so that order recommendations can be actioned when actually required
• Supplier ordering constraints, such as minimum order quantity or minimum order value constraints, so that these can be factored into order recommendations

Data synchronization

The second major challenge with inventory spreadsheets is the need for consistently synchronized data. When extracting, compiling, and consolidating information to feed into the spreadsheet (possibly even from multiple sources), it is vital that the data is relevant and synchronized. All of the “transactional” data must be extracted at exactly the same time.

For example, extracting stock balances at 9 a.m. this morning and outstanding purchase orders at 9:30 a.m. or 8:30 a.m. might have serious implications for both stock shortages and excess inventory.

• If the stock figure includes a purchase order that has been receipted but the purchase order data still shows that PO as outstanding, then we have double counted the quantity on the PO. In this case we would believe we have enough in stock and on order, so there would be no recommendation to order when in reality we should be ordering.
• If the stock figure did not include the receipt of the PO and the PO was no longer showing as outstanding in the purchase order data, then we would likely order too much as we have lost visibility of the quantity on the PO.

Appropriate inventory planning inputs

Another stumbling block for inventory spreadsheet users is determining the correct planning inputs. The key inventory planning inputs that should ideally be established at an item or item by location level, include:

• Lead time, the duration of time from order placement until the item is in stock and available for sale
• Classification, including whether the item is stocked, non-stocked, or obsolete, as well as a pareto classification showing the importance of the item
• Safety or buffer stock, a level of stock that is dynamically computed and required to compensate for inaccurate forecasts and late delivery from suppliers
• Order cycle, how frequently you plan to order this item

Do the math on your spreadsheet:

Inaccurate data + unsynchronised data + inadequate inventory planning inputs = sub-optimal order recommendations

This process has serious implications for your business because it often results in expensive mistakes which only become apparent after it's too late. You end up with stock shortages or excess inventory (or both!) and are continuously in firefighting mode.

Resolving stock outs often requires placing emergency airfreight orders at great cost to the business. The excess inventory ties up capital you could have used to resolve the stock shortages and has numerous ongoing costs which all add up to lower margins and less profit.

In the next blog in this series, we will examine the complexity required to generate optimal order recommendations from your inventory planning spreadsheet.