Data analytics in internal audit: replacing sampling with full populations.
Our practice, in detail.
1. Why analytics changes the question.
Sampling is a tool for periods when the population was too large to test fully. With reasonable data access, that constraint no longer holds for most processes. A mid-sized manufacturer has a few hundred thousand transactions a year; that population is testable end to end on a laptop.
The question therefore shifts. With analytics, you are no longer asking "did the sample of 25 contain an exception?" You are asking "across the full population, where are the outliers, and what do they tell us about the control environment?" The former answers a procedural question; the latter answers a business question.
Analytics does not replace walk-throughs (design effectiveness still needs them) or interviews (root-cause analysis still depends on them). It replaces the substantive testing step in the middle, where sampling used to live.
2. Data readiness, before tooling.
The fastest path to a failed analytics engagement is buying a tool before agreeing the data. The order is reversed:
- Agree the data extracts at scoping (see our scoping note). Standing extracts refreshed on a fixed calendar.
- Agree the data format (CSV, Excel, direct database read). Excel is fine for populations under a million rows; CSV scales further; direct ERP read is best where IT can grant it.
- Validate the extract on the first run. Tie the GL extract back to the trial balance, the vendor master count to the ERP count, the payroll extract to the salary summary. If the data ties, every analytic that follows is defensible. If it does not, no analytic is defensible.
- Document any limitations (e.g. only one of two business units' data was extracted, or the period covered is shorter than the audit period). Findings later must be qualified by these limits.
3. Five analytics tests that always pay back.
Across cycles and industries, five tests find more in less time than any other set we run:
- Duplicate vendor / invoice / payment detection. Match on vendor name, GST registration, bank account number; on invoice number across vendors; on payment reference and amount. Almost every population produces a small list of true duplicates and a longer list of near-duplicates that need investigation.
- Round-number testing. Transactions with suspiciously round values (₹50,000.00 exactly, ₹1,00,000.00 exactly) are over-represented in fraud populations. Pull the round-number transactions and review the supporting documentation.
- Approval-limit testing. Transactions at or just below the approver's delegated authority. A cluster of transactions at ₹4,99,999 when the approval limit is ₹5,00,000 is a signal worth pulling on.
- Weekend / public-holiday postings. Postings outside working hours can indicate override of business-hour controls. Cross-reference with the user-access log to see who posted.
- Vendor master to employee master cross-match. Match vendor bank accounts, addresses, phone numbers and PAN against the employee master. Where they match, investigate. (We have used this single test to identify ghost-vendor fraud more often than any other.)
For each test, the analytic produces an exception list. The exception list is not a finding. It is the starting point for the substantive work.
4. Treating exceptions as evidence, not findings.
A common failure mode is for the IA team to file the analytics exception list as the finding. ("The data showed 47 transactions where the round-number test flagged.") That is not a finding; it is an observation that needs investigation.
The finding is what the investigation produces:
- Of the 47 round-number transactions, 41 were legitimate (regular round-figure rentals, repeated standing payments). (Cleared)
- Of the remaining 6, 4 were rate-revisions where the new rate happened to be round but had been properly approved. (Cleared)
- Of the remaining 2, one was a duplicate payment that had not been recovered. (Finding: duplicate payment ₹2.4 lakhs; recovery initiated.)
- The other was an approval below the threshold that turned out, on inspection, to be a series of three sub-threshold transactions to the same vendor on the same day. (Finding: approval-limit circumvention; SOP and ERP control gap.)
The discipline is to follow every exception to a conclusion. The analytics produce the questions; the auditor still has to answer them.
5. Tools, and why they matter less than you think.
The tool debate (IDEA vs ACL vs Excel + Python vs Alteryx vs Tableau) is genuinely less important than the data-readiness debate. Any reasonably capable tool can run the five tests above on a few hundred thousand rows.
In our practice, we have run analytics-led audits with all of: Excel pivot tables (yes, on a few million rows), Power Query, Python with pandas, and SQL queries against the ERP. The findings have not depended on the tool. They have depended on the imagination of the test design, the quality of the data, and the discipline of the follow-up.
If the firm has IDEA or ACL licences and the team is fluent in them, use them. If not, build the analytic in Excel or Python. Do not let a tool-procurement discussion delay the audit by a quarter.
6. Integrating analytics into the cycle.
Analytics belongs in the planning week of each cycle, not at the end:
- Day 1 to 2: Receive the data extracts. Validate against the trial balance and master records.
- Day 3 to 4: Run the standard five tests. Generate exception lists.
- Day 5 onwards: Walk-throughs in the field, with the exception lists already in hand. Conversations with process owners are sharper because the questions are specific.
- Final week: Each exception followed to a conclusion. Findings drafted; analytics evidence cross-referenced from the working files.
Done this way, analytics is not a separate activity competing for time; it is the spine of the cycle.
Frequently asked
Do we need to invest in a specialised tool to do analytics?
Not as a precondition. The first three or four cycles can be run with Excel or Python (free) plus disciplined data extraction. If the IA function or the company decides to invest later in IDEA, ACL or a workflow tool, the existing test designs port across. Tooling is an optimisation; it is not the starting line.
How do we get the data extracts when IT is overloaded?
Agree the standing extracts at scoping, on a fixed quarterly calendar. The IT team prepares them once each quarter on a date in the calendar, with the format and the recipient pre-agreed. This converts a recurring ad-hoc request into a scheduled deliverable, which is much easier for IT to plan around.
What about data privacy and confidentiality?
Internal audit access to company data is covered under the engagement letter and the internal-audit charter. Personally identifiable information (employee data, customer data) should be handled per the company's privacy policy, with appropriate masking where outputs are shared beyond the IA team. The extracts themselves stay within controlled IA storage.
Is sampling ever still appropriate?
Yes - for controls that operate manually with no system record (a physical review that produces only a signature), for IFC operating-effectiveness testing where attribute sampling is the methodology of choice, and for testing the operation of a control where full-population testing would not add information beyond what the sample reveals.
How do we present analytics evidence in the report?
Summarise in the report (e.g. 'we tested the full population of 1.2 lakh transactions for round-value postings, identifying 47 exceptions of which 2 required action'). Keep the data tables and exception lists in the working files. The report should be readable without the data tables; the data tables should be available without the report.
