Audit data analytics and CAATs

In short

Audit data analytics (ADA) is the use of analysis, modelling and visualisation on the whole population of an entity's data to discover and analyse patterns, deviations and inconsistencies, and to extract other useful audit information. CAATs (Computer Assisted Audit Techniques) are the tools and techniques the auditor uses to do this when records exist only in electronic form — generalised audit software, test data, embedded modules and spreadsheet or SQL analysis. They matter because in a computerised environment there is no visible paper audit trail: substantive procedures on a sample of vouchers cannot by themselves give sufficient appropriate audit evidence. This topic is examined in CA Intermediate and Final Auditing papers (short notes, 4-6 mark questions and case scenarios) and in SA 500, SA 315, SA 330 and the ICAI's guidance on automated environments, and it is the single most asked practical area in audit interviews at the Big Four, Grant Thornton, BDO and in internal audit and forensic roles.

Explained

Why CAATs and ADA became necessary

• In an automated environment the entity's accounting is in an ERP (SAP, Oracle, Tally Prime, Microsoft Dynamics, Zoho, Busy); transactions are initiated, authorised, processed and recorded by the system with no signed voucher at each step.
• Consequences for the auditor: loss of visible audit trail, uniform processing of transactions (one wrong rule corrupts every transaction, not one), systematic rather than random errors, concentration of duties in IT, and automated controls that cannot be tested by inspecting documents.
• Volume: lakhs of journal lines make manual 100% checking impossible, while a sample of 40 vouchers covers a negligible fraction of value.
• Standards driving it: SA 315 (understanding the entity and its IT environment, identifying risks), SA 330 (responses to assessed risks — tests of controls and substantive procedures), SA 500 (audit evidence — reliability depends on the source and on controls over preparation of information produced by the entity, "IPE"), SA 520 (analytical procedures), SA 240 (fraud — mandatory testing of journal entries and other adjustments), SA 230 (documentation of the work done).
• ICAI has issued guidance on auditing in an automated environment (Standards on Auditing implementation guides and the Guidance Note on Audit of Internal Financial Controls over Financial Reporting), and automated environment questions are examinable in both CA Inter and Final.

Classification of CAATs

1. Audit software (data analysis tools) — the auditor interrogates the entity's data files:
   • Generalised Audit Software (GAS): ready-made packages — IDEA (CaseWare), ACL/Galvanize, TeamMate Analytics, Arbutus. Functions: import, extraction, stratification, ageing, gap and duplicate detection, summarisation, joins, sampling, re-computation, Benford analysis.
   • Purpose-written / custom programs: written by the auditor, entity or a third party for one client's unusual file format. Costly; must be independently verified.
   • Utility programs and system management software: sort, merge, query, report writers, database and OS utilities. Not designed for audit, so they do not themselves maintain an audit trail.
   • Spreadsheets and query languages: Excel (pivot tables, lookups, Power Query, Power Pivot), SQL, Python/R, Power BI and Tableau for visualisation. Dominant in Indian practice because they are cheap and available.
2. Test data techniques — the auditor's own dummy transactions, valid and invalid, are put through the client's programs and the output compared with the expected result; tests programmed controls (validation checks, limits, authorisation rules).
   • Dead/dummy test data on a copy of the program, offline.
   • Live test data processed in the real system — risky; the entries must be reversed and the fact documented.
   • Integrated Test Facility (ITF): a dummy unit (branch, cost centre, employee) is built into the live system and the auditor's test transactions are processed with real ones, then removed by journal.
3. Embedded audit facilities (continuous audit techniques) — audit routines built into the client's application:
   • Embedded Audit Module / SCARF (System Control Audit Review File): the program writes transactions meeting auditor-set criteria (e.g. payments above ₹10 lakh, after-hours postings) to an audit file for later review.
   • Snapshot: captures the state of a transaction before and after each processing stage — shows how a figure was computed.
   • Audit hooks: flags only selected high-risk transactions in real time.
   • Transaction tagging: a tag follows one transaction through every module.
   • Requires auditor involvement at the system design stage and management's cooperation; used mainly by internal audit and in banks.
4. Parallel simulation / re-performance — the auditor writes an independent program replicating the client's logic (depreciation, interest, EMI, gratuity, bill discounting, GST rates), runs it on real data and reconciles exceptions.
5. Review of program logic and system documentation — code review, mapping, comparison of production source code with authorised versions; needs IT-audit skill.

Using CAATs: a step-by-step process

1. Set the objective — which assertion (existence, completeness, valuation, cut-off, presentation) or which control is being tested.
2. Understand the IT environment and data — applications, interfaces, database, table and field layout, key fields, date formats, number of records.
3. Decide feasibility — availability of data and of the auditor's skill, cost against benefit, timing (data may be archived), effect on the client's operations.
4. Obtain the data — preferably a read-only extract by IT in the auditor's presence, in a neutral format (CSV, text, Excel, print report file, direct ODBC read). Never work on the live production database.
5. Verify data integrity before testing — reconcile record count, control totals of key amount columns, and opening/closing balances to the trial balance or audited financials; check completeness of the date range; look for blank or default values. *An analytic on unreconciled data is worthless.*
6. Design and run the tests — build the script/query, run on a small sample first, then on the full population.
7. Evaluate the output — exceptions are not errors; investigate causes with management, distinguish data artefacts from control failures and from misstatements.
8. Document (SA 230) — objective, source and extent of data, integrity checks performed, software and version, scripts/formulae, parameters, output, exceptions, conclusions. Retain the script so the test is reproducible.
9. Conclude — effect on risk assessment, on the nature/timing/extent of further procedures, on reporting (including CARO 2020 and IFCoFR comments where relevant).

Common analytics the auditor actually runs

• Journal entry testing (SA 240 requirement): entries posted by unexpected users, on holidays or after hours, with round-rupee amounts, blank or vague narrations, manual entries to revenue or to related-party accounts, entries at period end or reversed soon after.
• Three-way match: purchase order to goods receipt note to invoice — quantity, rate and value; unmatched or part-matched items are the exception population.
• Duplicate payments: same vendor + same invoice number + same amount; or same amount + same date + different vendor codes (fuzzy duplicates).
• Vendor-employee overlap: matching bank account, PAN, address or phone between vendor master and payroll — classic fraud test.
• Master data analysis: vendors with no PAN/GSTIN, inactive vendors suddenly paid, duplicate customer codes, credit limits exceeded.
• Ageing and recoverability: receivable/payable/inventory ageing, slow-moving stock, provision adequacy (expected credit loss buckets).
• Revenue analytics: gap in invoice number sequence, credit notes after year-end, cut-off testing of the last and first invoices, price/quantity reasonableness against the approved price master.
• Payroll: duplicate PAN/UAN/bank accounts, ghost employees (no leave, no PF, no attendance), payments after the date of leaving, overtime outliers.
• Statutory: GST rate applied against the HSN master (5%/18%/40% slabs since 22 September 2025, plus the specified 0.25%/3% rates on rough diamonds and bullion and 28% on some items withdrawn into the new structure), TDS rate against section, ITC mismatch against GSTR-2B.
• Benford's law on the first digit of large transaction populations as a *directional* indicator of manipulation, never as proof.
• Visualisation: heat maps of postings by user and hour, trend graphs, scatter plots to spot outliers.
• Stratification and sampling: value banding, monetary unit sampling, random and systematic selection with a documented seed (supports SA 530).

Advantages and limitations

• Advantages: 100% of the population can be tested instead of a sample; speed and repeatability; independence from the client's own reports; ability to test programmed controls and logic that leave no document; detection of patterns invisible to manual review; cheaper in later years once scripts exist; stronger documentation; enables continuous and remote auditing.
• Limitations: high initial cost and training; dependence on the client's cooperation for data; risk of corrupting live data (live test data, ITF); data in incompatible or legacy formats; output only as good as input data integrity — "garbage in, garbage out"; produces a long list of exceptions needing judgment; it does not replace professional scepticism, physical verification, external confirmation or enquiry; small entities may have no reliable electronic data at all; data privacy and confidentiality obligations (Code of Ethics; and until 13 May 2027 the IT Act section 43A and SPDI Rules govern sensitive personal data, with the DPDP Rules, 2025 fiduciary duties applying from that date).
• Need for independent verification: audit software must be tested by the auditor before reliance; if the client's staff helped build it, the auditor must confirm the version actually run was the one tested.

ADA in the audit cycle, and what is new

• Risk assessment stage (SA 315): process mining, full-population trend analysis, identification of unusual flows — strengthens risk identification rather than providing substantive evidence.
• Substantive stage: recalculation and re-performance give high-quality evidence; exception testing of 100% of a population can reduce or remove the need for sampling, but the auditor must still evaluate every exception.
• Tests of controls: test data and ITF test automated controls; configuration and master-data review tests the settings; General IT Controls (GITC) — access, change management, program change, backup, segregation of duties — must be effective before any automated control or system-generated report can be relied upon.
• Audit trail and metadata are themselves audit evidence; so is the user-access log.
• Continuous auditing/monitoring is standard in bank concurrent audit and large internal audit functions.
• RPA and AI tools (bots for reconciliation, machine-learning anomaly scoring, large-language-model document review) are increasingly used, but the auditor remains responsible for the evidence and must be able to explain the tool's logic; ICAI has issued advisories on AI use in practice and the position continues to evolve.
• Under the Companies Act, 2013 and the Companies (Accounts) Rules, accounting software of companies must have an audit trail / edit log feature, enabled and retained; the auditor reports on it under Rule 11(g) of the Companies (Audit and Auditors) Rules — a direct link between data analytics and the audit report.

Key points

• CAAT - use of a computer as an audit tool, to test both the entity's data and its programmed controls.
• ADA - discovering and analysing patterns and anomalies in whole populations, used for risk assessment and substantive testing.
• Four families of CAAT - audit software, test data, embedded audit facilities, parallel simulation (plus program-logic review).
• Generalised Audit Software - ready-made (IDEA, ACL, TeamMate); flexible, client-independent, needs no programming.
• Test data - auditor's dummy valid and invalid transactions, tests programmed controls; live use must be reversed.
• Integrated Test Facility - dummy entity inside the live system; test and real transactions processed together.
• Embedded audit module / SCARF - audit routine inside the client's program writing flagged transactions to an audit file; basis of continuous auditing.
• Snapshot and transaction tagging - capture a transaction before and after each stage, or follow one transaction end to end.
• Parallel simulation - auditor's own program re-performs the client's computation and differences are reconciled.
• Data integrity check first - record count, control totals, reconciliation to trial balance, before any test is run.
• Exception is not error - every exception needs investigation and professional judgment before being called a misstatement.
• GITC dependency - automated controls and system reports can be relied upon only if general IT controls are effective.
• SA 240 journal entry testing - mandatory; typically done with analytics on user, time, narration and round amounts.
• Benford's law - expected frequency of leading digits; an indicator of possible manipulation, not evidence of fraud.
• SA 230 documentation - data source, integrity checks, tool and version, script, parameters, output, conclusion; must be reproducible.
• Rule 11(g) - auditor's report must state whether the audit trail (edit log) in the company's accounting software was enabled and preserved.
• Limitations - cost, skills, data access, GIGO, no substitute for physical verification, confirmation, enquiry or scepticism.
• GAS vs utility programs - utilities do the processing but keep no audit trail of their own; GAS is designed to.

Formulas and examples

(Values are in ₹; counts are plain numbers; percentages to one decimal.)

Example 1 — Coverage: analytics versus a voucher sample. A company has 2,40,000 purchase invoices totalling ₹600 crore. The auditor manually examines 60 vouchers worth ₹1.8 crore; separately, a CAAT script tests all invoices above ₹25 lakh, which number 900 and total ₹480 crore.

• Manual coverage by count = 60 ÷ 2,40,000 = 0.025%; by value = 1.8 ÷ 600 = 0.3%.
• CAAT coverage by count = 900 ÷ 2,40,000 = 0.375%; by value = 480 ÷ 600 = 80.0%.
• Conclusion: the same effort directed by value covers 80% of the rupees. The residual ₹120 crore in 2,39,100 small invoices is covered by analytics on the whole population (duplicates, weekends, unapproved vendors) rather than by inspection.

Example 2 — Duplicate payment test. A vendor ledger has 1,85,000 payment lines. A script keying on (vendor code + invoice number + amount) returns 410 exact duplicate groups covering 820 lines worth ₹6.15 crore. Investigation shows 300 groups (₹4.20 crore) are genuine instalments recorded with the same reference, 85 groups (₹1.55 crore) are data-entry duplicates already reversed, and 25 groups (₹40 lakh) are unrecovered double payments.

• Exception rate = 820 ÷ 1,85,000 = 0.44%.
• Confirmed error value = ₹40,00,000; error rate by value = 40 lakh ÷ 6.15 crore of flagged value = 6.5%.
• Audit action: ₹40 lakh is a recoverable asset/overstated expense — propose adjustment or recovery, report the control weakness (no duplicate-invoice-number block in the ERP) in the management letter and consider it for IFCoFR reporting.

Example 3 — Benford's law on the first digit. A sample of 10,000 expense claims gives these first-digit counts against Benford expectation:

• Leading digit 9 appears 1,200 times against 460 expected — an excess of 740, about 2.6 times the expectation, while digit 1 is short by 710.
• Likely explanation to test: claims pushed just under an approval limit of ₹10,000 (so they start with 9). Follow-up test: all claims between ₹9,000 and ₹9,999, split by employee and approver.
• Note the limit: Benford applies to naturally occurring, unbounded, multi-scale data; it does not apply to assigned numbers (invoice numbers, PAN, employee codes) or to amounts with a fixed price list.

Example 4 — Parallel simulation of interest. A term loan has an outstanding of ₹8.40 crore; the sanction letter specifies 9.25% per annum on a monthly reducing balance. The auditor's spreadsheet recomputes interest for the year on the month-end balances and gets ₹82,15,000; the books show ₹85,30,000.

• Difference = 85,30,000 − 82,15,000 = ₹3,15,000 overstated interest cost (3.8% of the auditor's figure).
• Root cause found: the ERP applied 9.60% for the whole year because the rate-reset master was not updated.
• Treatment: quantify, discuss with management, obtain a bank confirmation of the rate charged, adjust or include in the summary of uncorrected misstatements under SA 450, and note the master-data control weakness.

Example 5 — Monetary unit sampling using a CAAT. Population book value ₹25 crore (18,000 lines), tolerable misstatement ₹50 lakh, risk factor 3.0.

• Sampling interval = 50,00,000 ÷ 3.0 = ₹16,66,667.
• Expected sample size = 25,00,00,000 ÷ 16,66,667 ≈ 150 items; all lines of ₹16,66,667 or more are picked with certainty.
• The script finds a ₹2,00,000 overstatement in a line with a book value of ₹8,00,000 → tainting = 2,00,000 ÷ 8,00,000 = 0.25; projected misstatement for that item = 0.25 × 16,66,667 = ₹4,16,667, which is compared with tolerable misstatement.

-- Weekend / after-hours manual journals (SA 240 journal entry test)
SELECT doc_no, posting_date, entry_user, gl_account, amount, narration
FROM   gl_lines
WHERE  entry_type = 'MANUAL'
  AND  (DATEPART(weekday, entry_timestamp) IN (1,7)
        OR DATEPART(hour, entry_timestamp) NOT BETWEEN 9 AND 20)
  AND  ABS(amount) >= 1000000
ORDER  BY ABS(amount) DESC;

Where it is used

• Statutory audit at the Big Four and Indian firms (Deloitte, EY, KPMG, PwC, Grant Thornton, BDO, S.R. Batliboi, Walker Chandiok, SRBC, Lodha, Haribhakti): audit data analytics teams run journal entry testing, revenue and procure-to-pay analytics on every large engagement.
• Internal audit and risk advisory: continuous control monitoring dashboards in Power BI for retail chains, NBFCs and manufacturers; roles titled Analytics Associate, IA Analytics, Internal Audit Executive.
• Bank audit: concurrent and central statutory audit use CBS data extracts for NPA/IRAC classification testing, income leakage, interest-rate master checks and KYC exception reports — alongside RBI inspection and long-form audit reports.
• Forensic and fraud investigation: vendor-employee matching, ghost employees, benami vendors, circular trading, under SFIO/EOW and bank-fraud investigations.
• GST and tax practice: reconciliation of books with GSTR-1, GSTR-3B and GSTR-2B, e-invoice and e-way-bill data, HSN-wise rate checks; TDS rate verification before filing.
• Industry roles: finance controllership and shared-service centres (Genpact, WNS, Accenture, Infosys BPM) use the same scripts for payment-run controls; ERP implementation and IT-audit roles (ISA/DISA, CISA holders) test configuration and GITC.

Interview questions

• Q: What are CAATs and when are they necessary? A: Techniques that use the computer as an audit tool to test an entity's data and programmed controls; they are necessary when there is no visible audit trail, volumes are too large for manual checking, or controls are embedded in the software.
• Q: Difference between audit software and test data? A: Audit software analyses the entity's *data* to test balances and transactions; test data puts the auditor's dummy transactions through the entity's *programs* to test whether controls work.
• Q: What is an Integrated Test Facility and what is its risk? A: A dummy unit created inside the live system so the auditor's test transactions are processed with real ones; the risk is contaminating real data, so entries must be identified and reversed and the procedure documented.
• Q: What is the very first thing you do with a client data extract? A: Verify integrity and completeness — reconcile record count, control totals and opening/closing balances to the trial balance and check the date range — before running any test.
• Q: Does testing 100% of a population remove the need for judgment? A: No. It removes sampling risk, but each exception must still be investigated and evaluated, and completeness and reliability of the data must be established.
• Q: How would you design a journal entry test under SA 240? A: Filter manual entries for unusual users, after-hours or holiday postings, round amounts, blank narrations, entries direct to revenue or related-party accounts, and period-end entries reversed shortly afterwards; then vouch the selected entries.
• Q: Can Benford's law prove fraud? A: No — it indicates that a population's digit distribution is abnormal and directs further testing; it does not apply to assigned numbers or price-controlled data and is never evidence of fraud by itself.
• Q: Why must general IT controls be tested before relying on an automated control or a system report? A: Because without effective access, change-management and program-change controls, the application logic or the report could have been altered, so its output is not reliable evidence.
• Q: What is parallel simulation and give one Indian example? A: The auditor independently re-computes the client's processing on real data — for example recomputing depreciation under Schedule II of the Companies Act, 2013, or interest on a loan portfolio — and reconciles the difference.
• Q: What does the auditor report about the audit trail in a company audit? A: Under Rule 11(g) of the Companies (Audit and Auditors) Rules, the auditor states whether the company's accounting software had the audit trail (edit log) feature, whether it operated through the year, whether it was tampered with and whether the log was preserved as required.
• Q: Give three limitations of CAATs that you would mention to a client. A: Cost and skill requirement, dependence on the integrity and availability of the client's data (garbage in, garbage out), and that they cannot replace physical verification, external confirmation or professional scepticism.
• Q: How do you document a data analytic so another auditor can rely on it? A: Record the objective and assertion, data source and extraction method, integrity reconciliation, tool and version, the script or formula with parameters, the output and exception list, and the conclusion — so the test can be re-run and reproduced (SA 230).

Common mistakes

• Treating every exception thrown up by a script as a misstatement, instead of investigating causes and distinguishing data artefacts from control failures.
• Running tests before reconciling the extract to the trial balance — any conclusion from unreconciled data is void.
• Confusing test data (tests the program) with audit software (tests the data), and ITF with an embedded audit module, in exam answers.
• Using live test data or an ITF without reversing the entries or without management's written consent.
• Claiming Benford's law, or an analytic alone, as proof of fraud, or applying Benford to invoice numbers, PANs and other assigned numbers.
• Saying that 100% testing means zero audit risk, forgetting non-sampling risk, incomplete data and the limits of the analytic itself.
• Writing the answer with no reference to the relevant standards (SA 315, SA 330, SA 500, SA 240, SA 230) or to Rule 11(g) — examiners award marks for the linkage.

Related topics

Audit planning, materiality, risk assessment and sampling · Internal control and internal audit · Standards on Auditing (SAs) · Forensic accounting and fraud detection · Company audit: CARO 2020 and the auditor's report · Bank audit, tax audit and special audits