Blog · Data quality and reconciliation · Finance and FP&A teams
Financial anomaly detection compares ledger amounts with prior periods and explicit review thresholds. Follow a worked expense review, spreadsheet formulas and a reconciliation that keeps unusual transactions visible until someone explains them.
Financial anomaly detection compares finance records with an explicit expectation and flags differences that need evidence. Start with comparable ledger periods, apply agreed thresholds, then inspect the underlying transactions. A flag identifies a review item; the accountant determines whether it needs correction or explanation.
Export posting date, entity, account, supplier, invoice reference, currency and signed expense amount. Retain a stable source record ID where available. Keep expense debits positive and expense credits negative throughout the review.
Use closed periods with the same account mapping. Separate entities and currencies before aggregating. Tie each period's export to the corresponding ledger control total, and record the export time so later postings do not silently change the comparison.
Prepare separate tests for different questions:
| Question | Comparison | Evidence to inspect |
|---|---|---|
| Did an expense move unusually? | Current account total against prior period | Transactions contributing to the change |
| Is an invoice repeated? | Matching entity, supplier, reference, currency and amount | Source record IDs and invoice document |
| Is a transaction unusually large? | Amount against comparable transactions | Contract, approval and business purpose |
These tests can overlap. Keep one review record per issue with several reasons, rather than counting every reason as another financial exposure.
Consider fictional US manufacturer Mesa Components. Its reviewer selects an absolute change of at least $5,000 and a change of at least 20% for this expense comparison. A zero prior balance instead triggers review when current activity has an absolute value of at least $5,000. These are illustrative review choices, not universal accounting thresholds.
Put this summary in columns A through F, starting in row 2. Percentages are rounded to one decimal place; calculations retain full precision.
| Account | August expense (USD) | September expense (USD) | Change (USD) | Change (%) | Review result |
|---|---|---|---|---|---|
| Travel | 12,000 | 18,000 | 6,000 | 50.0% | Review |
| Software | 24,000 | 25,200 | 1,200 | 5.0% | Below thresholds |
| Freight | 40,000 | 52,000 | 12,000 | 30.0% | Review |
| Repairs | 0 | 8,000 | 8,000 | Not defined | Review: new activity |
| Total | 76,000 | 103,200 | 27,200 | 35.8% |
In Google Sheets, enter these formulas and fill down through row 5:
D2 = C2-B2
E2 = IF(B2=0,"",D2/ABS(B2))
F2 = IF(B2=0,IF(ABS(C2)>=5000,"Review: new activity",""),IF(AND(ABS(D2)>=5000,ABS(E2)>=20%),"Review",""))
The labels before each equals sign identify destination cells; enter only the formula beginning with =. Leave the percentage blank when the baseline is zero. Calling repairs growth zero would conceal new activity, while calling it infinite adds no useful review information.
To prepare September totals from the ledger, use columns A through G in the order listed above. For an export already restricted to the selected entity and USD, the formula in C2 is:
=SUMIFS(Ledger!$G$2:$G$10001,Ledger!$C$2:$C$10001,$A2,Ledger!$A$2:$A$10001,">="&DATE(2026,9,1),Ledger!$A$2:$A$10001,"<"&DATE(2026,10,1))
Use actual date cells and extend the ranges to include the entire export. Change the date boundaries for August. Google's SUMIFS documentation describes summing amounts that meet multiple criteria.
For broader interpretation, the tests in what makes a change an insight help connect a material movement to a business explanation.
The filtered September detail for the flagged accounts is:
| Source ID | Account | Supplier | Invoice | Expense (USD) |
|---|---|---|---|---|
| T201 | Travel | Summit Travel | TR-201 | 9,000 |
| T202 | Travel | Summit Travel | TR-202 | 9,000 |
| F101 | Freight | Cascade Freight | FR-101 | 26,000 |
| F102 | Freight | Cascade Freight | FR-101 | 26,000 |
| R301 | Repairs | Valley Repair | RP-301 | 8,000 |
| Total | 78,000 |
All these rows have the same entity and USD currency. The freight entries have distinct source IDs but matching invoice references and amounts. Investigate them; do not assume the second row is wrong. The travel entries have equal amounts but different invoice references, which demonstrates why amount alone makes a weak duplicate rule.
On the complete ledger, this formula tests the stated matching fields:
=COUNTIFS($B$2:$B$10001,B2,$D$2:$D$10001,D2,$E$2:$E$10001,E2,$F$2:$F$10001,F2,$G$2:$G$10001,G2)>1
Google's COUNTIFS documentation explains counting rows against multiple criteria. Exclude missing invoice references from automatic matching and route them to a separate data-quality review. Installment bills and split allocations can legitimately share an invoice reference.
The NIST guidance on outlier detection distinguishes labeling potential outliers for investigation from establishing that they are erroneous. This workflow uses business thresholds, rather than claiming a statistical test or fraud probability. A valid one-time event may deserve the same contextual treatment as a one-off sales order.
The original movement is 103,200 minus 76,000, or $27,200. Flagged account changes contribute 6,000 + 12,000 + 8,000 = $26,000. Software contributes the remaining $1,200. Together they reconcile to $27,200; the flagged September balances separately reconcile to $78,000 of detail.
Suppose the accountant confirms one freight entry was posted twice and approves a $26,000 reversal. Revised September expense is 103,200 minus 26,000 = $77,200. Against August, the revised increase is $1,200, or 1.6%. The original export stays preserved, and the reversal explains the complete bridge to the revised result.
Do not call that reversal a saving. It corrects recorded expense. The travel increase and repairs activity still need explanations even though other movements offset them. Keep the wider financial KPI review separate from the exception list so a small net movement does not hide large opposing changes.
Covirage is not affiliated with Google.
Bring a ledger file or Google Sheet with comparable periods and your agreed thresholds; Covirage maps the columns, and its tools compute the comparisons and check the figures. The external AI model explains the results with every figure traced to its rows, giving your finance reviewer the evidence to assess each movement. Create a free Covirage account and bring a sample of your own data.
See Covirage for finance teams for the broader reporting context.
No. A flagged amount might be a valid annual renewal, a repair, a posting mistake or a repeated export row. The comparison identifies something to investigate. Establish the business reason and inspect supporting documents before deciding what action is appropriate.
Choose thresholds by account and review purpose, using prior periods to see how much work they create. Combine a dollar threshold with a percentage threshold, and specify separate treatment for zero baselines. Document who approved the thresholds and when they should be reviewed.
Keep the original export intact. Record the explanation and any approved adjustment separately, then reconcile the original amount to the revised view. A legitimate unusual transaction belongs in actual results even when a planning comparison also shows its effect separately.
Use a cadence that matches the decision and data availability. A monthly close review needs complete comparable periods, while an interim cash review may need more frequent exports. Label incomplete periods clearly so ordinary posting delays do not become unexplained exceptions.