How to Check the Month-End Close With Copilot in Excel

A collection agency can't pay its clients until the trust account that holds their money ties and every remittance is right. Here is the check on one invented month: formulas decide, and Copilot or AI agents trace each break to its rows.

On this page
  1. What the Formulas Decide
  2. Every Break Traces Back to Its Rows
  3. The Big Swings Get Explained After the Breaks
  4. Send Us Last Month's Exports
  5. Frequently asked questions

Before a collection agency sends its clients their money for the month, someone proves two things in Excel. The trust account, the bank account that holds clients' money until the agency pays it out, holds what the agency owes.

And each client's remittance, what the agency pays that client for the month, is right: the month's collections less the agreed commission, after the payments that bounced and the ones debtors paid straight to the client.

We built an invented agency's October to show it: Tallis Creek Recovery, five clients, 512 payments, and six errors planted before the first formula ran.

The Checks sheet for the invented October: a trust difference of -660.90 split into -48.50 and 612.40, nothing unexplained, one misposted payment, two repeated rows, four remittances that differ, and NOT YET on the last line.
Every planted error shows up as a number, and the last line still says NOT YET.Screenshot by Qvery, captured in Google Sheets from the invented sample made for this tutorial.
6 of 6planted errors the workbook's formulas flagged
$660.90trust account difference, traced to a $48.50 bank fee and a $612.40 payment entered twice
Qvery: one invented month for an invented collection agency, built and computed in a spreadsheet, November 1, 2026

Those numbers are the workbook's, not an AI's: what an AI writes about your month changes with the model and the run.

By hand, someone in accounting lays the bank statement beside the trust ledger beside the posting report and works line by line against a deadline.

The errors are ordinary: a payment posted to the wrong client, a check keyed in twice, a bounced payment left in, an old commission rate. Each one overpays a client or shorts one, and a trust account holding less than the agency owes can break state trust-account rules.

Copilot in Excel can do that reading now, and so can AI agents. Neither should be the one that decides: in this check formulas decide whether the month ties, the AI traces and explains what they flag, and a person releases the money.

What the Formulas Decide

The trust check is a three-way tie, and the agents run it on every export: the bank's closing balance, plus the deposits the bank hasn't credited yet, less the checks it hasn't paid yet. That adjusted balance should equal your trust ledger.

The third side is what you owe. The adjusted bank balance has to cover the total due to clients, recomputed from the exports, and what is left over is your own commission still sitting in the account. On our month, $138,307.71 covers $94,854.95, so the account holds enough even before the corrections.

Five steps: bank 148,923.49; timing items adjust it to 138,307.71; ledger 138,968.61; a 48.50 fee and a 612.40 duplicate explain the gap, in red; 94,854.95 owed to clients is covered.
The timing items explain themselves next month; the two red items have to be fixed before the money goes out.Illustration by Qvery, drawn for this article.

The remittance check recomputes every client from the exports: collections by the client that placed the account, less returns and the commission at the contract rate, compared with the draft schedule.

Direct payments are the line people forget. When a debtor pays your client directly, your commission on it is still yours if your contract says so, and our sample takes it as an offset: subtracted from what the agency pays that client.

The Remittance check: five clients with collected, returned, direct payments, commission, net due, the draft's net and the difference; four rows read DIFFERS and Oxbow Valley Water reads ties.
Four drafts differ from the recomputed remittance; the one that ties is still wrong.Screenshot by Qvery, captured in Google Sheets from the invented sample made for this tutorial.

Every Break Traces Back to Its Rows

Our agents read the Checks sheet, the workbook's page of formulas, and trace every line that is not zero, "ties" or "yes" to the rows behind it, naming the sheet, the row and the amount, and they mark timing items, like a deposit the bank hasn't credited yet, apart from errors.

Three of the breaks are what formulas can't say. The misposted payment shows up on two clients' rows as two different numbers, and Brightvale's row shows one number for two errors pulling in opposite directions.

Oxbow ties because its remittance is recomputed from the same payment table that holds the duplicate, so only the trust check sees it.

Vlad ShvetsPartner @ Qvery

A remittance schedule that ties to itself proves the arithmetic, not the money: only the check against the bank saw a payment keyed twice. So the order of the close is the decision, and it is yours to set: the trust account ties to the bank first, every month, and no remittance goes out until it does. In our invented month, skipping that order would have overpaid clients $816.58 net.

Finding those connections across sheets is the reading the agents do. Where their answer and the Checks sheet disagree, a person goes to the rows: the sheet is right about its arithmetic, and Oxbow shows it can still be wrong about the data it was given.

Each finding is something a person fixes, somewhere other than this workbook:

Three of the planted breaks, what each costs, and the fix a person makes
The breakWhat it costsWho fixes it, and how
A $48.50 bank fee taken from the trust account$48.50 of clients' money paid out as a bank feeAccounting: operating pays the $48.50 back, and the bank charges operating from now on
$2,400.00 posted to the wrong clientQuillfield would be overpaid $1,560.00 and Brightvale underpaid $1,800.00Accounting: re-post it in the collection system, then export again
$612.40 entered twiceThe ledger claims money the bank never got, and Oxbow would be overpaid $410.31Accounting: confirm it from the check's image, then reverse it in the collection system

The agents we build for a collection agency pick up each posting report the day it's exported, so a payment on the wrong client or a check keyed twice surfaces that week. At month end they tie the trust account, recompute every remittance and draft the corrections.

Your team keeps its systems: the agents read the collection system and the trust bank's exports and write into the close workbook in OneDrive or SharePoint, on autopilot, alongside the accountant who prepares the schedule.

Piotr BabelPartner @ Qvery

Wiring this in is lighter than people expect, because the collection system itself is never touched. Where it breaks is the export: a renamed column or a new client code changes what the agents read without anyone noticing. So every run starts by matching the totals they read against the export's own totals, and when those differ the agents stop and say so, rather than trace breaks that are really a changed file.

Every line they write into the workbook is logged with the rows it came from. Whether Copilot or the agents do the reading, they only draft: a person releases each remittance and re-posts every payment.

Work out what the tie-out costs your agency now, and with the agents.

The calculator opens on an example: two accountants, each about $135,400 a year with benefits (the Bureau of Labor Statistics' May 2025 mean for accountants and auditors, plus 30%). We assume they spend about three days of each month on the tie-out and the remittance schedule, and that the agents take 75% of it, the tie-out, the recompute and the drafts, while the fixes and the release stay with people.

Month-End Trust Check ROI Calculator

What Tying Out The Trust Account And Checking Remittances Costs You, And What An Agent Saves

Every accountant who ties out the trust account or checks client remittances, counted as whole people.
Pay plus benefits per person. Accountants and auditors earned a mean $94,750 in May 2025 (BLS); benefits are 30% of private employer costs, so about $135,400.
How much of their time goes to the tie-out and the remittance schedule: 15% is about three days of each month. Raise it for more clients or a second trust account.
Reading each posting report as it is exported, tying the trust account to the bank, recomputing every remittance and drafting the corrections. Fixing entries in the collection system, confirming contract rates and releasing each remittance stay with your accountants.

Saved Per Year

$26,865

468 hours handed back, after $3,600 a year to run it

Hours Handed Back
468 hours a year
Agent Running Cost (Estimate)
$300 a month
Payback Per $10,000 Of Build
4.5 months

On these numbers, the agent saves $26,865 a year after its own running cost, and every $10,000 of the build is earned back in 4.5 months.

Example numbers. Type over them with yours.

On that example the job falls from $40,620 to $13,755 a year, the agents' running cost included (about $300 a month for model usage, hosting and upkeep). That is $26,865 a year and 468 hours back for collections and client work, and a build costing $10,000 pays for itself in about 4.5 months.

The tie-out is one close job among several. Our accounting playbook prices three more an agent takes on: customer payments waiting for invoice matches, group companies whose entries disagree and close plans chased by hand.

The Big Swings Get Explained After the Breaks

A rule written before anyone looks at the month decides which clients get explained: a swing over $5,000 and over 25% in our sample. The agents explain each flagged client from the notes, the placements and last month's totals, after the errors they have already traced, and say what is left.

Explain the swings after the breaks. "New accounts" is a true reason for Quillfield's rise, and asked first, it would have carried $2,400.00 of Brightvale's money with it.

Send Us Last Month's Exports

The build starts from a month your team has already closed and paid out: the agents run on that month first, beside what your accountants found. Two weeks after we have the exports and access, a first version ties your current month, and from then on your controller sees each month's tie-out hours and the errors caught before any remittance went out.

Frequently asked questions

Can a client's remittance tie and still be wrong?

Yes. In our sample, one client's remittance tied while it was overpaid $410.31, because it was recomputed from the same payment table that held a check entered twice. Only the trust account check, which compares the ledger with the bank, saw the duplicate.

Who should release the remittances?

A person, and not the one who prepared the schedule. Copilot or the agents trace and draft, the formulas on the Checks sheet decide whether the month ties, and people make every correction in the collection system and the bank.

Why does the trust account have to tie before remittances go out?

Because it holds clients' money until the agency pays it out, and an account holding less than the agency owes can break state trust-account rules. Tying it first also catches a payment entered twice, which a client's own remittance check can miss when it is recomputed from the same payment table.

What is a trust account three-way tie-out?

The bank's closing balance, plus deposits the bank hasn't credited yet, less checks it hasn't paid yet, should equal your trust ledger. The third side is what you owe: the adjusted balance has to cover the total due to clients.

How do you check a collection agency's client remittances?

Recompute every client from the exports: collections for the accounts that client placed, less returns and the commission at the contract rate, compared with the draft schedule. Formulas do the arithmetic, not the AI.

What errors happen in a collection agency's month-end?

Ordinary ones: a payment posted to the wrong client, a check keyed in twice, a bounced payment left in, an old commission rate. Each one overpays a client or shorts one.

What did the test month's trust account difference turn out to be?

$660.90, traced to a $48.50 bank fee and a $612.40 payment entered twice. The workbook's formulas flagged all 6 of the 6 planted errors; the numbers are the workbook's, not an AI's.

How much would the planted errors have cost?

Net, the draft would have paid $816.58 too much, three clients over and two under. The remittance check's own total said only $406.27, because the duplicate hides inside it.

How should direct payments to a client be handled?

When a debtor pays your client directly, your commission on it is still yours if your contract says so. Our sample takes it as an offset, subtracted from what the agency pays that client.

Why explain the big swings after the breaks?

Because a true reason can hide an error. In the sample, new accounts was a true reason for one client's rise, and explained first it would have carried $2,400.00 of another client's money with it.

Which client swings need an explanation?

The ones a rule written before anyone looks at the month flags: in the sample, a swing over $5,000 and over 25%. The agents explain each from the notes, the placements and last month's totals.

What systems do trust-account agents work with?

They read the collection system and the trust bank's exports and write into the close workbook in OneDrive or SharePoint. A person releases each remittance and re-posts every payment.

Did the test month's trust account hold enough to pay clients?

Yes. The adjusted bank balance of $138,307.71 covered the $94,854.95 due to clients, so the account held enough even before the corrections. What is left over is the agency's own commission still sitting in the account.

What do the agents do with each posting report?

They pick up each posting report the day it is exported, so a payment on the wrong client or a check keyed twice surfaces that week. At month end they tie the trust account, recompute every remittance and draft the corrections.

How do trust-account agents document each close discrepancy?

Findings name the sheet, row and amount behind each Checks sheet line that is not zero, ties or yes. Agents distinguish timing items, such as deposits awaiting bank credit, from errors that need correction.

Who fixes the errors the check finds?

Accounting fixes errors outside the workbook. Operating repays the $48.50 fee, and the bank charges operating thereafter. Accounting re-posts misposted payments in the collection system and exports again. Duplicates are confirmed against check images, then reversed in the collection system.

Is an AI's reading of the month repeatable?

Not exactly. What an AI writes about your month changes with the model and the run. So the formulas decide whether the month ties and the AI only traces and explains.

Keep Reading

How Accurate Is AI Bookkeeping?

A company of 250 people can spend 900 hours a year checking coded transactions, and most of the lines it checks were already right.

What AI Agents Take Off Your Month-End Close Checklist

A finance team of four spends about a quarter of its month on the close, $135,400 a year at its pay, and most of the delay is waiting on someone else.

Which Client Work Accounting Firms Should Hand to AI First

In tax season, preparers wait on files more than on skill: the W-2 that hasn't come, the 1099 that was corrected, the K-1 due in March.