Inventory Planning Blog

Inherent Dangers to Proper Inventory Planning

Posted on Mon, Aug 04,2014@10:35 AM

spreadsheets, excel, inventory planning, inventory optimizationIn today’s dynamic business climate, companies are looking for simple ways to reduce waste, improve profitability, and better manage their inventory. Going through many business cycles or phases faster than ever, new technology initiatives must be affordable, easy to implement, and have a high, measurable rate of return.

Companies approach their inventory planning in many different ways - from seat of the pants guessing, to team collaboration, to spreadsheets that may invoke more team collaboration and manual changes, to forecasting software and some to advanced planning solutions.

Business software solutions like accounting packages or ERP systems come with some level of inventory management or inventory control functionality but rarely do they include forecasting or demand planning. They rely on the user manually setting min and max levels and safety stock to control the inventory. There are stand-alone forecasting packages that offer dozens of statistical models that allow the user to select the one or ones they want to use, and some even provide automated forecasting in which the solution uses a best-fit approach.

Spreadsheets and Reports – The Only Alternative?

The vast majority of companies that own an inventory invariably use spreadsheets to control, manage, and report. The types of companies vary from manufacturing, to wholesale distribution, to retail, to aftermarket services, to maintenance and repair operations. In addition, this is true geographically and for companies from the very small to the largest. So why the fascination with spreadsheets?

In most cases, companies already own the software as part of a suite or as already installed on the computer they purchased. They are relatively low cost and have an abundance of features that make them attractive. It is also a case of “you don’t know what you don’t know”. Meaning, people are unaware of the availability of easy to use affordable, automated inventory planning software.

The dangers inherent in spreadsheets are:

1.  Lack of Control
2.  Errors from:
  • Formula and data entry mistakes and typos
  • Cell formatting and range definition mistakes (or errors)
  • Data exporting problems
  • Cell referencing problems
3.  Complicated Calculations using incorrect assumptions
4.  Potential for fraud

Lack of adequate training can result in poor to mediocre spreadsheet results, such as improper referencing, linking to other spreadsheets, or using inaccurate formulas to master complex calculations.

spreadsheet errors, inventory planning, inventory optimization, excel, spreadsheetsIf the policies and procedures to mitigate spreadsheet risks are inadequate, errors will become more common and lack of consistency will show up in internal control audit reports. Therefore, the style, content, and accountability for spreadsheets should be documented in the organization's policies and procedures or in the spreadsheet used.

People are creatures of habit, which is one reason why spreadsheets are reused from year to year. Unfortunately, after cutting and pasting information, the spreadsheet might not work the way it did before — formulas can be damaged, links can be broken, or cells can be overwritten.

Many disruptions to the daily workday are common reasons personnel make data entry errors such as skipped entries or transposed numbers. A 2004 PricewaterhouseCoopers study shows that up to 91 percent of sophisticated spreadsheets contain errors. Unfortunately, if auditors know there are spreadsheet errors, so do fraudsters. For example, inadequate spreadsheet controls may lead to errors, misstatements, and possibly fraud.

Failure to back up data is a common and sometimes fatal error that may result in the loss of hours of data entry for computer users, which applies equally to all software tools including spreadsheets. Hardware and software breakdowns do occur from time to time, and backing up regularly and frequently is the best prevention for the spreadsheet user.

Keeping track of reorder points and safety stock for hundreds or thousands of items is becoming an overwhelming task. The time involved to update and maintain these spreadsheets can take hundreds of hours every week. Moreover, demand is constantly changing which can make the data stale and behind the curve for the most recent trends. This can lead to under-stocking causing lost sales and overstock creating excess inventory. In cases, a loss and a waste of valuable cash.

Ray Panko, University of Hawaii compiled data from numerous studies that indicates up to 90% of spreadsheets contain significant errors. Read this report.

Download our Whitepaper

Tags: Inventory Planning, inventory optimization, advanced inventory planning solutions, Spreadsheets