Having a standardized balance sheet reconciliation is a well-known best practice. Improve the efficiency of your month-end close process by incorporating a consistent and reliable framework. This balance sheet reconciliation is feature full – dynamic formulas, conditional formatting, and based on a simple methodology. This balance sheet reconciliation is free to download and contains no macros – just a simple but effective tool!

Balance Sheet Reconciliation Template

System Requirements & Download

Several years ago we created a bank reconciliation template, which has been the most downloaded template since we’ve been tracking downloads. (as of the date of this post).

Our last template was a month-end close checklist. To accompany this checklist, we decided to create a general balance sheet reconciliation template.

We leveraged the bank reconciliation template, but kept it in two columns for simplicity. Since it’s best practice to have consistent formats and functionality – it just made sense.

One key element of this reconciliation template that helps both the person preparing, as well as the reviewer is the two-column layout. Just like the bank reconciliation template, this balance sheet reconciliation template gives the user a quick understanding of what’s happening to the account. Are the issues in the general ledger or the sub-ledger (or if no sub-ledger, in the underlying detail)?

Because one section focuses on the general ledger and the other section focuses on the sub-ledger, you can see where the issues are developing. Whenever a difference has occurred and there is a corrective entry that has to be made, be sure to evaluate why. Once the root cause is identified, it may be possible to adjust or modify it in some way (i.e., change in controls or some type of automation) that will eliminate this issue.

Some Ideas on Using the Balance Sheet Reconciling Template

After you download the reconciliation, make copies for each of your balance sheet accounts to be reconciled. The download contains two tabs. The first tab is a blank ready-to-use reconciliation. The second tab includes an example of a reconciliation for accounts receivable.

When thinking about the different accounts that will be reconciled, try putting together a tip sheet to accompany the reconciliation. We would also suggest thinking about:

Common reconciling items

• Use the Example tab and create an example reconciliation to accompany the account. This can be used as a reference point for future reconciliations. The goal here is to document and share those common items so that the reconciliation process becomes a well-oiled effective process.  Along these same lines, ensure that any common terminology that needs to be understood has been defined.  Define common/critical accounting terminology.

Escalation procedures

• There should be a common process for all accounts. The process should be part of the larger month-end close process. However, if there are any specifics within a specific account, document them. Document them within the reconciliation. This way they will be front-of-mind, easy-to-find, and aren’t difficult to update when things change.

Resources

• Contacts, locations of reports, names of system reports to run, etc. Consider what resources are necessary to reconcile each account. Include this documentation along with the reconciling as well. Depending on the complexity, it may be necessary to include an additional tab in the template. Otherwise, they can be included in some of the space provided on the template.

Outcome

• Process for dealing with reconciling items. Ensure there is an understanding between items that require an adjustment vs. items that are valid reconciling items.

Note About Groupings

Other than entering the information specific to your account, there isn’t anything special to using this template. For those of you not familiar with groupings, look at the picture to the left.  You’ll notice at the bottom of the template, there are little plus symbols.  These can be expanded by left clicking them. Under each grouping, we’ve put a red uppercase letter.  A red uppercase letter corresponding to each note should be placed next to the reconciling item in the main section of the template. For those not used, just close the grouping.

Low Hanging Reconciliation Fruit

What do you do when you have a difference? Sometimes it feels overwhelming when you’re not sure on the first step. Here are some tips to get you going:

• Start before you have a difference – The tips above, if part of a comprehensive close process, should provide a solid framework for issue resolution.  However, maybe that framework isn’t yet there.  Go through these tips and consider incorporating them into your framework.
• Have a plan – It doesn’t have to be anything fancy, but think through what is likely causing the problem.  Before you start going down rabbit holes, do a little brainstorming of causes.  After you have a handful of ideas, determine which ones are the quickest to resolve and/or most likely to be the issue and start there.
• General journal entries – Often times when comparing the general ledger to the sub-ledger and there’s a difference, the cause can be found within the general ledger.  Think about accounts receivable or accounts payable, the system is generally automatically creating the entries.  It’s the functionality that is manually driven where mistakes can be made.
• Manual processes – For the same reason the general ledger is more likely to contain the issue than the sub-ledger, all manual processes should be looked at.  Manual processes that could impact the account you’re reconciling.  Look for footing errors, transposition errors, grouping errors, etc.
• Transposition errors – If the difference you’re reconciling is divisible by 9, the cause could be a transposition error.  For example, in the balance sheet reconciliation template download, on the example tab in cell M17 there is a transposition error.  A $750 transaction was entered as $570, resulting in a $180 difference, which is divisible by 9.
• System reports – most accounting systems provide guides and/or system reports that can help in the reconciliation process.  Find out what your system does and use it to your advantage.

Note, remember the data included in this balance sheet reconciliation template is example data only. There could be items included that are not relevant to your situation, or items missing that will be necessary for your reconciliation. Additionally, assumptions have been made surrounding the nature of the reconciling items, changes in these assumptions could impact how the entry is recorded.

On January 24, 2016   /   Accounting, Business Tools, Downloads, Reconciliations