Top 25 Analytic Test for Every Organization: Test 3 GL – Journal Entries with Outlier amounts

2013-05-01

Top 25 Analytic Tests for Every Organisation: Test 3 GL Journal Entries with Outlier Amounts

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

TEST STEPS:

1. Obtain a dump of all the journal entries from within the audit period you want to review.

   – 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.

2. Import the Journal Entry data into ACL Analytics using the import wizard.
3. Calculate the average posted amount for each GL account:

   – Summarize on GL Account, subtotaling on Posted Amount
   – Define a computed field for Account Average Posted Amount as sum of Posted Amount/COUNT.

4. Calculate the standard deviation of posted amounts for each GL account:

   – 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.

5. Identify journal entries that deviate from the average posted amount by more than 2 standard deviations.

   – 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).

6. Alternatively, here’s an ACL Script pre-built to do all of this. Just open your journal entry table and run it!

Find out more: ACL Blog