Messy buildup in your vendor master file? It’s time for some spring cleaning!

May 18, 2022

Spring has sprung, and it's time to start cleaning up your financial records from years' past! Why not tackle the messy buildup in your vendor master file? This database file containing information about your government's vendors needs a periodic scrubbing.

Governments use vendor master files to issue purchase orders to vendors and pay invoices, and it can grow over time. It may include vendor records that you haven't used for a while and duplicate vendor records. This can increase your risk for fraudulent payments or paying vendors twice.

Cleaning your vendor master file will require a well-coordinated plan. It's likely that the project could span several months. To get you started, we've compiled some easy steps to follow:

Understand your technology tools

Make sure you understand your software options before you embark on this project. First, you will need to answer these questions: how do I inactivate vendors that have payment histories? Will my software allow me to merge two vendor records into one? Does my software have other features that could facilitate cleanup and organization? For example, some software has a parent-child relationship feature so that you can have one primary vendor record for your government and subrecords for each different vendor payment address.

What's in a name?

A lot, especially when it comes to your vendor master file! Ensure your government has an approved naming convention that you can apply to your vendor master file to keep it organized and consistent. The naming convention should address how to name vendors that start with “the,” as well as capitalization, punctuation and abbreviation differences. Having a naming convention will help you decide which vendor records to keep and how to go about cleaning them up.

Start with the low hanging fruit

Start with the easiest tasks first. Purge any payment history beyond your required records retention period. Next, purge all vendors with no payment histories so they don't clutter your database.

Then, inactivate vendors that you haven't used for a while (these probably cannot be purged yet because they are tied to payment history you must keep). The goal here is to prevent these vendors from being used for fraudulent purposes or to make sure they aren't used because they have gone bankrupt or are no longer authorized suppliers. Many consider a vendor inactive after one or two years of no activity.

Target and tackle the duplicates

It's critical that you identify and address all duplicate vendors. You might extract the vendor master records into an excel file so that you can easily sort and filter them and make notations. You can search for duplicates by looking for matching addresses or taxpayer identification numbers (TIN). Then, inactivate any duplicates that you find. If your system will not allow you to inactivate vendor records, consider adding “do not use” to the vendor name as a reminder to accounts payable to not use the record. That way, eventually you will be able to purge that vendor once you no longer have to keep the associated payment history.

What's missing? What might be wrong?

Run a report or query to see if any key vendor information like TINs, addresses, and email addresses is missing. Develop a plan to obtain the information you need. You can find the TIN on the W-9 form that the vendor gives you. Make sure you have a completed W-9 for each vendor and the TIN has been entered into your system. You can use the IRS's TIN matching service to verify the information for a single vendor or for all your vendors in bulk.

Lock down the vendor master file so it stays clean

You should restrict the ability to edit your vendor master records. Limit this activity to one or two people. Ideally, they should not also be responsible for processing invoices; this is a conflicting duty under the segregation of duties principle and increases your fraud risk.

Look to the future

As you move forward, add new vendors appropriately. You might consider creating policies and procedures, as well as a checklist to help guide your responsible employee through the process. You'll also want to plan to revisit your vendor master file in the future because you'll need to remove or inactivate those unused vendors that inevitably build up over time.

We hope these steps help you plan for your own cleanup project! It may seem time consuming, but you will reap the benefits for years to come.

Resources that might help you improve your accounts payable processes

Remember, SAO is here to help you. If you have specific technical accounting questions, submit them using our HelpDesk in the client portal. We also have financial management specialists at the Center available to talk with you about best practices, resources or internal controls. For assistance, reach out to the Center@sao.wa.gov.