Adding VLookup as a Control for Your
Month-End Workpapers
When closing for the month, many use an Excel workbook to
reconcile balances to the general ledger.
As the general ledger changes, do you have a process in place to easily
determine that balances previously reconciled still tie to the general ledger? If not, using Vlookup on each reconciliation
worksheet provides a quick and easy way to accomplish this control.
Usually, the month-end balance from a reconciliation
worksheet is at the bottom of the worksheet.
Below this balance, you can enter the associated general ledger account
number to be used in a Vlookup formula.
After the account number and Vlookup formula have been entered in each
workbook, all you need to do is cut and paste the most recent general ledger
balances into a separate worksheet. The
Vlookup will display the most recent balance and you can use conditional
formatting to highlight whether the two balances are within a tolerable error
limit.
The example below assumes that the AR Trial Balance has been
created and the total needs to be compared to the general ledger. There is a worksheet labeled “Accounts
Receivable” and a separate worksheet labeled “General ledger”.
The general ledger account for accounts receivable has been
entered at the bottom of the Accounts Receivable worksheet and a Vlookup
formula is used to compare this total to the amount in the General Ledger
worksheet.
The Vlookup function
is searching for a worksheet titled “General ledger” in the same workbook. The key to making this work is that you never
delete this worksheet. When the general
ledger changes, simply paste the new general ledger data over the old data in
the same worksheet.
For Vlookup to work, the lookup value, in this case the general
ledger account number, must be in the leftmost column of the General Ledger
worksheet (In this example, column A). The
general ledger data is in columns A and B and the account balance is in column
2 (which is the B column). The FALSE
range lookup value is used to indicate an exact match. The example general ledger is shown below.
Why use the entire columns of A and B? That way, if general ledger accounts are added,
we’re not constrained by a limited range.
This is important as we want to paste over this data again and again
without using new Vlookup formulas each time.
To calculate any differences, subtract the general ledger balance from the reconciliation balance. Use the ABS function in the formula since we just want to know the absolute amount of a difference and do not need to know whether it is positive or negative (ABS provides the absolute value of a number). More.....
