Reconcilliation spreadsheets

kazflan Registered Posts: 36 Regular contributor ⭐
I am looking for some advice on monthly reconcilliation of the balance sheet accounts. In my current role I have to reconcile by taking the YTD balance sheet balances from the accountancy package into excel. To reconcile I input the movements for the month and hopefully will have a zero difference. I just feel that there must be a better way and was hoping if someone does a similar thing they could give me some idea on how I could update the current system, or it may be that everyone does it the same way? I have tried to look online for templates etc to see how others do the monthly rec's but no joy so far. Any hints/help much appreciated


  • AK002
    AK002 Registered Posts: 2,492 Beyond epic contributor 🧙‍♂️
    Where are you picking up the movement from that you input?
  • kazflan
    kazflan Registered Posts: 36 Regular contributor ⭐
    I basically have to extract a transaction report from the system for each account and input that information into excel. It is a duplicate of the information in the system but allows me to see if entries are in the correct accounts. The bank recs are checked against bank statements. Some recs will just have entries for the current month such as PAYE etc whereas other build up each month. I jus wonder how others do them? Hope this makes sense
  • oakley
    oakley Registered Posts: 73 Regular contributor ⭐
    I would imagine that everyone has their own idea of what a reconciliation would look like, but all should show the same information, a GL balance and a list of items that make up that balance.

    Not all reconciliations will have the same info on them, eg PAYE/NI may be one line per month reconciled to an individual GL code, where you may have several entries for prepayments and the the credits to the P&L writing down the balance eg software, maintenance contracts etc

    Be sure to deal with any aged items on reconciliations else they tend to start to look like transaction reports and lead to the possibility of having asset balances that are not realisable.

    We reconcile all our balance sheet accounts monthly but some companies may do this less frequently for some/all accounts.
  • coojee
    coojee Registered Posts: 794 Epic contributor 🐘
    You should be trying to prove the balance by other means rather than relisting the transactions in the nominal.

    For example, for fixed assets, the balance should agree to the fixed asset register, if there are additions and/or disposals in the nominal, are they genuine and correct?

    Debtors - does it agree to the aged debtors listing. A journal could have been entered into the control account that would mean that debtors in the TB don't agree to the listing

    Bank - Bank rec

    Cash - Petty cash book

    Creditors - same as debtors

    Loans - Agree to loan statements

    PAYE - Is the balance on the account the actual amount owed?

    Accruals/Pp - Do you know what the balance represents? If you've got a balance of £3000 in accruals you should be able to say that £1000 is for accountancy, £1000 is for rent, £500 is for telephone and £500 is for heat and light

    I wouldn't be doing it with spreadsheets at all. This is the nuts and bolts of what I was doing when I did management accounts for clients years ago. I took the balances from their accounts and verified them by other means. Essentially you're doing a mini audit every month.
Privacy Policy