Risk-Scoring For Journal Entries Using Data Analytics

Even though some automated postings can be generated on holidays, it's worth examining such instances to ensure that you're aware of them and their impact.
In Arbutus Analyzer, you can create a file of holidays, join the journal entry file to the holiday file using the dates as the key fields, and assign a value of 1 if the entry was posted on a holiday.

The second test is to review weekend postings. This is straightforward in many applications where you can build a conditional computed field using the Weekend function to test the dates. Weekends and holidays are of particular significance if you're reviewing manual journal entries.

Keyword searches of comment or description fields are the third test. You can create a keyword list in Notepad, including not only standard keywords but also those that are particular to your organization and your industry. A conditional computed field using the ListFind function will identify postings where any keyword is detected in the text fields. The condition expression assuming "JE Keywords.txt" is the keyword file and the field to be searched is the Comment field, will look like this:

ListFind("JE Keywords.txt",Comment)

Duplicates or fuzzy duplicates are popular tests. Looking for the same user, same account, same debit/credit indicator, same amount within 14 days of each other will exclude bi-weekly recurring transactions. Reducing false positives is a good way to make the reviews more efficient. You can run the Duplicates command with the appropriate parameters to send the results to a new file. You then join that file back to the master file and create a binary score of 1 if the journal entry appears in both lists.

• Classify on the GL account field to a new file.
• In the new file, run the Statistics and Stratify commands on the Count field to determine what your threshold, in terms of the number of transactions, should be.
•  Extract the records filtering on the Count field to isolate only the low-number accounts.
• In the JE file, create an All-Primary Join to the file created in step 3, using the account numbers as the key fields.
• Create a conditional computed field to risk-score the journal entries.

There shouldn't be any postings to top-line revenue accounts. To identify them, use the top-line revenue account number as the key field in the formula. Assuming the top-line revenue account number is "0000400000" and your materiality threshold is 2 standard deviations above the average, your condition expression would be:

GL_Account = "0000400000" AND Amount_in_local_currency > average + 2 standard deviations

Similarly, we can also look at large credits to other revenue accounts. Assuming all income statement revenue accounts begin with "00004", your condition expression would filter for those accounts and would exclude the top-line revenue account:

GL_Account = "00004" AND GL_Account <> "0000400000" AND Amount_in_local_currency > average + 2 standard deviations

This test involves identifying postings where the amounts are more than 2 standard deviations above the population average. We can run the Statistics command to generate the average and the standard deviation. The next step is to build a conditional computed field that scores the posting if it is above our threshold.

The condition expression would be:

Amount_in_local_Currency > 4634.26 + (2 * 26570.21)

• Run the Summarize command by account to a new file including the mean and standard deviation for the amount field. This can be done by selecting the amount field twice and changing the Type values to "AVG" and "STDDEV". The output will contain one row per account with the mean and the standard deviation for each account.
• In the new file, calculate the 2 standard deviations threshold for each account.
• Join to JE file based on account number and include threshold amount.
•  Risk score = 1 if amount is in excess of the threshold for the account.

Summarize: Click "Choose" in the "Fields to process" section.

You can use the same process that were used in identifying seldom-used accounts.

Round amounts are not common for naturally-occurring transactions and thus may be higher-risk than other amounts. There are many cases of fraudsters using round amounts in their schemes, sometimes repeatedly.

You might want to establish a materiality threshold so that you would be considering the size of the posting. Let's use $10,000 currency units as our threshold when we build our condition using the Modulus function. This function calculates the remainder after the amount is divided by the $10,000 threshold:

MOD(Amount_in_local_currency,10000) = 0

Unauthorized users are rare, but they've been known to circumvent controls in order to post entries. Obtain a file of authorized users and join it to the transaction file based on the user field. Any unmatched records will indicate that the user was not in the authorized users file.

Manual journal entries can sometimes override system controls, and they're not automatically generated, so it's a good idea to review them as well. Identify the field and the value(s) that indicate a manual entry and create the condition expression accordingly. In our example, the field is "Document_Type" and the two indicators of manual entries are the codes KG and ZH:

MATCH(Document_Type,"KG","ZH")

Postings around the end of the period are also suspect. Many organizations have been known to post amounts to sales in the waning days of a period, and then reverse those transactions immediately after the beginning of the next period. This inflates sales figures and bonuses, and it paints a rosier picture of the firm's financial state. The impact is also felt in the stock price.

If we establish the five days before and five days after the period end of March 31, 2020 to be relevant, our condition expression would look like this:

BETWEEN(ABS(Posting_Date - `20200331`),0,5)

As auditors, we're all familiar with the old scheme whereby someone doesn't want to bother their direct manager for amounts above their limits. They take it upon themselves to split up the large transactions into two transactions, for instance, each of which is below the individual's approval limit. The test can either be a fixed dollar amount or a percentage below their limits.

• Obtain a file of user limits.
• Join the transactions to this file based on the user as the key field. This will place each user's limit adjacent to each journal entry.
• Create the scoring field using the following condition for transactions within $100 below each user's limit:

BETWEEN(Limit_in_Local_Currency - Amount_in_local_currency,.01, 100)