16  Audit Monitoring and Report Assurance

Learning Objectives

After completing this chapter, you will be able to:

  1. Distinguish continuous auditing from continuous monitoring, and exceptions from anomalies, and specify a monitoring test by its rule, population, frequency, and expected result.
  2. Build an exception register in Power Query that turns the flags of several tests into one table of exceptions, with a table of tests and the populations needed for rates.
  3. Build a monitoring page that shows exception counts and rates by test and month and lets a reviewer drill through to the documents behind them.
  4. Record a disposition for each exception in a table that survives every refresh, and decide which open exceptions a reviewer should examine first.
  5. Test a report before relying on it, by reconciling its populations, rerunning its tests, and establishing the date through which its data run.
  6. Explain the change controls that a report used every month needs, and why uncontrolled copies of its file escape them.
Chapter slides View slides Download PowerPoint

Opening Scenario

It is May 2027, and you are back on the internal audit team of Chapters 8 and 12. The chief audit executive brings three requests. The first comes from the audit committee. The tests of Chapters 8 and 12 found journal entries approved by the people who created them, purchase orders dated after their approver had left the company, and pay approved by the employee being paid. The committee wants to know every month whether such exceptions recur, rather than wait for the next annual review. The second is the chief audit executive’s own. A monthly report is useful only if each exception is reviewed once and the conclusion kept, so that next month’s review starts from the exceptions not yet reviewed rather than from the beginning. The third comes from the controller, who now presents Charles River Reports, the package of Chapters 13 to 15, at the managers’ monthly meeting, plans to give each manager access of their own, and asks whether internal audit can rely on it too.

Your task is to build a monitoring report for internal audit, to record and prioritize the review of its exceptions, and to test both reports before anyone relies on them. This chapter teaches continuous monitoring and the design of an exception register, a monitoring page that leads from exception counts to documents, the review of exceptions over time, and the audit of a report itself: its populations, its tests, the currency of its data, and the control of changes to it. How a report is published, secured, and shared, which needs a Power BI license to practice in full, is the subject of the appendix on publishing and security.

16.1 From Audit Tests to Monitoring

Chapter 8 tested whole populations once, in a workbook, and Chapter 12 saved its tests as a library of queries that the team could run again every quarter. Monitoring takes the next step: the same tests run every period, on new data, with the results kept and compared over time. Vasarhelyi and Halper (1991) described one of the first such systems, built for the internal auditors of AT&T Bell Laboratories, which measured key figures of large systems, compared them with standards, and called the auditors’ attention to problems as they arose. Two decades later, an adoption survey and pilot implementations at two firms showed continuous auditing moving from research into practice (Alles et al., 2008).

Two terms describe this work, and they differ in who does it. Continuous auditing is internal audit’s use of technology to assess risks and controls on an ongoing basis, so that it can give the board and management assurance throughout the year rather than once (Institute of Internal Auditors [IIA], 2025). Continuous monitoring is management’s own ongoing review of whether its controls operate as designed. Many auditors hold that monitoring belongs to management, not to internal audit, but effective continuous auditing depends on strong monitoring, and internal audit can help a business build it (Tronto & Killingsworth, 2021). In this chapter you build a monitoring report as internal audit’s tool, and the same tests could be handed to management to run as its own monitoring.

Monitoring tests are of two kinds. Kogan et al. (2014) separate a first layer that checks deterministic business rules, such as an approval within the approver’s limit, from a second layer of analytical monitoring that looks for unusual patterns. A rule either holds or fails, and a failure is an exception in the sense of Chapter 8, a record that meets a test’s criteria and therefore needs an explanation. Some rules test a policy, such as an approval within the approver’s limit; others, such as an entry keyed on a weekend or in whole thousands, pick out a condition that is unusual rather than forbidden. Either way an exception is a record selected for review, and only the review shows whether it is expected, a deficiency, or a misstatement. An analytical test produces anomalies: records or periods that differ from what is expected, such as two payments of the same amount to the same supplier a few days apart, or first digits that depart from Benford’s Law. An anomaly is a reason to look, not yet a failure. The exception register you build in this chapter holds the rule tests; the anomaly tests of Chapters 8 and 12 return in the exercises.

A monitoring system that runs every month can produce more exceptions than anyone can review, a problem this chapter returns to once the register is built. A practical first defense is design. Each test should state its rule, its population, and the result expected, as the comment above each query of Chapter 12’s audit query library did, and also how often it runs, so that a reviewer can tell an expected exception from a new one. Metcash, an Australian distributor, ran more than a hundred automated tests daily, with an integrated system for managing their exceptions and visual reports for reviewing them (Hardy & Laslett, 2015). Table 16.1 lists the tests of this chapter, all of them rules that the book has already run once, each with the process whose documents it tests and where it was first run.

Table 16.1: The monitoring tests
TestID Test Process Source
JE Weekend Entry created on a weekend Journal entries Chapter 8
JE Backdated Entry created after its posting date Journal entries Chapter 8
JE SelfApproved Entry approved by its creator Journal entries Chapter 8
JE AboveLimit Entry above its approver’s limit Journal entries Chapter 8
JE RoundAmount Entry in whole thousands Journal entries Chapter 8
PO SelfApproved Order approved by its creator Purchase orders Chapter 12
PO AboveLimit Order above its approver’s limit Purchase orders Chapter 12
PO AfterTermination Order dated after the approver left Purchase orders Chapter 12
PR PaidBeforeApproval Pay made before the register was approved Payroll Exercise 12.5
PR AfterTermination Pay period ending after termination Payroll Exercise 12.5
PR SelfApproved Register approved by its own employee Payroll Exercise 12.5

Who sees the results matters too. Gonzalez and Hoffman (2018) found in an experiment that telling people promptly which of their transactions a monitoring system had flagged also told them what it could not detect, and that when the system was weak, prompt notification made participants more inclined to commit fraud. A monitoring report therefore belongs with internal audit’s audit workpapers, which is why this chapter builds it in a file of its own rather than as pages of the controller’s package. The report, with what its tests can and cannot detect, goes to the people who oversee the controls. The people whose approvals it tests still hear of the exceptions they must explain or correct, through internal audit’s questions and reports rather than through the report itself. The experiment is a reason to think about what such notices reveal and when they go out, not a rule that the people responsible for a control never learn its results. Figure 16.1 shows the cycle a monitoring test follows.

A cycle diagram. A rule runs on each period's population and produces exceptions; the exceptions go to internal audit's monitoring report, where each receives a disposition; dispositions feed back into the rule, its threshold, or the control. A side panel distinguishes continuous auditing, run by internal audit for assurance, from continuous monitoring, run by management on its own controls, and notes that the report goes to the people who oversee the controls, while those whose transactions are tested hear only of the exceptions they must explain.
Figure 16.1: The Monitoring Cycle.

16.2 Designing an Exception Register

An exception register is a single table that holds the exceptions of every monitoring test, one row for each exception of each test, with the same columns whatever the test: the test that found it, the document, its date, its amount, and the employee responsible, here the approver. Chapter 12 built the same thing in SQL, when UNION ALL stacked the exceptions of several queries into one result, each labeled with the test that found it. In Power BI the register is built in Power Query, so that every refresh runs the tests again on the new data. Table 16.2 shows where each column comes from in the three processes this chapter monitors.

Table 16.2: The columns of the exception register
Register column Journal entries Purchase orders Payroll registers
TestID JE and the flag name PO and the flag name PR and the flag name
DocumentNumber EntryNumber PONumber Register and its ID
EventDate PostingDate OrderDate PayDate
Amount TotalAmount OrderTotal NetPay
EmployeeID ApprovedByEmployeeID ApprovedByEmployeeID ApprovedByEmployeeID

A document that fails two tests appears twice in the register, each time with its full amount, so an exception is identified by its test and its document together, and the amounts describe the documents behind the exceptions rather than add up to a total. EventDate is the date each process records for its documents; for a payroll register it is the pay date of its period, which can differ from the date of the payment.

The register takes three moves. First, each test becomes a flag column, 1 if the record fails the rule and 0 if it passes, the Power Query counterpart of the flag formulas Chapter 8 wrote in the columns of an Excel table, and the flags of a document add up to its risk score. Second, unpivoting turns the flag columns of a table into rows. Each record becomes one row per flag, with the flag’s name in a column named Attribute and its value in a column named Value (Microsoft, 2025), and keeping only the rows whose value is 1 leaves exactly the exceptions. Third, appending stacks the exception tables of the three processes into one (Microsoft, 2026a). Append is Power Query’s counterpart of UNION ALL, with one difference that matters: UNION ALL matches columns by their position, while Append matches them by their names, and a column that one table lacks is filled with nulls. Each exception table must therefore rename its columns to the register’s names before the append, or the result has a DocumentNumber column that is empty for one process and a PONumber column that is empty for the others.

A count of exceptions means little without the size of the population it came from. A dozen exceptions among a few hundred journal entries and a dozen among many thousands of purchase orders are very different rates. The register therefore sits beside the populations it tests, which stay in the model as fact tables of their own, and a measure divides the exceptions of each test by the documents of its process. The populations are also what the auditor reconciles before relying on the tests, as Chapters 8 and 12 did: if the journal entries in the model do not add up to the journal entry postings in the ledger, records may be missing or duplicated, values wrong, or the two sides drawn on different scopes, and the difference is reconciled before anyone relies on the tests. Figure 16.2 shows the model.

A model diagram. Three flagged tables, JournalEntry, PurchaseOrder, and PayrollRegister, each feed an exception query through Unpivot and a filter on the flag value; the three exception queries are appended into one Exceptions table. Exceptions relates to Tests on TestID, to Date on EventDate, and to Employee on EmployeeID, and Employee also relates to JournalEntry through the entry's approver. The three flagged tables remain in the model as populations, related to Date, and JournalLines relates to JournalEntry.
Figure 16.2: The Exception Register in the Monitoring Model.

16.3 Guided Tutorial 16.1: Journal Entry Monitoring

Context and objective. In this tutorial, you will start internal audit’s monitoring report, a new file of its own. You will rebuild Chapter 8’s five journal entry tests as flag columns in Power Query, add up each entry’s risk score, and look at the entries with the highest scores together with the ledger lines behind them. Then you will turn the flags into a table of exceptions, the first part of the exception register, and reconcile the population of entries to the ledger before relying on any test.

Prerequisites. You need Power BI Desktop and CharlesRiver.xlsx. Tutorial 14.1 created the Date table and Tutorial 14.2 the Key Measures table, and this tutorial repeats both briefly in a new file. The two settings that Tutorial 13.1 turned off belong to that report, so Step 1 turns them off again in this one.

Step-by-step instructions.

Step 1. Start the monitoring file.

  1. Start a new report in Power BI Desktop, and save it as Audit Monitoring.pbix.
  2. Choose File > Options and settings > Options, and under Current File, select Data Load.
  3. Clear Auto date/time and Autodetect new relationships after data is loaded, and click OK.
  4. Choose Home > Get data > Excel workbook, and select CharlesRiver.xlsx.
  5. In the Navigator, select T2_JournalEntry and T74_Employee, and click Transform Data.
  6. Rename the queries JournalEntry and Employee.

The two options apply to this file only, as they did to the report of Tutorial 13.1. The second matters here, because Power BI would otherwise relate Employee to any table with a column of the same name, such as the payee’s EmployeeID on a payroll register, and Tutorial 16.2 and Exercise 16.6 depend on Employee reaching the journal entries and not the orders or the registers. You create every relationship yourself, and Model view shows each one.

Step 2. Keep the columns the tests need.

  1. In Employee, keep EmployeeID, EmployeeName, JobTitle, CostCenterID, EmploymentStatus, TerminationDate, and MaxApprovalAmount, and set TerminationDate’s type to Date.
  2. In JournalEntry, keep JournalEntryID, EntryNumber, PostingDate, EntryType, TotalAmount, CreatedByEmployeeID, CreatedDate, and ApprovedByEmployeeID.
  3. Set PostingDate’s type to Date and CreatedDate’s type to Date/Time.

CreatedDate records when an entry was keyed, with its time; PostingDate is the date it affects the ledger. Two of the tests compare them.

Step 3. Bring in the approver’s limit.

  1. Select JournalEntry, choose Home > Merge Queries, and merge it with Employee on ApprovedByEmployeeID and EmployeeID, with a left outer join.
  2. Expand only MaxApprovalAmount, without the prefix.
  3. Rename the new column ApproverLimit.

Each entry now carries the limit of the person who approved it, the column Tutorial 8.3 added with the same merge.

Step 4. Add the first three flags. Each flag is 1 when the entry fails the test and 0 when it passes, so the flags of an entry add up to its risk score.

  1. Choose Add Column > Custom Column, name the column Weekend, and enter the first formula below.
  2. Add Backdated with the second formula, in the same way.
  3. Add SelfApproved with the third formula.
if Date.DayOfWeek([CreatedDate], Day.Monday) >= 5 then 1 else 0
if Date.From([CreatedDate]) > [PostingDate] then 1 else 0
if [CreatedByEmployeeID] = [ApprovedByEmployeeID] then 1 else 0

Date.DayOfWeek counts from the day named in its second argument, so with Monday as day 0, Saturday is 5 and Sunday 6, the same test as Chapter 8’s WEEKDAY with return type 2. Date.From drops the time from CreatedDate before comparing it with the posting date.

Step 5. Add the last two flags and the score.

  1. Add AboveLimit and RoundAmount with the first two formulas below.
  2. Add RiskScore with the third formula.
  3. Select the six new columns and set their type to Whole Number.
if [TotalAmount] > [ApproverLimit] then 1 else 0
if Number.Mod([TotalAmount], 1000) = 0 then 1 else 0
[Weekend] + [Backdated] + [SelfApproved] + [AboveLimit] + [RoundAmount]

Figure 16.3 shows the query with its flags. Most entries carry five zeros. The flags are the rules of Table 16.1, written once in the query, so every refresh applies them to whatever entries the workbook holds.

A mock of the Power Query editor showing the JournalEntry query after the flag columns are added. Each row is a journal entry, shown by its number, followed by the columns Weekend, Backdated, SelfApproved, AboveLimit, RoundAmount, and RiskScore; the other columns of the query are scrolled out of view. Most rows show zeros; a few rows of January entries show ones, and their risk score is outlined. The Applied Steps pane lists the merge, the expansion, and one step for each added column.
Figure 16.3: The Journal Entry Flags in Power Query.

Step 6. Load the entries and give them a calendar.

  1. Choose Home > Close & Apply.
  2. Choose Modeling > New table, and create the Date table with the formula below, as in Tutorial 14.1.
  3. Set the Date column’s type to Date, sort MonthName by MonthNumber, and mark the table as a date table.
  4. In Model view, relate JournalEntry to the Date table on PostingDate, and to Employee on ApprovedByEmployeeID.
Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2027, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Quarter", "Q" & QUARTER ( [Date] ),
    "YearQuarter", YEAR ( [Date] ) & "-Q" & QUARTER ( [Date] ),
    "YearMonth", FORMAT ( [Date], "yyyy-mm" ),
    "MonthNumber", MONTH ( [Date] ),
    "MonthName", FORMAT ( [Date], "mmm" )
)

The relationship to Employee runs through the approver, so a visual of entries by employee name shows who approved them.

Step 7. Score the entries.

  1. Create a Key Measures table with Home > Enter data, as in Tutorial 14.2, and add the two measures below.
  2. On a page named Journal Entries, add a matrix with RiskScore on Rows and Entries and Entry Amount as Values.
Entries = COUNTROWS ( JournalEntry )
Entry Amount = SUM ( JournalEntry[TotalAmount] )

The matrix reproduces Chapter 8’s distribution: most entries score 0, a couple of dozen score 1, a handful score 2, none score 3, and one scores 4.

Step 8. List the riskiest entries. A risk score describes one entry and is not meant to be added up, so you first tell Power BI not to summarize it; the visual’s filter can then test each entry’s score.

  1. Select RiskScore in the Data pane, and on the Column tools tab, set Summarization to Don’t summarize.
  2. Add a table visual with EntryNumber, PostingDate, EntryType, and RiskScore.
  3. In the Filters pane, under Filters on this visual, set RiskScore to Advanced filtering, show items when the value is greater than or equal to 2, and click Apply filter.
  4. Sort the table by RiskScore, descending.

The table lists the entries that failed two tests or more. At the top is the opening balance entry of January 2024, which failed four. Five of the others were posted on the first day of a year and keyed on the first Saturday after it, and two are the round debt reclassifications that Chapter 8 found above the controller’s limit.

Step 9. Bring in the entries’ ledger lines.

  1. Choose Home > Get data > Excel workbook, select T3_GLEntry and T1_Account, and click Transform Data.
  2. Rename the queries JournalLines and Account.
  3. In JournalLines, filter SourceDocumentType to JournalEntry first, then keep GLEntryID, PostingDate, AccountID, Debit, Credit, and SourceDocumentID.
  4. In Account, keep AccountID, AccountNumber, and AccountName.
  5. Merge JournalLines with Account on AccountID, expand AccountNumber and AccountName without the prefix, and clear Enable load on Account.
  6. Choose Home > Close & Apply, and in Model view relate JournalLines to JournalEntry on SourceDocumentID and JournalEntryID.

As in Tutorial 8.3, the ledger is filtered before anything else, so only the journal entry postings, a few thousand of its hundreds of thousands of rows, reach the model. For these postings, SourceDocumentID holds the JournalEntryID, Chapter 10’s source-document join.

Step 10. Drill through to an entry’s lines.

  1. Add a page named Entry Lines, and with no visual selected, drag EntryNumber from JournalEntry into the Drillthrough section of the Visualizations pane, where it says Add drillthrough fields here.
  2. Add a table visual with AccountNumber, AccountName, Debit, and Credit.
  3. On the Journal Entries page, right-click the opening balance entry in the table, and choose Drillthrough > Entry Lines.

Figure 16.4 shows the result. The opening balance entry has many lines, and its debits equal its credits. Tutorial 16.2 uses the same move, from a test to the documents behind it, for every test of the register.

Two mocks of Power BI report pages, one above the other. Above, the Journal Entries page: a matrix of risk scores with the number of entries and their amount for each score, most entries at zero and one entry at four, and a table of the entries that failed two tests or more, with the opening balance entry at the top. Below, the Entry Lines page reached by drill-through, with a back button, listing that entry's ledger lines by account with their debits and credits, which total the same amount.
Figure 16.4: The Risk Scores and an Entry’s Ledger Lines.

Step 11. Turn the flags into exceptions.

  1. Choose Home > Transform data, right-click JournalEntry, and choose Reference. Rename the new query JE Exceptions.
  2. Select the five flag columns from Weekend to RoundAmount, right-click one of them, and choose Unpivot Only Selected Columns.
  3. Filter the Value column to 1.
  4. Add a custom column named TestID with the formula "JE " & [Attribute].
  5. Keep TestID, EntryNumber, PostingDate, TotalAmount, and ApprovedByEmployeeID, and rename the last four DocumentNumber, EventDate, Amount, and EmployeeID.
  6. Clear Enable load on JE Exceptions, and read the number of rows in the status bar.

Each entry is now one row per test it failed, labeled with the test, and its columns carry the register’s names from Table 16.2. The query is not loaded, because Tutorial 16.2 appends it to the exceptions of the other processes. The row count equals the sum of the five flags, a few dozen exceptions.

Step 12. Reconcile the population.

  1. Add a page named Validation with cards for Entries, Entry Amount, and the sum of Debit in JournalLines.
  2. In DAX query view, rename the first query tab Tests, enter the query below, and run it.
  3. Save the file.
// Test 1: the journal entries equal their postings in the ledger
EVALUATE
ROW (
    "Entries", [Entries],
    "Entry amount", ROUND ( [Entry Amount], 2 ),
    "Line debits", ROUND ( SUM ( JournalLines[Debit] ), 2 ),
    "Line credits", ROUND ( SUM ( JournalLines[Credit] ), 2 )
)

The entries’ total equals the debits and the credits of their postings, the reconciliation Chapter 8 performed before its tests and Chapter 12 performed in SQL. That is good evidence that the tests run on the entries the ledger holds, though agreeing totals alone could hide an entry missing from both, as Tutorial 4.1, Step 10 showed.

Checkpoint. Before you add the other processes, confirm the following:

  • The five flags and the risk score reproduce Chapter 8’s counts and distribution.
  • The opening balance entry drills through to its ledger lines, and they balance.
  • JE Exceptions has one row per failed test, with the register’s column names, and is not loaded.
  • Test 1 shows the entries’ total equal to the debits and the credits of their ledger postings.

The tests of Chapter 8, which took an afternoon in a workbook, now run again with every refresh, on a population that has been reconciled.

16.4 The Monitoring Page

A monitoring page answers three questions in order: which tests are failing, when, and on which documents. A matrix with the tests as rows and the months as columns, each cell holding the number of exceptions, answers the first two at a glance. Shading the cells by rule, darker for more exceptions, turns the matrix into a heat map, in which a row of dark cells and a column of dark cells mean different things. The number stays in each cell, so the shading never carries the meaning alone, the rule Chapters 4 and 6 set for conditional formats and charts and Chapter 13 applied to every visual. How well a reviewer reads such a display depends on the visual chosen as well as on the reviewer: in a study by Baaske et al. (2025), participants who chose better visualizations identified anomalies more accurately.

Charles River’s register shows both patterns. Taken together, most of the journal entry and purchase order exceptions fall in January, in each of the three years, and so do five of the six payroll exceptions outside one recurring test. A cluster in the same month of every year points to how the controls operate at that time of year, when the first entries, orders, and payrolls of a new year are processed, and it tells the auditor where to look and when. One test behaves differently: registers approved by the employee they pay appear in every month, because one employee approves every register, including the approver’s own. An exception that recurs in every period is not a lapse to be explained one document at a time. It points to the design of the control, which gives no one the task of approving the approver’s pay. The concentration of approval that Chapter 12 found for purchase orders, and Exercise 12.5 for payroll, reappears when the exceptions are counted by approver.

The rate puts each count beside its population. Journal entries are few, so even a handful of exceptions is a high rate, while purchase orders run to many thousands and their exceptions are a small fraction. Rates per thousand documents show how often the tests find exceptions as a process’s volume changes, which a count alone hides. A lower rate is not by itself evidence that a control improved. The comparison holds only while the tests, their coverage, and the mix of documents stay the same, and the causes of the exceptions still need a look. Over the three years, the rate for journal entries fell and rose again, the rate for purchase orders fell by about two-thirds while the number of orders more than doubled, and the payroll rate barely moved, held in place by the recurring test.

From the matrix, a drill-through page lists the documents behind any test, so a reviewer can move from a dark cell to the exceptions in it, as Chapter 8 promised. What the reviewer then concludes about each exception, and how those conclusions are kept from month to month, is the subject of the section after the next tutorial.

16.5 Guided Tutorial 16.2: The Exception Register and the Monitoring Page

Context and objective. In this tutorial, you will add the purchasing and payroll tests of Chapter 12 to the monitoring file, append the exceptions of all three processes into one register, and build the page the audit committee asked for: a heat map of exceptions by test and month, the rates behind it, the approvers who account for them, and a drill-through to the documents. Then you will test the register against the counts of Chapters 8 and 12.

Prerequisites. You need Audit Monitoring.pbix from Tutorial 16.1, with the JE Exceptions query. If you start here, download Audit Monitoring as it stands at the end of Tutorial 16.1 from Companion Files: a Power BI project that you point at your copy of CharlesRiver.xlsx, refresh, and save as Audit Monitoring.pbix, as that page explains.

Step-by-step instructions.

Step 1. Choose the purchasing and payroll Tables.

  1. Choose Home > Get data > Excel workbook, and select CharlesRiver.xlsx.
  2. In the Navigator, select T33_PurchaseOrder, T69_PayrollRegister, T71_PayrollPayment, and T59_PayrollPeriod, and click Transform Data.
  3. Rename the queries PurchaseOrder, PayrollRegister, PayrollPayment, and PayrollPeriod.

Step 2. Keep the columns the tests need.

  1. In PurchaseOrder, keep PurchaseOrderID, PONumber, OrderDate, OrderTotal, CreatedByEmployeeID, and ApprovedByEmployeeID.
  2. In PayrollRegister, keep PayrollRegisterID, PayrollPeriodID, EmployeeID, NetPay, ApprovedByEmployeeID, and ApprovedDate.
  3. In PayrollPayment, keep PayrollRegisterID and PaymentDate, and in PayrollPeriod, keep PayrollPeriodID, PeriodEndDate, and PayDate.
  4. Set the type of every date column to Date.

Step 3. Flag the purchase orders.

  1. Merge PurchaseOrder with Employee on ApprovedByEmployeeID and EmployeeID, with a left outer join.
  2. Expand MaxApprovalAmount and TerminationDate without the prefix, and rename them ApproverLimit and ApproverTermination.
  3. Add the custom columns SelfApproved, AboveLimit, and AfterTermination with the three formulas below, and set their type to Whole Number.
if [CreatedByEmployeeID] = [ApprovedByEmployeeID] then 1 else 0
if [OrderTotal] > [ApproverLimit] then 1 else 0
if [ApproverTermination] <> null and [OrderDate] > [ApproverTermination] then 1 else 0

Only a terminated employee has a TerminationDate, so the third flag first checks that there is one. A relational comparison such as > returns null in Power Query when either side is null, and the if would fail on it, while the <> null test returns true or false and so guards the comparison. PurchaseOrder records no approval date, so the order date stands in for it, as in Chapter 12. The flag finds orders dated after their recorded approver’s termination, and only the approval log, the delegated authority, and the employment records can show when the approval was given and whose access was used. The limit and the termination date are the values the Employee table holds now, applied to documents of all three years. A monitor that runs for years needs the limits in force on each document’s date, which this extract does not record. Every approver here has a recorded limit, and a limit of 0 is a real limit. On new data, count apart any document with no approver or no limit, as Chapter 12 advised, because the equality test of SelfApproved would pass it.

Step 4. Bring the payment, the period, and the payee onto each register.

  1. Merge PayrollRegister with PayrollPayment on PayrollRegisterID, and expand PaymentDate.
  2. Merge it with PayrollPeriod on PayrollPeriodID, and expand PeriodEndDate and PayDate.
  3. Merge it with Employee on EmployeeID, and expand TerminationDate.
  4. Clear Enable load on PayrollPayment and PayrollPeriod.

Every merge is a left outer join and every expansion is without the prefix. Each register now carries the date it was paid, the period it covers, and whether the employee it pays had left.

Step 5. Flag the payroll registers.

  1. Add the custom columns PaidBeforeApproval, AfterTermination, and SelfApproved with the three formulas below.
  2. Set their type to Whole Number.
if [PaymentDate] <> null and [PaymentDate] < [ApprovedDate] then 1 else 0
if [TerminationDate] <> null and [PeriodEndDate] > [TerminationDate] then 1 else 0
if [EmployeeID] = [ApprovedByEmployeeID] then 1 else 0

The last test compares the employee being paid with the person who approved the pay, the question Exercise 12.5 asked of every register. The second compares the end of the pay period, not the pay date as Exercise 12.5 did, with the termination date, so that a period that ended before the employee left is not flagged only because it was paid later. Both comparisons find the same three registers. A period that straddles the termination date is still flagged and can hold wages earned before it, so the earnings, the termination record, and any settlement are examined before anyone calls a payment unauthorized.

Step 6. Turn the new flags into exceptions.

  1. Reference PurchaseOrder as PO Exceptions, unpivot only its three flag columns, and filter Value to 1, as in Tutorial 16.1, Step 11.
  2. Add TestID with the formula "PO " & [Attribute], keep TestID, PONumber, OrderDate, OrderTotal, and ApprovedByEmployeeID, and rename the last four as in Table 16.2.
  3. Repeat the same moves for PayrollRegister as Payroll Exceptions, with "PR " & [Attribute], and add a column DocumentNumber with the formula "Register " & Text.From([PayrollRegisterID]).
  4. In Payroll Exceptions, keep TestID, DocumentNumber, PayDate, NetPay, and ApprovedByEmployeeID, and rename the last three EventDate, Amount, and EmployeeID.
  5. Clear Enable load on both queries.

A register has no document number of its own, so the custom column gives it one. Every exception query now has the same five columns, with the same names.

Step 7. Append the register.

  1. Choose Home > Append Queries > Append Queries as New.
  2. Select Three or more tables, add JE Exceptions, PO Exceptions, and Payroll Exceptions to Tables to append, and click OK.
  3. Rename the new query Exceptions, set EventDate’s type to Date, and read its number of rows.
  4. Choose Home > Close & Apply.

The register holds a little over a hundred and fifty exceptions, the three exception queries stacked. Because Append matches columns by name, the renaming in Step 6 is what puts each process’s dates under EventDate. A query that kept OrderDate would add a column of its own, empty for the other processes, and leave EventDate empty for its rows.

Step 8. Describe the tests.

  1. Choose Home > Enter data, and in the Create Table dialog name four columns TestID, Test, Process, and Source, by double-clicking the column headers.
  2. Type the eleven rows of Table 16.1.
  3. Type Tests as the table’s name, and click Load.

The Tests table is the register’s dimension: one row per test, with the process whose population the test runs on.

Step 9. Relate the register and the populations.

  1. In Model view, relate Exceptions to Tests on TestID, to the Date table on EventDate, and to Employee on EmployeeID.
  2. Relate PurchaseOrder to the Date table on OrderDate, and PayrollRegister to the Date table on PayDate.
  3. Hide the keys and the flag columns of the three population tables.
  4. In Model view, check that PurchaseOrder and PayrollRegister have no line to Employee, and delete any that you find.

Figure 16.2 showed this model. The populations stay loaded, related to Date, so that a measure can count the documents of any month. Employee relates only to Exceptions and, through the approver, to JournalEntry. A line from PayrollRegister to Employee would join on the payee’s EmployeeID, so a click on an employee would filter the registers that employee was paid on, not the ones the employee approved.

Step 10. Measure the exceptions and their rates.

  1. Add the measures below to the Key Measures table, and format Rate per 1,000 with one decimal.
Exceptions = COUNTROWS ( Exceptions )
Population =
SWITCH (
    SELECTEDVALUE ( Tests[Process] ),
    "Journal entries", COUNTROWS ( JournalEntry ),
    "Purchase orders", COUNTROWS ( PurchaseOrder ),
    "Payroll", COUNTROWS ( PayrollRegister )
)
Rate per 1,000 = DIVIDE ( [Exceptions], [Population] ) * 1000

Population counts the documents of the test’s process in the filter context, so in a visual by test and year it is the number of entries, orders, or registers of that year. With no single process selected, as in a total row, SELECTEDVALUE returns blank and so does Population, because a rate across processes would mix populations. At a test’s row, the rate is the share of the process’s documents that failed the test. At a process’s row, which adds the exceptions of its tests, a document that fails two tests counts twice, so the rate counts failed tests per 1,000 documents, not documents with an exception.

Step 11. Build the heat map.

  1. Add a page named Monitoring, with a slicer of Year.
  2. Add a matrix with TestID on Rows, MonthName on Columns, and Exceptions as Values.
  3. In the Values well, open the menu of Exceptions, choose Conditional formatting > Background color, select Rules as the format style, and add two rules: a light fill from 1 up to 5, and a darker fill for 5 and more.

The number stays in every cell, and the fill only adds emphasis, so the matrix reads the same in grayscale.

Step 12. Add the approvers and the rates.

  1. Add a clustered bar chart of Exceptions by EmployeeName.
  2. Add a table of Exceptions, Population, and Rate per 1,000 by TestID.
  3. Select the bar chart, choose Format > Edit interactions, and click the None icon above the table.

Figure 16.5 shows the page for all three years. The January column is the darkest. Taken together, most of the journal entry and purchase order exceptions fall in the first month of a year, in every year. December’s cell for journal entries above the approver’s limit holds the year-end closing entries, which exceed every approver’s limit. They are expected in size, though Chapter 8 asked for the closing-entry policy and the limits that applied before calling them a gap in the policy or a failure to follow it. One row is shaded in every month: registers approved by the employee they pay. The bar chart shows why, with one approver far ahead of everyone else. The edit keeps the table’s rates for whole processes. Employee filters the exceptions and, through the approver, the journal entries, but not the orders or the registers, so a click on an approver would have divided that approver’s exceptions by the approver’s journal entries but by all the orders and registers. Exercise 16.6 counts each approver’s own documents.

A mock of a Power BI report page named Monitoring. A matrix lists the eleven tests as rows and the months January to December as columns, with the number of exceptions in each cell and darker shading for larger numbers. The January column is the darkest; the row for registers approved by their own employee has a shaded cell in every month. A bar chart beside it ranks approvers by their exceptions, with the Accounting Manager far ahead. A table lists each test's exceptions, population, and rate per thousand documents.
Figure 16.5: The Monitoring Page.

Step 13. Drill through to the documents.

  1. Add a page named Exception Detail, and drag TestID from Tests into the Drillthrough section of the Visualizations pane.
  2. Add a table visual with DocumentNumber, EventDate, Amount, EmployeeName, and JobTitle.
  3. On the Monitoring page, right-click the January cell of PO AfterTermination, and choose Drillthrough > Exception Detail.

The detail page shows the orders behind that cell, as Figure 16.6 shows. They are dated after the termination of their recorded approver, an account executive whose employment had ended long before. Drill-through passes both the row and the column of the cell, so the page shows the test’s exceptions of January only. The test has a third order, dated in another month, which the detail page leaves out, so it lists fewer orders than the test counts in all. A reviewer can start from any cell of the heat map and reach the documents that need a disposition.

A mock of a Power BI drill-through page named Exception Detail, with a back button and a note of its drill-through filters: the test of orders dated after the approver left, and the month of January. A table lists the orders behind that cell, with their numbers, order dates, amounts, and their approver, an account executive whose employment had ended in 2024.
Figure 16.6: The Exception Detail Page.

Step 14. Test the register.

  1. In DAX query view, open the Tests tab, add the query below, and run it.
  2. Save the file.
// Test 2: each test's exceptions must equal the counts of Chapters 8 and 12
EVALUATE
SUMMARIZECOLUMNS (
    Tests[TestID],
    "Exceptions", [Exceptions],
    "Population", [Population],
    "Rate per 1,000", ROUND ( [Rate per 1,000], 2 )
)
ORDER BY Tests[TestID]

Each count equals the count of the test in Chapter 8 or Chapter 12, so the register reproduces the earlier tests, and every refresh will run the same tests on the new data.

Checkpoint. Before you review the exceptions, confirm the following:

  • Exceptions holds the three processes, with every date under EventDate and every number under DocumentNumber.
  • Test 2 returns the counts of Chapters 8 and 12 for each of the eleven tests.
  • The heat map shows the January cluster and one row shaded in every month, and the bar chart shows the approver behind that row.
  • A cell of the heat map drills through to its documents, and the detail lists as many documents as the cell counts.
  • Clicking an approver in the bar chart leaves the rates table unchanged.

The monitoring page separates two kinds of problem that a once-a-year test would have reported the same way: exceptions that cluster at one time of year, which call for a look at how the controls work then, and an exception that recurs every month, which calls for a change in the design of the control.

16.6 Reviewing Exceptions

Each exception needs a disposition, Chapter 8’s term for the auditor’s conclusion about it: expected, with the evidence that explains it; follow up, with the question to ask; or a misstatement or control deficiency to report. Chapter 8 kept its dispositions on a worksheet of pasted values, because text typed beside a query’s output can end up beside a different row when the query is refreshed. A monitoring report meets the same problem every month, since the register is rebuilt at each refresh. The answer is to keep the dispositions in a table of their own, keyed by the test and the document, and to merge them into the register. Each refresh then attaches every conclusion to the exception it concerns, and an exception that is new this month arrives with none. Because the register is rebuilt from the current data, it groups the exceptions by the dates of their documents, not by the review that first found them, and an exception whose document is later corrected leaves it, so each review’s dispositions and memo are its record of what was found. The exceptions without a disposition are the open exceptions, those not yet reviewed and the reviewer’s first work for the month. A disposition records a conclusion, not a resolution: a follow-up question still needs its answer and a deficiency its correction, so those stay on internal audit’s list of matters to track until they are closed.

The dispositions are internal audit’s record and need the same care as any audit workpaper. Only the reviewers change them, each conclusion should show who reached it and when, in columns that the short table of Tutorial 16.3 leaves out, and the chief audit executive reviews them. A disposition recorded against the wrong document, or typed twice, misstates the review while seeming to complete it, so the register is tested to confirm that every disposition found exactly one exception.

Even with dispositions, a monitoring system can raise more exceptions than its reviewers can examine. The volume of exceptions a continuous auditing system generates can overwhelm an internal audit department (Li et al., 2016), and Alles et al. (2006) found that preventing such alarm floods was one of the critical tasks in implementing continuous monitoring at Siemens. Li et al. (2016) proposed a framework that gives each exception a suspicion score from the rules it fails, has the auditors investigate the exceptions in the order of their scores, and updates the confidence placed in each rule as the investigations show which exceptions were errors or fraud. For a small register like this one, a simpler rule gives a first cue. A document that fails several tests comes before one that fails a single test, the reasoning behind Chapter 8’s risk score, and an exception that is new this month comes before one whose explanation is already on file. The rule is not the adaptive score that Li et al. (2016) proposed, and the reviewer still weighs each exception’s amount, age, and urgency, so a serious single failure can come first.

Two kinds of exception should not be reviewed one document at a time. An exception expected every period, such as the year-end closing entries that exceed every approver’s limit, needs its disposition and evidence once, and then only a check that it recurs as expected. An exception that recurs because of the design of a control, such as the registers approved by the employee they pay, is one finding, not dozens. The response is a change to the control, reported once, after which the exceptions should stop. Reviewing them one by one would only bury the new exceptions among the old.

Counting exceptions by approver shows who approved the documents that failed. It does not show how much of a process rests on one person, which needs the population: the share of each process’s documents that each employee approved. At Charles River, one employee approved every payroll register and another nearly every purchase order, a concentration that no single exception reveals. Exercise 16.6 measures it.

16.7 Auditing the Report Itself

Internal audit’s monitoring report and the controller’s package are both evidence that someone relies on, and neither is better than the data and the calculations behind it. Chapter 12 began every test with a reconciliation, because a query run against an incomplete table can report a clean result that means nothing, and a report is no different. Before relying on a report’s numbers, an auditor asks of it the questions asked of any system that produces evidence, and Table 16.3 shows where a Power BI report keeps the answers. One more question, who can see the report, belongs with how it is published and shared, in the appendix on publishing and security.

Table 16.3: The questions an auditor asks of a report
Question Evidence In the reports of this Part
Is every document in the model? Each population reconciled to the ledger, and document numbers checked for gaps Tests 1 and 4 of the monitoring file; the Validation pages
Do the measures compute what they claim? Queries that reproduce totals known from other tools The package’s Tests 1 to 5; Test 2 of the monitoring file
How current are the data? The last date of each population, and the refresh history The Data Through card; the package’s About page
Who changed the report, and was the change reviewed? The version history of the report’s files, and a record of approvals A Power BI project under version control

The first two questions already have answers. Test 1 of Tutorial 16.1 reconciled the journal entries to their postings, and the package’s Tests tab, begun in Chapter 14, reproduces control totals of Chapters 6, 7, and 11, as the control totals of Chapter 4 checked a workbook. A population with nothing in the model to reconcile it to can still be tested for completeness. Charles River numbers its purchase orders in one series that runs across the years, as Exercise 8.5 found, so if the numbers are all different, start at 1, and end at the count of orders, none is missing between the first and the last, the gap test of Chapter 8. Whether the series continues past the last number in the model is a question for the purchasing system’s own count. What any test shows depends on when it last ran: a test that passed in April says nothing about a measure changed in May. Rerunning the tests is part of every review, and their results, with the date they were run, belong in the audit workpaper.

The third question is easy to overlook. A report shows the data of its last refresh, and each population has its own last date. In Charles River’s data, the journal entries and the purchase orders run to the last day of fiscal 2026, but the last pay date of a payroll register is December 18. No pay period has a later pay date in December. The work of the rest of the month belongs to the last two pay periods, which are marked Open in the data, have no registers, and are scheduled to pay in January 2027, as Chapter 12 found, so no payroll test has yet seen that work. A measure that returns the earliest of the populations’ last dates, shown on the page, states the latest date that every population reaches, as the package’s About page states the period its data cover. It is a bound observed in the extract. It does not show that a refresh succeeded, which the refresh history records, or that no document is missing before that date, which needs the population tests and the source systems’ own counts. Whether payroll is complete for a period takes a reconciliation of the periods and registers expected with those recorded, and of their status.

The fourth question concerns the report itself. A report that managers act on every month is a system, and changes to it deserve the change controls auditors expect of any system: each change to a query, a relationship, or a measure is requested, tested, approved, and recorded before readers see it, and the person who approves a change is not the one who made it. A .pbix file is a single binary file, so two copies can differ in one measure with nothing to show where. Saving the report as a Power BI project, a .pbip, stores the model and the report as folders of text files that version-control tools such as Git can keep and compare line by line (Microsoft, 2026b). A reviewer then sees each change as the lines it altered, and the Tests tab, rerun before the change is published, becomes the report’s regression test, the check that a change has not broken what worked before.

WarningWatch out

A copy of a .pbix file whose model imports its data, as both reports of this Part do, holds a full copy of that data, every row of every table, whatever its pages show. An uncontrolled copy, sent to a colleague or saved to a shared drive outside the approved release, escapes the controls of this chapter. Its data stop at the date of the copy, its measures can drift from the version readers use, and whoever opens it in Power BI Desktop can read all of its imported data. Treat the report under change control as the authoritative version, approve and record any copy that must be distributed, and protect a project’s folders as well as its .pbix files. When you audit a report, ask who holds copies of it.

TipIn Practice

Teams that keep reports as Power BI projects often manage them as programmers manage code. Each change is made on a branch of a Git repository and reviewed by a colleague as a list of changed lines before it is merged and published, and the repository’s history, with its record of reviews, shows who changed what, when, and with whose approval. An auditor testing change control asks for that history and compares it with the published report, much as Chapter 12’s saved scripts let a reviewer rerun and compare the audit queries. Sensitivity labels, which classify a report, are not supported in projects (Microsoft, 2026b), a tradeoff the appendix on publishing and security describes.

NoteConnecting the Dots

Every Part of this book has ended its tool’s work with a way to make results checkable: the Documentation worksheet and control totals of Chapter 4, the saved scripts and audit query library of Part III, and the About page and Tests tab of Chapters 14 and 15. This chapter turns the auditor’s eye on that discipline. Control totals are evidence only if they are rerun, tests only if the report they test is the one readers use, and documentation only if it states how current the data are. Auditing a report means checking that each of these still holds.

16.8 Guided Tutorial 16.3: Reviewing Exceptions and Testing the Reports

Context and objective. In this tutorial, you will record internal audit’s first dispositions, merge them into the exception register, and measure the exceptions that remain open, starting with the documents that fail more than one test. Then you will test both reports before relying on them: the completeness of the purchase orders and payroll registers, the date through which the monitoring data run, and the package’s own tests, rerun as audit evidence. You will finish with a memo to the chief audit executive.

Prerequisites. You need Audit Monitoring.pbix from Tutorial 16.2 and Charles River Reports.pbix as you left it at the end of Chapter 15. If you start here, download this tutorial’s folder from Companion Files, which holds Audit Monitoring at the end of Tutorial 16.2 and Charles River Reports at the end of Chapter 15, and point each project at your copy of CharlesRiver.xlsx, refresh it, and save it as a .pbix file, as that page explains.

Step-by-step instructions.

Step 1. Record the dispositions.

  1. In Audit Monitoring.pbix, choose Home > Enter data, and in the Create Table dialog name four columns TestID, DocumentNumber, Disposition, and Note.
  2. Type the fifteen rows of Table 16.4.
  3. Type Dispositions as the table’s name, and click Load.
  4. In Model view, right-click Dispositions, and choose Hide in report view.
Table 16.4: The dispositions of a first review
TestID DocumentNumber Disposition Note
JE AboveLimit JE-2024-000265 Expected Year-end close
JE AboveLimit JE-2024-000266 Expected Year-end close
JE AboveLimit JE-2025-000287 Expected Year-end close
JE AboveLimit JE-2025-000288 Expected Year-end close
JE AboveLimit JE-2026-000296 Expected Year-end close
JE AboveLimit JE-2026-000297 Expected Year-end close
PO AfterTermination PO-2024-000474 Deficiency Approver had left
PO AfterTermination PO-2025-003922 Deficiency Approver had left
PO AfterTermination PO-2026-010467 Deficiency Approver had left
PR AfterTermination Register 840 Deficiency Payee had left
PR AfterTermination Register 2646 Deficiency Payee had left
PR AfterTermination Register 5420 Deficiency Payee had left
PR PaidBeforeApproval Register 2 Follow up Paid before approval
PR PaidBeforeApproval Register 2647 Follow up Paid before approval
PR PaidBeforeApproval Register 5421 Follow up Paid before approval

The table holds the conclusions of a first review, with Chapter 8’s three dispositions. The year-end closes are expected in size, as Chapter 8 found, with the closing-entry policy and the limits that applied still to obtain. For this tutorial, assume that the reviewer looked past the register before recording the rest. Chapter 12 and Exercise 12.5 found the orders and registers of the employee who had left, and the approval log, the employment records, and the earnings behind them led the reviewer to call them control deficiencies to report. The flags alone would support only a follow-up. The payments made before their registers were approved need a question for payroll. Each test and document number must match the register character for character, or the disposition will attach to nothing.

Step 2. Merge the dispositions into the register.

  1. Choose Home > Transform data, and select the Exceptions query.
  2. Choose Home > Merge Queries, and select Dispositions as the second table.
  3. In the Exceptions preview, click TestID, and then hold Ctrl and click DocumentNumber; in the Dispositions preview, click TestID and DocumentNumber in the same order.
  4. Keep the Left Outer join, click OK, and expand Disposition and Note without the prefix.
  5. Choose Home > Close & Apply.

A merge can match on several columns at once, and the order in which you select them pairs them, as the small numbers beside the column headers show. The register keeps every exception, with a disposition on the fifteen that have one and null on the rest. Because the merge is a step of the Exceptions query, every refresh runs the tests again and then attaches the conclusions, however the rows of the register change.

Step 3. Measure the open exceptions, and test the dispositions.

  1. Add the measures below to the Key Measures table.
  2. In DAX query view, open the Tests tab, add the query of Test 3 below it, select the query, and click Run.
Reviewed Exceptions = CALCULATE ( [Exceptions], NOT ISBLANK ( Exceptions[Disposition] ) )
Open Exceptions = CALCULATE ( [Exceptions], ISBLANK ( Exceptions[Disposition] ) )
// Test 3: every disposition must match exactly one exception of the register
EVALUATE
VAR ExceptionKeys =
    SUMMARIZE ( Exceptions, Exceptions[TestID], Exceptions[DocumentNumber] )
VAR DispositionKeys =
    SUMMARIZE ( Dispositions, Dispositions[TestID], Dispositions[DocumentNumber] )
RETURN
    ROW (
        "Exceptions", COUNTROWS ( Exceptions ),
        "Exception keys", COUNTROWS ( ExceptionKeys ),
        "Dispositions", COUNTROWS ( Dispositions ),
        "Disposition keys", COUNTROWS ( DispositionKeys ),
        "Reviewed exceptions", [Reviewed Exceptions],
        "Open exceptions", [Open Exceptions]
    )

Each table has as many keys as rows, so no test and document pair appears twice in either, and the reviewed exceptions equal the dispositions, so every disposition found exactly one exception. A disposition typed twice would copy its exception in the merge while the dispositions and the reviewed exceptions still agreed, which is why the test counts the keys. If the reviewed exceptions fall short, a test or document number has been mistyped, or an exception has left the register since its review, and the disposition is kept and explained, not deleted. The open exceptions, those not yet reviewed, are by far the larger group, and the follow-ups and deficiencies, though reviewed, still await action.

Step 4. Build the review page.

  1. Add a page named Review, with a card of Reviewed Exceptions and a card of Open Exceptions.
  2. Add a matrix with TestID on Rows, Year on Columns, and Open Exceptions as Values.

The matrix shows where the open exceptions are. Most of them belong to one test, the registers approved by the employee they pay, a single design finding that the memo of Step 9 reports once rather than document by document. The year-end closes have left the AboveLimit row, which now holds only the entries that still need a look.

Step 5. List the documents that fail more than one test.

  1. Add a table visual with DocumentNumber, Exceptions, and Open Exceptions.
  2. In the Filters pane, under Filters on this visual, show items when Exceptions is greater than or equal to 2, and click Apply filter.
  3. Sort the table by Exceptions, descending.

At the top of the table, as Figure 16.7 shows, is the opening balance entry, which failed four tests. The other journal entries are the five keyed on the first Saturday of a new year and the two debt reclassifications. The purchase orders include small orders that buyers with no approval limit approved themselves, the orders that name the departed account executive as approver, and two capital orders the purchasing manager approved alone. No payroll register fails two tests. The orders dated after their approver left are reviewed for one of their two tests and still open for the other, because a disposition belongs to an exception, not to a document.

A mock of a Power BI report page named Review. Two cards show the number of reviewed exceptions and the much larger number of open exceptions. A matrix lists the open exceptions by test and year; the row of registers approved by their own employee holds most of them, and the row of journal entries above the approver's limit is small because the year-end closes have dispositions. A table lists the documents that fail two tests or more, with the opening balance entry at the top with four, followed by journal entries and purchase orders with two; three orders show one open exception of their two.
Figure 16.7: The Review Page.

Step 6. Test the populations. Journal entries were reconciled to the ledger in Test 1; orders and registers need tests of their own.

  1. In the Tests tab, add the query of Test 4 below.
  2. Select it, and click Run.
// Test 4: the order numbers have no gap; three registers have no payment record
EVALUATE
VAR Numbers =
    SELECTCOLUMNS (
        PurchaseOrder,
        "Number", VALUE ( RIGHT ( PurchaseOrder[PONumber], 6 ) )
    )
VAR NoPayment =
    FILTER ( PayrollRegister, ISBLANK ( PayrollRegister[PaymentDate] ) )
RETURN
    ROW (
        "Orders", COUNTROWS ( PurchaseOrder ),
        "Unique numbers", COUNTROWS ( DISTINCT ( Numbers ) ),
        "First", MINX ( Numbers, [Number] ),
        "Last", MAXX ( Numbers, [Number] ),
        "Registers", COUNTROWS ( PayrollRegister ),
        "No payment record", COUNTROWS ( NoPayment )
    )

The variables hold the order numbers, taken from the last six characters of each PONumber, and the registers with no payment record. Figure 16.8 shows the result. The order numbers are all different, the first is 1, and the last equals the number of orders, so none is missing between them, the gap test of Chapter 8 on a series that continues across the years. A repeated number could hide a missing one, which is why the test counts the unique numbers. Whether orders were numbered after the last one in the model is a question for the purchasing system’s own count. Three registers have no payment record: the ones Chapter 12 found with payment postings in the ledger and no payment record behind them, a break in the trail to resolve with payroll’s and the bank’s records, not proof that the pay was never received. Two of them already appear among the exceptions as pay after termination; the third, the chief executive’s first register, is a finding for the memo.

A mock of DAX query view in Audit Monitoring.pbix with the Tests tab open. The editor holds the query of Test 4, which counts the purchase orders and their unique numbers, finds the first and last order numbers, and counts the payroll registers and those without a payment record. The Results grid shows one row: the number of orders equal to the number of unique numbers and to the last number, a first number of 1, the number of registers, and 3 registers with no payment record.
Figure 16.8: Test 4 in DAX Query View.

Step 7. State the date through which the data run.

  1. Add the measure below to the Key Measures table, and set its format to a short date.
  2. On the Monitoring page, add a card of Data Through, titled Data through.
  3. Select the heat map, choose Format > Edit interactions, and click the None icon above the card; select the bar chart of approvers, and do the same.
Data Through =
MINX (
    {
        MAX ( JournalEntry[PostingDate] ),
        MAX ( PurchaseOrder[OrderDate] ),
        MAX ( PayrollRegister[PayDate] )
    },
    [Value]
)

The braces build a one-column table of the three last dates, whose column DAX names Value, and MINX returns the earliest. The card shows a date in mid-December 2026, the last pay date of a payroll register, while the journal entries and orders run to the 31st. No pay period has a later pay date in December, and the work of the rest of the month belongs to the two periods marked Open in the data, scheduled to pay in January 2027. The card describes the dates observed in this extract. It does not show that payroll is complete, which takes a reconciliation of the periods and registers expected with those recorded, or that the refresh ran, which the refresh history shows. With the Year slicer set to an earlier year, it shows that year’s last payday, and the edited interactions keep a click on a cell or an approver from changing it. MINX skips a blank, so a population with no rows would leave the card showing the others’ date. An empty table is caught by the counts of Tests 1 and 4, not by the card.

Step 8. Rerun the package’s tests.

  1. Save Audit Monitoring.pbix, and open Charles River Reports.pbix.
  2. In DAX query view, open the Tests tab, select the query of Test 1 with its comment line, and click Run.
  3. Run Tests 2 to 5 in the same way, one at a time.
  4. Compare each result with the values its chapter expected, and record the result and today’s date.
  5. Read the period that the About page states, and close the file without saving it.

Each test returns the totals of the chapter it was written against, so the package’s measures still reproduce the totals of Chapters 6, 7, and 11 that the tests name. Test 5 reproduces Part III’s ratio of hours to standard hours, which Chapter 15 read as an observed-records diagnostic, not a complete measure of the plant’s labor performance. The tests check those totals, not every visual on the package’s pages, which a reviewer checks by using the pages. The About page notes that the time records end before the year does, direct time on December 11, 2026, and indirect time on December 4, 2026, because of the same open pay periods that end payroll’s dates in Step 7. Closing without saving leaves the controller’s file as you found it: internal audit tests the package but does not change it.

Step 9. Write the memo. Write a short memo to the chief audit executive.

  1. Report the dispositions recorded, the follow-ups and deficiencies still awaiting action, and the open exceptions, with the documents that fail more than one test listed first.
  2. Report the registers approved by the employee they pay as one finding on the design of the payroll approval, and recommend a second approver.
  3. Report the results of Tests 1 to 4 of the monitoring file and Tests 1 to 5 of the package, with the date you ran them and the date through which the data run.
  4. Recommend change controls for both reports: a project under version control, a named owner, a reviewer who approves each change, the tests rerun before each release, and approved access and distribution controls for any copies of the file.
  5. Save Audit Monitoring.pbix.

Checkpoint. Before you send the memo, confirm the following:

  • The dispositions are merged into the register on the test and the document, and Test 3 shows that every one found exactly one exception.
  • The Review page shows the open exceptions by test and year and lists the documents that fail two tests or more.
  • Test 4 shows the order numbers without a gap and the three registers without a payment record, and the Data Through card shows the latest date that every process’s data reach, a bound observed in the extract.
  • The package’s Tests 1 to 5 return the values their chapters expected, and the package was closed without saving.

A monitoring report earns its place when its conclusions accumulate and its own numbers can be trusted: the dispositions give it a memory, and the tests give internal audit grounds to rely on the totals they test in both reports.

16.9 Looking Ahead

This chapter has turned the tests of Chapters 8 and 12 into monitoring. You built an exception register that runs the rule tests of three processes with every refresh, set each count beside its population, and found two patterns that call for different responses: exceptions that cluster in the first month of every year, and one that recurs every month because a control has no one to perform it. You recorded dispositions that survive every refresh, so that each month’s review starts from the exceptions not yet reviewed while the follow-ups and deficiencies stay on the list until they are closed, and put first the documents that fail several tests. And you audited the reports themselves. Their journal entries reconcile to the ledger and their order numbers run without a gap, their tests reproduce the totals of earlier Parts, their data reach a date that differs by process, and their changes need controls that a single file cannot provide. Most of this work was descriptive and diagnostic analytics, in the terms of Table 1.1, and its recommendations, on the controls and on the reports, are prescriptive.

This chapter completes Part IV. Across its four chapters you have built a model from the Charles River workbook, measured it with DAX, turned it into a reporting package for its managers, and monitored and audited it for internal audit. How the package and the monitoring report reach their readers, and how each reader sees only what they should, is the subject of the appendix on publishing and security, which you can work through in Power BI Desktop and, with a license, in the Power BI service. The Part IV Comprehensive Case that follows brings the four chapters together on a question the audit committee has put to the controller: how a profitable year ended with less cash than it began. Part V then offers three capstone cases that draw on every tool of the book.

16.10 Chapter Summary

Monitoring runs audit tests every period on new data and keeps the results over time. Continuous auditing is internal audit’s ongoing, technology-based assessment of risks and controls; continuous monitoring is management’s ongoing review of its own controls, on which continuous auditing depends. Rule tests produce exceptions, records that meet a deterministic rule’s criteria and so need an explanation, while analytical tests produce anomalies, records or periods that differ from what is expected and call for a closer look. A monitoring system must keep its exceptions reviewable, through tests that state their rule, population, frequency, and expected result, and its full results go to the people who oversee the controls, while the people whose transactions it tests hear of the exceptions they must explain or correct.

An exception register holds the exceptions of every test in one table, one row per exception per test, with the same columns whatever the process. In Power BI it is built in Power Query: each test becomes a flag column, unpivoting turns the flags into rows from which the exceptions are kept, and appending stacks the exceptions of several processes, the counterpart of Chapter 12’s UNION ALL, matching columns by name rather than position. Tutorial 16.1 rebuilt Chapter 8’s journal entry tests and risk scores, drilled from the highest-scoring entry to its ledger lines, and reconciled the population of entries to the ledger. Tutorial 16.2 added the purchasing and payroll tests of Chapter 12 and Exercise 12.5, appended the register, and measured each test’s exceptions against its population.

The monitoring page shows exceptions by test and month as a heat map, with the number in every cell, and leads by drill-through to the documents behind a cell. Charles River’s register showed two patterns: most journal entry and purchase order exceptions cluster in the first month of every year, which points to how the controls operate at that time, and one test, registers approved by the employee they pay, recurs every month, which points to the design of the control. Rates per thousand documents show how often the tests find exceptions as volume changes, which counts alone hide, though a lower rate is not by itself evidence of better controls.

Each exception needs a disposition. Kept in a table of their own and merged into the register on the test and the document, the dispositions survive every refresh, and the exceptions without one are the open exceptions, not yet reviewed; a follow-up or a deficiency stays a matter to track until it is closed. A test that counts the keys of both tables and the reviewed exceptions catches mistyped and repeated keys. Other things equal, reviewers start with documents that fail several tests and with exceptions that are new, record an expected exception once, and report an exception that recurs by design as one finding on the control. The concentration of approval is measured on the population, as the share of each process’s documents that each employee approved.

A report is evidence only if its populations are complete, its calculations correct, its data current, and its changes controlled. Tutorial 16.3 tested the purchase order numbers for gaps and the registers for payment records, stated the latest date that every population reaches, a bound observed in the data that for payroll falls before the year ends, and reran the package’s tests as audit evidence without changing the package. Saving a report as a Power BI project lets version control record and compare every change, while an uncontrolled copy of a .pbix file escapes those controls. How reports are published, secured, and shared is covered in the appendix on publishing and security.

16.11 Key Terms

Anomaly. A record or period that differs from what an analytical test expects, such as a near-duplicate payment or a departure from Benford’s Law. An anomaly is a reason to look more closely, while an exception is a record that meets a rule’s criteria and needs an explanation.

Append. A Power Query operation that stacks the rows of two or more tables into one, matching their columns by name and filling with nulls the columns a table lacks. It is the counterpart of SQL’s UNION ALL, which matches columns by position.

Change control. The process by which changes to a system, such as the queries, relationships, and measures of a report, are requested, tested, approved by someone other than the person who made them, and recorded before users see them.

Continuous auditing. Internal audit’s use of technology to assess risks and controls on an ongoing basis, with tests that run every period on new data, so that it can give assurance throughout the year rather than once.

Continuous monitoring. Management’s ongoing review, often automated, of whether its own controls operate as designed. Continuous auditing can rely on it when it is strong.

Data currency. How recent the data in a report are, stated as the last date each population reaches, with the date field it uses, and the time of the last refresh. A report whose populations end on different dates can have been tested only through the earliest of them, and even that date is a bound observed in the data, not proof that every record up to it is present.

Exception register. A table that holds the exceptions of several monitoring tests, one row per exception per test, with the same columns for every test: the test, the document, its date, its amount, and the employee responsible.

Open exception. An exception with no disposition recorded, one that no reviewer has yet examined. In a monitoring report, the open exceptions are the reviewers’ first work for the period, while the exceptions already reviewed keep their dispositions from one refresh to the next. A reviewed exception is not necessarily resolved: one marked for follow-up or reported as a deficiency stays a matter to track until it is closed.

Unpivot. A Power Query operation that turns selected columns into rows, each column becoming a row with its name in an Attribute column and its value in a Value column. Unpivoting flag columns and keeping the rows with a value of 1 turns flags into exceptions.

16.12 Multiple Choice Questions

1. Which of the following describes continuous monitoring, as distinct from continuous auditing?

  1. Internal audit’s yearly test of a sample of transactions
  2. The external auditor’s review of controls at year-end
  3. Management’s ongoing review of whether its own controls operate
  4. A report of key performance indicators prepared for the board each quarter

2. In the framework of Kogan et al. (2014), a purchase order approved above its approver’s limit fails a deterministic business rule. What does such a test produce?

  1. An exception
  2. An anomaly
  3. A false positive
  4. A near-duplicate

3. Gonzalez and Hoffman (2018) studied the prompt notification of auditees about the transactions a monitoring system flagged. What did they find when the monitoring system was weak?

  1. Notification reduced fraud by a large margin in every condition
  2. Notification had no effect on behavior
  3. Notification mainly increased false positives
  4. Notification increased the inclination to commit fraud

4. A query has five flag columns, each 1 or 0, and you need one row per flag with the flag’s name and value. Which Power Query command produces that?

  1. Merge Queries with the Employee table
  2. Unpivot Only Selected Columns
  3. Append Queries as New
  4. Group By with the Advanced option

5. Before appending the exceptions, a colleague renamed the columns of the journal entry and payroll queries but left the purchase order query’s date column named OrderDate. What does the appended register contain?

  1. An OrderDate column of its own, with EventDate empty for orders
  2. An error that stops the query until the column names match exactly
  3. The order dates under EventDate, because the columns are in the same order
  4. No purchase order rows, because Append drops tables whose columns differ

6. Why does the monitoring model keep the journal entries, purchase orders, and payroll registers as tables of their own beside the exception register?

  1. The register cannot be related to the Date table
  2. Power BI requires every unpivoted table to stay loaded
  3. The rates divide each test’s exceptions by its population
  4. The drill-through page reads documents only from those tables

7. On the monitoring page, many exceptions cluster in January, while one test shows exceptions in every month of every year. Which reading is most appropriate?

  1. The Date table assigns the recurring test’s dates incorrectly
  2. A possible design flaw in the recurring test’s control
  3. The January cluster is more serious because it is larger
  4. The recurring test should be removed as a source of noise

8. Why does Tutorial 16.3 keep the dispositions in a table of their own, merged into the register, rather than in a column typed beside the register’s rows?

  1. A table visual cannot show text and numbers in the same row
  2. A merged column refreshes faster than one typed beside the rows
  3. Only a separate table can be hidden from the report’s readers
  4. The merge reattaches each conclusion after every refresh

9. After the merge, one exception from this month’s refresh has a null Disposition. What is it?

  1. An exception that a reviewer has marked as expected
  2. An open exception, still awaiting review
  3. A document missing from the population of its process
  4. A disposition whose document number was mistyped

10. A reviewer has time for one exception today, and the candidates’ amounts and urgency are otherwise alike. Under the chapter’s simple order of review, which should come first?

  1. A new exception on a document that fails three tests
  2. One of the year-end closing entries above every approval limit
  3. One of the registers approved by the employee they pay
  4. The oldest open exception in the register, from January 2024

11. Seventy-seven registers, spread over every month, fail the test of pay approved by the employee being paid. How should the review report them?

  1. As seventy-seven exceptions, each with its own follow-up question
  2. As false positives, because the approver is the accounting manager
  3. As one finding on the design of the control
  4. As expected exceptions that need no attention in later months

12. The Data Through card shows December 18, 2026, although the journal entries and the purchase orders run to December 31. What does the card establish?

  1. The refresh failed for two of the three populations
  2. The journal entries after December 18 were backdated
  3. The Date table of the monitoring file ends on December 18
  4. No payroll register in the extract has a later pay date

13. Which practice lets a reviewer see exactly which lines of a measure or query a change altered before the report is published?

  1. Keeping the report as a project under version control
  2. Applying a sensitivity label to the report in Power BI Desktop
  3. Adding a page navigator and an About page to the report
  4. Emailing each new version of the .pbix file to the reviewer

14. A department manager asks for a copy of Charles River Reports.pbix to keep and work with offline, outside the approved release. Why should internal audit object?

  1. A copy cannot be opened without a Power BI Pro license
  2. It holds every imported row and can drift from the release
  3. The copy’s measures are recalculated by a different engine
  4. The copy loses its Tests tab and the queries saved in it

16.13 Applied Exercises

Work on every exercise in a copy of Audit Monitoring.pbix, saved with File > Save as after you finish this chapter’s tutorials, such as Ch16 Exercises.pbix. Build each exercise on its own report page, or in its own query tab in DAX query view, named after it (such as Ex 16.1). Do not change the queries, relationships, measures, or pages the tutorials created. When an exercise needs a Table the tutorials did not load, import it under the query name the exercise gives.

16.13.1 Financial Accounting Exercises

Exercise 16.1: Monitoring the Receivables

Table group: Order-to-Cash

Scenario. Chapter 8 aged the receivables once, at the end of fiscal 2026, and found that the detail did not agree with the ledger. The audit committee wants the aging and its reconciliation on the monitoring report, so that the difference is followed every time the report is refreshed.

Requirements.

  1. Import T16_SalesInvoice as ARInvoices, keeping SalesInvoiceID, InvoiceNumber, CustomerID, InvoiceDate, DueDate, and GrandTotal; T20_CashReceiptApplication as ARApplications, keeping SalesInvoiceID, ApplicationDate, and AppliedAmount; and T23_CreditMemo as ARCredits, keeping OriginalSalesInvoiceID, CreditMemoDate, and GrandTotal.

  2. In Power Query, keep the applications and credit memos dated on or before 2026-12-31, group each by invoice with Group By, and merge the totals into ARInvoices. Replace the nulls of the two merged totals with 0, as in Tutorial 8.2, Step 2e: an invoice with no application or credit memo finds no match, and a subtraction with a null returns null. Add OpenBalance as GrandTotal less the applications and credits, rounded to two decimals, and keep the invoices dated on or before 2026-12-31 with an open balance above zero.

  3. Add an aging column with the days past due at 2026-12-31, in Chapter 8’s buckets: Current, 1–30, 31–60, 61–90, and over 90 days. Show the open balance and the number of invoices by bucket.

  4. Import T3_GLEntry as ARLedger with Transform Data, merge AccountNumber from the Account query, because the ledger records each account’s AccountID, and keep the postings to account 1020 dated on or before 2026-12-31, and compare their balance with the open balances. Explain the difference with Chapter 8’s finding.

  5. The open balances leave out invoices whose credits and applications exceed their total. Duplicate ARInvoices as ARCreditBalances with the filter reversed to keep them, count them, total them, and explain where Chapter 8 found that they belong.

Deliverable. The aging page, the reconciliation, and a short note answering requirements (4) and (5).

Exercise 16.2: Year-End Cutoff on the Monitoring Report

Table group: Order-to-Cash

Scenario. Chapter 12 found sales invoices for December deliveries recorded in January, and December shipments never invoiced at all. Added to the monitoring report, the same tests would show these exceptions at the first refresh after each year-end, not at the next annual review. They compare the year of delivery with the year of the invoice, so they test the cutoff at a year-end, not between the months of a year.

Requirements.

  1. Import T14_Shipment as CutoffShipments, keeping ShipmentID and DeliveryDate, and T15_ShipmentLine as CutoffShipmentLines, keeping ShipmentLineID, ShipmentID, and ExtendedStandardCost. Merge the delivery date onto the shipment lines.

  2. Import T17_SalesInvoiceLine as CutoffInvoiceLines, keeping ShipmentLineID and SalesInvoiceID, and T16_SalesInvoice again as CutoffInvoices, keeping SalesInvoiceID, InvoiceNumber, InvoiceDate, and SubTotal. Merge InvoiceNumber, InvoiceDate, and SubTotal onto the invoice lines, and the delivery date from CutoffShipmentLines. The ARInvoices query of Exercise 16.1 cannot supply the invoices, because it keeps only those open at the end of the year.

  3. Build two exception queries with the register’s columns: SI DeliveredPriorYear, one row per invoice whose lines were all delivered in a year before the year of the invoice, dated by the invoice, with its SubTotal as the amount; and SL NotInvoiced, one row per shipment line with no invoice line, dated by its delivery, with its standard cost as the amount.

  4. Append them to a copy of the register named Exceptions 16.2, and add the two tests to a copy of the Tests table named Tests 16.2. Relate Exceptions 16.2 to Tests 16.2 on TestID and to the Date table on EventDate, add a measure that counts the rows of Exceptions 16.2, and show the new tests by month.

  5. Chapter 12 counted the invoices for December deliveries by the date they were posted in the ledger, and found one more in January 2026 than your test finds. Find that invoice in the ledger and explain the difference. Use the ARLedger query of Exercise 16.1. If you work on this exercise alone, import T3_GLEntry as ARLedger with GLEntryID, PostingDate, AccountID, Debit, Credit, VoucherNumber, and SourceDocumentType, merge AccountNumber from Account, and keep account 1020 through 2026-12-31. Leave the query unfiltered by invoice, and look for the invoice in a report table or a DAX query.

Deliverable. The two exception queries, the page, and a short note answering requirement (5).

16.13.2 Managerial Accounting Exercises

Exercise 16.3: Surge Days by Month

Table group: Payroll and Time

Scenario. Part III traced the growth of the plant’s overtime to surge days, days on which every hourly manufacturing employee clocked exactly the same hours, and Chapter 12 found that the production manager approved every surge-day entry. The audit committee wants to know each month whether surge days continue, and asks internal audit to add them to its monitoring report.

Requirements.

  1. Import T66_TimeClockEntry as ClockEntries, keeping EmployeeID, WorkDate, RegularHours, and OvertimeHours, and merge the employee’s CostCenterID from the Employee query. Keep the entries of cost center 4, Manufacturing.

  2. Add a column of total hours, regular plus overtime. Reference ClockEntries as SurgeDays, and group it by WorkDate with Group By in its advanced mode, with three aggregations: Employees and HoursValues, each with any operation for now, and OvertimeHours, the sum of the overtime hours. Group By in Power BI Desktop cannot count the distinct values of a column (its Count Distinct Rows counts whole rows), so in the formula bar change the first two aggregations to the ones below, which count the distinct employees and the distinct values of total hours.

{"Employees", each List.Count(List.Distinct([EmployeeID])), Int64.Type},
{"HoursValues", each List.Count(List.Distinct([TotalHours])), Int64.Type},

  1. In SurgeDays, keep the days with more than one employee and a single distinct value of total hours, Chapter 12’s test H8. Like H8, it counts the employees who clocked in, not the plant’s roster. Load both queries, and relate each to the Date table on WorkDate.

  2. Show the surge days and their overtime hours by month, with a line of the share of the plant’s overtime they account for, the overtime of all of the month’s entries in ClockEntries.

  3. Describe when surge days began, how often they occur, and how their share of overtime has changed. Explain why the test belongs in internal audit’s monitoring report rather than on the Plant page of the package, which the production manager reads.

Deliverable. The SurgeDays query, the page, and a short note answering requirement (5).

Exercise 16.4: Exception Rates over Time

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

Scenario. The audit committee asks whether the controls behind the monitoring tests are getting better or worse. Counts alone cannot answer that when the number of documents changes from year to year, and the exceptions already explained, the year-end closes and the recurring payroll test, hide what changes in the rest.

Requirements.

  1. On a page named Ex 16.4, add a matrix with Process from the Tests table on Rows, Year on Columns, and Exceptions, Population, and Rate per 1,000 as Values. Explain why each process shows a rate while the total row does not.

  2. Write a measure Unexplained Rate per 1,000 that divides the exceptions, leaving out those with the disposition Expected and those of the test PR SelfApproved, by the population, and add it to the matrix.

  3. Add a line chart of Rate per 1,000 by Year, with Process as the Legend, and a second chart of Unexplained Rate per 1,000.

  4. Compare the purchase orders’ exceptions and their rate from 2025 to 2026, and explain why the count and the rate tell different stories.

  5. Write a short note for the audit committee: how each process’s rate has changed, what more you would need before calling a change an improvement in its controls, and what the unexplained rate adds to the rate of all exceptions.

Deliverable. The page, the measure, and the note.

16.13.3 Auditing Exercises

Exercise 16.5: Benford’s Law in DAX

Table group: Procure-to-Pay

Scenario. Chapter 8 tested the first digits of supplier payments in a workbook and found that partial payments, not the payments as a whole, departed from Benford’s Law. Nickell et al. (2023) show the same test built in Power BI. The audit committee wants it on the monitoring report as the anomaly layer beside the rule tests.

Requirements.

  1. Import T39_DisbursementPayment as Payments, keeping DisbursementID, PaymentDate, SupplierID, PurchaseInvoiceID, and Amount, with the payments dated on or before 2026-12-31. Merge GrandTotal from T37_PurchaseInvoice, imported as PaymentInvoices with load cleared, and add a column PaymentKind, Full when the amount equals the invoice total and Partial when it is less.

  2. Add a calculated column FirstDigit that takes the first digit of the amount from its scientific notation, as Chapter 8 did, so that amounts below one dollar are classified correctly. Explain why taking the first character of the amount as text would fail.

  3. Build a disconnected table of the digits 1 to 9, with Benford’s expected share for each digit, LOG10 ( 1 + 1 / [Digit] ). Write measures for the observed share of each digit and for the mean absolute deviation across the nine digits, one that ignores a digit selected on the page.

  4. Show the observed and expected shares by digit, with a slicer of PaymentKind, and record the mean absolute deviation for all payments, full payments, and partial payments.

  5. Classify each result with Chapter 8’s ranges, and explain why a departure in the partial payments is an anomaly that calls for a look rather than an exception.

Deliverable. The page, the measures, and a short note answering requirements (2) and (5).

Exercise 16.6: Near-Duplicates and the Concentration of Approval

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

Scenario. The chief audit executive asks for two additions to the monitoring report: the near-duplicate payments of Chapter 8, as a second anomaly test beside Benford’s Law, and a measure of how much of each process rests on one approver, which the exceptions by approver do not show.

Requirements.

  1. In Power Query, reference the Payments query of Exercise 16.5 as PaymentPairs, and merge it with itself on SupplierID and Amount. Keep the pairs of two different payments on different dates, each pair once, by keeping the rows whose DisbursementID is less than the other payment’s: the merge pairs every payment with itself, and every pair in both orders.

  2. Add the number of days between the two payments, and show the pairs by that number. Count the pairs within 30 days, and check whether any pair pays the same invoice.

  3. Write three measures, Entries Approved, Orders Approved, and Registers Approved, that count the documents each employee approved. JournalEntry is related to Employee through its approver; PurchaseOrder and PayrollRegister are not, and Model view should show no line from either to Employee, so apply the employees in the filter context to their ApprovedByEmployeeID with TREATAS, which Test 2 of Tutorial 14.2 used to apply a list of values as a filter.

  4. Add a measure for each count’s share of its process’s documents, and show a table by EmployeeName and JobTitle with the three counts and shares. Compare it with the bar chart of exceptions by approver on the Monitoring page.

  5. Explain why a near-duplicate pair on different invoices is an anomaly to look at rather than a duplicate payment, and what the shares of approval say about the design of the purchasing and payroll approvals that no exception shows.

Deliverable. The PaymentPairs query, the table of approval shares, and a short note answering requirement (5).

16.14 Further Reading

Codesso, M., de Freitas, M. M., Wang, X., de Carvalho, A., & da Silva Filho, A. A. (2020). Continuous audit implementation at Cia. Hering in Brazil. Journal of Emerging Technologies in Accounting, 17(2), 103–118. https://doi.org/10.2308/jeta-2020-006 A case of internal auditors at a large Brazilian clothing company who implemented continuous auditing of the tax compliance of their manufacturing division by comparing the company’s electronic invoices with data from the state tax office. It shows a monitoring test that compares internal records with an outside source, a kind of test this chapter’s register does not include.

Gonzalez, G. C., & Hoffman, V. B. (2018). Continuous auditing’s effectiveness as a fraud deterrent. Auditing: A Journal of Practice & Theory, 37(2), 225–247. https://doi.org/10.2308/ajpt-51828 An experiment on whether telling auditees promptly about the exceptions a monitoring system flags deters fraud. When the system’s detection was weak, prompt notification increased participants’ inclination to commit fraud, one reason this chapter keeps the monitoring report with internal audit.

Hardy, C. A., & Laslett, G. (2015). Continuous auditing and monitoring in practice: Lessons from Metcash’s Business Assurance Group. Journal of Information Systems, 29(2), 183–194. https://doi.org/10.2308/isys-50969 A case study of an Australian distributor that ran more than a hundred automated tests every day, with an integrated system for managing their exceptions and visual reports for reviewing them. It describes the challenges of reaching that point and how the company distinguished continuous auditing from continuous monitoring.

Institute of Internal Auditors. (2025). GTAG: Continuous auditing and monitoring (3rd ed.). https://www.theiia.org/en/content/guidance/recommended/supplemental/gtags/continuous-auditing-and-monitoring The Institute of Internal Auditors’ guidance on using technology to assess risks and controls on an ongoing basis, and on how continuous auditing works together with management’s continuous monitoring. It is the professional companion to the research cited in this chapter.

Nickell, E. B., Schwebke, J., & Goldwater, P. (2023). An introductory audit data analytics case study: Using Microsoft Power BI and Benford’s Law to detect accounting irregularities. Journal of Accounting Education, 64, 100855. https://doi.org/10.1016/j.jaccedu.2023.100855 A teaching case that builds a Benford’s Law analysis in Power BI to look for irregularities in accounting data. It practices, on a different company, the anomaly test of Exercise 16.5.

Tronto, S., & Killingsworth, B. L. (2021). How internal audit can champion continuous monitoring in a business operation via visual reporting and overcome barriers to success. The International Journal of Digital Accounting Research, 21, 23–59. https://doi.org/10.4192/1577-8517-v21_2 An account of how a university’s internal audit department worked with its procurement card program to build a continuous monitoring tool with visual reports, and of the barriers it met along the way. It shows internal audit helping management build monitoring that internal audit can rely on once it has evaluated it.

Vasarhelyi, M. A., & Halper, F. B. (1991). The continuous audit of online systems. Auditing: A Journal of Practice & Theory, 10(1), 110–125. https://doi.org/10.2308/ajpt-9703192366 A pioneering paper on continuous auditing, describing a system built for the internal auditors of AT&T Bell Laboratories that measured key figures of large systems, compared them with standards, and called the auditors’ attention to problems. Much of the later research on continuous auditing starts from it.

16.15 References

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

Alles, M. G., Kogan, A., & Vasarhelyi, M. A. (2008). Putting continuous auditing theory into practice: Lessons from two pilot implementations. Journal of Information Systems, 22(2), 195–214. https://doi.org/10.2308/jis.2008.22.2.195

Baaske, B. N., Eulerich, M., & Wood, D. A. (2025). Improving audit quality with data analytic visualizations: The importance of spatial abilities and feedback in anomaly identification. Accounting Horizons, 39(3), 85–97. https://doi.org/10.2308/horizons-2023-073

Gonzalez, G. C., & Hoffman, V. B. (2018). Continuous auditing’s effectiveness as a fraud deterrent. Auditing: A Journal of Practice & Theory, 37(2), 225–247. https://doi.org/10.2308/ajpt-51828

Hardy, C. A., & Laslett, G. (2015). Continuous auditing and monitoring in practice: Lessons from Metcash’s Business Assurance Group. Journal of Information Systems, 29(2), 183–194. https://doi.org/10.2308/isys-50969

Institute of Internal Auditors. (2025). GTAG: Continuous auditing and monitoring (3rd ed.). https://www.theiia.org/en/content/guidance/recommended/supplemental/gtags/continuous-auditing-and-monitoring

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

Li, P., Chan, D. Y., & Kogan, A. (2016). Exception prioritization in the continuous auditing environment: A framework and experimental evaluation. Journal of Information Systems, 30(2), 135–157. https://doi.org/10.2308/isys-51220

Microsoft. (2025). Unpivot columns. Microsoft Learn. https://learn.microsoft.com/en-us/power-query/unpivot-column

Microsoft. (2026a). Append queries. Microsoft Learn. https://learn.microsoft.com/en-us/power-query/append-queries

Microsoft. (2026b). Power BI Desktop projects (PBIP). Microsoft Learn. https://learn.microsoft.com/en-us/power-bi/developer/projects/projects-overview

Nickell, E. B., Schwebke, J., & Goldwater, P. (2023). An introductory audit data analytics case study: Using Microsoft Power BI and Benford’s Law to detect accounting irregularities. Journal of Accounting Education, 64, 100855. https://doi.org/10.1016/j.jaccedu.2023.100855

Tronto, S., & Killingsworth, B. L. (2021). How internal audit can champion continuous monitoring in a business operation via visual reporting and overcome barriers to success. The International Journal of Digital Accounting Research, 21, 23–59. https://doi.org/10.4192/1577-8517-v21_2

Vasarhelyi, M. A., & Halper, F. B. (1991). The continuous audit of online systems. Auditing: A Journal of Practice & Theory, 10(1), 110–125. https://doi.org/10.2308/ajpt-9703192366