We work with many customers in many industries to connect our advanced analytical, forecasting, and inventory planning software to their ERP systems. Despite the variety of situations we encounter, some data-related problems tend to crop up over and over. This blog lists ten tips that can help you avoid these common problems.

Once a customer is ready to implement software for demand planning and/or inventory optimization, they need to connect the analytics software to their corporate data stream. In our case, we mainline transaction data directly into the analytical software. This provides information on item demand and supplier lead times, among other things. We extract the rest of the data from the ERP system itself, which provides metadata such as each item’s location, unit cost, and product group.

These tips are important because it is not uncommon for implementation projects to start with great enthusiasm but then quickly bog down because of problems with the data that fuel for analytics. These delays can reduce team enthusiasm, embarrass project leaders, and delay (and thereby reduce) the ROI payoff that ultimately justified the implementation project in the first place.

The importance of connecting the analytics software to the corporate data stream

Here is the list of tips, grouped by the general themes of handling files safely, insuring data integrity, and dealing with exceptions.

Handling Files Safely

1. Have a test environment to use as a “sandbox.” Copy your current data to a test environment where you can safely experiment with the software without risking current operations. Besides helping users learn the ins-and-outs of the new software, having the latest data in the software allows end users to discover any problems with the data.

2. Protect your data extraction rules. If you aren’t utilizing a pre-built connector to your ERP system then you to need to ensure that you can create savable extract rules to move data from your ERP to a file.  Column orders, data types, date formats, etc. should not vary each time the same extract is re-executed.  Otherwise the project gets bogged down in manual errors or confusion in re-extracts after fixes to the data or when new data roll in. All data extraction rules should be saved and available to IT – we’ve encountered situations where files extracted were done so in ad hoc manner resulting in a slightly different formats with each new extract.  We’ve also seen customers work hard to develop a complex and accurate data extraction routine only to find all their work was lost when it was not properly archived.  Both situations led to confusion and project delays.

3. Don’t use Excel native file formats for data transfers. If your planning solution doesn’t have a direct integration to your ERP system, then export ERP data to a flat file format, such as comma delimited (.csv) or tab delimited text files.  Don’t use MS Excel formats such as .xls or .xlsx as the export file type because Excel auto-reformats field values in unexpected ways. Many users assume they need to use .xlsx files if they want to manually review them, not realizing that .csv or .txt files can be opened just as easily and don’t carry the risk of auto-reformats.

Insuring Data Integrity

Data Problems and solutions in Software Implementation. Here is the list of tips, grouped by the general themes of handling files safely, insuring data integrity, and dealing with exceptions.

4. Confirm the accuracy of your catalog data. Export your catalog data (i.e., list of products, list of customers, list of suppliers) and all their relevant attributes.  Check for wrong or suspicious values in the attributes (especially item lead times and costs).  Problematic values include blanks, zeros when you don’t expect zero as a data value, and text strings when you expect numeric values (or vice versa).  It can help to open each extract file in Excel and filter on each attribute field, looking at the unique values to see what jumps out as not like the others (e.g., “1”, “2”, “&&”, “3”…).

5. Confirm the accuracy of your grouping data. Another useful activity that can be done while viewing the product catalog data in Excel is to check major grouping/filtering fields like product family, category or class to make sure no products are assigned to the wrong category, class, or family.  Likewise check any product status/product lifecycle fields, e.g., make sure that you have correctly identified all discontinued products.

6. Check for spurious control characters within text fields. Check that there are no unusual characters extracted in your product descriptions, such as carriage returns or tabs within the description value itself.  If so, make sure you can extract that data using double quote enclosures around the description or else fix data entry errors in the ERP system directly.

7. Verify that data have a standard layout. Check that your extracts of transactional data (e.g., customer orders, customer shipments, purchase orders, supplier receipts) contain no duplicate rows.  If they do, either identify what fields need to be added to make the rows distinct or, if they are truly duplicates, remove the extra copies in the ERP database.

Dealing with Exceptions

8. Detect and react to exceptions. Identify any attributes of transactional data that would mean they should not be used, such as cancelled orders.  Understand the process around mistakenly entered orders or cancelled orders to ensure against counting, or double counting, these types of transactions.  Watch for other data attributes that would imply that attribute should not be used, such as drop shipping to the customer directly from a supplier rather than shipping it from your own company. 

9. Codify the handling of exceptional internal transfers. Define the idealized record of emergency internal stock transfers and then provide rules to edit any transactions done on an emergency basis that vary from the ideal pattern.  For example, if product P1 is supposed to be shipped out of location A, but there was an emergency shipment out of location B, the demand history for P1 at location A is hijacked and less than it should have been.  If possible, provide a rule on the preferred shipping location for each product so that the history can be corrected by the inventory optimization software for forecasting purposes.

10. Devise a procedure to handle supersession. Supersessions arise, for instance, when adopting a new ERP which re-indexes the products, or an old product is replaced by an updated version, or an entirely new product obsoletes and old one. If product identifiers changed within the past few years for any reason, identify a mapping from the old product ID to the new.  These rules should be available to the demand planning and forecasting system and editable within the application.

Failure to anticipate data problems is a major impediment to smooth implementation of new analytical software. No list can enumerate all the odd things that can go wrong in curating data, but this one highlights common problems and sensible responses.

Note: For more on how data problems can stymie the application of advanced analytical  software, see Sean Snapp’s excellent blog on how this issue is obstructing the application of artificial intelligence and machine learning.  https://www.brightworkresearch.com/demandplanning/2019/05/how-many-ai-projects-will-fail-due-to-a-lack-of-data/

MAX-MIN OR ROP – ROQ

by Philip Slater

This guest blog is authored by Philip Slater, Founder of SparePartsKNowHow.com the leading educational resource for spare parts management. Mr. Slater is a global leader and consultant in materials management and specifically, engineering spare parts inventory management and optimization. In 2012 Philip was honored with a national Leadership in Logistics Education Award. To view the original blog post, click here.

There are essentially two ways that companies express their inventory control settings: either as MAX- MIN (sometimes MIN-MAX) or ROP-ROQ.

Some people will say that it doesn’t really matter which you use, just as long as you understand the definitions and the pros and cons. However, in my experience it does matter and this is one aspect of spare parts inventory management that you really do need to get right.

Let’s Start With the Definitions for MIN, MAX, ROP & ROQ

MIN = short for minimum

There is, confusingly, two schools of thought about what is meant by the MIN. Most typically this is the point at which the need to order more stock is triggered. Sometimes, however, the MIN is seen as the minimum quantity that can be safely held to cover expected needs. In this case the need to order more stock is set so that the reorder point is one less than the MIN value. That is. MIN -1.

The key to managing when using a MIN setting is to understand the configuration of the computer system you use, as different definitions will change the resulting holding level, the re-order point, and perhaps even the actual safety or buffer stock.

MAX = short for maximum

This value is most typically the targeted maximum holdings of the item. Usually, in a MAX- MIN system, where the MIN is the reorder point, the quantity reordered after reaching the MIN is the quantity required to get back to the MAX. For example, if the MAX- MIN is 5-2, when the quantity in the storeroom reaches 2, procurement would need to order 3 to get back to the MAX.

ROP = Reorder Point

As the name suggests, quite simply, this is the stock level at which the need to reorder is triggered. This is calculated by determining the safety stock level and the stock required to service needs during the reorder lead time.

ROQ = Reorder Quantity

Again, as the name suggest, this is the quantity to be reordered when the ROP is reached. This is not the EOQ but rather the quantity that both makes economic sense and is commercially available.

The Differences are Meaningful and Important

It is essential that every inventory manager understands that the MAX- MIN and ROP-ROQ approaches are not simply interchangeable.

For example, in general terms:

MIN can be equated with the ROP, except if you have a system set up for reordering at a point of MIN-1. In that case, there is no equivalence.

For slow moving items the MAX can in some circumstances be equal to the ROP + ROQ. This is because for slow moving items it is possible that there will be no additional demand before the newly ordered item(s) arrive in stock.

However, with all other items the MAX is UNLIKELY to be equal to the ROP + ROQ as items may be issued between the time of reaching the MIN and the newly ordered items arriving. In fact, there is a logic that says that the MAX would never actually be achieved.

Do these differences matter? I think that they do.

For example, what if you change IT systems? If you move from one type of MAX-MIN system to another but they define the MIN differently then you cannot just migrate your data. This may not seem obvious if everyone is using the language of MAX-MIN but is classic trap where words are used in different ways.

Similarly, if you are benchmarking your holding levels with another company or site then you need to be aware of the different definitions and the outcomes that each approach would achieve. Otherwise you are comparing ‘apples with pears’.

Or what about what happens when a new team members arrives at your company and their previous company used the terms MAX-MIN but with different parameters or meaning to that your company uses. There will likely be an assumption that the terms are used in the same way and this could lead to stock shortages or overstocks, depending on the differences in the definitions.

To add further confusion, some software systems use the term ‘Safety Stock’ to represent the MIN holding level, despite this not being the universal definition of safety stock. This different nomenclature leads some people to assume that holding less than the so-called ‘safety stock’ according to your IT system is ‘unsafe’ or risky, when in fact it may not be at all. They may even be holding an excessive level of stock because they don’t properly apply the term ‘safety stock’. Calling it safety stock does not make it so.

Pros and Cons

MAX-MIN

Pros:

• Conceptually simple to understand.

Cons:

• Terms can be misleading in terms of safety stock and actual maximums.

• Terms are used in different ways and so caution required to ensure a common understanding.

• Values often set using ‘experience’ or intuition.

• Often leads to overstocking while reporting misleading overstock data

ROP-ROQ

Pros:

• Meaning of each term is clear and consistent.

• Values set using auditable logic.

• Safety stock values clearly established.

• Holdings more likely to reflect the actual needs and commercial constraints.

Cons:

• Requires more work to determine the appropriate values.

You Need to Get This Right

The differences between MAX-MIN and ROP-ROQ are not trivial and the terms certainly are not interchangeable. In my experience, the ROP-ROQ approach produces greater transparency and is easier to manage because there is no confusion about the meaning of the terms. This approach also produces a more appropriate and auditable level of inventory.

This suggests that if spare parts inventory management is important to you then you really do need to get this right.

Does your extended supply chain suffer from extreme seasonal variability? Does this situation challenge your ability to meet service level commitments to your customers? I have grappled with this at Rev-A-Shelf, addressing unusual conditions created by Chinese New Year and other global events, and would like to share the experience and a few things I learned along the way.

First, let me explain our situation. We import 60% of the parts we use to build our kitchen and bath accessories from China and Europe. Most of the year we were able to plan our inventory needs using a spreadsheet-based min/max approach. But not during Chinese New Year, which drives the planet’s greatest annual population migration. Chinese New Year shuts down production for up to two months, creating significant supply risk as we strive to meet our three day order fulfillment commitment.

We solved our problem, introducing statistical demand forecasting with the flexibility to extend lead times when necessary, the ability to reliably establish safety stocks that achieve our required service levels and a continuous reporting system that lets everyone know exactly where we stand. However, success required much more than a new piece of software. We needed to change the way we view future demand, supply risk and safety stock. Here are a few key things we did that made all the difference.

Stakeholder education and buy-in

Regardless of the project, it’s always best to enlist the buy-in of all stakeholders. We knew we had to do something to solve our problem, but there was bound to be resistance. Senior managers, for example, had developed a healthy distrust of software and wondered whether demand forecasting software could help. Our buyers had developed their own perspectives and procurement methods, and felt personally at risk as we considered new approaches.

People came around as they developed a common understanding of the problem and how we would address it. Education was a big part of the solution. We explained how forecasting works and key factors we should all understand: how to analyze trends, how to use “what if” scenarios, impact of shifting lead times, how to relate service levels to supply risk and safety stock and key performance indicators like inventory turns. Going through this process together, we all became stakeholders in the solution.

Use the Right software

When you have lots of part numbers and any sort of supply or demand variability, you just cannot forecast effectively with a spreadsheet. With our min/max forecasting system, we were planning to an average, and it wasn’t working. Average usage has inherent flaws for planning purposes—it’s always looking backward!

You need software that looks ahead, recognizes seasonal patterns and enables you to determine how much stock you’ll need to meet required service levels over varying lead times.

Fine-tune processes

When the old ways don’t work, you need to be open to adjusting your assumptions. Think less about where you’ve been, and more about where you want to be. Take a look at your lead times and plan to your desired service level. Last year’s history may not be the best predictor of this year’s demand. The same forecast horizon may not be appropriate for all products or certain time of the year.

Make the Forecast Actionable

It’s not enough to produce an accurate forecast and estimated inventory stocking levels. You’ve got to develop a way to make the information actionable for those tasked with using it. We developed a set of reports that enabled buyers to leverage better forecast and safety stock information. Now, at the end of every month, we produce a forecast report that provides a clear picture of current inventory, safety stock, past usage, forecasted usage, incoming deliveries (PO’s) and recommended order quantities.

Validate Results

You can, and we did, test our new methods against our own demand history. Still, an authoritative outsider can make acceptance easier. We commissioned a study by a professor at Louisville University’s College of Business who set one of her graduate students to the task. Through them we were able to reinforce what we saw happening from our results, and feel comfortable that we were on a good path.

All of these factors helped Rev-A-Shelf transform its demand planning process, to great effect. Today we are exceeding our service level targets, and our fill rate, based on a three day ship cycle, is showing steady improvement, and trending up. Overall, units-in-stock have stayed flat while supporting a 13% increase in sales.

John Engelhardt is currently Director of Purchasing and Asian Operations for Rev-a-Shelf, LLC in Louisville, KY. He has held a variety of management positions both in private business and public organizations. At Rev-A-Shelf he held the position of International Sales Manager and Director of Sales Support before assuming his current position. He can be reached at johne at rev-a-shelf dot com.

At year’s end, we are often caught up in thinking and planning for the coming year. Did 2013 turn out the way you expected? Will 2014 be dramatically different? Are there other factors—things we are planning to do; things we think our competitors might do; outside forces like changing taste, demographics or economics—that might change the course of business in the coming year?

Most companies that do a formal forecast start out with a statistical projection of past sales patterns into the future. Your forecast model should detect and characterize any seasonality inherent in your markets and include that in the projection. But that’s just the first step.

The next thing to consider is product lifecycle. Nearly all products go through a predictable cycle of introduction, acceptance and growth, maturity (demand levels off) and finally decline to obsolescence. These cycles can be as short as weeks or as long as decades. Clothing fashions and consumer electronics would be on the shorter end of the scale, while products like plumbing fixtures and construction equipment would experience longer cycles. In specialized situations like bus fleet management, entire fleets may be replaced over defined transition periods. In any case, the demand forecast should be adjusted to reflect increasing or decreasing demand according to the product’s position in its lifecycle.

Now comes the hardest part—predicting the unpredictable. In general, the future is likely to look a lot like the recent past in a similarity to Newton’s first law of motion: a body in motion tends to stay in motion unless acted upon by an external force. But it’s those external forces that can send your carefully calculated forecast right into the gutter. A competitor might slash their prices to take away some of your market share. New technologies might obsolete your product before the end of its expected life span. Changing tastes or new regulations might stop sales in their tracks.

But good things might happen as well. You might be the one to slash prices or improve your product and take away a competitor’s business. Your product may catch the fancy of the market and sales will skyrocket. A competitor may abandon the business or go bankrupt, leaving you with more opportunity.

Should you plan for these kinds of things? Certainly, to the extent that you can. You may know when you’ll run promotions or phase in the next product line. But the future, by nature, is uncertain. History and your business knowledge of the past lay the foundation for your view of the future. Statistically-based tools can help you create a risk-adjusted forecast, with safety stock recommendations that correspond with the level of risk you are willing to take. Beyond this, your key to success is agility—the ability to adapt to changing conditions. Prepare the best forecast you can, build your plans around that forecast—then monitor sales and market conditions closely and continuously. Look for early warning that things may be going in a direction other than you predicted.

You must be willing to recognize and adapt to changing conditions—in other words, don’t fall in love with your forecast and ignore evidence that it may be wrong. Pride of authorship in this case can be deadly to the business.

It is also important to have contingency plans in place so you will be prepared to make the necessary changes to procurement, production and inventory to respond to the new estimate of demand. The best tools for this are the shortest possible lead times (both production and supplier), good supplier relationships and a clear view of the world and your markets.

Forecasting is difficult mainly because people know it is likely to be wrong and nobody likes to be publicly and visibly wrong. Nevertheless, a good forecast is necessary to position the resources necessary to satisfy customer demands. Just be open to the first signs of change and be prepared to react quickly and decisively.

Dave Turbide, CFPIM, CIRM, CSCP, CMfgE is a New Hampshire-based independent consultant and freelance writer. He can be reached via e-mail at dave at daveturbide dot com.

Bud Schultz, CPA, Vice President of Finance for NKK Switches, presented his company’s experience with demand planning during a recent webinar. The following is a brief summary of Bud’s key points; view the complete webinar by clicking here.

Q: Tell us about NKK’s business and demand planning challenges.

NKK Switches, based in Scottsdale, Arizona, is a leading manufacturer and supplier of electromechanical switches. The business involves many different switch types—toggles, push-button, rotary, even some programmable switch types. We are known for our high quality, and for our ability to meet an exceptionally broad range of customer requirements on a turnkey (custom configuration) basis. NKK Switches produces customized solutions from component parts sourced exclusively from manufacturing facilities in Japan and China.

There are literally millions of possible switch configurations, and we never know what configured solutions our customers will order. This makes our demand highly intermittent and exceptionally difficult to forecast. In fact, until fairly recently we considered our demand unforecastable. We operated on a build-to-order basis, which meant that customer orders could not be fulfilled until their component parts were produced and then fashioned into finished goods by NKK. This resulted in long lead-times, painful for our customers and a competitive challenge for our sales organization.

Q: What did you expect to get from improved product demand forecasting?

When we began to investigate the value of demand forecasting software (SmartForecasts from Smart Software), we tried to view the decision from a Return on Investment (ROI) point of view. We did some capital budgeting, making assumptions about potential reductions in inventory levels, reduced inventory carrying costs and other potential savings. Although the capital budgets returned positive returns on investment, we nevertheless were unable to move forward based on that information. We lacked confidence in our assumptions, and we were worried that we wouldn’t be able to justify the safety stock and inventory levels that the software would suggest.

What we didn’t expect was a challenge from our parent company. In light of the capabilities of a newly implemented ERP system, they would consider a new approach. If we could produce demonstrably reliable demand forecasts, they would consider procuring raw materials and producing switch components on a build-to-forecast rather than build-to-order basis. This opened the door to a much more profound impact. We tracked actuals against forecasts over a twelve-month period and found that our forecasts, particularly in aggregate, were exceptionally accurate: actual demand was within 3% of forecast. Once we were able to prove the validity of our forecasts, we were able to move forward with the parent company’s plan to manufacture product based on those forecasts.

Q: How did accurate forecasts of product lines with intermittent demand data transform NKK’s operations?

From the many different combinations we manufacture to order, individual switch parts can show very intermittent demand (long periods with zero orders and then seemingly random spikes), but we can identify more consistent patterns across switch series. All of the part numbers in a given series have common components and raw materials, such as plastic housing, brackets and other hardware, gold, silver and LEDs.

Providing our manufacturing facilities with reliable forecasts ended up allowing us to make dramatic changes. Our manufacturing plants could start procuring raw materials that in the aggregate would eventually be used in production of different part numbers within that series, even if the specific part numbers to be produced were unknown at the time the forecasts were made. And in many instances, despite the irregular demand history data, it was even possible for the suppliers to manufacture specific part numbers based on the forecast.

Once the program is fully implemented, we anticipate our leads times will be reduced to half the time or even less. Shorter lead times will result in lower reorder points, resulting in higher service levels while reducing our inventory requirements.

Bud Schultz leads all finance and accounting functions at NKK. His background as a Certified Public Accountant, attorney, engineer and pilot for the US Air Force provide unique perspective on finances for engineering and manufacturing operations.