Clean-up Master Data, preserve Transactional Integrity

Situation

One of the largest Entertainment/ Theme Park Companies in the world, implemented a tier one ERP solution to automate Financial and Supply Chain functions. During the course of time, the master data records’ correctness was affected by a number of data quality issues in the following areas:

  • Customer Master
  • Item Master & Product List
  • Vendor Master

Master Data Challenges

Abbreviations Used – (UOM: Unit of Measure; LB: Pound, KG: Kilogram, ml: milliliters)

# Sample Problems Identified Key Business Issues Quartiz’s Resolution Action
1 Duplicate Master Records Pricing errors, Delayed payments, Incorrect shipping address Tool sets were used to identify duplicate records based on matching of various fields such as address, phone number, EIN, DUNS, descriptions, contact persons, etc.
2 Incorrect customer categorization Inadequate revenue categorization, Not-so-effective effective marketing campaigns, incorrect pricing calculations, revenue leakage Customer Group names were more effectively and meaningfully rearranged. Records were reassigned the new (correct, or target group name for revenue, pricing, marketing group types)
3 Hierarchy relationships between Sold To, Ship To, and Bill To were incorrect Bill consolidation issues, payment delays due to incorrect Remit customer, ineffective collection efforts, lack of responsible party information Tools set were used to identify Duplicate or Inaccurate relationships, which were then identified by Business User team, and later updated in ERP system.
4 Incorrect base currency was attached to customer master records Incorrect bill amounts, gain/loss calculation errors Based on Corporate/Bill To customer relations, the incorrect currency associations were identified and corrected in ERP system
5 UOM of ‘Each’ was attached to food items such as Steaks, Lamb Chops, etc Lack of definition created transactional integrity problems. International locations considered EA as KG, whereas the intention was to use LB Correct UOM (such as KG, LBS)  was reassigned at the Business Unit level to the Item ID to transact in more meaningful weight-specific terms
6 UOM of Bottle, without the volume dimension was attached to items, Soft and Alcoholic drinks Many bottles came in volume units of 750ml, 1 liter, 2 liter, etc. and recording consumptions became inaccurate. Correct UOM (such as B750ml, Bottle1L, Bottle2L) at the Master Table level was reassigned to the Item ID to transact in volume accurate terms
7 Case was attached an an alternate UOM to items such as  Soft and Alcoholic drinks, with one standard conversion ratio (to Bottle) of 1:24 Items came in cases of 12, 36, 50, 100, etc. The generic definition of case was inadequate for receipt and consumption transactions recording Correct UOM (such as CS12, CS24, CS36) at the Master Table level was reassigned to the Item ID to transact in quantity accurate terms
8 Duplicate vendor records Resulted in duplicate/ over payments against deliveries, Lost volume discount opportunities, and ineffective fraud prevention Tool sets were used to identify duplicate records based on matching of various fields such as address, phone number, EIN, DUNS, descriptions, contact persons, etc.
9 Incorrect Vendor Item ID to ERP Product Item ID linking Inappropriate Item Kit explosion, Incorrect availability forecast of items, wrong items on PO Tools were used to identify Duplicate or Inaccurate relationships, which were then vetted by Business User team, and later updated in ERP system.

Transactional Data Integrity

Once the Master Data Correction changes were identified, all transactional data tables/records were identified within the ERP system to maintain data integrity. For example, the Item_ID field was present in 135 ERP transactional tables across Sales and Distribution tables. These tables were identified by our consultants using field referential checks. An analysis/impact of correcting these field in transactional table was done keenly, and more importantly with key user involvement.

Other Observations/Risk factors Identified:

  1. Financial impact was minimal on customer master data and vendor master data changes
  2. Significant correction entries had to be entered into General Ledger for master UOM changes that resulted in revaluing inventory, requiring journal adjustments for transactions such as receipts, adjustments, transfers, and consumptions
  3. Customer ID updates required changes in Order Entry, Pricing Rules tables, Billing and Accounts Receivables tables

Technical Approach

  1. Freeze transactions prior to Master Item tables and Business Unit Item tables update
  2. Update will be performed via SQL script, directly on the database
  3. Identified a current Field Name and Target Field Name to perform SQL updates

Results/ Benefits

  1. Customer Master clean-up resulted offering group/discounted pricing, better & efficient marketing campaigns, effective segmentation, better analysis of predictive models, Better Inventory levelling, transaction recording, reduced carrying cost resulted in better customer satisfaction levels
  2. Reduced labor efforts due to correct transaction recording – price adjustments on orders, manual inventory quantity corrections, etc. were labor intensive
  3. Vendor ID changes and analysis resulted in effective fraud prevention