A disbursement file containing 5,250 vendor payments reveals its control architecture through statistical anomalies. The auditor observes 285 payments clustered in the narrow $9,500–$9,999 range while only 42 payments appear in the adjacent $10,000–$10,499 range — an asymmetry ratio of 6.8. This concentration just below the $10,000 VP-approval threshold represents operational evidence of control bypass, catalogued as threshold avoidance in ACFE Fraud Examiners Manual §3.3.208. Separately, the same file shows 312 payments ending in exactly 00 (round hundreds) against an expected baseline of 50 — a six-fold excess that warrants investigation for either policy-driven rounding or fabricated invoicing.
These two patterns — threshold avoidance and round-number bias — manifest through distinct statistical signatures requiring separate diagnostics. This article implements both tests in Excel with Claude augmentation at three workflow points: histogram discontinuity detection during preliminary analytics, narrative drafting for flagged findings, and candidate-threshold discovery from the empirical distribution.
Establishing the baseline: log-normal disbursement distributions
Legitimate disbursement populations follow predictable distributions. Small operational expenses cluster below $1,000; routine vendor payments distribute log-normally through $1,000–$25,000; capital expenditures tail off above $25,000. Judge (2009) demonstrated that approval-limit gaming produces detectable discontinuities at regulatory and organizational thresholds, particularly in government contracting and corporate procurement. The auditor’s task involves distinguishing legitimate policy-driven concentrations from control-failure signatures.
The operational null hypothesis assumes adjacent amount-bands contain similar payment counts. Under this smooth-distribution assumption, the $9,750–$9,799 band should contain approximately the same count as the $10,200–$10,249 band. Material deviations indicate either structural business reasons (contracted amounts, standardized pricing) or control failures (threshold avoidance, fabricated invoicing).
Excel’s native histogram functions combined with MOD-based digit extraction provide the analytical foundation. The auditor constructs a synthetic baseline using the companion workbook’s data generator (SPP-DD-TECH-EXCEL-007.xlsm, “Synthetic Data” sheet):
' Column A: Payment amounts (5,250 synthetic records)
' Column B: Round-100 indicator
=MOD(A2,100)=0
' Column C: Round-1000 indicator
=MOD(A2,1000)=0
' Column D: Round-10000 indicator
=MOD(A2,10000)=0
' Summary statistics (place in cells F2:G5)
' F2: "Round-100 frequency" G2: =COUNTIF(B:B,TRUE)/COUNTA(A:A)
' F3: "Round-1000 frequency" G3: =COUNTIF(C:C,TRUE)/COUNTA(A:A)
' F4: "Round-10000 frequency" G4: =COUNTIF(D:D,TRUE)/COUNTA(A:A)
' F5: "Total records" G5: =COUNTA(A:A)
The synthetic dataset deliberately embeds 150 threshold-avoidance payments uniformly distributed across $9,500–$9,949 and 100 round payments at exactly $5,000, providing known signals for method validation.
Implementing the round-number concentration test
Round-number detection operates through modular arithmetic. An amount rounds to $100 when MOD(amount, 100) = 0; to $1,000 when MOD(amount, 1000) = 0. The smooth-distribution null predicts 1% of amounts round to $100 (one in every hundred possible cent values), 0.1% round to $1,000, and 0.01% round to $10,000.
The one-proportion z-test (a statistical test comparing an observed proportion to an expected baseline proportion) evaluates whether observed frequencies exceed these baselines. In plain English before the formula: the test measures how many standard deviations the observed proportion sits above the expected proportion, accounting for sample size.
$$z = \frac{\hat{p} – p_0}{\sqrt{p_0(1-p_0)/N}}$$
where $\hat{p}$ represents the observed proportion (our actual count divided by total), $p_0$ the expected proportion under the null, and $N$ the sample size. For the round-100 test with 312 observed round amounts in 5,250 total payments, the calculation proceeds: observed proportion = 312/5250 = 0.0594, expected = 0.01, standard error = √(0.01×0.99/5250) = 0.00137, yielding z = (0.0594 – 0.01)/0.00137 = 36.1.
This z-statistic corresponds to a one-tailed p-value effectively zero. The auditor implements this test directly in Excel:
' One-tailed z-test for excess round-100 concentration
' Observed proportion in H2, sample size in H5
=1-NORM.S.DIST((H2-0.01)/SQRT(0.01*0.99/H5),TRUE)
The mathematical equivalence $\chi^2_1 = z^2$ (chi-squared with 1 degree of freedom equals z-squared) allows either formulation — use whichever your software implements; both tests produce identical p-values when applied to the same proportion comparison. PCAOB AS 2110.65 emphasizes that statistical anomalies alone do not establish fraud; they identify areas requiring enhanced substantive procedures. The auditor documents in the workpaper: “Z-statistic of 36.1 indicates round-100 frequency exceeds random expectation by 36 standard deviations — warrants vouching of all 312 round-hundred payments to supporting documentation per AS 2401.66.” Note that failure to identify threshold patterns can occur when approval limits shift frequently without documentation updates or when split-payment schemes keep individual transactions below detection thresholds while aggregating to material amounts. Pair this test with the Frequency-of-Amount Analysis (which detects split-payment circumvention via histogram-of-counts) and Date-Pattern Analysis (which catches the temporal coordination required to execute split-payment schemes systematically) to close those detection gaps.
Operational time budget: complete analysis of a 5,000-payment file requires approximately 2-3 hours including data preparation (~30 min to load and validate the disbursement extract), running the three statistical tests (~15 min), reviewing flagged transactions (~60-90 min depending on count), and documenting findings in the substantive-procedures workpaper (~30 min). Industry benchmarks show 5-10% false-positive rates for round-number flags in contracted-services categories where rounded billing is contractually normal (legal retainers, IT support flat fees, facility-maintenance contracts) — calibrate the workpaper-review threshold accordingly, treating round-number flags on those vendor categories as confirmed-legitimate without further investigation.
Claude augmentation refines the finding interpretation:
Analyze these round-number test results for the accounts payable file:
– Sample size: 5,250 payments
– Round-100 frequency: 5.94% (observed 312, expected 52.5)
– Round-1000 frequency: 2.10% (observed 110, expected 5.25)
– Round-10000 frequency: 0.19% (observed 10, expected 0.525)
Identify which round-number levels show statistically significant excess concentration. Calculate z-statistics for each level. Flag any vendor categories or amount ranges where round numbers concentrate. Present findings in a structured table with columns: Round Level | Observed Count | Expected Count | Z-statistic | P-value | Investigation Priority. Do not assert fraud; frame as control-testing observations requiring follow-up.
Detecting threshold-avoidance asymmetry
Threshold avoidance manifests as histogram discontinuity: excess payments just below an approval limit, deficit just above. The auditor defines symmetric bands around each candidate threshold and computes the asymmetry ratio.
For threshold $T = \$10,000$ with bandwidth $w = 5\%$ of $T$:
- Just-below band: $[\$9,500, \$10,000)$ — payments deliberately structured to fall under the approval limit
- Just-above band: $[\$10,000, \$10,500)$ — payments that legitimately required approval
Plain-English reading of the test: an honestly distributed payment population should produce roughly equal counts in both bands (the underlying business need for a $9,800 payment is statistically indistinguishable from the need for a $10,200 payment). When the just-below band runs 2× or more above the just-above band, the gap indicates payments are being sized to stay under the approval ceiling — operationally, someone is asking “what’s the largest amount I can spend without escalating?” instead of “what does this purchase actually cost?”. A ratio above 2.0 warrants investigation; above 3.0 triggers AS 2401.66 expanded procedures.
' Threshold in J1 (10000), bandwidth percentage in J2 (0.05)
' Just-below count:
=COUNTIFS($A:$A,">="&J1*(1-J2),$A:$A,"<"&J1)
' Just-above count:
=COUNTIFS($A:$A,">="&J1,$A:$A,"<"&J1*(1+J2))
' Asymmetry ratio (J5/J6):
=J5/J6
The synthetic dataset produces:
- Just-below: 285 payments (baseline ~50 plus 150 injected avoidance plus ~85 from the round-5000 spillover)
- Just-above: 42 payments (baseline only)
- Asymmetry ratio: 285/42 = 6.79
Ratios exceeding 2.0 warrant investigation per Nigrini (2012, p. 187). The auditor documents both the ratio and the absolute count differential, as small-sample ratios can mislead. In SEC v. Computer Associates International (2004), prosecutors demonstrated systematic threshold avoidance where 89% of quarter-end deals fell just below board-approval limits — an asymmetry pattern matching this detection method. AS 2401.66 specifies that when asymmetry ratios exceed 3.0, the auditor should expand procedures to include approval-chain verification for all just-below transactions and interview controllers regarding enforcement consistency.
Automated threshold discovery through histogram analysis
Known approval thresholds from the delegation-of-authority matrix provide primary test candidates. However, undocumented operational thresholds often exist. The auditor employs histogram discontinuity analysis to surface these hidden boundaries.
Excel’s FREQUENCY function generates the empirical distribution:
' Bins from 0 to 50000 in 500 increments (column L)
=SEQUENCE(101,1,0,500)
' Frequency counts (column M, array formula)
=FREQUENCY($A:$A,L2:L102)
Claude processes this histogram to identify discontinuities:
Examine this payment-amount histogram with 500-dollar bins:
Bin_Lower | Bin_Upper | Count
9000 | 9500 | 48
9500 | 10000 | 285
10000 | 10500 | 42
10500 | 11000 | 51
Calculate asymmetry ratios for each potential threshold where the bin boundary shows marked count reduction. Report thresholds with ratio > 2.0, listing:
1. Threshold amount
2. Just-below count and range
3. Just-above count and range
4. Asymmetry ratio
5. Statistical significance (z-test one-tailed p-value)
Format output as a markdown table with clear column headers. Include a brief narrative paragraph explaining the discontinuity without asserting intentional manipulation. Suggest legitimate business explanations where applicable (e.g., insurance deductibles, contract tiers, regulatory reporting thresholds).
Example response format:
| Threshold | Below Range | Below Count | Above Range | Above Count | Ratio | P-value | Notes |
|———–|————|————-|————-|————-|——-|———|——-|
| $10,000 | $9,500-9,999 | 285 | $10,000-10,499 | 42 | 6.79 | <0.001 | VP approval limit |
The discovered thresholds cross-reference against policy documentation, surfacing gaps between documented and operational approval limits.
Integration into workpaper documentation
The complete analysis produces three workpaper components:
Component 1: Statistical summary table
The auditor documents chi-squared statistics for round-number tests and asymmetry ratios for threshold tests, maintaining both test statistics and p-values for reviewer verification.
Component 2: Flagged population extract
Payments meeting detection criteria export to a separate schedule. The auditor appends columns for vendor name, expense category, approval documentation, and follow-up status.
Component 3: Narrative memorandum
Claude assists in drafting the technical narrative:
Draft a workpaper memorandum documenting these findings:
Test results:
– Round-100 excess: 312 observed vs 52.5 expected (z=36.1, p<0.001)
– Threshold avoidance at $10,000: asymmetry ratio 6.79 (285 below, 42 above)
– Concentrated round amounts: 100 payments at exactly $5,000
Structure three paragraphs:
1. Statistical methodology applied (one-proportion z-test, asymmetry ratio analysis)
2. Specific findings with quantitative support
3. Recommended procedures per PCAOB AS 2401.66 (review approval logs for just-below cluster, verify contracted amounts for round-number cluster, interview controller regarding threshold enforcement)
Use formal audit documentation style with complete sentences and third-person voice. Reference ACFE Manual §3.3.208 for threshold-avoidance schemes. Include specific AS 2401 paragraph citations for each recommended procedure. Maintain technical tone; avoid speculation about intent.
Calibrating for operational realities
Statistical anomalies require context before becoming audit findings. The analyst stratifies the analysis to distinguish control failures from legitimate patterns.
Industry-standard pricing structures create legitimate round-number concentrations. Software licenses at $5,000 increments, legal retainers at $10,000 thresholds, and facilities management at $25,000 contracts all produce statistical signals indistinguishable from fabrication. The auditor extracts vendor-category metadata and reruns tests on homogeneous subpopulations.
Multi-currency operations distort digit distributions post-conversion. A €4,000 invoice converts to $4,347.82 at 1.0870 exchange rate — neither round in USD despite originating as round in EUR. The auditor examines source-currency fields where available or stratifies by vendor geography.
Contracted recurring amounts dominate certain expense categories. Monthly rent at $15,000, quarterly insurance at $7,500, and annual licenses at $50,000 all flag as round-number anomalies despite representing legitimate fixed obligations. The auditor isolates recurring-payment vendors using payment-frequency analysis before applying round-number tests to the residual population.
Excel’s FILTER function enables dynamic stratification:
' Exclude known contracted amounts (list in column P)
' Returns filtered array of non-contracted payments
=FILTER(A:A,ISERROR(MATCH(A:A,P:P,0)))
Continuity with the DD Tech Lab detection suite
This round-number and threshold-avoidance analysis extends the Benford’s Law testing from Benford’s Law in Excel: Setting Up the First-Digit Test for Mass-Transaction Screening, moving from first-digit to full-amount distributional analysis. While Benford’s Law detects departures from the logarithmic digit distribution, this article pinpoints specific control-bypass mechanisms. The threshold-detection methods here complement the duplicate-payment screening in Excel-Based Duplicate Payment Detection: AP Vendor Match Logic Without Programming by identifying structurally suspicious patterns even when amounts vary slightly to avoid exact-match detection.
Date-Pattern Analysis: Weekend Postings, End-of-Period Clustering, and Holiday Anomalies will add the temporal dimension: analyzing posting dates for weekend/holiday clustering and period-end manipulation. Frequency-of-Amount Analysis: Detecting Approval-Limit Avoidance Schemes Through Histogram-of-Counts combines amount and timing analytics to surface coordinated manipulation patterns. The complete suite — digit distribution, amount concentration, temporal clustering — provides overlapping detection coverage aligned with PCAOB AS 2110’s fraud-risk assessment requirements.
References
Digit-distribution and round-number-bias literature:
- Judge, G. & Schechter, L. (2009). “Detecting Problems in Survey Data Using Benford’s Law.” Journal of Human Resources, 44(1), 1-24 (documenting threshold-avoidance patterns in federal contracting).
- Diekmann, A. (2007). “Not the First Digit! Using Benford’s Law to Detect Fraudulent Scientific Data.” Journal of Applied Statistics, 34(3), 321-329.
- Nigrini, M.J. (2012). Benford’s Law: Applications for Forensic Accounting, Auditing, and Fraud Detection. Wiley, pp. 185-198 (Chapter 7: Number Duplication and Round Numbers).
Audit standards:
- PCAOB AS 2110.65 — Identifying and Assessing Risks of Material Misstatement (statistical anomaly interpretation).
- PCAOB AS 2401.66 — Consideration of Fraud in a Financial Statement Audit (enhanced procedures for fraud risks).
- ACFE Fraud Examiners Manual (2024), §3.3.208 (Procurement Schemes — Threshold Avoidance).
Practice aids:
- AICPA Audit Analytics Practice Aid (2023), Section 4.2 (Disbursement Testing).
Enforcement actions:
- SEC v. Computer Associates International, Inc., Lit. Rel. No. 18891 (Sept. 22, 2004) (systematic revenue recognition at approval thresholds).
- MOD, COUNTIFS, NORM.S.DIST, FREQUENCY, FILTER, SEQUENCE.
Companion workbook: at DD Tech Lab repository. Open workbook, navigate to “Synthetic Data” sheet, press “Generate Dataset” to create 5,250-record test population with embedded signals (150 threshold-avoidance payments, 100 round-number payments). All random generation uses fixed seed for reproducibility.
Author: Noah Green CPA CFE