Bank Rec Issues!!! How to fix?

Bank Rec Issues

Didn't find your answer?

Hi All

I'm working with a business where the previous accountants didn't maintain the accounts package (Xero) - as such it has resulted in over 3,000 unreconciled bank items, spanning different accounting periods. They filed the previous years accounts (first year) by getting the business owner to categorise each item in a bank statement and then they did something to get a set of statements, however I don't think they are going to be accurate.

I am now going through the bank rec (since the dawn of time), I can categorise the outgoings fine enough by going through with the business owner to categorise them all. The issue I have is there is an app the business uses for it's invoices and customer management which automatically records invoices and payments in Xero - the problem is there will be several invoices which match to a single bank transfer. I don't really have a clear way of seeing which invoices match to which payments. There is around 1,500 bank items which need matching to a massive amount of invoices/ payments and really is pretty much un-doable. It's easy to tell that it is revenue but this app pulls the invoice and payments through into Xero so it already accounts for them but then matching it to the cash is very difficult.

Any advise appreciated!!

Replies (30)

Please login or register to join the discussion.

RLI
By lionofludesch
11th Jan 2020 14:25

I'd think about starting again from the last year end.

Thanks (0)
avatar
By JDBENJAMIN
11th Jan 2020 15:14

In your shoes I would advise them to ditch Xero altogether, then redo everything from scratch with a better accounting system such as VTT+. Then, when the first two years are complete and done as they should have been to begin with, use the second year to correct everything that's wrong with the first. If they refuse to do this, I would just drop the client.

Thanks (0)
avatar
By BIG123
11th Jan 2020 15:17

Do you have access to VT Transaction+? My suggestion would be to obtain a .csv download from the bank, bring into VT and start from scratch. Post it all back into Xero once complete.

Thanks (0)
Replying to BIG123:
avatar
By DannnyD
12th Jan 2020 07:39

No I don't have VT Transaction+ and haven't used it before.

Thanks (0)
RLI
By lionofludesch
11th Jan 2020 17:32

What are the client's expectations as regards fees ?

Has he been prepared for the worst ?

Thanks (0)
Replying to lionofludesch:
avatar
By DannnyD
12th Jan 2020 07:36

I'm not part of a pratice, I work for a company that gives them all different types of support. So fees aren't an issue as it won't be billed like that.

Thanks (0)
By Moonbeam
12th Jan 2020 07:46

As it's just the sales side of things you have the issue with, could you ask for access to the app, in the hope that you could pull off reports from there to Excel and match up invoices/receipts that way?

Thanks (0)
RLI
By lionofludesch
12th Jan 2020 10:09

Hmmm - you're not really leaving much option but to slog through those 3000 transactions.

Thanks (1)
avatar
By Sandnickel
12th Jan 2020 16:07

I'm curious why you think the previous accounts are wrong, have you seen them? From your description it would seem the previous accountants did a manual analysis of the bank statements. If this is balanced using the descriptions given by the client arent you just doing the same?

Cant you take the opening balances and get the bookkeeping correct going forward?

Thanks (0)
avatar
By Manchester_man
13th Jan 2020 01:52

As above.

Also, you say that the app automatically records invoices and payments in Xero, so which bank account in Xero does it record the payments going into? There must be a bank account in Xero where all the debits have been posted ‘automatically’. Do these debits equal the unreconciled bank receipts?

Thanks (0)
By Charlie Carne
16th Jan 2020 11:29

There is no need to manually reconcile all 3,000 bank transactions. You only need to reconcile the difference (if any) between the balance at the bank and that in the accounts as at the last year-end that the old accountants processed. Unless it is a business that regularly writes cheques, there are likely to be very few (if any) unreconciled items at the year-end. Ignore what Xero says is unreconciled and just look at the actual numerical difference (if, indeed, there is a difference) and work out what comprises that figure. You could also look at bank items in the week or two around the year-end date to see which were included in the previous bank balance and thus reconstruct the bank rec.

Did the previous accountant not provide balance sheet schedules as at the last year-end that they processed, showing which unpaid invoices comprised the closing debtor and creditor balances (as well as a bank rec)? If not, then ask them for schedules - they have a responsibility to provide them.

Unless you believe that the last accounts prepared by the accountants were materially incorrect, then you can work from the closing balances. Don't worry about how they got to those balances; just start from the analysis of those closing balances. This will drastically reduce the amount of work for you to do.

Thanks (1)
avatar
By ShayaG
16th Jan 2020 14:27

Establish correct present debtors and creditors position and write everything else off to a suspense account.

Thanks (0)
Replying to ShayaG:
By petersaxton
31st Jul 2021 10:23

Suspense account!?
That's not solving the problem.

Thanks (0)
avatar
By AK Accountancy
25th Jan 2020 17:47

I'm afraid you've hit a terminology problem in some of the answers. Xero automatically downloads bank transactions from the live bank feed, meaning the balance on Xero is always reconciled to the balance at the bank.
Very (VERY) unhelpfully Xero calls the 'posting' or allocation of bank transactions to the relevant ledger accounts 'reconciling. So when you see 3000 + unreconciled transactions it means the payment has been credited to the bank account within Xero but is awaiting the debit - and vice versa.
So you will need to reconcile historic transactions - but annotated bank statements will help.
Xero does enable you to set up rules for posting costs which aren't invoiced - bank charges etc.
Xero does permit you to 'split' payments/ receipts over several invoices.
The matching App will have set up an account within Xero to 'hold' the payments of invoices - you will need to reconcile the amounts received per the actual bank account into this account and then match them off against the App posting. Doing this makes more sense than describing doing it!

Thanks (0)
avatar
By duncanphilpstate
04th Jul 2021 20:19

I know this is now an old topic but I just wanted to share that I've inherited a client with almost exactly the same issues. The original poster has my sympathy.

As some replies suggested, I'm seriously considering exporting to VT Transaction, sorting out the mess and then reinserting into Xero. But that does seem to be a waste if Xero would do as it's told.

Xero does have its own way of doing things, sometimes not what you'd expect if you're an accountant. ;-)

My issue at present, which brought me here, is trying to get its automated reconciliation/posting "bank rules" to copy the description from the bank statement into the posted transaction. Something which I'd assume would be trivial but which searches of Xero forums suggest has been a request accountants have been pleading for since 2013! Any ideas on that would be welcome.

Thanks (0)
Replying to duncanphilpstate:
avatar
By New To Accountancy
04th Jul 2021 21:33

I'm glad you responded to this thread as I'd never have seen it and the answers are extremely helpful. I'm dealing with a bit of a mess at the moment too so this is very helpful. Thank you

Thanks (1)
Replying to New To Accountancy:
avatar
By Paul Crowley
05th Jul 2021 12:40

I deal with badly used software quite often
if it is a small job I just start again from bank transactions.
It is not often the software, usually the software operator.
Always consider whether the software operator will ever be able to get it right and consider alternatives
I start by recommending that paper statements exist and are annotated in a useful way
Annotated, not highlighted with 4 versions of red (not made up, she did use four versions of red, no blue and no green)

Thanks (1)
Replying to Paul Crowley:
avatar
By New To Accountancy
05th Jul 2021 14:59

Thank you for that Paul. Very helpful advice from you as always.

Thanks (0)
Replying to New To Accountancy:
avatar
By Paul Crowley
05th Jul 2021 15:26

Thanks
I try to dissuade journals from the less able
Just enter bank transactions. I can work with that
Clients journals are always suspect and I can do the journals quicker than I can check the clients version

Thanks (1)
Replying to Paul Crowley:
avatar
By New To Accountancy
05th Jul 2021 17:13

Yes, understood and agreed. Thank you once again.

Thanks (0)
Replying to Paul Crowley:
avatar
By duncanphilpstate
06th Jul 2021 10:34

Off topic, but the red ink reminded me: While still training I did an accounts reconstruction job where the previous firm's working papers had been photocopied by the partner and all the negative numbers which had been in red ink were now in the same black as the positives. This was long before colour copiers or indeed PCs. And I'm deliberately not referring to debits and credits because it was every type schedule he'd copied. We couldn't go back and refer as the client was suing the previous auditors and relations were not rosy.

The team got very good at guesswork and judging what we might now call 50 shades of grey.

Thanks (0)
Replying to duncanphilpstate:
avatar
By Paul Crowley
06th Jul 2021 12:00

Going back again
The senior over me was colour blind so red was seen by him as grey.
Our team did not use red as meaning negative
I expect my team now never to use red as negatives on spreadsheets
-number
and never accounting always number as the - shoots all the way over to left on accounting

Thanks (0)
Replying to Paul Crowley:
avatar
By duncanphilpstate
08th Jul 2021 09:12

Nor do I like "-" as a negative indicator: too easily to get confused with a mark on the copy. Nice clear brackets, pointy if necessary "<999>" and written clearly if by hand.

The old clerks had something when they used different columns for debit and credit.

Thanks (0)
Replying to Paul Crowley:
avatar
By duncanphilpstate
06th Jul 2021 10:36

I agree re software operator. Certainly true in this case as the mess was caused by complete incompetence from someone who claimed to be a Xero expert when taken on. Yet who manifestly was not and probably knew very little about bookkeeping using any method. Each day brings me a new revelation.

Thanks (0)
Replying to duncanphilpstate:
avatar
By duncanphilpstate
06th Jul 2021 10:49

I shall now respond to my own question, as I've since discovered the / an answer although I don't like it.

I've worked out that the Xero "Bank Rules" feature and the closely associated "Cash Coding" aren't intended for bulk posting of *distinct* transactions but for the repeated, so-closely-similar-they-don't-warrant-a-description type of items. Thus, "electricity" if paid by direct debit. Or "rent". Or perhaps "Cash takings" where the only useful additional identifier would be the date which *is* present.

But several hundred Amazon transactions where it would be helpful to have the descriptions carried across from the bank statements so that after reconciling the bank account you can go back and analyse out from "Amazon expenses" is not possible. I think that's a shame but I can see that it is part of the Xero philosophy and they aren't going to change it. They (I think) come from a position where the user can identify and post the bank transaction to the bank account and the expense account at the same moment. Which may well be the case if you have the books under control or are a small-enough business owner who knows every transaction intimately. But for advisers doing bulk rectification it's not a good option. A lot of leg work needed, which is why exporting to VT sounds like a sensible way to go, despite everything. Before that, I may see if I can get at the bank feed as a CSV, massage it a bit and re-import into the account transactions side of the show. That would then do the job as a historic one-off at least.

As a final thought for the moment, it is possible to write a rule that puts the Description from the bank statement into the Reference field. But I'm reluctant as I have a gut feeling that "Reference" is expected to be much shorter in length than the bank statement description and might lead to more difficulty down the line.

Thanks (1)
Replying to duncanphilpstate:
avatar
By Hugo Fair
06th Jul 2021 11:13

I certainly wouldn't bother with putting "Description from the bank statement into the Reference field" ... but for a different reason.

The value in the Statement's 'description' is arbitrary (or to be precise whatever the payer has asked to be put there). So, although some people put useful things (like the invoice number), I've seen many that are entirely from the perspective of the payer ... with something like 'gardening' (which is not enlightening if your client provides gardening services)!

Thanks (1)
Replying to Hugo Fair:
avatar
By duncanphilpstate
08th Jul 2021 09:09

Oh, so true and one of my client's many problems. NHS Trusts seem to be particularly bad. For instance Devon Partnership (NHS Trust) pays and gives as a reference.... "Devon Partnership". Thank you so much. which of the 10 invoices for the same amount resulting from orders by different individual staff does that relate to? Sigh, and another tedious search.

I wasn't thinking of putting the description in the reference for any reason other than that sometimes it does have useful info and it is worth carrying across. Although having said that, if you are having to "reconcile" (create) the post manually, you'll pick it up when you look at it.

Thanks (0)
Replying to Hugo Fair:
avatar
By duncanphilpstate
08th Jul 2021 11:34

I had a further thought on this. My interest in using Bank Rules was primarily for payments which emerge from automated systems, for example Amazon Business, where the content and format of the Description in the bank stmt line is highly consistent. Even so, while the info is a bit more useful than just "Gardening" or "Devon Partnership", it still may not give anything very useful (es, that's you Amazon).

Thanks (0)
avatar
By duncanphilpstate
08th Jul 2021 11:39

I just love adding answers to my own questions as I find out more.

I've now discovered that:
1 if you use a bank rule in Xero, the Payee name (or possibly Contact name you give in the rule) is prefixed to the Description even if it otherwise empty. That's when you look at the transaction using Account Transactions. On reports I suspect it remains in a separate column / field.

2 If you go through Account Transactions to view a transaction, the original description from the bank stmt remains available if you select "View reconciliation" for the item. So even though you may not have inserted it into the expense account narrative, it is still there if you change your mind and want to edit the transaction.

Thanks (1)
avatar
By duncanphilpstate
30th Jul 2021 18:43

Another update from me:
In order to get to something resembling a set of accounts, I have now done various exports and reimports from Xero (sales invoices) and the bank statements (cash rec'd and expenses) into VT Transaction. Thank goodness for VT! it has all the flexibility that Xero lacks because it's aimed at accountants whereas Xero seems to be aimed at providing huge assistance to non-accountants, which is fine so long as your business roughly fits their assumptions about how things happen AND you stay up to date. If either of those break down, there will be trouble ahead.

Nonethless, it will be necessary for me to slog though the Xero fixes in the long-term.

Thanks (1)