There are nine essential steps involved in cleaning up a vendor master file. These steps include several actions that should occur at vendor onboarding but may not have happened when someone first added many vendors to the master file. Execution of each of these steps will deactivate old vendors and validate and verify current vendors, providing greater peace of mind regarding security and regulatory compliance.
Here are the nine steps.
Step 1: Find and Eliminate Duplicate Vendors
Finding and removing duplicate vendors is critical to vendor master file maintenance. You can use different techniques to find duplicates, including sorting by tax ID, legal vendor name or address. You can also use Excel or Google add-ins incorporating fuzzy matches and other advanced features.
Not every match is a duplicate. Some vendors have multiple payment sites, shipping locations or other reasons for several entries. Be aware of parent-child vendor file entries; research and understand each vendor’s particulars.
Once you identify duplicate vendors, additional research will help determine which record to keep and which to inactivate. Consider history as well as open activity, such as unpaid invoices or open purchase orders, in making the decision. Put a note in the active record referring to the inactivated duplicate, including the vendor number.
Step 2: Inactivate Vendors with No Activity
Eventually, your company no longer uses some vendors; you should mark them inactive. To find and inactivate those vendors, pull a vendor report. If a vendor has not been active for a determined period, for example, 24 months, then the vendor’s information should be considered stale.
Pull all vendor information required to validate the vendor, such as a legal name, tax ID and address. Also include sections of the vendor record with invoice or payment activity tied to them, such as the address and location sections. This information typically comprises the vendor ID number, legal name and tax ID, the address ID and all address fields, the location ID and all locations.
Next, pull transaction activity, including the last activity date or open and pending statuses. Activities include invoices, payments, purchase orders, and vendor record updates.
After pulling the reports, the next step is to merge the information into one consolidated working file, matching each vendor’s vendor ID with their address, location, and all activity. Now you are ready to review. For each vendor ID, address, and location ID, you want to check the last activity date of each or whether an item is pending, such as an invoice, or open, such as with a purchase order.
Mark for inactivation all those not open or pending and have a last-activity date before the inactivity period. For example, suppose your inactivity period is 24 months, and today’s date is November 1st. In that case, you want to inactivate all vendors whose last activity date was before November 1st, two years ago.While this process is manual for many, there are some automation options. Some accounting systems or ERPs can be customized to look at the PO invoice payment or vendor record activity to see if a vendor should be inactivated, handling this step for you. Parts of the process may be automated using robotics process automation or RPA.
Step 3: IRS TIN Match
Vendors may not always notify you when they have a legal name or tax ID change. It is good practice to proactively identify changes before the 1099 season when you must distribute 1099s or 1042s and send the related tax filing to the IRS.
Use the IRS bulk TIN match process. You can upload a batch file to validate that the vendor’s legal name and tax ID in your master file still match. The IRS indicates that processing a batch file should take 48 hours. This re-validation process helps to reduce IRS CP-2100 notices that trigger the B-notice process because you are catching mismatches in advance.
You can also use a third-party service, such as VendorInfo, to automatically perform the IRS TIN match and additional vendor validations.
Step 4: Validate Tax Registration Numbers for Foreign Vendors
In this step, you want to validate foreign tax registration numbers for vendors in other countries. You might need to collect non-US tax registration numbers for teams outside vendor maintenance or accounts payable since they may use them to reclaim taxes paid on invoices. Examples of such foreign tax ID numbers are GST (in countries such as Canada and India) and VAT (European and other countries) numbers.To validate the value-added tax or VAT number for EU vendors or countries with VAT, use the VAT information exchange system or “VIES.” Validate the goods and services tax or GST for vendors in India using the government website for GST and the GST/HST site for Canada. Validate other countries based on the applicable tax business or individual registration number valid for that vendor’s country.
Step 5: Address Validation and Standardization
By validating and standardizing addresses, you ensure your mail to vendors will reach them successfully. You don’t want check payments, 1099s or 1042s that you mail out to return to you, necessitating rework.
Standardizing addresses also assists in uncovering duplicates. If you are mailing from the US to a US or non-US vendor address, use the United States Postal Services USPS zip code lookup tool to validate US addresses individually. Use the postal addressing standards, publication 28, to identify standardizations for US addresses and formats for international addresses.
If you are mailing from outside the US to a US or non-US vendor address, use the Universal Postal Union or UPU to validate US and non-US addresses. You can also inquire if their EDI process is an option for your IT team to configure to automate this process. There are third-party services, such as VendorInfo, that can provide validation for vendor addresses.
Step 6: Validate Sanction and Exclusion List Compliance
Use the vendor’s legal name to verify that the vendor has not been added to any watchlist applicable to your company. Sanction screening is an essential step of this process because vendors may have been subsequently added to a sanction or exclusion list since you onboarded or last validated them. Paying vendors on these lists may result in civil or criminal penalties for your company.
Here are three significant U.S. sanction and exclusion lists. Be sure to add any additional that may apply to your organization:
- Office of Foreign Assets and Control (OFAC) of the U.S. Treasury Department
- The System of Award Management or SAM/Excluded Parties List System (EPLS)
- The List of Excluded Individuals and Entities or LEIE (Healthcare-related)
Note: Some third-party services, such as VendorInfo, automatically screen your vendors daily against multiple sanction and exclusion lists.
Step 7: Validate Bank Details
In this step, you want to validate bank details, including account ownership, ABA routing numbers for US and Canadian banks and Business Identifier Code or BIC for non-US countries that do not use IBANs. Account number verification alone does not provide the protection you need. Ownership confirmation is a critical piece. Note, too, that routing numbers can change even when the bank account does not; and an incorrect routing number in an ACH incurs returns and eventually fines from NACHA.
Step 8: Verify Insurance
This step is optional, but some organizations and industries require it. You want to verify if the vendor has the appropriate insurance coverage, such as general liability or workers’ compensation, to perform services for your company. You may also want to ensure the insurance is current and not expired.
Step 9: Verify Other Information
The final step is to verify any additional information required by your organization, industry, or regulatory requirements. This step may include verifying if the vendor has the appropriate certifications, such as ISO or SOC, or ensuring that the vendor complies with environmental or social responsibility requirements.
Maintaining a clean vendor master file is essential for effective financial management and fraud risk reduction. By following the nine steps outlined in this lesson, you can ensure that your vendor master file is accurate and up to date. It is important to remember that this is an ongoing process you should perform regularly to keep your vendor data clean.
Consider your company’s unique needs and processes when determining the inactivity period for vendors and how often to perform vendor master file cleanup. Factors such as vendor types, industry regulations, and internal policies can all impact these decisions.Ultimately, seek leadership and audit approval before implementing new processes to ensure your vendor master file maintenance is effective and compliant. By cleaning and maintaining your vendor master file, you can reduce the risk of fraud, improve financial management, and ensure that you are operating efficiently and effectively.