Maintaining and Verifying Database Integrity in a Manufacturing Environment

January 1, 2011


In this age of rapidly changing products and processes it is important that all elements of the manufacturing database are verifiably accurate if an MRP system or any other type manufacturing control system is to be used effectively. The objectives of this paper are 1) to review cycle counting principles as applied to inventory quantities, 2) to establish a problem solving approach that will correct the source of errors, not merely correct the errors and 3) to identify other elements of the manufacturing database that should be audited.

Too few companies have an effective inventory cycle counting system in place, fewer still have any method of auditing the accuracy of other elements of their database essential to successful operation of the manufacturing system. The result is the real data values change over time while the values used by the computer system remain the same. A factor as obscure as a tool replacement lead time, if incorrectly specified in the database, can cause schedule dates to be missed that can ripple through the entire manufacturing process.

The goal of the manufacturing control system is to have material and resources available in a timely manner when needed for production. At the extremes this can be accomplished by 1) the Brute Force Method (BFM), having lots of material and resources so you never run out or 2) the Computer Fanatic Method (CFM), using the computer to control everything down to the last eyelash. Neither method is feasible or cost effective, so most companies function somewhere between the extremes. Some companies believe themselves to be using the CFM when, in fact, they are using the BFM.

In order to maximize the effectiveness of a system, database verification must be an integral part of the system. Date errors must be identified, the source of the error discovered and a solution to prevent (not just correct) the problem at its source developed. The proposed solution should be tried and, if successful, implemented as a part of the ongoing operating procedures. The goal is to prevent, not just correct.

Cycle Counting of Inventory Quantities

It is not the intention of this paper to develop a complete cycle counting system, but to review the principles that should be reflected by a good cycle counting system.

Although often misused in this manner, the purpose of cycle counting is not merely to correct inaccurate inventory quantities and thereby artificially improve inventory percentage accuracy. Using cycle counting to correct inventory values without attacking the underlying problems results in a counter productive masking of the problems. The appropriate goal of an inventory cycle counting system is to 1) verify that the system is doing a good job of tracking actual quantities (verification) and 2) identify problems that exist so management and operating personnel can focus on solving them (feedback).

Cycle counting replaces the BFM of the annual inventory “count and correct” performed by lightly trained personnel for whom it is a part time activity. The replacement is a regular (daily) audit of selectively rotated inventory items performed by well trained personnel with the purpose of verification and feedback as stated above.

Cycle counting is a focused activity; that is, the greatest effort is put into that portion of inventory items with the most value. Thus high value items receive more attention (more frequent counts) and lowest value items receive less attention. In addition, items that have value to the process disproportionate to their purchase value are also given more attention. Examples of this are items with long lead times and special or hard to procure items.

Good cycle counting systems also match frequency of counts to the inventory control method. For example items for which perpetual records are kept would require more cycle counting attention than items that are physically controlled with a two bin system.

Finally, the frequency of count should increase when a lack of accuracy is discovered. This increase in frequency is maintained throughout the problem solving and solution implementation phase to insure that the solution is effective in solving the problem. When cycle counting verifies this, the frequency of count can return to the normal level.

To summarize the cycle counting principles, cycle counting should:

  • Be used to verify the accuracy and feedback information regarding problems within the system. It should not be used only to correct inaccurate counts and hide problems.
  • Be focused, with higher levels of effort being devoted to items that have higher value to the process or as products.
  • Be performed by personnel well trained and well acquainted with the cycle counting process.
  • Match the level of counting attention to the inventory method used on a particular item.
  • Adjust the frequency of counting to the accuracy or lack of accuracy encountered in an item or group of items.
  • In addition to problem identification which is an inherent part of the process, cycle counting should include a solution and implementation process.
  • Must always be viewed as a feedback loop system.

Problem Solving in a Cycle Counting System

Accuracy problems uncovered by cycle counting are quality problems, just the same as any other process or product problems. As such they respond to the same analysis techniques developed for quality problems found on the shop floor.

The first step in establishing an effective cycle counting and problem solving system is the establishment of accuracy goals. Goals are indispensable as a focus for activity and a progress measurement tool. There are many guidelines for inventory accuracy goals discussed in other sources. Any of these that fits the situation is probably a good starting point. However it must be remembered that successful quality improvement is a process that never ends. Each goal attained should lead to the setting of a new, higher level goal with the ultimate goal being 100% inventory accuracy.

Problem Identification

The next step, problem identification, is performed by the cycle counting process itself. Each inaccuracy discovered by a cycle count must be considered a problem. It is important that cycle counters be well trained and product knowledgeable so they do not introduce errors into the system through incorrect counts and identification. Verification counts and subsequent counts of related items will provide guidelines as to the scope of the problem. The analysis phase will provide the mechanism to prioritize problem analysis by identifying the magnitude of the cause.

Search for Problem Causes

The search for causes typically yields a source in one or more of three categories–systems, people or database. Problems will also appear to have one of two attributes–chronic or random.

Systems problems, those caused by an incorrect procedure or software “bug” are almost always chronic, since the program or procedure, if continually followed, will yield consistently incorrect results. The difficulty is in identifying the pattern of the chronic behavior of the system. Once this is done it usually leads directly to the source of the problem.

People problems may be chronic or random. However most random problems fall into the category of “statistical variation” and can be solved by a gentle reminder or brief retraining. Chronic people problems can almost always be solved by training, but what the training should be and who should be trained is a subject far beyond the scope of this paper.

Database problems can be random or chronic and in both cases must be tracked through the interconnections of the database to the root cause. Once the root cause is discovered, it is important that the determination be made whether the problem is chronic or random. Random problems can be fixed by correcting the erroneous piece of data in the database. Chronic problems require not only the correction of the data but also 1) the implementation of cycle counting procedures for that element of the database to insure continuing accuracy 2) review and correction, if necessary, to the process of entering new data into that element of the database and 3) review, in some cases, of the method and frequency by which data in the database element is updated and kept from getting “stale”.

Many problems will be caused by a combination of system, people and database. As such, system correction, training and database process improvement will all be applied in the development of a solution.

Propose a Solution to Correct the Problem

A well defined root problem cause will suggest its own corrective action. If the people directly involved in the process being reviewed are involved in the investigation, they will also be a good source of proposed corrective actions.

Several corrective actions may be suggested. Each should be listed and prioritized based on the consensus of the personnel involved. Often more than one suggestion can be incorporated into the proposed solution. The chosen solution often will not be the most sophisticated or most expensive. Good problem solutions tend to simplify the system, not make it more complicated. The group must be committed to the solution selected. Retain the list of alternative solutions. The first corrective action may not work or may only solve part of the problem. The list will serve as a starting point for further problem analysis.

Test the Solution

Develop an implementation plan to test the proposed solution. The plan should include:

  1. A description of proposed changes to be made in the system.
  2. The subset of data to be tested by the proposed solution.
  3. A time schedule for all events in the implementation plan.
  4. A method of measuring progress and results of the solution toward fixing the problem.
  5. A set of criteria that defines what will constitute a successful solution. These should be related to the overall quality goals established for data accuracy.
  6. A scheduled followup point at which the next step (permanent implementation, revision or “back to the drawing board”) is defined.

Review and Evaluate the Solution

One of three things will happen–the problem will get better, the problem will get worse or nothing. If the problem gets better, depending on the degree of improvement, the problem or some part of the problem may be solved. If the stated accuracy goal has not been attained, a return to the search for problem causes step is required using the knowledge you have gained.

If the problem gets worse, the selected solution may have no effect or be counter productive. More likely, you have solved a problem that was acting as a compensating error to another, larger problem. Any accountant can tell you compensating error stories about a $10.00 error in Accounts Receivable that turned out to be a $10,000.00 positive error and a $9,990.00 negative error. Compound errors frequently happen early in an accuracy improvement program. Analyze the effect of your solution carefully to determine if it is effective; then go back to search for additional problem causes.

If there is no effect as a result of your action, chances are you have not discovered the root cause. Make sure you have implemented your solution properly, then return to search for additional root problem causes.

Make Your Solution Standard and Permanent

Once the proposed solution is verified as improving or correcting the problem, it must be made a permanent part of the operating process of the departments involved. This almost always takes more planning, effort and involvement than a memo from the boss beginning “From now on….”.

Typically the change must be included into written and oral procedures and specifications. Training of all relevant personnel must take place, complete with testing and measurement of the training to insure that the proper learning has taken place. Finally, there must be followup, both on the personnel training and measurement of accuracy via increased frequency of cycle counts in the problem area. Continue followup until sure that the new procedure is effective and has come to be considered a “normal” part of the operation by those involved.

A Lot of Work

If this process appears to be a lot of work, that’s because it is. Remember, the solution to most problems first requires unlearning the existing process, then learning the new process. If you think its too much work in your environment, just ask yourself the old question, “Why do we never have time to do things right the first time, but always have time to do them over (and over and over)?”. The approach here is not to create systems and double checks to correct problems, but to create processes that prevent problems. Don’t complicate the system, simplify it.

Several factors will, over time, change the perception of the amount of work involved and verify the wisdom of pursuing this approach. First, all problems will not require such detailed analysis as described here. In many cases the process is much more complicated in the telling than in the doing.

Second, as the problem identification and solution process is repeatedly applied and problems are permanently eliminated, both the number to solve and the requirements for application of the problem solving process are reduced.

Finally, as this process becomes the standard approach to problem solving within your organization and the results become apparent, it will come to be considered much less onerous by everyone.

Generalizing the Approach

When inventory accuracy problems occur in a highly integrated manufacturing control system, the search for the source of the problem, no matter what the problem solving technique often leads to other elements in the data base. Inaccurate inventory can often be traced to inaccurate or obsolete bills of material, incorrect scrap factors, or even the specification of the wrong unit of measure.

Incorrect leadtimes cause late completions, inaccurate shop routers and manufacturing rates create erroneous capacity plans, incorrect bin locations cause expense, delays and confusion.

Some of the relationships are not so direct. Incorrect labor rates, labor grades and burden rates can cause the wrong new products to be selected for production or wrong existing products to be deleted from the line. Obsolete ROP’s, EOQ’s and safety stock calculations can increase inventory levels, stock outs and manufacturing costs.

It is apparent that the cycle counting principles and problems solving techniques that apply to inventory quantity verification can be applied with little or no modification to these other elements of the manufacturing database. The intention of a more comprehensive database verification program is to identify and correct process problems closer to the source. The result is a system that focuses on error prevention rather than correction after the fact.

What Data Elements to Consider

The importance of accuracy and the amount of effort that should be expended to attain it for each data element can be categorized by using the tried and true ABC method. Definitions must first be determined for each category. These definitions will, by necessity, be general and should be used as a framework for more specific definitions to be used in your particular environment.

  1. Items – Data elements that have frequent changes, large magnitude changes and/or affect the system values (time, quantity, cost, quality) in major ways.
  2. Items – Data elements that have some changes, moderate magnitude changes and some effect on system values.
  3. Items – Data elements that have little or no magnitude of change over time and little or no effect on system values. Note that in the long run few, if any, data elements are static. All need a periodic review of some kind.

The specific values by data element which you assign to “frequent”, “some” and “little or no” depend on your environment.

Which data elements should be assigned to which category also depends on the environment. For example, an industry with short product life cycles or high levels of product improvements, Bills of Material and Shop Router change levels might dictate that many data elements in those files be classified as “A” items. In other environments these same data elements may be “B” or even “C” items.

Data Elements to Consider

Various data systems group data elements differently, so you may not find the data elements in your system in the same files as are listed here. Also this is by no means an exhaustive list of all data elements. Others in your specific environment may be more important.

  1. Bill of Material file
    1. Parent/component relationship
    2. Quantity of component/parent
    3. Unit of measure of component or parent
    4. Cost of component
    5. Scrap factor of component
    6. Items shown on bill as controlled when they are not and vice versa
    7. Components may also be shown where they are not used, shown as an incorrect item or not shown at all when they are actually used.
  2. Shop Router file
    1. Work center/machine number
    2. Setup time/Inspection time/Move time
    3. Queue time
    4. Labor rate/Labor Grade
    5. Estimated or Standard Man Hours/Machine Hours/Feeds and Speeds
    6. Tool Identification
    7. Tool replacement lead time
    8. Operation scrap rates
    9. Correct operation sequence
    10. Missing/Spurious operations
  3. Inventory file
    1. Product category
    2. Quantities on hand/available/on order
    3. ROP, EOQ and Safety Stock
    4. Bin locations
    5. Prices and costs
    6. Unit of measure
    7. Unit conversion – purchase to usage
    8. Weight per unit
    9. Lead time
    10. Vendors
    11. Order minimum
    12. Stocked, Controlled, Purchased or Manufactured, Inventory class, Backorder, Taxable codes
    13. Engineering Drawing and Release number
  4. Cost file
    1. Labor rates
    2. Labor grades
    3. Burden types
    4. Burden rates
This list is limited to files directly related to the manufacturing process. Of course there are Purchasing, Customer, Vendor, Shipping, Order, Accounting, Engineering and many other files in the database that would benefit from this type of verification, feedback and analysis.

Setting Up the Program

In all but the smallest companies a program of this type needs a focal point, a Director of Data Quality, to coordinate the selection, reviewing, measurement and feedback of the effort. If the company has an effective inventory quantity cycle counting system, this individual may already be identified as the one in charge of that program. The work itself should be done by employees directly involved with and affected by the data elements being audited. It goes without saying that top management support is essential.

The following is a brief description of the step to be taken to set up a database quality program:

  1. Review all elements of the database, categorize them by the ABC method and select those with which you want to start. Focus on the vital few rather than the trivial many. As the program progresses, problems solved and revised goals put in place, formerly trivial items will become vital and will be added to the program.
  2. Develop procedures for selecting, counting or otherwise reviewing, measuring, problem solving, followup and feedback. Follow the guidelines of a well designed inventory quantity cycle counting system discussed earlier.
  3. Select and train personnel. Training must include not only review and measurement procedures but also training in problem solving methodology, such as cause/effect analysis. Management will need training too.
  4. As a supplement to training it is often useful to initiate a pilot project. In addition to the valuable “on the job” experience it provides, it is an opportunity to fine tune the procedures and to generate enthusiasm for the project by demonstrating a success.
  5. Implement in additional areas using experienced line personnel from the pilot and other projects to be trainers and facilitators. It must be emphasized that this is not another “Spring Housecleaning” project that will end when everything has been checked. This is a basic change in the way the company maintains database quality, by focusing on improving the process, not just correcting errors in the data.
  6. Reap the benefits.

Is It Worth It?

Companies spend anywhere from many thousands to several million dollars on the computer hardware and software that was going to solve all their problems. They have implemented the CFM and are really operating on the BFM. Without an accurate database the system effectiveness is significantly reduced. In the extreme it can be counter productive (“The computer says….so it must be true.”).

Expediters are running around the facility tracking down parts that are supposed to exist but don’t, pushing orders delayed, trying to find out why a tool isn’t available or (fill in the problem on YOUR desk today).

You have wrong parts, angry customers, lost orders, missed opportunities, excess costs, poor decisions based on inaccurate data. Many of these problems are due in whole or in part to the poor quality of the database. Much of the cost of these problems is hidden; in fact it may be built into the overhead rate as expected and acceptable.

An excellent way to start for looking for program justification is to list the activities in the operation that do not add value to the products you manufacture or the process that makes those products possible. These non value added activities are candidates for elimination. Improving and verifying the quality of your database is one of the tools you can use to make this happen.


This article has taken the principles of a well designed inventory quantity cycle counting system, combined them with the problem identification and solving steps of a quality improvement program and indicated other elements in the database to which these techniques apply.

In summary, the data quality management program should:

  • Start with the vital few, those database elements that have the largest impact on the system. Use ABC techniques.
  • Develop a process based on sound cycle counting principles to validate system accuracy and provide feedback on problems.
  • Use systematic problem solving techniques; test and measure the results of corrective action.
  • Implement successful solutions as a permanent part of the organization operations.
  • Focus on improving the process, not just correcting the data.
  • Train and develop front line personnel both in operating processes and problem identification and solution techniques. Many problems emanate from a lack of understanding.
  • Make database quality maintenance procedures a regular ongoing part of the operating procedures of the company.
  • As database quality goals are attained, set new goals, pursue additional elements in the database and refine the process.

All business organizations are dynamic, in a constant state of change. Maintaining the quality level of the database requires ongoing auditing, measurement and adjustment.

Categorised in: ,

   ©2020 The ACA Group.