Proof of Cash Reconciliation: Convert Bank Statements to Excel for Quality of Earnings and Financial Due Diligence

Proof of cash versus a bank reconciliation

They sound similar and test very different things. A monthly bank reconciliation proves one number: that the ending book balance agrees with the ending bank balance after timing items. A proof of cash proves the flow: that everything which came in was recorded as revenue or correctly identified as something else, and that everything which went out was recorded as an expense or correctly identified as something else. A set of books can reconcile perfectly every month and still be missing a whole revenue stream, because the unrecorded deposits and the unrecorded receivable cancel each other in the balance.

How to do a proof of cash reconciliation

Run it per bank account, per month, then roll the months up. Working monthly rather than annually is what makes a variance traceable to a specific transaction.

1. Convert every statement. Turn each month into rows with date, description, amount, and running balance, then confirm each month opens where the prior closed so you know the set is complete.
2. Total receipts and disbursements by month. A pivot on the converted data gives total deposits and total withdrawals per account per month.
3. Strip out what is not revenue. Remove owner contributions, loan and line of credit draws, transfers between the company own accounts, refunds received, and insurance proceeds. Whatever remains is cash from customers.
4. Strip out what is not expense. Remove owner draws and distributions, loan principal repayments, transfers out, and asset purchases. Whatever remains is cash operating spend.
5. Compare to the books. Put cash from customers next to recorded revenue, and cash operating spend next to recorded expense, month by month. Adjust for the movement in accounts receivable, accounts payable, and deferred revenue.
6. Investigate the variances. Diligence practitioners commonly treat a residual variance above roughly two percent on revenue or five percent on expenses as worth explaining. Chase each one to a specific transaction rather than accepting a rounding narrative.

Steps two through six are spreadsheet work. Step one is the part that stalls, and it is what BankXLSX removes. Grouping the disbursement side by vendor type is fastest with the transaction categorization tool, and the running balance extraction is the control that proves the converted period is complete.

Why bank statements beat seller prepared financials

Internal financial statements are produced by the party with the strongest interest in the number. That is not an accusation, it is a structural fact, and it is why every serious diligence process pushes back to source documents. A bank statement is produced by a third party with no stake in the sale price, it cannot be edited after the fact without leaving evidence, and it captures every dollar that moved regardless of how the bookkeeper coded it. For an owner-operated business with unaudited books, the bank record is frequently the only independent evidence in the data room. That is the entire logic of the proof of cash: start from the document nobody in the transaction controls, and work back toward the one they do.

What a proof of cash actually finds

The findings cluster into a short list. Revenue recorded that never arrived, usually from optimistic accrual entries or from a related party sale that was booked but never paid. Revenue that arrived but was never recorded, which matters as much to a buyer because it changes what the business is worth and raises a tax question. Personal spending run through the business, which is normal in owner-operated companies and belongs in the add-back schedule with documentation, not in a verbal claim. Loan draws recorded as income, which inflates revenue and is one of the more common errors in informal books. Merchant processing recorded gross while the bank shows net of fees, which understates cost of sales. Timing games near the measurement period, where invoices are pulled forward or expenses pushed back to lift trailing twelve month earnings. None of these is automatically a deal breaker, but a buyer who finds them before closing prices them, and a buyer who finds them after closing pays for them.

How far back should you go?

Twelve months is the floor, because valuation usually rests on trailing twelve month earnings and that period has to be tested. Common practice is three full historical years plus the trailing twelve months, which lets you see whether the business is genuinely growing, whether seasonality is real, and whether the most recent year is an outlier assembled for the sale. If the business is seasonal, anything less than a full year is meaningless. If the seller can only produce a partial set, that is itself a finding: an inability to complete the cash testing because records are missing or inconsistent raises the risk profile of the transaction, and it is a reasonable basis to slow the process down rather than push through it.

Building the exhibit in Excel

A workable proof of cash schedule is one tab per bank account plus a summary. Each account tab holds four blocks: converted bank rows, a monthly pivot of receipts and disbursements, a reclassification block listing every non-revenue receipt and non-expense payment with its reason, and a comparison block putting adjusted cash against recorded revenue and expense with a variance column and a percentage. The summary tab consolidates across accounts and carries the variance explanations. Keep the raw converted rows in the workbook rather than deleting them, because the first question any reviewer asks is where a number came from, and being able to click through from a variance to the underlying transaction is what makes the exhibit credible. The pivoting mechanics are covered in how to analyze bank statements in Excel, and if the goal is a cash flow view of the target, the bank statement to cash flow statement page walks through that build.

Where a converter fits, and where it does not

BankXLSX prepares the bank side. It does not perform diligence, it does not issue a quality of earnings report, and it is not a substitute for a CPA or diligence provider engaged on the transaction. What it removes is the transcription: instead of a week of keying statements, you get accurate, source-traceable spreadsheets of the target activity in minutes, across every account and every year in the data room. Lenders underwriting the acquisition will find the borrower side workflow on the lender converter page, examiners testing for concealment should read the forensic accounting analysis page, firms doing this at volume can automate intake through the conversion API, and the general bank statement converter reads statements from more than 90 US banks so a target banking at three institutions still produces one consistent workbook.