How to Detect Duplicate Vendors While Auditing the Vendor Master File

Contract implementation is an integral part of the strategic sourcing process. An important aspect of contract implementation is conducting periodic audits, to make sure all data is accurate and all components of the project are in sync. In this article Source One’s partner Cash Recovery Partners LLC discusses components of a Vendor Master File audit.

Since duplicate vendors are the leading cause of duplicate payments, it is important to make an analysis of the vendor master file a top priority. Further, reducing any duplicate vendors can create greater efficiencies for the payables operation and help reduce the risk of fraud.

While some systems have built-in controls to prevent a duplicate name, address, or tax ID number, errors can still slip through that can cause the duplicate vendor scenario. Essentially, while controls may exist and are useful, many times they are bent slightly in order to benefit efficiency (for example, to expedite a rush payment).

Duplicate vendors may appear in different ways:

  • The same vendor may show up as two different entries in the vendor master file if a different name or spelling of the name is used. Supplier organizations often have several names that they are known by, including parent /child relationships and acronyms that represent the name. For example, Acme Business Systems may also be known as ABS Inc.
  • Errors in vendor address can also be the cause of a duplicate vendor. Different vendor locations, shipping versus billing addresses, and change of address are examples of address differences.

There are several ways to uncover duplicate vendors. The best fields in the database for detection include comparisons of (1) name, (1) address, (3) phone, (4) social security / tax id numbers, and/or 5) alternate shipping/billing address. Tools that detect a “fuzzy” match are also helpful in identifying fields that are similar, but not exactly the same. Acme, Inc. and Acme Incorporated would be flagged as a fuzzy duplicate vendor, as an example. Another useful search is to identify all vendors that are two or three initials (IBM, ATT, etc) or have standard words that tend to be abbreviated (Inc, Co., etc.)

A good way to resolve vendor duplication is to first clean your masterfile completely as of a certain date, and then work to prevent it from occurring in the future. Utilizing system controls that flag duplicate fields when entering new vendors can go a long way to avoiding duplication. Another is to complete a duplicate review of each vendor against the current database (continuous check of the one record to the entire data file) before the vendor is entered, possibly for a second time.

Microsoft Excel is sufficient for a basic cleanup, especially the later versions which allow up to a million rows of data. An inexpensive add-on product, ActiveData[TM] for Excel and Office, will make searches for duplicates and other common audit tasks easier. There are also standalone direct and fuzzy matching tools available from other consultants that may be better suited to your specific challenges.

Richard B. Lanza, CPA, CFE

President – Cash Recovery Partners, LLC

Rich has two decades of experience in audit technology and recovery auditing, becoming a leading authority in these areas. Rich is the author of 13 publications with his most recent, Cost Recovery: Turning Your Accounts Payable Department Into a Profit Center for John Wiley & Sons. He has worked for companies ranging in size from $30 million to $30 billion, and in all he has helped them save money through the use of technology and cost recovery initiatives.

Share this