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:
Required | Nice to have for context/other analysis |
Card Number Cardholder Name Purchase Amount (Functional Currency, FC) Purchase Amount (Original Currency, LC) Purchase Date |
Merchant Name Merchant Country Merchant Address Merchant State Cost Center (Business Unit/Division/Department) Purchase Currency Purchase Description Purchase MCC/SIC |
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)