Anonymous
Share this content
14

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 (14)

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 (0)
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 JoF
12th Jan 2020 16:49

Have you involved the Accountant in what you are doing now? I would, especially before you start messing about with a prior year and agree with a couple of the posters to only start from the new year (with the benefit of Accountants figures).

Assuming the debtor book is correct and an app correctly matches the payments to the invoices (??) there is probably a simple way round processing it that you are missing.

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 (0)
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)
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)
Share this content