Chatting about Excel and More

This is a guest post from Joseph Helstrom, CPA.

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.....https://cpaselfstudy.com/vlookup-control/