Cooking the (Quick)Books
Ah, tax season… could there be a more thrilling time of the year?
So, today I was reviewing a sales & use tax form for the State of Illinois. Since our governor really isn't helping matters in our state these days, we felt the least we could do to help was to make sure to pay our taxes on time.
So, I was looking at our sales tax report in Quickbooks and, like a good accountant, just quickly checked to make sure it matched up against the total revenues in the income statement. They didn't match.
Hmm… funny thing about accounting, things really ought to balance.
It was a small discrepancy, but after searching unsuccessfully for the difference, it was clear that the issue involved more than one transaction. And, unfortunately, there were just far too many transactions to try and come up with a solution manually.
So, since I happened to have this data browser laying around, I exported both reports as CSV files and opened them up in Kirix Strata™.
The Quickbooks CSVs were obviously meant for spreadsheet export (as it included subtotals and odd record breaks), so I quickly did some clean up and then did a few gymnastics to compare the tables. Turns out there were a few manual journal entries that weren't mapped to the sales tax codes required by Quickbooks. And here I was hoping to blame Quickbooks… oh well.
Running through this process was a 5 minute affair, but it made me wonder about all these other small data manipulation tasks that are out there. There have got to be millions, nay, billions, of these things — 5 minute one-off, ad hoc data tasks that just can't be solved with the help of a spreadsheet (in this case, grouping or relationships were needed to do this quickly).
What do people normally do in these situations? I fear that they probably spend hours working the problem manually. Got a similar story and/or solution? Feel free to share in the comments section below.
October 13th, 2010 at 7:38 pm
Great article, it's nice to see that I'm not the only one struggling with IL Sales Tax. I've been dealing with the IL ST-1 for almost 2 years now by manually using a spreadsheet to track my product sales and the price I sell them at. Do you have any tools that may make this process a bit easier? I always fear that I am calculating it wrong.
October 14th, 2010 at 10:44 am
Hi Bill,
Yeah, the ST-1 is one of the more confusing Illinois tax forms I've run into. Unfortunately, we don't offer anything that really helps this process.
I think the spreadsheet option could work if your not drowning in data. They key would be being diligent in breaking out each sale by Illinois companies vs. out-of-state (interstate commerce), exempt companies, etc. Also, I've had good success in the past when I've contacted the Illinois tax folks directly with questions — they're usually quite helpful. Sorry we can't be of further help on this… good luck!