Vendor Master File Data Analytics

Vendor Master file controls are the foundation and the beginning point for the disbursement process controls. In this post, I’ll explain a bit based on my experience how Vendor Master File Data Analytics might help you in the audit.

  1. VMF scrubbing/cleaning not conducted in years

    • One of the first points (which is not exactly data analytics) I’m always looking is when the VMF was last time cleaned/evaluated. Most of the organizations I’ve seen require cleaning to be made every 1-2 years time to get rid off the useless/duplicate vendors etc. This is absolutely critical to make sure VMF is kept up-to-date and free of possibilities for make incorrect/unauthorized payments.
  2. Standard analytics – However be careful to make to quick conclusions!

    • Number duplicates: This is usually quite stupid check as 99.9999% of the ERPs are blocking this to happen. However, in that 0.00001% there is a system issue allowing this to happen and it might lead to more serious things overall with that system.
    • Name duplicates: With vendor names you can already find some duplicates, but problem comes with large suppliers with multiple branches/offices.
    • Address duplicates: This starts to be already somewhat relevant as, besides business parks etc.), you don’t usually have two companies in a same exact address
    • VAT/Registration number duplicates: Excluding large suppliers with multiple branches/offices, with duplicate VAT/Registration numbers you can identify potential duplicate vendors
    • Bank Account duplicates: If duplicates are found here, it’s usually quite a certain case that you’ve found a duplicate vendors
    • Last time a payment made: This is quite a good indication of how up-to-date vendor master file is kept
  3. Usually standard analytics does not bring too much light. You need to use data field combinations

    • As mentioned above, especially with large companies have multiple branches and most of the time you cannot purely look at the vendor name to identify duplicates. That’s where the data field combinations may bring some information to the plate.
      • I usually use at least a combination of Name – VAT number – Address 
        • If you still find duplicates with that combination and they have different bank accounts, ask the question: “Are these two vendors the same company, but with two different bank accounts? If yes, why is it twice in the VMF?”
          • The risk above is that the company is dividing the spend made to that vendor by using multiple vendors in the payment process.
  4. # of times changes made to a vendor in the past 1-2 years

    • At least from the big ERP -systems it’s possible to get a report out showing how many times vendor information has been changed. This gives a good indication for potential questionable actions.
  5. Dummy/test vendor check

  6. Lastly a couple of tips

    • Make sure to check only active vendors! I made this mistake a few times in the early years in the audit that my analytics also included blocked vendors (not in all the ERPs you can actually remove vendors)
    • If possible, extract the VMF yourself from the ERP. It has happened once or twice that the VMF provided by the company, was not 100% accurate. 😉
    • Take into account also payments made to vendors to see if questionable transactions have been made.
    • VMF is only the starting point, never ever assume that rest of the disbursement process is adequately controlled even if VMF is.
      • An example here is that payment file can be modified. This is the very often the case even with the major ERP providers. Often banks do not accept encrypted payment files. Be sure to check in in practice:
        • to where the payment file is extracted from the ERP
        • who can access it
        • in which format is it
        • what is the approval process in the eBanking system.
    • if there’s been recent change of ERP, make sure to check how company ensured that data was moved correctly from the old system to new.

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Note of the Day

  • Compose cells together with "&" in Excel

    For example: A1&"_"&B1 will merge content in those cells with "_" in between