A cursory scan of any company's vendor master file will reveal a number of duplicate supplier records. While most companies perform a periodic purge of the vendor master file to clear out these duplicates, it is better to install controls to keep the duplicates from being created in the first place. Here are some possible controls:
- Match against the vendor identification code. For example, if you are checking to see if "Smith Brothers" is a match, then enter SMITH in the vendor code field and see what pops up. This is the most common search, but many duplicates still arise. The following additional controls tend to achieve better results.
- Match unique supplier information to existing records. To do so, extract from the supplier master file database at the end of each month the identification code, name, address, and tax identification number for every supplier, and convert the data into an Excel spreadsheet. There are then several options for matching information, using the Edit > Find command in Excel:
- Compare a new supplier's tax identification number to a listing of the same numbers for all existing suppliers. Since this is a unique number, it should result in an immediate match with duplicate records. This method only works if there is a strong procedure in place for obtaining tax identification numbers at the point of initial data entry.
- Compare the new supplier's name and/or address to the database. This works very well with the Excel Edit > Find command, since it can locate even a few letters of a name or address. For most companies, this approach results in the largest number of matches.
- Ask the supplier if it has ever done business with the company before, and if so, how long ago. If they reply in the affirmative, this is a large clue that a duplicate record exists somewhere (though it may have been purged if the preceding activity was several years in the past). This query can be part of the initial credit application.
Also, periodically review the duplication errors that still slip through the initial screening, and determine what additional screening technique would have found them.
While the controls noted here will certainly reduce the number of duplicate records, they also add time to the supplier record creation task. Thus, the accountant should evaluate the cost effectiveness of using either all or a subset of these controls. It is quite possible that only a single new duplication-avoidance control will result in the lowest overall cost.