A few weeks ago, I was working with a retailer client that had a problem calculating its unclaimed property liability balance on gift cards it had sold to customers.  The client sold luxury goods at its chain of stores across the U.S., and customers could purchase gift cards and use their balances toward the purchase of retailer’s goods at any one of its store locations.

Gift cards are unclaimed property and must be forfeited to the state of purchase when they go unused.  Retailers typically make a voluntary disclosure of their balance of unused cards and transfer the cash to each state.  These voluntary disclosures are subject to audit which, in the case of this retailer, was likely given the nature of its business and the volume of transactions it processed.

The wrinkle that arose on this project was that the client’s point of sale (POS) system which managed the issuance and redemption of gift cards generated unreliable data.  The system had been managed by multiple vendors over the years and upgraded piecemeal.  Though the POS provided gift card ID numbers, customer names and addresses, store locations, transaction dates and balances, the data were inconsistent: gift card ID numbers were reused at different stores, customers who bought the gifts cards gave them to other people to redeem them who subsequently registered as new customers, store locations changed, etc.  It was impossible for the auditor to rely upon a list of transactions for any individual gift card – a gift card purchased for $10,000 might go unused while four other gift cards with redemptions of $2,500 would appear in the system as each having a negative (-) $2,500 balance.

So, the audit question was – how could I match up the gift card purchase transactions (debits) with their redemptions (credits) using the data that I had.  I used a genetic algorithm.  A genetic algorithm is a search technique that finds solutions to “optimization” problems – i.e. the process of choosing the best fit given a set of alternatives.  For each debit transaction, I was tasked with finding the most likely credit transactions that corresponded to it. 

I wrote a program in C# to iterate through each debit transaction and find dangling credit transactions that, if netted together, would result in a $0.00 balance.  The criteria I used to find these dangling credits were based on the information I had in the dataset.  For instance, if I had a $10,000 gift card issued to a Mr. Smith in Morristown, NJ, I searched for any transaction involving customers who lived near Morristown, NJ and tried to find dangling credits that equaled $-10,000.  If I found these, I would net them together, and remove them from the data set so that they couldn’t be “found” in subsequent iterations. 

In doing this, I observed that the data set evolved as subsequent iterations occurred, and it became easier to find the dangling credits because the dataset shrank.  The result of this activity was a substantial ($1.2m) reduction in the amount of unclaimed gift cards which saved the client from having to hand this money over to the states.  The results subsequently survived at least two state audits because the matches of gift card issuances and redemptions were both plausible and supported by a logical algorithm.

This heuristics in the genetic algorithm was supplemented with address validation.  Because the customers’ addresses were validated against the US Postal Service database of valid addresses and were geo-coded (assigned their latitude and longitudinal coordinates) they were known to exist and the relationships between two addresses in the same town or county could be used as part of the genetic algorithm.

Therefore, this case is yet another reason why auditors need to make a genuine effort to understand information architecture, search algorithms and analytics.  The savings gained by creative use of a genetic algorithm and USPS address validation far surpassed the fee for consulting services this client paid.

• Previous Entry: Why auditors need flowcharts (an example)
• Next Entry: Cubicle 101: Should I add ‘MBA’ after my name?