Inventory Planning Blog

In 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.

If 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.

As the president or owner of a company, you are expected to lead, and in most cases, make all the decisions. It’s not easy sitting at the top with employees and customers depending on you to make the right call. But here you are faced with many challenges including tough local and global economies, a growing number of new items, inactive and obsolete stock and other daunting inventory issues.

Up to now, you have relied on spreadsheets and reports and maybe a basic or old business management system. You know mistakes have been made, costly ones that cost you both in cash and in losing customers. There is no IT department in-house to help you determine if a new ERP or Accounting solution is required or an inventory forecasting solution or maybe a more advanced inventory planning and optimization solutions.

So, you sit there and consternate on what to do. What move to make next. Time passes, months go by and still you are in the same position facing the same but growing issues and decisions. Does it really matter that you are maintaining the status quo? The business is doing Ok. It could be doing better but you are so busy there is no time to stop and think.

Well, this lull in decision making is costing you a lot more than you think. Every day that you delay in upgrading your business processes and software solutions costs you, time, money and customers. Today ERP systems like NetSuite, SAP Business One, Sage and a host of others really do make a difference. They help to provide the visibility and data required for quicker, more correct decisions. NetSuite, for example, is a hosted cloud-based solution which makes it affordable and fast to implement requiring no additional hardware and IT support in your company.

Augmenting those systems are the modern advanced inventory planning and optimization solutions. Many are seamlessly integrated into these systems making them a richer, more intelligent system and one that is easier to implement. A few of truly cloud-based built more the ground up taking advantage of modern tools and technologies.

Basically, advanced inventory planning and optimization solutions automate the entire inventory planning process. These systems automatically import data from the master system, generate a forecast, optimize the inventory and create inventory replenishment requirements. Optimization is very important because it balances inventory investment with customer service levels. It makes the hard decisions for you and presents the data in easy to understand formats.

You can save a lot of time and money, reduce overstocks and avoid buying more of the wrong items. It will impact your bottom line cash flow and help increase your competitive advantage. All really good things and all you need to do is unfreeze yourself and make the decision to get your business software up to date. Procrastination is easy telling yourself tomorrow is the day. Doing can be just as easy!

Ease of Use is the Key to Successfully Using Advanced Inventory Planning Solutions

But not for primary forecasting and replenishment planning

Reports and spreadsheets should be used to provide a basic level of information. However, many companies and planners use reports & spreadsheets to completely manage their inventory. To understand the use of these tools on effectiveness and efficiency in inventory planning let’s explore each one independently.

Reports
Many ERP and accounting systems have many standard reports that provide useful information. Usually, however, a company will create or build custom reports either in-house or through their 3rd party IT provider (Value Added Reseller – VAR). OK, so this may help because it provides a level of detail that a company or planner feels is necessary to better manage the inventory.

Assume your outside IT firm builds the report. It costs $5,000 to develop. Let’s also assume it is refreshed, updated, each day. Now what? This is static data and yes it is current but it just sits there and provides no efficiency for the planner. The planner must review and decide what the data suggests and what to do with the data.

But the real cost is much higher than the cost to develop the report. Every report, like spreadsheets must be maintained. Much like a car, the purchase price is just the beginning. Hours of a planner’s time are spent pouring over the results to make decisions. You need to count those hours as an associated cost. So, if a planner is paid say $45,000 per year, plus taxes and benefits the total annual cost is over $50,000. If 35% of a planner’s time is spent reviewing just the reports to manage the inventory, then the first year cost is $5,000 + $17,500 = $22,500. That is some expensive report. Are you getting your money’s worth? You still may not be done.

Most people who have reports will export the data from the report into a spreadsheet so they can better manipulate the data. Here is where the fun comes in.

Spreadsheets
The data from the report is now in the spreadsheet that you spent countless hours creating. The formulas in the cells calculate the numbers you need to evaluate your inventory and plan out the replenishments required.

The dangers inherent in spreadsheets are:
1.    Lack of Control
2.    Errors from:

Advanced inventory planning solutions are known as decision support software. This means they are smart systems that forecast and plan optimal inventory requirements. They are superior to commonly used types of software; standard inventory transaction software and spreadsheets. Many of these solutions are both strategic and tactical tools. They cover long range inventory budget planning down to daily replenishment and problem solving.
 
Comparison to Spreadsheets
 
Spreadsheets are manual, time consuming. They do not provide intelligent part mix management and part specific forecasts. In addition, spreadsheets are extremely difficult to update and maintain current data effectively, plus they normally cannot be interfaced directly with enterprise and customer relationship software. Conducting quality assurance on the calculations and models is also difficult and based on a study by a leading international consulting firms over 90% of spreadsheets they audited contained significant errors. These errors led to the loss of millions of dollars.
 
Comparison with Standard Inventory Management other or Control Systems
 
Almost every inventory solution is a transaction (accounting) type system. This means they keep track of the physical movement of items on and off the shelf. These are “dumb” systems, meaning they are static and cannot intelligently determine what to stock, project demand, or create a complete replenishment plan. These systems are based on 40 year old plus, methods using ABC as the only part classification technique. They do not have leading-edge Exception Management capabilities which provide "alerts" to the User for unusually occurrences.
 
What Inventory Management Software can do:
 
• keep basic part information like: part number, description, cost, dimensions, etc.
• track parts movement: received, on order, on shelf, removed from the shelf, etc.
• provide a basic reorder point and min/max settings
• record the different bin locations where parts are stored
• provide an order for new parts needed, using standard reorder point, min/max, and safety stock
 
What Inventory Management Software cannot do:
 
• provide a system generated forecast or offer multiple advanced forecasting methods
• manage repair parts and their repair cycle (reverse logistics)
• provide an optimal inventory mix to improve service level while reducing cost
• offer multiple stocking and reordering policies
• manage parts specifically required for PM (preventative maintenance) schedules
• help to reduce overstock and excess part inventory
• manage by exception and provide key user alerts.
 
What Advanced Inventory Planning Solutions CAN DO:
 
• forecast parts demand with multiple models appropriate for different demand patterns
• plan for unlimited part and location combinations (scalable)
• provide advanced calculations for calculating shelf stock and reorder points
• calculate the optimal mix based on dynamic demand changes
• provide time phased plans and order for both new and repair items
• manage by exception and provide alerts to the planner
• identify overstock and excess inventory and provide redeployment strategies
• plan parts for preventative maintenance schedules
• multi-echelon or theatre planning - can plan at every stocking level both real and virtual
      (from master warehouse to stock kept with a field or service engineer)
• plan part life cycle, substitutes and supercedes, and end-of-life parts
 
Combining Transaction Systems + Advanced Planning Solutions
 
Advanced planning solutions do not replace the existing transaction systems. Instead, they are designed to complement and improve existing software. The best of all worlds is to add an advanced planning solution to an existing transaction system. This provides a more complete, robust system that dynamically plans based on what is happening now and projected for the future. Download our free whitepaper to find out more!

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. And this is true geographically and for companies from the very small to the largest. So why the fascination with spreadsheets?

I 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:
Lack of Control
Errors from:

Complex Environment Creates Inventory Challenges

Inventory management has become more challenging in today’s complex and competitive business environment. Yet many companies are still maintaining inventories manually, and  performing complicated computations using spreadsheets and point solutions. Keeping track of reorder points can become an overwhelming task as businesses grow. And standard spreadsheets offer little help in driving down costs and improving revenues because they are time consuming and frequently contain multiple errors. Ray Panko, University of Hawaii compiled data from numerous studies that indicates up to 90% of spreadsheets contain significant errors.

Where is the money hiding?
It is common to find excess and obsolete stock representing thirty-sixty percent of inventory and to find that five-forty percent of the time customer demands cannot be met (based on Valogix’ experience). At worst, companies lose sales; at best, they must ship items at a premium in order to fulfill orders, further driving down the profit margins.

There is good news on the (inventory planning) horizon
Automated planning tools are designed to remove complexity and improve inventory mix. They dramatically reduce the amount of time required to properly plan inventory. By automatically forecasting, replenishing and optimizing, companies can manage inventory more efficiently and meet the demands of customers and suppliers at lower costs for a powerful competitive edge.

Reports and Spreadsheets– An Unproductive Combination for Inventory Planning

Reports and spreadsheets should be used to provide a basic level of information. However, many companies and planners use reports & spreadsheets to completely manage their inventory. To understand the use of these tools on effectiveness and efficiency in inventory planning let’s explore each one independently.

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.  And this is true geographically and for companies from the very small to the largest.  So, why the fascination with spreadsheets?