Top 25 Tests for Every Organization Test 12, Purchasing Card Round Amounts

2014-03-01
Think of the epic superhero reboots of our time. Feel the anticipation of audiences as they return with great excitement after the hiatus. Perhaps the break was necessitated by audience fatigue, aging actors, or a shift in tastes. Nonetheless, this superhero is back. Bolder, braver, and more bad-ass than before.

Often in reboots, an old villain is revived and re-imagined. In this case, we’re bringing back a twist on T&E expenses, but this time we’re honing in on purchase card transactions. Just as old and new villains often share core traits, T&E expenses and purchase card transactions share many key risks.

One particular risk is the use of purchase cards to pay for gift cards or cash advances. While typically not prohibited outright, the purchase of gift cards or advances require additional scrutiny as they can be used for fraud or abuse. So how do we identify the round amounts that would be an indicator of gift card purchases?

We can use ACL’s Modulus function. This function cuts through numbers with a specified divisor and returns the remainder after the divisor has been divided out. For example, 78 modulus 25 is 3. Identifying round amounts is easy with this weapon; just look for those amounts which, after applying modulus, are zero.

Risk: Transactions with round amounts may be an indication of use for purchasing gift cards or cash advances.

Test: Identify transactions with amounts that are divisible by a specified divisor, totaling greater than a specified threshold for an employee.

Data Acquisition: If you’ve been a fan of this series, you’ll recall from Episode 4 that acquiring T&E data is most easily accomplished through tapping a standard feed from Concur. Purchase cards are similar.

For most organizations, purchase cards are outsourced to a major credit card provider. Each credit card provider has their own standard data file format that can be tapped into. Unfortunately, these files are very complex. Fortunately, ACL has scripts to read these files in.Regardless how you acquire your data for analysis, here’s some guidance on the required fields for this analysis:

Test Steps:

1. Obtain a dump of all the purchase card transactions for the investigation period. See above section on Data Acquisition
2. Import the data into ACL Analytics using the import wizard.
3. Ensure completeness by verifying the data against control totals (e.g. Total & count purchase card transactions in the file to tie to investigation period actuals).
4. First, we need to pick a divisor and use the modulus function to identify the round amounts. Let’s use 25: SET FILTER TO MOD(purchase_amount_LC, 25) = 0
5. Total each cardholder’s round amount transactions by summarizing on card number: SUMMARIZE ON Card_Number SUBTOTAL purchase_amount_LC OTHER ALL TO T_Emp_Round_Amount_Sum PRESORT
6. Now, identify and report any employees with total round amounts greater than a threshold (let’s use 1000 as our example): EXTRACT FIELDS ALL TO PCARD_Round_Amounts IF purchase_amount_LC > 1000
7. Repeat steps 4 through 7 for other divisors (e.g. 10, 20, etc.).

Conclusion:

The Modulus function in ACL is a superpower of mass destruction to identify gift card purchases and cash advances. It’s going to zap a lot, so it’s important to tame its power to identify the employees whose total round amounts are the highest. It’s the ability to combine broad strokes and needle precision so deftly that makes this new analytic superpower so effective.

(Source: ACL Blog)