top of page
Search
  • Derrick Wong (Managing Director)

Popular Analytics (4 of 6 series)


Many Internal Audit team needs to prove ROI if they intend to invest on Analytics technology. The best way is to achieve quick implementation of analytics use cases, and start testing it out on their data. When it comes to analytics use cases, we always share with our customers on 2 things:

1) Analytics is only limited to your own creativity.

2) No Two Organizations are Identical, But there will be commonly use analytics which many will be using the same tests.


So to achieve quick ROI on your analytics, we have come up with 6 areas of the common and popular analytics tests that you can consider. They are:

1) Employee Spending

2) Vendor Management

3) Technology

4) GL JE Risk Scoring

5) Duplicate Payments

6) Counterparty Validation & Outliers


Do follow us on our LinkedIn or subscribe to our newsletters for updates. We have discussed about Technology in Part 3 (url link) series. For part 4, we are going to discuss about Journal Entry Risk Scoring.


Manual journal entries are high-risk items because they are not part of an automated process. JEs that fall around the end-of-period dates are also of concern. You need to consider the areas:


2022 Holidays: Match(Posting_Date,`20220101`,20220120`,`20220217`,`20220625`,`20220703`,`20220907`,`20221012`,`20221111`,`20221126`,`20221225`)

Weekends: Match(CDOW(Posting_Date,3),"Sun","Sat")

Keywords in Description: ListFind("Keywords.txt")

Same Account, Same Account: Duplicates ON Account Amount OTHER ALL TO …

Seldom Used Accounts:

Classify ON Account TO Newfile_1 OPEN

Extract Account IF COUNT1 <= 3 TO Newfile_2

OPEN <source file>

OPEN Newfile_2 SECONDARY

Join PKEY Account FIELDS ALL SKEY Account WITH PRESORT SECSORT TO JEs_Seldom_Used_AC OPEN


Large Credits to Revenue 5 Days Prior to Period End

(Assumption: Revenue Accounts Begin With "4")

Extract RECORD IF Account = "4" AND Amount_CR <> 0 TO JE_Rev_Accts OPEN

Statistics ON ABS(Amount_CR) STD Number 5

Extract RECORD IF ABS(Amount) > AVERAGE1 + (2* STDDEV1) TO Large_CR_Rev_Accts


Large Credits to Income Statement Non Revenue Accounts

(Assumption: Target accounts begin with "5","6", or "7")

Extract RECORD IF Match(Account, "5","6", "7" ) AND Amount_CR <> 0 TO JE_NonRev_Accts OPEN

Statistics ON ABS(Amount_CR) STD Number 5

Extract RECORD IF ABS(Amount) > AVERAGE1 + (2* STDDEV1) TO Large_CR_NonRev_Accts


Round Amounts

Filter for all amounts with 0 cents: MOD(Amount,1) = 0

For a more granular, materiality-based analysis, create a conditional computed field with a default value of Blanks(30):


Prior Year Entries Posted 5 Days After Year End

Between(Posting_Date,`20220101`,`20220106`) AND Period = "2021"


Amount Just Below Approval Threshold

(Assumption: Threshold = $5000) Create a filter, adjusting the lower bound as necessary: Between(Amount,4999.99,4900)


Do watch out for part 5 on analytics in Duplicate Payments, or reach out to our team info@connevate.com for further discussion on your areas you will like to cover.


bottom of page