- All Topics
- Training & Events
- Buyer's Guide
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:
Poor spare-parts cataloguing results in:
Most inventory savings are achieved from the following four areas:
Quality cataloguing is a prerequisite to tackle these cost-reduction opportunities. Good cataloguing is the foundation stone for improved inventory performance.
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
The main areas included in the basic cataloguing audit are:
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.
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.:
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
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
In terms of cataloguing, Phase 2 includes the development of a cataloguing and an abbreviation guide.
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:
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
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.
Most companies have populated their inventory system with poor cataloguing data, thus leading to increases in stocking costs due to:
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.