A nightmare for any organization is discovering they have been the victim of a fictitious vendor scheme. In just the past several months, guilty pleas have been entered in two local cases (Cepia, Inc – think Zhu Zhu Pets – and Goodwill) with estimated exposures in the hundreds of thousands of dollars each.
The most basic step you can take to protect your organization from this nefarious activity is performing an employee/vendor address match project. With any luck an employee setting up a fictitious vendor might send checks to their home address or the address of a beneficiary or emergency contact. The good news is you do not need expensive tools such as IDEA or ACL to do these match ups. With a little savvy, Microsoft Excel can be turned into a powerful tool for automated fraud detection.
The Basics:
The key to a successful employee/vendor address match project is understanding address fields in the HR and Accounts Payable systems are ‘free form’. They are likely to contain abbreviations, special characters and even misspellings. For example, an Accounts Payable clerk will likely input an address into the Accounts Payable System slightly differently than an HR employee would into the HR system:
Example: 1200 Main Street vs. 1200 Main St.
To successfully match these addresses simply go through the three steps listed below to ‘clean’ them into a standardized form:
Step 1) Change all letters to uppercase
Step 2) Remove all spaces and special characters
Step 3) Shorten the address to its first 9 characters
The addresses above will now look like those below and they can now be reviewed for matches.
Example: 1200MAINST vs. 1200MAINST
Automating in Excel:
These steps are easy enough to perform manually with a handful of addresses. But what if you have hundreds of employees and vendors to match? Or thousands? This is where Microsoft Excel macros come in handy. Using Microsoft’s built in Visual Basic editor you can automate Excel to perform the three steps mentioned above on all of your addresses in a visually appealing way. Better than that, you can re-run the macro any time you want with new or updated addresses at the click of a button.
The screen shot below is a snapshot of an Excel macro I built to illustrate this point. The ‘Tests’ in the ‘Evaluation Area’ correlate to the three steps mentioned above. If an address match is found between an employee and vendor the macro will display a green check mark and list the full address and other details in the ‘Potential Matches’ area. If there is no match the macro displays a red ‘X’.
Your Next Steps:
Obviously, creating a macro like this takes a little bit of practice. The good news is everything you need to know can be self taught using online resources. I recommend doing an internet search on ‘Microsoft Excel macro tutorial’ to find resources and you tube video’s that can get you started. CLICK HERE to watch a video on how this excel macro works here. Good luck with your fraud detection projects!
The most basic step you can take to protect your organization from this nefarious activity is performing an employee/vendor address match project. With any luck an employee setting up a fictitious vendor might send checks to their home address or the address of a beneficiary or emergency contact. The good news is you do not need expensive tools such as IDEA or ACL to do these match ups. With a little savvy, Microsoft Excel can be turned into a powerful tool for automated fraud detection.
The Basics:
The key to a successful employee/vendor address match project is understanding address fields in the HR and Accounts Payable systems are ‘free form’. They are likely to contain abbreviations, special characters and even misspellings. For example, an Accounts Payable clerk will likely input an address into the Accounts Payable System slightly differently than an HR employee would into the HR system:
Example: 1200 Main Street vs. 1200 Main St.
To successfully match these addresses simply go through the three steps listed below to ‘clean’ them into a standardized form:
Step 1) Change all letters to uppercase
Step 2) Remove all spaces and special characters
Step 3) Shorten the address to its first 9 characters
The addresses above will now look like those below and they can now be reviewed for matches.
Example: 1200MAINST vs. 1200MAINST
Automating in Excel:
These steps are easy enough to perform manually with a handful of addresses. But what if you have hundreds of employees and vendors to match? Or thousands? This is where Microsoft Excel macros come in handy. Using Microsoft’s built in Visual Basic editor you can automate Excel to perform the three steps mentioned above on all of your addresses in a visually appealing way. Better than that, you can re-run the macro any time you want with new or updated addresses at the click of a button.
The screen shot below is a snapshot of an Excel macro I built to illustrate this point. The ‘Tests’ in the ‘Evaluation Area’ correlate to the three steps mentioned above. If an address match is found between an employee and vendor the macro will display a green check mark and list the full address and other details in the ‘Potential Matches’ area. If there is no match the macro displays a red ‘X’.
Your Next Steps:
Obviously, creating a macro like this takes a little bit of practice. The good news is everything you need to know can be self taught using online resources. I recommend doing an internet search on ‘Microsoft Excel macro tutorial’ to find resources and you tube video’s that can get you started. CLICK HERE to watch a video on how this excel macro works here. Good luck with your fraud detection projects!
RSS Feed
