EXCELlent Ones Consulting Skip to Content Skip to Search

Print | | Download PDF

Rooting Out Problems: An Analytical Approach

Problems are everywhere.  It is a natural part of life and an integral part of every profession.  Many problems if caught early are but a trickle of water, if it is allowed to linger and progress it becomes a tidal wave.  Quite often the individuals that are able to locate problems, provide and implement thoughtful solutions become prized in any organization.  However, many things can cause errors.  Perhaps an overnight process didn’t execute properly, journal entries may have been posted to the wrong account, the payroll G/L posting was delayed.  The issues can be numerous.  We should never assume that technology always works as intended.    In this data intensive world following through on these steps proves difficult.  We are not able to manually comb through volumes of data to locate issues.  Our only hope is to leverage technology for this process.  At one point in time I naively thought technology cured all errors.  Below is a loose idea of steps which can be taken to uncover data integrity issues.




Define Alert Criteria’s


Threshold Alert


In today’s work environment looking for problems with data is a lot like finding a needle in the proverbial haystack.  While this may seem like a daunting task there are ways to automate the process.  The first step is to define the problem.  Or to put it another way, what types of errors might occur? How would I be alerted about these errors?  Start by making a list of errors which has occurred with the data set in the past.  Next ask yourself how this issue could be brought to my attention through data analysis.  For example, for one of our recent projects we had to analyze payroll data to discover opportunities for a large institution.  One approach we used was to compare the budget by person to actual payout to discover large changes.  A list of large changes could then be used as a springboard to find errors in salary payments, mis-allocations etc.  Now this would be an exhaustive list for larger organizations but we developed an alert that filtered out normal variances.  Things like salary increases, promotions and one-time termination payouts were wanted excluded as normal business operations.  In general within this organization salary increases were below 5%, promotions rarely exceeded 10% of a employees salary and one-time payments were broken out by a specific code.  With this bit of information we were able to isolate anomalies beyond these typical business related activities. Threshold we used did this by isolating salaries that were 20% above the budget. This technique was able to attain this organization a significant savings.


Pattern Deviation


One of the simplest ways to surface problems in financial data is to isolate deviations from the typical pattern.  This maybe fairly simple to isolate manually with small data sets but as organizations grow it becomes increasingly more complex.  Below are a few techniques to begin analyzing data.  The first is called pattern analysis.  Compare the total transactions (revenue or expense) to a similar time period.  This could be a day over day comparison.  This could be Thursday vs. Friday, Saturday vs. last Saturday or this day vs. the same day last year.  The comparison could also month over month or year over year.  These comparisons will typically show some sort of variance or change.  If the change is significant the next step would be to determine the reason for the change.  For example if sales decreased by 15% year over year we need to determine the reason for the decline.  Perhaps as part of this analysis there was a large increase in the cost of goods sold which decreased the net revenue.  Or perhaps certain key products for sale were improperly discounted by clients.  This is a key component to rooting out problems.


Another method for rooting out problems is through the employment of statistical techniques.  A few techniques we use when employing analysis are random sampling, average, weighted average, frequency, high and low.  Random sampling is a technique used by many auditors to test the validity of transactions and confirm the integrity of a process.  This involves combing through a list of expense transactions, randomly selecting one then asking for back-up.  This technique is a bit manual but can be automated to some degree.  For example we developed a technique to automate the random sampling process shared on our blog post titled Cutting Audit Sampling Risk Down to Size. The next few statistical techniques can be used to determine if summary data is out of line.  For example, if the expense transaction in the month of May is $15K but the average expense transaction per month is $10K this should prompt investigation.  Why was there an additional expense of $5K?  Frequency is another statistical technique which can be used to discover issues with expenses.  For example a frequency analysis can be done on the number of expense transactions in a particular month.  Is the number of transactions trending upward?  This can be due to a number of issues for example perhaps charges meant for another division is showing up on your books; many correcting entries were made on the expense side which can be a symptom of inefficiency. The next technique is the high and low analysis.  Determine the highest and lowest transaction amount.  Compare this to the average and ask yourself does it make sense?  Statistics is an under used technique in many businesses which can yield tremendous benefits for both small and large organization alike.




Extract Detailed Data


In many cases summaries can lie.  Often summary analysis cannot truly expose problems because of the “noise”.  Noise is essentially interference that hides certain sounds.  Rather than applying analytical techniques at a summary level another approach would be to comb through the data itself.  Large corporations have various summary reports in order to bring to light issues.  However, these summary reports can “lie” at times.  Summary reports tend to mask and hide issues lurking in the details.  We’ve been exploring some techniques that would surface issues occurring at the transaction level.  Essentially, these techniques involve applying rules against financial transactions at the detail level.  Any transaction that breaks these rules will be sent to the user for further investigation.  While tools like QuickBooks and PeopleSoft maybe helpful in recording and displaying financial data, errors at tiems are difficult to root out. 



Build Systems & Processes



There are a number of techniques which can be employed to make this process seamless and automated rather than time consuming.  The first step in this process would be to get the transactional data.  Depending on the system this can be a manual download by the user in a CSV file or automated extraction using things like Macros/VBA or ODBC (Open Database Connectivity) connection.


The next step would be to decide how the results would be displayed.  This is largely driven by the type/ nature of the rules or alerts being applied.  For examples if a rule was setup to show a red flag when the transaction count exceeds 100 this can displayed in a summary dashboard.  Conversely if the rule is defined as all transactions that exceed $100K then a transaction level display would be ideal.  The display alert is essentially driven by the type of alert needed.



Define Actions to be Taken


Most people end the problem identification process at locating the problem.  However, this leaves a tremendous opportunity on the table.  After a problem is identified a solution must follow.  Rather than just identifying the problems define the actions that should be taken if a problem appears.  For example, any transaction over $100K sends an email to the Sr. Accountant for further investigation.  These actions can be defined ahead of time and executed automatically in the blink of an eye.


Problems are natural part of life but more often than not solutions are available.  Small problems grow into big ones.  However, applying analytical techniques can stifle the growth of small issues into monumental catastrophes.


Email: info@excellentones.com | P.O. Box 160493 | Brooklyn, NY 11216 | Phone: (347) 763-1035 | Fax: (718) 228-7859

All rights reserved. Copyright 2014 Excellent Ones Consulting LLC.