12  Audit Analytics in SQL

Learning Objectives

After completing this chapter, you will be able to:

  1. Reconcile a population to the general ledger and profile it in SQL before testing it, and explain what a reconciliation can and cannot show.
  2. Trace ledger postings to their source documents and documents to the ledger with anti-joins, combine the tests into one list of exceptions, and test cutoff with the dates of the events behind revenue and payroll.
  3. Perform a three-way match of purchase orders, goods receipts, and supplier invoices, summing quantities to the purchase order line and applying a price tolerance.
  4. Test approvals across a business process against each approver’s limit, role, and employment dates, and measure how concentrated they are.
  5. Test the time, labor, payroll, and payment records behind a cost as one reconciled chain, recalculate the pay from the hours and rates, recognize patterns that the records cannot explain, and judge what the exceptions support.
  6. Organize audit queries into a documented library that can be rerun each period.
Chapter slides View slides Download PowerPoint

Opening Scenario

In Chapters 9 to 11 you worked as the controller’s staff accountant. This chapter puts you back on the internal audit team of Chapter 8. In practice the analyst and the auditor would be different people, because auditors do not test their own work, so read the analysis of Chapters 9 to 11 as the work of a colleague whose conclusions you must test. The audit committee has read it. The plant recorded about half as many hours again as the standard hours of its output. The change began in the middle of fiscal 2024, when indirect time appeared in the records and overtime on direct labor started to climb; since the middle of fiscal 2025 indirect time has been roughly flat, while direct hours and their overtime kept rising. Before it accepts that explanation, the committee wants to know whether the records behind it can be trusted. Were the hours worked, approved, recorded against the right operations, and paid correctly? And why does indirect time almost vanish from the records for four months of 2024? The chief audit executive has given you three assignments, all to be done in the database this time.

The first is to confirm that the ledger and its populations can be relied on, by reconciling them, tracing postings to their documents, and testing whether transactions were recorded in the right year. The second is to test the purchasing chain that supplies the plant, from order to receipt to invoice, and the approvals along it. The third is to test the time, labor, and payroll records behind the variance itself. The chief audit executive also wants the tests saved as a library of queries that the team can rerun every quarter. This chapter shows how to do all three, and how to turn the queries into a library.

12.1 From Samples to Populations

Chapter 8 tested Charles River’s payments, receivables, and journal entries in Excel. Each test brought one population into a workbook, reconciled it, and examined every row. SQL does the same work without importing anything, and it removes the limits that a workbook sets on how many tables a test can touch. A test that follows a purchase from its order through its receipt to its invoice, or a labor hour from the time clock through the payroll to the payment, joins five or six tables at once. In a workbook, each of those tables would have to be imported and merged, and a test that reaches across the whole process is exactly the kind that shows whether a control worked.

As Chapter 8 explained, testing an entire population rather than a sample changes what an exception means. A sample finds errors at a rate that the auditor projects to the population, while a population test finds the actual items, each of which needs a disposition, the auditor’s conclusion about it. As Chapter 1 noted, the Public Company Accounting Oversight Board (PCAOB) amended its standards in 2024 to clarify the auditor’s responsibilities when using technology-assisted analysis of this kind (PCAOB, 2024). Those standards govern the external audits of public companies and broker-dealers; internal auditors, like the team in this chapter, follow the Global Internal Audit Standards of the Institute of Internal Auditors (IIA, 2024). The power of a full-population test also raises the stakes of its inputs. A query that runs against an incomplete table, or that joins on keys that do not match, can report a clean result that means nothing. That is why every test in this chapter begins with a reconciliation.

12.2 Reconciling and Profiling Before Testing

An audit test is only as complete as the population it runs on. Before testing journal entries, you confirm that the entries in the JournalEntry table add up to the journal entry postings in the ledger. Before testing the payroll behind a cost, you confirm that the payroll registers add up to what the ledger charged. As Chapter 8 noted, the audit data analytics guide of the American Institute of Certified Public Accountants (AICPA) includes considerations for assessing the reliability of that data (AICPA, 2026).

In SQL, a reconciliation is usually two aggregate queries, one on the document table and one on the ledger, or a single query that joins their totals. The result should agree to the cent. When it does not, the difference is itself the first finding, and it must be explained before the population is used. In Exercises 6.1 and 10.1, the invoice lines of fiscal 2026 differed from the ledger by three invoices posted in the year after their date. In Chapter 8, the receivables ledger held an opening balance that no invoice supported. Either difference, left unexplained, would have undermined every test built on the population.

A profile says what the population holds. Chapter 10’s data profile (Table 10.3) counts a table’s rows and values, checks its keys and codes, and finds the dates it covers, in a query that takes a minute to write. For an audit test, it answers the questions that come before any exception: how many records there are, over which dates, and how many of them are incomplete or unapproved to begin with. Tutorial 12.3 begins by profiling the time records.

A reconciliation has a limit, though. It compares two records, so it cannot find what both of them left out. If the ledger and the payroll registers both record wages on the day they are paid, they agree to the cent even when the wages earned in the last weeks of a year are recorded in neither until the next. Tutorial 12.1 meets that limit.

NoteConnecting the Dots

Chapter 8’s workflow (Figure 8.1) began every test by defining and reconciling its population, and Chapter 4’s documentation worksheet recorded what an analysis rested on. In SQL, the population check becomes two aggregate queries, and the comment at the head of each test in the script plays the part of the documentation worksheet and of Chapter 8’s audit workpaper: it states what the test covers and what a clean result would be.

12.3 Tracing in Both Directions

The ledger’s source-document columns, which Chapter 3 introduced, allow a trace in two directions with the anti-joins of Chapter 10. From the ledger to the documents, the test asks whether every posting points to a document record that exists. An anti-join from GLEntry to the document table on SourceDocumentID finds the postings that point to nothing. From the documents to the ledger, the test asks whether every document was posted. An anti-join from the document table to GLEntry finds the documents with no posting. The first test addresses the existence of what the ledger records (for transactions, their occurrence), as far as a document record can support it, and the second its completeness, as Figure 12.1 shows. The completeness test can find only documents that were recorded but not posted: a transaction with no document at all escapes it.

The general ledger on the left and the source document tables on the right, linked by SourceDocumentType and SourceDocumentID. An arrow from the ledger to the documents is labeled existence: an anti-join finds postings whose document does not exist. An arrow from the documents to the ledger is labeled completeness: an anti-join finds documents that were never posted. A third test, cutoff, compares the date of the posting that first records a transaction with the date of the event behind it: the delivery date of the goods a sales invoice bills, when control passes on delivery, and the work dates of the pay period a payroll pays.
Figure 12.1: Tracing in Both Directions Between the Ledger and Its Documents.

The two directions often need several queries, one for each kind of document. UNION ALL stacks the results of queries with the same columns into one result, so the exceptions of every test can be listed together, each labeled with the test that found it:

SELECT 'Posting with no payment' AS Test, gl.GLEntryID AS RecordID
FROM GLEntry AS gl
    LEFT JOIN PayrollPayment AS pp
        ON pp.PayrollPaymentID = gl.SourceDocumentID
WHERE gl.SourceDocumentType = 'PayrollPayment'
    AND pp.PayrollPaymentID IS NULL
UNION ALL
SELECT 'Invoice with no posting', si.SalesInvoiceID
FROM SalesInvoice AS si
    LEFT JOIN GLEntry AS gl ON gl.SourceDocumentType = 'SalesInvoice'
        AND gl.SourceDocumentID = si.SalesInvoiceID
WHERE gl.GLEntryID IS NULL;

UNION without ALL also removes duplicate rows, which is rarely what an exceptions list should do.

WarningWatch out

UNION ALL matches the columns of its queries by position, not by name, and the result takes its column names from the first query. A second query whose columns are in a different order stacks its values under the wrong headings, and no error says so. An ORDER BY sorts the combined result, so it goes once, at the end, after the last query.

A third test uses the same columns to test cutoff: whether each transaction was recorded in the period in which it happened. The ledger’s PostingDate says when a transaction was recorded, and the event behind it has a date of its own. Revenue from goods belongs to the period in which control of them passed to the customer, which depends on the terms of each sale. This chapter assumes that control passes on delivery, so a revenue cutoff test compares the posting date with the delivery date of the shipments that the invoice bills. It also reads Shipment.DeliveryDate as the date on which the goods arrived, one of the two readings that Chapter 2 weighed, rather than as an expected date. The invoice date is a second check: under a policy of dating an invoice no earlier than its goods ship, which the chapter also assumes, an invoice dated before its shipment needs correction or an explanation. Revenue from services, which ship nothing, needs a test of its own, against the dates on which the work was done. Wages belong to the period in which the work was done, so a payroll cutoff test compares the dates of the pay period’s work with the pay date on which the payroll was posted. A company that records transactions when it invoices or pays them, rather than when it earns or incurs them, needs an accrual at each period end for the difference; without one, each year-end is misstated by the transactions on either side of it.

NoteConnecting the Dots

Chapter 1 introduced source-to-ledger traceability, and Tutorial 3.2 followed one sale, SI-2025-007947, from its order to its ledger rows. The trace tests of this chapter do the same for whole tables at once: from the ledger to two kinds of payment, and from two kinds of invoice to the ledger. Chapter 10’s In Practice callout named them as anti-joins, and Exercise 10.5 found the six PayrollPayment postings that point to no payment; Tutorial 12.1 explains them.

12.4 Guided Tutorial 12.1: Reconciling the Populations, Tracing the Ledger, and Testing Cutoff

Context and objective. Before testing any process, the audit team must know that the ledger and its populations can be relied on. In this tutorial, you will reconcile the journal entries and the manufacturing payroll to the ledger, trace postings to their documents and documents to their postings, and explain the exceptions you find. You will then test the cutoff of revenue and of payroll at each year-end.

Prerequisites. Complete the tutorials of the earlier Part III chapters, and open CharlesRiver_Work.sqlite in DB Browser for SQLite on the Execute SQL tab. If Chapter11.sql is still open in the editor, click New tab (Ctrl+T) for an empty editor tab. Start a new script for the audit query library with the header comment below, with your own name and the date, and click Save SQL file to save it as Audit.sql beside your working copy. As in Chapters 9 to 11, type each test at the end of the script, under its comment, and run it by selecting it. The first line of each comment names the test, and the second states its population and the result expected if the controls worked. If you start here, you need only the working copy, made as Step 1 of Tutorial 9.1 describes.

/* Audit.sql: tests of the records behind the manufacturing variance
   Database: CharlesRiver_Work.sqlite (a copy of CharlesRiver.sqlite)
   Prepared by: your name, date; reviewed by: name, date
   Checks: listed at the end of Tutorial 12.3 */

Step-by-step instructions.

Step 1. Reconcile the journal entries to the ledger. Scalar subqueries in the SELECT list put the totals of two tables side by side, as in Tutorial 10.2.

  1. Type and run the test below.

    -- Tutorial 12.1, test L1: journal entries against the ledger
-- Population: all journal entries; expected: counts and totals agree
SELECT
    (SELECT COUNT(*) FROM JournalEntry) AS Entries,
    (SELECT ROUND(SUM(TotalAmount), 2) FROM JournalEntry) AS EntryTotal,
    (SELECT COUNT(DISTINCT SourceDocumentID) FROM GLEntry
        WHERE SourceDocumentType = 'JournalEntry') AS PostedEntries,
    (SELECT ROUND(SUM(Debit), 2) FROM GLEntry
        WHERE SourceDocumentType = 'JournalEntry') AS PostedDebits;

  2. Compare the first two columns with the last two.

The number of entries and their total agree with the entries and the debits posted to the ledger, as Tutorial 8.3 found in Excel. The agreement supports the completeness of the population with respect to the ledger, at the level of counts and totals. As Tutorial 4.1, Step 10 showed, differences that offset would leave the totals equal. An anti-join like those of Step 3 finds an entry missing from one side, but an entry posted at the wrong amount still has its match, so only a comparison of each entry’s total with its own postings would find such differences.

Step 2. Reconcile the manufacturing payroll to the ledger. The payroll registers state what each employee was paid and what the company paid on top, and GLEntry records the cost center of each posting.

  1. Type and run the test below.

    -- Tutorial 12.1, test L2: manufacturing payroll against the ledger
-- Population: Manufacturing registers; expected: no difference
SELECT reg.FiscalYear, reg.RegisterCost, gl.LedgerCost,
    ROUND(reg.RegisterCost - gl.LedgerCost, 2) AS Difference
FROM (
    SELECT pp.FiscalYear,
        ROUND(SUM(pr.GrossPay + pr.EmployerPayrollTax
            + pr.EmployerBenefits), 2) AS RegisterCost
    FROM PayrollRegister AS pr
        INNER JOIN PayrollPeriod AS pp
            ON pp.PayrollPeriodID = pr.PayrollPeriodID
        INNER JOIN CostCenter AS cc ON cc.CostCenterID = pr.CostCenterID
    WHERE cc.CostCenterName = 'Manufacturing'
    GROUP BY pp.FiscalYear
) AS reg
    INNER JOIN (
        SELECT gl.FiscalYear, ROUND(SUM(gl.Debit), 2) AS LedgerCost
        FROM GLEntry AS gl
            INNER JOIN CostCenter AS cc
                ON cc.CostCenterID = gl.CostCenterID
        WHERE gl.SourceDocumentType = 'PayrollSummary'
            AND cc.CostCenterName = 'Manufacturing'
        GROUP BY gl.FiscalYear
    ) AS gl ON gl.FiscalYear = reg.FiscalYear
ORDER BY reg.FiscalYear;

  2. Read the Difference column.

The registers of the manufacturing cost center agree with the ledger’s payroll postings to the cent in each year. The ledger side counts the postings to every account, not only account 1090: the manufacturing part of the first payroll of 2024 was posted to the variance account, as Tutorial 11.2 noted, so a comparison with account 1090 alone would differ in that year. Both sides record payroll on its pay date, so the agreement shows that the registers and the ledger record the same manufacturing payroll in each year. It cannot show whether wages earned but not yet paid were recorded, the question of Steps 7 and 8.

Step 3. List the trace exceptions in both directions. Each test is an anti-join, and UNION ALL stacks their results into one list, labeled by test.

  1. Type and run the test below.

    -- Tutorial 12.1, test L3: postings and documents traced both ways
-- Population: four document types; expected: no rows
SELECT 'Posting with no payroll payment' AS Test,
    gl.GLEntryID AS RecordID, gl.SourceDocumentID AS DocumentID,
    gl.PostingDate AS RecordDate, gl.Debit + gl.Credit AS Amount
FROM GLEntry AS gl
    LEFT JOIN PayrollPayment AS pp
        ON pp.PayrollPaymentID = gl.SourceDocumentID
WHERE gl.SourceDocumentType = 'PayrollPayment'
    AND pp.PayrollPaymentID IS NULL
UNION ALL
SELECT 'Posting with no supplier payment', gl.GLEntryID,
    gl.SourceDocumentID, gl.PostingDate, gl.Debit + gl.Credit
FROM GLEntry AS gl
    LEFT JOIN DisbursementPayment AS dp
        ON dp.DisbursementID = gl.SourceDocumentID
WHERE gl.SourceDocumentType = 'DisbursementPayment'
    AND dp.DisbursementID IS NULL
UNION ALL
SELECT 'Sales invoice never posted', si.SalesInvoiceID,
    si.SalesInvoiceID, si.InvoiceDate, si.GrandTotal
FROM SalesInvoice AS si
    LEFT JOIN GLEntry AS gl ON gl.SourceDocumentType = 'SalesInvoice'
        AND gl.SourceDocumentID = si.SalesInvoiceID
WHERE gl.GLEntryID IS NULL
UNION ALL
SELECT 'Supplier invoice never posted', pi.PurchaseInvoiceID,
    pi.PurchaseInvoiceID, pi.InvoiceDate, pi.GrandTotal
FROM PurchaseInvoice AS pi
    LEFT JOIN GLEntry AS gl ON gl.SourceDocumentType = 'PurchaseInvoice'
        AND gl.SourceDocumentID = pi.PurchaseInvoiceID
WHERE gl.GLEntryID IS NULL;

  2. Read the Test column of each row in the result.

The result, shown in Figure 12.2, holds six rows, all from the first test: the postings that Exercise 10.5 found. DocumentID shows the SourceDocumentID that each posting points to, and for an invoice it repeats the invoice’s own ID. The six ledger rows point to three payroll payments that do not exist in the PayrollPayment table, in three pairs that share a DocumentID, each a debit to accrued payroll and a credit to cash, in January of each year. The other three tests return no rows. Every supplier payment in the ledger has its document, and every sales and supplier invoice was posted. Each kind of document is traced in one direction only. An anti-join tests only that a matching ID exists, so a document posted at the wrong amount would pass it, and a full trace would run both directions, and compare the amounts, for every kind of document the ledger records.

A mock of the Execute SQL editor and its result. The editor, on the tab Audit.sql, shows the start of a test that combines four anti-joins with UNION ALL: ledger postings with no payroll payment, ledger postings with no supplier payment, sales invoices never posted, and supplier invoices never posted. The result grid holds six rows, all labeled Posting with no payroll payment, three pairs of equal amounts dated in January 2024, January 2025, and January 2026, each pair pointing to the same missing payment. The Test column is outlined.
Figure 12.2: The Trace Exceptions in One List.

Step 4. Explain the six postings. In this data, a payroll payment carries the same ID as the payroll register it pays, so the missing payments point to their registers. CostCenter appears twice in the test, as ec for the employee’s own cost center and as rc for the register’s, so that each row can show both.

  1. Type and run the test below.

    -- Tutorial 12.1, test L4: approved registers with no payment
-- Population: all payroll registers; expected: no rows
SELECT pr.PayrollRegisterID, e.JobTitle,
    ec.CostCenterName AS EmployeeCenter,
    rc.CostCenterName AS RegisterCenter, e.TerminationDate,
    pp.PayDate, pr.GrossPay, pr.NetPay, pr.Status
FROM PayrollRegister AS pr
    INNER JOIN PayrollPeriod AS pp
        ON pp.PayrollPeriodID = pr.PayrollPeriodID
    INNER JOIN Employee AS e ON e.EmployeeID = pr.EmployeeID
    INNER JOIN CostCenter AS ec ON ec.CostCenterID = e.CostCenterID
    INNER JOIN CostCenter AS rc ON rc.CostCenterID = pr.CostCenterID
    LEFT JOIN PayrollPayment AS pay
        ON pay.PayrollRegisterID = pr.PayrollRegisterID
WHERE pay.PayrollPaymentID IS NULL;

  2. Compare the register IDs and net pay with the DocumentID and amounts of the six postings.

  3. Compare each register’s cost center with its employee’s.

Three approved registers have no payment record, and their IDs and net pay match the six postings: the ledger recorded the cash, but the payment documents are missing. The first register pays the chief executive officer. The other two pay an Account Executive of the Sales cost center, terminated in April 2024, in January 2025 and January 2026, and they are charged to the Executive cost center at the same gross pay as the chief executive’s register. A credit to cash does not say who received the money, so the bank records must confirm the payee. Exercise 12.5 follows the employee further.

Step 5. Test the cutoff of revenue. With control of the goods assumed to pass on delivery, revenue belongs to the fiscal year in which the goods were delivered, so the test compares each invoice’s posting date with the delivery date of the shipments it bills, and uses the invoice date as a second check. The joins to the shipments keep only the invoices for goods: the invoices for design services ship nothing, so they are left out here and need a cutoff test of their own. Read the test from the top: Shipped finds the first shipment date and the last delivery date of the goods each invoice bills, Posted finds the date on which each invoice was posted, Classified gives each invoice a finding with CASE, and the final query counts the exceptions.

  1. Type and run the test below.

    -- Tutorial 12.1, test L5: revenue posted in the year of delivery
-- Population: invoices for shipped goods; expected: no rows
WITH Shipped AS (
    SELECT sil.SalesInvoiceID, MIN(s.ShipmentDate) AS ShipmentDate,
        MAX(s.DeliveryDate) AS DeliveryDate
    FROM SalesInvoiceLine AS sil
        INNER JOIN ShipmentLine AS sl
            ON sl.ShipmentLineID = sil.ShipmentLineID
        INNER JOIN Shipment AS s ON s.ShipmentID = sl.ShipmentID
    GROUP BY sil.SalesInvoiceID
),
Posted AS (
    SELECT SourceDocumentID AS SalesInvoiceID,
        MIN(PostingDate) AS PostingDate
    FROM GLEntry
    WHERE SourceDocumentType = 'SalesInvoice'
    GROUP BY SourceDocumentID
),
Classified AS (
    SELECT si.SubTotal,
        strftime('%Y', sh.DeliveryDate) AS DeliveryYear,
        CASE
            WHEN strftime('%Y', p.PostingDate)
                    <> strftime('%Y', sh.DeliveryDate)
                THEN 'Revenue posted in another year'
            WHEN si.InvoiceDate < sh.ShipmentDate
                THEN 'Invoice dated before shipment'
            ELSE 'In the year of delivery'
        END AS Finding
    FROM SalesInvoice AS si
        INNER JOIN Shipped AS sh
            ON sh.SalesInvoiceID = si.SalesInvoiceID
        INNER JOIN Posted AS p ON p.SalesInvoiceID = si.SalesInvoiceID
)
SELECT Finding, DeliveryYear, COUNT(*) AS Invoices,
    ROUND(SUM(SubTotal), 2) AS SubTotal
FROM Classified
WHERE Finding <> 'In the year of delivery'
GROUP BY Finding, DeliveryYear
ORDER BY Finding, DeliveryYear;

  2. Read the rows of each finding.

The result, shown in Figure 12.3, has two findings. More than twenty invoices for goods delivered in December were posted in January, so their revenue was recorded in the next fiscal year, while the cost of the goods was recorded when they shipped. Eight invoices are dated before their shipment, the kind of error that Tutorial 2.1 and Exercise 5.2 found, and were posted in the year of delivery, so they are failures in the dating of invoices, not in the posting of revenue. A ninth invoice dated before its shipment was also posted in the next year; the CASE gives each invoice the first finding whose condition it meets, so it counts that invoice only as revenue posted in another year. Exercise 12.1 lists all nine.

A mock of the Execute SQL editor and its result. The editor, on the tab Audit.sql, is scrolled to the part of a test that classifies each sales invoice with CASE: revenue posted in another year than the delivery of its goods, invoice dated before shipment, or in the year of delivery, and counts the exceptions by finding and delivery year. The result has five rows: invoices dated before shipment in each of the three years, and invoices whose revenue was posted in another year for deliveries of 2024 and 2025. The rows of revenue posted in another year are outlined.
Figure 12.3: Revenue Posted Outside the Year of Delivery.

Step 6. Find the shipments never invoiced. An anti-join from the shipment lines to the invoice lines finds the goods that were shipped but not billed.

  1. Type and run the test below.

    -- Tutorial 12.1, test L6: shipments never invoiced
-- Population: all shipment lines; expected: no rows
SELECT s.ShipmentNumber, s.ShipmentDate, s.DeliveryDate,
    ROUND(sl.ExtendedStandardCost, 2) AS StandardCost,
    ROUND(sl.QuantityShipped * sol.UnitPrice * (1 - sol.Discount), 2)
        AS SalesValue
FROM ShipmentLine AS sl
    INNER JOIN Shipment AS s ON s.ShipmentID = sl.ShipmentID
    INNER JOIN SalesOrderLine AS sol
        ON sol.SalesOrderLineID = sl.SalesOrderLineID
    LEFT JOIN SalesInvoiceLine AS sil
        ON sil.ShipmentLineID = sl.ShipmentLineID
WHERE sil.SalesInvoiceLineID IS NULL
ORDER BY s.ShipmentDate;

  2. Read the dates and the two values of each row.

A handful of shipment lines of December 2026 have no invoice by the end of the data. Their cost is in the ledger at the year-end, and their revenue is not, the same timing as the invoices of Step 5. Exercise 12.1 measures the effect of both on each year.

Step 7. List the pay periods that cross a year-end. Wages belong to the period in which the work was done, but, as Tutorial 11.2 found, Charles River records payroll on its pay date, so a period whose work begins in one fiscal year and is paid in the next moves cost between the years.

  1. Type and run the test below.

    -- Tutorial 12.1, test L7: pay periods that cross a year-end
-- Population: all pay periods; expected: every period processed
SELECT pp.PeriodNumber, pp.PeriodStartDate, pp.PeriodEndDate,
    pp.PayDate, pp.Status, COALESCE(reg.Registers, 0) AS Registers,
    reg.PayrollCost
FROM PayrollPeriod AS pp
    LEFT JOIN (
        SELECT PayrollPeriodID, COUNT(*) AS Registers,
            ROUND(SUM(GrossPay + EmployerPayrollTax
                + EmployerBenefits), 2) AS PayrollCost
        FROM PayrollRegister
        GROUP BY PayrollPeriodID
    ) AS reg ON reg.PayrollPeriodID = pp.PayrollPeriodID
WHERE strftime('%Y', pp.PeriodStartDate) <> strftime('%Y', pp.PayDate)
ORDER BY pp.PeriodStartDate;

  2. Read the dates, the Status, and the PayrollCost of each period.

At each year-end, the work of the last weeks of December was paid, and recorded, in January of the next fiscal year. At the end of fiscal 2026, the last two periods are still Open, with no registers, so the wages of late December 2026 are recorded nowhere.

Step 8. Look for an accrual of the unpaid wages. An accrual at the year-end would record the wages earned but not yet paid in account 2030 Accrued Payroll.

  1. Type and run the test below.

    -- Tutorial 12.1, test L8: accrued payroll at the year-end
-- Population: account 2030 to 2026-12-31; expected: unpaid wages
SELECT gl.SourceDocumentType, COUNT(*) AS Postings,
    ROUND(SUM(gl.Credit) - SUM(gl.Debit), 2) AS Balance
FROM GLEntry AS gl
    INNER JOIN Account AS a ON a.AccountID = gl.AccountID
WHERE a.AccountNumber = 2030 AND gl.PostingDate <= '2026-12-31'
GROUP BY gl.SourceDocumentType;

  2. Read the Balance of each source.

At the end of fiscal 2026, the balance of account 2030 is the opening entry of 2024: the only journal entry posted to the account is that opening line, and the payroll summaries and payments net to zero. No journal entry accrued the wages earned but not yet paid, and Exercise 12.1 finds the same balance at each earlier year-end. Each year’s payroll cost follows its pay dates, and the liability at each year-end is understated by a little more than one pay period’s payroll, a few hundred thousand dollars. Exercise 12.1 estimates the amounts.

Checkpoint. Check that:

  • the journal entries and the manufacturing payroll reconcile to the ledger to the cent;
  • the combined trace returns six postings with no payroll payment and nothing else;
  • the six postings match three approved registers without payments, two of them for a terminated employee, charged to the Executive cost center;
  • the revenue of December deliveries posted in January fell in the next fiscal year, and a few December 2026 shipments were never invoiced;
  • account 2030 holds only its opening balance, with no accrual of the wages of late December;
  • Audit.sql holds the header and the eight tests of this tutorial, each under its comment.

Within the populations tested, the ledger holds up: the journal entries and the manufacturing payroll reconcile to it, and the traces found only the six postings of three missing payments. Its weakness is timing. Revenue and payroll are recorded when they are invoiced and paid, not when they are earned, so each year-end is misstated by the transactions on either side of it, and the wages of each December’s last weeks, manufacturing wages among them, are charged to the next year.

12.5 The Three-Way Match

A purchase leaves three documents: the purchase order that authorized it, the goods receipt that recorded what arrived, and the supplier invoice that billed for it. The three-way match compares them. A supplier should not be paid for more than was ordered and received, or at a price above the one the order approved. Chapter 1 named the three-way match as one of the tests that the Procure-to-Pay tables support (Table 1.3), and in SQL it becomes a comparison of totals at the level of the purchase order line.

The level matters. A purchase order line may be received in several deliveries and invoiced on several invoices, so comparing a single receipt with a single invoice line, row by row, reports a mismatch wherever a delivery was split. The comparison must first sum the quantities received and the quantities invoiced for each purchase order line, in CTEs, and then compare the sums with the quantity ordered, as Figure 12.4 shows. A line is fully matched when the three quantities agree. Other outcomes include a line not yet received, a line partly received, and a line received but not yet fully invoiced. A line invoiced above what was received, or received above what was ordered, is an exception. The quantities are REAL numbers, and a sum of fractional quantities can differ from the quantity ordered by a tiny rounding amount, so the match treats two quantities as equal when they differ by less than a small tolerance, 0.0001, rather than testing them for exact equality.

Prices are compared the same way, with the invoice’s unit cost against the order’s. Some difference is normal: the purchase price variance of Exercise 9.2 records it. A tolerance, such as two or three percent, separates ordinary differences from exceptions to be investigated. The goods received but not yet invoiced at a year-end are not exceptions either. They are a liability that the company should have accrued, and their total should agree with the ledger account that records it.

A purchase order line from Charles River at the top, with its ordered quantity and unit cost. On the left, its two goods receipt lines are summed into a received quantity; on the right, its three supplier invoice lines are summed into an invoiced quantity, and each invoice price is compared with the order price. The three quantities agree, so the line is matched, although one receipt line was billed on two invoices. Below, a table lists the possible statuses: matched; not yet received; partly received; received but not yet fully invoiced, which is an accrual; and two exceptions, invoiced above received and received above ordered.
Figure 12.4: The Three-Way Match at the Level of the Purchase Order Line.
NoteConnecting the Dots

Summing before comparing is Chapter 10’s cure for fan-out. Joined directly, the two receipt lines and three invoice lines of the purchase order line in Figure 12.4 would produce six rows, and every received quantity would be counted three times. Summing each side to the purchase order line first leaves one row per line on each side, and the comparison is exact.

12.6 Testing Approvals Across a Process

An approval control works only if the approver was entitled to approve. SQL tests entitlement by joining each document to the Employee row of its approver and comparing the document with the approver’s attributes. An amount above the approver’s MaxApprovalAmount is an approval above authority. A document approved after the approver’s TerminationDate was approved by someone who no longer worked for the company. Where a table records no approval date, as PurchaseOrder does not, the document’s own date stands in for it, and a document dated after the termination is a question for the approval records rather than a proven exception. A creator who is also the approver breaks the segregation of duties. An approver whose job title has nothing to do with the process, such as a sales employee approving purchases, is not a violation in itself, but it is worth a question.

These tests look for a small number of exceptions in a large population, and they return a clean result more often than not. A clean result is evidence too, provided the population was reconciled first. An approval test can also reveal concentration, which is not an exception but a risk. When one person approves nearly every document in a process, the approval control depends on that one review, and an error it misses goes unnoticed unless another control, such as a budget review or a reconciliation, would catch it. The concentration can indicate a weakness in the design of the control, so the auditor asks whether the approver has the time and authority to review so many documents, and what else would detect an error.

12.7 Guided Tutorial 12.2: Matching Purchases and Testing Their Approvals

Context and objective. The plant’s materials and services come through the purchasing chain. In this tutorial, you will perform a three-way match of every purchase order line with its receipts and invoices, test invoice prices against order prices at two tolerances, and reconcile the goods received but not yet invoiced to the ledger. You will then test the approvals of the purchase orders against their approvers’ limits, their employment dates, and the rule that no one approves their own order, and measure how concentrated the approvals are.

Prerequisites. Complete Tutorial 12.1, and continue at the end of Audit.sql. If you start here, make the working copy as Step 1 of Tutorial 9.1 describes, and download Audit.sql as it stands at the end of Tutorial 12.1 from Companion Files.

Step-by-step instructions.

Step 1. Match every purchase order line. Read the test from the top: Received and Invoiced sum the quantities received and invoiced for each purchase order line, Matched joins both sums to the line, and the final query classifies each line with CASE and counts the lines of each status.

  1. Type and run the test below.

    -- Tutorial 12.2, test P1: three-way match by purchase order line
-- Population: all PO lines; expected: no exception statuses
WITH Received AS (
    SELECT POLineID, SUM(QuantityReceived) AS QtyReceived
    FROM GoodsReceiptLine
    GROUP BY POLineID
),
Invoiced AS (
    SELECT POLineID, SUM(Quantity) AS QtyInvoiced
    FROM PurchaseInvoiceLine
    WHERE POLineID IS NOT NULL
    GROUP BY POLineID
),
Matched AS (
    SELECT pol.POLineID, pol.Quantity AS QtyOrdered,
        COALESCE(r.QtyReceived, 0) AS QtyReceived,
        COALESCE(i.QtyInvoiced, 0) AS QtyInvoiced
    FROM PurchaseOrderLine AS pol
        LEFT JOIN Received AS r ON r.POLineID = pol.POLineID
        LEFT JOIN Invoiced AS i ON i.POLineID = pol.POLineID
)
SELECT
    CASE
        WHEN QtyInvoiced > QtyReceived + 0.0001
            THEN 'Exception: invoiced above received'
        WHEN QtyReceived > QtyOrdered + 0.0001
            THEN 'Exception: received above ordered'
        WHEN QtyReceived = 0 THEN 'Not yet received'
        WHEN QtyInvoiced < QtyReceived - 0.0001
            THEN 'Received, not fully invoiced'
        WHEN QtyReceived < QtyOrdered - 0.0001 THEN 'Partly received'
        ELSE 'Matched'
    END AS MatchStatus,
    COUNT(*) AS POLines
FROM Matched
GROUP BY MatchStatus
ORDER BY POLines DESC;

  2. Read the statuses and their counts.

The result, shown in Figure 12.5, has no exception rows. No line was invoiced above what was received, and none was received above what was ordered. Nearly every line is matched, and the others are lines not yet received, partly received, or received but not yet fully invoiced, all normal at a year-end. The CASE gives each line the first status whose condition it meets, so a line that is partly received and also not fully invoiced counts as received, not fully invoiced, and the exception tests come first so that no exception can hide under a normal status. COALESCE turns the NULL of a line with no receipts or invoices into zero, as in Chapter 9, so that the comparisons work.

A mock of the Execute SQL editor and its result. The editor, on the tab Audit.sql, is scrolled past the three CTEs that sum received and invoiced quantities for each purchase order line, to the CASE expression that classifies each line. The result has four rows: Matched, with the great majority of lines; Received, not fully invoiced; Not yet received; and Partly received. No exception status appears, and the result is outlined.
Figure 12.5: The Three-Way Match of Every Purchase Order Line.

Step 2. Test prices against two tolerances. An invoice price that differs from the order price by more than the tolerance is an exception, and counting at two tolerances shows how much the choice matters. The prices are recorded to the cent, so the Prices CTE turns each one into whole cents, and the test compares the differences with cross-multiplied whole numbers. A division of two decimal prices can land a hair above the tolerance for a line that is exactly on it, as 15.30 against 15.00 divides to 0.02000000000000005 and would be flagged at two percent, while whole numbers compare exactly. A line with no usable order price would be counted apart as not testable, not passed.

  1. Type and run the test below.

    -- Tutorial 12.2, test P2: invoice prices against order prices
-- Population: invoice lines with a PO line; expected: none above 3%
WITH Prices AS (
    SELECT
        CAST(ROUND(pil.UnitCost * 100) AS INTEGER) AS InvoiceCents,
        CAST(ROUND(pol.UnitCost * 100) AS INTEGER) AS OrderCents
    FROM PurchaseInvoiceLine AS pil
        INNER JOIN PurchaseOrderLine AS pol
            ON pol.POLineID = pil.POLineID
)
SELECT COUNT(*) AS InvoiceLines,
    SUM(CASE WHEN OrderCents > 0
        AND ABS(InvoiceCents - OrderCents) * 100 > 3 * OrderCents
        THEN 1 ELSE 0 END) AS AboveThreePercent,
    SUM(CASE WHEN OrderCents > 0
        AND ABS(InvoiceCents - OrderCents) * 100 > 2 * OrderCents
        THEN 1 ELSE 0 END) AS AboveTwoPercent,
    ROUND(MAX((InvoiceCents - OrderCents) * 1.0 / OrderCents), 4)
        AS LargestIncrease,
    ROUND(MIN((InvoiceCents - OrderCents) * 1.0 / OrderCents), 4)
        AS LargestDecrease,
    SUM(CASE WHEN InvoiceCents IS NULL OR OrderCents IS NULL
        OR OrderCents <= 0 THEN 1 ELSE 0 END) AS NotTestable
FROM Prices;

  2. Compare AboveThreePercent with AboveTwoPercent, and read NotTestable.

At a tolerance of three percent, no invoice line is an exception: the largest increase over the order price is a little over two and a half percent, the largest decrease is under two, and every line has a usable order price. At two percent, several thousand lines would be flagged, all of them billed above the order price, the differences that Exercise 10.4 traced to the purchase price variance. Every price in this database has two decimals, so whole cents are exact, and a source with more decimals needs a finer scale. The choice of tolerance decides how many items an auditor must follow up, so it should be set before the test is run, based on what the purchasing policy allows, and documented with the test.

Step 3. Reconcile the goods received but not invoiced. Receipts that suppliers have not yet billed are a liability, which the ledger records in account 2020 Goods Received Not Invoiced. Despite its name, the ExtendedStandardCost of a receipt line holds the quantity received at the order price, the value at which account 2020 records it.

  1. Type and run the test below.

    -- Tutorial 12.2, test P3: receipts not invoiced against account 2020
-- Population: open receipt lines; expected: equal to the ledger
WITH InvoicedByReceipt AS (
    SELECT GoodsReceiptLineID, SUM(Quantity) AS QtyInvoiced
    FROM PurchaseInvoiceLine
    GROUP BY GoodsReceiptLineID
)
SELECT
    (SELECT ROUND(SUM(grl.ExtendedStandardCost
        * (1 - COALESCE(ibr.QtyInvoiced, 0) / grl.QuantityReceived)), 2)
     FROM GoodsReceiptLine AS grl
        LEFT JOIN InvoicedByReceipt AS ibr
            ON ibr.GoodsReceiptLineID = grl.GoodsReceiptLineID
     WHERE COALESCE(ibr.QtyInvoiced, 0) < grl.QuantityReceived - 0.0001)
        AS NotInvoicedValue,
    (SELECT ROUND(SUM(gl.Credit) - SUM(gl.Debit), 2)
     FROM GLEntry AS gl
        INNER JOIN Account AS a ON a.AccountID = gl.AccountID
     WHERE a.AccountNumber = 2020 AND gl.PostingDate <= '2026-12-31')
        AS LedgerBalance;

  2. Compare the two columns.

The value of the receipts not yet invoiced agrees with the balance of account 2020 to within a dollar, the rounding of the amounts by which invoice lines clear the account, so the ledger’s liability is supported by the detail of the open receipts. That does not prove it complete: a receipt never recorded would be missing from both sides, which is why auditors also search for unrecorded liabilities among the invoices received after the year-end. The test reads all the data, which ends with fiscal 2026; to rerun it at another date, bound the receipts and invoices by date, as Exercise 12.2 does.

Step 4. Test the approvals of the purchase orders. Three tests, joined with UNION ALL in a CTE, are summarized in one result. PurchaseOrder records no approval date, so the order date stands in for it, and the third test is named for what it measures: orders dated after their approver left.

  1. Type and run the test below.

    -- Tutorial 12.2, test P4: purchase order approvals
-- Population: all purchase orders; expected: no rows
WITH ApprovalExceptions AS (
    SELECT 'Self-approved' AS Test, po.PONumber, po.OrderTotal
    FROM PurchaseOrder AS po
    WHERE po.CreatedByEmployeeID = po.ApprovedByEmployeeID
    UNION ALL
    SELECT 'Above approver limit', po.PONumber, po.OrderTotal
    FROM PurchaseOrder AS po
        INNER JOIN Employee AS e
            ON e.EmployeeID = po.ApprovedByEmployeeID
    WHERE po.OrderTotal > e.MaxApprovalAmount
    UNION ALL
    SELECT 'Dated after approver left', po.PONumber, po.OrderTotal
    FROM PurchaseOrder AS po
        INNER JOIN Employee AS e
            ON e.EmployeeID = po.ApprovedByEmployeeID
    WHERE e.TerminationDate IS NOT NULL
        AND po.OrderDate > e.TerminationDate
)
SELECT Test, COUNT(*) AS Orders, ROUND(SUM(OrderTotal), 2) AS OrderValue
FROM ApprovalExceptions
GROUP BY Test
ORDER BY Orders DESC;

  2. Read the number of orders and their value for each test.

The summary, shown in Figure 12.6, flags between three and thirteen orders for each test, the exceptions that Exercise 8.5 found in Excel. Most of their value is in a few large orders. An order with no approver, or whose approver is missing from Employee or has no limit recorded, passes the tests that need those values, because the join or the comparison finds nothing. No order here lacks that data, and on new data such orders are counted apart, as not testable rather than passed.

A mock of the Execute SQL editor and its result. The editor, on the tab Audit.sql, shows a test whose CTE combines three approval tests on purchase orders with UNION ALL: self-approved, above the approver's limit, and dated after the approver's termination. The summary grid shows each test with its number of orders and their total value, between three and thirteen orders for each test. The grid is outlined.
Figure 12.6: Approval Exceptions in the Purchase Orders.

Step 5. List the orders behind the exceptions. A second CTE groups the exceptions by order, with GROUP_CONCAT, the aggregate of Table 10.2 that joins text, listing the tests each order fails in one cell. Employee appears twice in the final query, as c for the employee who created the order and as a for the approver.

  1. Copy the test of Step 4, with its comment, and paste the copy at the end of the script.

  2. Edit the copy until it reads as below: change the comment, type a comma after the closing parenthesis of ApprovalExceptions, add the Flagged CTE, and replace the final query. Run it.

    -- Tutorial 12.2, test P5: the orders behind the approval exceptions
-- Population: the orders flagged by test P4
WITH ApprovalExceptions AS (
    SELECT 'Self-approved' AS Test, po.PONumber, po.OrderTotal
    FROM PurchaseOrder AS po
    WHERE po.CreatedByEmployeeID = po.ApprovedByEmployeeID
    UNION ALL
    SELECT 'Above approver limit', po.PONumber, po.OrderTotal
    FROM PurchaseOrder AS po
        INNER JOIN Employee AS e
            ON e.EmployeeID = po.ApprovedByEmployeeID
    WHERE po.OrderTotal > e.MaxApprovalAmount
    UNION ALL
    SELECT 'Dated after approver left', po.PONumber, po.OrderTotal
    FROM PurchaseOrder AS po
        INNER JOIN Employee AS e
            ON e.EmployeeID = po.ApprovedByEmployeeID
    WHERE e.TerminationDate IS NOT NULL
        AND po.OrderDate > e.TerminationDate
),
Flagged AS (
    SELECT PONumber, GROUP_CONCAT(Test, '; ') AS Tests
    FROM ApprovalExceptions
    GROUP BY PONumber
)
SELECT f.PONumber, po.OrderDate, po.OrderTotal,
    c.JobTitle AS CreatedBy, a.JobTitle AS ApprovedBy,
    a.MaxApprovalAmount, a.TerminationDate, f.Tests
FROM Flagged AS f
    INNER JOIN PurchaseOrder AS po ON po.PONumber = f.PONumber
    INNER JOIN Employee AS c ON c.EmployeeID = po.CreatedByEmployeeID
    INNER JOIN Employee AS a ON a.EmployeeID = po.ApprovedByEmployeeID
ORDER BY po.OrderTotal DESC;

  3. Read the job titles, the approval limit, and the tests of each order.

Fourteen orders are flagged, and most fail two tests. The two largest are the capital-equipment orders whose note-financed invoices you met in Exercise 8.2, created and approved by the Purchasing Manager, above that manager’s limit and above even the chief financial officer’s. Three orders name as their approver an Account Executive, a sales employee with no approval authority, and are dated after that employee’s termination: the employee of Tutorial 12.1. Whether they were approved after the termination, and by whom, only the approval records can show. Several small orders were approved by buyers and analysts whose approval limit is zero. Each is an exception in the operation of the approval control, and each needs a disposition.

Step 6. Measure the concentration of approvals. An approval control also depends on how many people share it. The test groups by the approver’s key, not by the job title, which several employees share, and the window function SUM(COUNT(*)) OVER () of Chapter 11 divides each count by the total of the counts.

  1. Type and run the test below.

    -- Tutorial 12.2, test P6: who approves the purchase orders
-- Population: all purchase orders; expected: approval shared
SELECT e.EmployeeID, e.JobTitle, COUNT(*) AS OrdersApproved,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2)
        AS SharePercent
FROM PurchaseOrder AS po
    INNER JOIN Employee AS e ON e.EmployeeID = po.ApprovedByEmployeeID
GROUP BY e.EmployeeID, e.JobTitle
ORDER BY OrdersApproved DESC;

  2. Read SharePercent for the first employee.

The chief financial officer approved nearly every purchase order in the three years, and the other approvers only a few each. SharePercent is calculated over the orders in this query, which are those whose approver is found in Employee, so compare the total of OrdersApproved with the count of all purchase orders, and count apart any order with no approver or an unmatched one before you interpret the concentration. With one approver, the purchasing control depends heavily on one person’s review, and it would be hard for one person to review so many orders with care. That concentration can indicate a weakness in the design of the control, rather than an exception in any one order, depending on whether other controls, such as budget reviews or reconciliations, would detect an error the approver missed.

Checkpoint. Check that:

  • the three-way match reports no line invoiced above received or received above ordered;
  • no invoice price differs from its order price by more than three percent;
  • the receipts not yet invoiced agree with account 2020 to within a dollar;
  • the approval tests flag fourteen orders, among them two large self-approved orders and three dated after their approver’s termination, and one person approved almost all the others;
  • Audit.sql holds this tutorial’s six tests under their comments.

The purchasing chain holds together in the records tested: no line was billed for more than was received, prices were close to the orders, and the liability for unbilled receipts agrees with the ledger. Its approval control has exceptions and depends on one approver, which the chief audit executive will want in the report once the policy, the approvers’ authority at the time, and any compensating review have been examined.

12.8 Testing the Records Behind a Cost

The manufacturing variance is built from a chain of records, which Figure 12.7 traces. An employee clocks in and out, and the time clock entry records the hours, with an overtime approval when the overtime is long enough to need one. The hours are recorded against the operation of a work order, or as indirect time. The payroll register pays for the hours, through earnings lines that point to the labor time entries they pay, and a payroll payment disburses the net pay. The ledger records the cost and the payment. An auditor tests each link: that the hours clocked were the hours recorded and paid, to the employee who worked them; that overtime was approved; that the records look like records of individual work rather than entries made in bulk; that direct time was not charged to work already finished; that the people paid were employed and were paid what they earned; and that each register was approved by someone independent of it and paid after its approval. Tutorial 12.3 tests the hours and the pay for them, and Exercise 12.5 the pay of people who had left and the approval of the registers.

A chain of five boxes from left to right: time clock entry, labor time entry, payroll register, payroll payment, and general ledger, each naming the key that links it to the record before it; the payroll register is linked to the labor time through the earnings lines of PayrollRegisterLine. The last link, a posting to the ledger, is dashed. Beneath each box, the test that checks it, with the tutorial step or exercise that runs it: the entries are profiled, overtime above half an hour is approved, and no day shows everyone clocking the same hours; the labor hours equal the clock entry's and no direct time is recorded after its operation ended; the register pays its own employee's approved time at the employee's rate and is not approved by that employee; each approved register has a payment, made after approval; and every payment posting traces to a payment, with unpaid wages accrued at the year-end.
Figure 12.7: The Chain of Records Behind the Labor Cost.

Tests of a chain like this find two kinds of exception. An isolated exception, such as a single register paid to a terminated employee, usually shows a control that did not operate as designed. A systematic pattern, such as a large share of hours whose work dates fall after the operations they belong to had ended, more often shows a control that is missing or poorly designed. Chapter 8 called both control deficiencies, and which kind an exception is depends on its cause, which the auditor must find. A pattern does not necessarily mean that anyone was paid for time not worked, but it can mean that the records cannot support some of the conclusions drawn from them. An auditor reports both kinds, and distinguishes them. Tutorial 12.3 finds such a pattern on what this chapter calls surge days: days on which every hourly manufacturing employee recorded exactly the same hours.

Not every test compares two records. A substantive analytical procedure compares a recorded amount with an expectation that the auditor develops from a plausible relationship in other data, and investigates any difference above a threshold set in advance (PCAOB, n.d.). Chapter 6 defined the procedure, and Exercise 7.5 built expectations for revenue and commissions with regression, where the reliability of the inputs, the precision of the expectation, and the threshold all matter. A recalculation is different, because it only checks the arithmetic of a recorded amount from its inputs. For payroll, the hours of each earnings line times the employee’s rate of pay recompute the amount apart from the payroll system’s own calculation, although from rates that the same system stores, so test H5 is a recalculation. It supports the arithmetic of the pay on those inputs, not that the hours were worked or the rates authorized. A reconciliation asks whether two records agree, a recalculation whether an amount follows from its inputs, and an expectation whether a recorded amount is what its drivers imply. Evidence from an independent source can strengthen an expectation, but its relevance and reliability must still be evaluated.

TipIn Practice

Payroll is a common area for population testing, because the tables are large and the controls are well defined. The typical frauds also leave traces in the data: employees paid after they have left, hours paid that were never clocked, overtime without approval, and time entered in bulk rather than by the people who worked it. A test for different employees paid into the same bank account is a common red flag too, although the Charles River data holds no bank details. Tests like these are often run every pay period, so that an exception is found while it can still be corrected, rather than at the year-end audit.

12.9 Guided Tutorial 12.3: Testing the Hours Behind the Variance

Context and objective. The committee wants to know whether the extra hours behind the manufacturing variance were worked, approved, recorded against the right operations, and paid correctly, and why so little indirect time appears in the records for four months of 2024. In this tutorial, you will profile the time records, reconcile the hours along the chain from the time clock to the payroll, test whose time was paid, and recalculate the pay from the hours and the pay rates. You will then test the overtime approvals and the days on which everyone clocked the same hours, test when direct time was recorded, and decide what the results mean for the variance.

Prerequisites. Complete Tutorials 12.1 and 12.2, and continue at the end of Audit.sql. If you start here, make the working copy as Step 1 of Tutorial 9.1 describes, and download Audit.sql as it stands at the end of Tutorial 12.2 from Companion Files. Figure 10.8 shows the payroll and time tables, and Figure 12.7 the chain of records that the tests follow.

Step-by-step instructions.

Step 1. Profile the time records. Before testing the time clock entries, find out what they hold, with the tests of Table 10.3.

  1. Type and run the test below.

    -- Tutorial 12.3, test H1: a profile of the time clock entries
-- Population: all clock entries; expected: approved and complete
SELECT COUNT(*) AS Entries,
    SUM(CASE WHEN ClockStatus <> 'Approved' THEN 1 ELSE 0 END)
        AS NotApproved,
    COUNT(*) - COUNT(ClockOutTime) AS NoClockOut,
    COUNT(DISTINCT EmployeeID) AS Employees,
    MIN(WorkDate) AS FirstWorkDate, MAX(WorkDate) AS LastWorkDate
FROM TimeClockEntry;

  2. Read each column of the result.

The table holds tens of thousands of entries by several dozen employees, from January 1, 2024, to December 11, 2026, the last day with time records that Tutorial 10.3 found. A handful of entries are still waiting for approval, and three have no clock-out time; Steps 4 and 6 find whose they are. The profile also fixes the dates over which the totals of this tutorial are measured, although indirect time, as Tutorial 10.3 found, ends a week before the last day.

Step 2. Reconcile the hours along the chain. Every hour clocked should be recorded as labor time and paid. Read the test from the top: the CTEs Clocked, Recorded, and Paid each total the hours of every pay period on one record, and the final query joins them on the period and counts the periods in which they differ.

  1. Type and run the test below.

    -- Tutorial 12.3, test H2: clocked, recorded, and paid hours
-- Population: pay periods with time records; expected: equal in each
WITH Clocked AS (
    SELECT PayrollPeriodID, SUM(RegularHours + OvertimeHours) AS Hours
    FROM TimeClockEntry
    GROUP BY PayrollPeriodID
),
Recorded AS (
    SELECT PayrollPeriodID, SUM(RegularHours + OvertimeHours) AS Hours
    FROM LaborTimeEntry
    GROUP BY PayrollPeriodID
),
Paid AS (
    SELECT pr.PayrollPeriodID, SUM(prl.Hours) AS Hours
    FROM PayrollRegisterLine AS prl
        INNER JOIN PayrollRegister AS pr
            ON pr.PayrollRegisterID = prl.PayrollRegisterID
    WHERE prl.LineType IN ('Regular Earnings', 'Overtime Earnings')
    GROUP BY pr.PayrollPeriodID
)
SELECT COUNT(*) AS Periods,
    SUM(CASE WHEN ABS(c.Hours - r.Hours) > 0.001
        OR ABS(r.Hours - p.Hours) > 0.001 THEN 1 ELSE 0 END)
        AS PeriodsThatDiffer,
    ROUND(SUM(c.Hours), 1) AS Clocked,
    ROUND(SUM(r.Hours), 1) AS Recorded,
    ROUND(SUM(p.Hours), 1) AS Paid
FROM Clocked AS c
    INNER JOIN Recorded AS r ON r.PayrollPeriodID = c.PayrollPeriodID
    INNER JOIN Paid AS p ON p.PayrollPeriodID = c.PayrollPeriodID;

  2. Read PeriodsThatDiffer and the three totals.

The three records agree in every pay period they share: the hours clocked are the hours recorded and the hours paid. The inner joins keep only the periods found in all three records; here every processed pay period appears in each, and only the last two periods of December 2026, still Open, have no records at all. Equal totals for a period can still hide hours paid to the wrong person, so Step 4 tests whether each paid hour belongs to the employee who was paid.

Step 3. Look at the months with little indirect time. Splitting the manufacturing hours of each pay period of fiscal 2024 by kind of labor shows what the records hold for those months.

  1. Type and run the test below.

    -- Tutorial 12.3, test H3: the manufacturing hours of 2024 by kind
-- Population: manufacturing labor time; expected: every period recorded
SELECT pp.PeriodNumber, pp.PeriodStartDate,
    ROUND(SUM(lt.RegularHours + lt.OvertimeHours), 0) AS Hours,
    ROUND(SUM(CASE lt.LaborType WHEN 'Direct Manufacturing'
        THEN lt.RegularHours + lt.OvertimeHours ELSE 0 END), 0)
        AS DirectHours,
    ROUND(SUM(CASE lt.LaborType WHEN 'Indirect Manufacturing'
        THEN lt.RegularHours + lt.OvertimeHours ELSE 0 END), 0)
        AS IndirectHours
FROM LaborTimeEntry AS lt
    INNER JOIN PayrollPeriod AS pp
        ON pp.PayrollPeriodID = lt.PayrollPeriodID
WHERE lt.LaborType <> 'NonManufacturing' AND pp.FiscalYear = 2024
GROUP BY pp.PayrollPeriodID, pp.PeriodNumber, pp.PeriodStartDate
ORDER BY pp.PeriodStartDate;

  2. Read the IndirectHours of the periods from February to May.

The result answers, as far as the records can, the second question that Chapter 11 handed on. From February to May 2024, the plant recorded normal hours in every pay period, and most of them were direct time. From June, a large part of most periods is indirect. Nothing is missing from those months, as far as the chain of Step 2 can show: their hours were clocked, recorded, and paid like any others. The production records suggest an explanation. The plant was building stock, completing thousands of units more than it shipped in its first months, which is consistent with most hours being assigned to work orders. Indirect time rose from June, and by July completions had fallen back to the rate of shipments while the same employees kept recording their hours. The build is a plausible explanation of the mix, to be tested against the production schedules and the way hours were assigned. Exercise 12.3 measures the build.

Step 4. Test whose time was paid. A payroll earnings line points to the labor time entry it pays, so each line can be checked against the employee paid and against the approval of the clock entry behind it. Employee appears twice in the test, as pe for the employee paid and as we for the employee who worked, so that each row can show both. Each row also carries the keys of its earnings line and its register, so that a disposition can be tied to the row.

  1. Type and run the test below.

    -- Tutorial 12.3, test H4: time paid to another person or unapproved
-- Population: all earnings lines; expected: no rows
SELECT 'Time paid to another employee' AS Test,
    prl.PayrollRegisterLineID, pr.PayrollRegisterID,
    pr.EmployeeID AS PaidID, pe.JobTitle AS PaidTitle,
    lt.EmployeeID AS WorkedID, we.JobTitle AS WorkedTitle,
    we.TerminationDate AS WorkerLeft, prl.Amount
FROM PayrollRegisterLine AS prl
    INNER JOIN PayrollRegister AS pr
        ON pr.PayrollRegisterID = prl.PayrollRegisterID
    INNER JOIN LaborTimeEntry AS lt
        ON lt.LaborTimeEntryID = prl.LaborTimeEntryID
    INNER JOIN Employee AS pe ON pe.EmployeeID = pr.EmployeeID
    INNER JOIN Employee AS we ON we.EmployeeID = lt.EmployeeID
WHERE lt.EmployeeID <> pr.EmployeeID
UNION ALL
SELECT 'Time paid on an unapproved clock entry',
    prl.PayrollRegisterLineID, pr.PayrollRegisterID,
    pr.EmployeeID, pe.JobTitle, lt.EmployeeID, we.JobTitle,
    we.TerminationDate, prl.Amount
FROM PayrollRegisterLine AS prl
    INNER JOIN PayrollRegister AS pr
        ON pr.PayrollRegisterID = prl.PayrollRegisterID
    INNER JOIN LaborTimeEntry AS lt
        ON lt.LaborTimeEntryID = prl.LaborTimeEntryID
    INNER JOIN Employee AS pe ON pe.EmployeeID = pr.EmployeeID
    INNER JOIN Employee AS we ON we.EmployeeID = lt.EmployeeID
    INNER JOIN TimeClockEntry AS tc
        ON tc.TimeClockEntryID = lt.TimeClockEntryID
WHERE tc.ClockStatus <> 'Approved';

  2. Compare the PaidID and WorkedID of each row, and read the job titles and the earnings line keys.

The first test finds six earnings lines that pay one employee’s time to others. The time belongs to an Inventory Specialist who left in September 2024, and the lines pay it to two other employees, another Inventory Specialist and an Assembler on the manufacturing payroll. The second test finds eight lines paid on clock entries not yet approved, two of them the Assembler’s own time, and four of them lines that fail the first test as well, so those four earnings lines appear under both labels and are one item each, not two. The amounts are small, but the exceptions reach the manufacturing payroll, and each needs a disposition.

Step 5. Recalculate the pay from the hours and the rates. A reconciliation compares two records; a recalculation recomputes an amount from its inputs. Each earnings line should equal its hours times the employee’s base hourly rate from the Employee table, and time and a half for overtime. Set the threshold before you run the test: a line that differs by more than one cent, the rounding of one amount, is an exception.

  1. Type and run the test below.

    -- Tutorial 12.3, test H5: earnings paid against hours and pay rates
-- Population: hourly earnings lines; expected: within a cent a line
WITH Expected AS (
    SELECT pp.FiscalYear, prl.Amount,
        prl.Hours * e.BaseHourlyRate
            * CASE prl.LineType WHEN 'Overtime Earnings'
                THEN 1.5 ELSE 1 END AS ExpectedAmount
    FROM PayrollRegisterLine AS prl
        INNER JOIN PayrollRegister AS pr
            ON pr.PayrollRegisterID = prl.PayrollRegisterID
        INNER JOIN PayrollPeriod AS pp
            ON pp.PayrollPeriodID = pr.PayrollPeriodID
        INNER JOIN Employee AS e ON e.EmployeeID = pr.EmployeeID
    WHERE prl.LineType IN ('Regular Earnings', 'Overtime Earnings')
)
SELECT FiscalYear, ROUND(SUM(Amount), 2) AS EarningsPaid,
    ROUND(SUM(ExpectedAmount), 2) AS Expected,
    ROUND(SUM(Amount) - SUM(ExpectedAmount), 2) AS Difference,
    SUM(CASE WHEN ABS(Amount - ExpectedAmount) > 0.01 THEN 1 ELSE 0 END)
        AS LinesAboveOneCent
FROM Expected
GROUP BY FiscalYear
ORDER BY FiscalYear;

  2. Read the Difference and LinesAboveOneCent columns.

The earnings paid differ from the recalculation by a few dollars a year, the sum of each line’s rounding, and no line differs by more than a cent. Every hour was paid at the paid employee’s rate on file, and every overtime hour at time and a half. The test recalculates the pay from rates that the payroll system itself stores, so it cannot show that a rate was authorized or, as Step 4 found, that the hours belonged to the person paid. The pay is right for the hours recorded, so the rest of the tutorial asks about the hours themselves.

Step 6. Test overtime against its approvals. A time clock entry with overtime links to an overtime approval, and classifying the entries by the length of their overtime shows where the policy draws the line.

  1. Type and run the test below.

    -- Tutorial 12.3, test H6: overtime and its approvals
-- Population: clock entries with overtime; expected: approved
SELECT
    CASE WHEN OvertimeHours <= 0.5 THEN 'Half an hour or less'
        ELSE 'More than half an hour' END AS OvertimeLength,
    COUNT(*) AS Entries,
    SUM(CASE WHEN OvertimeApprovalID IS NULL THEN 1 ELSE 0 END)
        AS WithoutApproval,
    ROUND(SUM(OvertimeHours), 1) AS OvertimeHours
FROM TimeClockEntry
WHERE OvertimeHours > 0
GROUP BY OvertimeLength;

  2. Compare the WithoutApproval column of the two rows.

  3. Type and run the test below, which lists the longer entries without an approval and looks for an approval of the same employee and day. Employee appears twice again, as e for the employee and as ap for the approver.

    -- Tutorial 12.3, test H7: long overtime without an approval
-- Population: overtime above half an hour; expected: no rows
SELECT tc.TimeClockEntryID, tc.EmployeeID, e.JobTitle,
    e.TerminationDate, tc.WorkDate,
    tc.OvertimeHours, tc.ClockOutTime, tc.ClockStatus,
    oa.OvertimeApprovalID AS UnlinkedApproval,
    ap.JobTitle AS ApprovedBy
FROM TimeClockEntry AS tc
    INNER JOIN Employee AS e ON e.EmployeeID = tc.EmployeeID
    LEFT JOIN OvertimeApproval AS oa
        ON oa.EmployeeID = tc.EmployeeID AND oa.WorkDate = tc.WorkDate
    LEFT JOIN Employee AS ap ON ap.EmployeeID = oa.ApprovedByEmployeeID
WHERE tc.OvertimeHours > 0.5 AND tc.OvertimeApprovalID IS NULL;

No entry with half an hour of overtime or less has an approval, while nearly every longer one has one linked, which suggests a policy that exempts short overtime; the team should confirm it with the payroll department rather than assume it. Above that threshold, only three entries lack an approval link, all for the Inventory Specialist of Step 4, on New Year’s Day of each year, still Pending, with no clock-out time, and two of them after the employee left. The left join finds an approval for each, entered by the Warehouse Manager, but none is linked to its entry. A link shows only that an approval is recorded for the entry, not that it belongs to the same employee and day, is approved, or covers the hours worked.

Step 7. Look for days on which everyone clocked the same hours. Employees who clock in and out themselves arrive and leave at slightly different times, so a day on which every hourly employee of a plant recorded exactly the same hours is unusual; this chapter calls such a day a surge day. The CTE PlantDays counts, for each day, the employees who clocked in and the different numbers of hours they recorded, and the final query summarizes the surge days by year, with the fewest employees on any of them.

  1. Type and run the test below.

    -- Tutorial 12.3, test H8: days when everyone clocked the same hours
-- Population: manufacturing clock entries; expected: no such days
WITH PlantDays AS (
    SELECT tc.WorkDate, COUNT(DISTINCT tc.EmployeeID) AS Employees,
        COUNT(DISTINCT tc.RegularHours + tc.OvertimeHours)
            AS HourPatterns,
        SUM(tc.OvertimeHours) AS OvertimeHours
    FROM TimeClockEntry AS tc
        INNER JOIN Employee AS e ON e.EmployeeID = tc.EmployeeID
        INNER JOIN CostCenter AS cc ON cc.CostCenterID = e.CostCenterID
    WHERE cc.CostCenterName = 'Manufacturing'
    GROUP BY tc.WorkDate
)
SELECT strftime('%Y', WorkDate) AS WorkYear,
    SUM(CASE WHEN HourPatterns = 1 AND Employees > 1
        THEN 1 ELSE 0 END) AS SurgeDays,
    MIN(CASE WHEN HourPatterns = 1 AND Employees > 1
        THEN Employees END) AS EmployeesPerDay,
    ROUND(SUM(CASE WHEN HourPatterns = 1 AND Employees > 1
        THEN OvertimeHours ELSE 0 END), 0) AS SurgeOvertime,
    ROUND(SUM(CASE WHEN HourPatterns = 1 AND Employees > 1
        THEN OvertimeHours ELSE 0 END) / SUM(OvertimeHours), 3)
        AS SurgeShare
FROM PlantDays
GROUP BY WorkYear
ORDER BY WorkYear;

  2. Read SurgeDays and SurgeShare for each year.

The result, shown in Figure 12.8, is the most important finding of the audit. On a growing number of days since mid-2024, all 64 hourly manufacturing employees recorded the same hours. The query counts only the employees who clocked in each day. A separate count from the Employee table shows that the 64 are the plant’s whole hourly staff, each with one entry on every surge day. Figure 12.9 shows six of them on such a day and on an ordinary day before the first one. On the surge day, every entry records eight regular hours and four and a half of overtime, clocked in and out at the same minutes on one of two shifts, and the Production Manager approved all of the overtime on the work date. The overtime of the surge days grew to more than two-thirds of the plant’s overtime by fiscal 2026, while overtime on other days, which Exercise 12.4 compares, stayed about the same. Identical times for a whole plant look like time entered in bulk, or a schedule recorded in place of the clock, rather than clocked by each employee. They do not prove that the hours were not worked, but the records cannot show that they were.

A mock of the Execute SQL editor and its result. The editor, on the tab Audit.sql, shows a test that groups the manufacturing time clock entries by day in a CTE, counts the different numbers of hours recorded each day, and summarizes by year the days on which every employee recorded the same hours. The result has one row for each of 2024, 2025, and 2026, with the number of such days, which rises each year, the 64 employees on each of them, their overtime hours, and their share of the year's manufacturing overtime, which rises from about a third to more than two-thirds. The SurgeDays and SurgeShare columns are outlined.
Figure 12.8: Days on Which Every Hourly Manufacturing Employee Clocked the Same Hours.
Two tables of the time clock entries of the same six Assemblers. On a day in March 2024, before the first surge day, their clock-in and clock-out times differ by minutes and hours, their regular hours vary, and only some of them recorded overtime, approved by the Production Manager. On a surge day in June 2026, all six clocked in and out at the same minutes on one of two shifts and recorded the same regular and overtime hours, all approved by the Production Manager on the work date; those cells are highlighted. A line under each table summarizes the day for all 64 hourly manufacturing employees: many different clock-in times and numbers of hours on the first day, and two clock-in times and one number of hours on the surge day, when every employee recorded overtime.
Figure 12.9: A Day Before the Surge Days and a Surge Day.

Step 8. Test the work dates of direct labor. Direct labor is charged to a work order operation, which has an actual end date, and time whose work date falls after that date was charged to work that was already finished. LaborTimeEntry holds the date of the work, not the date someone entered it, so “recorded after” in this chapter means a work date after the end of the operation. The test finds such time whatever the reason, and cannot tell a late entry for work done while the operation was open from time that belongs to another operation. A CTE lists the surge days again, and a left join marks the time dated on them.

  1. Type and run the test below.

    -- Tutorial 12.3, test H9: direct time dated after its operation ended
-- Population: direct labor time; expected: a small share
WITH SurgeDays AS (
    SELECT tc.WorkDate
    FROM TimeClockEntry AS tc
        INNER JOIN Employee AS e ON e.EmployeeID = tc.EmployeeID
        INNER JOIN CostCenter AS cc ON cc.CostCenterID = e.CostCenterID
    WHERE cc.CostCenterName = 'Manufacturing'
    GROUP BY tc.WorkDate
    HAVING COUNT(DISTINCT tc.RegularHours + tc.OvertimeHours) = 1
        AND COUNT(DISTINCT tc.EmployeeID) > 1
)
SELECT strftime('%Y', lt.WorkDate) AS WorkYear,
    ROUND(SUM(lt.RegularHours + lt.OvertimeHours), 0) AS DirectHours,
    ROUND(SUM(CASE WHEN lt.WorkDate > op.ActualEndDate
        THEN lt.RegularHours + lt.OvertimeHours ELSE 0 END), 0)
        AS AfterEnd,
    ROUND(SUM(CASE WHEN lt.WorkDate > op.ActualEndDate
        THEN lt.RegularHours + lt.OvertimeHours ELSE 0 END)
        / SUM(lt.RegularHours + lt.OvertimeHours), 3) AS ShareAfterEnd,
    ROUND(SUM(CASE WHEN lt.WorkDate > op.ActualEndDate
        AND sd.WorkDate IS NOT NULL
        THEN lt.RegularHours + lt.OvertimeHours ELSE 0 END), 0)
        AS AfterEndOnSurgeDays
FROM LaborTimeEntry AS lt
    INNER JOIN WorkOrderOperation AS op
        ON op.WorkOrderOperationID = lt.WorkOrderOperationID
    LEFT JOIN SurgeDays AS sd ON sd.WorkDate = lt.WorkDate
WHERE lt.LaborType = 'Direct Manufacturing'
GROUP BY WorkYear
ORDER BY WorkYear;

  2. Read ShareAfterEnd and AfterEndOnSurgeDays for each year.

The result, shown in Figure 12.10, rises from about a fifth of direct labor in fiscal 2024 to half in fiscal 2025 and about two-thirds in fiscal 2026, and most of the late time was recorded on surge days. Exercise 12.3 finds the month in which the share first jumped. The inner join leaves out the few direct entries with no valid operation that Tutorial 10.3 found, so the shares are of the direct hours recorded against an existing operation. Exercise 12.3 repeats the comparison on the time dated within the operation’s window, a sensitivity analysis of how the hours were assigned, not a confirmation that they were worked or assigned correctly.

A mock of the Execute SQL editor and its result. The editor, on the tab Audit.sql, is scrolled past a CTE of surge days to a query that joins direct labor time entries to their work order operations, compares each work date with the operation's actual end date, and left-joins the surge days. The result has one row for each year, with the direct hours, the hours recorded after the operation had ended, their share, which rises from about a fifth in 2024 to about half in 2025 and about two-thirds in 2026, and the part of them recorded on surge days, which is most of them. The share and surge-day columns are outlined.
Figure 12.10: Direct Labor Recorded After Its Operation Ended.

Step 9. Find where the late time was charged. Splitting the direct time of fiscal 2026 by work center shows which operations received the time recorded after they had ended.

  1. Type and run the test below.

    -- Tutorial 12.3, test H10: late direct time by work center, 2026
-- Population: direct labor time of 2026; expected: a small share
SELECT wc.WorkCenterName,
    ROUND(SUM(lt.RegularHours + lt.OvertimeHours), 0) AS DirectHours,
    ROUND(SUM(CASE WHEN lt.WorkDate > op.ActualEndDate
        THEN lt.RegularHours + lt.OvertimeHours ELSE 0 END)
        / SUM(lt.RegularHours + lt.OvertimeHours), 3) AS ShareAfterEnd
FROM LaborTimeEntry AS lt
    INNER JOIN WorkOrderOperation AS op
        ON op.WorkOrderOperationID = lt.WorkOrderOperationID
    INNER JOIN WorkCenter AS wc ON wc.WorkCenterID = op.WorkCenterID
WHERE lt.LaborType = 'Direct Manufacturing'
    AND lt.WorkDate BETWEEN '2026-01-01' AND '2026-12-31'
GROUP BY wc.WorkCenterID, wc.WorkCenterName
ORDER BY ShareAfterEnd DESC;

  2. Compare the ShareAfterEnd of the work centers.

At Packing, the last operation of every routing, nearly all the direct time of fiscal 2026 was recorded after the operation had ended, and at Quality Assurance most of it was. Much of the time above plan at those work centers in Tutorial 11.2 was charged to operations already complete, so the operation records cannot reliably say which operations the late time belongs to.

Step 10. Complete and save the script. The header comment should record the checks that the chapter’s tests passed.

  1. Replace the last line of the header comment of Audit.sql, so that the header reads as below.

    /* Audit.sql: tests of the records behind the manufacturing variance
   Database: CharlesRiver_Work.sqlite (a copy of CharlesRiver.sqlite)
   Prepared by: your name, date; reviewed by: name, date
   Checks: populations tie to the ledger; exceptions listed by test */

  2. Click Save SQL file.

The script now holds the tests of the three tutorials, L1 to H10, each under a comment that names it and states its population and expected result, the structure of the audit query library in Table 12.1. The examples in the chapter’s sections and the exercises’ scripts stay apart from it.

Checkpoint. Check that:

  • the profile finds a handful of entries awaiting approval and three with no clock-out time, in records that end on December 11, 2026;
  • clocked, recorded, and paid hours agree in every pay period the records share, and the manufacturing hours of February to May 2024 are recorded like any others, most of them direct;
  • six earnings lines pay a terminated employee’s time to others, two of them to an Assembler, and eight rest on unapproved clock entries;
  • every earnings line agrees with the recalculation from its hours and the employee’s rate to within a cent;
  • long overtime has an approval linked, except three entries of the terminated employee whose approvals are not linked;
  • on a growing number of days every hourly manufacturing employee clocked the same hours, and by fiscal 2026 those days carried more than two-thirds of the plant’s overtime;
  • the share of direct time recorded after its operation ended rose to about two-thirds, most of it on surge days and at Packing;
  • Audit.sql is saved with a completed header and a comment above each test.

The tests support part of the controller’s explanation. The hours behind the variance were recorded, paid at the rates on file, and posted, and the manufacturing payroll ties to the ledger, so the variance is a cost that the plant’s records say it incurred. The growth in overtime, though, sits on surge days whose identical times, single approver, and charges to finished operations the records cannot explain. The chief audit executive will report the exceptions for correction, ask whether the time-recording practice reflects a control that is missing or one that did not operate, and report the surge-day overtime as a matter to corroborate before the committee relies on it.

12.10 Chapter 8’s Tests in SQL

The tests you ran in Excel in Chapter 8 translate directly into SQL, and in SQL they can join tables that the workbook kept apart; Exercises 11.5 and 11.6 have already moved the journal entry and threshold tests. An aging and a stratification become CASE expressions. An aging assigns each open invoice to a bucket by its days past due, measured with julianday from an as-of date kept in a one-row CTE, as in Chapter 11, so that the test can be rerun at another year-end; a stratification assigns each balance to a stratum by its amount; and GROUP BY the bucket or stratum counts and totals each, as COUNTIFS and SUMIFS did in Chapter 8. A duplicate invoice test groups the supplier invoices by supplier and invoice number, and a duplicate payment test groups the payments by supplier and check number, each keeping the groups with more than one row. A near-duplicate test joins the payments to themselves, a self-join, matching the same supplier and amount on different dates within a window of days. The query below uses a window of two weeks:

SELECT a.PaymentNumber, b.PaymentNumber AS MatchNumber,
    a.SupplierID, a.Amount, a.PaymentDate, b.PaymentDate AS MatchDate
FROM DisbursementPayment AS a
    INNER JOIN DisbursementPayment AS b
        ON b.SupplierID = a.SupplierID AND b.Amount = a.Amount
            AND b.DisbursementID > a.DisbursementID
WHERE a.PaymentDate <> b.PaymentDate
    AND ABS(julianday(b.PaymentDate) - julianday(a.PaymentDate)) <= 14
    AND a.PaymentDate <= '2026-12-31' AND b.PaymentDate <= '2026-12-31';

ABS counts the gap in either direction, because payment IDs are not always in the order of their dates.

WarningWatch out

Without the condition b.DisbursementID > a.DisbursementID, a self-join pairs every payment with itself and lists every other pair twice, once in each order. Here the condition on the dates happens to remove the first problem, but only the condition on the IDs removes the second. Count the rows with and without it before you trust a self-join.

A sequence test uses LAG to compare each document number with the one before it and report any step greater than one. A series that restarts each year, such as the journal entry numbers, is partitioned by year. A series that continues from one year to the next, such as the sales invoice numbers, is ordered across the years without a partition, so that a gap at the year boundary is not missed. A first-digit test needs the first significant digit of each amount. printf('%.15e', Amount) writes a positive amount in scientific notation with fifteen decimals, as 6.884200000000000e+02 for 688.42, so its first character is the first significant digit even for amounts below one dollar; zero and negative amounts must be left out. The decimals matter, because printf rounds: with its default of six, printf('%e', 999999.99) writes 1.000000e+06, and the first digit would read 1 instead of 9. LOG10(1 + 1.0 / Digit) gives the Benford expectation for each digit. LOG10 is one of SQLite’s math functions, and whether it is available depends on how the SQLite library in the program was built, not on the program’s name or version, so run SELECT LOG10(10.0);, which returns 1 when the functions are present. The Windows release of DB Browser for SQLite includes them. A build without them, such as the DB Browser for SQLCipher program installed beside it, answers no such function: LOG10, and a CASE expression that lists the nine expected proportions of Tutorial 8.1 can take its place. Grouped and compared, the digits and their expected proportions produce the same mean absolute deviation as the workbook of Tutorial 8.1. Exercise 12.2 ages the open balances of Tutorial 8.2 in SQL, and Exercise 12.6 builds the other tests.

12.11 A Library of Audit Queries

A test that is saved, documented, and run again every period becomes part of the company’s control environment. An audit query library is a set of such tests, usually one script for each area, with a consistent structure. Each test states its objective, the population it runs on and the reconciliation that supports its completeness, the query, the expected result, and the disposition of any exception. Table 12.1 indexes the library that the tutorials of this chapter build in Audit.sql.

Table 12.1: The audit query library of this chapter
Tests What they test Technique Expected result
L1–L2 Journal entries and manufacturing payroll against the ledger Aggregates, scalar subqueries Agree to the cent
L3–L4 Postings traced to payments, and invoices to postings; unpaid registers Anti-joins, UNION ALL No rows
L5–L6 Revenue outside the year of delivery; shipments never invoiced CTEs, CASE, anti-join No rows
L7–L8 Pay periods across a year-end; accrued payroll Derived table, aggregates Unpaid wages accrued
P1–P3 Three-way match, price tolerance, receipts not invoiced CTEs, CASE, tolerances No exceptions; within rounding of account 2020
P4–P6 Purchase order approvals and their concentration UNION ALL, joins to Employee No rows; approval shared
H1–H3 Time records profiled; hours clocked, recorded, and paid; the hours of 2024 Aggregates, CTEs, CASE Complete; equal
H4–H5 Whose time was paid; pay recalculated from hours and rates Joins, UNION ALL, CTE No rows; within a cent
H6–H8 Overtime approvals; days on which everyone clocked the same hours CASE, left joins, CTE Approved; no such days
H9–H10 Direct time dated after its operation ended Inner join to operations, left join to a CTE A small share

Figure 12.11 shows the start of the script, Audit.sql, as the tutorials of this chapter leave it. Views make the library easy to rerun: each test can be saved as a view in the working copy, as in Tutorial 11.3. A summary query can then count, in one result, the rows of the views that list exceptions; a test that returns totals or a status for every line, as L1 and P1 do, is compared with its expected result instead. A view runs on whatever data the file holds, so each rerun starts from a refreshed copy of the data, with the as-of dates in the queries moved to the new period.

A mock of the Execute SQL editor holding the script Audit.sql. A header comment names the script's purpose, the database, the preparer and reviewer, and the checks the tests passed. Below it, each test begins with a two-line comment that gives its tutorial and test number, its objective, its population, and its expected result, followed by the query. The lines shown are the header and the first test, the journal entry reconciliation.
Figure 12.11: The Start of an Audit Query Library.
NoteConnecting the Dots

Chapter 11 saved the controller’s measure as a view, which stores its query and runs it again on the current data. An audit test saved the same way runs again every quarter with a single line, on whatever data the file then holds, and the library of views becomes the SQL counterpart of the workbook of Chapter 8, with one difference: nothing has to be imported or filtered by hand first.

Whatever the form, the library itself needs the controls it tests. Changes to a test should be reviewed and recorded, and each run’s results should be kept as a workpaper, with the date, the data it ran on, and the reviewer. Each result should keep the key of its source record, such as a register line or a clock entry, so that a disposition can be tied to the row, and a row count of a summary query is not the number of items affected. For each exception the workpaper records the condition observed, the evidence requested, and a provisional conclusion until the corroboration, such as the bank records, arrives, and then the final disposition. A library that anyone can change without a record would undermine the evidence it produces.

12.12 Looking Ahead

This chapter has applied SQL to the work of an internal auditor. You reconciled populations to the ledger before testing them, traced postings to their documents and documents to their postings, combined the exceptions into one list with UNION ALL, and tested the cutoff of revenue and payroll with the dates of the events behind them. You performed a three-way match at the level of the purchase order line, reconciled the goods received but not invoiced to the ledger, and tested approvals against the approvers’ limits, roles, and employment dates. You profiled the time records, reconciled the chain of time, labor, payroll, and payment records behind the manufacturing variance, compared the pay with an expectation, tested the chain link by link, and translated the tests of Chapter 8 into queries for a reusable library. In the terms of Table 1.1, most of the tests were diagnostic, the estimate of the unrecorded late-December payroll in Exercise 12.1 was predictive, and the recommendations that close each finding are prescriptive: they tell management what to change.

The audit also answers the committee’s questions. The populations tested reconcile to the ledger, and the traces found only six postings without a document and no document left unposted, but the ledger records revenue and payroll when they are invoiced and paid, so each year-end is misstated by the transactions around it, and no accrual covers the wages of each December’s last weeks, as the pay dates of Tutorial 11.2 first suggested. The purchasing chain matches, with approval exceptions and a single approver behind nearly every order. The hours behind the variance were clocked, recorded, and paid at the rates on file, and the four months of 2024 in which indirect time almost vanished are recorded like the others, as far as the chain of records can show. The plant was building stock then, which is consistent with most hours going to work orders, an explanation that the production schedules would have to confirm. The payroll exceptions are few and small, but they reach the manufacturing payroll. The largest finding is the one the committee did not ask about. The growth in overtime sits on surge days, on which every hourly manufacturing employee clocked the same hours, all approved by one manager and mostly charged to operations that had already ended, and the records alone cannot show that those hours were worked as recorded. Until they are corroborated from outside the payroll system, the committee can accept that the variance is a cost the plant’s records show it incurred, but not yet the explanation of where the hours went.

That also answers, as far as the records can, the question the controller asked at the start of Chapter 9: is production becoming less efficient, or do the standards no longer describe how the plant works? These tests give no reason to revise the standards, though they do not show that the standards are right, which takes a review against corroborated work and current operating conditions. The direct time recorded for the output stayed within them in every year, although which operations it belongs to cannot be relied on, and records that agree cannot show how much work the output required. What grew is the time paid beyond the recorded work: time with no work order, recorded as indirect from the middle of 2024, and overtime, much of it on surge days that the records cannot corroborate. In the records, the hours grew around the work rather than in it, and how much of the extra time was avoidable they cannot measure. Part of the growth from fiscal 2024 is the calendar, too, because fiscal 2024 charged two payrolls fewer to account 1090 than the later years. Of the conclusions of Chapters 10 and 11, the plant-level totals reproduce: output, the hours recorded and paid, and the growth of indirect time and overtime all reconcile within the populations tested, although their explanation still rests on corroborating the hours. The operation-level comparison of Chapter 11 does not hold until time is recorded against open operations, and neither does the surge-day overtime until it is corroborated. What to do about it is for management, once the hours are corroborated. Hours that bring nothing can be cut, and a standard is revised when the work or the conditions it assumes have changed, not to absorb inefficiency. Abnormal costs are charged to the period in which they occur rather than carried in inventory. This chapter completes the SQL chapters of Part III. The Part III Comprehensive Case that follows brings these threads together in the decisions the controller must make next: what the plant should plan for 2027, and what the fiscal 2026 statements need before they are final. SQL has let you ask questions of whole tables at once, from the first SELECT to a library of audit tests. Part IV turns from finding answers to communicating them, with reports and dashboards that let managers and audit committees explore the evidence for themselves.

12.13 Chapter Summary

SQL lets an auditor test entire populations across the tables of a process, and a population test finds the actual exceptions rather than an error rate. Its power depends on its inputs, so every test begins by reconciling the population to the ledger, remembering that two records on the same basis can agree and still omit the same thing, and by profiling it: its records, its dates, and the entries incomplete from the start. The source-document columns of the ledger allow a trace in two directions. An anti-join from the ledger to a document table finds postings with no document, which tests existence and contributes to testing the occurrence of the recorded transactions, and an anti-join from the documents to the ledger finds recorded documents never posted, which tests completeness. Neither proves that the event occurred or finds a transaction omitted from both records. UNION ALL combines the results of several tests into one labeled list of exceptions, matching their columns by position. A cutoff test compares the posting date with the date of the event behind it: the delivery of the goods for revenue, when control passes on delivery, and the work of the pay period for payroll.

A three-way match compares the quantities ordered, received, and invoiced at the level of the purchase order line, after the receipts and invoices of each line are summed, and it compares invoice prices with order prices within a tolerance chosen in advance. Goods received but not yet invoiced are an accrual, and their value should agree with the ledger. Approval tests join each document to its approver and compare it with the approver’s limit, employment dates, and role, and with the document’s creator, and they also reveal how concentrated the approval of a process is. Tests of the records behind a cost reconcile the chain from the time clock through the labor record and the payroll register to the payment and the ledger, and then test each link. A substantive analytical procedure compares a recorded amount with an expectation built from a plausible relationship in other data, such as revenue or commissions predicted from other measures, and investigates differences above a threshold set in advance. Recalculating the pay for the hours at each employee’s rate on file is a recalculation, which supports the arithmetic of the pay on those inputs but not that the hours were worked. Isolated exceptions usually show a control that did not operate; systematic patterns more often show one that is missing or poorly designed. Chapter 8’s aging, stratification, duplicate, sequence, and first-digit tests translate into CASE, GROUP BY, self-joins, LAG, and aggregates, and a documented library of such tests can be rerun every period.

Applied to the audit committee’s questions, the tests found that the populations tested reconcile to the ledger and that the traces found only six postings without a document, but that revenue and payroll are recorded when invoiced and paid, so each year-end is misstated and the wages of late December are never accrued. The six postings that do not trace record cash paid without a payment record, two of those payments for an employee who had left. The purchasing chain matches, and its unbilled receipts agree with the ledger, but its approvals show exceptions and a single point of failure. The manufacturing hours were clocked, recorded, and paid at the rates on file, and the months of 2024 with little indirect time are recorded like the others, while the plant was building stock, a plausible explanation still to confirm. The payroll exceptions are small but reach manufacturing. The most important finding is that the growth in overtime sits on surge days, on which every hourly manufacturing employee clocked the same hours, approved by one manager and mostly charged to operations that had already ended, so those hours need corroboration before the committee relies on the explanation of where they went. These tests give no reason to revise the standards, though they do not show that the standards are right. In the records, the variance grew with the hours paid beyond the recorded work for about the same output, although the tests alone cannot show how much work that output required or how much of the extra time was avoidable.

12.14 Key Terms

Audit query library. A documented set of saved queries that perform an organization’s recurring audit tests, each stating its objective, population, reconciliation, expected result, and the disposition of its exceptions, so that the tests can be rerun every period.

Concentration of approval. A condition in which one person approves most or all of the documents in a process, so that the approval control depends on that person alone. It is a risk rather than an exception in any one document, and it can indicate a weakness in the design of the control.

Cutoff. The recording of a transaction in the accounting period in which it occurred. A cutoff test compares the posting date that first records each transaction with the date of the event behind it, such as the delivery of the goods a sales invoice bills, when control passes on delivery, or the work a payroll pays, and flags the transactions recorded in a different period.

Existence and completeness tests. Tests that trace in opposite directions between the ledger and its documents. An existence test starts from the ledger and asks whether each posting has a document record, which contributes to testing the occurrence of the recorded transactions; a completeness test starts from the documents and asks whether each recorded document was posted. Neither independently proves that the underlying event occurred or finds a transaction omitted from both records. The second is an audit assertion about the ledger, not the data quality dimension of Chapter 2, which asks whether the expected records and values are present in a dataset.

Full-population testing. An audit approach that applies a test to every item of a population rather than to a sample, so that it finds the actual exceptions instead of estimating an error rate.

Goods received not invoiced. The liability for goods or services received from suppliers before their invoices have been recorded. Its balance should agree with the value of the receipts not yet invoiced.

Price tolerance. The difference between an invoice price and an order price that a match accepts without investigation, set in advance from the purchasing policy. Differences above it are exceptions.

Self-join. A join of a table to itself, under two aliases, used to compare each row with other rows of the same table, such as payments to the same supplier for the same amount on different dates. A condition such as b.ID > a.ID keeps each pair once.

Substantive analytical procedure. An analytical procedure that an auditor uses as evidence about whether an amount is misstated, by comparing the recorded amount with an expectation developed from plausible relationships and investigating differences above a threshold set in advance. In SQL, the expectation can be calculated for every record of a population, as Exercise 7.5 did in Excel for revenue and commissions. Recomputing the pay of each earnings line from its hours and the rate the same system stores, as test H5 does, is a recalculation of arithmetic, weaker evidence than an expectation built from independent data.

Three-way match. A comparison of the purchase order, the goods receipt, and the supplier invoice for the same purchase, testing that the company is billed only for what it ordered and received, at the price it approved.

UNION ALL. An operator that stacks the results of two or more queries with the same number of columns into one result, matching the columns by position and keeping every row. UNION, without ALL, also removes duplicate rows.

Unrecorded liability. An obligation that exists at the balance sheet date but is missing from the ledger, such as goods received but never recorded, or wages earned but not yet paid. A reconciliation of two records misses it when both of them omit it, so the auditor looks for it in other evidence: the invoices and payments after the year-end, supplier statements and receipt logs, and the dates of the events behind them.

12.15 Multiple Choice Questions

1. Before testing Charles River’s journal entries, an auditor compares the total of the JournalEntry table with the journal entry debits in the ledger. Why?

  1. To check that the tested population agrees with the ledger before testing it
  2. To find the journal entries approved by the same employees who created them
  3. Because a query cannot read the JournalEntry table without first reading GLEntry
  4. To calculate the risk score that Chapter 8 assigned to each journal entry

2. An anti-join from the PayrollPayment postings in GLEntry to the PayrollPayment table, on SourceDocumentID, returns six rows. What do they show?

  1. Six payroll payments that were made but never posted to the general ledger
  2. Six employees who were each paid twice for the same pay period
  3. Six payments that were dated before their payroll registers were approved
  4. Six ledger postings that point to a payroll payment that does not exist

3. Which test addresses the completeness of the ledger?

  1. An anti-join from GLEntry to a document table, finding postings with no document
  2. A comparison of each supplier invoice’s price with the price on its purchase order
  3. An anti-join from a document table to GLEntry, finding documents never posted
  4. A count of the journal entries that were created on a Saturday or a Sunday

4. Two queries are combined with UNION ALL, and the second lists its columns in a different order from the first. What happens?

  1. SQLite sorts the columns of both queries by name before it stacks the rows
  2. SQLite stops with an error, because the column names of the queries differ
  3. The values are stacked by position, under the column names of the first query
  4. The rows of the second query are left out, because their columns do not match

5. Revenue is recognized when goods are delivered. Goods were shipped and delivered on December 6, 2025, and their invoice was posted to the ledger on January 1, 2026. What does this indicate?

  1. The invoice was posted twice, once in each of the two fiscal years
  2. Its revenue was recorded in the wrong fiscal year, a cutoff error
  3. The invoice has no ledger posting and must be traced to its order
  4. The customer paid the invoice before the goods were delivered

6. A three-way match compares each goods receipt line with each invoice line, row by row, and reports thousands of mismatches. What is the most likely problem?

  1. Quantities were not summed to the purchase order line before comparing
  2. The invoices were matched to the receipts of a different fiscal year
  3. The receipts were valued at standard cost instead of the order price
  4. The comparison needed a price tolerance instead of a quantity tolerance

7. At a price tolerance of 3 percent, no invoice line is an exception; at 2 percent, several thousand are. What should the auditor conclude?

  1. The 2 percent tolerance is correct, because a stricter test finds more exceptions
  2. The 3 percent tolerance is correct, because a test with no exceptions is cleaner
  3. Invoice prices cannot be tested against order prices, because prices change
  4. The tolerance should come from the purchasing policy, set before the test is run

8. Receipts not yet invoiced at year-end are valued within a dollar of the balance of account 2020. What does the agreement show?

  1. Every liability to suppliers at the year-end has been recorded in the ledger
  2. The suppliers have billed Charles River for every receipt of the year
  3. The ledger’s balance is supported by the detail of the open receipts
  4. The purchase orders behind the receipts were approved within their limits

9. On dozens of days of fiscal 2026, every hourly manufacturing employee clocked the same hours at the same minutes, and one manager approved all of the overtime. What should the auditor conclude?

  1. The time may have been entered in bulk, so the hours need corroboration
  2. The overtime is proven false and should be removed from the payroll cost
  3. The pattern is a normal shift schedule and needs no further work
  4. The approvals make the hours reliable, because each day was authorized

10. One executive approved nearly all of Charles River’s purchase orders over three years. What does this indicate?

  1. The purchase orders were all approved above the executive’s approval limit
  2. The approvals were made after the executive’s employment had ended
  3. Nothing, because an approval by a senior executive is always sufficient
  4. A concentration that can indicate a weakness in the design of the control

11. No time clock entry with half an hour of overtime or less has an approval, while nearly every longer one does. What should the auditor do?

  1. Report every short overtime entry as an exception in the audit report
  2. Treat the pattern as an error in the data and leave the entries out of the test
  3. Confirm the policy threshold with payroll, then test the longer entries
  4. Ask the payroll department to remove the short entries from the payroll

12. Among the direct labor entries linked to a valid work order operation, the share of hours whose WorkDate falls after the operation’s ActualEndDate rose to about two-thirds in fiscal 2026. What does this mean for the controller’s analysis?

  1. The manufacturing variance is overstated by about two-thirds of its amount
  2. The records cannot reliably tie the late hours to their operations
  3. The employees were paid for about two-thirds more time than they worked
  4. The standard costs were set too high and should be reduced for fiscal 2027

13. A self-join finds pairs of payments to the same supplier for the same amount. Why does it include the condition b.DisbursementID > a.DisbursementID?

  1. It keeps only the payments that were made after the supplier’s first invoice
  2. It lists each pair once and never pairs a payment with itself
  3. It makes the query faster by reading the payments in order of their IDs
  4. It removes the payments whose amounts were entered more than once

14. Why does the first-digit test use printf('%.15e', Amount) rather than the first character of the amount?

  1. Scientific notation starts with the first significant digit, even below $1
  2. printf rounds each amount to the nearest dollar before its first digit is read
  3. The first character of an amount below $1 is its decimal point, not a digit
  4. SQLite cannot read the first character of a number that is stored as REAL

15. An auditor multiplies the hours of each hourly earnings line by the employee’s base rate, and by 1.5 for overtime, and compares the result with the amount paid. The totals differ by a few dollars a year, and no line differs by more than a cent. What does the result support?

  1. The hours paid were worked, because the pay agrees with the recalculation
  2. The pay fits the recorded hours and rates; the question is the hours
  3. The payroll registers are complete, because they agree with the ledger
  4. The pay rates are wrong, because the totals do not agree to the cent

12.16 Applied Exercises

Work on these exercises in CharlesRiver_Work.sqlite, and save the queries of each exercise in a script of its own, named after the exercise (such as Ex 12.1.sql). Give each script the structure of the audit query library: a header comment, then one test after another, each under a comment that names it and states its population and expected result. Record a disposition for every exception you find.

12.16.1 Financial Accounting Exercises

Exercise 12.1: Cutoff at Each Year-End

Table group: Order-to-Cash, Procure-to-Pay, Payroll and Time, Accounting Core

Scenario. Tutorial 12.1 found revenue and payroll recorded in a later fiscal year than the events behind them. The controller asks for the effect on each year’s revenue, payroll cost, and year-end liabilities, and for the same test on the supplier side, before the fiscal 2026 statements are finalized.

Requirements.

  1. List the sales invoices whose revenue was posted in a different fiscal year from the delivery of the goods they bill, with their SubTotal, invoice date, shipment and delivery dates, and posting date, and list separately the invoices dated before their shipment.

  2. For each fiscal year, total the SubTotal of the revenue recorded in that year for goods delivered in the year before, and of the revenue for goods delivered in that year but recorded in the next one. Add the shipments of fiscal 2026 that were never invoiced, valued at their order price, and calculate the net effect of cutoff on each year’s revenue, as the revenue recorded less the revenue of the year’s deliveries. Explain why the effect on gross margin is larger than the effect on cost of goods sold.

  3. Repeat the test for supplier invoices, comparing InvoiceDate with the posting date of each invoice’s ledger rows. Compare the posting dates with ReceivedDate, and explain which date Charles River posts supplier invoices on.

  4. Total the hourly labor cost of LaborTimeEntry (ExtendedLaborCost) by the year of its WorkDate and the fiscal year of its pay period, and identify the cost of work done in December that was recorded in the next fiscal year. Estimate the full payroll cost of the work of December 14 to 31, 2026, whose pay periods have no registers, from the average cost of the last pay periods of fiscal 2026, and state the assumptions behind the estimate.

  5. Write a short memo (one half page) to the controller with the effect on each year and a recommendation for the year-end close.

Deliverable. A script with the cutoff tests and a half-page memo.

Exercise 12.2: Aging the Open Balances

Table group: Order-to-Cash, Procure-to-Pay, Accounting Core

Scenario. Tutorial 8.2 aged Charles River’s receivables in Excel, and Tutorial 12.2 reconciled the receipts not yet invoiced to account 2020 in total. The controller asks for both open balances at the end of fiscal 2026 in SQL, so that they can be aged and reconciled at every year-end without a workbook: what customers owe Charles River, and what Charles River owes for goods received but not yet billed.

Requirements.

  1. Calculate the open balance of each sales invoice at 2026-12-31, as Tutorial 8.2 did: its GrandTotal, less the receipts applied and the credit memos dated by the as-of date. Keep the as-of date in a one-row CTE, as in Chapter 11, and keep the invoices with a balance above zero.

  2. Age the open balances by days past due with CASE, in the buckets of Table 8.2, and reconcile their total to account 1020 Accounts Receivable. Explain the difference, and what the invoices with a negative balance are.

  3. Total the open balance by customer, grouping by CustomerID, and stratify the customers at $1,000, $10,000, and $50,000. List the customers with $50,000 or more as the key items to confirm, with their share of the open balance.

  4. List the goods receipt lines not fully invoiced at the end of fiscal 2026, valued at their receipt value for the part not yet invoiced, with the supplier and the receipt date, and bound the receipts and the invoices by the as-of date. Total the value by receipt month, and list the five largest suppliers.

  5. Write a short memo (one half page) to the controller on the two open balances: their aging, the difference in account 1020, and the receipts more than two months old.

Deliverable. A script with the aging tests and a half-page memo.

12.16.2 Managerial Accounting Exercises

Exercise 12.3: Where Did the Time Go?

Table group: Manufacturing

Scenario. Tutorial 11.2 showed Packing and Quality Assurance recording several times their planned hours, and Tutorial 12.3 showed that much of the time was recorded after operations had ended, and that the months of 2024 with little indirect time are recorded like the others. The plant manager asks what the work-center comparison looks like when only the time dated while each operation was open is counted, and what the plant was doing in the first months of 2024.

Requirements.

  1. For each direct labor time entry, flag whether its work date is on or before the operation’s ActualEndDate, and count the entries dated before the operation’s ActualStartDate, which would also fall outside the time the operation was open. Keep the entries with no valid operation, and count them as a group of their own.

  2. Repeat the work-center comparison of Tutorial 11.2, with the planned hours of each work center and year, and two recorded-to-planned ratios: one for all direct hours, and one for the hours dated while the operation was open.

  3. Rank the work centers by each ratio, and describe how the ranking changes.

  4. For each month from January to July 2024, count the work orders released and total the units completed and the manufactured units shipped. Explain what may account for the little indirect time from February to May 2024 and what evidence would confirm it. Find the month in which the share of direct time dated after its operation ended first passed a quarter.

  5. Write a short memo (one half page) to the plant manager: what the operation-level data can and cannot support, and what should change in how time is recorded.

Deliverable. A script with the comparison and a half-page memo.

Exercise 12.4: Is the Overtime Approval Doing Its Job?

Table group: Manufacturing, Payroll and Time

Scenario. Tutorial 12.3 found that overtime above half an hour is almost always approved, and that most of the growth in overtime sits on surge days, on which every hourly manufacturing employee clocked the same hours. The controller wants to know whether the approvals are a real control over the growth in overtime, or only a record that it happened.

Requirements.

  1. Count the overtime approvals by year and reason code, and describe how the mix of reasons changed as overtime grew.

  2. Count the approvals by approver and job title, and calculate the share approved by the largest approver.

  3. Compare each approval’s ApprovedHours with its RequestedHours, and its ApprovedDate with the WorkDate of the overtime.

  4. For the surge days of Tutorial 12.3, report who approved the overtime and when, and compare the overtime on those days with the overtime on other days in each year.

  5. Explain what the results say about the design of the overtime approval control, and suggest two changes that would make it a stronger control.

Deliverable. A script with the analysis and a short list of findings and recommendations.

12.16.3 Auditing Exercises

Exercise 12.5: Who Was Paid, and Who Approved the Pay?

Table group: Payroll and Time, Master Data

Scenario. Tutorial 12.1 found two approved payroll registers of an Account Executive who had left, with no payment record, and Tutorial 12.2 found the same employee named as the approver of purchase orders dated after leaving. The chief audit executive asks you to test the pay of everyone who had left, and the approval of every payroll register and payment, and to add the tests to the query library.

Requirements.

  1. List the registers whose pay date is after the employee’s termination date, with the job title, the employee’s own cost center and the register’s, the dates of the pay period, and the gross pay. Add the employee’s usual gross pay, the average gross pay of the registers paid on or before the termination date, with a correlated subquery in the SELECT list, and explain which registers could be a final paycheck.

  2. Count the registers each employee approved, grouping by the approver’s key, and how many were approved on their pay date.

  3. Count the registers approved by the employee they pay, and the payments made before their register was approved, with their net pay.

  4. For each result, state whether it is an isolated exception or a pattern, and what it says about the design of the payroll approval control.

  5. Write a short list of findings and recommendations for the chief audit executive.

Deliverable. A script with the tests and a list of findings.

Exercise 12.6: Chapter 8’s Tests in SQL

Table group: Order-to-Cash, Procure-to-Pay, Accounting Core

Scenario. Chapter 2 promised that the duplicate payment analysis of Exercise 2.6 would be completed in SQL, and Chapter 8 ran the duplicate, near-duplicate, sequence, and first-digit tests in Excel. The chief audit executive asks you to confirm those results in SQL on the supplier invoices and payments and the document series of fiscal 2024 through 2026, so that they run every quarter without a workbook.

Requirements.

  1. Find the supplier invoice numbers that appear more than once for the same supplier, and list both invoices of each pair with their amounts and payments. Repeat the test on invoice numbers standardized with UPPER, TRIM, and REPLACE, as Exercise 2.6 warned, and report whether it finds any pair that the exact test missed.

  2. Find the check numbers used more than once for the same supplier.

  3. Use a self-join to find pairs of payments to the same supplier for the same amount on different dates within 30 days of each other, dated through 2026-12-31, and report whether any pair pays the same invoice.

  4. For the sales invoice, purchase order, cash receipt, and journal entry numbers, extract the year and the sequence number with SUBSTR and CAST, and report the first and last number of each year. Use LAG to find any step greater than one: partitioned by year for a series that restarts each year, and ordered across the years, without a partition, for a series that continues.

  5. Calculate the first significant digit of every supplier payment dated through 2026-12-31 with printf('%.15e', Amount), and the mean absolute deviation from the expected proportions of LOG10(1 + 1.0 / Digit), for all payments, for full payments (Amount equal to the invoice’s GrandTotal), and for partial payments. Compare the results with Tutorial 8.1.

  6. For each test, record the number of exceptions and a disposition, and write a short note on why the near-duplicate test needs a self-join and on what running these tests in SQL gains over a workbook.

Deliverable. A script with the tests and a table of results and dispositions.

12.17 Further Reading

Alles, M., Brennan, G., Kogan, A., & Vasarhelyi, M. A. (2006). Continuous monitoring of business process controls: A pilot implementation of a continuous auditing system at Siemens. International Journal of Accounting Information Systems, 7(2), 137–161. https://doi.org/10.1016/j.accinf.2005.10.004 A report on a system that monitors business process controls for an internal audit department, running with read-only access on top of the company’s enterprise system. The authors found that much more of the audit program could be formalized than expected, and that preventing floods of alarms was a critical task, two lessons for any query library that runs its tests every period.

Huang, F., Cho, S., Lee, K., & No, W. G. (2025). Examining the effectiveness and efficiency of full population testing versus traditional sampling. Accounting Horizons, 39(4), 123–141. https://doi.org/10.2308/horizons-2022-177 An application of full-population testing to substantive tests of purchase transactions, compared with traditional sampling of the same data. The authors find full-population testing more effective and more efficient at identifying problematic transactions, and discuss its costs and what to consider before adopting it.

Institute of Internal Auditors. (2024). Global internal audit standards. https://www.theiia.org/en/standards/2024-standards/global-internal-audit-standards/ The standards of professional practice for internal auditors, issued in January 2024 and effective from January 2025, which the internal audit team of this chapter follows. They set out how an internal audit function plans its work, performs engagements, and reports its findings, and they are the counterpart for internal auditors of the PCAOB’s standards for external auditors.

Kogan, A., Alles, M. G., Vasarhelyi, M. A., & Wu, J. (2014). Design and evaluation of a continuous data level auditing system. Auditing: A Journal of Practice & Theory, 33(4), 221–245. https://doi.org/10.2308/ajpt-50844 A framework for auditing procurement data continuously, simulated on a large sample from a health care provider. Its first layer tests compliance with deterministic business rules, like the match and approval tests of Tutorial 12.2, and its second monitors the process analytically. The paper distinguishes the exceptions of the first layer from the anomalies of the second.

No, W. G., Lee, K., Huang, F., & Li, Q. (2019). Multidimensional audit data selection (MADS): A framework for using data analytics in the audit data selection process. Accounting Horizons, 33(3), 127–140. https://doi.org/10.2308/acch-52453 A framework for the problem that follows full-population testing: tests that flag more items than an auditor can investigate. It identifies problematic items across the whole population and then prioritizes them, so that the auditor’s work goes to the items assessed as having a higher risk of material misstatement.

Public Company Accounting Oversight Board. (2024). Amendments related to aspects of designing and performing audit procedures that involve technology-assisted analysis of information in electronic form (PCAOB Release No. 2024-007). https://assets.pcaobus.org/pcaob-dev/docs/default-source/rulemaking/docket-052/2024-007-adoptingrelease.pdf The release that amended the PCAOB’s standards on audit evidence and on responses to risk, effective for audits of fiscal years beginning on or after December 15, 2025. Among other things, it specifies considerations for investigating the items that a test of details identifies, and the auditor’s responsibilities for evaluating the reliability of external information that the company provides in electronic form. It governs the external audits of public companies and broker-dealers.

Tojiboyev, N., Appelbaum, D., Kogan, A., & Vasarhelyi, M. A. (2022). Basics of SQL for audit data retrieval and analysis. Journal of Emerging Technologies in Accounting, 19(1), 237–265. https://doi.org/10.2308/jeta-2020-021 A teaching note on using SQL queries, in Microsoft Access, to retrieve audit evidence directly from a fragment of the public Walton College enterprise datasets, so that the auditor does not depend on the formatting of reports prepared by others and can begin the analysis sooner.

12.18 References

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

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

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

Public Company Accounting Oversight Board. (2024). Amendments related to aspects of designing and performing audit procedures that involve technology-assisted analysis of information in electronic form (PCAOB Release No. 2024-007). https://assets.pcaobus.org/pcaob-dev/docs/default-source/rulemaking/docket-052/2024-007-adoptingrelease.pdf