8  Audit Analytics in Excel

Learning Objectives

After completing this chapter, you will be able to:

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.
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.

Chapter slides View slides Download PowerPoint

Opening Scenario

Charles River’s audit committee has asked the internal audit team for a data-driven review before the external auditors begin their work on fiscal 2026. You have joined the team, and the chief audit executive has given you three areas to review: the supplier payments of fiscal 2024 through 2026, the accounts receivable at the end of fiscal 2026, and the journal entries of the same three years.

The earlier chapters followed a staff accountant through the question of the Furniture margin. Along the way, they turned up conditions that an auditor would want to examine, from the reused supplier invoice numbers of Exercise 2.6 to the invoices dated before their orders in Exercise 5.2. This chapter takes the auditor’s view. Its question is not what the numbers mean for the business but whether they can be relied on: whether each population is complete, whether any transactions look unusual, and whether the ledger agrees with the detail behind it.

8.1 Audit Analytics and the Internal Auditor

Internal auditors work for, or on behalf of, the organization they audit. They evaluate its risk management, controls, and governance, and they report to the board of directors, usually through its audit committee. External auditors are independent of the organization and express an opinion on its financial statements. The two roles differ in purpose, but they rely on much of the same evidence, and increasingly on the same analytics: both need to know whether recorded transactions are complete and valid, and whether the controls over them operated as designed.

Traditional audit procedures examine a sample of transactions and draw conclusions about the rest. Data analytics lets an auditor apply a test to every record in a population instead: every payment, every invoice, every journal entry. The test covers only the characteristics it looks for, so other audit procedures may still use samples or examine specific documents. The American Institute of Certified Public Accountants (AICPA) publishes a Guide to Audit Data Analytics that addresses how auditors can use such analyses, including practical considerations for assessing the reliability of the data they use (AICPA, 2026).

Testing a whole population changes the nature of the work. A test that examines tens of thousands of records returns a list of exceptions, the records that meet its criteria, and many exceptions turn out to have an ordinary explanation, how many depending on the test and the population. The auditor’s judgment goes into designing the test, investigating each exception, and deciding what it means. Figure 8.1 shows the workflow that this chapter follows.

Figure 8.1: An Audit Analytics Workflow.

The earlier chapters have already covered two parts of an audit. Chapter 3 described the auditor’s requirement to understand a client’s information system and how its data flows to the ledger (AICPA, 2021), and the analytical procedures in the exercises of Chapters 6 and 7 compared recorded amounts with an expectation, as the auditing standard of the Public Company Accounting Oversight Board (PCAOB) describes (PCAOB, n.d.-b). This chapter turns to tests of individual transactions: which payments, invoices, and entries deserve a closer look, and why. It cites the PCAOB’s standards, which govern the audits of public companies’ financial statements, as the benchmark for those tests; internal auditors follow the Global Internal Audit Standards of the Institute of Internal Auditors (IIA, 2024), and they use the same techniques.

WarningWatch out

Charles River’s year-end closing entries, dated December 31, move each year’s revenue and expenses to retained earnings. Any analysis of revenue or expenses by period must leave them out, as Chapter 7 did when it compared operating expenses with the budget. A journal entry test must do the opposite and include them: a closing entry is a journal entry like any other, and entries at the end of a period are the ones that auditing standards single out for attention.

8.2 Reconciling the Population

A population is the complete set of records to which a test’s conclusion applies, such as every supplier payment dated in the period under review. Before you test a population, check that you have all of it. A test on an incomplete extract may find nothing wrong simply because the problem records were never extracted. The check is the control total you have used since Chapter 4: agree the count and total of the extract to an independent source. Agreement shows that the two sides describe the same total, not that every record is present: as Tutorial 4.1, Step 10 showed, a missing record and an equal duplicate, or two errors that offset, leave the totals unchanged. And the ledger is itself a record of the company’s system, so agreement with it says nothing about transactions that were never recorded at all.

For transactions that post to the ledger, the general ledger is that source. Every Charles River supplier payment credits the cash account, so the count and total of the payments in an extract should equal the count and total of the cash credits that payments posted in the same period. Fay and Negangard (2017) begin their journal entry testing case with the same step, validating that a file of entries is complete before testing it.

Defining the population also means defining its period. Charles River’s ledger already holds a few hundred supplier payments dated early in fiscal 2027, for invoices of fiscal 2026. A review of fiscal 2024 through 2026 filters them out by payment date, and the ledger comparison uses the same cutoff, so that both sides describe the same population.

8.3 Stratification

Stratification divides a population into groups, or strata, usually by value. A stratification table reports the number of items and their total value in each stratum, and the share of the population that each represents. It answers a simple question that shapes the rest of the audit: where are the dollars? A population of payments typically contains many small items and a few large ones, and the few large ones can carry a large share of the value.

Auditing standards use stratification in two ways. For audit sampling, the PCAOB’s standard on sampling notes that separating a population into relatively homogeneous groups, based for example on the recorded value of the items, can reduce the sample size the auditor needs. A sample always carries sampling risk, the risk that the conclusion drawn from the sample differs from the one the whole population would support. The same standard therefore directs the auditor to examine in full the items for which accepting any sampling risk is not justified. Examples are items that could individually equal or exceed tolerable misstatement, the largest misstatement in an account that, combined with misstatements elsewhere, would not make the financial statements materially misstated (PCAOB, n.d.-d). Such items are often called individually significant items. In analytics, where a test can run on every item, a stratification table still directs attention: the largest items are examined one by one, and the strata that hold most of the value receive the most careful tests.

In Excel, the strata are defined by lower and upper bounds typed in cells, and COUNTIFS and SUMIFS with a pair of criteria, greater than or equal to the lower bound and less than the upper bound, count and total each stratum. Keeping the bounds in cells makes them parameters: you can change a bound and see the table recalculate, and a reviewer can see exactly where each boundary lies.

8.4 Benford’s Law

8.4.1 The Expected Pattern of First Digits

In many collections of numbers, the first digit is far more often a 1 than a 9. Newcomb (1881) noticed that the early pages of books of logarithm tables, which cover numbers that begin with 1, wore out faster than the later ones, and Benford (1938) tested the pattern on many different kinds of data. Benford’s Law gives the expected proportion of numbers whose first digit is d as LOG10(1+1/d): about 30 percent start with 1, about 18 percent with 2, and fewer than 5 percent with 9. The pattern is plausible for amounts that varied economic processes produce by multiplying and combining many quantities, and that span several orders of magnitude, which describes many accounting amounts.

Nigrini and Mittermaier (1997) introduced digit tests based on the law as analytical procedures that auditors could use in planning: because authentic numbers tend to follow the expected frequencies, a population whose digits depart from them may contain invented or altered amounts, and deserves attention.

Not every population should be expected to conform, and Nigrini (2017) reviews which kinds of audit data are appropriate for a Benford analysis, a question the analyst must answer before running the test. Numbers that are assigned rather than measured, such as invoice numbers or check numbers, carry no information about magnitude and are unsuitable. A minimum or a maximum, a subset of a population selected by its size, and amounts that people set, such as prices ending in 99, can all distort the digits. A population shaped by any of them needs a specific reason to expect the pattern before it is compared with the law.

8.4.2 Measuring Conformity

A first-digit test counts how many amounts begin with each digit from 1 to 9 and compares each digit’s actual proportion with its expected proportion. The comparison needs a summary measure, and the usual statistical tests need care with large accounting populations. A chi-square test compares all nine counts with their expected values at once, and a z test compares one digit at a time. Both take the number of amounts into account, so with tens of thousands of amounts they can call a departure statistically significant when it is small in practical terms. Nigrini (2012) recommends the mean absolute deviation (MAD), the average of the nine absolute differences between the actual and expected proportions, which leaves the number of amounts out of its calculation, and gives the practical screening ranges in Table 8.1 for judging the first-digit test.

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

8.4.3 From a Pattern to Transactions

A nonconforming first-digit test does not show that anything is wrong. It shows where to look. The digits with the largest differences, and the subsets of the population that produce them, lead to the transactions that need an explanation, and the explanation is often a feature of the business: a pricing policy, a fee schedule, or a way of paying invoices. Nigrini (2017) examined a proposed method that selects as an audit sample the transactions whose removal would make a population conform, and found that it can produce large samples of questionable accuracy. Benford analysis is best used as it is used in Tutorial 8.1, to point the auditor toward subsets that deserve a closer look.

8.5 Guided Tutorial 8.1: Stratifying and Testing Supplier Payments

Context and objective. In this tutorial, you will begin the internal audit review of supplier payments. You will create an audit workbook, import the payments of fiscal 2024 through 2026, and reconcile them to the ledger before testing them. You will then stratify the payments by amount, examine the largest ones individually, and run a first-digit test to see whether the amounts follow Benford’s Law, investigating the payments that do not.

Prerequisites. You need CharlesRiver.xlsx and the Power Query skills of Chapters 4 to 6. The tutorial starts a new workbook, AuditAnalytics.xlsx, which Tutorials 8.2 and 8.3 extend.

Step-by-step instructions.

Step 1. Create the audit workbook and import the payments. An audit workbook keeps the evidence for one engagement apart from the analysis workbook of the earlier chapters.

1. Create a blank workbook and save it as AuditAnalytics.xlsx.
2. Choose Data > Get Data > From File > From Excel Workbook, and select CharlesRiver.xlsx.
3. In the Navigator, select the T39_DisbursementPayment, T37_PurchaseInvoice, and T31_Supplier Tables, and click Transform Data.
4. Rename the queries Payments, PurchaseInvoices, and Suppliers.
5. In PurchaseInvoices, use Home > Choose Columns to keep PurchaseInvoiceID, InvoiceNumber, and GrandTotal, and in Suppliers, keep SupplierID and SupplierName.
6. In Payments, open the filter list of PaymentDate, choose Date/Time Filters > Before, and enter 2027-01-01, so that the query keeps the payments of fiscal 2024 through 2026.
7. Merge Payments with PurchaseInvoices on PurchaseInvoiceID, with a left outer join, and expand InvoiceNumber and GrandTotal without the prefix.
8. Merge Payments with Suppliers on SupplierID, with a left outer join, and expand SupplierName without the prefix.
9. Choose Home > Close & Load > Close & Load To, select Only Create Connection, and click OK. The choice applies to all three new queries, so none of them adds a worksheet yet, and PurchaseInvoices and Suppliers stay connections that only feed Payments.
10. Open Data > Queries & Connections, right-click Payments, choose Load To, and load it as a Table on a New worksheet. Rename the new worksheet Payments.

The Payments Table holds tens of thousands of payments. The date filter leaves out the few hundred payments that the ledger already records in fiscal 2027, so that the population matches the three years under review.

Step 2. Reconcile the population to the ledger. Every supplier payment credits the cash account, 1010 Cash and Cash Equivalents, whose AccountID is 2, so the ledger gives an independent count and total of the payments. Group By, which you used in Chapter 6, reduces the ledger rows to those two numbers before they are loaded.

1. Choose Data > Get Data > From File > From Excel Workbook, select CharlesRiver.xlsx, select the T3_GLEntry Table, and click Transform Data, not Load, because the Table has hundreds of thousands of rows.
2. Name the query PaymentPostings, and filter it: SourceDocumentType equal to DisbursementPayment, AccountID equal to 2, and PostingDate before 2027-01-01.
3. Choose Transform > Group By, select Advanced, and group by SourceDocumentType.
4. Name the first new column Postings, and choose the operation Count Rows.
5. Click Add aggregation, name the second column CashCredited, choose Sum of Credit, and click OK.
6. Choose Home > Close & Load, and rename the new worksheet PaymentPostings.
7. Add a worksheet named Population, and type Source, Count, and Total in A1:C1.
8. In A2, type Payments Table, and in B2 and C2, enter =ROWS(Payments[Amount]) and =SUM(Payments[Amount]).
9. In A3, type Cash credits in the ledger, and in B3 and C3, enter =SUM(PaymentPostings[Postings]) and =SUM(PaymentPostings[CashCredited]).
10. In A4, type Difference, and in B4 and C4, enter =B2-B3 and =C2-C3.

Both differences are zero: the extract and the ledger agree in count and total, as they must if the extract holds every payment the ledger recorded in the period. The agreement is in total, so it cannot show that each payment is present, as Tutorial 4.1, Step 10 showed. A difference, though, would have to be explained, by missing or extra payments, a wrong amount, or a different cutoff, before any test on the extract could be relied on.

Step 3. Stratify the payments.

1. Add a worksheet named Strata, and type the headings Lower bound, Upper bound, Payments, Share of payments, Value, and Share of value in A1:F1.

2. In A2:A6, type the lower bounds 0, 1000, 10000, 50000, and 100000.

3. In B2, enter =A3, and copy it to B3:B5. In B6, enter =MAX(Payments[Amount])+0.01, so that the top stratum includes the largest payment.

4. In C2 and E2, enter the count and the total of the stratum, and copy them down to row 6:

   =COUNTIFS(Payments[Amount],">="&A2,Payments[Amount],"<"&B2)
   =SUMIFS(Payments[Amount],Payments[Amount],">="&A2,Payments[Amount],"<"&B2)

5. In A7, type Total, and in C7 and E7, add the columns with SUM.

6. In D2 and F2, enter =C2/C$7 and =E2/E$7, and copy them down to row 6. In D7 and F7, add the shares with SUM.

7. Check that C7 and E7 equal the count and total on the Population worksheet.

Figure 8.2 shows the result. About three-quarters of the payments are under $1,000, yet they hold less than a quarter of the value, while fewer than a thousand payments between $10,000 and $50,000 hold about a third of it. Only four payments are larger than that.

Figure 8.2: Supplier Payments Stratified by Amount.

Step 4. Examine the top strata in full. Items this large are examined one by one rather than sampled.

1. On the Payments worksheet, open the filter list of Amount, choose Number Filters > Greater Than Or Equal To, and type 50000.
2. For each payment, compare Amount with GrandTotal, and note the supplier and the invoice.
3. Copy the four rows, with the header row, to A10 of the Strata worksheet, below the stratification table.
4. Add a Disposition column beside them, and state the evidence you would examine for each payment.
5. Clear the filter.

The four payments went to two suppliers, and each pays a single invoice in full. None of them is an exception yet. Each is a large, individually significant payment whose disposition is to vouch it: to trace it back to its purchase order, its receiving record, and the approval of its invoice, and to confirm that each supports the payment.

Step 5. Add the first digit of each payment.

1. In the first empty column to the right of the Payments Table, type LeadDigit as the header, and in the first row enter the formula below. The two minus signs convert the text digit that LEFT returns into a number.

   =--LEFT(TEXT([@Amount],"0.00000000E+00"),1)

2. On the Population worksheet, type Payments with LeadDigit 0 in A6, and enter =COUNTIF(Payments[LeadDigit],0) in B6.

B6 returns 0. Scientific notation always starts with the first significant digit, so this formula works for every amount in this population, all of them positive amounts in dollars and cents; a column with zeros, negative amounts, or more decimal places needs a rule of its own. Two shorter formulas do not. LEFT([@Amount],1) returns 0 for the payments below $1, and LEFT(TEXT([@Amount],"0"),1) rounds before it takes the digit, so that $0.60 becomes 1 and $9.70 becomes 10, which starts with 1. Together, the two shortcuts misclassify several hundred payments.

Step 6. Compare the first digits with Benford’s Law.

1. Add a worksheet named Benford, type the headings Digit, Expected, Payments, Actual, and Difference in A1:E1, and type the digits 1 to 9 in A2:A10.
2. In B2 and C2, enter =LOG10(1+1/A2) and =COUNTIF(Payments[LeadDigit],A2), and copy them down to row 10.
3. In A11, type Total, and in B11 and C11, add the columns with SUM.
4. In D2 and E2, enter =C2/C$11 and =ABS(D2-B2), copy them down to row 10, and add column D with SUM in D11.
5. In A12, type MAD, and in E12, enter =AVERAGE(E2:E10), the mean absolute deviation.
6. Format columns B, D, and E as percentages with two decimals, and E12 as a number with five decimals.
7. Select D1:D10, hold Ctrl, and select B1:B10.
8. Choose Insert > Insert Combo Chart > Create Custom Combo Chart, set the Actual series to Clustered Column and the Expected series to Line, and click OK, so that the actual proportions are columns and the expected proportions a line. The chart section of Chapter 6 compares chart types; a combo chart suits a comparison of actual values with a standard.

The MAD falls in the marginally acceptable range of Table 8.1. The chart in Figure 8.3 shows the pattern behind it: the digits 1 and 2 appear less often than Benford’s Law expects, and the digits from 5 to 9 more often. With this many payments, a chi-square test would call the difference highly significant; the MAD says that it is modest but worth explaining.

Figure 8.3: A First-Digit Test of Supplier Payments.

Step 7. Split the payments into full and partial payments. A nonconforming test points to a subset of the population. Charles River pays some invoices in several parts, and a part of an invoice is a different kind of number from a whole invoice.

1. In the Payments Table, add a column named Partial with the formula =[@Amount]<[@GrandTotal], which is TRUE for a payment of part of its invoice.

2. On the Benford worksheet, type Full, Partial, Full share, and Partial share in F1:I1.

3. In F2 and G2, enter the counts of full and partial payments that start with each digit:

   =COUNTIFS(Payments[LeadDigit],A2,Payments[Partial],FALSE)
   =COUNTIFS(Payments[LeadDigit],A2,Payments[Partial],TRUE)

4. In H2 and I2, enter =F2/SUM(F$2:F$10) and =G2/SUM(G$2:G$10), and copy F2:I2 down to row 10.

Columns H and I now hold the first-digit proportions of each subset, which can be compared with the expected proportions in column B as the whole population was.

Step 8. Measure the conformity of each subset.

1. In H12, enter =AVERAGE(ABS(H2:H10-$B$2:$B$10)), the MAD of the full payments. It is the same average of absolute differences as E12, calculated in one formula without a difference column.

2. Copy H12 to I12 for the partial payments.

3. In F14, type Smallest invoice paid in parts, and in I14, enter:

   =MINIFS(Payments[GrandTotal],Payments[Partial],TRUE)

Figure 8.4 shows the two subsets. The full payments conform acceptably. The partial payments do not conform at all, and the likely reason lies in how they arise. I14 shows that Charles River pays in parts only invoices of $1,000 or more, and most of those invoices are between $1,000 and $2,000. Split into two or three payments, such an invoice produces amounts in the hundreds, so the partial payments start with the digits 5 to 9 far more often than Benford’s Law expects, and with 2 and 3 far less often. The size selection and the installments give a plausible explanation for the nonconformity, and the invoices themselves, whose first digits you can test in the same way, conform closely. The digit test cannot show that each payment is genuine, which takes the supporting documents and approvals.

Figure 8.4: First Digits of Full and Partial Payments.

Step 9. Document the tests.

1. Add a worksheet named WP Payments, and type the workpaper labels in A1:A10: Objective, Source, Population check, Procedure, Parameters, Results, Exceptions and disposition, Conclusion, Prepared by, and Reviewed by.
2. In column B, describe the payment tests beside each label, and type your name and the date beside Prepared by, leaving Reviewed by for the reviewer’s name and date.
3. Record the date filter, the strata bounds, and the digit formula in the Parameters entry.
4. Record the four largest payments to vouch and the explanation of the partial payments in the Exceptions and disposition entry.
5. Save the workbook.

The workpaper now tells a reviewer what was tested, on which population, with which parameters, and what each result means. Tutorial 8.3 shows a completed workpaper of the same form.

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 of the review has found no exception that points to an error, and that is a result worth documenting. The payments reconcile to the ledger, the largest ones are identified for vouching, and the one pattern that departs from expectations has a plausible explanation in the payment process, which leaves the support of the payments still to be examined.

8.6 Aging and Reconciling Receivables

8.6.1 The Aging Schedule

An aging schedule classifies the unpaid invoices at an as-of date by how long they are past their due date, in buckets such as current, 1 to 30 days, 31 to 60 days, 61 to 90 days, and over 90 days. It serves the credit department, which follows up the oldest balances, and it serves the accountants who estimate how much of the receivables will not be collected.

Under U.S. generally accepted accounting principles, the standard of the Financial Accounting Standards Board (FASB) on credit losses requires an allowance for credit losses: a valuation account, deducted from the receivables, for the amount not expected to be collected. The standard lists methods based on an aging schedule among the acceptable ways to estimate it, and it requires the estimate to reflect the risk of loss even when that risk is remote. It illustrates the method with historical loss rates for each age bucket, adjusted for current conditions and reasonable and supportable forecasts (FASB, 2016). Only where that adjusted information gives an expectation that nothing will go unpaid does the standard require no estimate, and management must support that conclusion like any other, so a ledger with no allowance does not show by itself whether one is needed. Charles River’s chart of accounts calls this account 1030 Allowance for Doubtful Accounts, the older name for the same valuation account.

Aging depends on dates, so it depends on the as-of date described in Chapter 5. An invoice’s balance at the as-of date is its total minus the payments and credits recorded on or before that date. Payments applied after the as-of date must be excluded, or the aging will show the receivables as they are today rather than as they were at the reporting date.

8.6.2 Reconciling the Detail to the Ledger

The open invoices are the detail behind the accounts receivable account in the general ledger, a sub-ledger in the terms of Chapter 2. At any date, the two should agree. A reconciliation lists the balance of the ledger account, the total of the detail, and every reconciling item that explains a difference between them. Some reconciling items are expected, such as a receipt recorded in the ledger before it was matched to its invoices, and each one should clear in a later period. An item that cannot be explained is a finding in itself: either the ledger holds an amount that no invoice supports, or the detail is missing invoices that the ledger recorded. Either way, the aging does not describe the whole balance, and an allowance estimated from it would not cover the whole balance either.

8.6.3 Confirming Balances and Selecting a Sample

A reconciliation shows that the detail and the ledger agree, but both are records kept by the company. External evidence comes from the customers themselves. A confirmation asks a customer to state the balance it owes directly to the auditor. The PCAOB’s standard on confirmation requires the auditor to confirm accounts receivable that arise from sales of goods or services, or otherwise to obtain the evidence by directly accessing information held by a knowledgeable external source. If the auditor determines that this is not feasible, the auditor documents that determination and performs other substantive procedures that obtain external information indirectly, such as subsequent cash receipts or carriers’ shipping documents. In selecting the receivables to confirm, the auditor takes into account the nature of the items that make up the balances (PCAOB, n.d.-c).

Confirming every balance is rarely practical, so the auditor selects. The largest balances are confirmed in full as individually significant items, and the rest are sampled. The sampling standard requires a sample to be selected so that it can be expected to represent the population, which means that every item must have an opportunity to be selected. Random-based selection achieves this, whether it draws from the population as a whole or from each stratum separately, a method called stratified random sampling (PCAOB, n.d.-d).

In Excel, RANDARRAY(n) returns n random numbers between 0 and 1, and SORTBY(array, by_array) sorts one array by the values of another. Sorting a list of customers by a column of random numbers therefore shuffles it, and the first rows of the shuffled list are a random sample. RANDARRAY draws new numbers every time the workbook recalculates, however, so the selection must be frozen with Paste Values as soon as it is made, and its date and parameters recorded. Pasting values freezes the sample as the record of what was selected, but it does not make a later draw repeat, so a reviewer needs the frozen records and the documented selection method.

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

Context and objective. In this tutorial, you will age Charles River’s receivables at the end of fiscal 2026 and reconcile the aged invoices to the accounts receivable account in the general ledger. You will calculate each invoice’s open balance from the receipts and credit memos recorded by the as-of date, classify the open invoices by days past due, and compare their total with the ledger. You will then investigate the difference, select customer balances for confirmation, and record what the work means for the audit.

Prerequisites. You need the AuditAnalytics.xlsx workbook from Tutorial 8.1. If you start here, download AuditAnalytics.xlsx as it stands at the end of Tutorial 8.1 from Companion Files, and point its queries at your copy of CharlesRiver.xlsx as that page explains.

Step-by-step instructions.

Step 1. Import the invoices, receipts, and credit memos. A customer receipt reaches an invoice through CashReceiptApplication, which records how much of each receipt was applied to each invoice, and a credit memo reduces the invoice named in its OriginalSalesInvoiceID.

1. Choose Data > Get Data > From File > From Excel Workbook, and select CharlesRiver.xlsx.
2. In the Navigator, select the T16_SalesInvoice, T20_CashReceiptApplication, T23_CreditMemo, and T4_Customer Tables, and click Transform Data.
3. Rename the queries OpenInvoices, Applied, Credited, and Customers.
4. In OpenInvoices, check that FreightAmount shows the decimal number icon, 1.2. If it shows the whole number icon, 123, choose Decimal Number and click Replace current, as in Tutorial 4.1, Step 6, so that no freight amount is rounded to whole dollars.
5. In Customers, keep CustomerID, CustomerName, and CreditLimit.
6. In Applied, filter ApplicationDate to dates before 2027-01-01. Choose Transform > Group By, group by SalesInvoiceID, name the new column AppliedTotal, and choose Sum of AppliedAmount.
7. In Credited, filter CreditMemoDate to dates before 2027-01-01, and group by OriginalSalesInvoiceID with a new column CreditedTotal that sums GrandTotal.

The extract ends on December 31, 2026, so the two date filters remove nothing today. They keep the aging correct if you rerun it on a later extract, when receipts and credits after the as-of date must not reduce the balances. Each date filter of this tutorial, these two and those of Steps 2 and 5, types the cutoff as a date of its own, so a new as-of date must be typed in every one of them as well as in the AsOfDate cell of Step 3.

Step 2. Calculate the open balance of each invoice.

1. In OpenInvoices, filter InvoiceDate to dates before 2027-01-01.
2. Merge OpenInvoices with Applied on SalesInvoiceID, with a left outer join, and expand AppliedTotal without the prefix.
3. Merge OpenInvoices with Credited, matching SalesInvoiceID with OriginalSalesInvoiceID, with a left outer join, and expand CreditedTotal without the prefix.
4. Merge OpenInvoices with Customers on CustomerID, with a left outer join, and expand CustomerName and CreditLimit without the prefix.
5. Select the AppliedTotal and CreditedTotal columns, choose Transform > Replace Values, and replace null with 0. An invoice with no receipt or no credit memo finds no match in the merge, which leaves a null, and a subtraction with a null returns null.
6. Choose Add Column > Custom Column, name it OpenBalance, and enter [GrandTotal] - [AppliedTotal] - [CreditedTotal].
7. Set the type of OpenBalance to Decimal Number, and choose Transform > Rounding > Round with 2 decimal places.
8. Filter OpenBalance to values greater than 0.
9. Choose Home > Close & Load > Close & Load To, select Only Create Connection, and click OK. The choice applies to all four new queries.
10. Open Data > Queries & Connections, right-click OpenInvoices, choose Load To, and load it as a Table on a New worksheet, as in Tutorial 8.1, Step 1. Rename the new worksheet OpenInvoices.

Most invoices are fully paid and drop out, leaving about a thousand open invoices. A negative balance is also removed: the receipts and credit memos recorded on that invoice together exceed its total. Most of these invoices were paid in full, although not always before the credit memo, and a few were only partly paid. Charles River records the excess as a liability to the customer, in account 2060 Customer Deposits and Unapplied Cash, and in nearly every case refunds it later, so it is not a receivable.

Step 3. Age the open invoices.

1. Add a worksheet named Aging. In A1, type As-of date, and in B1, type 2026-12-31.

2. Click B1, type AsOfDate in the Name Box, and press Enter.

3. In F3:G8, type the bucket table in Table 8.2. Select it, choose Insert > Table, and name the Table Buckets in Table Design > Table Name.

4. In the OpenInvoices Table, add a column named DaysPastDue with the formula =AsOfDate-[@DueDate], formatted as a whole number.

5. Add a column named Bucket with this formula:

   =XLOOKUP([@DaysPastDue],Buckets[From],Buckets[Bucket],,-1)

Table 8.2: The aging buckets on the Aging worksheet

From	Bucket
-9999	Current
1	1-30 days
31	31-60 days
61	61-90 days
91	Over 90 days

The last argument of XLOOKUP, -1, returns the bucket whose From value is the largest one not above the days past due. An invoice that is not yet due has a negative number of days and falls in Current.

Step 4. Summarize the aging.

1. In A3:D3 of the Aging worksheet, type Bucket, Invoices, Open balance, and Share of balance, and type the five bucket names in A4:A8.

2. In B4 and C4, enter these two formulas, and copy them down to row 8:

   =COUNTIFS(OpenInvoices[Bucket],A4)
   =SUMIFS(OpenInvoices[OpenBalance],OpenInvoices[Bucket],A4)

3. In A9, type Total, and in B9 and C9, add the columns with SUM.

4. In D4, enter =C4/C$9, copy it down to row 8, and add column D with SUM in D9.

5. Sort the OpenInvoices Table by DaysPastDue from largest to smallest, and look at the oldest invoices.

Most of the open balance is current, and most of the rest is less than thirty days past due. The buckets beyond sixty days hold very little, and most of their invoices carry residual balances of a few cents or a few dollars left after the customer’s payment, which are candidates for a write-off under an approved small-balance policy rather than for collection. The aging describes only the invoices still open: invoices already paid have left it, so it shows what is owed at the year-end, not how promptly customers have paid.

Step 5. Reconcile the aging to the ledger. The accounts receivable account is 1020 Accounts Receivable, whose AccountID is 3. The query also keeps the two accounts where a provision for uncollectible receivables would appear: 1030 Allowance for Doubtful Accounts (AccountID 4) and 6170 Bad Debt Expense (AccountID 74).

1. Choose Data > Get Data > From File > From Excel Workbook, select CharlesRiver.xlsx, select the T3_GLEntry Table, and click Transform Data, not Load.
2. Name the query LedgerAR. Open the filter list of AccountID, choose Number Filters > Equals, and select Advanced.
3. Add three clauses joined by Or: AccountID equals 3, equals 4, and equals 74, and click OK.
4. Filter PostingDate to dates before 2027-01-01.
5. Use Home > Choose Columns to keep GLEntryID, PostingDate, AccountID, Debit, Credit, VoucherNumber, SourceDocumentType, and Description, which stay in that order.
6. Add a custom column Amount with the formula [Debit] - [Credit], and set its type to Decimal Number.
7. Choose Home > Close & Load, and rename the new worksheet LedgerAR.
8. On the Aging worksheet, type Open invoices (sub-ledger), Account 1020 (AccountID 3), and Difference in A11:A13.
9. In C11, enter =C9; in C12, enter =SUMIFS(LedgerAR[Amount],LedgerAR[AccountID],3); and in C13, enter =C12-C11.

The ledger balance is larger than the open invoices by a round amount, as Figure 8.5 shows. Both sides use the same cutoff, which rules out one cause of a difference, records on one side dated after the other ends, but not every timing item, such as a receipt recorded in the ledger before it was applied to its invoices. Step 6 traces the difference to its source.

Figure 8.5: The Receivables Aging Reconciled to the Ledger.

Step 6. Investigate the difference.

1. Select a cell in the LedgerAR Table, choose Insert > PivotTable, and place it on a new worksheet.
2. Drag AccountID and then SourceDocumentType to the Rows area.
3. Drag GLEntryID and Amount to the Values area, and set GLEntryID to Count in its Value Field Settings.
4. Double-click the Sum of Amount for JournalEntry. Excel lists the rows behind the value on a new worksheet, a feature called Show Details.

Figure 8.6 shows what the PivotTable reveals. Only account 1020 appears: in three years, Charles River has posted nothing to the allowance or to bad debt expense. Within account 1020, sales invoices increase the balance, and receipt applications and credit memos reduce it, all of them traceable to invoices. One journal entry line adds the rest: the accounts receivable opening balance in JE-2024-000001, dated January 1, 2024. No invoice in the extract stands behind that amount, and no receipt has been applied to it since, because every receipt application names an invoice. Whether customer detail for it exists elsewhere, in the records of the system Charles River used before, is the question the finding raises.

Figure 8.6: Accounts Receivable in the Ledger by Source Document.

Step 7. Select balances for confirmation. The largest customer balances are confirmed in full, and a random sample of the other customers represents the rest. The selection is made by customer, because a confirmation asks each customer for its whole balance.

1. Choose Data > Get Data > Launch Power Query Editor, right-click OpenInvoices in the Queries pane, choose Reference, and name the new query CustomerBalances.

2. Choose Transform > Group By, select Advanced, group by CustomerID, click Add grouping, and choose CustomerName.

3. Name the new column Balance, choose Sum of OpenBalance, and click OK.

4. Choose Home > Close & Load, and rename the new worksheet CustomerBalances.

5. Add a worksheet named Confirmations. Type Key item threshold in A1 and 50000 in B1, and Sample size in A2 and 20 in B2.

6. Type Key items in A4, and in A5, enter:

   =FILTER(CustomerBalances,CustomerBalances[Balance]>=B1)

7. Type Other customers in random order in E4, and in E5, enter:

   =SORTBY(FILTER(CustomerBalances,CustomerBalances[Balance]<B1),RANDARRAY(COUNTIF(CustomerBalances[Balance],"<"&B1)))

8. Type Random sample in I4, and in I5, enter =INDEX(E5#,SEQUENCE(B2),{1,2,3}), which returns the first B2 rows of the shuffled list.

9. Select the sample, from I5 to the last row it fills in column K, copy it, and choose Home > Paste > Paste Values without moving the selection.

About two dozen customers are key items, and together they hold about two-fifths of the open balance. The random sample gives each of the other customers the same chance of selection. Grouping by CustomerID as well as by name keeps apart the two different customers named Brown LLC. The list in column E reshuffles every time the workbook recalculates, and pasting values froze the sample in column I as the record of what was selected. The threshold and the sample size are choices for this tutorial; in an audit they follow from the risk and the materiality of the engagement. The selection covers the customer balances rebuilt from the invoices, not the unsupported opening balance, whose customer detail is needed before any procedure can be designed for it. It is also only the first part of the procedure: sending the requests, controlling the replies, and following up the customers who do not reply are the rest, and they lie outside the workbook.

Step 8. Record the findings.

1. Add a worksheet named WP Receivables, with the workpaper labels of Tutorial 8.1 in column A.
2. Record the population check (the number of open invoices and their total), the aging, and the reconciliation to account 1020.
3. Record the confirmation selection: the threshold, the sample size, the date of the selection, and the customers selected.
4. Record the unsupported opening balance as a finding: it has no invoice detail in the extract, and no receipt has been applied to it in three years. The follow-up is to request the customer-level detail behind it, from the records of the earlier system, and to assess whether any of it is still collectible.
5. Record the absence of an allowance as a finding to follow up. The credit loss standard of the Financial Accounting Standards Board (FASB) requires the estimate of expected credit losses to reflect the risk of loss even when it is remote, and an aging schedule is one of the methods it accepts (FASB, 2016); management’s own assessment, if it has one, is the evidence to request.
6. Record the small residual balances as a finding, to be written off under an approved small-balance policy or collected.
7. Save the workbook.

The workpaper now holds the evidence of the reconciliation, the plan for external confirmation, and three findings, each with its follow-up.

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.

The aging tells the credit department that most of the balance open at the year-end is not yet due. The reconciliation tells the auditor something more important: 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 the evidence behind them.

8.8 Journal Entry Testing

8.8.1 Management Override

Every control depends on the people who operate it, and the people at the top of an organization are in a position to override it. Management override of controls, the ability of managers to bypass controls that work for everyone else, is a risk in every organization, and journal entries are one of the main ways it can be carried out. Auditing standards therefore treat journal entries as a focus of every financial statement audit.

The PCAOB’s standard on fraud, AS 2401, observes that material misstatements due to fraud often involve recording inappropriate or unauthorized journal entries during the year or at the end of a period, or adjustments that never appear in a formal journal entry. It requires the auditor to test both for evidence of such misstatement, and it describes characteristics that fraudulent entries often have, among them:

• entries made to unusual or seldom-used accounts;
• entries made by individuals who typically do not make journal entries;
• entries recorded at the end of a period, or as post-closing entries after the period’s books have been closed, with little or no explanation;
• entries that contain round numbers or a consistent ending number.

The standard adds that the testing should ordinarily focus on the end of the period, while considering whether entries throughout the period also need testing (PCAOB, n.d.-e).

Table 8.3 translates these characteristics, and three further indicators used in practice, into tests on Charles River’s JournalEntry table. The table records who created each entry and when, who approved it and when, the date it posts to, its type, and its total. The Employee table adds each approver’s approval limit, MaxApprovalAmount. Fay and Negangard (2017) build their journal entry case around the same kinds of questions: who recorded an entry, when it was created, how it was described, and whether it was posted to an earlier date than the one on which it was made.

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

Two of the tests need a word of explanation. A backdated entry is one created on a later day than the date it posts to, so that it may change a period that has already been closed or reported. The flag tests only part of the standard’s characteristic: an entry created on the last day of a period is not flagged, and whether the books were already closed when an entry was made takes the company’s closing calendar, which the data do not hold. An entry approved by the person who created it defeats segregation of duties, the principle that no one person should both initiate and approve a transaction.

Digit tests can be applied to journal entries too, but with care. When Debreceny and Gray (2010) examined the journal entries of 29 organizations, the first digits of the amounts departed from Benford’s Law in every one of them, so a nonconforming digit test on journal entries is not unusual and does not by itself suggest fraud. Characteristics of the kind in Table 8.3 are more direct: each one identifies specific entries and a specific question to ask about them.

8.8.2 Risk Scores and Dispositions

Each flag in Table 8.3 is a TRUE or FALSE value, and adding the flags gives a risk score: the number of characteristics an entry has. A risk score is a way to rank entries for review, not a probability of fraud. An entry with one flag often has an ordinary explanation. An entry with several flags comes first in the review, although its flags can share one cause, as when an entry keyed on a Saturday after its posting date is both a weekend entry and a backdated one. The weights are a judgment, too. Adding the flags treats them as equally important, and an auditor may decide that some matter more.

Many flagged entries are false positives, entries that meet a test’s criteria but are legitimate, and how many depends on the test and the population. Nigrini (2019) classified the numbers used in fraud schemes by their patterns, such as round amounts, amounts just above or below control thresholds, and duplicates of authentic transactions. Nigrini emphasizes that the analysis should include refining and rerunning the tests, and grouping and filtering the results, to deal with false positives. The auditor records a disposition for every exception: expected, with the evidence that explains it; follow up, with the question to ask and the document to request; or a misstatement or a control deficiency, a weakness in the design or operation of a control that keeps it from preventing or detecting misstatements on time, to be reported. A test whose exceptions are mostly expected is refined, for example by excluding a recurring type of entry whose pattern has been explained and whose evidence has been examined, and rerun, as the dashed loop in Figure 8.1 shows. The workpaper keeps the original results, the entries excluded, and the reason, because an explained pattern clears only the entries whose evidence was examined.

8.9 Guided Tutorial 8.3: Testing Journal Entries for Management Override

Context and objective. In this tutorial, you will test every journal entry of fiscal 2024 through 2026 for the characteristics in Table 8.3. You will bring each approver’s authority limit onto the entries, add a flag for each characteristic, and combine the flags into a risk score. You will then separate the patterns that have an ordinary explanation from the entries that need follow-up, trace the highest-scoring entry to its ledger lines, and complete the workpaper.

Prerequisites. You need the AuditAnalytics.xlsx workbook from Tutorial 8.2. If you start here, download AuditAnalytics.xlsx as it stands at the end of Tutorial 8.2 from Companion Files, and point its queries at your copy of CharlesRiver.xlsx as that page explains.

Step-by-step instructions.

Step 1. Import the journal entries with their creators and approvers.

1. Choose Data > Get Data > From File > From Excel Workbook, and select CharlesRiver.xlsx.
2. In the Navigator, select the T2_JournalEntry, T74_Employee, and T1_Account Tables, and click Transform Data.
3. Rename the queries JournalEntries, Employees, and Accounts.
4. In Employees, keep EmployeeID, EmployeeName, JobTitle, and MaxApprovalAmount. In Accounts, keep AccountID, AccountNumber, and AccountName.
5. In JournalEntries, set the type of PostingDate to Date, and of CreatedDate and ApprovedDate to Date/Time.
6. Merge JournalEntries with Employees, matching ApprovedByEmployeeID with EmployeeID, with a left outer join. Expand EmployeeName, JobTitle, and MaxApprovalAmount without the prefix.
7. Rename the three new columns ApproverName, ApproverTitle, and ApproverLimit.
8. Merge JournalEntries with Employees again, matching CreatedByEmployeeID with EmployeeID. Expand EmployeeName and JobTitle without the prefix, and rename them CreatorName and CreatorTitle.
9. Choose Home > Close & Load > Close & Load To, select Only Create Connection, and click OK. The choice applies to all three new queries.
10. Open Data > Queries & Connections, right-click JournalEntries, choose Load To, and load it as a Table on a New worksheet, as in Tutorial 8.1, Step 1. Rename the new worksheet JournalEntries.

The JournalEntries Table holds several hundred entries, each with the names of the people who created and approved it and the approver’s limit.

Step 2. Add the five flags. Each flag is a calculated column that returns TRUE when the entry has the characteristic.

1. Add the five columns in Table 8.4 to the right of the JournalEntries Table, one at a time.
2. Check a few rows by hand. For example, an entry whose CreatedByEmployeeID equals its ApprovedByEmployeeID should show TRUE in SelfApproved.

Table 8.4: The flag columns of the JournalEntries Table

Column	Formula
Weekend	=WEEKDAY([@CreatedDate],2)>5
Backdated	=INT([@CreatedDate])>[@PostingDate]
SelfApproved	=[@CreatedByEmployeeID]=[@ApprovedByEmployeeID]
AboveLimit	=[@TotalAmount]>[@ApproverLimit]
RoundAmount	=MOD([@TotalAmount],1000)=0

WEEKDAY with the second argument 2 numbers the days from Monday (1) to Sunday (7), so values above 5 are Saturdays and Sundays. INT removes the time from CreatedDate, so Backdated is TRUE for an entry created on a later day than the date it posts to. ApproverLimit is the approver’s MaxApprovalAmount, and RoundAmount is TRUE for totals in exact thousands. The Employee table holds only each employee’s current limit, so AboveLimit assumes that the limit applied on the date of every entry; a history of the limits would be the evidence to request before calling an exception a breach.

Step 3. Combine the flags into a risk score.

1. Add a column named RiskScore with this formula, which adds the five flags because Excel counts TRUE as 1 in arithmetic:

   =[@Weekend]+[@Backdated]+[@SelfApproved]+[@AboveLimit]+[@RoundAmount]

2. Add a worksheet named JE Summary, and type the five flag names, Weekend, Backdated, SelfApproved, AboveLimit, and RoundAmount, in A1:A5.

3. In B1, enter =COUNTIF(JournalEntries[Weekend],TRUE), and in B2:B5, enter the same formula with the matching column.

4. In A8:A13, type the scores 0 to 5, and in B8, enter =COUNTIF(JournalEntries[RiskScore],A8), copied down to B13.

Most entries score 0. About two dozen have one flag, a handful have two, and a single entry has four.

Step 4. Separate the expected patterns. Some flags follow from how Charles River’s accounting works, and a PivotTable groups them so that you can explain each pattern once.

1. Insert a PivotTable from the JournalEntries Table on a new worksheet.
2. Drag CreatorTitle and then EntryType to the Rows area, and EntryNumber to the Values area, where Excel counts it because it is text.
3. Insert a second PivotTable from the same Table on a new worksheet.
4. Drag EntryType to the Rows area, RiskScore to the Columns area, and EntryNumber to the Values area.

The first PivotTable answers the question of who makes journal entries. The accounting manager creates most of them, the controller creates the monthly depreciation and the year-end closes, the production manager creates a monthly factory overhead entry, and the chief financial officer has created exactly one entry, the opening balance entry. The second PivotTable shows that some flags come in groups. Every Depreciation entry with a Weekend flag was created on the last day of a month that fell on a weekend, the date it posts to. The year-end closing entries all exceed their approver’s limit, because a closing entry carries a whole year’s revenue and expenses, or the year’s net income, and the Employee table records no approval limit above $250,000. The size of a close explains the flag, but not who may approve it. Request the closing-entry policy, the limits that applied at the time, and the evidence of the required review before deciding whether the exception is a gap in the policy or a failure to follow it.

Step 5. Record a disposition for each flagged entry.

1. Sort the JournalEntries Table by RiskScore from largest to smallest, as in Figure 8.7.
2. Filter RiskScore to values of 1 or more.
3. Copy the visible rows of the EntryNumber through RiskScore columns, and paste them on a new worksheet named Dispositions with Paste Special > Values.
4. Clear the filter on the JournalEntries Table.
5. On the Dispositions worksheet, add a Disposition column, and type a disposition for every entry: expected, with the evidence that explains it, or follow up, with the question to ask.

Pasting values freezes the list of exceptions as evidence of what the test found, and it keeps each disposition beside its entry: text typed next to a query’s output can end up beside a different row when the query is refreshed. The entries to follow up stand out in Figure 8.7. The opening balance entry has four flags: it was created by the chief financial officer on a Saturday, five days after the date it posts to, approved under the same employee ID, and far above that approver’s limit. Five other entries were created on the same kind of Saturday in the first week of January, after the date they post to. The accounting manager approved several rent entries and a utilities entry that the same person had created, and the two round debt reclassifications exceed the controller’s limit.

Figure 8.7: Journal Entries Sorted by Risk Score.

Step 6. Trace the highest-scoring entry to the ledger. The JournalEntry Table holds each entry’s header; its lines are in GLEntry.

1. Choose Data > Get Data > From File > From Excel Workbook, select CharlesRiver.xlsx, select the T3_GLEntry Table, and click Transform Data, not Load.

2. Name the query JournalLines, and filter SourceDocumentType to JournalEntry as the first step.

3. Merge JournalLines with Accounts on AccountID, with a left outer join, and expand AccountNumber and AccountName without the prefix.

4. Use Home > Choose Columns to keep VoucherNumber, AccountNumber, AccountName, Debit, and Credit, and drag the column headers into that order.

5. Choose Home > Close & Load, and rename the new worksheet JournalLines.

6. On the JE Summary worksheet, type Entry totals in A15, Ledger debits in A16, and Ledger credits in A17, and enter these three formulas in B15, B16, and B17:

   =SUM(JournalEntries[TotalAmount])
   =SUM(JournalLines[Debit])
   =SUM(JournalLines[Credit])

7. Add a worksheet named Drilldown. In A1, type Entry number, and in B1, type JE-2024-000001.

8. Type the headings AccountNumber, AccountName, Debit, and Credit in A3:D3, and in A4, enter:

   =FILTER(JournalLines[[AccountNumber]:[Credit]],JournalLines[VoucherNumber]=B1)

9. Type another entry number, such as JE-2026-000296, in B1 to see its lines, and then type JE-2024-000001 again.

B15, B16, and B17 agree: the entries’ headers carry the same total as the debits of their ledger lines, and the debits of those lines equal their credits, a control total for the population of journal entries. Agreement in total is not agreement entry by entry. A stronger test compares each entry’s own debits and credits with its header, and looks for headers without lines, lines without headers, and approvers who are missing from the Employee table. The formula in A4 spills the eighteen lines of the opening balance entry, the opening balances of Charles River’s balance sheet, as Figure 8.8 shows. One of them is the debit to 1020 Accounts Receivable that you could not match to any invoice in Tutorial 8.2. Two independent tests, the reconciliation of the receivables and the test of the journal entries, have led to the same entry, and that makes the finding stronger.

Figure 8.8: The Ledger Lines of the Opening Balance Entry.

Step 7. Complete the workpaper.

1. Add a worksheet named WP Journals, with the workpaper labels of Tutorial 8.1 in column A, and complete each entry as Figure 8.9 shows.
2. In the Exceptions and disposition entry, summarize the Dispositions worksheet: the patterns that are expected and the evidence for them, and the entries to follow up.
3. Write a conclusion that says what the tests found and what remains open.
4. Save the workbook.

The completed workpaper records the journal entry tests in a form that another auditor can review and rerun.

Figure 8.9: A Workpaper for the Journal Entry Tests.

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 journal entry tests explained the flags on the recurring entries by their timing, which still leaves their amounts and approvals to be checked against their schedules before the entries are cleared. They did find approval exceptions to follow up, and they led back to the opening balance entry from two directions. That entry, created after its date, approved by its own creator, and carrying balances that the detail does not support, is the first item for the audit committee.

8.10 Duplicate, Gap, and Threshold Tests

8.10.1 Duplicates and Near-Duplicates

Exercise 2.6 tested for exact duplicates: supplier invoice numbers and check numbers that appear twice for the same supplier. A duplicate payment does not always repeat a reference number, so auditors also look for near-duplicates: records that agree on the fields that matter but differ in one, such as the same supplier and amount on different dates. In Excel, COUNTIFS can flag every payment whose supplier and amount appear more than once, and adding the invoice as a third criterion separates a second payment of the same invoice from two invoices of the same amount.

At Charles River, about a hundred pairs of payments share a supplier and an amount on different dates, about a dozen of them within thirty days of each other, and every pair pays two different invoices. That gives each pair a likely ordinary explanation, two invoices of the same amount, which their purchase orders and receipts would confirm. The test cannot show on its own that the two invoices are separate obligations, or catch a bill entered twice under two invoice numbers. The test is still worth running, because a near-duplicate that pays the same invoice twice is exactly the error that an exact-duplicate test misses. Exercise 8.5 asks you to run it.

8.10.2 Gap Tests

Documents numbered by a system should form a continuous sequence, and a gap may mean a document that was deleted or never recorded. A quick test for each numbering series compares the number of documents with the range of their numbers: if the largest number minus the smallest, plus one, exceeds the count, numbers are missing. The comparison assumes that every number is used once, because a repeated number can hide a missing one, so check first that the numbers are unique. Charles River’s sales invoice, journal entry, purchase order, and cash receipt numbers are unique and run without gaps within each year, a clean result that the workpaper should record as carefully as an exception.

A gap test also needs a series that is meant to be continuous. Charles River’s check numbers follow the numbering of all its supplier payments, so the payments made by ACH or wire transfer leave thousands of gaps in the check sequence. Matched against the payment numbers, every gap is explained by a payment of another kind, except the numbers of the two checks that reused an earlier check number, the pairs you met in Exercise 2.6.

8.10.3 Threshold Tests

People who want to avoid a control often keep amounts just below the threshold that would trigger it, and amounts just above or below control thresholds are one of the patterns Nigrini (2019) found in fraud schemes. A threshold test counts the items in narrow bands just below and just above each threshold, such as the approval limits in the Employee table’s MaxApprovalAmount column, and looks for a concentration below the line. The same test extends to consecutive documents with the same requester and amount, which may be one purchase split to stay under a limit. Exercise 8.5 applies it to Charles River’s purchase requisitions.

8.10.4 Tests from Earlier Chapters

Several tests from earlier chapters belong in the same audit program, the planned set of procedures for an engagement. Exercise 2.5 found purchase requisitions converted to orders without an approver. Exercise 5.2 tested invoice numbers against invoice dates and found invoices whose number carries a different year from their date, and invoices dated before their orders. Exercise 5.5 matched supplier addresses against employee addresses, and Exercise 5.6 reviewed the supplier and item master files for suppliers that were never approved and items sold outside their active life. Each can point to a risk or give evidence about a control, so the workpaper states the control or financial statement question each one tests, with its population, its results, and a disposition for every exception.

TipIn Practice

Pricing is a control area where approval records and transactions can tell different stories. A promotion’s approval sets the dates when its discount may be used, and a price override request must be approved before an order is billed at the lower price. A price list, too, is valid only between its effective dates. Testing these controls means comparing the transactions with the approvals: the order dates of promotion lines with the promotion’s effective dates, the order dates of price-list lines with the list’s effective dates, and the prices billed with the status of the override requests. Exercise 8.6 carries out these tests on Charles River’s sales.

8.11 Documenting Audit Analytics

An audit test is only as useful as its documentation. The PCAOB’s standard on audit documentation requires enough information for an experienced auditor with no previous connection to the engagement to understand the work performed, the evidence obtained, and the conclusions reached, and to identify who performed and who reviewed the work, and when (PCAOB, n.d.-a). The Global Internal Audit Standards ask internal auditors for documentation from which another competent auditor could repeat the work and reach the same results (IIA, 2024). The documentation worksheet you built in Chapter 4 recorded the purpose, sources, parameters, and open questions of an analysis. An audit workpaper, like the one in Figure 8.9, adds what an auditor needs to rely on the analysis: the objective of the test, the reconciliation of the population to the ledger, the exceptions and their dispositions, and a conclusion.

Analytics makes two features of good documentation especially important. The first is reproducibility. A workbook whose queries import the data and whose formulas perform the tests can be refreshed and rerun, but only if the workpaper records which extract it used, its as-of date and every query filter that repeats that date, and the parameters of each test, such as the strata bounds, the aging buckets, and the approval limits. The second is the treatment of exceptions. A test that flags dozens of records is not complete until each one has a disposition and the evidence behind it, and a test that flags nothing is a result to be recorded, not a reason to omit the test.

NoteConnecting the Dots

The tests in this chapter rest on the skills of the earlier chapters: importing and filtering Tables with Power Query, merging tables on their keys, conditional aggregation, and PivotTables. They also reach the limits of a workbook. A test that compares every payment with every other payment, or every journal entry line with every account, becomes slow in a worksheet long before it becomes difficult in a database. The later Parts of the book return to the same tests with SQL, which can join and compare whole tables in a single query, and with interactive reports that let a reviewer move from a summary of exceptions to the transactions behind it.

8.12 Looking Ahead

This chapter has looked at the Charles River data as an auditor does. You reconciled populations to the ledger before testing them, stratified the supplier payments to see where the dollars are, and used a first-digit test to find a group of payments whose digits depart from Benford’s Law in a way that the payment process plausibly explains. You aged the receivables at the end of fiscal 2026, found that the ledger holds a balance that no invoice in the extract supports, and selected customer balances for confirmation. You tested every journal entry for characteristics of management override, ranked them with a risk score, and traced the highest-scoring entry to the same balance. Along the way, you recorded a disposition for every exception and documented each test in a workpaper.

This chapter completes Part II. The Part II Comprehensive Case that follows brings the techniques of all five Excel chapters together on a decision: whether Charles River should run its calendar of promotions again in 2027. Part III then turns from the workbook to the database, where the same questions can be asked of whole tables at once.

8.13 Chapter Summary

Internal and external auditors differ in purpose, but they rely more and more on the same analytics. Analytics lets them apply a test to every record in a population instead of a sample, although each test covers only what it looks for. Testing a whole population produces exceptions, and many of them are false positives. The auditor’s judgment therefore goes into designing each test, investigating each exception, and recording a disposition for it. The work follows a workflow: state the objective, reconcile the population to the ledger, run the tests, investigate the exceptions, record their dispositions, and document the whole in a workpaper.

Reconciling a population’s count and total to the general ledger checks that a test covers the records the ledger holds, although agreeing totals cannot show that each record is present. Stratification then shows where the value lies. At Charles River, most supplier payments are small, fewer than a thousand payments of $10,000 or more hold about a third of the value, and four payments are large enough to examine one by one. A first-digit test compares the payments’ leading digits with Benford’s Law, and the mean absolute deviation measures how closely they conform. The payments conform only marginally, and splitting them suggests why. Partial payments come only from invoices of $1,000 or more, a subset selected by size, so they do not conform; full payments and the invoices themselves do. A nonconforming digit test points to where to look. It does not show that anything is wrong, and an explanation for it does not show that each payment is genuine.

An aging schedule classifies open invoices by days past due at an as-of date. It supports the estimate of the allowance for credit losses, which the credit loss standard of the Financial Accounting Standards Board (FASB) requires to reflect the risk of loss even when it is remote. Most of Charles River’s open balance at the year-end is not yet due. The reconciliation of the aged invoices to the accounts receivable account, however, found a round difference: an opening balance, recorded in the first journal entry, that no invoice in the extract supports and no receipt has reduced. The ledger also shows no allowance in three years. Confirmations provide external evidence of the balances. In Tutorial 8.2, the selection takes the largest balances in full and a random sample of the rest, frozen with Paste Values so that it can be reviewed.

Journal entry testing looks for the characteristics that auditing standards associate with fraudulent entries. Examples are entries recorded after the period they affect, entries by people who do not usually make them, and round amounts, together with tests of approval. Flags for each characteristic add up to a risk score that ranks entries for review. Some flagged patterns have ordinary explanations, such as month-end depreciation on a weekend. Others, such as entries approved by their own creators, point to possible weaknesses in the approval controls. The highest-scoring entry was the opening balance entry, the same entry that the receivables reconciliation had found. Duplicate, near-duplicate, gap, and threshold tests add further tests to the audit program. A workpaper that records the population, parameters, exceptions, dispositions, and conclusion of each test makes the analytics reviewable and repeatable.

8.14 Key Terms

Aging schedule. A classification of the unpaid invoices at an as-of date by how long each is past its due date, in buckets such as current, 1 to 30 days, 31 to 60 days, 61 to 90 days, and over 90 days. It supports credit follow-up and the estimate of credit losses.

Allowance for credit losses. A valuation account, deducted from receivables, for the amount not expected to be collected. Under the credit loss standard of the Financial Accounting Standards Board (FASB), it reflects expected losses even when the risk of loss is remote, unless the expected nonpayment is zero, and may be estimated from an aging schedule. Charles River’s account for it is named Allowance for Doubtful Accounts, the older term.

Audit workpaper. The documentation of an audit test: its objective, source, population check, procedure, parameters, results, exceptions and their dispositions, conclusion, and the preparer and reviewer with their dates, recorded so that an experienced auditor with no connection to the engagement can understand and review the work.

Backdated entry. A journal entry created on a later day than the date it posts to, so that it may change a period that has already been closed or reported.

Benford’s Law. The observation that in many collections of numbers the first digit d occurs with a proportion of about LOG10(1+1/d), so that about 30 percent of the numbers begin with 1 and fewer than 5 percent with 9.

Confirmation. A response sent directly to the auditor by a third party, such as a customer asked to state its balance. For accounts receivable, the PCAOB’s standard on confirmation calls for confirmation or direct access to information held by a knowledgeable external source, and for other substantive procedures where neither is feasible.

Control deficiency. A weakness in the design or operation of a control that keeps it from preventing or detecting misstatements on time.

Disposition. The auditor’s conclusion about an exception: expected, with the evidence that explains it; follow up, with the question to ask or the document to request; or a misstatement or control deficiency to be reported.

Exception. A record that meets a test’s criteria and therefore needs an explanation, such as a payment above an approval limit or a journal entry created after its posting date. An exception is a reason to investigate, not proof of an error.

False positive. A record that meets a test’s criteria but turns out to be legitimate. Many exceptions from tests of whole populations are false positives, and the auditor’s work is to explain them and refine the tests.

First-digit test. A test that compares the proportion of amounts beginning with each digit from 1 to 9 with the proportions that Benford’s Law expects.

Gap test. A test of whether documents numbered in a sequence form a continuous series. A missing number may indicate a document that was deleted or never recorded, if the sequence is meant to be continuous.

Individually significant item. An item large enough, or risky enough, that the auditor examines it in full rather than accepting any sampling risk on it.

Journal entry testing. The identification and examination of journal entries with characteristics of inappropriate or unauthorized entries, required in financial statement audits because journal entries are a common means of management override.

Management override of controls. The ability of managers to bypass controls that operate effectively for everyone else, for example by recording or approving journal entries outside the normal process.

Mean absolute deviation (MAD). In a Benford analysis, the average of the absolute differences between the actual and expected proportions of each digit. Nigrini’s practical screening ranges classify a first-digit MAD from close conformity to nonconformity.

Near-duplicate. A record that agrees with another on the fields that matter, such as supplier and amount, but differs in one, such as the date. Near-duplicates can reveal duplicate payments that repeat no reference number.

Population. The complete set of records to which a test’s conclusion applies, such as every supplier payment dated in the period under review.

Reconciliation. A comparison of two balances that should agree, such as a ledger account and the detail behind it, that lists every reconciling item explaining the difference between them.

Reconciling item. An amount that explains a difference between two balances that should agree, such as a ledger account and the detail behind it. An item that cannot be explained is a finding in itself.

Risk score. The number of risk characteristics a record has, calculated by adding its flags. It ranks records for review; it is not a probability of fraud.

Sampling risk. The risk that the conclusion drawn from a sample differs from the conclusion the whole population would support. Examining individually significant items in full removes it for those items.

Segregation of duties. The principle that no one person should both initiate and approve a transaction, or both record and authorize it, which reduces the risk that one person’s error or fraud goes undetected.

Stratification. The division of a population into groups, or strata, usually by value, to show where the items and the value are concentrated and to direct audit attention.

Threshold test. A test that counts items in narrow bands just below and just above a control threshold, such as an approval limit, to look for amounts kept below it.

Tolerable misstatement. The largest misstatement in an account or class of transactions that, combined with misstatements elsewhere, would not make the financial statements materially misstated.

8.15 Multiple Choice Questions

1. Why should an auditor agree the count and total of an extracted population to the general ledger before testing it?

1. Because auditing standards prohibit testing any data that was not extracted by the auditor
2. Because the ledger contains more columns than the source tables
3. Because a test cannot find problems in records that were never extracted
4. Because the reconciliation replaces the need to test individual transactions

2. A stratification of supplier payments shows that about three-quarters of the payments are under $1,000 but hold less than a quarter of the value, and that four payments exceed $50,000. How should the auditor use this result?

1. Examine the largest payments one by one, and focus on the high-value strata.
2. Test only the payments under $1,000, because they are the most numerous.
3. Replace every other test of the payments with the stratification table.
4. Ignore the payments over $50,000, because there are too few of them to matter.

3. According to Benford’s Law, about what proportion of the amounts in a conforming population begin with the digit 1?

1. About 5 percent
2. About 11 percent, one-ninth of the amounts
3. About 50 percent
4. About 30 percent

4. Which formula returns the first significant digit of a Charles River payment amount in A2, including amounts below $1?

1. =LEFT(A2,1)
2. =--LEFT(TEXT(A2,"0.00000000E+00"),1)
3. =LEFT(TEXT(A2,"0"),1)
4. =--RIGHT(TEXT(A2,"0.00000000E+00"),1)

5. A first-digit test of about forty thousand payments has a mean absolute deviation (MAD) of 0.013, and a chi-square test calls the difference from Benford’s Law highly significant. What is the best interpretation?

1. The payments are fraudulent, because the chi-square test is significant.
2. The payments conform closely, because the MAD is below 0.015.
3. The test is invalid, because Benford’s Law applies only to populations of fewer than a thousand amounts.
4. Marginally acceptable conformity, and a population this large can make a small departure significant.

6. At Charles River, payments that cover only part of an invoice do not conform to Benford’s Law, while full payments do. What best explains the difference?

1. The partial payments were invented, because nonconformity proves that amounts were fabricated.
2. Only invoices of $1,000 or more are paid in parts, a subset selected by size.
3. Partial payments are recorded in a different currency from the full payments.
4. The formula that extracts the first digit does not work on partial payments.

7. Which of the following populations is the least suitable for a Benford analysis?

1. Supplier invoice totals
2. Customer invoice totals
3. Check numbers
4. Journal entry line amounts

8. An invoice due on November 15, 2026, is aged at an as-of date of December 31, 2026. The customer paid it in full on January 5, 2027. How should the aging at December 31, 2026, treat the invoice?

1. Include its full balance in the 31 to 60 days bucket.
2. Include it as current, because it was paid within 90 days.
3. Exclude it, because it has been paid in full.
4. Include it in the over 90 days bucket, because the payment was late.

9. The accounts receivable balance in the general ledger exceeds the total of the open invoices, and no reconciling item, such as a receipt not yet applied, explains the difference. What does this mean?

1. The ledger is always right, so the difference can safely be ignored.
2. The aging schedule has been calculated incorrectly and should be discarded.
3. The difference is the allowance for credit losses, which the ledger records.
4. The ledger holds unsupported amounts, or the detail is missing invoices.

10. Which statement about the allowance for credit losses under the credit loss standard of the Financial Accounting Standards Board (FASB) is correct?

1. An allowance is required only when a specific customer has declared bankruptcy or stopped paying.
2. Historical loss rates must be used as they are, without any adjustment.
3. The estimate must reflect even a remote risk, and aging-based methods are acceptable.
4. Trade receivables are outside the scope of the credit loss standard.

11. Which of the following is not one of the characteristics of fraudulent journal entries that the fraud standard of the Public Company Accounting Oversight Board (PCAOB) describes?

1. Entries made by individuals who typically do not make journal entries
2. Entries posted automatically by the accounting system for each sales invoice
3. Entries containing round numbers or a consistent ending number
4. Entries recorded at the end of a period or as post-closing entries with little explanation

12. In the JournalEntries Table, what does =INT([@CreatedDate])>[@PostingDate] return TRUE for?

1. Entries created on a later day than the date they post to
2. Entries whose total exceeds the approver’s limit
3. Entries created and approved by the same employee
4. Entries created on a Saturday or a Sunday, whatever their posting date

13. How should an auditor interpret a journal entry’s risk score, calculated as the number of flags the entry has?

1. As proof of override whenever the score is at least 2
2. As the probability that the entry is fraudulent
3. As a ranking for review, not a probability of fraud
4. As a replacement for examining supporting documents

14. An auditor selects customers for confirmation by sorting them with SORTBY on a column from RANDARRAY. Why should the selection be pasted as values as soon as it is made?

1. The sampling standard forbids formulas in audit workpapers
2. Paste Values makes the random numbers more evenly spread across the customers
3. SORTBY cannot sort a list that contains formulas
4. RANDARRAY recalculates, so the selection could not be reproduced

15. A gap test on Charles River’s check numbers finds thousands of missing numbers. What is the most likely explanation?

1. Checks share one numbering series with ACH and wire payments.
2. Thousands of checks were written and never recorded in the general ledger.
3. The gap test does not work on check numbers stored as text.
4. The missing checks were all voided at the end of each year.

8.16 Applied Exercises

Work on these exercises in a copy of AuditAnalytics.xlsx, made after you finish this chapter’s tutorials, and put each exercise on its own worksheets, named after it. Exercises 8.3 and 8.4 add columns to queries of the tutorials, which is safe in the copy; every other exercise imports the Tables it needs as new queries with the names it gives.

8.16.1 Financial Accounting Exercises

Exercise 8.1: Estimating the Allowance for Credit Losses

Table group: Order-to-Cash

Scenario. Tutorial 8.2 found that Charles River’s ledger shows no allowance for credit losses in three years. The controller asks you to estimate one at the end of fiscal 2026 from the aging schedule. The credit department proposes these loss rates for the buckets, which are an assumption for this exercise and not data from Charles River:

• current: 0.5 percent;
• 1 to 30 days past due: 2 percent;
• 31 to 60 days: 5 percent;
• 61 to 90 days: 15 percent;
• more than 90 days: 40 percent.

An update to the credit loss standard, effective for Charles River’s fiscal 2026, lets an entity elect to assume that the conditions at the balance sheet date do not change over the remaining life of its current trade receivables, and lets an entity other than a public business entity that makes that election also consider collections after the balance sheet date (FASB, 2025). This exercise assumes neither election.

Requirements.

1. Add a LossRate column to the Buckets Table of Tutorial 8.2 with the proposed rates, and calculate the expected credit loss of each bucket and in total on the Aging worksheet.

2. The unsupported opening balance of account 1020 is not part of the aging. Calculate the allowance again with that balance treated as more than 90 days past due, and explain why its age, and whether it should be in the aging at all, cannot be known from the data.

3. List the open invoices whose balance is less than $10 and total them. First show how the aging would change if all of them were written off. Then distinguish the old residual balances from any invoice that is not yet due, say which balances, if any, an approved small-balance policy would write off, and show the aging for that case separately.

4. Draft the adjusting journal entry that would record your recommended allowance, using accounts 6170 Bad Debt Expense and 1030 Allowance for Doubtful Accounts. Explain how the requirement of the credit loss standard of the Financial Accounting Standards Board (FASB) to reflect current conditions and reasonable and supportable forecasts would affect the proposed rates (FASB, 2016).

5. Write a memo of one half to one page to the controller that presents the estimate, the treatment of the opening balance, and the small-balance write-off.

Deliverable. The Aging worksheet with the loss rates and expected credit losses, the adjusting entry, and the memo.

Exercise 8.2: Reconciling Accounts Payable to the Ledger

Table group: Procure-to-Pay, with the GLEntry table from Accounting Core

Scenario. After the receivables finding, the chief audit executive asks you to perform the same reconciliation for accounts payable at the end of fiscal 2026: the unpaid supplier invoices against account 2010 Accounts Payable, whose AccountID is 22.

Requirements.

1. Import the T37_PurchaseInvoice Table again as a new query named OpenPayables, filtered to invoices dated before 2027-01-01. Create a query named PaidByInvoice that references the Payments query of Tutorial 8.1, which already holds the payments dated before 2027-01-01, and group it by PurchaseInvoiceID with the sum of Amount as PaidTotal. Merge PaidByInvoice into OpenPayables, replace the nulls with 0, and calculate each invoice’s open balance. Round it to 2 decimal places, as in Tutorial 8.2 Step 2, so that a fully paid invoice does not keep a residue of a fraction of a cent, and keep the invoices with a balance greater than zero.

2. Import the T3_GLEntry Table again as a new query named LedgerAP, clicking Transform Data, and filter it to AccountID 22 and to PostingDate before 2027-01-01. Calculate the balance of account 2010 at the end of fiscal 2026. For a liability account, the balance is the credits minus the debits.

3. Build a PivotTable of the account 2010 rows by SourceDocumentType, and use Show Details to list the JournalEntry rows. Identify each reconciling item between the ledger and the open invoices.

4. For each reconciling item, explain what it is and whether the correction belongs in the ledger, in the invoice detail, or in neither. Import the T33_PurchaseOrder Table as a query named PurchaseOrders, look up the purchase orders behind any invoices the reconciliation involves, and note who created and approved them.

5. Prepare a reconciliation workpaper that shows the ledger balance, the open invoices, each reconciling item with its explanation, and any unexplained difference.

Deliverable. A reconciliation workpaper for accounts payable at the end of fiscal 2026, with the PivotTable and the detail of each reconciling item.

8.16.2 Managerial Accounting Exercises

Exercise 8.3: Customer Credit Exposure

Table group: Order-to-Cash, with the Customer table from Master Data

Scenario. The credit manager has seen the aging and asks a different question: which customers owe the most, which of them exceed their credit limits, and how concentrated the receivables are. The answers will help decide where to tighten credit terms.

Requirements.

1. Add CustomerSegment and PaymentTerms to the columns kept in the Customers query of Tutorial 8.2, and select them in the step of the OpenInvoices query that expands Customers, so that they reach the OpenInvoices Table. Refresh, and build a PivotTable of the open balance by CustomerID and CustomerName, sorted from largest to smallest.

2. Add CreditLimit, which is already on OpenInvoices, to the PivotTable as the Max of CreditLimit, and beside the PivotTable, calculate each customer’s balance as a percentage of the limit. List the customers whose balance exceeds their limit, and for each one show how much of the balance is past due.

3. Calculate the share of the receivables held by the five and the ten largest customers, and summarize the open balance and the past-due balance by CustomerSegment and by PaymentTerms.

4. Write a memo of one half to one page to the credit manager that describes the concentration of the receivables, the customers over their limits, and the credit actions you would recommend, with the reasons for each.

Deliverable. A credit exposure worksheet with the customer ranking, the credit limit comparison, the concentration measures, and the memo.

Exercise 8.4: Supplier Concentration and Payment Timing

Table group: Procure-to-Pay

Scenario. The treasurer is reviewing Charles River’s working capital and asks two questions about the supplier payments of fiscal 2026: how concentrated the spending is among suppliers, and whether the company pays its suppliers early, on time, or late.

Requirements.

1. Add SupplierCategory to the columns kept in the Suppliers query and DueDate to those kept in the PurchaseInvoices query of Tutorial 8.1, and select them in the two steps of the Payments query that expand those queries. Refresh the Payments query. The Payments Table holds three fiscal years, so add a column FiscalYear with the formula =YEAR([@PaymentDate]). Build a PivotTable of the payments by SupplierName, with FiscalYear in the Filters area set to 2026, sorted from largest to smallest, and use Show Values As > % Running Total In to show the cumulative share of spending.

2. Report how many suppliers account for 50 percent, 80 percent, and 90 percent of the spending, and summarize the spending by SupplierCategory.

3. Add a column DaysAfterDue equal to the PaymentDate minus the invoice’s DueDate, and format it as a whole number, because Excel shows the difference of two dates as a date until you do. For fiscal 2026, count the payments made before, on, and after the due date, calculate the median days after due, and count the payments made more than 30 days late.

4. Write a memo of one half to one page to the treasurer on what the concentration and the timing mean for supplier risk and for cash management, including what the company gains and loses by paying early or late.

Deliverable. A worksheet with the supplier ranking, the concentration measures, the category summary, the payment timing analysis, and the memo.

8.16.3 Auditing Exercises

Exercise 8.5: Testing Purchasing and Payment Controls

Table group: Procure-to-Pay, with the Employee table from Master Data

Scenario. The journal entry tests of Tutorial 8.3 found exceptions in how entries are approved. The chief audit executive asks you to apply the same approach to purchasing and payments: the approval of purchase orders and supplier invoices, the amounts of purchase requisitions around an approval limit, and the duplicate and gap tests described in this chapter.

Requirements.

1. Import the T33_PurchaseOrder Table as a query named POApprovals, and import the T74_Employee Table again as a query named Approvers that keeps EmployeeID, EmployeeName, JobTitle, MaxApprovalAmount, and TerminationDate. Merge Approvers into POApprovals twice, once for the approver’s name, JobTitle, MaxApprovalAmount, and TerminationDate, and once for the creator’s name.

2. Add three flags and a risk score: SelfApproved when the creator and the approver are the same employee, AboveLimit when OrderTotal exceeds the approver’s limit, and AfterTermination when the approver’s TerminationDate is filled in and earlier than the OrderDate. Count the flagged orders and record a disposition for each one, as in Tutorial 8.3.

3. Import the T37_PurchaseInvoice Table again as a query named InvoiceApprovals, and merge in the approver’s name, JobTitle, and MaxApprovalAmount from Approvers. List the invoices approved by anyone other than the chief financial officer, flag those above the approver’s limit, and note whether each approver’s role belongs in the approval of supplier invoices.

4. Import the T32_PurchaseRequisition Table and add a RequisitionValue column equal to Quantity times EstimatedUnitCost. With COUNTIFS, count the requisitions in $50 bands from $4,800 to $5,199.99, and chart the counts. Examine the band just below $5,000: list its requisitions with their requesters, approvers, dates, and statuses, look for consecutive requisition numbers with the same value, and relate what you find to the approval limits in the Employee table and to Exercise 2.5.

5. Test the payments for near-duplicates. In the Payments Table of Tutorial 8.1, add a column SameAmount that is TRUE when another payment has the same SupplierID and Amount, and a column SameInvoice that is TRUE when another payment also has the same PurchaseInvoiceID:

=COUNTIFS([SupplierID],[@SupplierID],[Amount],[@Amount])>1
=COUNTIFS([SupplierID],[@SupplierID],[Amount],[@Amount],[PurchaseInvoiceID],[@PurchaseInvoiceID])>1

Count each flag, and explain what the difference between the two counts shows.

6. Run a gap test on the purchase order numbers. Add a column with the year of each PONumber, =--MID([@PONumber],4,4), and one with its sequence number, =--RIGHT([@PONumber],6). For each year, check that no sequence number repeats, compare the number of orders with the largest sequence number minus the smallest, plus one, and state whether any numbers are missing.

7. Write a memo of one half to one page that summarizes the exceptions, distinguishes possible weaknesses in the design of the approval controls from possible failures in their operation, names the evidence that would settle each, records the clean results of the duplicate and gap tests, and recommends follow-up.

Deliverable. The flagged purchase orders and invoices with their dispositions, the requisition band analysis with its chart, the duplicate and gap tests, and the memo.

Exercise 8.6: Testing Pricing Controls

Table group: Order-to-Cash

Scenario. Exercise 4.6 found price override requests still recorded as Pending, and the promotion analyses of Chapters 4 through 6 relied on the effective dates in the PromotionProgram Table without testing them. The price lists that set most prices have effective dates too. The chief audit executive asks you to test these pricing controls against the sales actually ordered and billed.

Requirements.

1. Import the T7_PromotionProgram Table as a query named Promotions, and add a flag for promotions whose EffectiveEndDate is on or before their EffectiveStartDate. List the flagged promotions with their approver and approval date.

2. Import the T17_SalesInvoiceLine, T16_SalesInvoice, and T9_SalesOrder Tables as queries named InvoiceLines, InvoiceHeaders, and Orders, and make sure Discount in InvoiceLines has the decimal number type, as in Tutorial 4.1 Step 6. For the invoice lines of the flagged promotions, bring in each line’s OrderDate from Orders through the SalesOrderID of InvoiceHeaders, and compare it with the promotion’s effective dates. By promotion, count the lines and total the discount, calculated as Quantity times UnitPrice times Discount.

3. Import the T8_PriceOverrideApproval Table and filter it to the Pending requests. Find the invoice lines of their SalesOrderLineIDs, and compare each line’s UnitPrice with the request’s ReferenceUnitPrice and ApprovedUnitPrice. Note the PricingMethod and PriceOverrideApprovalID recorded on the lines, and calculate the amount billed below the reference price.

4. Import the T5_PriceList, T6_PriceListLine, and T10_SalesOrderLine Tables. Flag the price lists whose Status is Expired or whose EffectiveEndDate is before their EffectiveStartDate. Bring each order line’s PriceListID through its PriceListLineID and its OrderDate from Orders, and count by price list and year the order lines dated after their list’s EffectiveEndDate. List any active price lists that no order line uses.

5. For the customers that have their own price list (ScopeType Customer), find the order lines whose PricingMethod is neither Customer Price List nor Approved Override. Compare each line’s UnitPrice with the customer’s own list and with the list the line cites, and explain what the comparison shows.

6. For each exception, decide whether it points to a weakness in the design of the control, a failure in its operation, or both, explain why, and name the evidence that would confirm the classification.

7. Write a memo of one half to one page to the sales manager and the controller that describes the exceptions, their amounts, and the changes you would recommend to the pricing controls.

Deliverable. The flagged promotions with their invoice lines, the pending override requests with the lines billed at their prices, the price-list tests, and the memo.

8.17 Further Reading

American Institute of Certified Public Accountants. (2026). Guide to audit data analytics. https://www.aicpa-cima.com/cpe-learning/publication/guide-to-audit-data-analytics The AICPA’s guide to the use of data analytics in audits. It includes practical considerations for assessing the reliability of the data that an analytic uses, the question behind every population check in this chapter.

Drake, P. D., & Nigrini, M. J. (2000). Computer assisted analytical procedures using Benford’s Law. Journal of Accounting Education, 18(2), 127–146. https://doi.org/10.1016/S0748-5751(00)00008-7 A teaching case in which you analyze a real accounts payable file with digit tests as an analytical procedure in an external audit, then graph and report the results. It applies three digit tests where Tutorial 8.1 applies one.

Fay, R. G., & Negangard, E. M. (2017). Manual journal entry testing: Data analytics and the risk of fraud. Journal of Accounting Education, 38, 37–49. https://doi.org/10.1016/j.jaccedu.2016.12.004 A teaching case in two phases: validating that a file of manual journal entries is complete, and then testing the entries for red flags of fraud, such as who recorded them, when they were created, their descriptions, and whether they were back-posted. It parallels Tutorial 8.3 with audit software instead of Excel.

Nigrini, M. J. (2012). Benford’s Law: Applications for forensic accounting, auditing, and fraud detection. Wiley. https://doi.org/10.1002/9781119203094 A book-length treatment of digit analysis in forensic accounting and auditing, and the source of the conformity ranges for the mean absolute deviation used in this chapter.

Nigrini, M. J. (2017). Audit sampling using Benford’s Law: A review of the literature with some new perspectives. Journal of Emerging Technologies in Accounting, 14(2), 29–46. https://doi.org/10.2308/jeta-51783 A review of the accounting applications of Benford’s Law that examines a proposed Benford-based sampling method, finds that it can produce large samples of questionable accuracy, and discusses which audit data are appropriate for digit analysis.

Nigrini, M. J. (2019). The patterns of the numbers used in occupational fraud schemes. Managerial Auditing Journal, 34(5), 606–626. https://doi.org/10.1108/MAJ-11-2017-1717 A classification of the numbers found in fraud cases: round amounts, amounts just above or below control thresholds, duplicates of authentic transactions, and outliers, among others. It connects each pattern to tests like those of this chapter and discusses how to deal with false positives.

Public Company Accounting Oversight Board. (n.d.-e). AS 2401: Consideration of fraud in a financial statement audit. https://pcaobus.org/oversight/standards/auditing-standards/details/AS2401 The auditing standard on fraud for audits of public companies. Its paragraphs on journal entries explain why auditors test them, the characteristics of fraudulent entries, and when testing should focus on the end of the period.

8.18 References

American Institute of Certified Public Accountants. (2021). Understanding the entity and its environment and assessing the risks of material misstatement (Statement on Auditing Standards No. 145). https://www.aicpa-cima.com/resources/download/aicpa-statement-on-auditing-standards-no-145

American Institute of Certified Public Accountants. (2026). Guide to audit data analytics. https://www.aicpa-cima.com/cpe-learning/publication/guide-to-audit-data-analytics

Benford, F. (1938). The law of anomalous numbers. Proceedings of the American Philosophical Society, 78(4), 551–572. https://www.jstor.org/stable/984802

Debreceny, R. S., & Gray, G. L. (2010). Data mining journal entries for fraud detection: An exploratory study. International Journal of Accounting Information Systems, 11(3), 157–181. https://doi.org/10.1016/j.accinf.2010.08.001

Fay, R. G., & Negangard, E. M. (2017). Manual journal entry testing: Data analytics and the risk of fraud. Journal of Accounting Education, 38, 37–49. https://doi.org/10.1016/j.jaccedu.2016.12.004

Financial Accounting Standards Board. (2016). Financial instruments—Credit losses (Topic 326): Measurement of credit losses on financial instruments (Accounting Standards Update No. 2016-13). https://storage.fasb.org/ASU%202016-13.pdf

Financial Accounting Standards Board. (2025). Financial instruments—Credit losses (Topic 326): Measurement of credit losses for accounts receivable and contract assets (Accounting Standards Update No. 2025-05). https://storage.fasb.org/ASU%202025-05.pdf

Institute of Internal Auditors. (2024). Global internal audit standards. https://www.theiia.org/en/standards/2024-standards/global-internal-audit-standards/

Newcomb, S. (1881). Note on the frequency of use of the different digits in natural numbers. American Journal of Mathematics, 4(1), 39–40. https://doi.org/10.2307/2369148

Nigrini, M. J. (2012). Benford’s Law: Applications for forensic accounting, auditing, and fraud detection. Wiley. https://doi.org/10.1002/9781119203094

Nigrini, M. J. (2017). Audit sampling using Benford’s Law: A review of the literature with some new perspectives. Journal of Emerging Technologies in Accounting, 14(2), 29–46. https://doi.org/10.2308/jeta-51783

Nigrini, M. J. (2019). The patterns of the numbers used in occupational fraud schemes. Managerial Auditing Journal, 34(5), 606–626. https://doi.org/10.1108/MAJ-11-2017-1717

Nigrini, M. J., & Mittermaier, L. J. (1997). The use of Benford’s Law as an aid in analytical procedures. Auditing: A Journal of Practice & Theory, 16(2), 52–67. https://doi.org/10.2308/ajpt-1302507

Public Company Accounting Oversight Board. (n.d.-a). AS 1215: Audit documentation. https://pcaobus.org/oversight/standards/auditing-standards/details/AS1215

Public Company Accounting Oversight Board. (n.d.-b). AS 2305: Substantive analytical procedures. https://pcaobus.org/oversight/standards/auditing-standards/details/AS2305

Public Company Accounting Oversight Board. (n.d.-c). AS 2310: The auditor’s use of confirmation. https://pcaobus.org/oversight/standards/auditing-standards/details/AS2310

Public Company Accounting Oversight Board. (n.d.-d). AS 2315: Audit sampling. https://pcaobus.org/oversight/standards/auditing-standards/details/AS2315

Public Company Accounting Oversight Board. (n.d.-e). AS 2401: Consideration of fraud in a financial statement audit. https://pcaobus.org/oversight/standards/auditing-standards/details/AS2401