Excel VBA got me to London, Hong Kong and a great job!
In my new life as a consultant, occasionally a client will ask me to do some Technical Auditing, which is always a welcome diversion from writing code. One such task was validating payment to a subcontractor. I learned the payments (a couple hundred grand every month) were all calculated in a Microsoft Access Database. I naively thought “cool beans” I love Access. I met with the manager, did a cursory review of the databases(2) and learned of the Perfect Storm:
♦ The back up person had been in an accident and their return to work date was unknown (no fooling).
♦ Joe had been lured back a couple days a month to run the reports and contact to him was limited to talking to the manager.
♦ The subcontractors had been paid estimated payments because no one knew how to run the reports.
♦ Documentation was non existent.
I was brought in at the end of their review. So I needed to get it done very quickly.
As I recall, there were 30 or more sub queries to run. The trouble was the databases had lots of extraneous tables and queries left over from development and testing and the naming conventions were not consistent. The developer had simply failed to clean up. Put kindly it wasn’t all it could be. After many hours of effort I was able to recreate a significant portion of the payment calculations without any exceptions. I simply ran out of time. The access databases created the correct results but no one knew how they worked. I would have loved to string those queries together with some VBA into a coherent, tight, well annotated one click application. The trouble is your auditor can’t be your developer so I turned my results over to the client and moved on.
I’ve given a good deal of thought into this episode in my now occasional auditing career and although it posed quite a challenge it was a case study in what happens when you have the “perfect storm”. Without being critical here is my list of controls that were not in place.
Key Person Dependency
The day good old Joe retired there was only one person who knew how to run those reports
Failure to properly Supervise the report creation
– No detailed documentation, very limited annotation in the work
– Even without vba to string it all together the process could have been made simpler by the use of a consistent naming convention.
– Elimination of all unneeded tables and queries
– because the report had grown organically and was built incrementally it was a bit of a patchwork.
Things happen – people get sick, have accidents, retire and just move on. The question is are you prepared for the perfect storm?
If you would like to say hello and Buy me a cup of coffee please follow the link.
