Quick Balance Sheet Account Matching

Hi all,

If your accounting package doesn’t let you apply g/l entries to other g/l entries, or if you just haven’t been doing it, reconciliation of balance sheet can often be an arduous task, with dozens, hundreds or thousands of line to match before you can find out your outstanding items.

The following formulas will help you make a first pass of reconciliation before you dig in further. The goal is to take care of all the one-for-one reversals. To find them, we check the amount column, and for each line, we check if the sum of amounts matching that line’s amount, either with a negative or positive sign, is equal to zero.

First, the two step method, using table references rather than range references. If the [ ] and [@..] confuse you, check out this tutorial on office.microsoft.com.

  • Create a column with the absolute value of the line’s amount, using the ABS() function: =ABS([@Amount])
  • Create a column with a formula summing the Amount column if the Absolute values match: =SUMIF([ABS],[@ABS],[Amount])=0

Second the one step method, using sumproduct:

  • =SUMPRODUCT((ABS([Amount])=ABS([@Amount]))*[Amount])=0

Both of these methods will return a list of TRUE / FALSE values: TRUE if the amount is matched, FALSE if it isn’t. You can filter out the TRUE value and focus your attention on the FALSE. Your work isn’t done yet, but it got started faster. Check out the example file (quick balance sheet matching) to see it in action.

Try it, you might like it,

Thomas

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s