By Phil Lim
Last time we targeted potential outliers in a specific GL account. This time, we’ll look at broadening our reach by applying statistical analysis across the whole field of GL accounts!
To do this, we want to figure out what would be the typical sized posting to each account, and look for unusually sized postings. Now, some accounts will inherently have a large range of sized transactions posted to them, but some will have a very narrow range. We’ll take this into consideration when we identify our outliers.
Applying outlier analysis can reveal the transactions that are ‘unusual’. Instead of applying a hard threshold (there are probably many Journal Entries larger and smaller than $500K in your General Ledger), we can analyze the trends of all your accounts to highlight transactions that might require additional scrutiny. These transactions are unusual because their amounts deviate vastly from the expected amounts posted to the account.
Word of warning, the test steps do get a bit dicey, so a little background in statistics might help in understanding.
RISK: Posted entries may not be authorized or valid.
TEST: Select journal entries that deviate more than two standard deviations from the average posted amount to the account.
DEFINITIONS: (GL) Account: General Ledger Account that the journal entry is applied against (e.g. Accounts Payable, Payroll Expense, Fixed Assets – Property)
Posted Amount: Amount of the journal entry applied to the account
– Get it from someone in IT responsible for your ERP or core financial system.
– Get it in a delimited text file format, quicker to import into your analysis tool.
– Include all the key fields, but in particular things like description, date, amount, employee ID, account, etc.
– *Hulk smash* if IT gives you any trouble – getting the data for this test should actually be quite easy to do.
– Summarize on GL Account, subtotaling on Posted Amount
– Define a computed field for Account Average Posted Amount as sum of Posted Amount/COUNT.
– Relate the journal entry table with the summarized table from step 3
– Define a computed field for the Deviation of each transaction (difference between the Account Average Posted Amount and Posted Amount).
– Define a computed field for the Variance (Deviation of each transaction squared).
– Summarize on GL Account, subtotaling on Variance.
– Define a computed field for Account Standard Deviation Posted Amount (square root of sum of Variance/COUNT).
– Relate the journal entry table with the Standard Deviation table.
– Extract records where the ABS(Deviation) > Account Standard Deviation Posted Amount * 2.
– (The 2 represents 2 standard deviations as the outlier threshold. You can adjust this to taste).
Find out more: ACL Blog