Data Analytics in Modern Audits: Using CAATs Effectively

8/2/2026

There is a sentence that should precede every conversation about audit analytics, and it usually does not:

Testing 100% of a population provides no assurance if you cannot establish that the population is complete and accurate.

An auditor who extracts a transaction file, runs every test on all of it, and finds nothing has demonstrated something about that file — not about the client's transactions. If the extraction omitted a subsidiary ledger, excluded voided-and-reissued items, or captured a filtered view, the elegance of the analysis is irrelevant.

Which makes data reliability the gate, not a preliminary step.

Establishing the Data Is the Work

Before any routine runs:

Reconcile the extracted population to the financial statements — or to a general ledger balance that reconciles to them. Record totals, transaction counts, and the reconciliation itself. An unreconciled extraction cannot support a conclusion, and this is the single most common deficiency in analytics-based procedures.

Understand what the extraction includes and excludes. Which entities, which ledgers, which posting types, which statuses. Ask what the report writer's default filters are, because the person running the extract frequently does not know.

Establish the source. Who extracted the data, from which system, on what date, using what parameters, and can it be re-run reproducibly.

Test the period boundaries for completeness at both ends.

Consider the general IT controls relevant to the data's reliability. Data from a system whose access and change controls are unreliable is itself less reliable, whatever the reconciliation says.

Watch for the transformation step. Data cleaned, standardized, or reshaped between extraction and analysis has been altered, and the transformation must be documented and — where it affects the conclusion — tested. A silent character-encoding fix or an inferred date format can change results.

Two Different Things Called "Analytics"

Confusing them is why analytics work sometimes produces no audit evidence at all.

Risk assessment analytics identify where to look. Trend analysis, ratio review, stratification, outlier identification, Benford's-type digit analysis. They direct effort. They are not, by themselves, substantive evidence about an assertion, and a file that documents an anomaly identified and never followed anywhere has documented curiosity.

Substantive procedures provide evidence about an assertion. These fall in two kinds:

Tests of details performed on the full population — recalculations, matching, criteria-based testing. These are genuinely powerful, and they are what people mean when they say analytics changed auditing.

Substantive analytical procedures, which require an expectation developed with sufficient precision to detect a material misstatement, a defined threshold for investigation, and investigation of differences. This is where most "analytics" fall down: comparing this year to last year and observing that the change looks reasonable is not a substantive analytical procedure. Without a precise, independently developed expectation, it is a reasonableness impression.

Say which one you are doing, in the workpaper. Method coverage runs through the audit training courses catalog and the internal auditing training courses listing.

Routines That Earn Their Keep

Specific, because the general case does not help anyone.

Journal entry testing. The highest-yield routine available, and required attention in any case. Entries posted by unexpected users; entries posted outside business hours or on non-business days; entries to seldom-used, suspense, or clearing accounts; entries with missing or generic descriptions; entries posted at period end and reversed shortly after; manual entries to normally automated accounts; and round-dollar entries above a threshold. Our post on embezzlement detection covers the same tests from the investigative side.

Three-way matching across purchase order, receipt, and invoice, on the full population.

Duplicate testing — payments, invoices, employees, vendors — using fuzzy matching for near-duplicates, which is where the results are.

Gap and sequence analysis on documents that should be sequential.

Recalculation of aging, depreciation, accruals, allowances, payroll gross-to-net, and interest — full population, cheaply.

Cut-off testing around period end on shipments, receipts, and revenue.

Matching across files that should agree — subledger to ledger, vendor master to employee master, inventory records to costing.

Criteria-based selection of the entire subset meeting a condition, rather than sampling from it: all credit memos above an amount, all entries by one user, all transactions with a specific counterparty.

Stratification and completeness checks to understand the population before designing anything.

Practical tooling: much of this is achievable in a spreadsheet with pivot tables and lookup functions, and technique matters more than software. The Essential Excel Skills course, Excel training for accountants catalog, and Excel pivot tables session cover the mechanics; the High Impact Excel dashboard material covers presentation.

Where Analytics Actually Fail: Disposition

The most important operational point, and it is not about detection.

A full-population routine over a large transaction file produces a large number of items meeting the criteria. Detection is easy and cheap. What follows is not:

Every item requires disposition — investigated, explained, and concluded on — and the file must show it. An audit file containing an exception list with no resolution is worse than one that never ran the routine, because it documents an identified anomaly the auditor did not pursue.

Volume defeats teams. A routine returning hundreds of hits gets triaged informally, then sampled, then quietly abandoned in the last week of fieldwork.

So design for disposability. Set the criteria so the population of hits is one the team can actually clear; refine iteratively rather than casting wide; and decide in advance what an acceptable explanation looks like and what evidence supports it.

And a "clean" result requires the same rigour. A routine that returns nothing may mean the control works — or that the filter was wrong, the field was empty, or the criteria never matched anything. Prove the routine can find what it is looking for by testing it against a known item.

That last point deserves emphasis: a procedure that finds nothing because it was mis-specified looks identical in the workpapers to one that finds nothing because there is nothing there.

Sampling Is Not Obsolete

A corrective, since the marketing suggests otherwise.

Full-population testing works where the attribute is machine-testable from reliable data. It does not replace sampling where:

The evidence is in a document rather than in a field — the invoice, the contract, the approval, the shipping record.

The test requires judgment about whether something is appropriate rather than whether it matches.

The data cannot be relied upon, in which case full-population testing of it proves nothing.

Only some populations are digital, which is most engagements.

The right answer on most engagements is a combination: full-population testing where the data supports it, sampling where the evidence is documentary, and analytics for risk assessment throughout.

Documentation

What the file must show, because this is where analytics-based work is most often found deficient:

The source data — system, extraction date, extracted by whom, parameters used, and how it can be reproduced.

The reconciliation to the financial statements, with amounts and counts.

Any transformation applied, and its effect.

The procedure — what routine, what criteria, what thresholds, and why those thresholds.

The complete results, not a summary.

Disposition of every exception, with the evidence supporting each conclusion.

The conclusion, and what assertion it supports.

Which kind of procedure it was — risk assessment, test of details, or substantive analytical procedure — because the standards' requirements differ.

A reviewer should be able to re-perform it. A screenshot of a dashboard with a comment saying "no issues noted" is not documentation of anything.

Three Governance Matters

Independence. Designing or implementing a client's own analytics or monitoring is a non-attest service requiring evaluation for an attest client, per the discussion in our post on post-season audit preparation. A client who asks the audit team to build what the audit team recommended needs that analysis before anyone agrees.

Client data handling. Extractions contain the client's entire transaction history, frequently including personal data. Where it is stored, who can access it, how long it is retained, and how it is disposed of belong in the firm's policy — the same policy discussed in our post on technology governance, and reinforced by ethics training and professional conduct.

Your own tools are a risk. A firm-built routine with an error produces consistent, confident, wrong results across every engagement that uses it. Routines need validation before deployment, version control, an owner, and re-validation when the underlying data structures change — the same discipline our post on automation applies to bots.

Getting Started Without a Program

For a firm with no analytics function, the sequence that works:

1. Journal entry testing on one engagement, since the data is available and the value is immediate.
2. Build the reconciliation habit before building any routine.
3. Standardize the request — a defined data specification given to clients in advance, which also improves the client's preparation.
4. Keep the first routines narrow so exceptions are clearable.
5. Document to a re-performance standard from the first attempt, because retrofitting documentation does not happen.
6. Then extend to a second routine on the same engagement rather than the same routine everywhere.

What not to do: buy a platform first, or assign this to whoever is most comfortable with software rather than to someone who understands the assertions being tested.

Where CAATs Work Goes Wrong

• Testing a population never reconciled to the financial statements
• Not knowing what the extraction excludes, including default report filters
• Undocumented transformation between extraction and analysis
• Treating risk assessment analytics as substantive evidence
• Calling a year-over-year comparison a substantive analytical procedure with no precise expectation
• No defined investigation threshold, so differences are explained after the fact
• Exception lists with no disposition, documenting an anomaly nobody pursued
• Criteria so broad the hit volume cannot be cleared, so the routine is abandoned
• A clean result accepted without proving the routine can detect a known item
• Assuming full-population testing replaces sampling for documentary evidence
• Relying on data from a system with weak IT controls
• Documentation that cannot be re-performed — a screenshot and "no issues noted"
• Not stating which kind of procedure was performed, when requirements differ
• Building the client's analytics without evaluating independence
• Client extractions stored with no retention or access policy
• Firm-built routines with no validation or version control, producing consistent wrong answers
• Buying a platform before establishing the reconciliation discipline

The summary for an audit senior: reconcile the population before you run anything, say in the workpaper which kind of procedure you are performing, set the criteria narrowly enough that every exception can actually be disposed of, and prove the routine works by testing it against something you know it should catch — because a mis-specified procedure and a clean population look identical in the file.

Frequently Asked Questions

Why is data reliability the gate rather than a preliminary step?

Because testing 100% of a population provides no assurance if the population is not complete and accurate. An auditor who runs every test on an extraction that omitted a ledger, excluded voided-and-reissued items, or captured a filtered view has demonstrated something about that file and nothing about the client's transactions.

What must be established before running any routine?

A reconciliation of the extracted population to the financial statements with amounts and counts; an understanding of what the extraction includes and excludes, including the report writer's default filters; the source, date, extractor, and parameters; period-boundary completeness; the relevant general IT controls; and documentation of any transformation applied between extraction and analysis.

What separates a substantive analytical procedure from a reasonableness impression?

An expectation developed with sufficient precision to detect a material misstatement, a defined threshold for investigating differences, and actual investigation of them. Comparing this year to last year and concluding the change looks reasonable is not a substantive analytical procedure, however it is labeled in the file.

Why do analytics-based procedures fail on disposition rather than detection?

Because a full-population routine over a large file is cheap to run and produces many items meeting the criteria, and every one requires investigation, explanation, and a documented conclusion. High hit volume gets triaged informally, then sampled, then abandoned late in fieldwork — leaving a file that documents an anomaly the auditor did not pursue, which is worse than never running the routine.

Does full-population testing make sampling obsolete?

No. It works where the attribute is machine-testable from reliable data. Sampling remains necessary where the evidence sits in a document rather than a field, where the test requires judgment about appropriateness rather than matching, where the data cannot be relied upon, and on the many engagements where only some populations are digital.

Why must a "clean" analytics result be proven?

Because a routine that returns nothing because it was mis-specified — wrong filter, empty field, criteria that never matched — looks identical in the workpapers to one that returns nothing because there is nothing there. Testing the routine against a known item that it should catch is what distinguishes the two.