We are currently trying to improve and standardize our account reconciliation process. We have currently over 200 accounts that we reconcile to excel spreadsheets. Can someone please share some of the best practices followed in accounts reconciliation in general? Also can someone please share good excel template that they use?
Best Practices for Account Reconciliation Process (besides automation)
Answers
Anon
It would be useful to understand the reasoning behind a desire to improve the process "besides automation."
What types of accounts are your reconciling?
Some very general suggestions:
-Prepare while closing books
-Always reconcile to your ending book balance and use one column versus doing a reconciliation to a value that is neither a beginning or ending balance ( I have seen these before)
-Have them reviewed and approved
-Don't delay any reconciliations
@Len They are all balancesheet accounts - accruals, prepaid, cash etc. Our goal is to close the books quickly and ensure that the reconciliation actually supports the balance.
Since you have so many accounts, at a previous company I worked for, we implemented an Advanced Excel template where we downloaded the trial balance into the first tab and the subsequent tabs had the template of each account reconciliation in it. So the only thing that changed each month was the downloading of the trial balance and it would show whether there was a variance or not and what that variance was. Now they could do this because the majority of the time the accounts should not have a variance but I don't know what is the case in your particular situation. I don't know if I explained the whole Excel template creation process thoroughly but this is something that if you have someone who has Advanced Excel skills on your team, they could easily do and if you can explain to them what I just told you they would understand. This basically makes it into one workbook on separate tabs interconnected with each other through the main trial balance first tab. I hope this helps.
If you have 200 accounts you need automation, period.
I can't believe no one has brought this up...
1. Your problem is NOT reconciliation....spreadsheet or otherwise. Your problem is transaction capture and processing. You do NOT have any confidence in the transaction capture and processing and you are compensating for this issue with reconciliation as a solution.
2. I think you have a misunderstanding of the reconciliation process. It is a process to determine differences between TWO or MORE sources of data (i.e. bank transactions and what you have in the books) and getting them to have the same numbers/details.. Per your admission, even other asset accounts, accruals, prepaids etc. are being reconciled. This only means that you are encoding transactions......TWICE. One in the spreadsheet and one in the books.....THAT is the problem.
3. I don't care how big your company is, if you are reconciling 200 accounts, that is TOO MUCH. Reconciliation and spreadsheet support summarizing what is in the balance are too different things. You should apply audit sampling standards (risk, materiality etc) to determine how many accounts you should be checking or reconciling.
Tackle the problem....don't compensate for it.