[Done v1.5.0]UK tax in stock WRONG! if using Store as tax basis and deleted all zones

[kelvyn]

Over the last few weeks, I've posted multiple posts regarding tax errors.
Not just tax showing the wrong rate, I'm talking about completely miscalculated subtotals, shipping vat not calculating correctly, and many more as you'll see in the video.
The posts have either been ignored or dismissed as being caused by a bad add-on or something.

So let's get this straight: This is a totally fresh brand new install of ZC 1.3.9h. All that I've done is set the store up, add the tax rates, add two products, and link Paypal to a new sandbox account.

Rather than posting detailed text which doesn't seem to get me anywhere, I've made an 8 minute video which clearly demonstrates this bug. It's here:
http://www.youtube.com/watch?v=GBpYvSPkiaU&hd=1

This is not just annoying for our customers, it someone forgot to manually change the invoice it could cause legal problems. I would guess this applies to all UK stores.

You need to select the "full screen" option (bottom right of the video) to be able to see the text clearly.

Currently, this video is set to "direct link only" (ie: it won't show up for general browsers). Hopefully this can be resolved before I need to make the video public.

Many thanks.

[DrByte]

Okay, finally with a detailed set of configuration settings, I can duplicate your reported problems as you describe in your video.

Your problems come down to your odd configuration settings:
You've selected "Store" as basis of tax. Why again did you select this?

You're complaining about things like:
- prices in store change to 0.83 instead of 0.99 when logged in with a UK address
- tax rates are all showing as 0% when using a UK address
- shipping taxes aren't calculating
Those problems go away if you choose "Basis of Product Tax = Shipping", and "Basis of Shipping Tax = Shipping", and Tax Basis in your shipping module to "Shipping".

Granted, then since you've set your shipping as Taxable, you'll need to set your shipping rate to 8.3333 instead of 10.0000 so that when VAT is added it will calculate back to 10.00 properly.



As an aside, I note that in your "untouched install" you've done several things, including:
- You've deleted all countries except UK
- You've deleted ALL zones
This may be of minimal significance, but noted.

[kelvyn]

Thanks for your reply, which must have come in literally seconds after I shut down last night by the looks it it. Anyway...

Okay, finally with a detailed set of configuration settings, I can duplicate your reported problems as you describe in your video.

Your problems come down to your odd configuration settings:
You've selected "Store" as basis of tax. Why again did you select this?

Why is this "odd"? We're a local UK based store, 1/4 of our trade is store collection, we only post to the UK where tax is uniform in all areas.

Why not select "store"? If "Store" was an odd thing to select, why make it an option?

The reason we did it was when set to "shipping", Google Checkout doesn't calculate tax correctly.
As Kobra told me in his own inimitable way when I wondered about a new version of GC "there's nothing wrong with it", even though there clearly is. But that's another story.

For the time being, we can use your suggestion (see result below), and turn GC off, but it would be better got get a solution to both.

You're complaining about things like:
- prices in store change to 0.83 instead of 0.99 when logged in with a UK address
- tax rates are all showing as 0% when using a UK address
- shipping taxes aren't calculating
Those problems go away if you choose "Basis of Product Tax = Shipping", and "Basis of Shipping Tax = Shipping", and Tax Basis in your shipping module to "Shipping".

So is that a bug or not? If the customer is in the same country/VAT zone as the store, and I've asked it to show VAT, shouldn't it be showing VAT?

Granted, then since you've set your shipping as Taxable, you'll need to set your shipping rate to 8.3333 instead of 10.0000 so that when VAT is added it will calculate back to 10.00 properly.

OK, you're right. With all those conditions setup as you state, the VAT rates are showing correctly.

But totals are still wrong! With the store setup exactly as you suggest, 99p items are still showing as £1 in admin. That, we can deal with.
But the invoices are not good:

[SCR]http://www.zen-cart.com/forum/attachment.php?attachmentid=8978&stc=1&d=1299680705[/SCR]

In this example, subtotal and total(inc) only differ by 20p, but it's still wrong. You can imagine for £500 orders the difference can be quite large and isn't solved by your suggestions.
What's the best way of dealing with this? Why is it happening?

As an aside, I note that in your "untouched install" you've done several things, including:
- You've deleted all countries except UK

Well hang on, of course it's not TOTALLY untouched! Because I don't live in Florida, and I don't have a 7% tax rate. Of course there are a FEW things to setup, but it's easier to just run a quick "truncate" command in sql on the countries and zones table, than have all that mess we don't need.

You've deleted ALL zones

I was following Wilt's "Guide To UK VAT setup" in the Wiki.

Note the above assumes you have no zone definitions/tax rates already set up. If you do just delete them.

Anyway, thanks for getting us this far, but there's still a little way to go.

[DrByte]

OK, you're right. With all those conditions setup as you state, the VAT rates are showing correctly.

Correct.

But totals are still wrong! With the store setup exactly as you suggest, 99p items are still showing as £1 in admin. That, we can deal with.
But the invoices are not good:

[SCR]http://www.zen-cart.com/forum/attachment.php?attachmentid=8978&stc=1&d=1299680705[/SCR]

In this example, subtotal and total(inc) only differ by 20p, but it's still wrong. You can imagine for £500 orders the difference can be quite large and isn't solved by your suggestions.
What's the best way of dealing with this? Why is it happening?

It's happening because of the rounding-up of the price to 2 decimal places.
0.8250 rounds to 0.83 when stored as the product pre-tax price when the order is saved.
When you multiply 0.83 by your VAT rate it comes to 1.00 instead of 0.99.
Then when you multiply by quantities, it calculates out to the slightly larger amount above the subtotal.
However, the subtotal amount, along with the other various totals listed, are correct. And they are just marginally less than the incorrectly calculated amounts shown above. The customer is not overcharged, nor undercharged.

Quickest solution at this point is to simply not display the excluding-tax price columns. Just like the My Account area does when the customer does their own self-service display of their order/purchase details.

Anything else requires significant architecture change. And that work is being done in v2.0, and unlikely to be ported back to v1.x due to its significant complexities.

[kelvyn]

It's happening because of the rounding-up of the price to 2 decimal places.
0.8250 rounds to 0.83 when stored as the product pre-tax price when the order is saved.
When you multiply 0.83 by your VAT rate it comes to 1.00 instead of 0.99.
Then when you multiply by quantities, it calculates out to the slightly larger amount above the subtotal.

But this isn't correct, is it? No business could function on such a basis. Is displaying the wrong totals to a customer on a legal document normal practice in Zen, or is it a bug?

However, the subtotal amount, along with the other various totals listed, are correct. And they are just marginally less than the incorrectly calculated amounts shown above. The customer is not overcharged, nor undercharged.

I'm not sure you're really understanding the implications of this. We sell costumes, often to large companies that run events. Child costumes are tax-free, adult costumes and accessories are taxable. A correct tax invoice is required so the company can claim and offset tax in their accounting.

Quickest solution at this point is to simply not display the excluding-tax price columns.

40 * .83 is £33.20, not £33.00 as shown in the excluding tax columns.
1 * .99 is .99 not 1.00 as shown in the individual price including tax column.
40 * .99 is £39.60, not £39.80 as shown in the per-product line total (including tax).
The subtotal including tax doesn't match the per-product line total (including tax).

And something I missed earlier: If postage is a taxable item, it should be included as part of the tax breakdown.
20% of £49.60 is £9.92 not £8.27.

Which means that while the total is correct, four elements of the invoice are correct. If we were to remove the columns you suggested, it would't be a tax invoice.

The store is setup as you requested. It's a brand new install.

Is this a bug or not?

Incidentally, I went over order in our old store on 1.3.9c and this doesn't seem to be happening.
What could have changed?

[kelvyn]

Just checking in - I know it's only been just over a day, but we're having to manually edit almost every single order of >1 product to make it correct. Just wondering how long fixes for confirmed and verified showstoppers like this usually take?
Thanks.

[wilt]

Hi Kelvyn,

Sorry for the delay.

Have setup a clean install, and tried where possible to reproduce your settings, albeit I have not used the Store tax basis.

However even without that setting I have been able to reproduce the problem of the 39.8/39.60 discrepancy.

I believe I have a simple fix for this, but just want to test a little bit more.

Will post here tonight with the results.

Ian

[Alex Clarke]

And something I missed earlier: If postage is a taxable item, it should be included as part of the tax breakdown.
20% of £49.60 is £9.92 not £8.27.

Hi Kelvyn,

You're not calculating the tax correctly here and actually charging tax on top of tax.

To get the net price you need to use the following formula.

49.60 / 1.2 = 41.33333333333333

Now you can work out the correct tax figure.

49.60 - 41.33333333333333 = 8.26666666666667

You can reverse the above (add tax to a net price) by simply multiplying instead of dividing, as below.

41.33333333333333 * 1.2 = 49.6

Hope this helps,

Alex

[wilt]

Hi,

Sorry. Meant to post this last night,

The fix for the disparity between the line item and sub total in Admin Orders can be fixed by editing admin/orders.php

circa line 521

change

$currencies->format(zen_add_tax($order->products[$i]['final_price'], $order->products[$i]['tax']) * $order->products[$i]['qty'], true, $order->info['currency'], $order->info['currency_value']) . 

to

$currencies->format(zen_add_tax($order->products[$i]['final_price'] * $order->products[$i]['qty'], $order->products[$i]['tax']), true, $order->info['currency'], $order->info['currency_value']) . 

HTH

[torvista]

Is this a bugfix for all stores, that will be included in 1.5, or a special case?