Imagine you are the head of Accounts Receivable department at a large company. Drab, I know, But humor me and imagine.
Now, every month you get a transaction report like this:
And you want to know which numbers are matching up.
i.e, if your company gave Vendor-0002 $872.34 on 1st of April, 2014 and your received below payments from them subsequently,
- $427.77 on 1st April
- $152.88 on 2nd April
- $291.69 on 2nd April
Then you consider the account matched since the total received is same as total payable.(427.77 + 152.88 + 291.69 = 872.34).
Manually identifying all such matching transactions can be tedious, boring & error-prone.
This is where formulas come handy.
Using simple Excel formulas, we can identify all matching transactions, highlight them using conditional formatting so that you can chase the vendors with an outstanding.
Note: thanks to Kirstin whose email question prompted me to write this article.
Using formulas to match up (reconcile) accounting transactions
Step 1: Lets take a look at the data
This is how our AR (Accounts Receivable) data looks above (very first image in this post).
For the sake of simplicity I have set up this data as an Excel table.
Step 2: Write the formula
Here is the criteria for matching.
- If the total amounts (paid & received) corresponding to a vendor is zero, we consider it matched.
- Else not.
Add an extra column to the table and write this formula.
=ROUND(SUMIFS([Amount],[Vendor Reference],[@[Vendor Reference]]),2)=0
What this formula does:
It gives TRUE if a particular vendor’s amounts to total to ZERO
How it works?
The SUMIFS formula sums up all the numbers for the vendor name in current row [@[Vendor Reference]]
The ROUND formula rounds it to 2 digit precision. We need to use this because of a floating point error in Excel (that returns extremely small values when the result should be zero).
Step 3: Fill down the formula
Fortunately, you don’t have to do this step. Excel automatically fills the formula down as we are using tables. Yay!
Step 4: Highlight matched rows using conditional formatting.
Make a note of the column where TRUE / FALSE values are calculated. In my set-up, it is column E.
Select the entire table. Go to conditional formatting > new rule from home ribbon.
Set up a rule like this:
Note the mixed reference style $E4. This ensures that for highlighting all columns, Excel checks only column E.
That is all. You have now matched all the paid-up transactions. Time to flex your muscles, put-up your legs on the desk and call those other people who did not pay yet.
Download example workbook
Click here to download example workbook & practice. Examine formulas & conditional formatting to learn more.
How do you reconcile / match-up transactions?
I will be honest. I have never been the head of accounts receivable department. Even in my own household, I do not handle receivables. My wife takes care of that. I handle payables (ie credit card bills, shopping expenses and other such things).
But I often use formulas to reconcile my bank statement.
What about you? Do you use formulas to match transactions. What techniques you rely on? Please share your tips & ideas using comments.
Assuming we have data like above, what formula answers the question:
How many vendors have their transactions matched?
Post your formulas in comments.
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« CP010: Averages are Mean – 8 Techniques for making your analysis above average||Matching transactions using pivot tables [video] »|