Forensic Accounting in Excel: Tests You Can Build

Forensic accounting in Excel starts with one clean table of transactions, each row tied to its source page. Pivot tables then surface new payees and spikes, COUNTIFS flags duplicate payments, a sorted difference finds check gaps, and a largest-to-second-largest ratio finds outliers. On big or court-bound matters, document every step or move to a tool.

Building a forensic transaction table in Excel

A forensic transaction table in Excel holds one row per bank transaction, with the same columns for every account and period, and a reference on each row back to the statement page it came from. Every later test runs on this table, so its design decides whether the results can be checked by someone else.

Use Excel's Format as Table feature so formulas use column names and new rows pick up the formulas automatically. Keep the raw table on its own sheet and never type over it. Tests go in added columns or on separate sheets.

Columns to include

The layout below is an example. The values in the last column are invented.

Two columns do most of the forensic work. PayeeClean lets "RIDGE FREIGHT LLC," "Ridge Freight" and "RIDGE FRT" group together, which matters for every per-payee test. Source is what lets you, or opposing counsel, go from any number in your report to the page that supports it.

Proving the table is complete

Before testing anything, prove that the table holds every line. For each statement, total the credits and debits in the table and compare them with the statement's own totals, and check that opening balance plus net activity equals the closing balance. A table that misses one page will give clean results on data that is not all there.

Pivot tests by payee, month and amount band

Pivot tests summarize the transaction table so unusual patterns stand out: a payee that appears for the first time, a month with twice the usual spending, or a cluster of payments just under an approval limit. Save each pivot on its own sheet with a note of the date range and filters.

Three pivots worth building every time:

1. Payee by year. Rows are PayeeClean, columns are years, values are the sum and count of Amount. New payees, payees that stopped and payees that grew sharply all show up.
2. Month by type. Rows are months, columns are Type. A spike in wires or cash withdrawals in one quarter is the kind of change a tip often describes.
3. Amount bands. Add a band column, such as =FLOOR(ABS([@Amount]),500), and pivot the count by band. A pile of payments in the 4,500 band, just under a 5,000 approval limit, is a classic sign of splitting.

Pivots answer "where," not "why." When a pivot shows something odd, filter the table to those rows and read the source documents. A structured way to move from an allegation to the tests that answer it is in how to investigate a fraud tip.

Duplicate payment formulas with COUNTIFS

Duplicate payment formulas flag rows where the same payee received the same amount more than once, either exactly or within a short date window. Excel's COUNTIFS function, documented by Microsoft, counts the rows where every criterion is met, which is exactly what a duplicate test needs.

An exact duplicate flag, entered in a new column of the table:

=COUNTIFS([PayeeClean],[@PayeeClean],[Amount],[@Amount])>1

A near duplicate within seven days either side:

=COUNTIFS([PayeeClean],[@PayeeClean],[Amount],[@Amount],[Date],">="&([@Date]-7),[Date],"<="&([@Date]+7))>1

Here is how the second formula reads on four example rows. The data is invented.

Rows 000210 and 000214 pay the same vendor the same amount four days apart, so both flag. Row 000288 is the same amount about six weeks later, which looks like a regular monthly charge and does not flag. Every TRUE row still needs an invoice check: a supplier may genuinely bill the same amount twice in a week.

Duplicates the formula misses

The COUNTIFS test only finds what matches exactly on the columns you give it. Three kinds of duplicate slip past:

• Different spellings. If PayeeClean was not normalized, "Atlas Tools" and "ATLAS TOOLS INC" count as two payees. Fix the names before you trust a clean result.
• Different channels. The same invoice paid once by check and once by ACH lands under two descriptions. Add a test that ignores Type and matches on amount and a 30-day window, then review by hand.
• Small changes in amount. A second payment of 1,316.05 instead of 1,316.50 is a transposition the formula treats as unique. Rounding both to whole dollars in a helper column, then rerunning the count, catches many of these.

Each extra test adds rows to review, so record which ones you ran and how many rows each returned. A reviewer reading the workbook later should see the reasoning, not just the flags.

How do you find check number gaps with an Excel formula?

You find check number gaps by sorting the checks by number and subtracting each number from the one after it. A difference of 1 is normal. A difference above 1 means numbers are missing in between, and each missing number is either outstanding, voided, destroyed or used somewhere it should not have been.

Filter the table to checks only, copy the account and check number columns to a working sheet, and sort by account and then by number. With check numbers in column C starting at row 2, enter in D3 and fill down:

=IF(C3-C2>1,"missing "&(C3-C2-1),"")

Each result names how many numbers are missing after the row above. A gap of one or two that matches the voided-check log is routine. A gap that matches nothing, or a run of numbers far outside the range in use, needs a look at the physical stock. How stock should be logged so that gaps can be explained is covered in check stock security.

Reverse the test too: duplicate check numbers within one account, found with =COUNTIFS([Account],[@Account],[CheckNo],[@CheckNo])>1, usually mean a printing error or two checks written on the same number.

Which payment outlier tests can Excel run?

Excel can run the main payment outlier tests with ordinary formulas: the relative size factor for each payee, round-number flags, weekend and holiday dates, and amounts just below an approval limit. Each test gives a short list of rows to read, rather than proving anything on its own.

Relative size factor

The relative size factor divides each payee's largest payment by its second-largest. A ratio of 1.2 means the largest payment is close to normal for that payee. A ratio of 8 means one payment stands far above the rest. Microsoft documents the LARGE function as returning the k-th largest value in a data set, which gives both figures. On a payee summary sheet, with payee names in column A and payments stored as positive numbers:

=LARGE(IF(Txn[PayeeClean]=A2,ABS(Txn[Amount])),1)/LARGE(IF(Txn[PayeeClean]=A2,ABS(Txn[Amount])),2)

The same page notes that LARGE returns #NUM! when k is larger than the number of values, so a payee paid only once returns an error. Wrap the formula in IFERROR and treat single-payment payees as their own list. Reading the ratio, and choosing a cut-off, is covered in the relative size factor test.

Other quick flags

• Round amounts: =MOD(ABS([@Amount]),100)=0 flags whole hundreds.
• Weekend dates: =WEEKDAY([@Date],2)>5 flags Saturday and Sunday postings, which on some accounts are normal and on others never happen.
• Just under a limit: =AND(ABS([@Amount])>=4500,ABS([@Amount])<5000) for a 5,000 approval limit.

Where a forensic Excel workbook stops being defensible

A forensic Excel workbook stops being defensible when nobody can show how each number was produced or that the data underneath it is complete. Excel itself is not the problem. The risks are manual data entry, formulas changed without a record, and files too large to check by hand.

Signs that you have reached that point:

• The data was typed. Every hand-keyed amount is a possible error, and a single transposed digit can create or hide a finding.
• Nobody can say which version is final. Several copies of the workbook, each with different filters, invite the question of which one the report used.
• Formulas vary down a column. A formula overwritten in one row can change a total silently. Excel tables reduce this, but do not prevent it.
• The workbook covers many accounts and years. Hundreds of thousands of rows make the file slow and hard to review.
• The matter is going to court. An expert will be asked how each figure was derived. A written procedure, saved copies at each stage and a source on every row become requirements, not good practice.

Keep a log sheet in the workbook that records each step: date, what was done, which sheet, and by whom. Save a read-only copy at each milestone. Compare your totals with the statements again before the report goes out.

For the data-entry risk, PreciseBankFlow extracts statement lines with a confidence score on each field, checks that every statement foots, and exports the lines to Excel, so the table you test starts complete and the uncertain fields are marked for you to vouch. The analysis stays in your workbook. When the matter needs more than a spreadsheet, what forensic accounting software should prove sets out what to ask of a tool, and you can join the early group to try ours.