Chapter 16: Audit Monitoring and Report Assurance

Accounting Analytics: An Integrated Approach

In this chapter

Part IV of V: Data Visualization and Power BI

Chapter 4 of 4 in this Part

• 16.1 From Audit Tests to Monitoring
• 16.2 Designing an Exception Register

• Guided Tutorial 16.1
• 16.4 The Monitoring Page
• Guided Tutorial 16.2
• 16.6 Reviewing Exceptions
• 16.7 Auditing the Report Itself
• Guided Tutorial 16.3
• 16.9 Looking Ahead

This chapter turns the audit tests of Chapters 8 and 12 into monthly monitoring, and then audits the reports themselves. Each module follows a section of the chapter, and the three guided tutorials build internal audit’s own file, Audit Monitoring.pbix.

Learning objectives

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.

The first three objectives cover monitoring and its tests, the exception register, and the monitoring page.

Learning objectives (continued)

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.

The last three cover reviewing exceptions over time, testing a report before relying on it, and the change controls a monthly report needs.

Three requests reach internal audit

• The audit committee: do the exceptions of earlier audits recur?
• The chief audit executive: review each exception once, and keep the result
• The controller: can internal audit rely on the reporting package too?
• So: build a monitoring report, and test both reports before relying on them

You are back on the internal audit team of Chapters 8 and 12. Those tests found entries approved by their creators, orders dated after their approver had left, 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. How a report is published and shared is the subject of the appendix on publishing and security.

16.1 From Audit Tests to Monitoring

Monitoring runs the same tests every period

• Chapter 8 tested whole populations once, in a workbook
• Chapter 12 saved its tests as queries to rerun every quarter
• Monitoring runs them every period, on new data, and keeps the results
• So the results can be compared over time

Vasarhelyi and Halper described one of the first such systems, for the internal auditors of AT&T Bell Laboratories: it measured key figures of large systems, compared them with standards, and called the auditors’ attention to problems as they arose. Two decades later, the adoption survey and the pilot implementations at two firms that Alles and colleagues describe showed continuous auditing moving from research into practice.

Continuous auditing and continuous monitoring

• Continuous auditing: internal audit’s ongoing assessment of risks and controls
• It gives the board and management assurance throughout the year
• Continuous monitoring: management’s ongoing review of its own controls
• Auditing relies on strong monitoring, which internal audit can help build

The IIA’s guidance defines continuous auditing as internal audit’s use of technology for ongoing assessment. Tronto and Killingsworth note that many auditors hold monitoring to be management’s job, not internal audit’s. This chapter builds the report as internal audit’s tool; the same tests could be handed to management to run as its own monitoring.

Rules produce exceptions; analytical tests, anomalies

• A rule holds or fails, such as approval within the approver’s limit
• A failure is an exception: a record that needs an explanation
• An analytical test finds anomalies: records unlike what is expected
• An anomaly is a reason to look, not yet a failure

Kogan and colleagues separate a first layer of deterministic business rules from a second layer of analytical monitoring. Two equal payments to one supplier a few days apart, or first digits that depart from Benford’s Law, are anomalies. The register of this chapter holds the rule tests; the anomaly tests return in the exercises. Each test states its rule, population, expected result and frequency; Metcash, an Australian distributor, ran more than a hundred automated tests daily with a system for managing their exceptions.

Five journal entry tests from Chapter 8

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

Every test in the register is a rule the book has already run once, so its results can be checked against the earlier chapters.

Six purchasing and payroll tests

Table 16.1 · The monitoring tests

TestID	Test	Process	Source
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

The purchase order tests come from Chapter 12 and the payroll tests from Exercise 12.5. Each test belongs to the process whose documents it tests, which decides the population its rate divides by.

The monitoring cycle

Figure 16.1 · The Monitoring Cycle (detail)

Gonzalez and Hoffman found that telling people promptly which of their transactions a monitoring system had flagged also told them what it could not detect, and that with a weak system, prompt notification made participants more inclined to commit fraud. So the report, with what its tests can and cannot detect, goes to the people who oversee the controls, which is why it lives in a file of its own rather than in the controller’s package. The people whose approvals it tests still hear of the exceptions they must explain, through internal audit’s questions rather than the report.

Check your understanding

Exception or anomaly; auditing or monitoring?

1. An entry approved by the person who created it
2. Two equal payments to one supplier a few days apart
3. Management reviews its own approvals every month
4. Internal audit runs the tests to give the board assurance

Ask students which of the four needs an explanation before anyone calls it a problem.

Each one has its name

1. An exception: a rule failed
2. An anomaly: a reason to look, not yet a failure
3. Continuous monitoring
4. Continuous auditing

The second is the anomaly: it may have a good explanation, which only a look at the payments can show.

16.2 Designing an Exception Register

One table holds every test’s exceptions

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

An exception register has one row for each exception of each test, with the same columns whatever the test: the test, the document, its date, its amount and the employee responsible, here the approver. Chapter 12 built the same thing in SQL with UNION ALL. In Power BI it is built in Power Query, so every refresh runs the tests again on new data.

Flag, unpivot, append

• Each test becomes a flag column: one if a record fails, zero if not
• Unpivot turns the flag columns into rows, one per record and flag
• Keep the rows whose value is one: exactly the exceptions
• Append stacks the processes, matching columns by name, not position

The flags of a document add up to its risk score, as Chapter 8’s flag formulas did. Unpivot puts each flag’s name in a column named Attribute and its value in Value. Append differs from UNION ALL in the way that matters: a column one table lacks is filled with nulls, so each exception table must rename its columns to the register’s names first.

A count needs its population

• A dozen exceptions in a few hundred entries is a high rate
• The same dozen in many thousands of orders is a low one
• So the populations stay in the model, as fact tables of their own
• They are also what the auditor reconciles before relying on the tests

A measure divides the exceptions of each test by the documents of its process. 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.

From flags to the register in Power Query

Figure 16.2 · The Exception Register in the Monitoring Model (detail)

Each flagged table feeds an exception query through Unpivot and a filter on the flag value, and the three exception queries, not loaded, are appended into one Exceptions table.

The register beside the populations it tests

Figure 16.2 · The Exception Register in the Monitoring Model (detail)

Exceptions relates to Tests on TestID, to Date on EventDate, and to Employee on EmployeeID. Employee also reaches JournalEntry through the entry’s approver, but not the orders or the registers. The three flagged tables stay in the model as the populations the rates divide by, and JournalLines relates to JournalEntry.

Guided Tutorial 16.1: Journal Entry Monitoring

Steps 1 to 6: start the file and flag the entries

1. Start the monitoring file
2. Keep the columns the tests need
3. Bring in the approver’s limit
4. Add the first three flags
5. Add the last two flags and the score
6. Load the entries and give them a calendar

The monitoring report is a new file of its own. The approver’s limit is merged onto each entry, as in Tutorial 8.3, and the five tests become flag columns in Power Query, written once and applied by every refresh.

Steps 7 to 12: score, drill through and reconcile

7. Score the entries
8. List the riskiest entries
9. Bring in the entries’ ledger lines
10. Drill through to an entry’s lines
11. Turn the flags into exceptions
12. Reconcile the population

The ledger is filtered to the journal entry postings before anything else, so only a few thousand of its rows reach the model. JE Exceptions is not loaded, because Tutorial 16.2 appends it to the other processes. Test 1 shows the entries’ total equal to the debits and credits of their ledger postings: good evidence, though agreeing totals alone could hide an entry missing from both, as Tutorial 4.1 showed.

Each rule becomes a flag column

// Weekend
if Date.DayOfWeek([CreatedDate], Day.Monday) >= 5
    then 1 else 0

// SelfApproved
if [CreatedByEmployeeID] = [ApprovedByEmployeeID]
    then 1 else 0

// RiskScore
[Weekend] + [Backdated] + [SelfApproved]
    + [AboveLimit] + [RoundAmount]

With Monday as day 0, Saturday is 5 and Sunday 6, the same test as Chapter 8’s WEEKDAY with return type 2. Backdated compares the date part of CreatedDate with the posting date, AboveLimit compares the amount with the approver’s limit, and RoundAmount tests for whole thousands.

The flags and the score in Power Query

Figure 16.3 · The Journal Entry Flags in Power Query (detail)

Most entries carry five zeros. The first, the opening balance entry, fails four of the five tests. The Applied Steps list records each flag as a step, so the tests are documented in the query itself.

Eight entries fail two tests or more

Figure 16.4 · The Risk Scores and an Entry’s Ledger Lines (detail)

The score distribution reproduces Chapter 8’s: most entries score zero, a couple of dozen score one, a handful two, none three, and one four. Five of the entries scoring two were posted on the first day of a year and keyed on the first Saturday after it, and two are the round debt reclassifications above the controller’s limit. A risk score describes one entry, so it is set not to summarize before the visual’s filter tests it.

Drill through to the entry’s ledger lines

Figure 16.4 · The Risk Scores and an Entry’s Ledger Lines (detail)

The detail shows the first lines of the Entry Lines page for the opening balance entry. The entry has many lines, and the page’s total row shows its debits equal to its credits. Tutorial 16.2 uses the same move, from a test to the documents behind it, for every test of the register.

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 reconciled to the ledger.

16.4 The Monitoring Page

A heat map shows which tests fail, and when

• A matrix of tests by month, with the number of exceptions in each cell
• Cells shaded by rule, darker for more exceptions
• The number stays in every cell, so shading never carries meaning alone
• A drill-through page lists the documents behind any cell

A monitoring page answers three questions in order: which tests are failing, when, and on which documents. A dark row and a dark column mean different things. Baaske and colleagues found that participants who chose better visualizations identified anomalies more accurately.

A January cluster, and a row in every month

• Taken together, most entry and order exceptions fall in January
• A seasonal cluster points to how controls work at that time of year
• One test recurs every month: registers approved by their own employee
• A recurring exception points to the design of a control

January is when the first entries, orders and payrolls of a new year are processed, so the cluster tells the auditor where to look and when. The payroll test recurs because one employee approves every register, including the approver’s own, and no one has the task of approving the approver’s pay. It is not a lapse to explain one document at a time.

A rate sets each count beside its population

• Journal entries are few, so a handful of exceptions is a high rate
• Purchase orders run to many thousands, so theirs is a small fraction
• Rates per thousand show how often the tests find exceptions
• A count alone hides that as volume changes

Over the three years, the journal entry rate fell and rose again, the purchase order rate 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. A lower rate is not by itself evidence that a control improved. The tests, their coverage and the mix of documents must stay the same, and the causes still need a look. A process’s rate adds the exceptions of its tests, so a document that fails two tests counts twice: it counts failed tests per thousand documents, not documents with an exception.

Check your understanding

What does each pattern suggest?

1. A dark January column in every year
2. One row shaded in every month
3. A dark December cell for entries above the limit
4. Many exceptions in a process of many thousands of documents

Ask students which of the four calls for a change to a control rather than a look at documents.

Each pattern has its meaning

1. How controls operate at the start of a year
2. The design of a control: one finding, not dozens
3. The year-end closes, expected and explained once
4. Check the rate before the count

The December cell 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.

Guided Tutorial 16.2: The Exception Register and the Monitoring Page

Steps 1 to 7: flag the other processes and append

1. Choose the purchasing and payroll Tables
2. Keep the columns the tests need
3. Flag the purchase orders
4. Bring the payment, the period, and the payee onto each register
5. Flag the payroll registers
6. Turn the new flags into exceptions
7. Append the register

A relational comparison such as greater than returns null in Power Query when a side is null, so the termination flags first check, with a test that returns true or false, that a date exists. PurchaseOrder records no approval date, so the order date stands in for it, and only the approval log and the employment records can show when the approval was given. The payroll test compares the end of the pay period, not the pay date, so a period that ended before the employee left is not flagged only because it was paid later. A period that straddles the departure is still flagged and can hold wages earned before it. Because Append matches columns by name, the renaming of Step 6 is what puts each process’s dates under EventDate.

Steps 8 to 14: measure, map and test the register

8. Describe the tests
9. Relate the register and the populations
10. Measure the exceptions and their rates
11. Build the heat map
12. Add the approvers and the rates
13. Drill through to the documents
14. Test the register

The Tests table is the register’s dimension, with the process each test runs on. Test 2 returns the counts of Chapters 8 and 12 for each of the eleven tests, so the register reproduces the earlier tests. Step 12c sets the approver chart’s interaction with the rates table to None, because Employee reaches only the journal entries’ population.

Population counts the test’s own documents

Population =
SWITCH (
    SELECTEDVALUE ( Tests[Process] ),
    "Journal entries", COUNTROWS ( JournalEntry ),
    "Purchase orders", COUNTROWS ( PurchaseOrder ),
    "Payroll", COUNTROWS ( PayrollRegister )
)

• With no single process selected, it is blank: rates do not mix populations

In a visual by test and year, Population is the number of entries, orders or registers of that year. The rate divides the exceptions by Population and scales it to a thousand documents.

The heat map: January, and one row every month

Figure 16.5 · The Monitoring Page (detail)

The matrix shows all three years by month of the year. The January column is the darkest. The December cell for entries above the limit holds the year-end closes. One row is shaded in every month, and the page’s bar chart of exceptions by approver shows why: one approver far ahead of everyone else.

Rates set each count beside its population

Figure 16.5 · The Monitoring Page (detail)

The journal entry tests have the highest rates, because the population is small. The recurring payroll test has a far higher rate than the other payroll tests. The table keeps whole-process rates: a click on an approver leaves it unchanged, because Employee filters the exceptions and the journal entries but not the orders or the registers.

From a cell to the documents behind it

Figure 16.6 · The Exception Detail Page (detail)

Drilling through from the January cell of orders dated after termination shows orders that name as 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 that test’s exceptions of January only, and the detail should list as many documents as the cell counts.

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 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, and an exception that recurs every month and calls for a change to the control.

16.6 Reviewing Exceptions

Dispositions live in a table of their own

• Each exception needs a disposition: expected, follow up, or report
• Text typed beside a refreshed query can move to the wrong row
• So keep the dispositions in a table, keyed by test and document
• Merge them into the register; those without one are open, not yet reviewed

Chapter 8 kept its dispositions on a worksheet of pasted values for the same reason. Every refresh attaches each conclusion to its exception, and a new exception arrives with none. The dispositions are internal audit’s workpaper: only reviewers change them, a full record shows who reached each one and when (columns the tutorial’s short table leaves out), and Test 3 confirms that every disposition found exactly one exception. A disposition records a conclusion, not a resolution: follow-ups and deficiencies stay on the list until they are closed.

Order the review by score and novelty

• A monthly system can raise more exceptions than reviewers can examine
• All else equal, start with documents that fail more tests, and weigh amount, age and urgency
• An exception new this month comes before one already explained
• Scores can learn from what past investigations found

Li and colleagues note that the volume of exceptions can overwhelm an internal audit department, and Alles and colleagues found that preventing such alarm floods was critical at Siemens. Li and colleagues proposed suspicion scores from the rules each exception fails, updated as investigations show which were errors or fraud. Chapter 8’s risk score is a simpler first cue, not the score they tested, and a reviewer still weighs amount, age and urgency, so a serious single failure can come first.

Two kinds of exception are not reviewed one by one

• Expected every period, such as the year-end closes above every limit
• Its disposition and evidence are recorded once, then checked as it recurs
• Recurring by design, such as registers approved by their own employee
• One finding, reported once; the response is a change to the control

Reviewing either one by one would only bury the new exceptions among the old. Counting exceptions by approver does not show how much of a process rests on one person; that needs the population, the share of each process’s documents each employee approved. One employee approved every payroll register and another nearly every purchase order, which Exercise 16.6 measures.

Check your understanding

How should each be reviewed?

1. Year-end closing entries above every approver’s limit
2. Registers approved by the employee they pay, every month
3. An entry that fails four tests
4. An exception that is new this month

Ask students which two should not be reviewed document by document.

Each exception has its review

1. Explained once with evidence, then checked as it recurs
2. One design finding, reported once
3. First, other things equal, since it fails the most tests
4. Before the exceptions already explained

The first two are the kinds not reviewed one by one: one expected, one a design finding.

16.7 Auditing the Report Itself

The questions an auditor asks of a report

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

Both reports are evidence that someone relies on, and neither is better than the data and 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. Who can see a report belongs with how it is published, in the appendix on publishing and security.

Completeness: reconcile, or test for gaps

• Reconcile each population to the ledger, as Test 1 does
• With nothing to reconcile to, test the document numbers for gaps
• Unique numbers from one to the count leave no gap between them
• Rerun the tests at every review, and keep their results and date

Charles River numbers its purchase orders in one series across the years, as Exercise 8.5 found, so the gap test of Chapter 8 applies; a repeated number could hide a missing one, which is why the test counts the unique numbers, and whether orders were numbered past the last one in the model is a question for the purchasing system’s own count. The package’s Tests tab reproduces control totals of Chapters 6, 7 and 11. A test that passed in April says nothing about a measure changed in May.

Currency: each population has its own last date

• A report shows the data of its last refresh
• Entries and orders run to the last day of the year
• Payroll’s last pay date comes earlier, with later work in open periods
• So a page states the latest date that every population reaches

No pay period has a later pay date in December. The work of the rest of the month belongs to the two periods marked Open in the data, scheduled to pay in January 2027, so no payroll test has yet seen it. 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 its period. It is a bound observed in the extract, not proof that a refresh succeeded or that nothing is missing before it, and payroll’s completeness takes a reconciliation of the periods and registers expected with those recorded.

Change control: a project shows every change

• Each change is requested, tested, approved and recorded
• The person who approves a change is not the one who made it
• A .pbix is one binary file; a .pbip stores text that Git can compare
• The Tests tab, rerun before publishing, is the regression test

A report that managers act on every month is a system and deserves the change controls auditors expect of any system. Teams that keep reports as projects often review each change on a Git branch as a list of changed lines before it is merged and published; an auditor testing change control asks for that history. Sensitivity labels are not supported in projects, a tradeoff the appendix describes.

Watch out: an uncontrolled .pbix copy escapes control

• A copy of an import model holds every row, whatever its pages show
• Its data stop at the date of the copy
• Its measures can drift from the version readers use
• Keep one authoritative version, and approve and record any copy

Whoever opens an uncontrolled copy in Power BI Desktop can read all its imported data, so a project’s folders need the same protection as the file. When you audit a report, ask who holds copies. 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.

Check your understanding

Which question does each piece of evidence answer?

1. Unique order numbers that run from one to the count of orders
2. The package’s tests, rerun and dated
3. The Data Through card on the page
4. The Git history of the report’s project

Ask students which of the four a test that passed last month cannot answer.

Each piece of evidence has its question

1. Is every document in the model?
2. Do the measures compute what they claim?
3. How current are the data?
4. Who changed the report, and was the change reviewed?

A test that passed last month cannot answer the second for a measure changed since, which is why the tests are rerun at every review.

Guided Tutorial 16.3: Reviewing Exceptions and Testing the Reports

Review and test the reports in nine steps

1. Record the dispositions
2. Merge the dispositions into the register
3. Measure the open exceptions, and test the dispositions
4. Build the review page
5. List the documents that fail more than one test
6. Test the populations
7. State the date through which the data run
8. Rerun the package’s tests
9. Write the memo

The first review records three dispositions. The year-end closes are expected in size, with the closing-entry policy still to obtain. The orders and registers of the employee who had left are deficiencies to report, on the tutorial’s assumption that the reviewer examined the approval log, the employment records and the earnings, since the flags alone would support only a follow-up. The payments made before their registers were approved need a question for payroll. Open Exceptions counts the exceptions not yet reviewed, those with no disposition, and Test 3 shows that every disposition found exactly one exception; the follow-ups and deficiencies, though reviewed, still await action. Step 8 reruns the package’s tests and closes it without saving: internal audit tests the package but does not change it.

Most open exceptions are one design finding

Figure 16.7 · The Review Page (detail)

Most open exceptions belong to the registers approved by the employee they pay, a single design finding the memo reports once. The year-end closes have left the AboveLimit row, which now holds only the entries that still need a look.

Documents failing two or more tests for review

Figure 16.7 · The Review Page (detail)

At the top is the opening balance entry, which failed four tests; the other entries are the five keyed on the first Saturday of a new year and the two debt reclassifications. The orders outlined with dashes were reviewed for one of their two tests and are still open for the other: a disposition belongs to an exception, not to a document. The list continues with the remaining orders, among them the third dated after its approver had left and the two capital orders the purchasing manager approved alone.

Test 4 checks the numbers and the payment records

Figure 16.8 · Test 4 in DAX Query View (detail)

The variables hold the order numbers, taken from the last six characters of each PONumber, and the registers with no payment record. The query returns one row: the orders and their unique numbers, the first and last numbers, the registers, and those with no payment record.

Test 4: no gap; three registers lack a payment record

Figure 16.8 · Test 4 in DAX Query View (detail)

The order numbers are all different and run from one to the number of orders, so none is missing between them; whether any was numbered after the last is a question for the purchasing system’s count. The three registers without a payment record are 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 already appear as pay after termination; the third, the chief executive’s first register, is a finding for the memo.

Data Through summarizes observed event dates

Data Through =
MINX (
    {
        MAX ( JournalEntry[PostingDate] ),
        MAX ( PurchaseOrder[OrderDate] ),
        MAX ( PayrollRegister[PayDate] )
    },
    [Value]
)

• Earliest maximum event date: December 18, 2026, the scheduled PayDate of a loaded register

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, the last pay date of a payroll register. No pay period has a later pay date in December, and the rest of the month’s work is in the open periods, so the card is a bound observed in the extract, not proof that payroll is complete or that the refresh ran. Step 7c keeps a click on a cell or an approver from changing it, and MINX skips a blank, so an empty population is caught by the counts of Tests 1 and 4, not by the card.

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

What monitoring and the audit of the reports found

• Exceptions cluster in the first month of every year
• One recurs every month, because a control has no one to perform it
• Dispositions survive every refresh; open exceptions await review
• Entries reconcile, orders have no gap, and currency differs by process

Most of this work was descriptive and diagnostic analytics, and its recommendations, on the controls and on the reports, are prescriptive. The follow-ups and deficiencies stay on the list until they are closed, and the reports’ changes need controls that a single file cannot provide.

Part IV is complete

• A model from the workbook, measured with DAX
• A reporting package for the managers
• Monitored and audited for internal audit
• Next: the appendix, the Part IV case, and the capstone cases

How the package and the monitoring report reach their readers, and how each sees only what they should, is the subject of the appendix on publishing and security. The Part IV Comprehensive Case asks how a profitable year ended with less cash than it began. Part V offers three capstone cases that draw on every tool of the book.

Summary

• Monitoring reruns rule tests every period and keeps the results
• An exception register: flag, unpivot and append, beside its populations
• A heat map with rates and drill-through shows tests, months and documents
• Dispositions in their own table leave the open exceptions to review
• A report is audited for completeness, tests, currency and change control

Rule tests produce exceptions; analytical tests produce anomalies, which return in the exercises.

Key terms

Anomaly · Append · Change control · Continuous auditing · Continuous monitoring · Data currency · Exception register · Open exception · Unpivot

Every term is defined at the end of the chapter. Exception register, open exception and data currency are the ideas a monitoring report rests on.

Practice and next steps

• Financial Accounting
  • Exercise 16.1: Monitoring the Receivables
  • Exercise 16.2: Year-End Cutoff on the Monitoring Report
• Managerial Accounting
  • Exercise 16.3: Surge Days by Month
  • Exercise 16.4: Exception Rates over Time
• Auditing
  • Exercise 16.5: Benford’s Law in DAX
  • Exercise 16.6: Near-Duplicates and the Concentration of Approval

Next: Comprehensive Case: Finding the Cash Behind the Profit

Assign one exercise from each perspective; Exercise 16.2 adds the year-end cutoff tests to the monitoring report, Exercise 16.4 follows the exception rates over time, and Exercise 16.6 measures the concentration of approval. The Part IV Comprehensive Case follows.

About these slides

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

https://aa.accountinganalyticshub.com/chapters/16-audit-monitoring/chapter.html

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