Is Your MRO Cataloguing Data Set Up to Support Minimum Stocking Costs?

Introduction

For the past 10 years, Ramsoft UK have been focused on helping companies reduce their inventory. Our most popular training course outlines 38 tactics to optimize and reduce inventory costs. We have conducted this workshop almost 80 times worldwide.

Five recent MRO Inventory data audits indicate that most companies are still not set up to achieve minimum stocking costs. These audits have ranged from 4,000 line items, in the case with most smaller companies, to large companies with almost 200,000 line items, covering such industries as mining, oil & gas, and food & beverage.

All five companies have major cataloguing issues and likely involved consultants in creating three of these five datasets, either at the initial project phase or during the data migration.

Why is cataloguing of MRO data still poor? We believe the main causes are:

• Lack of knowledge – oftentimes, people do not look at their master data critically. During a recent training class, an inventory manager, when asked to give his opinion on the quality of his catalogue data, replied “our cataloguing is very good and was carried out by a consultant”. This did surprise us, so we offered a free mini-audit. After seeing our findings, he soon changed his mind and agreed that the consultant company had done a poor job in building their catalog. Most people do not realize that they are not set up for minimum costs and, hence, are incurring excessive stocking costs.
• The people who attend our training course return to their workplace and soon find themselves getting sucked back into their daily activities.
• Many consultants do not use tools to achieve data governance. Often, they subcontract this work to countries with a low wage rate, as many of these consultants are not spare-parts specialists themselves.
• Companies have outsourced their warehouse management to specialist companies. We have audited MRO datasets that are managed by these companies and have so far seen no evidence that they improve data quality and eliminate duplicates.

Poor spare-parts cataloguing results in:

• Inability to identify duplicates
• Inability to find the right spare in a timely manner
• Inability to identify standardization opportunities
• Inability to conduct effective comparisons of new recommended spares to existing inventory
• Too much reliance on OEM and vendor P/Ns
   

What Do We Mean by Set-Up for Minimum Cost?

Most inventory savings are achieved from the following four areas:

• Identifying and Removing Duplicates: In our most recent audit for Fisher Control Parts, we found that from a total of 6,068 parts,1,256 could be removed, which resulted in a 20% reduction. In some cases, the same part was stocked under eight (8) different stock numbers.
• Converting OEM Vendor P/Ns to OPM (Original Parts Manufacturers): The issue here is that spares recommendations received in the project phase are purchased, received, and put on the shelf. But there is no validation of the initial catalogued data to the actual item received in the stores. So, the spares record continues to show the Vendor P/Ns along with the initial descriptions. Remember, the original vendor P/N is meaningless, i.e., not recognized by another vendor who has received a PO from the company. Delegates have admitted this was a major problem in the two training courses we have conducted in the past.
• Part-Standardization: This means standardizing on type of material or a manufacturer or a spare type. One of our first studies identified between 60-70% reduction in line items for ring-type joints and spiral-wound gaskets by a combination of standardizing and removing duplicates. Stock optimization could have revealed further reductions in stock holding.
• Stock Optimization: A recent case study showed a potential for up to a $500,000 reduction in maximum stock levels, and our recommendations were very conservative — approximately 50% of the difference between actual max. levels and the optimized maximum stock level.

Quality cataloguing is a prerequisite to tackle these cost-reduction opportunities. Good cataloguing is the foundation stone for improved inventory performance.
 

How Do We Identify Cataloguing Issues?

A full MRO inventory audit comprises many elements as shown in Fig 1 below. However, this article is only focused on cataloguing and duplication analysis, which are parts of the analysis of MRO inventory data.

 

Fig 1 Inventory Management Audit Scope

Fig 2 illustrates our six-stage improvement process for an existing site and shows that achieving good cataloguing and cost reduction is covered in all six (6) stages. We covered these steps in  an earlier article.

 

Fig 2 MRO Improvement Process
 

Phase 1 - Catalogue Data Audit

The main areas included in the basic cataloguing audit are:

• Check noun- and qualifier-use for consistency.
• Check for appropriate attribute use.
• Check manufacturer P/Ns for: consistent format, no vendor P/Ns, no additional text has been added (e.g., manufacturer identifier, size, etc.)
• Check for consistent use of stock type or category codes.
• Check for duplicates, triplicates.
• Check for potential standardization.

Here are some examples from our many audits. Fig 3 shows bearings from an aircraft maintenance company with very poor item descriptions. This dataset is among the worst we have seen at Ramsoft:

Fig 3 Part Description (Aviation Maintenance Company)

Fig 4 shows a bearing example from data being housed in an SAP database. If you look closely, you will see six (6) variations to enter a ball bearing. You will also see the use of no spaces, one or two spaces, use of commas, along with descriptions inconsistently named — some start with a noun (bearing) while others start with the qualifier (ball).

 

Fig 4 Bearing Descriptions Oil & Gas Operator

 

Fig 5 shows an example of timing belts from a large food manufacturer:

Fig 5 Poor Descriptions & Population

The timing belts in Fig 5 do not show the belt width, therefore, it is impossible to purchase these belts. Four of the five belts shown only have a vendor P/N, and these P/Ns are not searchable. It is not uncommon to find in the data-validation and cleansing phase up to 50% of line items are inadequately categorized, and the new part description cannot be completed without a physical storeroom bin check.

Fig 6 shows a similar example for bolts and set screws: there is no material specified, and we can only assume these metric threads are coarse. For grub screws, we need to know the endpoint and the head type:

Fig 6 Lack of Key Attributes

It is possible some of these bolts and screws require a high tensile material, and therefore, are a disaster waiting to happen if the wrong grade of bolt is purchased and fitted during maintenance.

Duplicate Analysis

Most companies typically see 10-15% duplicates their stockrooms, which can directly result in excess stocking costs. There are many reasons for duplicates, i.e.:

• There is no manufacturer’s P/N populated in its own field.
• The manufacturer's part-number data field frequently contains vendor part numbers.
• There are typos during data population, e.g., ‘I’ used instead of a ‘1’.
• Manufacturer P/Ns are modified by the vendor or the cataloguer, e.g., by adding a size or the manufacturer’s name.
• Different P/N formats are used during the setup.
• Incomplete P/N strings
• Manufacturers P/Ns are populated with different P/N formats e.g., ‘2Z’ or ‘ZZ’ as used in bearing P/Ns
• There is a lack of attributes in populated data, with the description often focused on where the item is used.
• Lack of catalog maintenance when manufacturers update their P/Ns; this often results in both the new and the superseded P/N’s being stocked, e.g., Bearings ‘2RS’ and ‘2RS1’.
• A process is not in place to effectively identify duplicates (we have never seen this explained in any textbook).
• There are no rules in place to deal with parts that have dual P/N, i.e., a product identifier as well as a catalog P/N. When we audit or cleanse MRO datasets, we find the manufacturer P/Ns are a mix of either:
• The Catalog Number
• The Product Id or
• Both the product and the catalog P/N have been entered

Fig 7 shows one example where different formatting has been used for the P/N. Only by stripping out the non-alphanumeric characters can these duplicates or triplicates be identified effectively:

Fig 7 Lack of Key Attributes

Potential Rationalization Opportunities

Fig 8 shows an example from a large mining operation. The company stocked many caterpillar belts showing only the caterpillar P/N. From just the part descriptions alone, we do not know the type of belt or the cross-section or the length. The stores inventory included many other OEM P/Ns that are likely to be duplicated and result in excess stocking cost. Drive belts are considered generic items, and as such, the description should not include the manufacturer.

Fig 8 Lack of Key Attributes
 

Phase 2 - Improve Documentation

In terms of cataloguing, Phase 2 includes the development of a cataloguing and an abbreviation guide.

 

Phase 3 - Data Validation and Cleansing

If the audit reveals significant quality issues, then a full or partial data validation and cleansing would be recommended. This is a time-consuming activity and cannot be done remotely. As highlighted, many items will need to be validated physical at the bin by the company’s storeroom team.

Fig 9 below shows a typical data-validation and data-cleansing process:

 

Fig 9 Process for Data Validation and Data Standardization

Our analysis tool currently holds almost 500,000 part numbers and continues to grow. This master P/N database helps to speed up the validation and cleansing process, as well as identifying potential P/N typos in the existing inventory list. The process also identifies obsolete items (where this information is available from the mfr's or vendor’s web site).

There are several options to validate and standardize short descriptions and P/Ns.

Option 1: Update the master data direct in the CMMS.

Option 2: Download to MS Excel and then standardize. However, it is difficult to build-in adequate data governance with Excel, so a database solution is preferred.

Option 3: Use a template approach, i.e., develop noun/qualifier1/qualifier2 templates that includes a standardized description. A 'Template Id' is then assigned to each spare part. This approach is carried out in an MS Access database.

Option 4: Use a combination of Template Ids as well as a master database of P/Ns (This is what we use in our data-validation and data-cleansing projects). This approach can save a significant amount of time especially for multi-site companies. For multi-site companies it means you only standardize a part description once.

Following master-data cleansing, we have achieved a 50-60% reduction in the number of line items for certain material groups by:

• Eliminating duplicates
• Converting OEM parts to OPM parts
• Reducing the number of types and manufacturers
• Removing parts made from a material that is not fit for purpose

Fig 10 below shows an example of before (yellow text boxes) and after cataloguing (grey text boxes). The existing master data only shows a vendor P/N. Luckily, the RS Component Stock Nos. are searchable in Google, and we were able to identify the Mfr and the Mfr’s P/N.

Fig 10 Before and After Cataloguing

With a database solution, you can link different data tables, as well as include an image of the item, as shown in Fig 10. These images provide additional governance.

Good cataloguing means that when we conduct an Excel sort in descending order, all similar material is grouped together as shown in Fig 11 below. The new descriptions also make it easier to find the right spare in a timely manner, thus leading to reduced equipment downtime.

Fig 11 Before Versus After Cleansing Descriptions
 

Phase 4 - Standardize and Rationalize

We have highlighted one example of a lack of standardization for belts, see Fig 8.

Standardization should be applied in the project phase. A recent audit for a major food manufacturer revealed that drive-chain components were stocked for both European and ANSI standards. The project-phase equipment specification should have included which standard should have been used for the parts to help reduce the variety of spares stocked.
 

Conclusion

Most companies have populated their inventory system with poor cataloguing data, thus leading to increases in stocking costs due to:

• Too many duplicates
• Many line items that cannot be identified
• An excess of OEM stocked parts
• Lack of standardization

Auditing by an expert can reveal if there are deeper cataloguing issues in your facility. If identified properly, then the appropriate approach for standardization needs to be selected and applied. This can be carried out in-house following proper training in good cataloguing principles or subcontracted to an external company.

Just keep in mind that if conducted by an external company, it is essential that a knowledgeable person reviews their deliverables and is also involved in the process. We have seen many examples of cleansing work done by so-called "experts" that did not meet the standards that are required for optimal inventory management. Since the resulting excess stocking costs from a miscatalogued stockroom can greatly affect your company's bottom line, it is crucial to make sure you know your data and that it's catalogued correctly for optimal stockroom performance.