Chapter 12: Audit Analytics in SQL

Accounting Analytics: An Integrated Approach

In this chapter

Part III of V: Accounting Analytics with SQL

Chapter 4 of 4 in this Part

• 12.1 From Samples to Populations
• 12.2 Reconciling and Profiling Before Testing
• 12.3 Tracing in Both Directions
• Guided Tutorial 12.1
• 12.5 The Three-Way Match

• 12.6 Testing Approvals Across a Process
• Guided Tutorial 12.2
• 12.8 Testing the Records Behind a Cost
• Guided Tutorial 12.3
• 12.10 Chapter 8’s Tests in SQL
• 12.11 A Library of Audit Queries
• 12.12 Looking Ahead

This chapter tests the analysis of Chapters 9 to 11 before the audit committee relies on it. Each module follows a section of the chapter, and the three guided tutorials build one script, Audit.sql, which becomes a library of audit tests.

Learning objectives

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.

The first three objectives cover reconciling and profiling a population, tracing in both directions with a cutoff test, and the three-way match.

Learning objectives (continued)

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.

The last three cover approval tests, the chain of records behind a cost, and the library that lets the tests run again each period.

Can the records behind the explanation be trusted?

• You are back on Chapter 8’s internal audit team, testing a colleague’s analysis
• The plant recorded about half as many hours again as its output’s standard
• Were the hours worked, approved, charged to the right work, and paid?
• And why does indirect time almost vanish for four months of the first year?

Auditors do not test their own work, so read Chapters 9 to 11 as a colleague’s. The chief audit executive has three assignments, all in the database: the ledger and its populations, the purchasing chain that supplies the plant, and the time, labor and payroll records behind the variance. The tests are also to be saved as a library the team can rerun every quarter.

12.1 From Samples to Populations

SQL tests whole processes, not one table

• A test can follow a purchase or a labor hour across five or six tables
• A sample estimates an error rate; a population test finds the actual items
• Each item found needs a disposition, the auditor’s conclusion about it
• An incomplete table can report a clean result that means nothing

In a workbook, each of those tables would have to be imported and merged, and a test that reaches across a whole process is the kind that shows whether a control worked. The PCAOB amended its standards, which govern the external audits of public companies and broker-dealers, to clarify the auditor’s responsibilities with technology-assisted analysis; internal auditors follow the IIA’s Global Internal Audit Standards. A join on keys that do not match can also report a clean result that means nothing, so every test in this chapter begins with a reconciliation.

12.2 Reconciling and Profiling Before Testing

Reconcile and profile before any test

• A reconciliation is two aggregates: the document table and the ledger
• They should agree to the cent; a difference is the first finding
• A profile says what the population holds: its records, keys and dates
• And how many records are incomplete or unapproved from the start

Exercises 6.1 and 10.1 found invoice lines that differed from the ledger by three invoices posted in the year after their date, and Chapter 8 found a receivables balance that no invoice supported. Left unexplained, either would undermine every test built on the population. The profile is Chapter 10’s, and the AICPA’s audit data analytics guide includes considerations for assessing the reliability of the data.

A reconciliation cannot find what both records omit

• It compares two records, so it misses what both of them left out
• Ledger and payroll registers both record wages on the day they are paid
• So they agree to the cent, even when the last weeks’ wages are in neither
• Tutorial 12.1 meets that limit at each year-end

Chapter 8’s workflow began every test by defining and reconciling its population, and Chapter 4’s documentation worksheet recorded what an analysis rested on. In SQL, the comment at the head of each test plays both parts: it states what the test covers and what a clean result would be.

12.3 Tracing in Both Directions

Trace both ways between the ledger and documents

Figure 12.1 · Tracing in Both Directions Between the Ledger and Its Documents (detail)

Restrict the ledger rows to the document type, then match the document ID.

An anti-join from the ledger to a document table finds postings that point to nothing, which tests existence, or occurrence for transactions, as far as a document record can support it. An anti-join from the documents to the ledger finds documents never posted, which tests completeness. The completeness test finds only documents that were recorded but not posted: a transaction with no document at all escapes it. The figure’s third test, cutoff, follows on its own slide.

UNION ALL lists every test’s exceptions together

SELECT 'Posting with no payment' AS Test, ...
FROM GLEntry LEFT JOIN PayrollPayment ...
UNION ALL
SELECT 'Invoice with no posting', ...
FROM SalesInvoice LEFT JOIN GLEntry ...

• Each query is the anti-join for one kind of document, labeled with its test
• UNION without ALL also removes duplicate rows, rarely what a list wants

Each trace joins on SourceDocumentType and SourceDocumentID and keeps the rows whose other side is NULL. The book prints the full query; Tutorial 12.1 stacks four such tests into one list of exceptions.

Watch out: UNION ALL matches columns by position

• Columns are matched by position, not by name
• The result takes its column names from the first query
• Columns in another order stack their values under the wrong headings
• ORDER BY sorts the combined result, so it goes once, at the end

No error says so. Check that every query lists its columns in the same order before you trust the combined list.

Cutoff compares the posting with the event behind it

• PostingDate says when it was recorded; the event has its own date
• Revenue on delivery, as the chapter assumes: compare the delivery dates
• An invoice dated before its goods shipped needs correction or a reason
• Wages belong to the period of the work: compare the work with the pay date

Revenue from goods belongs to the period in which control passed to the customer, which depends on the terms of the sale; the chapter assumes it passes on delivery, reads Shipment.DeliveryDate as the date the goods arrived, and assumes that invoices are dated no earlier than the goods ship. Services ship nothing, so their revenue needs a test of its own. 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. Tutorial 3.2 traced one sale; these tests trace whole tables at once, and Exercise 10.5 already found six payroll payment postings with no payment, which Tutorial 12.1 explains.

Check your understanding

Which test finds each problem?

1. A ledger posting whose document does not exist
2. A supplier invoice that was never posted
3. Revenue posted in January for goods delivered in December
4. A sale for which no document was ever created

Ask students which of the four no trace can find.

Each problem has its test

1. Existence: an anti-join from the ledger to the documents
2. Completeness: an anti-join from the documents to the ledger
3. Cutoff: the posting date against the delivery date
4. No trace finds it: there is no record to join

The completeness test finds only documents that were recorded but not posted. A transaction with no document escapes every trace in the database, so it needs evidence from outside the records.

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

Reconcile, trace and test cutoff in eight steps

1. Reconcile the journal entries to the ledger
2. Reconcile the manufacturing payroll to the ledger
3. List the trace exceptions in both directions
4. Explain the six postings
5. Test the cutoff of revenue
6. Find the shipments never invoiced
7. List the pay periods that cross a year-end
8. Look for an accrual of the unpaid wages

Students start Audit.sql with a header comment. Each test goes at the end of the script under a two-line comment: the first names the test, and the second states its population and the result expected if the controls worked.

Six postings point to no payment

Figure 12.2 · The Trace Exceptions in One List (detail)

The four traces, two from the ledger to the payments and two from the invoices to the ledger, return six rows, all from the first test: three pairs, each a debit to accrued payroll and a credit to cash, in January of each year. Every supplier payment has its document, and every sales and supplier invoice was posted. The DocumentID column shows which missing payment each pair points to, and Step 4 matches the six to three approved registers with no payment: one pays the chief executive, and two pay an Account Executive who left in the first year, charged to the Executive cost center at the chief executive’s gross pay. Only the bank records can confirm who received the cash.

December deliveries were posted in January

Figure 12.3 · Revenue Posted Outside the Year of Delivery (detail)

More than twenty invoices for goods delivered in December were posted in January, so their revenue fell in the next fiscal year, while their cost was recorded when the goods shipped. Eight invoices are dated before their shipment but were posted in the year of delivery: errors in dating, not in posting. A ninth dated before its shipment was also posted in the next year, and the CASE counts only its first finding. The test covers only the invoices for goods; the design services ship nothing. Step 6 finds a handful of the latest December’s shipment lines that were never invoiced.

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 reconciliations agree to the cent; the payroll agrees because both sides record it on its pay date. Agreement of totals supports the populations’ completeness, but it is not a comparison of every record, and an anti-join compares IDs, not amounts. The six postings are the only exceptions of the trace, and they record cash paid without a payment record.

Checkpoint (continued)

• 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, and its weakness is timing. Revenue and payroll are recorded when they are invoiced and paid, so each year-end is misstated, and the accrued payroll account holds only its opening balance: the wages of each December’s last weeks are charged to the next year.

12.5 The Three-Way Match

Sum each side to the purchase order line

Figure 12.4 · The Three-Way Match at the Level of the Purchase Order Line (detail)

The line was received in two deliveries and invoiced on three invoices. Compared row by row, a receipt and an invoice line disagree wherever a delivery was split; summed to the purchase order line first, in CTEs, the three quantities agree, even though one receipt line was billed on two invoices. Summing before comparing is Chapter 10’s cure for fan-out: joined directly, the two receipt lines and three invoice lines would make six rows.

Each line ends in one status

Figure 12.4 · The Three-Way Match at the Level of the Purchase Order Line (detail)

The CASE gives each line the first status whose condition it meets, so the exceptions are tested first and none can hide under a normal status. A line received but not fully invoiced is not an exception: it is an accrual.

Tolerances, prices and unbilled receipts

• Quantities are REAL, so two sums are equal within a tiny tolerance
• Some price difference is normal: the purchase price variance records it
• A tolerance, such as two or three percent, separates the exceptions
• Goods received but not invoiced are a liability to agree with the ledger

A sum of fractional quantities can differ from the quantity ordered by a tiny rounding amount, so the match never tests for exact equality. Exercise 9.2 met the purchase price variance. Choose the price tolerance before the test is run, from what the purchasing policy allows. Chapter 1 named the three-way match among the tests the Procure-to-Pay tables support.

12.6 Testing Approvals Across a Process

Was each approver entitled to approve?

• Join each document to its approver’s Employee row
• An amount above MaxApprovalAmount is an approval above authority
• One dated after TerminationDate may have come from someone who had left
• A creator who also approves breaks the segregation of duties

Where a table records no approval date, as PurchaseOrder does not, the document’s own date stands in for it, so a document dated after a termination is a question for the approval records. 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.

Concentration is a risk, not an exception

• Approval tests look for a few exceptions in a large population
• They often return a clean result, and that is evidence too
• Provided the population was reconciled first
• If one person approves nearly everything, the control rests on one review

If that one review missed an error, nothing else would notice unless another control, such as a budget review or a reconciliation, would catch it. Concentration can therefore indicate a weakness in the design of the control, rather than an exception in any one document, and the auditor asks what else would detect an error.

Check your understanding

Exception, accrual or risk?

1. A split receipt: ordered, received and invoiced quantities agree when summed at the order line
2. Goods received at the year-end that the supplier has not yet billed
3. A purchase order above its approver’s limit
4. One person approves nearly every purchase order

Ask students which of the four needs a disposition for a single document.

Each finding has its meaning

1. No quantity exception: the summed quantities agree at the purchase order line
2. An accrual: a liability to agree with the ledger
3. An exception: an approval above authority
4. A risk: a possible weakness in the control’s design

Only the third needs a disposition for the document itself. The fourth goes in the report as a design risk to investigate.

Guided Tutorial 12.2: Matching Purchases and Testing Their Approvals

Match and test the approvals in six steps

1. Match every purchase order line
2. Test prices against two tolerances
3. Reconcile the goods received but not invoiced
4. Test the approvals of the purchase orders
5. List the orders behind the exceptions
6. Measure the concentration of approvals

Students match every purchase order line with its receipts and invoices, test prices at two tolerances, reconcile the unbilled receipts to the ledger, test the approvals, list the orders behind the exceptions, and measure how concentrated the approvals are.

No line was billed or received beyond its order

Figure 12.5 · The Three-Way Match of Every Purchase Order Line (detail)

Nearly every line is matched; the others are not yet received, partly received, or received but not fully invoiced, all normal at a year-end. COALESCE turns the NULL of a line with no receipts or invoices into zero. At three percent no invoice price is an exception, while at two percent several thousand lines would be. The unbilled receipts agree with the ledger to within a dollar, but a receipt never recorded would be missing from both, which is why auditors also search for unrecorded liabilities after the year-end.

A handful of orders fail the approval tests

Figure 12.6 · Approval Exceptions in the Purchase Orders (detail)

Step 5 lists fourteen orders, most failing two tests. The two largest are capital-equipment orders created and approved by the Purchasing Manager, above even the chief financial officer’s limit. Three name the Account Executive of Tutorial 12.1 as approver and are dated after that employee had left, and several small ones by buyers and analysts with a limit of zero. Step 6 shows that the chief financial officer approved nearly every other order.

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 above what was received, prices were close to the orders, and its unbilled receipts agree 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 chain of records behind the labor cost

Figure 12.7 · The Chain of Records Behind the Labor Cost

An employee clocks in and out, the hours are recorded against a work order operation or as indirect time, the payroll register pays for them through earnings lines that point to the labor time, a payment disburses the net pay, and the ledger records the cost and the payment. Tutorial 12.3 tests the hours and the pay for them; Exercise 12.5 tests the pay of people who had left and the approval of the registers.

Test each link of the chain

• The hours clocked were recorded and paid, to the person who worked
• Overtime was approved, and the records look like individual work
• Direct time was not charged to work already finished
• Each register was approved independently, and paid after approval

Records entered in bulk, rather than clocked by each employee, are one of the things the third assignment looks for. The people paid must also have been employed, and paid what they earned.

Isolated exceptions and systematic patterns

• An isolated exception usually shows a control that did not operate
• A systematic pattern more often shows a control missing or poorly designed
• Both are control deficiencies; which kind depends on the cause
• A pattern can mean the records cannot support some conclusions

A single register paid to a terminated employee is isolated; a large share of hours recorded after their operations ended is a pattern. A pattern does not necessarily mean that anyone was paid for time not worked. The auditor reports both kinds and distinguishes them.

A recalculation tests arithmetic, not hours

• A substantive analytical procedure compares an amount with an expectation
• The expectation comes from a plausible relationship; the threshold is set in advance
• For payroll, each line’s hours times the employee’s rate recompute the pay
• That is a recalculation: it tests the arithmetic, not whether the hours were worked

Chapter 6 defined the procedure, which the PCAOB’s standards describe, and Exercise 7.5 built expectations for revenue and commissions with regression. A reconciliation asks whether two records agree, a recalculation whether an amount follows from its inputs, and an expectation whether an amount is what its drivers imply. Test H5 uses rates the payroll system itself stores, so it is a recalculation; evidence from an independent source can strengthen an expectation, but its relevance and reliability must still be evaluated. Payroll is a common area for population testing because its typical frauds leave traces: employees paid after they left, hours paid but never clocked, overtime without approval, and time entered in bulk. Such tests often run every pay period, so that an exception can still be corrected.

Check your understanding

Isolated exception or systematic pattern?

1. A register paid to an employee who had left
2. Most direct time recorded after its operation ended
3. One unapproved overtime entry
4. Every hourly employee clocking the same minutes on many days

Ask students what each suggests about the control behind it.

Each exception has its kind

1. Isolated: a control that did not operate
2. A pattern: a control missing or poorly designed
3. Isolated: one entry that needs a disposition
4. A pattern: time that looks entered in bulk

Which kind an exception is depends on its cause, which the auditor must find. The second and fourth are the patterns Tutorial 12.3 finds.

Guided Tutorial 12.3: Testing the Hours Behind the Variance

Steps 1 to 5: profile, reconcile and test the pay

1. Profile the time records
2. Reconcile the hours along the chain
3. Look at the months with little indirect time
4. Test whose time was paid
5. Recalculate the pay from the hours and the rates

The profile fixes the dates over which the totals are measured, although indirect time ends a week before the last day. The hours clocked, recorded and paid agree in every pay period the records share, and the four quiet months are recorded like any others. The plant was building stock then, completing thousands of units more than it shipped, which is consistent with most hours staying on work orders, a plausible explanation still to confirm. Every earnings line agrees with its recalculation from its hours and the paid employee’s rate on file to within a cent.

Steps 6 to 10: overtime, surge days and late time

6. Test overtime against its approvals
7. Look for days on which everyone clocked the same hours
8. Test the work dates of direct labor
9. Find where the late time was charged
10. Complete and save the script

No overtime of half an hour or less has an approval, and nearly every longer entry does, which suggests a policy the team should confirm with the payroll department. This chapter calls a day on which every hourly manufacturing employee recorded the same hours a surge day.

Surge days carried more and more of the overtime

Figure 12.8 · Days on Which Every Hourly Manufacturing Employee Clocked the Same Hours (detail)

The number of surge days grew each year, and by the latest year their overtime was more than two-thirds of the plant’s, while overtime on other days stayed about the same. This is the most important finding of the audit. The query counts the employees who clocked in, and a separate count against the Employee table shows they are the plant’s whole hourly staff, one entry each. Identical times for a whole plant look like time entered in bulk, or a schedule recorded in place of the clock; they do not prove that the hours were not worked, but the records cannot show that they were.

On an ordinary day, times and hours differ

Figure 12.9 · A Day Before the Surge Days and a Surge Day (detail)

Six Assemblers on a day 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. The line under the table summarizes the day for every hourly manufacturing employee: many different clock-in times and numbers of hours.

On a surge day, everyone clocks the same minutes

Figure 12.9 · A Day Before the Surge Days and a Surge Day (detail)

The same six on a surge day clock in and out at the same minutes on one of two shifts, with eight regular hours and four and a half of overtime, all approved by the Production Manager on the work date; a clock-out after midnight falls on the next day. Every hourly manufacturing employee clocked in at one of two times and recorded the same hours.

Late direct time grew, mostly on surge days

Figure 12.10 · Direct Labor Recorded After Its Operation Ended (detail)

The share of the direct hours recorded against an operation that came after the operation had ended rose from about a fifth to half and then to about two-thirds, and most of it fell on surge days. Step 9 finds it at Packing, where nearly all of the latest year’s direct time came after the operation ended, and at Quality Assurance: much of the time above plan there in Tutorial 11.2 was charged to finished work.

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;

The time that pays one employee’s hours to others belongs to an Inventory Specialist who had left; the amounts are small, but they reach the manufacturing payroll, and each needs a disposition.

Checkpoint (continued)

• 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 hours were recorded, paid at the rates on file, and posted, so the variance is a cost the records say the plant incurred. 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

Chapter 8’s tests become queries

• An aging or a stratification is a CASE, counted and totaled with GROUP BY
• Days past due come from julianday and an as-of date in a one-row CTE
• Duplicate invoices or payments: GROUP BY with HAVING more than one row
• Near-duplicates: a self-join on supplier and amount within a window of days

GROUP BY does what COUNTIFS and SUMIFS did in Chapter 8, and the as-of date in a CTE lets the aging run again at another year-end. The book’s near-duplicate query uses a two-week window, and ABS counts the gap in either direction because payment IDs are not always in the order of their dates. Exercise 12.2 ages the open balances in SQL.

Sequences and first digits

• LAG compares each document number with the one before it
• A series that restarts each year is partitioned by year
• printf('%.15e', Amount) writes the first significant digit first
• LOG10(1 + 1.0 / Digit) gives Benford’s expectation for each digit

A series that continues from one year to the next, such as the sales invoice numbers, is ordered without a partition, so a gap at the year boundary is not missed. Leave zero and negative amounts out of the first-digit test. Fifteen decimals keep printf’s rounding from carrying an amount such as 999,999.99 to 1.000000e+06, which would read as a 1. LOG10 is one of SQLite’s math functions, and SELECT LOG10(10.0) returns 1 where they are present; the Windows release of DB Browser for SQLite includes them, while DB Browser for SQLCipher lacks them and answers “no such function: LOG10”, and a CASE that lists the nine expected proportions can take its place. Grouped and compared, the digits give the same mean absolute deviation as the workbook of Tutorial 8.1, and Exercise 12.6 builds the other tests.

Watch out: a self-join pairs every row twice

• Without b.DisbursementID > a.DisbursementID, a row pairs with itself
• And every other pair is listed twice, once in each order
• A condition on the dates removes only the first problem
• Count the rows with and without the condition before you trust it

In the near-duplicate test, requiring different payment dates happens to remove the pairs of a payment with itself, but only the condition on the IDs keeps one row for each pair.

12.11 A Library of Audit Queries

The library’s ledger tests

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

A test that is saved, documented and run again every period becomes part of the company’s control environment. Each test in the library states its objective, its population and the reconciliation that supports its completeness, the query, the expected result, and the disposition of any exception.

The library’s purchasing and hours tests

Table 12.1 · The audit query library of this chapter

Tests	What they test	Technique	Expected result
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

An expected result is not always “no rows”: the purchasing tests expect the unbilled receipts to agree with the ledger account within rounding, and the late direct time is expected to be a small share.

Each test opens with what it tests

Figure 12.11 · The Start of an Audit Query Library (detail)

The header names the script’s purpose, the database, the preparer and reviewer, and the checks the tests passed. Each test begins with a two-line comment that gives its tutorial and test number, its objective, its population and its expected result.

The library needs the controls it tests

• Save each test as a view; count the rows of those that list exceptions
• Review and record every change to a test
• Keep each run’s results as a workpaper: the date, the data, the reviewer and each item’s key
• A library that anyone can change without a record undermines its evidence

Saved as views, as in Tutorial 11.3, the tests run again every quarter with a single line, on whatever data the file holds, so each run starts from a refreshed copy with its as-of dates moved. A test that returns totals or a status for every line, such as L1 or P1, is compared with its expected result rather than counted. The library of views becomes the SQL counterpart of Chapter 8’s workbook, with nothing to import, filter or refresh by hand.

12.12 Looking Ahead

What the audit found

• The ledger reconciles and traces, but no year-end accrues the late wages
• Purchasing matches, with approval exceptions and one dominant approver
• The hours were clocked, recorded and paid; the quiet months match a stock build
• The surge-day overtime cannot be corroborated from the records alone

Revenue and payroll are recorded when invoiced and paid, so each year-end is misstated. The payroll exceptions are few and small, but they reach manufacturing. Until the surge-day hours are corroborated from outside the payroll system, the committee can accept the variance as a cost the records show, but not yet the explanation of where the hours went.

Do the standards still fit?

• These tests give no reason to revise them, though they do not show they are right
• What grew is the time paid beyond the work: indirect time and overtime
• In the records, the hours grew around the work, not in it
• Part of the growth is the calendar: the first year charged two payrolls fewer

Direct time stayed within the standards, and a review against corroborated work and current operating conditions would be needed to show they are right. This answers the question the controller asked at the start of Chapter 9. The plant-level totals of Chapters 10 and 11 reproduce within the populations tested, although records that agree cannot show how much work the output required. The operation-level comparison does not hold until time is recorded against open operations, and neither does the surge-day overtime until it is corroborated. How much of the extra time was avoidable, the records cannot measure. Management decides what to do 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. The Part III case asks what the plant should plan for and what the latest statements need before they are final.

Summary

• Reconcile and profile a population before testing it
• Anti-joins trace both ways, UNION ALL lists the exceptions, dates test cutoff
• The three-way match compares sums at the order line, within tolerances
• Approval tests check each approver’s authority and concentration
• The chain behind a cost is tested link by link, with the pay recalculated

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. Part IV turns from finding answers to communicating them, with reports and dashboards.

Key terms

Audit query library · Concentration of approval · Cutoff · Existence and completeness tests · Full-population testing · Goods received not invoiced · Price tolerance · Self-join · Substantive analytical procedure · Three-way match · UNION ALL · Unrecorded liability

Every term is defined at the end of the chapter. Existence and completeness, cutoff, and concentration of approval are the ideas the audit’s findings rest on.

Practice and next steps

• Financial Accounting
  • Exercise 12.1: Cutoff at Each Year-End
  • Exercise 12.2: Aging the Open Balances
• Managerial Accounting
  • Exercise 12.3: Where Did the Time Go?
  • Exercise 12.4: Is the Overtime Approval Doing Its Job?
• Auditing
  • Exercise 12.5: Who Was Paid, and Who Approved the Pay?
  • Exercise 12.6: Chapter 8’s Tests in SQL

Next: Comprehensive Case: Planning the Plant’s Hours for 2027

Assign one exercise from each perspective; Exercise 12.1 measures the cutoff at each year-end, Exercise 12.3 asks where the time went, and Exercise 12.6 moves Chapter 8’s tests into SQL. The Part III case follows, then Part IV turns to Power BI.

About these slides

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

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

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