Me and my big mouth had gotten my team into a bit of trouble.
Our new client had told us that their credit card reconciliation process – the one I had agreed to take over and had just finished for the first time – took no more than ten hours each month to complete. We were neck deep in a new contract, on a tight timeline, and our client had already lost their in-house support by the time I realized the truth: The process would take at least ten hours each week. The extra 30 hours a month were going to eat our profits.At the time, my team and I provided outsource back office services for travel agencies. We were good at it, too. We could generally adapt our well-tuned processes quickly and have a $5- to $10-million travel agency’s back-office systems relocated and running smoothly in as little as two weeks.
The pain point that drove our outsource business was ARC (Airlines Reporting Corporation) reporting. Most clients came to us because they were about to lose a long-time employee who almost invariably was the only person within 50 miles familiar with ARC reporting. Each one had figured out what we knew from long experience: It took a full year to develop sufficient expertise in that deceptively simple reporting process, where simple mistakes could cost hundreds of dollars with a single keystroke*.
WHY, IT’S MERE CHILD’S PLAY!
In that climate, taking over related I.S. processes like credit card reconciliations seemed like easy money. I was good with data and reporting, so I never expected much trouble with anything that involved a spreadsheet. Analyze source data and required outputs, create and document a repeatable procedure, hand it off to someone else on the team to perform, and move on to the next client. Easy-peasy.
My come-uppance was a series of four similar but distinct client-facing credit card reconciliations. Comparative data had to be downloaded manually from several client credit card provider websites, none of which used the same file spec. Required outputs were Excel-based reports, each heavily formatted and containing lots of summarized results.
Past reports were no help: Nothing was formula-driven, and there were no self-contained data points to differentiate between a dozen root causes for variance. It looked completely random. If there had been another program that created these complex outputs — and it was beginning to look that way — it was not available to me. I had to reverse engineer a process based on a picture of the end state.
PAIN IS A POWERFUL MOTIVATOR
The first run-through was completely, mind-numbingly manual. After a few false starts, I found I could reconcile 90% or more of all transactions through a series of concatenated field matches and an iterative process of elimination. (Pro tip: When first approaching a murky data problem, write down your steps and experiments along the way. Once you reach your goal, you will forget how you got there.). There was no getting around some manual lookups and deductive reasoning to finish the process, but a dozen orphaned transactions are easier to manage than hundreds.
At the time, I was an intermediate level Excel user. I was capable with formulas and formatting but I had no experience with two of Excel’s most powerful features: Macros and Pivot tables. 100 work hours later they were my new best friends.
Excel Macros are excellent for reducing definable, repetitive tasks to a couple of keystrokes. If you want to concatenate a few fields (for example, a document number, date, and dollar amount) a formula will work fine. But then what if you want to compare that result with concatenated fields on another worksheet, mark the matched records, move the matches to a separate workbook, and then try again with different fields on the remaining unmatched records? A macro can combine all of those steps into a few keystrokes. If you go a step further, you can add some VBA code and supercharge the process.
That process – working out step by step manual methods and then converting them to macros – is essentially what I worked on for 100 hours. I started by recording and playing back small macros, and then followed the Google-brick road into editing the VBA code behind the macros. I made a lot of mistakes along the way, and 40 hours in I had managed to somehow overwrite my file and lose all my progress! Even with that setback, it was all well worth it.
When I finished writing my macros – heavy into VBA by that time – I had reduced reconciliation time by 90%. My new process took less time per month than the old process took per week. We used those same macros for the next three years.
SO WHAT ABOUT THE $42,000?
Our blended, burdened labor rate was $30 per hour. We saved 9 hours per week, 52 weeks per year, for three years. $30 x 9 x 52 x 3 = $42,120. Even better: That macro spawned many others since. The dividends have been enormous.
* At that time (and possibly still today?), it was very easy to void a ticket during report reconciliation, which immediately notified the airline. Once voided, airlines often refused to reinstate tickets at the original fare. We accidentally voided a single international ticket that increased by more then $2,000. We had to eat the increase.