ACCT-499 · Lesson 7 of 12 · 29 min
Data analytics for the engagement: journals, Benford and duplicate payments
Controls testing in Lesson 6 looked at samples. Data analytics looks at everything. With an extract of the full general ledger and the accounts payable file, the team can test all 3,000 journal entries and all 5,000 payments for patterns that sampling would almost never catch. ACCT-341 taught the techniques: journal entry testing, Benford analysis and duplicate detection. ACCT-460 showed how they are used to find fraud. In the capstone, Priya applies them to Bluestem's data with two goals: to find specific items that need explanation, and to give the client a repeatable set of tests it can run itself.
Getting and checking the data
Analytics is only as good as the data. The team requests three extracts from the ERP system: the general ledger journal entry detail for 2026 with posting date, time, user, account, amount and description; the accounts payable payment file with vendor, invoice number, amount and payment date; and the vendor master with addresses and bank details. Before running any test, it checks completeness and accuracy. The total of the journal entry extract must agree with the trial balance movements for the year; the payment file must agree with the cash disbursements in the bank reconciliations; record counts must match what the system reports. In Bluestem's case the first journal extract was short by about 1,200 entries because the export excluded system-generated postings; a second extract fixed it. Without that check, every later result would have been unreliable.
The data are confidential. The team receives them through the university's secure file share, works only on university machines, removes personal information that the tests do not need, and agrees with Bluestem that all copies will be deleted at the end of the term. These steps reflect both the confidentiality rules of the professional codes and the privacy laws that apply to employee and supplier data.
Journal entry testing
Management override of controls is a risk in every organization, because managers can direct staff to make entries that bypass normal processes. Auditing standards require auditors to test journal entries for evidence of override, and the same tests are valuable in an advisory review. The tests look for characteristics associated with inappropriate entries: posted on weekends or holidays, posted outside working hours, round amounts, amounts just below an approval threshold, manual entries with no description, and entries close to the year end. No single characteristic is suspicious; combinations are. The program applies six filters to a synthetic version of Bluestem's 2026 ledger and lists entries that hit three or more.
# Bluestem Water Instruments Inc. (invented), in thousands of dollars, years ended December 31.
YEARS = [2024, 2025, 2026]
IS = {'Hardware revenue': [138000, 149500, 161200], 'Subscription revenue': [44000, 55600, 69800],
'Hardware cost of sales': [82800, 92700, 104800], 'Subscription cost of sales': [11000, 13300, 16100],
'Selling, general and admin': [42000, 48500, 56900], 'Research and development': [18200, 21000, 23600],
'Depreciation': [6200, 6900, 7600], 'Amortization of intangibles': [3000, 4800, 4800],
'Interest expense': [2100, 3900, 4600], 'Income tax expense': [3300, 2500, 2500]}
BS = { # balances at December 31, 2023 to 2026
'Cash': [18000, 24200, 25500, 23400], 'Receivables': [26000, 30000, 39800, 52100],
'Inventories': [22000, 26000, 31500, 41900], 'Other current assets': [2800, 3000, 3600, 4100],
'Property, plant and equipment': [45000, 48000, 51000, 53500], 'Right-of-use assets': [13000, 12000, 15500, 14800],
'Intangible assets': [9000, 6000, 19200, 14400], 'Goodwill': [8000, 8000, 24000, 24000],
'Payables': [15000, 17000, 19500, 22700], 'Accrued liabilities': [8000, 9000, 10500, 11200],
'Deferred revenue': [11000, 14000, 18500, 21000], 'Warranty provision': [2300, 2500, 2700, 2600],
'Income taxes payable': [1200, 1300, 900, 700], 'Lease liabilities': [13500, 13000, 16800, 16300],
'Borrowings': [28000, 25000, 57000, 60000], 'Deferred tax liabilities': [1800, 2000, 2300, 2700],
'Share capital': [40000, 40000, 40000, 42000], 'Retained earnings': [23000, 33400, 41900, 49000]}
CF = {'Cash from operations': [20900, 15900, 5800], 'Capital expenditure': [-8000, -8400, -8400],
'Acquisition of Merrow Lane': [0, -34000, 0], 'Dividends paid': [-3000, -3000, -3000]}
def rev(i): return IS['Hardware revenue'][i] + IS['Subscription revenue'][i]
def cogs(i): return IS['Hardware cost of sales'][i] + IS['Subscription cost of sales'][i]
def ebit(i): return rev(i) - cogs(i) - sum(IS[k][i] for k in ('Selling, general and admin', 'Research and development', 'Depreciation', 'Amortization of intangibles'))
def ni(i): return ebit(i) - IS['Interest expense'][i] - IS['Income tax expense'][i]
ASSETS = ['Cash', 'Receivables', 'Inventories', 'Other current assets', 'Property, plant and equipment', 'Right-of-use assets', 'Intangible assets', 'Goodwill']
CUR_A = ASSETS[:4]
CUR_L = ['Payables', 'Accrued liabilities', 'Deferred revenue', 'Warranty provision', 'Income taxes payable']
LIABS = CUR_L + ['Lease liabilities', 'Borrowings', 'Deferred tax liabilities']
def tot(keys, t): return sum(BS[k][t] for k in keys)
# Lesson 7: journal entry testing filters on a synthetic 2026 general ledger extract.
import random, datetime as dt
random.seed(2026)
users = ['jlee', 'mroy', 'kdas', 'tfinn', 'hmarsh', 'sysauto']
start = dt.date(2026, 1, 1)
je = []
for n in range(1, 3001):
d = start + dt.timedelta(days=random.randrange(365))
if d.weekday() >= 5 and random.random() > 0.05: d -= dt.timedelta(days=d.weekday() - 4) # most entries post on weekdays
hour = random.choice([9, 10, 11, 13, 14, 15, 16]) if random.random() > 0.03 else random.choice([22, 23, 2])
amt = round(random.lognormvariate(8, 1.4), 2)
if random.random() < 0.02: amt = float(random.choice([10000, 25000, 50000]))
user = random.choice(users)
je.append({'id': n, 'date': d, 'hour': hour, 'amount': amt, 'user': user,
'manual': user != 'sysauto', 'desc': random.choice(['accrual', 'reclass', 'adjust', 'invoice', 'correction', ''])})
je.append({'id': 3001, 'date': dt.date(2026, 12, 31), 'hour': 23, 'amount': 4800000.00, 'user': 'hmarsh', 'manual': True, 'desc': ''})
threshold = 25000 # manual entries at or above this need controller approval
tests = {
'weekend or holiday postings': lambda e: e['date'].weekday() >= 5 or e['date'] in (dt.date(2026, 7, 1), dt.date(2026, 12, 25)),
'after-hours postings (before 7 or after 20)': lambda e: e['hour'] < 7 or e['hour'] > 20,
'round amounts (multiples of 1,000)': lambda e: e['amount'] >= 1000 and e['amount'] % 1000 == 0,
'just below approval threshold (90-100%)': lambda e: 0.9 * threshold <= e['amount'] < threshold,
'blank description, manual entry': lambda e: e['manual'] and e['desc'] == '',
'last two days of the year, manual': lambda e: e['manual'] and e['date'] >= dt.date(2026, 12, 30),
}
flags = {}
for name, f in tests.items():
hit = [e['id'] for e in je if f(e)]
for i in hit: flags[i] = flags.get(i, 0) + 1
print(f"{name:45} {len(hit):5}")
multi = sorted((v, k) for k, v in flags.items() if v >= 3)[::-1]
print(f"\nEntries hitting three or more tests: {len(multi)}")
for v, k in multi[:6]:
e = je[k - 1]
print(f" JE {k}: {e['date']} {e['hour']:02d}:00 {e['amount']:>13,.2f} by {e['user']} ({v} tests)")weekend or holiday postings 76 after-hours postings (before 7 or after 20) 88 round amounts (multiples of 1,000) 59 just below approval threshold (90-100%) 35 blank description, manual entry 393 last two days of the year, manual 14 Entries hitting three or more tests: 2 JE 3001: 2026-12-31 23:00 4,800,000.00 by hmarsh (4 tests) JE 2464: 2026-06-25 02:00 24,192.78 by jlee (3 tests)
Most filters return manageable numbers. About 400 manual entries have blank descriptions, which is a documentation weakness in itself and a finding for the report. Only two entries hit three or more filters. The first, JE 3001, stands out: a manual entry of exactly 4.8 million, posted at 11 p.m. on December 31 by the chief financial officer's user account, with no description. The team traced it to the bill-and-hold sale in Lesson 5: the billing system could not invoice goods that had not been picked, so the revenue was booked by manual journal. That explains how the sale bypassed the normal workflow, and it raises a question for Ms. Marsh about who used her account. She confirmed that she approved the entry at the sales director's request, believing finance had reviewed the arrangement; it had not.
The second entry, JE 2464, is a 24,193-dollar entry posted at 2 a.m. in June, just below the 25,000-dollar approval threshold. On enquiry it was an accrual reversal posted by a scheduled job under a staff member's account after a system migration. The team accepts the explanation but notes that scheduled jobs should run under system accounts, not personal ones, which links to the ITGC findings.
| Filter | Hits | What it may indicate | Follow-up |
|---|---|---|---|
| Weekend or holiday | 76 | Entries outside normal oversight | Review a sample; most were system postings |
| After hours | 88 | Rushed or concealed entries | Focus on manual entries by senior staff |
| Round amounts | 59 | Estimates or invented amounts | Match to supporting calculations |
| Just below threshold | 35 | Splitting to avoid approval | Look for related entries by the same user |
| Blank description, manual | 393 | Poor documentation | Policy requiring descriptions and support |
| Last two days of year, manual | 14 | Earnings management at year end | Review all 14 |
Benford's law
In many naturally occurring sets of numbers, the first digit is 1 about 30 per cent of the time and 9 less than 5 per cent of the time. Benford's law describes this distribution, and payment amounts usually follow it. Deviations can indicate invented numbers, duplicated amounts or systematic behaviour such as splitting invoices below an approval limit. Mark Nigrini's mean absolute deviation (MAD) thresholds, widely used in practice, classify conformity for the first-digit test as close below about 0.006, acceptable to 0.012, marginal to 0.015 and nonconforming above that. The program runs the test on a synthetic payment file and then looks more closely at the first two digits.
# Bluestem Water Instruments Inc. (invented), in thousands of dollars, years ended December 31.
YEARS = [2024, 2025, 2026]
IS = {'Hardware revenue': [138000, 149500, 161200], 'Subscription revenue': [44000, 55600, 69800],
'Hardware cost of sales': [82800, 92700, 104800], 'Subscription cost of sales': [11000, 13300, 16100],
'Selling, general and admin': [42000, 48500, 56900], 'Research and development': [18200, 21000, 23600],
'Depreciation': [6200, 6900, 7600], 'Amortization of intangibles': [3000, 4800, 4800],
'Interest expense': [2100, 3900, 4600], 'Income tax expense': [3300, 2500, 2500]}
BS = { # balances at December 31, 2023 to 2026
'Cash': [18000, 24200, 25500, 23400], 'Receivables': [26000, 30000, 39800, 52100],
'Inventories': [22000, 26000, 31500, 41900], 'Other current assets': [2800, 3000, 3600, 4100],
'Property, plant and equipment': [45000, 48000, 51000, 53500], 'Right-of-use assets': [13000, 12000, 15500, 14800],
'Intangible assets': [9000, 6000, 19200, 14400], 'Goodwill': [8000, 8000, 24000, 24000],
'Payables': [15000, 17000, 19500, 22700], 'Accrued liabilities': [8000, 9000, 10500, 11200],
'Deferred revenue': [11000, 14000, 18500, 21000], 'Warranty provision': [2300, 2500, 2700, 2600],
'Income taxes payable': [1200, 1300, 900, 700], 'Lease liabilities': [13500, 13000, 16800, 16300],
'Borrowings': [28000, 25000, 57000, 60000], 'Deferred tax liabilities': [1800, 2000, 2300, 2700],
'Share capital': [40000, 40000, 40000, 42000], 'Retained earnings': [23000, 33400, 41900, 49000]}
CF = {'Cash from operations': [20900, 15900, 5800], 'Capital expenditure': [-8000, -8400, -8400],
'Acquisition of Merrow Lane': [0, -34000, 0], 'Dividends paid': [-3000, -3000, -3000]}
def rev(i): return IS['Hardware revenue'][i] + IS['Subscription revenue'][i]
def cogs(i): return IS['Hardware cost of sales'][i] + IS['Subscription cost of sales'][i]
def ebit(i): return rev(i) - cogs(i) - sum(IS[k][i] for k in ('Selling, general and admin', 'Research and development', 'Depreciation', 'Amortization of intangibles'))
def ni(i): return ebit(i) - IS['Interest expense'][i] - IS['Income tax expense'][i]
ASSETS = ['Cash', 'Receivables', 'Inventories', 'Other current assets', 'Property, plant and equipment', 'Right-of-use assets', 'Intangible assets', 'Goodwill']
CUR_A = ASSETS[:4]
CUR_L = ['Payables', 'Accrued liabilities', 'Deferred revenue', 'Warranty provision', 'Income taxes payable']
LIABS = CUR_L + ['Lease liabilities', 'Borrowings', 'Deferred tax liabilities']
def tot(keys, t): return sum(BS[k][t] for k in keys)
# Lesson 7: Benford first-digit test and duplicate payment search on a synthetic accounts payable file.
import random, math
random.seed(7)
pay = [round(10 ** random.uniform(2, 5), 2) for _ in range(5000)] # three full decades: Benford-like
pay += [round(random.uniform(4500, 4999), 2) for _ in range(90)] # cluster below a 5,000 limit
obs = [0] * 10
for p in pay: obs[int(str(int(p))[0])] += 1
n = sum(obs)
mad = sum(abs(obs[d] / n - math.log10(1 + 1 / d)) for d in range(1, 10)) / 9
print("Digit observed expected")
for d in range(1, 10): print(f" {d} {obs[d] / n:7.1%} {math.log10(1 + 1 / d):7.1%}")
band = 'close conformity' if mad < 0.006 else 'acceptable conformity' if mad < 0.012 else 'marginal conformity' if mad < 0.015 else 'nonconformity'
print(f"MAD {mad:.4f} -> {band} (Nigrini's first-digit bands)")
# The overall MAD can hide a local spike, so look at first-two-digit groups 45 to 49.
two = {}
for p in pay: two[int(str(int(p))[:2])] = two.get(int(str(int(p))[:2]), 0) + 1
print("First two digits 45-49: " + ", ".join(f"{d}: {two.get(d, 0) / n:.2%} vs {math.log10(1 + 1 / d):.2%}" for d in range(45, 50)))
# Duplicate payments: same vendor and amount within 30 days, invoice numbers equal after cleaning.
inv = [('V118', 'INV-20931', 7420.00, 41), ('V118', 'inv 20931', 7420.00, 55), ('V204', '88412', 1260.50, 102),
('V204', '88412-A', 1260.50, 160), ('V090', 'BL7731', 15900.00, 210), ('V090', 'BL-7731', 15900.00, 222),
('V333', '5501', 980.00, 300), ('V333', '5502', 980.00, 301)]
clean = lambda s: ''.join(ch for ch in s.upper() if ch.isalnum()).replace('INV', '')
seen, dups = {}, []
for v, num, amt, day in inv:
key = (v, clean(num), amt)
if key in seen and day - seen[key] <= 30: dups.append((v, num, amt))
seen.setdefault(key, day)
print(f"\nPossible duplicate payments: {len(dups)}")
for v, num, amt in dups: print(f" vendor {v}, invoice {num}, {amt:,.2f}")Digit observed expected 1 29.3% 30.1% 2 18.0% 17.6% 3 12.6% 12.5% 4 11.1% 9.7% 5 8.3% 7.9% 6 6.5% 6.7% 7 5.5% 5.8% 8 4.7% 5.1% 9 4.0% 4.6% MAD 0.0051 -> close conformity (Nigrini's first-digit bands) First two digits 45-49: 45: 1.36% vs 0.95%, 46: 1.41% vs 0.93%, 47: 1.04% vs 0.91%, 48: 1.26% vs 0.90%, 49: 1.45% vs 0.88% Possible duplicate payments: 2 vendor V118, invoice inv 20931, 7,420.00 vendor V090, invoice BL-7731, 15,900.00
The overall result is close conformity, with a MAD of about 0.005. A team that stopped there would miss the point. The digit 4 is over-represented, and the first-two-digit view shows why: payments beginning with 45 to 49 occur about half again as often as expected. Bluestem's purchasing policy requires a second approval for purchases of 5,000 dollars or more, and a cluster just below the threshold suggests invoices being split or priced to avoid it. Priya filters the payments between 4,500 and 4,999 dollars by vendor and finds that most go to three maintenance contractors, often several on the same day for the same site. That is a finding for the procure-to-pay process, and it shows why overall statistics should always be followed by a look at the detail.
Duplicate payments
Duplicate payments are common and costly: an invoice is entered twice, once from the paper copy and once from an email, or with a slightly different invoice number. Exact matching misses most of them, because the second entry rarely matches exactly. The program cleans invoice numbers by removing spaces, punctuation and prefixes such as INV, then looks for the same vendor, the same cleaned invoice number and the same amount within 30 days.
In the program's output, two pairs are flagged: vendor V118's invoice INV-20931 entered again as inv 20931, and vendor V090's BL7731 entered again as BL-7731. A third pair, 88412 and 88412-A, is not flagged because the suffix makes it a different invoice after cleaning, which on review was correct: it was a separate partial delivery. A fourth pair with consecutive invoice numbers and identical amounts was a legitimate monthly service charge. Duplicate tests always need human review, because the cost of a false accusation, to a supplier or an employee, is high. The two confirmed duplicates totalled about 23,300 dollars, which Bluestem recovered from the suppliers.
Vendor master and payroll matches
The team ran two further tests that do not need code to explain. It matched vendor bank accounts and addresses against the employee master file and found one match: a contractor whose bank account was the same as a former employee's. On investigation the former employee had become a contractor through a properly approved process, so the match was explained, but the team recommends that such conversions be flagged in the vendor master. It also looked for vendors created and paid within a short time by the same user, and found three, all created by user U07, the accounts payable clerk with conflicting roles in Lesson 6. Each was a genuine supplier, but the combination of conflicting access and quick payments is exactly the pattern a fraudster would exploit.
From analytics to findings
Analytics produce many results, most of which are explained by normal business. The team's working papers record every exception and its resolution, and the report presents only the findings that matter. For Bluestem, three analytics findings go to the client: the year-end manual revenue entry, which supports the reporting and controls findings; the cluster of payments below the approval threshold, which points to a procure-to-pay weakness; and the duplicate payments, which were small but show that the accounts payable process has no duplicate check. The team also gives Bluestem the scripts, with instructions, so that its finance team can run the tests quarterly. Handing over tools is one of the most valuable things a capstone can leave behind.
A note on standards. When auditors perform these tests, they follow the requirements on journal entry testing and fraud in CAS 240 and on audit data analytics in CPA Canada's guidance. The team's tests are advisory and follow no assurance standard, but their design borrows from the same thinking: define the population, test completeness, set criteria in advance, investigate exceptions and document conclusions.
Worked problem
Following up a threshold cluster
Payments between 4,500 and 4,999 dollars are 50 per cent more frequent than Benford predicts. What steps should Priya take?
Show solution
- Filter payments in that range and group them by vendor, approver, date and site.
- Look for several payments to the same vendor for the same site or job on the same or nearby days.
- Obtain the underlying purchase orders and invoices for the largest clusters.
- Discuss with the purchasing manager whether work was split to stay under the 5,000-dollar limit, and why.
Answer: Drill down by vendor, date and site, examine documents for the clusters, and discuss the findings with management before concluding.
Common mistakes
MistakeRunning tests on an extract without checking it to the ledger.
InsteadReconcile totals and record counts before testing; the first extract may be incomplete.
MistakeReporting every exception as a finding.
InsteadInvestigate and resolve exceptions; report only those that matter.
MistakeStopping at an overall Benford result.
InsteadLook at individual digits and first-two-digit groups for local anomalies.
MistakeUsing exact matching for duplicates.
InsteadClean invoice numbers and use tolerant matching, then review results manually.
MistakeAccusing a person on the basis of a data pattern.
InsteadTreat patterns as leads; obtain evidence and explanations before concluding.
Summary and key terms
- Analytics test whole populations; check completeness and accuracy first.
- Journal entry filters found a manual 4.8-million revenue entry at 11 p.m. on December 31.
- Benford's law showed close conformity overall but a spike just below the approval limit.
- Tolerant matching found two duplicate payments; human review rejected two false positives.
- Vendor and employee matches and quick vendor payments link to the access findings.
- Leave the client with repeatable scripts and instructions.
- Journal entry testing
- Analysing journal entries for characteristics associated with error or management override.
- Benford's law
- The expected distribution of leading digits in many naturally occurring data sets.
- Mean absolute deviation (MAD)
- The average absolute difference between observed and expected digit proportions.
- Duplicate payment
- A payment made more than once for the same invoice.
- Management override
- Managers bypassing controls, often through manual entries or estimates.
- Completeness check
- Reconciling a data extract to its source to confirm nothing is missing.
Practice problems
Work each problem on paper first, then reveal the answer to check.
Why did the team reconcile the journal extract to the trial balance?
Show answer
To confirm completeness; the first extract excluded about 1,200 system postings.
Name four journal entry characteristics associated with override.
Show answer
Any four of: weekends or holidays, after hours, round amounts, just below thresholds, blank descriptions, near year end.
What did the team learn from JE 3001?
Show answer
The bill-and-hold revenue was booked by a manual journal at year end, bypassing the billing workflow.
What is Benford's expected frequency of the first digit 1?
Show answer
About 30.1 per cent.
Why did the overall Benford result not settle the question?
Show answer
A local spike in digits 45 to 49 indicated activity just below the approval limit.
Which cleaning steps help find duplicate invoices?
Show answer
Removing spaces, punctuation and prefixes such as INV, and matching vendor and amount within a time window.
Why was the pair 88412 and 88412-A not a duplicate?
Show answer
It was a separate partial delivery; the suffix distinguished it.
What did the vendor creation test find?
Show answer
Three vendors created and paid quickly, all by the clerk with conflicting roles; genuine, but a risky pattern.
What should the team hand over at the end of the analytics workstream?
Show answer
The scripts and instructions so that Bluestem can run the tests itself.
Why must duplicate results be reviewed by a person?
Show answer
False positives are common, and wrongly accusing a supplier or employee is costly.
Lesson quiz · 5 questions
Score 60% or more to complete this lesson.
Create a free account to save your progress, take quizzes and earn a certificate.