Audit Analytics by Sean Elrington

Data analytics is useful for good governance as it provides better assurance as compared to manual sampling. Is the need to hire consultants necessary for straight-forward audit tests? It can help recover unnecessary spending. There may be resistance from the other departments if audit wants to perform 100% checks. There are still auditors which do not use data analytics.

Common Objections to Using Audit Analytics. Some auditors are too busy to learn and to change. The data may not be readily available. In addition, the cost has to be justified. Some are too intimidated by change. You need an understanding of ERP, database structures, views, tables etc. The benefit is that you might save time for data analysis. How will analytics help audit productivity? As it requires less man-hours, analytics can be useful. Although in the short-run, probably more work will be required. If the error is systematic, testing 100% of the population might not be very useful. In such cases, it will be better just to test a few samples and fix the control first. Analytics is here to stay.

Questions that the IT manager will ask you. Why can’t the auditors use Excel? Excel has its limitations on data size. Random sampling is not a good way to detect fraud. Data can be amended easily in excel and it does not have much data security. Sorting can be slow and Excel lacks functions like Benford’s Analysis. Modern audit software have data logs too. It is good to host the data on a server especially when there are multiple users. If you rely on the IT department to generate data for you, there is a risk that the data could be manipulated before being provided to you. There is an issue of how much access that an audit should be given. Data should be obtained from production and not the data warehouse. In the data warehouse, bad data might have been removed already. Application controls rely on passwords and roles to work. Relying on the controls in the ERP system might not be useful when there is collusion. Data might be present from different systems and auditors can’t simply draw the data from one ERP system.

Considerations when choosing audit software. Some of the functions that are heavily used are extract, join, relate, summarize, stratify, classify and age. Continuous monitoring is a lot more expensive and complicated. Is training a big consideration? Do you need to write your own scripts? Or can you buy scripts? What is your required return on investment? Will learning the software help the auditors in their career development? How much technical support is needed? What are the server requirements?

Analytic Software Tools. Picalo is a free tool that can be downloaded online. Some of the other software besides Excel are TopCATTs, Arbutus Software, IDEA, Monarch, Picalo, ACL. ACL usually requires a lot of training before users will know how to use.

Testing for Duplicate Payments. One can test both exact and fuzzy matches. There are multiple reasons why this might occur. First, you have to ensure that there are no duplicate vendors by scrutinizing the vendor’s details. For exact match testing, you can use ‘Substring’; ‘Include’; ‘Exclude’; ‘Alltrim’ formulae to remove dashes, hyphens etc. Testing should be performed on fields like Invoice Number, Vendor Number, PO Number, Date, Amount etc. Deconstruction techniques are used for Fuzzy matches. They use techniques like Soundex, Soundslike, HEX etc. Some of the algorithms are Levenshtein distance, Metaphone etc.

P2P Vendor Analytics. Some of the objectives are 1) vendor master file is correct; 2) employees are not vendors; 3) no duplicate or unused vendors. Match vendor information with employee information. Check out vendor addresses to ensure that they are not mail drop addresses used by delivery services. Sort the number of vendors by payments per year. Use a vendor name fuzzy match. Find vendors with missing fields to check whether the vendor master is well-kept or not.

Purchase Card Analytics. Objectives are 1) only authorized employees are using cards; 2) card purchases are acceptable. Try and detect transactions by authorized card-holders. Find cardholders not in employee master file. List top spenders by department. Find transactions in excess of authorization limits. Identify weekend and holiday purchases.

FCPA analytics. Objectives are 1) test that there are no suspicious payments made to individuals or entities; 2) verify that gifts received are permitted. Identify payments made to high risk countries. Identify cash payments. Identify unusual gifts. Identify credit card spending with unusual Merchant Category Codes. Find unusual vendors, like PEPs etc. Flag out payments with the words ‘facilitate’. Match to watch-lists, world-check etc.

P2P Payment Analytics. Objectives: 1) POs are unique and properly filled; 2) SODs are working; 3) controls to match invoice and PO amounts are accurate. Detect split purchases. Find duplicate payments. Find POs that were raised late. Look out for people who can create and approve their own POs. Look out for unauthorized purchasers. Ensure that there is approval for all POs. Compare a list of payments to prohibited vendor lists.

GL Analytics. Objectives: 1) Only authorized employees are making GL entries; 2) GL entries are acceptable. Detect duplicate GL entries. Look for suspicious wordings like ‘park’; ‘temp’; ‘reverse’; ‘suspense’. Detect GLs made at odd timings. Detect payment voucher and look out for approvals etc. Look out for frequently changed or reversed accounts. Find temporary accounts.

Healthcare Analytics. Objectives: 1) procedures billed to the correct code; 2) appropriate charges are billed to correct account; 3) reasonable timeline of patient activities.

Fraud Facts. Whistle-blower hotlines are a great way to detect fraud. Some level of fraud might be acceptable. It depends on the organizational culture. It is not the auditor’s responsibility to detect fraud. Look out for transactions with fraud symptoms. In general, there are two types of fraud: 1) Fraudulent financial reporting and 2) misappropriation of assets. It is hard to distinguish whether it was an honest mistake or fraudulent. The top from the top must be correct.

Common Business Frauds. You might need the help of a skilful financial auditor to deconstruct fraudulent financial reporting. Financial fraud is a very serious matter. Misappropriation of assets often involve kickbacks. Multiple payees could be an issue. Duplicate payments are a potential source of fraud too. A shell company could be used to deliver fictitious services. Detect maintenance which has been performed too frequently. Physical inspection of works/goods can help. Look out for defective delivery of goods/services by having good IC over the receipting of goods and services. See how often different employees reject or accept goods based on their quality. Inaccurate pricing is one of the type of risks too. Contract rigging means awarding to the lowest bid, but later subsequently changing the product specs so that the contractor will have to deliver more and thus can earn more money. Check contracted projects over their original budgets. Contract rigging is difficult to detect if you are not familiar with the goods. Bid rigging is very difficult to detect. Ensure that there are no phantom employees or contractors. Look out for invalid employees’ wages.

Interesting Fraud Stories. The fraud triangle occurs when there is 1) opportunity; 2) motivation; 3) rationalization. Don’t let non-trained employees do the accounts. Do not let the salespeople collect the cash. Be wary of bribery to win contracts etc.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s