Magento Forum

Page 1 of 2
Fun with VAT - Pre-Planning the 2.5% Hike
 
J_T_
Moderator
 
Avatar
Total Posts:  1961
Joined:  2008-08-07
London-ish, UK
 

The 2.5% drop in VAT has done its job (cough) and the crisis is over. So it’s up to us merchants again to stomach the inconvenience of changing it for the second time in 12 months and a day.

Stupidly, I set up Magento with “Prices inc VAT”. Meaning, I have to now change all prices if I want to maintain our margins.

Here some thinking out loud to remedy this smoothly on what should have been a nice 0:00 on 01-01-10

Database wise, pricing seems to be stored in catalogindex_price and catalog_product_entity_decimal, the former probably being some kind of cached index of the latter.

A quick and dirty VAT increase fix for those who recorded pricing inc VAT can just do:

UPDATE catalog_product_entity_decimal SET value = (value * 1.02173913) this increases those prices from their state of being 100% + 15% to 100% plus 17.5%

Of course not forgetting to log in to the Magento admin and change the VAT rates under Sales > VAT > Manage VAT Zones & Rates or in one fell swoop. Those who recorded prices without VAT can happily just do the following and skip editing prices themselves:

UPDATE tax_calculation_rate SET rate = 17.50 WHERE rate = 15.00

The result of course gives you very ugly pricing. Something costing £9.99 then costs £10.21 just because the government is struggling without the extra 22p. It’s going to be a nice end of 2009 and/or start of 2010 to fix this up. Bastards.

What’s your strategy? Are you looking forward to it?

 
Magento Community Magento Community
Magento Community
Magento Community
 
darkedge3
Jr. Member
 
Total Posts:  24
Joined:  2009-03-13
 

MANY MANY thanks for this, it has been MOST useful!

instead of making around 6000 changes, it made them all for me in one hit within 0.1 seconds!

I’d like to add the following that may help you and many others :

One problem I experienced was that many products did not seem to have the price changed correctly, and I quickly reaslised that it was because most of my products have multiple options

example :

MDF skirting boards

However,

with guidance from the above standard solution, I found the table that saves the custom - product - options, and applied this to the code to result in :

UPDATE catalog_product_option_type_price SET price = (price * 1.02173913)

voila!

Happy new year!

 
Magento Community Magento Community
Magento Community
Magento Community
 
J_T_
Moderator
 
Avatar
Total Posts:  1961
Joined:  2008-08-07
London-ish, UK
 

I’m glad to hear at least one person joined in! Quite surprised it’s just you, had expected many more to at least have an opinion on the timing of the hike or how easy/hard it is to change in Magento.

I ended up ‘eating’ the difference for now and shortly after the fireworks, simply updated the rates in the admin back-end, not having easy access to execute the necessary SQL.

 
Magento Community Magento Community
Magento Community
Magento Community
 
edmondscommerce
Guru
 
Avatar
Total Posts:  342
Joined:  2008-08-26
 

it is a bit buried away this UK board - I’m not sure that many people are aware of it -

nice tip btw smile

 
Magento Community Magento Community
Magento Community
Magento Community
 
furnitureforyoultd
Enthusiast
 
Total Posts:  833
Joined:  2009-03-09
 

bumping this.

Does anyone have the multiplier need to go from 17.5% to 20.5%?

 
Magento Community Magento Community
Magento Community
Magento Community
 
J_T_
Moderator
 
Avatar
Total Posts:  1961
Joined:  2008-08-07
London-ish, UK
 

Yes, we’re repeating the same shenanigans… 20% though wink Let me grab a calculator…

 
Magento Community Magento Community
Magento Community
Magento Community
 
J_T_
Moderator
 
Avatar
Total Posts:  1961
Joined:  2008-08-07
London-ish, UK
 

OK - first of all, this is for if you have your pricing set to include VAT.

We’re going up 2.5% from 17.5% to 20%. Or actually, we’re rising 14.2857143% to be exact.

So last time round, from 15% to 17.5%, which is a 16.66667% rise, the SQL was:

UPDATE catalog_product_entity_decimal SET value = (value * 1.02173913) this increases those prices from their state of being 100% + 15% to 100% plus 17.5%

1.02173913 being the magic number required to multiply all prices with.

Say we have a product that now, pre-hike costs £9.99 inc the 17.5% VAT.

£9.99 = 117.5%
£8.50 = 100%
£1.49 = 17.5%

So the end price is made up of £8.50 plus £1.49 VAT = £9.99

That £1.49 part is rising.

20% * 9.99 = 1.998

Now it’s £8.50 + £1.998 = £10.498 - or £10.50 after rounding.

So what was £9.99 with 17.5% VAT included should now become £10.498 with 20% VAT included. Hence the magic number now being:

1.050850851

UPDATE catalog_product_entity_decimal SET value = (value * 1.050850851) this increases those prices from their state of being 100% + 17.5% to 100% plus 20%

That may or may not give some rounding issues, not tested this yet but the end price is correct technically.

After that you will probably want to change the pricing to something a bit more palatable, but in the interim, at least you won’t be incorrect or loosing out.

 
Magento Community Magento Community
Magento Community
Magento Community
 
elfling
Enthusiast
 
Avatar
Total Posts:  901
Joined:  2008-10-21
 

ooo, my hero grin

May also wish to apply this to your tier prices tables as well.

For those without the Aitoc percentage tier pricing module

UPDATE catalog_product_entity_tier_price SET value = (value 1.050850851);

And for those with the Aitoc percentage tier pricing module (which I’ve seen is now discontinued)

UPDATE catalog_product_entity_tier_price SET value = (value 1.050850851where percent 0;
 
Magento Community Magento Community
Magento Community
Magento Community
 
elfling
Enthusiast
 
Avatar
Total Posts:  901
Joined:  2008-10-21
 

Don’t forget to update your bundle products as well.

UPDATE catalog_product_bundle_selection SET selection_price_value = (selection_price_value  1.050850851);

Then refresh catalog indices, catalog indexes yada.. All depends what version of Mage.

Also, I think this wasn’t a popular topic previously, because most people just updated the VAT rate, but kept the prices the same and made more profit per product. A lot more people will be looking at this in the coming weeks, thats for sure smile.

For search engines sake:

20% VAT increase in the UK, Increase Product Prices, Tax Increase, bastard tory government increase VAT to 20% with very little benefit smile

 
Magento Community Magento Community
Magento Community
Magento Community
 
J_T_
Moderator
 
Avatar
Total Posts:  1961
Joined:  2008-08-07
London-ish, UK
 

Thanks for making it (the Magento VAT increase how to instructions guide) more complete, we don’t use Magento tier prices or bundled Magento products (much).

You’re right about pocketing the difference last time. We first didn’t want to, but when you then go a re-round all pricing and realize they don’t change much, we left most as is and reduced delivery a bit instead.

Now we’ll just change the VAT rate first so I don’t have to step away from the champagne for too long. Then come the 2nd or 3rd of Jan, we’ll go through the prices and pick and choose some changes post VAT increase.

We had actually predicted this scam, lowering it first, then increasing it over the original rate. Saw that one coming, just didn’t expect it to go to 20%, had anticipated more in line with other Euro countries, around 19%. The only upside of 20% VAT in Magento is that it’s easy to calculate!

Having said all that, the majority of products we ship are 0% anyway smile

 
Magento Community Magento Community
Magento Community
Magento Community
 
t0mbop
Jr. Member
 
Total Posts:  25
Joined:  2007-12-22
 

Hi guys,

I have a store where products are both Taxable and non-Taxable goods.

How do I apply this SQL calculation to just the “Taxable Goods” products leaving the non-Taxable unchanged, but increasing the price of the Taxable products by 2.5%.

Cheers

 
Magento Community Magento Community
Magento Community
Magento Community
 
J_T_
Moderator
 
Avatar
Total Posts:  1961
Joined:  2008-08-07
London-ish, UK
 

You’ll have to dig around in the DB to find the appropriate Tax ID of the taxable products. Then add a WHERE clause, possibly a JOIN to the above SQL statements.

We too have “taxable” and “non-taxable” products. Have you ever found a solution to have one product SKU and have the customer decide whether they qualify for 0% or not? If that’s applicable to your catalogue anyway. We now need to set up two SKUs for what essentially is the same physical product.

 
Magento Community Magento Community
Magento Community
Magento Community
 
t0mbop
Jr. Member
 
Total Posts:  25
Joined:  2007-12-22
 

Say for instance that’s 2500 products, that’s likely to be a statement with 2500 where ID’s in it isn’t it?

Could it not be something along the lines of:

WHERE column_name Taxable Goods ;

.. or similar?

Can you give me an example of what you mean?

Cheers.

 
Magento Community Magento Community
Magento Community
Magento Community
 
J_T_
Moderator
 
Avatar
Total Posts:  1961
Joined:  2008-08-07
London-ish, UK
 

No, most likely it’s one tax ID.

WHERE products_tax_id = 5;

For example, pseudo code, after having verified the field name for the product’s tax id and which number in your installation matches your VATtable goods. But I haven’t looked in the DB to be honest.

By the way only just realized, the VAT hike only starts January 4th this time. Last year it was Jan 1. So my champagne references are irrelevant as I don’t usually sip away on DOm Perignon on Jan 4th.

http://www.hmrc.gov.uk/vat/forms-rates/rates/rate-rise-guidance.pdf

 
Magento Community Magento Community
Magento Community
Magento Community
 
t0mbop
Jr. Member
 
Total Posts:  25
Joined:  2007-12-22
 

tax_class_id exists in the table “catalog_product_flat_1” however this isn’t the table you’re referencing above.

Any ideas?

 
Magento Community Magento Community
Magento Community
Magento Community
 
pglock
Jr. Member
 
Avatar
Total Posts:  30
Joined:  2010-05-06
Princes Risborough, UK
 

I’m just setting up for tomorrow evening’s update. I’ve looked at the sql way of doing this but decided to use a excel method instead as we have a mix of taxable and tax exempt products, the prices are set to include taxes, plus the store owner wants to round everything to the nearest 5p to keep the price points simple. Here’s the process:

0. Take a backup of your database…

1.  From Magento admin, export all products as xml using ‘System > Import/Export > Export All Products’ Export as an xml file to your var/export folder.
2.  Download exported file using ftp client, open in excel (proably works in OpenOffice Calc as well)
3. Add a column to calculate revised pricing, called it ‘new price’ in my spreadsheet this is column ‘CC’
4. Use the ‘Data > Filter’ function to show only taxable products from the ‘tax_class_id’ column.
5. Let’s assume the existing ‘price’ is in column ‘X’ Set the value for the first cell in ‘new price’ as ‘=ceiling(1.2*X2/1.175,0.05’ This calculates the new price at 20% VAT and rounds up to the next 5p price point.
6. Copy this formula down the rest of the ‘new price’ column to the end of the entries in the ‘price’ column
7. You should now have a column of nicely rounded up prices at the new price points you want for your store on 4 January. Copy the prices from ‘new prices’ then use ‘Edit > Paste Special’ to paste the values only over the existing entries in the ‘price’ column.
7a [optional] while you’ve got everything in one file you might want to run a spell checker over the descriptions column and check that all the products are in the correct tax class
8 Delete the ‘new price’ column, save as an xml file.

If you have a lot of products, this will be a large file (mine is around 17M for 2000+ products) so you’ll need to use an FTP client to upload this file to your magento server. Use the var/import folder to hold your file ready for the next bit.

1. In Magento admin, ‘System > Import/Export > Import All Products’ and use the file name for your xml file now in var/import.
2. Save the profile, then run it.
3. It takes a while but you should now have everything updated.

Good luck. Send the bill for your time to George Osborne?

P.S. Don’t forget to update your tax rates in ‘Sales > VAT > Manage Tax Zones & Rates’

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top
Page 1 of 2