Journal Entry and Ledger Testing

Journal Entry and Ledger Testing
Purpose	Detect management override and fraudulent postings in the general ledger
Audit basis	PCAOB AS 2401; ISA 240; AU-C 240
Core tests	Round-amount, weekend/holiday, after-close, duplicate, gap, rare-user, Benford
Tools	SQL, Excel, Python (pandas), audit analytics packages
Key risk	Incomplete or altered data extract

Journal entry testing is the examination of the general ledger’s postings to find entries that are unusual in timing, amount, preparer or account combination, and therefore more likely to be fraudulent or erroneous. It is a required response to the risk of management override of controls in financial-statement audits: PCAOB Auditing Standard 2401 notes that fraud often involves recording inappropriate or unauthorized journal entries throughout the year or at period end, and requires the auditor to test journal entries and other adjustments.^[1] For a forensic examiner the same techniques apply at larger scale and with more freedom: the ledger is the place where financial-statement fraud must eventually be recorded and where many occupational-fraud schemes leave a trace. This article is a hands-on guide; for the statistical foundation, see Benford’s Law and Analytical Methods.

1 Data acquisition and completeness

PCAOB inspections have repeatedly found that auditors failed to test the completeness of the population of journal entries.^[2] A flawless analysis of an incomplete extract is worthless, so validate before testing.

1. Define the sources. The population should include the general ledger, subledgers that post by interface (AR, AP, payroll, fixed assets, inventory), manual top-side entries, consolidation and elimination entries, and post-closing adjustments. Fraudulent entries are frequently made in the last category, outside the ERP.
2. Obtain the extract yourself or witness it. Have IT run the query under your direction, record the query text, server, run time and user, and take a hash of the output file (for example, SHA-256). Preserve it as evidence (see Chain of Custody).
3. Required fields. Journal ID, line number, posting date, entry (document) date, period, account, amount debit/credit, description, preparer user ID, approver user ID, source/module, manual versus automated flag, reversal flag, and timestamp.
4. Control totals. Verify (a) total debits equal total credits for every journal and for the file; (b) record count equals the count reported by the system; (c) the opening balance of each account plus the net activity equals the closing balance on the trial balance; (d) the trial balance agrees to the financial statements. Any unreconciled difference means missing or altered data.
5. Sequence check. Entry numbers should run without gaps (see section 5); gaps may mean deleted or unextracted entries.
6. Cross-source tie-out. Reconcile subledger totals to control accounts, and sample entries from the source documents to the ledger, to test whether entries outside the extract exist (a “reverse” completeness test).
7. Document everything so the work is reproducible (see Report Writing).

2 Selection criteria

AS 2401 and the ACFE both list characteristics of entries associated with override. A practical criteria set, with the logic for each, is below.^[1][2]

Test	Criterion	Rationale / typical false positives
Round amounts	Amount divisible by 1,000 (or ends in 000); entries ending in 999	Estimates and plugs are round; genuine invoices rarely are. Rent, loan payments and accruals are legitimate.
Weekend / holiday	Posting date on Saturday, Sunday or a company holiday	Override usually avoids supervision. Batch jobs and overseas offices are false positives.
After close	Entry date after period end but posted to the closed period; entries in the last 3 days of the period or the first days after close	Used to meet targets. Normal close adjustments create many hits.
Rare users	Posted by an executive, an IT/administrator ID, or a user who posts fewer than N entries per year	Management should rarely post directly.
Unusual account pairs	Debit to expense and credit to revenue; debit to cash and credit to an unusual liability; entries to seldom-used, suspense or intercompany accounts	Revenue overstatement often debits receivables and credits revenue with an unusual offset.
Missing or vague description	Blank, “adjustment,” “per CFO”	Concealment.
Large and manual	Manual entry above materiality threshold	High-value manual entries have the greatest impact.
Round-trip / reversals	Entry reversed in the next period	Window dressing across the period end.
Approval	Preparer = approver; no approver; approval after posting	Segregation of duties failure.
Near-threshold	Amounts just below an approval limit (for example, $9,900 with a $10,000 limit)	Avoiding review.

No single criterion is conclusive. The skill is to combine them: an entry that is manual, round, posted on a Sunday by an executive and described as “adjustment” deserves attention in a way that any one of those features does not. A simple scoring approach assigns one point to each criterion met and sorts by score and amount; see also Fraud Detection Analytics and ML Forensics Explainability for model-based ranking.

3 SQL recipes

Assume a table gl_lines(je_id, line_no, post_date, entry_date, period_end, account, debit, credit, descr, preparer, approver, source). Standard SQL (adapt date functions to your engine).

-- Control total: every journal must balance
SELECT je_id, SUM(debit) - SUM(credit) AS diff
FROM gl_lines GROUP BY je_id HAVING ROUND(SUM(debit) - SUM(credit), 2) <> 0;

-- Round-amount entries (divisible by 1,000, nonzero)
SELECT * FROM gl_lines
WHERE (debit > 0 AND MOD(debit, 1000) = 0) OR (credit > 0 AND MOD(credit, 1000) = 0);

-- Weekend postings (SQL Server style: Saturday=7, Sunday=1 under default DATEFIRST)
SELECT * FROM gl_lines WHERE DATEPART(weekday, post_date) IN (1, 7);

-- Entries posted after period end
SELECT * FROM gl_lines WHERE post_date > period_end;

-- Self-approved entries
SELECT * FROM gl_lines WHERE preparer = approver OR approver IS NULL;

-- Rare posters
SELECT preparer, COUNT(DISTINCT je_id) AS n FROM gl_lines
GROUP BY preparer HAVING COUNT(DISTINCT je_id) < 10;

4 Excel and Python recipes

Excel. With dates in column B and amounts in column C: round amounts, =IF(AND(C2<>0, MOD(ABS(C2),1000)=0),1,0); weekend, =IF(WEEKDAY(B2,2)>5,1,0); second digit of the amount, =MID(TEXT(ABS(C2),"0.00"),2,1) (use with care for amounts with a leading zero or decimal point; a more robust approach is to strip the decimal and read characters 1–2); duplicates, =COUNTIFS(D:D,D2,C:C,C2)>1. Sort by a composite score column and filter.

Python (pandas).

import pandas as pd
gl = pd.read_csv("gl_lines.csv", parse_dates=["post_date"])
gl["amt"] = gl["debit"].fillna(0) + gl["credit"].fillna(0)
gl["round"]   = (gl["amt"] % 1000 == 0) & (gl["amt"] > 0)
gl["weekend"] = gl["post_date"].dt.dayofweek >= 5
gl["selfappr"]= gl["preparer"] == gl["approver"]
gl["score"]   = gl[["round","weekend","selfappr"]].sum(axis=1)
flag = gl[gl["score"] >= 2].sort_values(["score","amt"], ascending=False)

Keep the script and the input file hash with the working papers so a second analyst can reproduce the result.

5 Duplicate and gap tests

Duplicates. Duplicate payments and duplicated journal lines are a common error and a common fraud (a paid-twice invoice where the second refund goes to the employee). Run three levels of test, from strict to loose:

• Exact: same vendor, invoice number and amount.
• Near: same vendor and amount, different invoice number or dates within 30 days; or same invoice number and different amount.
• Transposed or formatted variants: invoice numbers differing only by leading zeros, dashes or case (“INV-0045” versus “INV45”); amounts with transposed digits (for example, $4,580 versus $4,850, a difference divisible by 9).

SELECT vendor_id, invoice_no, amount, COUNT(*) AS n
FROM ap_payments GROUP BY vendor_id, invoice_no, amount HAVING COUNT(*) > 1;

Gaps. A sequence of document numbers, such as journal IDs or check numbers, should be continuous. In SQL, find the missing ranges with a window function:

SELECT je_id AS gap_after, next_id AS gap_before
FROM (SELECT je_id, LEAD(je_id) OVER (ORDER BY je_id) AS next_id
      FROM (SELECT DISTINCT je_id FROM gl_lines) t) s
WHERE next_id - je_id > 1;

Gaps are explained by voided entries, system-reserved numbers or skipped batches, but each needs an explanation from the preparer or the IT audit log. Conversely, a gap that falls inside a period end, or a sudden block of consecutive entries by one user, are leads.

6 Benford first- and second-digit tests

Benford’s Law gives the expected frequency of leading digits in many naturally occurring financial data sets (see Benford’s Law). The first-digit test reads the leading digit, expected frequencies running from 30.1% for digit 1 down to 4.6% for digit 9. The second-digit test uses the second digit, whose distribution is much flatter but still non-uniform:

Second digit	0	1	2	3	4	5	6	7	8	9
Expected %	11.97	11.39	10.88	10.43	10.03	9.67	9.34	9.04	8.76	8.50

The second-digit test is more sensitive to rounding and to numbers invented by people (who tend to favour 0 and 5) than the first-digit test, because a made-up number tends to have a plausible first digit but a patterned second.

Procedure.

1. Extract the field (for example, invoice amounts for one account) and drop zeros, negatives (or analyse separately) and amounts under a floor of, say, $10.
2. Count the observed frequency of each digit; compute the observed proportion p_o.
3. For each digit compute the z-statistic with the continuity correction:

   z = ( |p_o − p_e| − 1/(2n) ) / sqrt( p_e (1 − p_e) / n )

   A z above 1.96 is significant at 5%, and above 2.58 at 1% (two-sided).
4. Compute the overall chi-square (9 degrees of freedom for the second digit; critical value 16.92 at 5%) and the mean absolute deviation (MAD).
5. Drill into any digit that deviates. Do not stop at the chart.

Worked example. A population of n = 2,000 payments shows a second digit of 0 in 15.5% of cases, against an expected 11.97%.
z = (0.155 − 0.1197 − 0.00025) / sqrt(0.1197 × 0.8803 / 2000) = 0.03505 / 0.00726 ≈ 4.83, well above 2.58. The excess is about 71 payments (310 observed versus about 239 expected). Extracting the amounts whose second digit is 0 reveals what they share: for example, many payments between $1,000 and $1,099 clustering to one approver, where a $1,000 approval threshold is being targeted.

Nigrini’s MAD conformity bands for the first-digit test are about 0.006 or below for close conformity, 0.006 to 0.012 acceptable, 0.012 to 0.015 marginal, and above 0.015 nonconformity.^[3] Remember that with very large n almost any data set rejects the null; use MAD and the digit-specific z, and use judgment.

Limits. Benford tests are not appropriate for assigned numbers (check numbers, policy numbers), data with built-in minimums or maximums, small populations (n below about 300), or amounts set by price lists. A deviation shows where to look; it proves nothing about intent. See the literature review in Benford’s Law.

7 Sampling approaches

Journal-entry testing generates a large exception list and usually a sample is needed. There are three approaches:

• Targeted (judgmental) selection. Select all entries meeting the highest-risk combination. This is the primary approach in fraud work, since the point is to look at the suspicious items, not to estimate a rate. The results cannot be extrapolated.
• Monetary-unit sampling (MUS / PPS). Every dollar in the population has an equal chance of selection, so large entries are naturally more likely. Sampling interval = tolerable misstatement ÷ reliability factor. For example, population $50,000,000, tolerable misstatement $1,000,000, 95% confidence, expected zero errors (reliability factor 3.0): interval = $1,000,000 ÷ 3.0 = $333,333; sample size = $50,000,000 ÷ $333,333 = 150 items. Entries larger than the interval are selected with certainty.
• Stratified random sampling. Divide the population into strata (for example, by size, user or source) and sample each, so that a rare high-risk stratum is not diluted. Useful when you want a statistically valid estimate of an error rate by group.

For a forensic engagement a discovery sample (to find at least one instance of a fraud when it is thought to exist at a stated rate) is also used: with an expected occurrence rate of 1% and a desired 95% chance of seeing at least one item, the required sample is about 299 items (ln 0.05 ÷ ln 0.99). Note that a clean discovery sample reduces, but does not eliminate, the likelihood of fraud.

8 Following up flagged entries

1. Pull the support. For each flagged entry obtain the source document (invoice, contract, approval email), and the underlying business reason.
2. Trace both sides. Follow the debit and the credit to subledgers, bank statements (see Bank Statement and Cash Analysis) and later reversals.
3. Review the system audit trail. Login records, user-ID changes and edit logs show who actually keyed or changed an entry; shared or generic IDs are themselves a finding.
4. Interview the preparer and approver using open questions (see Forensic Interviewing and Statement Analysis). Legitimate explanations are common and should be recorded.
5. Quantify and conclude. Quantify confirmed misstatements by account and period and describe the evidence as confirmed, unexplained, or explained.

Pitfalls: analysing a sample of the data rather than the full population; ignoring reversed entries (a fraud that is booked and reversed leaves no net effect, but changes the interim numbers); testing only the ERP while top-side adjustments live in a spreadsheet; and reporting hit counts without a baseline (compare to prior years and peer entities).

See also: Benford’s Law, Financial Statement Fraud, Revenue and Earnings Manipulation Tests, Red Flags, Digital Forensics.

References

1. Public Company Accounting Oversight Board. AS 2401, Consideration of Fraud in a Financial Statement Audit. pcaobus.org
2. PCAOB. Audit Focus: Journal Entries (staff publication, January 2025). pcaobus.org
3. Nigrini, M. J. Benford’s Law: Applications for Forensic Accounting, Auditing, and Fraud Detection (Wiley), 2012.