[OFBiz] Users - VAT and rounding

[Manuel Meyer]

Hi Si,

I totally agree, what I've done is only very temporary and for a (my)
special case.
I can already see some issues coming, for example when you set a promotion
giving a % on the order total.
You have for example: (consider a VAT of 10%):
For a product Product1, net price 100.
An order containing 1 product product1, and a rebate of 10% on the total.

OrderItem unit_price  100
OrderAdjsutement type SALES_TAX amount 10 percentage 0.1 comments VAT
-> Promotion 10% on the order total
OrderAdjustment type PROMOTION amount -10

So it makes
Product1 100
SubTotal 100
Adjustments -10
VAT10% 10 in the orderadjustment
Total 100

In my case this is not the good amount because the VAT should be calculated
after all rebates have been set, and so give:

Product1 100
SubTotal 100
Adjustments 10
New subtotal 90
VAT10% should be 9 (because VAT must be calculated after all rebates,
promotions...etc)
Total should be 99

Do you think it makes sense or am I out of subject?
Don't you have also this problem in the states when you set a rebate on the
total order, as you have a different way of dealing with taxes?
 
So event with rounding up, I still can see other issues coming, and so
prefer to keep on staying with gross prices, and then calculate the VAT
afterwards, which is of course not the best process.
What I do is:
Product1(gross) 110
Subtotal(gross) 110
Adujstment 10%
Total 99%
VAT10% computed afterwards = 9 showed on the order and invoice.

That is the only way I found so far to keep on having the right VAT amount.
Things will certainly get more difficult when we'll have different products
with different tax rates...

I'll be happy to work on the implementation you are talking about.
You are right, the solution of working on OrderAdjustments for informational
purpose in the case prices contain taxes, and otherwise adding the amount in
the order item's amount is a good way; here if you do B2B, customers are
interested by seeing the net price, as they'll get the VAT back (they pay
the VAT but claim it back from the government), which is not the case when
you do B2C, where customers are only interested in gross prices.

May be we should modify/add more OrderAdjustments when we run promotions?

Best Regards,
Manuel

 
-----Message d'origine-----
De : [hidden email] [mailto:[hidden email]] De
la part de Si Chen
Envoyé : Thursday, October 06, 2005 2:06 AM
À : OFBiz Users / Usage Discussion
Objet : Re: [OFBiz] Users - VAT and rounding

Manuel, David, David, et al -

That's a pretty clever hack, and I'm glad it works for you.

It seems, though, that longer term we'll need to be able to support both
the US (tax added on top of price) and European (flat price including a
tax) formats.  It seems that it might not be so hard if we do it this way:
1.  Create a new tax type
2.  Additional code which calculates that tax for that type, including
correct rounding formulae
3.  An added field for OrderAdjustment which denotes whether it should
be added back to the order item's amounts (in the case of US sales
taxes) or is there just for informational purposes (in the case of
European/VAT), since the flat price includes the tax
4.  GL posting should work fine once the correct amounts in
OrderAdjustment or at most require a small change.

I think it might be nice to break the sales tax calculation code into a
master which calls several different routines or services depending on
the tax type, so we can keep the implementation separate or call an
outside service if it's available.

Just some general ideas.  Anybody up for doing this?

Si

Manuel Meyer wrote:

>Hi All,
>
>Further to what I've red and understood in this thread, here is what I've
>done in order to resolve my issue, which was more a rounding problem rather
>than a VAT problem:
>
>My big concern was that to sell a product of 120 VAT included, I could not
>enter a net amount with 2 digits of precision giving 120 after having
>applied 19.6% of taxes, or by entering a net price of 125.43 for a product,
>I got 150 for 1 product tax included, but 450.01 for 3.
>
>So, I've set all the currency entries in the DB to be numeric(18,6) (I am
>using postgres), and removed all the rounding made in the code (mainly in
>the OrderServices.getTaxAmount() and OrderReadHelper.calcOrderAdjustment()
>routines).
>I am only rounding when displaying amounts to the customer.
>
>And since that, I've resolved all my rounding and therefore VAT issues, and
>the site is a real Swiss clock :)
>
>The devil is in the details, so I might have missed something, but it looks
>like working so far.
>
>Best regards,
>Manuel
>
>-----Message d'origine-----
>De : [hidden email] [mailto:[hidden email]]

De

>la part de T E Schmitz
>Envoyé : Wednesday, October 05, 2005 3:17 PM
>À : OFBiz Users / Usage Discussion
>Objet : Re: [OFBiz] Users - VAT and rounding
>
>David Garrett wrote:
>  
>
>>Out of interest why are there differences in the vatAmount
>>vatAmount = 16.097 or 16.095 or 16.098
>>depending on which algorithm you use and on whether you are a retailer or
>>wholesaler.
>>    
>>
>
>The VAT Guide describes several rounding modes (unfortunately fails to
>be specific enough for invoicing at retailers).
>
>For our example
>gross = £123.45
>vatRate = 15%
>vatFraction = 0.1304
>==> vatAmount = 16.09788
>
>Invoicing at line level (B2B)
>a) round down to nearest 0.1p -> 16.097
>b) round to nearest 1p or 0.5p -> 16.095
>
>For invoicing at retailers, rounding *down* is forbidden:
>c) round to nearest 0.1p -> 16.098
>
>  
>
>>Thanks for the excellent summary.
>>    
>>
>
>Sadly, I seem to spend more time in accounting newsgroups than in Java
>newsgroups. ;-)
>
>
>  
>

 
_______________________________________________
Users mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/users

__________ NOD32 1.1243 (20051006) Information __________

This message was checked by NOD32 antivirus system.
http://www.eset.com


 
_______________________________________________
Users mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/users

[Si Chen-2]

Should VAT only be calculated after the promotion is done then?

I think it'd be great to implement some of these features.

David - where you going to implement VAT at some point?

Si

Manuel Meyer wrote:

>Hi Si,
>
>I totally agree, what I've done is only very temporary and for a (my)
>special case.
>I can already see some issues coming, for example when you set a promotion
>giving a % on the order total.
>You have for example: (consider a VAT of 10%):
>For a product Product1, net price 100.
>An order containing 1 product product1, and a rebate of 10% on the total.
>
>OrderItem unit_price  100
>OrderAdjsutement type SALES_TAX amount 10 percentage 0.1 comments VAT
>-> Promotion 10% on the order total
>OrderAdjustment type PROMOTION amount -10
>
>So it makes
>Product1 100
>SubTotal 100
>Adjustments -10
>VAT10% 10 in the orderadjustment
>Total 100
>
>In my case this is not the good amount because the VAT should be calculated
>after all rebates have been set, and so give:
>
>Product1 100
>SubTotal 100
>Adjustments 10
>New subtotal 90
>VAT10% should be 9 (because VAT must be calculated after all rebates,
>promotions...etc)
>Total should be 99
>
>Do you think it makes sense or am I out of subject?
>Don't you have also this problem in the states when you set a rebate on the
>total order, as you have a different way of dealing with taxes?
>
>So event with rounding up, I still can see other issues coming, and so
>prefer to keep on staying with gross prices, and then calculate the VAT
>afterwards, which is of course not the best process.
>What I do is:
>Product1(gross) 110
>Subtotal(gross) 110
>Adujstment 10%
>Total 99%
>VAT10% computed afterwards = 9 showed on the order and invoice.
>
>That is the only way I found so far to keep on having the right VAT amount.
>Things will certainly get more difficult when we'll have different products
>with different tax rates...
>
>I'll be happy to work on the implementation you are talking about.
>You are right, the solution of working on OrderAdjustments for informational
>purpose in the case prices contain taxes, and otherwise adding the amount in
>the order item's amount is a good way; here if you do B2B, customers are
>interested by seeing the net price, as they'll get the VAT back (they pay
>the VAT but claim it back from the government), which is not the case when
>you do B2C, where customers are only interested in gross prices.
>
>May be we should modify/add more OrderAdjustments when we run promotions?
>
>Best Regards,
>Manuel
>
>
>-----Message d'origine-----
>De : [hidden email] [mailto:[hidden email]] De
>la part de Si Chen
>Envoyé : Thursday, October 06, 2005 2:06 AM
>À : OFBiz Users / Usage Discussion
>Objet : Re: [OFBiz] Users - VAT and rounding
>
>Manuel, David, David, et al -
>
>That's a pretty clever hack, and I'm glad it works for you.
>
>It seems, though, that longer term we'll need to be able to support both
>the US (tax added on top of price) and European (flat price including a
>tax) formats.  It seems that it might not be so hard if we do it this way:
>1.  Create a new tax type
>2.  Additional code which calculates that tax for that type, including
>correct rounding formulae
>3.  An added field for OrderAdjustment which denotes whether it should
>be added back to the order item's amounts (in the case of US sales
>taxes) or is there just for informational purposes (in the case of
>European/VAT), since the flat price includes the tax
>4.  GL posting should work fine once the correct amounts in
>OrderAdjustment or at most require a small change.
>
>I think it might be nice to break the sales tax calculation code into a
>master which calls several different routines or services depending on
>the tax type, so we can keep the implementation separate or call an
>outside service if it's available.
>
>Just some general ideas.  Anybody up for doing this?
>
>Si
>
>Manuel Meyer wrote:
>
>  
>
>>Hi All,
>>
>>Further to what I've red and understood in this thread, here is what I've
>>done in order to resolve my issue, which was more a rounding problem rather
>>than a VAT problem:
>>
>>My big concern was that to sell a product of 120 VAT included, I could not
>>enter a net amount with 2 digits of precision giving 120 after having
>>applied 19.6% of taxes, or by entering a net price of 125.43 for a product,
>>I got 150 for 1 product tax included, but 450.01 for 3.
>>
>>So, I've set all the currency entries in the DB to be numeric(18,6) (I am
>>using postgres), and removed all the rounding made in the code (mainly in
>>the OrderServices.getTaxAmount() and OrderReadHelper.calcOrderAdjustment()
>>routines).
>>I am only rounding when displaying amounts to the customer.
>>
>>And since that, I've resolved all my rounding and therefore VAT issues, and
>>the site is a real Swiss clock :)
>>
>>The devil is in the details, so I might have missed something, but it looks
>>like working so far.
>>
>>Best regards,
>>Manuel
>>
>>-----Message d'origine-----
>>De : [hidden email] [mailto:[hidden email]]
>>    
>>
>De
>  
>
>>la part de T E Schmitz
>>Envoyé : Wednesday, October 05, 2005 3:17 PM
>>À : OFBiz Users / Usage Discussion
>>Objet : Re: [OFBiz] Users - VAT and rounding
>>
>>David Garrett wrote:
>>
>>
>>    
>>
>>>Out of interest why are there differences in the vatAmount
>>>vatAmount = 16.097 or 16.095 or 16.098
>>>depending on which algorithm you use and on whether you are a retailer or
>>>wholesaler.
>>>  
>>>
>>>      
>>>
>>The VAT Guide describes several rounding modes (unfortunately fails to
>>be specific enough for invoicing at retailers).
>>
>>For our example
>>gross = £123.45
>>vatRate = 15%
>>vatFraction = 0.1304
>>==> vatAmount = 16.09788
>>
>>Invoicing at line level (B2B)
>>a) round down to nearest 0.1p -> 16.097
>>b) round to nearest 1p or 0.5p -> 16.095
>>
>>For invoicing at retailers, rounding *down* is forbidden:
>>c) round to nearest 0.1p -> 16.098
>>
>>
>>
>>    
>>
>>>Thanks for the excellent summary.
>>>  
>>>
>>>      
>>>
>>Sadly, I seem to spend more time in accounting newsgroups than in Java
>>newsgroups. ;-)
>>
>>
>>
>>
>>    
>>
>
>_______________________________________________
>Users mailing list
>[hidden email]
>http://lists.ofbiz.org/mailman/listinfo/users
>
>__________ NOD32 1.1243 (20051006) Information __________
>
>This message was checked by NOD32 antivirus system.
>http://www.eset.com
>
>
>
>_______________________________________________
>Users mailing list
>[hidden email]
>http://lists.ofbiz.org/mailman/listinfo/users
>
>  
>

 
_______________________________________________
Users mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/users

[David Garrett]

Si

[sorry this mail was held up in my outbox for a day]

I generally agree.

I am of the view that, for me, there is a short-term and a medium term
solution.
 
The short-term solution that I need to implement for this weekend is to
change the price to numeric(18,6). This is a solution while the quantity
remain low/reasonable.
 
The medium term solution is to operate on gross price. This needs more
investigation but I feel it is the better solution.
 
I also believe the approach of using configuration settings in the
TaxAuthority entities as advocated by David is a more preferable method than
the plugin idea, which I think your master/sub-service idea alludes to.

I will be taking a closer look but I don't think the OrderAdjustment.addback
field will be required. It should be possible based on TaxAuthority config
to work back from the gross to the adjustment and base.

Anyway ... I'm giving it a go ...

David  

-----Original Message-----
From: [hidden email] [mailto:[hidden email]]
On Behalf Of Si Chen
Sent: Thursday, 6 October 2005 10:06 AM
To: OFBiz Users / Usage Discussion
Subject: Re: [OFBiz] Users - VAT and rounding

Manuel, David, David, et al -

That's a pretty clever hack, and I'm glad it works for you.

It seems, though, that longer term we'll need to be able to support both the
US (tax added on top of price) and European (flat price including a
tax) formats.  It seems that it might not be so hard if we do it this way:
1.  Create a new tax type
2.  Additional code which calculates that tax for that type, including
correct rounding formulae
3.  An added field for OrderAdjustment which denotes whether it should be
added back to the order item's amounts (in the case of US sales
taxes) or is there just for informational purposes (in the case of
European/VAT), since the flat price includes the tax
4.  GL posting should work fine once the correct amounts in OrderAdjustment
or at most require a small change.

I think it might be nice to break the sales tax calculation code into a
master which calls several different routines or services depending on the
tax type, so we can keep the implementation separate or call an outside
service if it's available.

Just some general ideas.  Anybody up for doing this?

Si

Manuel Meyer wrote:

>Hi All,
>
>Further to what I've red and understood in this thread, here is what
>I've done in order to resolve my issue, which was more a rounding
>problem rather than a VAT problem:
>
>My big concern was that to sell a product of 120 VAT included, I could
>not enter a net amount with 2 digits of precision giving 120 after
>having applied 19.6% of taxes, or by entering a net price of 125.43 for
>a product, I got 150 for 1 product tax included, but 450.01 for 3.
>
>So, I've set all the currency entries in the DB to be numeric(18,6) (I
>am using postgres), and removed all the rounding made in the code
>(mainly in the OrderServices.getTaxAmount() and
>OrderReadHelper.calcOrderAdjustment()
>routines).
>I am only rounding when displaying amounts to the customer.
>
>And since that, I've resolved all my rounding and therefore VAT issues,
>and the site is a real Swiss clock :)
>
>The devil is in the details, so I might have missed something, but it
>looks like working so far.
>
>Best regards,
>Manuel
>
>-----Message d'origine-----
>De : [hidden email]
>[mailto:[hidden email]] De la part de T E Schmitz Envoyé
>: Wednesday, October 05, 2005 3:17 PM À : OFBiz Users / Usage
>Discussion Objet : Re: [OFBiz] Users - VAT and rounding
>
>David Garrett wrote:
>  
>
>>Out of interest why are there differences in the vatAmount vatAmount =
>>16.097 or 16.095 or 16.098 depending on which algorithm you use and on
>>whether you are a retailer or wholesaler.
>>    
>>
>
>The VAT Guide describes several rounding modes (unfortunately fails to
>be specific enough for invoicing at retailers).
>
>For our example
>gross = £123.45
>vatRate = 15%
>vatFraction = 0.1304
>==> vatAmount = 16.09788
>
>Invoicing at line level (B2B)
>a) round down to nearest 0.1p -> 16.097
>b) round to nearest 1p or 0.5p -> 16.095
>
>For invoicing at retailers, rounding *down* is forbidden:
>c) round to nearest 0.1p -> 16.098
>
>  
>
>>Thanks for the excellent summary.
>>    
>>
>
>Sadly, I seem to spend more time in accounting newsgroups than in Java
>newsgroups. ;-)
>
>
>  
>

 
_______________________________________________
Users mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/users


 
_______________________________________________
Users mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/users