Select journal entries (JEs) that deviate more than two standard deviations from the average posted amount to the account:
- 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]
- Calculate the standard deviation of posted amounts for each GL account:
- Relate the JE table with the summarized table from Step 1
- 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 JE table with the standard deviation table
- Identify JEs 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]
- * In Step 3, the “2” in the equation represents 2 standard deviations as the outlier threshold. This number can be adjusted according to your audit needs.