Blog for Power Sheet AI BI for Excel
I’ve been struggling with CrushErrors.com messaging for years. A VC asked me for a 2 minute pitch. I only wanted to demo my amazing software. It does so much. It’s revolutionary. I couldn’t reduce it to a pitch. That didn’t go over too well with the VC.
I believed that the domain name, CrushErrors.com, was already broad and explanatory. While true, it's too hard to visualize. And what, specifically, does crushing errors really mean? I also tried to say that my software replaced the very old-fashioned “stare and compare.” While true, it doesn’t convey the full nature of the software either. It's visual, so I tried it anyway. I had many other ideas along the path, but nothing was perfect.
Meanwhile, I continuously had to add new functionality to the software. Why? Because it is shockingly hard to find needles in haystacks. And all of the new power forced me to add more artificial intelligence to reduce the complexity of implementing the new power.
Then I tried more messaging ideas. I tried to get specific. I explained how the software can be used. It reconciles. It finds fraud. It finds ghost accounts. It finds every kind of human error in data. While all true, it's too much detail. There is no emotional reaction. No visual.
I recently explained the software to an industry professional. For some subconscious reason, this time I blurted out “WE FIND NEEDLES IN HAYSTACKS.”
It felt perfect.
Why do I like it? It’s easy to visualize. It conveys the challenge of finding needles. It has proven itself as both powerful and memorable, as it has been an expression since 1532.
Bottom line is that “WE FIND NEEDLES IN HAYSTACKS" is my new message.
As I'm a stickler for precision, I feel compelled to reveal that I should asterisk the “WE FIND NEEDLES ...” message. In most cases, we actually scan TWO haystacks. We compare them using many AI algorithms. The difference reveals the needles.
**A second asterisk.
Sometimes it is not perfectly clear that hay is really hay. Sometimes hay is absolutely, definitively hay. Sometimes it is not 100% certain, so that is flagged. Users evaluate flagged items and apply human insight to confirm the AI.
Who is the "WE" in “WE FIND ...” ?
The WE could be (A) me and my experts, (B) the users, or (C) some combination. We could teach users how to find their own needles or it can be full outsourced.
Visit CrushErrors.com to schedule a demo. Give us the opportunity to prove ourselves with some of your sample data.
All data and calculations remain local. Nothing is transferred over the Internet.
Although CrushErrors is not as fun as watching the following video, please watch it to see how it may have the best messaging of all. Even though a user told me that my software is a little bit like playing a video game, it doesn't come close (https://goo.gl/QVWBCe):
Thanks for reading, Doug
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:
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
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.
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.