Back office

How do we stop month end being days of copying figures by hand?

By Paul Meakin7 min read

Business person handling paper receipts, meticulously organized
Photo: SHVETS production on Pexels

What actually happens at month end?

In most small businesses it looks something like this. Someone downloads the bank statement. Someone else exports the sales invoices. The two get pasted side by side in a spreadsheet, and a person works down the list, matching payments to invoices by eye and highlighting the ones that do not fit.

The supplier bills go the same way, and so does the card spend. Finally the totals get typed into the accounts package, or into a summary sheet the accountant asked for, and somebody checks that the typed figures match the pasted ones.

None of this is difficult. That is the problem. It is careful, repetitive work, done by someone expensive, under a deadline, and the steps are almost identical every month. Nobody's hobby is reconciliation.

What does HMRC say about copying figures by hand?

More than most people expect, at least for VAT. HMRC's VAT notice on Making Tax Digital for VAT defines a digital link as a transfer or exchange of data made electronically between software programs. It goes on to say that a digital link does not need any manual intervention, such as copying information over by hand.

It is blunter still on the habit most month ends rely on. HMRC does not consider cut and paste or copy and paste to be a digital link. What it does accept includes linked cells in a spreadsheet, where a formula in one sheet mirrors a value in another, and CSV import and export, file download and upload, automated transfer and API transfer.

That rule sits in the VAT notice, so whether it applies to a particular set of figures is a question for your accountant, not for us. The principle travels well beyond VAT, though. HMRC is describing the same weakness that makes month end slow: every time a person carries a number from one place to another, it can change on the way.

The company side matters too. GOV.UK's guide to company and accounting records says a limited company must keep accounting records covering all money received and spent, and that you must keep records for 6 years from the end of the last company financial year they relate to. It also says you can be fined £3,000 by HMRC or disqualified as a company director if you do not keep accounting records. A month end built on pasted spreadsheets is still a record. It is just harder to check.

Which jobs repeat identically every month?

Most of them. Write the close down as a list, in order, and mark each step by what it actually involves. The pattern is usually obvious within a page.

Month end step Done by hand Done with links
Get the bank transactions Download, open, paste into a sheet CSV import into the same sheet every month, or the accounts package's own bank feed
Match payments to invoices Read down two lists and highlight A lookup formula finds the match and flags anything without one
Move totals into the accounts summary Type the figures across Linked cells or an import, so the summary reads the source
Check the totals agree Add them up twice and compare A check cell that turns red when two totals differ
Tell people it is done An email, if someone remembers A checklist sheet that records who finished each step and when

The left column is where the days go. The right column still needs a person, but only for the exceptions: the payment with no matching invoice, the invoice paid twice, the figure that does not add up. That is the part that needs judgement anyway.

How does matching work without someone ticking every line?

With a lookup, and a clear rule about what counts as a match.

In Google Sheets, the XLOOKUP function returns values from a result range based on where a match was found in a lookup range. Put the bank lines on one tab and the open invoices on another, and a formula against each bank line can pull back the invoice it belongs to, matched on the invoice number in the payment reference, or on the amount and customer joined into one combined key.

XLOOKUP also takes a value to return when nothing matches. Set that to something unmissable, such as UNMATCHED, and the person doing the close stops reading every line. They read the short list of lines that failed, and they fix those.

Getting the rule right is most of the work. Customers type references badly, pay several invoices in one go, or pay from a different company name. Those cases need deciding once, written down, and then built into the sheet, rather than rediscovered every month by whoever is doing the close.

Can the close run itself overnight?

The repetitive parts can. Google's guide to installable triggers in Apps Script says time driven triggers let scripts run at a particular time or on a recurring interval, as often as every minute or as rarely as once per month.

So a short script can run on the first morning of the month, pull in the latest export, run the matching, check the totals and fill in the checklist with what passed and what did not. By the time anyone opens the file, the routine work is finished and the exceptions are waiting at the top.

That is the kind of build we do as process automation, and in Google Workspace through our Apps Script work. If your monthly reporting has the same problem further down the line, we wrote about that separately in why the monthly report takes days.

Why do people get this wrong?

The usual month end mistake is a macro that pastes the bank download into last month's layout by column position. It works until the bank adds, drops or reorders a column in its export. Then the amounts land under the dates, nothing errors, and the reconciliation looks finished when it is wrong. Read the columns by their headings, and have the sheet stop if a heading it expects is missing.

The second is hiding the exceptions. If the sheet quietly drops lines it cannot match, the totals look clean and the problem turns up at year end instead, when the accountant asks about a payment nobody can explain.

The third is never writing the matching rules down, so each person doing the close makes their own calls on part payments and odd references.

When is this not the answer?

When your accounts package already does it. Many accounting packages come with bank feeds and matching rules of their own. Check whether yours does, and if nobody has switched them on, start there. It costs less than anything we would build.

When the books themselves are wrong. If the trouble at month end is that nobody is sure how a transaction should be treated, that is a question for an accountant. Automation makes a sound process faster. It does not make a wrong one right, and none of this is accounting or tax advice.

And when the volume is small. A business with a handful of transactions a month may be better off with a tidy checklist and an hour in the diary. Not every annoyance needs software.

Where do you start?

Next month, keep a tally as you reconcile. Count how many bank lines you matched by eye, and how many were genuine exceptions that needed a decision. The first number is the work a formula can take over. The second is where you want a person spending their time.

If the list is long, or nobody quite agrees on what the steps are, that is usually where we come in. Time to talk yet? Tell us what's stuck and we'll map the quickest fix.

Common questions

Do we need new accounting software to do this?

Usually not. Start with what your current package can import and export, and whether it already has bank feeds you are not using. A spreadsheet with linked data can cover the gaps.

Is copying and pasting figures actually a problem for HMRC?

For VAT records under Making Tax Digital, HMRC's VAT notice says it does not consider copy and paste to be a digital link. Whether that affects your own records is a question for your accountant.

What should happen to payments that do not match anything?

They should land on a short exceptions list that a named person works through before the close is signed off. Never let the sheet drop them quietly.

How long do we have to keep month end records?

GOV.UK says a limited company must keep records for 6 years from the end of the last company financial year they relate to, and longer in some cases it lists.

Sources

  1. VAT Notice 700/22: Making Tax Digital for VAT, GOV.UK
  2. Running a limited company: company and accounting records, GOV.UK
  3. Installable triggers, Apps Script, Google for Developers
  4. XLOOKUP function, Google Docs Editors Help

Paul Meakin, Founder

Twenty years of fixing businesses from the inside, eighteen of them in recruitment from consultant to national operations, before building the automation, web applications and compliance systems Staxxd runs today.

More about Paul

Time to talk yet?

Tell us what's stuck and we'll map the quickest fix. Fifteen minutes, no obligation.

Time to talk yet?