QBFC - How do you handle Complex Taxes?

Question

When we import an invoice with multiple taxes, the Tax Liability Report in QuickBooks is reporting all of the Revenue under the first tax. It seems the report is using the Subtotal value and then reports the taxes collected individually. The Taxes being reported are correct, just the Revenue associated with each tax is not correct.

To resolve this we tried to add a subtotal to each line and then include the associated taxes with each. If you hand enter this transaction directly in to QuickBooks it handle the multiple taxes and Subtotal lines fine. If you do this programmatically QB seems confused about the subtotals and will subtotal everything above.

The following is an example of one that does not work:

Item     Description      Invoiced   Rate      Amount
A234567  Item 1 Desc      1          8.88      8.88T
         Subtotal                              8.88
         State Tax                   7.00%     0.62
         Fed Tax                     0.36      0.36
A5221478 Item 2 Desc      2         29.88     59.76T
         Subtotal                             60.12
         State desc                  7.00%     4.18
X4325567 Item 3 Desc      1         15.59     15.59T
         Subtotal                             19.77
         State desc                  7.00%     1.09
         Fed desc                    0.62      0.62

If we force in additional Subtotal lines it will total correctly but is confusing since there are many duplicate subtotal lines.

Item     Description      Invoiced   Rate      Amount
A234567  Item 1 Desc      1          8.88      8.88T
         Subtotal                              8.88
         State Des                   7.00%     0.62
         Fed desc                    0.36      0.36
         Subtotal                              0.36
A5221478 Item 2 Desc      2         29.88     59.76T
         Subtotal                             59.76
         State desc                  7.00%     4.18
         Subtotal                             63.94
X4325567 Item 3 Desc      1         15.59     15.59T
         Subtotal                             15.59
         State desc                  7.00%     1.09
         Fed desc                    0.62      0.62
         Subtotal                              0.62

This second version resolves the Tax Liability reporting issue but does not look good. We tried to force in a blank line, but the SDK does not support that method, or we are not doing it correctly.

We are trying to understand how we can handle multiple taxes and ensure the invoice looks as simple as possible the the Tax Liability Report is reporting the correct values. Please let us know if you have been able to resolve this situation.

Regards,

Robert

Answer 1

Getting QuickBooks to calculate sales tax revenue correctly can be tricky as the way that it assigns revenue lines is a bit odd. I would suggest trying to mimic what Intuit does with their POS system (though they do have a bug with taxable shipping that they still haven't fixed).

POS will first create a zero percentage sales tax item. They call this POS Sales Tax. They use this tax item at the bottom of EVERY sales transaction.

Next, they manually calculate the rates in their own software and when they transfer over, they manually enter the amounts. They do not let QuickBooks calculate the line items even if the percentage is filled in. This allows them to group items together and not worry about QuickBooks calculating a different amount.

Next, they break out the items based on what tax rates are applied to them. In your example, they would group the first and third items together since they are both subject to State and Fed taxes (although at different rates). They repeat this process until all 'groups' are added to the transaction. Here's how it would look from your example:

Item        QTY    Rate      Amount  Tax
A234567     1      8.88      8.88    T
X4325567    1      15.59     15.59   T
Subtotal                     24.47
State Tax                    1.71
Subtotal                     26.18
Fed Tax                      0.98
A5221478    2      29.88     59.76   T
State Tax                    4.18
Subtotal                     64.92

INVOICE WOULD TOTAL TO 91.10

The Sales Tax Liability report would show the following lines:

Tax Name        Taxable Sales            Tax Collected
Fed Tax         24.47                    .98
State Tax       84.23                    5.89
Zero Rate Tax   84.23                    0.00

One thing to keep in mind is you ALWAYS want a sales tax item as the last line in your invoice body, or the items will be associated with the Zero Rate Tax item that is assigned to your invoice. QB POS has bug where shipping is the last item listed, which causes the Taxable Sales/Non Taxable Sales amounts to be incorrect. Normally, shipping isn't taxable, so it's not a huge bug, but I had a client who did have to charge sales tax and it messed them up completely.