I know as an Auditor we sometimes struggle to quantify the value we are adding to the organization. One of the best ways to make that value a little more concrete is to show cash savings.
As an Auditor I have been able to identify close to $1 million dollars in duplicate payments. Some of the following techniques I’ve used to help identify duplicate payments.
First we’ll load our dataset:
We can visually see that these items look like duplicate invoices. However when you’re looking through hundreds of thousands of invoices you’ll need to identify them using analytics.
The main columns you’re going to want to look to see if they repeat are:
- Invoice Number
- Invoice Date
- Vendor Name
- Invoice Amount
These are really the main columns you’ll want to analyze.
It’s not often you will find this perfect of a match so there are some other modifications you can make to identify duplicate payments.
1. Set all invoice numbers to uppercase
The invoice processor may have used different case when they entered the invoice, so it wasn’t identified as a duplicate. We will set the invoice number to uppercase.
When we run the duplicate check again there are more results.
2. Strip all special characters from invoice numbers
Sometimes a processor will record the invoice number with a dash and the next person may use a different special character. If we want to pick up these duplicates, we’ll strip special characters from our invoice numbers and look for matches again.
We do use some regular expressions to ensure we replace everything except for letters, numbers, and spaces. When we run our duplicate check again, we see the next duplicate item.
3. Remove spaces before and after invoice numbers
The next modification we’ll do is to strip white space before or after our invoice number. Sometimes people will put a little space before or after the invoice that will make it so duplicate detection doesn’t work. We will strip our invoice numbers to remove extra spaces.
4. Remove extra spaces within the invoice number
The other item that is a little hard to see is an extra space within the text. We will replace spaces where there is more than one space with one space.
5. Identify if invoice matches within the month
The other thing I’ve seen happen is someone will process an invoice twice, but they may not put the same invoice date. However, they may put the date from the same month. We will create a column with the month then we’ll run our duplicate analysis on the month instead of invoice date
Hopefully this gets your juices flowing on how you can use analytics to help get cash back for your company.
