Small business tips and tricks: reconciliation hell (or 12,716 ways to spend your weekend) Mike | February 1st, 2010
Warning to readers: the topic of this week’s blog post is arcane, technical, and probably boring to roughly 95% of our audience. I hope that you “5-percenters” get something out of this and that the other 95% of you appreciate that what I am about to describe is how many entrepreneurs spend their Sunday afternoons in late January.
12,716 transactions in 2009. Separate transactions. Individual transactions. This is what we did in 2009 and now our accountants insist that each and every one of these reconcile our own data against our merchant processor data. OMG!
Why is this ridiculous exercise necessary? Well, they tell us that if we are ever audited non-reconciling payments will prove problematic. Problematic? Hello! Problematic is finding a way to do this that won’t drive me over the edge…
Alright, I exaggerate. There are ways to do this with relative ease. The key word being “relative.” Here’s what I did to ease my load and (fairly) quickly identify the problem transactions. Our merchant processor is PayPal. PayPal is great in many ways, but surprisingly awkward to use in many others. Every month I can download a report from their site which gives me a list of all of the transactions generated on our account for that month. Problem is that they do not make it easy to customize these reports, so you pretty much have to live with what they make available. OK – I can deal with that.
So I download roughly 1,000 transactions per month. Checking these manually, one by one, would literally take hundreds of hours, so I John helped me to create a new database report: “All transactions” is a simple list, which I can import into excel and that includes information such as the project name for each transaction along with date, time, amount, and (most importantly) PayPal transaction number. I simply import these into a spreadsheet one month at a time and I’m almost ready to go.
Next I took the PayPal transaction reports and did the same thing: imported them into another spreadsheet, month by month. Now begins the fun. If you have never used the Excel VLookup function, you may want to learn a bit about it. This powerful tool allows you to search across multiple spreadsheets to find matching information. By telling the “PayPal Transactions” spreadsheet to look for the matching transaction numbers in the “All Transactions” spreadsheet i can make excel return the name of the project associated with the PayPal transaction number. Simply copy the formula down the list and, voila! The project names magcally appear on the PayPal spreadsheet. Any transactions that don’t register a match display as “#N/A.” (BTW, what is up with Excel and these bizarre values that it returns? Why can’t it just say “error” or “no value?” Whatever, I don’t have time to teach Microsoft about user experience.)
Now I can simply sort each month’s data by the VLookup column forcing the #N/As to the top. Fortunately for me, there are probably only 5 or 10 for each month and this week I will attack those to figure out what they were. Here’s a link to a great post with step-by-step instructions for using VLookup. Take a look and see if this can help you to match up your own data, whether it is financial data, customer data, or just your collection of DVDs.
And I do hope that our accountants appreciate the effort. Dara? Are you reading this?