Chapter 8: Audit Analytics in Excel

Accounting Analytics: An Integrated Approach

In this chapter

Part II of V: Accounting Analytics with Excel

Chapter 5 of 5 in this Part

• 8.1 Audit Analytics and the Internal Auditor
• 8.2 Reconciling the Population
• 8.3 Stratification
• 8.4 Benford’s Law
• Guided Tutorial 8.1

• 8.6 Aging and Reconciling Receivables
• Guided Tutorial 8.2
• 8.8 Journal Entry Testing
• Guided Tutorial 8.3
• 8.10 Duplicate, Gap, and Threshold Tests
• 8.11 Documenting Audit Analytics
• 8.12 Looking Ahead

This chapter looks at the Charles River data as an auditor does. Each module follows a section of the chapter, and the three guided tutorials review the supplier payments, the receivables and the journal entries.

Learning objectives

1. Explain how internal and external auditors use data analytics on whole populations, and reconcile a population to the general ledger before testing it.
2. Stratify a population by value and use the strata to direct audit attention.
3. Perform a first-digit test of Benford’s Law, measure conformity with the mean absolute deviation, and explain a nonconformity that has a legitimate cause.

The first three objectives cover testing whole populations: reconciling them, stratifying them, and testing their first digits.

Learning objectives (continued)

4. Age receivables at an as-of date, reconcile the aged detail to the general ledger, and select balances for confirmation.
5. Test journal entries for characteristics of management override, combine the results into a risk score, and record a disposition for each exception.
6. Design duplicate, gap, and threshold tests, and document audit analytics in a workpaper that another auditor can review.

The last three cover receivables, journal entries, and the duplicate, gap and threshold tests, with the workpaper that documents them.

Can the numbers be relied on?

• The audit committee has asked internal audit for a data-driven review
• You review the supplier payments, the receivables and the journal entries
• The question is not what the numbers mean, but whether they can be trusted
• Is each population complete, does anything look unusual, does the ledger agree?

The earlier chapters followed a staff accountant and turned up conditions an auditor would want to examine, from reused supplier invoice numbers to invoices dated before their orders. This chapter takes the auditor’s view, before the external auditors begin their work.

8.1 Audit Analytics and the Internal Auditor

Analytics lets auditors test whole populations

• Internal auditors work for the organization and report to its audit committee
• External auditors are independent and give an opinion on the statements
• Both need complete, valid transactions and controls that worked
• A test of every record returns exceptions, and judgment explains them

Traditional procedures examine a sample and infer the rest. Analytics applies a test to every payment, invoice or entry, though each test covers only what it looks for. Many exceptions turn out to have an ordinary explanation, so the auditor’s judgment goes into designing the test, investigating each exception and deciding what it means. The AICPA’s Guide to Audit Data Analytics covers how auditors can use such analyses.

An audit analytics workflow

Figure 8.1 · An Audit Analytics Workflow

Every test follows these steps, from the objective to the workpaper. The dashed loop back from the exceptions is how a test whose exceptions are mostly false positives gets refined and rerun.

Watch out: closing entries cut both ways

• Year-end closes move each year’s revenue and expenses to retained earnings
• Leave them out of any analysis of revenue or expenses by period
• Keep them in a journal entry test: a close is a journal entry like any other
• Entries at the end of a period are the ones auditing standards single out

Chapter 7 left the closes out when it compared operating expenses with the budget. Tutorial 8.3 keeps them in, and asks what authority covers their size, above every approval limit on record.

8.2 Reconciling the Population

Agree the population to the ledger before testing

• A population is every record a test’s conclusion covers
• A test on an incomplete extract can find nothing because records are missing
• Agree its count and total to the ledger, such as the cash credits of payments
• Use the same cutoff on both sides; agreement is in total, not record by record

The ledger already holds supplier payments dated early in the next fiscal year, so a review of the three years filters them out on both sides. Agreeing totals do not show that each record is present, as Tutorial 4.1 showed, and the ledger is itself the company’s record. Fay and Negangard begin their journal entry testing case with the same step: validate that the file is complete before testing it.

8.3 Stratification

Stratify to see where the dollars are

• Strata group a population by value, with a count and a total for each
• Many small items and a few large ones: the few can carry much of the value
• The largest items are examined one by one, the richest strata most carefully
• Keep the strata bounds in cells, counted with COUNTIFS and summed with SUMIFS

The PCAOB’s sampling standard uses strata to reduce a sample and directs the auditor to examine in full the items that could equal or exceed tolerable misstatement, often called individually significant items. In analytics, where a test can run on every item, the table still directs attention.

8.4 Benford’s Law

First digits follow Benford’s Law

• In many data sets, about three in ten numbers start with 1, and under one in twenty with 9
• The expected share of first digit d is LOG10(1+1/d)
• It is plausible for amounts that combine many quantities and span several magnitudes
• Departures can point to invented or altered amounts (Nigrini and Mittermaier)

Newcomb noticed that the early pages of logarithm tables wore out first, and Benford tested the pattern on many kinds of data. Nigrini and Mittermaier introduced digit tests as analytical procedures for audit planning.

Not every population should be expected to conform

• Assigned numbers, such as invoice or check numbers, are unsuitable
• A minimum, a maximum or a size-based selection can distort the digits
• So can amounts people set, such as prices ending in 99
• A population shaped by them needs a reason to expect the pattern

Nigrini reviews which kinds of audit data suit a Benford analysis, a question to answer before running the test.

Judge conformity with the mean absolute deviation

Table 8.1 · Practical first-digit screening ranges for the mean absolute deviation

MAD of the first-digit test	Conclusion
0.000 to 0.006	Close conformity
Above 0.006 to 0.012	Acceptable conformity
Above 0.012 to 0.015	Marginally acceptable conformity
Above 0.015	Nonconformity

Chi-square and z tests take the number of amounts into account, so with tens of thousands of amounts they can call a small departure significant. Nigrini recommends the mean absolute deviation, the average of the nine absolute differences between actual and expected shares, which leaves the count out, judged against these practical screening ranges.

A nonconformity shows where to look

• It does not show that anything is wrong
• The digits that differ most lead to the subsets that need an explanation
• The explanation is often a feature of the business, such as a way of paying
• Use the test to point to subsets, not to pick a sample by what it removes

Nigrini examined a method that samples the transactions whose removal would make a population conform, and found it can produce large samples of questionable accuracy. Tutorial 8.1 uses the test to find a subset instead.

Check your understanding

Should each set of numbers be expected to follow Benford’s Law?

1. Supplier payment amounts over three years
2. Check numbers
3. Only the payments above an approval threshold
4. Retail prices that end in 99

Ask students which property of the data decides each answer.

Measured amounts are the candidates to conform

1. Likely: measured amounts that span several magnitudes
2. No: assigned numbers carry no magnitude
3. Not without a reason: a subset selected by its size
4. Not without a reason: prices set by people’s preferences

Even for the first, a nonconformity is a reason to look, not a finding. Tutorial 8.1 shows how.

Guided Tutorial 8.1: Stratifying and Testing Supplier Payments

Test the payments in nine steps

1. Create the audit workbook and import the payments
2. Reconcile the population to the ledger
3. Stratify the payments
4. Examine the top strata in full
5. Add the first digit of each payment
6. Compare the first digits with Benford’s Law
7. Split the payments into full and partial payments
8. Measure the conformity of each subset
9. Document the tests

Students start the audit workbook, reconcile the payments to the ledger, stratify them, examine the top strata, and run a first-digit test on the whole population and then on two subsets.

Four payments make up the top strata

Figure 8.2 · Supplier Payments Stratified by Amount

The four largest payments total about one percent of the population’s value, but they warrant individual attention because of their size. The stratum just below them holds about a third of the value. Those four went to two suppliers, each paying one invoice in full, so their disposition is to vouch them to the purchase order, the receiving record and the invoice approval.

Count the first digits and measure the MAD

Figure 8.3 · A First-Digit Test of Supplier Payments (detail)

COUNTIF counts the payments by first digit, and each share is compared with the expected share. The MAD of all payments falls in the marginally acceptable range.

Fewer ones, and more high digits, than expected

Figure 8.3 · A First-Digit Test of Supplier Payments (detail)

The columns are the actual shares and the line Benford’s expected shares. The pattern points to a subset worth splitting out.

Partial payments explain the nonconformity

Figure 8.4 · First Digits of Full and Partial Payments

Full payments conform acceptably; partial payments do not. Charles River pays in parts only larger invoices, most of them just above a thousand dollars, so their parts are amounts in the hundreds that start with the digits 5 to 9 far more often than expected, and with 2 and 3 far less often. The payment process is a plausible explanation, but the digit test cannot show that each payment is genuine.

Checkpoint

Check that:

• the Population worksheet shows no difference in count or total between the Payments Table and the ledger;
• the stratification totals equal the population, and the top two strata hold four payments;
• no payment has a LeadDigit of 0;
• the MAD of all payments is marginally acceptable, the full payments conform acceptably, and the partial payments do not conform.

The first test found no exception that points to an error, and that is a result worth documenting. The payments agree with the ledger in count and total, the largest are identified for vouching, and the one departure from expectations has a plausible explanation in the payment process, with the payments’ support still to examine.

8.6 Aging and Reconciling Receivables

An aging sorts open balances by days past due

• Buckets such as current, 1 to 30, 31 to 60, 61 to 90 and over 90 days
• It serves the credit department and the estimate of uncollectible amounts
• The FASB’s credit-loss standard requires an estimate of expected losses, often from an aging
• The as-of date matters: later payments must not reduce the balance

The standard requires the estimate to reflect the risk of loss even when it is remote, and illustrates it with loss rates by bucket. Only an expectation of no nonpayment at all, which management must support, removes the need for an estimate. Charles River’s chart calls the valuation account the Allowance for Doubtful Accounts, the older name.

Reconcile the aging to the ledger

• The open invoices are the detail behind the receivables account
• A reconciliation lists the ledger balance, the detail and every reconciling item
• Expected items, such as an unmatched receipt, clear in a later period
• An item no one can explain is a finding in itself

Either the ledger holds an amount no invoice supports, or the detail misses invoices the ledger recorded. Either way, the aging does not describe the whole balance, and an allowance estimated from it would not cover it either.

Confirm the largest balances; sample the rest

• A confirmation asks the customer to state its balance directly to the auditor
• The largest balances are confirmed in full, as individually significant items
• The rest are sampled at random, so every balance can be chosen
• RANDARRAY and SORTBY shuffle the list; freeze it with Paste Values

The PCAOB’s confirmation standard requires confirming receivables from sales, or directly accessing a knowledgeable external source’s information. Where neither is feasible, the auditor documents why and obtains external information indirectly through other substantive procedures. A reviewer needs the frozen sample and the documented method, so record its date and parameters.

Check your understanding

How does each item enter the receivables work?

1. A payment applied after the as-of date
2. A difference from the ledger that no one can explain
3. The customers with the largest balances
4. All the other customers

Ask students what would go wrong if each item were treated differently.

Each item has its place

1. Left out: the balance is aged as of the reporting date
2. A finding: the aging does not describe the whole balance
3. Confirmed in full, as key items
4. Sampled at random, and the selection frozen as values

Tutorial 8.2 applies all four, and the second turns out to be the most important.

Guided Tutorial 8.2: Aging Receivables and Reconciling Them to the Ledger

Age and reconcile in eight steps

1. Import the invoices, receipts, and credit memos
2. Calculate the open balance of each invoice
3. Age the open invoices
4. Summarize the aging
5. Reconcile the aging to the ledger
6. Investigate the difference
7. Select balances for confirmation
8. Record the findings

Students calculate each invoice’s open balance at the year-end from its receipts and credit memos, age it into five buckets with a lookup table, reconcile the total to the ledger, investigate the difference, and select balances for confirmation.

The ledger holds a balance with no invoice detail

Figure 8.5 · The Receivables Aging Reconciled to the Ledger

The aging shows that most of the balance open at the year-end is not yet due; it describes what is owed, not how promptly customers paid. The receivables account exceeds the open invoices by a round amount. The same cutoff on both sides rules out one cause, but the difference still has to be traced.

The difference traces to one journal entry line

Figure 8.6 · Accounts Receivable in the Ledger by Source Document

Grouped by source document, the receivables postings are invoices, receipts and credit memos, plus a single journal entry line: the opening balance. The allowance and bad-debt accounts have no postings at all.

Checkpoint

Check that:

• about a thousand invoices are open, and no open balance is negative;
• the aging total equals the sum of the OpenBalance column, and most of it is current;
• the difference between account 1020 and the open invoices is a round amount, traced to one journal entry line;
• the PivotTable shows no rows for accounts 1030 and 6170;
• the Confirmations worksheet lists the key items and a random sample of twenty other customers, pasted as values.

Part of the receivables balance has no invoice detail in the extract, and no allowance has been recorded against any of the balance. Both findings could affect the amount at which receivables are reported, and both go to the audit committee with their evidence.

8.8 Journal Entry Testing

Journal entries are where controls can be overridden

• Managers can bypass controls that work for everyone else: management override
• Fraud often involves unauthorized entries during the year or at period end (AS 2401)
• Warning signs: unusual accounts, unusual creators, period-end or post-closing timing
• Round numbers or a consistent ending number are another

The PCAOB’s fraud standard requires the auditor to identify and test journal entries, ordinarily focusing on the end of the period. Fay and Negangard build their case around the same questions: who recorded an entry, when, and whether it was posted to an earlier date than it was made.

Six characteristics become six tests

Table 8.3 · Characteristics of journal entries and the tests that look for them

Characteristic	Charles River columns	Test in Tutorial 8.3
Made by individuals who typically do not make journal entries (AS 2401)	CreatedByEmployeeID	Entries by creator
Recorded at the end of a period or after it (AS 2401)	PostingDate, CreatedDate	Backdated flag, a partial screen for entries created after their posting date
Round numbers or a consistent ending number (AS 2401)	TotalAmount	RoundAmount flag
Created at an unusual time	CreatedDate	Weekend flag
Approved by the person who made it	CreatedByEmployeeID, ApprovedByEmployeeID	SelfApproved flag
Approved above the approver’s authority	TotalAmount, MaxApprovalAmount	AboveLimit flag

A backdated entry is created on a later day than the date it posts to, so it may change a period already closed; the flag tests only that part of the characteristic, since the closing dates are not in the data. An entry approved by its creator defeats segregation of duties.

A risk score ranks entries for review

• Each flag is TRUE or FALSE, and their sum is a risk score
• A score ranks entries; it is not a probability of fraud
• Several flags come first in review, though they can share one cause
• Equal weights are a judgment too

Digit tests apply to journal entries with care: Debreceny and Gray found that journal entry amounts departed from Benford’s Law in every organization they examined, so a nonconforming test there suggests nothing by itself.

Every exception gets a disposition

• Many flagged entries are false positives, legitimate entries that meet the criteria
• Expected: record the evidence that explains it
• Follow up: record the question to ask and the document to request
• A misstatement or control deficiency: report it

Nigrini emphasizes refining and rerunning tests, and grouping and filtering results, to deal with false positives. A test whose exceptions are mostly expected is refined, for example by excluding a recurring entry type whose evidence has been examined, with the exclusion recorded, and rerun.

Check your understanding

What disposition does each flagged entry get?

1. Depreciation entries created on month-ends that fell on a weekend
2. Year-end closes above every approver’s limit
3. An entry created days after its posting date, with no explanation
4. An entry approved by the person who created it

Ask students what evidence would support each disposition.

Expected, or follow up

1. Expected timing pattern, but verify the depreciation schedule and approval before clearing
2. Expected in size, but request the closing-entry authority
3. Follow up: ask why, and request the support
4. Follow up, as a possible control deficiency: segregation of duties

Tutorial 8.3 shows the pattern behind the first two once with a PivotTable, so that the entries left to follow up stand out.

Guided Tutorial 8.3: Testing Journal Entries for Management Override

Test the journal entries in seven steps

1. Import the journal entries with their creators and approvers
2. Add the five flags
3. Combine the flags into a risk score
4. Separate the expected patterns
5. Record a disposition for each flagged entry
6. Trace the highest-scoring entry to the ledger
7. Complete the workpaper

Students import the entries with their creators, approvers and approval limits, add five flag columns, each a short formula listed in the chapter’s table of flags, sum them into a risk score, separate the expected patterns, and trace the highest score to the ledger.

One entry carries four flags

Figure 8.7 · Journal Entries Sorted by Risk Score

The opening balance entry was created by the chief financial officer on a Saturday, days after the date it posts to, approved under the same employee ID, and far above that approver’s limit. The entries scoring two include backdated entries keyed on the first Saturday of January and two large debt reclassifications.

The highest score is the opening balance entry

Figure 8.8 · The Ledger Lines of the Opening Balance Entry

A FILTER formula spills the entry’s eighteen ledger lines from its number. The entry sets up the opening balances of every balance sheet account.

Its receivables line has no invoice detail behind it

Figure 8.8 · The Ledger Lines of the Opening Balance Entry (detail)

The outlined line is the receivables difference found in Tutorial 8.2. The journal entry test and the receivables reconciliation have led to the same entry from two directions.

Checkpoint

Check that:

• the five flag columns and RiskScore calculate for every entry, and only one entry scores 4;
• the PivotTables show the patterns behind the weekend depreciation entries and the year-end closes;
• the Dispositions worksheet holds a disposition for every entry with a score of 1 or more;
• the totals of the JournalEntries and JournalLines Tables agree, the ledger’s debits equal its credits, and the Drilldown worksheet shows the accounts receivable line of JE-2024-000001.

The flags on the recurring entries are explained by their timing, which leaves their amounts and approvals to check against their schedules. The tests also found approval exceptions to follow up. The opening balance entry, created after its date, approved by its own creator, and carrying balances the detail does not support, is the first item for the audit committee.

8.10 Duplicate, Gap, and Threshold Tests

Near-duplicates catch what exact duplicates miss

• A duplicate payment need not repeat a reference number
• Look for the same supplier and amount on different dates
• Add the invoice as a criterion to tell a second payment from a second invoice
• About a hundred such pairs each pay two different invoices: likely ordinary

The pairs still need their invoices and receipts to confirm that they are two obligations, and the test cannot catch a bill entered under two invoice numbers. It is worth running, because a near-duplicate that pays the same invoice twice is exactly the error an exact-duplicate test misses. Exercise 8.5 runs it.

Gap tests need a series meant to be continuous

• Check the numbers are unique, then compare the count with largest less smallest, plus one
• Charles River’s invoice, entry, order and receipt numbers run without gaps
• A clean result is recorded as carefully as an exception
• ACH and wire payments explain most check-number gaps, and two gaps relate to reused check numbers

Matched against the payment numbers, every check gap is explained, except the numbers of the two checks that reused an earlier number, the pairs of Exercise 2.6.

Threshold tests look just below the line

• People avoiding a control keep amounts just under its threshold
• Count the items in narrow bands just below and just above each limit
• Look for consecutive documents with the same requester and amount
• They may be one purchase split to stay under a limit

Amounts just above or below control thresholds are one of the patterns Nigrini found in fraud schemes. Exercise 8.5 applies the test to Charles River’s purchase requisitions and the approval limits in the Employee table.

Earlier tests join the audit program

• Requisitions converted to orders without an approver (Exercise 2.5)
• Invoice numbers whose year differs from their date (Exercise 5.2)
• Suppliers sharing an employee’s address (Exercise 5.5)
• Unapproved suppliers and items sold outside their life (Exercise 5.6)

Each can point to a risk or give evidence about a control, so the workpaper names the question it tests, with its population, its results and a disposition for every exception. Pricing is another control area: promotion and price-list dates and override approvals can be compared with the orders, as Exercise 8.6 does.

8.11 Documenting Audit Analytics

A workpaper another auditor can follow

Figure 8.9 · A Workpaper for the Journal Entry Tests

The PCAOB requires documentation that lets an experienced auditor with no previous connection understand the work, the evidence and the conclusions, and shows who performed and reviewed it, and when. The workpaper adds the objective, the reconciliation of the population, the exceptions with their dispositions, and a conclusion.

Analytics depends on reproducibility and dispositions

• Record the extract, its as-of date and every test’s parameters
• Then the workbook can be refreshed and the tests rerun
• A test is complete only when every exception has a disposition and evidence
• A test that flags nothing is a result to record, not a test to omit

The parameters include the strata bounds, the aging buckets and the approval limits. These tests also reach the limits of a workbook: comparing every payment with every other one is slow in a worksheet, and the later Parts return to them in SQL and in interactive reports.

8.12 Looking Ahead

What the review found

• Payments: agree with the ledger; the largest identified; the nonconformity plausibly explained
• Receivables: part of the balance has no invoice detail, and no allowance
• Journal entries: approval exceptions, led by the opening balance entry
• Next: the Part II case, then Part III and the database

The Part II Comprehensive Case brings the five Excel chapters together on a decision: whether to run the promotion calendar again. Part III then asks the same questions of whole tables at once.

Summary

• Reconcile a population to the ledger before testing it
• Stratification shows where the dollars are; Benford’s Law where to look
• An aging reconciled to the ledger shows what the detail cannot support
• Journal entry flags rank entries by risk; every exception gets a disposition
• Duplicate, gap and threshold tests, documented so another auditor can follow

Ask students which finding they would put first for the audit committee, and why: the opening balance entry is the usual answer.

Key terms

Aging schedule · Allowance for credit losses · Audit workpaper · Backdated entry · Benford’s Law · Confirmation · Control deficiency · Disposition · Exception · False positive · First-digit test · Gap test · Individually significant item · Journal entry testing · Management override of controls · Mean absolute deviation (MAD) · Near-duplicate · Population · Reconciliation · Reconciling item · Risk score · Sampling risk · Segregation of duties · Stratification · Threshold test · Tolerable misstatement

Every term is defined at the end of the chapter. Population, disposition and management override are the ideas Part III’s audit chapter returns to.

Practice and next steps

• Financial Accounting
  • Exercise 8.1: Estimating the Allowance for Credit Losses
  • Exercise 8.2: Reconciling Accounts Payable to the Ledger
• Managerial Accounting
  • Exercise 8.3: Customer Credit Exposure
  • Exercise 8.4: Supplier Concentration and Payment Timing
• Auditing
  • Exercise 8.5: Testing Purchasing and Payment Controls
  • Exercise 8.6: Testing Pricing Controls

Next: Comprehensive Case: Deciding on the 2027 Promotion Calendar

Assign one exercise from each perspective; Exercise 8.1 estimates the allowance the receivables lack, and Exercise 8.5 runs the near-duplicate and threshold tests. The Part II case follows.

About these slides

These slides accompany Accounting Analytics: An Integrated Approach, licensed under CC BY-SA 4.0. Every figure and table comes from the chapter:

https://aa.accountinganalyticshub.com/chapters/08-audit-analytics/chapter.html

Students can find each figure and table, with the full explanation, in the online chapter.