One of the biggest problems with QuickBooks Online is the inability to report on multiple sales tax codes. Here in New York, it is very common to have both New York sales tax and New Jersey sales tax. Because of thus limitation we usually recommend to clients to avoid the online edition of QuickBooks, however we do have a few clients who want to use it because it can be accessed from anywhere there is an Internet connection.
Lately, I had a client who we needed to prepare sales tax for NY, NJ and TX who was Using QuickBooks Online. In order to work around this, I created a procedure using Excel that gets us the information so we can prepare the sales tax returns. This method works for this client, but does make some assumptions so it may not work for everyone.
First I exported the client list out to Excel. I made sure I had the client name and the billing state. Next, I ran the Taxable Sales Detail report. I modified it to inlaced taxable and non-taxable clients and items (set the filters to All). I also added the Taxable column, and then exported this report out to Excel.
Now that I had a list of sales transactions that indicated taxable status, I did a simple VLOOKUP formula in Excel to find the customer’s state code. After that, I could sort based on the state code and copy the three states into separate workbooks. Using the Subtotal feature of Excel I could then get the total taxable and non-taxable sales for each state.
This method should be fairly quick to implement for someone who is familiar with Excel, though it does take a few minutes to do. One of the problems, though is the assumption that the billing state on file for a client is the same state for taxation for all that client’s sales. Luckily for us, this is the case with this client.