Findings
This data quality audit examined the shop's entire ERP system end to end. The system consists of three master-level tables (Item, Supplier and Bill of Materials) and five transaction-level tables (Inventory Ledger, Purchase Orders, Service Orders, Production Orders and Cycle Counts), related as shown below.
Over the past 36 months, over 229K individual records were produced across the eight ERP tables. This audit reviewed all of these records and found 16 different types of data quality errors recur over this period. These errors touched a significant share of the ERP's total records, leaving it unreliable as the shop's central data record.
Master-level Table Errors
| Error | Description | ERP table | Scale (rows affected) | |
|---|---|---|---|---|
| #1 | Dead Records Never Deactivated | Items with no activity for years that are still flagged active in the item master | Item master | 980 of 2,484 (39.5%) |
| #2 | Stale Lead Times | Supplier lead times on the item record that no longer match how long deliveries actually take | Item master (live items) | 813 of 1,504 (54.1%) |
| #3 | Stale Reorder Points | Reorder points and safety stocks on the item record that no longer match how much the shop actually uses and how long deliveries take | Item master (live items) | 1,160 of 1,504 (77.1%) |
| #4 | Duplicate Item Records | The same physical part carried under two or more item numbers | Item master (live items) | 249 of 1,504 (16.6%) |
| #5 | UOM Mismatch | Items bought in one unit of measure and stocked in another, with no conversion factor recorded | Item master (live items) | 48 of 1,504 (3.2%) |
| #6 | Missing and Placeholder Fields | Required item fields left blank or filled with a placeholder value | Item master (live items) | 193 of 1,504 (12.8%) |
| #7 | BOM Omissions | Components used in production that are missing from the product's bill of materials | Bill of materials | 7 of 825 (0.8%) |
| #8 | Supplier Fragmentation | One supplier carried under several supplier records with different names or IDs | Supplier master | 7 of 44 (15.9%) |
Transaction-level Table Errors
| Error | Description | ERP table | Scale (rows affected) | |
|---|---|---|---|---|
| #9 | Unrecorded Consumption | Material consumed on the floor without a transaction recording it | Inventory ledger | 448 of 186,971 (0.2%) |
| #10 | Wrong References | Transactions posted against the wrong item or job | Inventory ledger | 1,435 of 186,971 (0.8%) |
| #11 | Quantity and Unit Errors | Transaction quantities keyed with the wrong magnitude or unit | Inventory ledger | 428 of 186,971 (0.2%) |
| #12 | Duplicate Postings | The same transaction entered twice | Inventory ledger | 498 of 186,971 (0.3%) |
| #13 | Adjustments as a Catch-All | Inventory adjustments used to correct all kinds of discrepancies rather than genuine count errors, usually without a reason code | Inventory ledger (adjustments) | 8,840 of 15,593 (56.7%) |
| #14 | Free-Text Purchases | Purchase order lines entered under a generic item code with a typed description instead of the stocked item number | Purchase orders | 1,627 of 15,185 (10.7%) |
| #15 | Batched and Backdated Postings | Transactions posted days after they happened, in batches | Purchase orders | 8,485 of 15,185 (55.9%) |
| #16 | Open Documents Never Closed | Purchase order lines and jobs left open after they were effectively complete | Purchase orders; production orders | 1,302 of 15,185 (8.6%) 407 of 5,841 (7.0%) |
Three errors stand out for their large scale within the ERP. First, the item master was full of dead records: 39% of its records (980 of 2,484) were items with no movement in two years or more yet were still flagged as active, meaning reports, searches and reorder logic were polluted by inactive parts. Second, 54% of lead times and 77% of reorder points listed in the item master were stale, meaning that if they were relied upon when making reordering decisions, then they would be placed at the wrong time and in the wrong quantity. Third, the majority of receipts (56%) are posted in batches (i.e., a large number of receipts held on to and inputted all at once) and are 3 days late on average, meaning every lead time or supplier delivery time calculated from these receipts is artificially long.
Error Remediation
Most of the data quality errors identified in Section 1 were closed in full, as shown below. A few were partially closed: 108 dead records were kept in as insurance spares, and not all duplicate pair or free-text line attributions could be confirmed as correct. No remediation attempt was made on a few of the errors, namely batched receipts (dates not recoverable), as well as unrecorded consumption and catch-all adjustments (fixed at source rather than in the history), and so while the ledger still carries them, the records are cleaned going forward.
Master-level Table Error Remediation
| Error | Remediation | Evidence | Remediated (rows) | |
|---|---|---|---|---|
| #1 | Dead Records Never Deactivated | Deactivated in the item master after a line-by-line review. The purchasing manager kept 108 as seasonal spares | ERP records only | 872 of 980 (89%) |
| #2 | Stale Lead Times | Recalculated per item from receipt history using the median and a trimmed 80th percentile, then written to the master | ERP purchase history, checked against the buyer's spreadsheet | 813 of 813 (100%) |
| #3 | Stale Reorder Points | Recalculated from actual usage over the corrected lead time at the ABC service level and loaded; the purchasing manager reviewed the A-class values before they went live | ERP purchase and issue history, checked against the buyer's spreadsheet | 1,160 of 1,160 (100%) |
| #4 | Duplicate Item Records | Candidate pairs scored, then merged to one surviving number through a crosswalk. The buyer and the stockroom lead reviewed and rejected 5 pairs as different parts | ERP records, every pair reviewed by the buyer | 160 of 165 (97%) |
| #5 | UOM Mismatch | A purchase-to-stock conversion factor added to each item, taken from the pack size on its receipts and confirmed by the stockroom lead; on-hand restated in stock units | ERP records and the pack sizes on receipts | 48 of 48 (100%) |
| #6 | Missing and Placeholder Fields | Blocking blanks filled from the ordering history (cost from the last price paid, supplier from the ordering record) and from the reorder-point recalculation; MISC items reclassified by the buyer | ERP records only | 193 of 193 (100%) |
| #7 | BOM Omissions | Missing components added back to the product and subassembly BOMs (7 change-log entries), each confirmed by the assembly supervisor | Expected vs actual consumption (jobs × BOM against issues) and floor observation | 7 of 7 (100%) |
| #8 | Supplier Fragmentation | Alias records mapped to one canonical supplier through a crosswalk (3 vendors, 7 records); the buyer confirmed each grouping, and new orders book to the canonical record | ERP records only | 4 of 4 (100%) |
Transaction-level Table Error Remediation
| Error | Remediation | Evidence | Remediated (rows) | |
|---|---|---|---|---|
| #9 | Unrecorded Consumption | Traced to its cause through the chronic-adjustment analysis (82 items assigned to BOM omission, floor practice, receiving error or count error) and fixed at the source: the BOM corrections stop the leak, and the cycle-count program corrected each balance as it was counted | ERP on-hand vs cycle counts; expected vs actual consumption | 0 of 448 (fixed at source) |
| #10 | Wrong References | Each issue re-pointed to the item the job's BOM calls for, after the stockroom lead reviewed the list | Expected vs actual consumption (the job's BOM) and stockroom review | 1,435 of 1,435 (100%) |
| #11 | Quantity and Unit Errors | Each outlier corrected to the true quantity after the stockroom lead reviewed the list; box/each keying closed off by the UOM conversions | ERP records and stockroom review | 428 of 428 (100%) |
| #12 | Duplicate Postings | The second posting reversed for every pair after the stockroom lead confirmed the two entries were one movement | ERP records only | 498 of 498 (100%) |
| #13 | Adjustments as a Catch-All | Historic adjustments left as posted but classified by the chronic-adjustment analysis | ERP records only | 0 of 8,840 (controlled at source) |
| #14 | Free-Text Purchases | Lines matched to stocked items by description similarity; the buyer settled every candidate (670 confirmed, 957 rejected as genuine non-stock buys) and the confirmed demand was attributed back to the item | ERP records, every candidate reviewed by the buyer | 670 of 1,627 (41%) |
| #15 | Batched and Backdated Postings | Not corrected line by line, because the true dates are not recoverable. The lead-time computation was made robust to it instead, and receiving moved to same-day posting under the new individual logins | ERP records only | 0 of 8,485 (method made robust) |
| #16 | Open Documents Never Closed | Each open line and finished job closed on confirmation: receiving records, the buyer or a supplier statement for PO lines, production confirmation for jobs | ERP records, receiving records and supplier statements | 746 of 1,302 PO lines; 407 of 407 jobs |
Results
As a result of the error remediation and fresh cycle counts, numerous measures of the ERP system's accuracy and reliability improved significantly. Importantly, the vast majority of active item records are now actually live and 100% of most identified data errors were resolved.
| Measure | Why it matters | Before | After |
|---|---|---|---|
| Active item records that are actually in use | Reports, searches and reorder logic are not polluted by dead parts | 61% | 93% |
| Live items whose lead time matches actual delivery (within 3 days) | Reorders are placed at the right time | 6% | 100% |
| Live items whose reorder point reflects real usage | Reorders are based on actual inventory levels | 23% | 100% |
| Live items with complete required fields | Every item can be costed, sourced and reordered | 87% | 100% |
| Purchase spend under a single, correct part number | An item's history is not split across duplicates or hidden under generic codes | 89% | 100% |
| Purchase spend under a single supplier record | Spend and supplier performance are visible | 91% | 100% |
| Products whose BOM matches what the floor consumes | Backflush records real consumption | 91% | 100% |
| Adjustments with a known cause | Corrections explain why, not just that the number changed | 66% | 100% |
| On-order value that is genuinely inbound | Reorder logic is not waiting for phantom deliveries | 70% | 100% |
| Transactions posted under an identifiable user | Errors can be traced to source | 22% | 100% |
Process Changes
The remediation efforts in Section 2 fixed the majority of the data quality issues identified through this audit. The changes outlined in this section prevent them from reoccurring, and fall into two categories.
The first category is the ERP system settings. These settings are administered once, take effect for every user immediately, and stop the errors at the point of entry. This category is a relatively easy lift: the changes hold on their own and nothing further will need to be done, though there can potentially be friction in the near term as users adjust to more limited data entry options within the ERP. The table below lists the settings changes, including what they do and the error(s) they close (the error can no longer occur) or address (the error is caught or reduced, but not prevented).
Changes to ERP System Settings
| Change | What it does | Impact |
|---|---|---|
| Reason codes required on adjustments | The ERP refuses an adjustment without a reason code from a fixed list (count variance, damage, scrap, receiving error, return), so every write-off carries its cause. | Closes #13. Addresses #9: a chronic write-off names its cause, so the pattern is visible. |
| Required fields enforced on item creation | A new item cannot be saved without a standard cost, a primary supplier, a unit of measure and a reorder point, or an explicit not-stocked flag. | Closes #6. |
| UOM conversions added for box/spool/length items | Each item bought and stocked in different units carries a conversion factor, and receipts convert to stock units automatically: a box of 100 lands as 100 each. | Closes #5. Addresses #11: a box keyed as each can no longer distort a balance. |
| Generic item codes restricted | NONSTOCK, MISC and SHOPSUPPLY cannot be used on a purchase line for anything that exists in the item master; the buyer is prompted to search the master first, and a generic code needs a one-line justification. | Closes #14. |
| Negative on-hand blocked | An issue that would take a balance below zero is rejected at entry, so a missing receipt, a wrong item or a keyed quantity is caught the moment it happens rather than at the annual count. | Addresses #9, #10 and #11: each is caught at entry instead of at the count. |
| Individual logins issued for floor and receiving | The shared ASSY1, FAB1 and RECV logins are retired and every floor and receiving user has their own, so each transaction names who posted it. | Addresses #10, #12 and #15: every posting is attributable to a person, and so trainable. |
| Part-creation approval routing enabled | A request to create an item routes to the item-master owner, who checks for an existing record before approving. | Addresses #4 and #6: a new duplicate or blank-field item is stopped before it enters the master. |
The second group is process changes that will require ongoing ownership and organizational alignment. That makes this category the harder lift. The shop has committed to the owners and cadences below, and keeping them is what protects the results in Section 3.
Changes Requiring Ongoing Processes and Ownership
| Change | What it does | Impact | Owner | Cadence |
|---|---|---|---|---|
| A named owner for the item and supplier masters | The purchasing manager owns both masters. No item or supplier record is created, merged or deactivated without her approval, and she clears the approval queue weekly. | Addresses #1, #4, #6 and #8: a new dead record, duplicate, blank-field item or supplier alias is stopped at the approval queue. | Purchasing manager | Weekly queue; continuous |
| A cycle-count program on the ABC schedule | A items counted monthly, B items quarterly, C items annually, unreliable items first. Variances are posted with a reason and investigated above a threshold, and the annual physical is retired. | Addresses #9 and #13: unrecorded consumption is caught within the count cycle rather than once a year, and every correction carries its cause. | Stockroom lead | Monthly / quarterly / annual by class |
| A monthly parameter refresh from the forecast | Lead times are recomputed from the last twelve months of receipts, and reorder points and safety stocks from the forecast at the ABC service level, then loaded. The buyers review the A-class changes before they go live. | Addresses #2 and #3: a lead time or reorder point can be no more than a month out of date. | Purchasing manager, with the buyers | Monthly |
| A monthly open-document review | Open PO lines older than twice the supplier's lead time and jobs past their due date are listed, then closed or chased. | Addresses #16: no line or job stays open unnoticed for more than a month, so on-order stays honest. | Buyer (PO lines); production scheduler (jobs) | Monthly |
| A quarterly dead-item review | Items with no issue or receipt in 24 months are listed each quarter and deactivated unless the service parts coordinator or the production lead keeps them, with a reason recorded. | Closes #1: a dead record cannot stay active past the next quarterly review. | Purchasing manager | Quarterly |
| A BOM review for every new product and option | Engineering signs off a complete bill, including hardware, fittings, consumables and finishing, before a product is released, and floor observation feeds corrections back. | Closes #7 for new products. Addresses #9: a complete bill leaves nothing to be consumed unrecorded. | Engineering manager | Per product release |
| Same-day posting of receipts and job completions | Receiving posts each delivery the day it arrives and job completions are reported daily, with supervisors spot-checking timeliness. | Closes #15. | Stockroom lead; assembly supervisor | Daily |
| The data-quality measures tracked monthly | The tests from this audit are rerun each month and the key shares (adjustments, blank reason codes, free-text lines, open documents, duplicates created) are tracked against targets and reviewed. | Addresses all sixteen: any error that returns is caught before it compounds. | ERP administrator; reviewed by the operations manager | Monthly |