Ranking the Quality of Reconciling Items
Why is this an issue?
This article addresses a problem that accountants and data users frequently experience. But, honestly, they don't think about it exactly in the terms presented below, largely because it is very hard to solve manually and even very hard for Excel. There is a solution that makes it much easier that is presented at the end.
If you have two sets of data that need to be reconciled, like a bank reconciliation or even more complex data, there are perfect matches, less than perfect matches, and no matches. First consider a super simplistic example. Let's say each side only has three columns: check number, date, and amount. For this example, they correspond to the letters A, B, and C. Ideally you would want to match on all 3, so ABC on one side exactly equals ABC on the other side. However, sometimes the check numbers could be different, but the dates and amounts could match. Or Let's say the check numbers match, but the dates don't.
So you can see already there are combinations of values. So here are the combinations: ABC, AC, and BC. That makes perfect sense if there is one amount that corresponds to the amount on the other side, but what if someone forgot to enter all of the check numbers on April 15. So now you'd want to compare the SUM of the April 15 checks on one side to the sum on the other side (refer to B combo below). Similarly, let's say a user enters multiple amounts with the same check number to reflect that there are items purchased, like a portion allocated to food, another to entertainment, another to air transport. But, in the bank, there is only one check. So the sum of the checks could match (refer to A combo below). All of the combinations are below (there are 8 (2^3), but that math is not the focus of this piece):
Finding results in Excel
Unfortunately, without some very intense VBA programming, there is no way to automatically cycle through every combination. Below, we're going to go through 2 combinations. Instead of just the 3 fields shown in Table 1, we're going to have 5 fields (ABCDH) that define the perfect match and 1 date (C) field that represents the imperfect matches. There are 32 different combinations of 5 columns, but after you see the complexity to any single combination, you'll see why we're only looking at 2.
Looking at Table 2 - Sample Bank Data
We need to create two helper columns for this data. The first column creates an Amount column which is equal to the Debit column minus the Credit column. This is referred to as column H. The second helper column concatenates all of the fields in the main columns. It uses Excel's new CONCAT function. Because it's in a table, it is simply:
=CONCAT([@[Doc ID]],[@Type],[@[Date Posted]],[@Name],[@Amount])
There is one more column in Table 2, in column J, that describes the quality of the match. We'll get back to this.
Looking at Table 3 - Internal Data
We need to create one helper column. It is column F. It concatenates all of the fields in the main columns. It is similar to column I in Table 2:
=CONCAT(internal[@[Number]:[Amount]])
Quality of Matches
So now all of the data is in place and the final connection needs to be made. We need to put in the "Quality of Match" data into Tables 2 and 3. We need to say whether it's a PERFECT match, meaning all key fields, or just Date matches. Or no match. Remember that theoretically there could be other combinations, but that's too much manual work.
Below are the formulas for Column J in Table 2 and Column G in Table 3. It first looks for perfect matches. If it doesn't find, it looks for Date matches. If it still doesn't find, it's a no match. The formulas uses the new IFS function. Here is column J in Table 2:
=IFERROR(
IFS(
SUMIF(internal[Most fields],[@[Most fields]],internal[Amount]) = SUMIF([Most fields],[@[Most fields]],[Amount]), "PERFECT",
SUMIF(internal[Date],[@[Date Posted]],internal[Amount]) = SUMIF([Date Posted],[@[Date Posted]],[Amount]), "JUST DATES"
)
,"no match")
Here is column G in Table 3. The formula is almost identical to the one in Table 2. The only difference is that in Internal data, the date field is called "Date", unlike the Bank data in Table 2, which is called, "Date Posted"
=IFERROR(
IFS(
SUMIF(bank[Most fields],[@[Most fields]],bank[Amount]) = SUMIF([Most fields],[@[Most fields]],[Amount]), "PERFECT",
SUMIF(bank[Date Posted],[@Date],bank[Amount]) = SUMIF([Date],[@Date],[Amount]), "JUST DATES"
)
,"no match")
How do these formulas work
- If the SUM of the "Most Fields" is the same on both sides, then it's a perfect match.
- If the SUM of the DATE fields match on both sides, then it's a match based on the dates.
- The IFS function makes it easy to prioritize the perfect matches.
Drawbacks
- There is no way to automatically cycle through every combination of matches.
- Reporting results are sloppy because you cannot see what matches on left equal matches on the right. If there were thousands of matches, it would be very hard to visualize results.
- here is no simple way to notate scattered matches.
Are you kidding me? There must be a better way! There is!
Doug Schiller created CrushErrors.com to solve this problem, along with many others. Here are the steps with CrushErrors.com.
- Copy Table 2 Raw data into Crush (without the helper columns)
- Copy Table 3 Raw data into Crush
- Press Artificial Intelligence Button
- Respond to Crush's question that you feel lucky and will trust the AI
- Respond to Crush's question that the signs are reversed (that's because a bank looks at a deposit as a Credit but internal records show the same number as a Debit. But Crush knows this and makes that the default setting.
- Press the Power button to cycle through ALL ... ALL ... combinations.
- It's done. But now you can look at the reports to see the results.
CrushErrors.com found, for example, the match for the payment of $1,022.00. Our Excel model did not find that match because the date was different. Crush found it because the amounts were identical.
I recommend that users with any kind of reconcilation problem consider using CrushErrors.com. It solves very basic problems experienced by many people every day.
RSS Feed
