Monthly reconciliation of balance sheet accounts is the only way to ensure that the financial data for a company is properly recorded on the accounting books. Balance sheet account reconciliation protects a company from reporting errors on the company financial statements, which can lead to errors when preparing the company's federal tax return. Many accountants create Microsoft Excel spreadsheets to assist with reconciling general ledger (GL) accounts to external documentation.
Step 1
Type the name of the general ledger account and the general ledger account number at the top of the Excel spreadsheet. You can type both names and numbers directly into the cells in Excel and the software will correctly format the entries.
Video of the Day
Step 2
Enter the month and year to be reconciled below the GL name and number.
Step 3
Type "Ending Balance per GL" in the first column of the spreadsheet a couple of lines below the heading.
Step 4
Enter the ending amount contained in the general ledger account in the second column of the spreadsheet on the same line as the "Ending Balance per GL" label.
Step 5
Type "Ending Balance per (source document)" in the first column of the spreadsheet below the row that contains "Ending Balance per GL." Examples of source documents include accounts receivable reports, accounts payable reports and asset listings.
Step 6
Enter the ending balance per the source document for that general ledger account in the second column of the spreadsheet on the same line as the "Ending Balance per (source document)" label.
Step 7
Type "Difference" in the next line in the first column.
Step 8
Create a calculation in the second column next to the "Difference" label that subtracts the source document total from the ending general ledger balance. This is the amount that needs to be reconciled.
Step 9
Create a section below the difference line called "Reconciling Items."
Step 10
List each item creating a reconciling difference in the reconciling items section. Include an explanation of each item and the amount of the item. An example of a reconciling item may be a payment on account that was posted to the bank account but not posted to the customer's account.
Step 11
Create a calculation at the end of the Reconciling Items section that totals the amount of reconciling items. When the Reconciling Items total equals the "Difference" total, then you have properly reconciled the account.
Video of the Day