Carrtegra Cleans Up Disorganized Vendor Master File with IDEA

Carrtegra Cleans Up Disorganized Master Vendor File with IDEA

Success Stories & Testimonials

About

Challenge

Carrtegra, LLC was approached by a leading academic medical system to help identify duplicates within a vendor master file with nearly 130,000 records. Controls for adding new vendors into the client’s ERP system were often bypassed or ignored, resulting in thousands of duplicate records and causing issues when preparing tax forms or reconciling accounts. The client attempted to resolve the duplication issue by making the Tax ID mandatory, however the field allowed for blank values, so many vendors were created without Tax IDs.

Highlights:

Vendor master file with nearly 130,000 records riddled with duplicates and inaccuracies
IDEA used to identify duplicates based on a scoring system and missing information
New standard for naming and formatting vendor master data prevents future errors

ERP system contained more than 100,000 inactive vendors, many of which were duplicates of active vendors that required their records and history to be merged. Of the 30,000 active vendors, nearly 20,000 were not legitimate or duplicates.

Solution

Carrtegra implemented a multi-step process to clean up the vendor master file, including identifying duplicates, merging legitimate records with pertinent data and creating a process and controls to prevent the activation of vendors with incomplete information.

Using CaseWare IDEA®, Carrtegra evaluated duplicates based on key fields expected to be unique, such as Tax ID numbers, phone/fax numbers, and P.O. Box and zip code combinations. IDEA formulas were developed to distill the data to just numbers to help normalize the data to check each record for completeness and to develop a standard for naming and formatting vendor master data.

A point system was developed to rank each record based on the type and strength of the match, including a cutoff score for accepting or rejecting potential duplicates. Records that approached, but did not meet the cutoff were manually reviewed. Based on recent activity, a “Golden Record” was used to narrow the population. Each duplicate identified generated an individual file, which was aggregated using the join function in IDEA to create a single table with all the duplicate data for a particular vendor ID to help determine what type of information was duplicated. If here was no match, the file was considered its own Golden Record. If a duplicate record was located, the formula would check the type and compare the data value(s) related to the DUP_TYPE with all other records. Matches were added to the match list, and then the record was evaluated based on the scorecard using a series of tests to determine its value.

Organizations that do business with a high volume of vendors are often susceptible to risk and potential fraud if records are not managed properly. In a disorganized system, fraudsters can easily change information of dormant vendors and let the checks roll in. Vendor master files need to be regularly reviewed to proactively prevent fraud.

Sam Carr, CPA, CIA, CISA, CCEP, Managing Partner, Carrtegra, LLC

Results

Using the scoring process to rank each vendor file based on missing or mis-formatted data helped separate valid vendor records from duplicates or invalid entries. Remaining records were merged and formatted to comply with the vendor master naming convention. The project reduced the mixed records from approximately 130,000 to 20,655 active records. Records that were missing critical information, such as a valid Tax ID were deactivated, as were any records associated with the Golden Vendor other than the correct record.

Carrtegra provided the client with production versions of the IDEAscripts, and reference materials to ensure existing and new vendors are entered properly to prevent future errors and help reduce potential risk.

About Carrtegra, LLC

Carrtegra, LLC is a successful financial and accounting management consulting firm specializing in designing and reviewing business processes, evaluating controls and making critical recommendations that increase process efficiency and reduce risk. As an IDEA Integration Partner, Carrtegra provides creative and effective solutions using IDEA to evaluate and resolve complex challenges, investigate fraud and monitor processes for compliance. For more information, visit www.carrtegra.com.

view more success stories