Cleaning Up Your Vendor Master File: A Step By Step Approach

By Richard B. Lanza and Dean Brooks, Cash Recovery Partners, LLC

Any business that uses outside vendors or independent contractors needs to make sure that accurate records are kept — and kept up to date. Problems in the vendor master file can expose your organization to fraud or lead to wasteful duplicate payments.

Maintaining up-to-date W-9 forms and complying with Sarbanes-Oxley are both easier to do if the vendor master file process and file is periodically audited.

·    Start with controls. Is there proper segregation of duties? Are updates to the file formally tracked and approved before being made? Does each user maintain a separate password?  Are independent checks of the vendor performed before any add or change?

·   Now look for independent sources. Is each Taxpayer Identification Number (TIN) up to date? If not, for companies with large numbers of vendors, The IRS.GOV website can provide TINs. Have all vendors been checked against the U.S. Excluded Parties List? Again, obtain a copy of the list from EPLS.GOV. Have Social Security numbers been checked against the official Death List? These checks should be made at least once each year, if not more often. For small organizations, rather than pay for access to the Death List, it may be worthwhile to ask a consultant who already has it to include that test in other work.

·   Next consider what rules you have in place regarding general data integrity. How are vendors with multiple shipping and billing addresses handled? Does all data need to be entered into the system? A clear “data dictionary” should already exist explaining all required fields.

·   The final task, and perhaps the most open to creative inspiration, is to search for gaps, duplicates and other errors in the data. There are many good questions to ask here, such as: Do two different vendors share the same vendor number? Does one vendor have multiple vendor numbers? Does the same address, or TIN, show up for seemingly unrelated vendors? Are any of these problems showing up consistently for one data entry ID, and not others?

As part of the cleanup, be sure to plan for a review of recent invoice payments. Any vendor shown to be duplicated in the system or on a watch-list should be checked out for errors or other issues. This step may well make the whole audit profitable, by returning more in misplaced cash than the audit costs.

Microsoft Excel is sufficient for a basic cleanup exercise, especially the later versions which allow up to a million rows of data. An inexpensive add-on product, ActiveDataTM for Excel and Office, will make searches for duplicates and other common audit tasks easier, including the use of a fuzzy matching ability.  Consulting firms also can bring a wealth of knowledge on the best tests and methods to carry them out for full coverage.

Share this