← Back to team overview

openerp-expert-accounting team mailing list archive

Floating point precisions, balanced entries and VAT included reflexions

 

Hello guys.
This mail is primarily targeted to the openerp-expert-accounting so please
reply only on that list. it's just that I also wanted to broadcast the
initial message here.
So we basically extended the reflexion from that bug:
https://bugs.launchpad.net/openobject-addons/+bug/452854 . We are here only
dealing with the mono-currency issues as we believe the multi-currency issue
is unrelated and would be addressed properly in 5.2 already with write-off
lines.

I'm forwarding here the revised mail of our trainee Sebastien Beau and then
commented on it at the end.
(BTW, I think Sebastien will deserve entering some lists after those 6
months of intense OpenERP work, let's say it's just a border line case, but
if we want those lists to stick with qualified content OpenERP S.A. would
have no excuse to miss, then we should elaborate a few entries rules to
avoid having the list as bloated as the forums and thus impossible for them
to track, thus missing the point of the experts lists compared to the
forum.)

I also notify you that Antony Lesuisse from OpenERP told me that OpenERP
S.A. is committed to work on the tax included system for 5.2 provided we
guys first draw a clear specification. Well, that's a "chicken or egg" issue
as Launchpad is bloated of un-listen specs and we can only afford start
dedicating time to spec once OpenERP S.A. take such a public stance upon
what they will listen or not listen too/integrate/not integrate.

And as you will see, that accuracy problem, could be very much related to
VAT included system, at least is some use cases such as our own which is
ecommerce integration.

So here is Sebastien's mail:

"
IN THEORY, HERE IS HOW YOU CAN COMPUTE VAT UPON INVOICE, AT LEAST IN FRANCE:

when VAT is computed two solutions are possibles (example with a rate =
19.6%):

********** 1) you round the sum of the exact line values:
*****************************************


Product 1 : price VAT included = 4,61 | VAT = 4,61*(1-1/1,196)
Product 2 : price VAT included = 6,34 | VAT = 6,34*(1-1/1,196)

On the invoice :
total VAT included = 10,95
total VAT excluded = 10,95/1,196 = 9,16
VAT total = 4,61*(1-1/1,196) + 6,34*(1-1/1,196) = 10,95*(1-1/1,196) = 1,79

In the sale journal, we have two solutions:

a) the first solution: with products move line grouped:

                     | credit | debit |
      Products          9,16
       Taxe             1,79
       Clients                   10,95

Works perfectly, but you might not always be able to group the invoice
lines.


b) the second solution: each product having their own accounting line
(possibly because they should go to different accounts):

Product 1 : untaxed = 3,8545
Product 2 : untaxed = 5,3010


                          | credit | debit |
      Product 1         3,86
      Product 2         5,30
       Taxe              1,79
       Clients                     10,95

As you can see I put 3,86 in the first product and NOT 3,85 (untaxed amount
= 3,8545) !!
I have to add (or subtract) some cents to match untaxed amount 9,15!!



********** 2) you sum the rounded value of each line:
*****************************************

Product 1 : price VAT included = 4,61 | price VAT excluded = 3,85 | VAT =
0,76
Product 2 : price VAT included = 6,34 | price VAT excluded = 5,30 | VAT =
1,04

total VAT included = 10,95
total VAT excluded = 9,15
VAT total = 1,80

In the sale journal:
we have


                         | credit | debit |
      Product 1        3,85
      Product 2        5,30
       Taxe             1,80
       Clients                   10,95



Those two solutions are acceptable.
Furthermore, some external sale system might select
different solutions and we need to accomodate them. For instance OSCommerce
and Magento ecommerce
seems to select solution 1) only, and we need to accomodate to it in OpenERP
to be able to show our customer
and accountant invoice and move lines that matches their ecommerce sale
order.



NOW LET'S SEE HOW OPENERP IS WORKING:

Each line in the invoice openERP make an entry in the sale journa (even if
they can be grouped sometimes eventually).
So in our case we have two accounting moves: one for the product 1 and one
for the product 2

So first remarque is that OpenERP make a rounded of EACH line in the sale
journal!!

we have in the sale journal two product with this value untaxed:
Product 1 : 3,85
Product 2 : 5,30

Total VAT excluded = 9,15 (like in solution 2) )

So as now everybody knows that OpenERP uses method 2) to compute VAT, let's
try to put a price accuracy of 4 and buy 2 products!!

Product 1 : price VAT included = 4,61 | price VAT excluded = 3,8545 | VAT =
0,7555
Product 2 : price VAT included = 6,34 | price VAT excluded = 5,3010 | VAT =
1,0390

If you try to buy those 2 products you will not be able to validate the
invoice!!!

->Why??
In the invoice:
total VAT excluded = 9,1555 (rounded 9,16)
VAT = 1,7945 (rounded 1,79)
total VAT included = 10,9445 (rounded 10,95)

Everything look perfect in the invoice!!

Yes in the invoice everything seems ok... But not in the sale journal!!

As we said before OpenERP will put in the sale journal for the product 3,85
and 5,30 (3,85 + 5,30 = 9,15 and not 9,16 as we can suppose when we reading
the invoice!!)

BUT for the 1.79 will be set for the VAT!! Here is the big problem: the
value has to be 1,80 for entries to be balanced!!

Indeed VAT is computed for each line without been rounded; it's rounded only
at the end to 2 digits to be put in the database!!
And the sale journal looks like this

                          | credit | debit |
      Product 1         3,85
      Product 2         5,30
         Taxe             1,79
       Clients                     10,95

3,85 + 5,30 + 1,79 = 10,94 != 10,95 OpenERP will not allow you to validad a
non balanced entry ;) (well, in 5.0.7 it would in this case because we let a
bad patch come in; but price_accuracy wasn't working anyway)

This is exactly the bug reported here:
https://bugs.launchpad.net/openobject-addons/+bug/452854


So the fix is "simple", We CANNOT use the first method for the VAT value and
the second for the VAT exlcluded value! We should use ONLY ONE method !



Two solutions are possible :

- switch for solution 1), and so add or substract some cents in the price of
the product in the sale journal

- stick to solution 2) to compute the VAT, so that would mean change how the
VAT is rounded.
This is ok but I think solution 1) is simpler and would resolve the currency
problem.
 [...]
"


Raphaël's remark:

Sebastien, I skip your subsequent reasonning about currencies here for now
because I'm not sure you got that one right, we should discuss it first to
avoid eating people's time if we are unsure about our claims (I was).

So as we see, the problem only shows up when we try to have a higher
floating precision here. With price_accuracy=2, the problem just don't shows
up.
I'm quite sure it would be hard to switch to solution 1) and we are almost
sure that we cannot do it here in Brazil at least because the electonic
invoice specification specifies we should have exactly the same amounts
between invoice lines and accounting lines which is not always true with
1).. I also start to believe ecommerce just do that because it's simpler and
because they don't give a shit about the sbusequent accounting, especially
if product lines can't be grouped.

Does it mean we should force VAT rounding to 2 digit before summing it? May
be, just not sure yet, please accountants comment on that.


But why is that important after all if it just works with price_accuracy=2 ?

In https://bugs.launchpad.net/openobject-addons/+bug/452854 lasts comments,
people seem to agree we need a price_accuracy option but it shouldn't
propagate to accounting as it does now and stick to only product price
accuracy.
Well that's probably very true.
So most of the time, people could stick to 2 digit precision almost
everywhere, except for those refactored price floating precisions.


Now, to integrate OpenERP properly with say ecommerces or other external
sale systems, we would absolutely need a way to reproduce VAT included
amounts of solution 1) into OpenERP. Indeed, If I buy a product 9 euros in
an ecommerce, I need an OpenERP invoice with 9 euros, not 9.01 euros (what
it would do if we use price_accuracy=2); and I also need entries to be
balanced of course. For us increasing the VAT excluded price accuracy though
the price_accuracy option was definitely a way to show the same VAT totals
but then we run into the  unbalanced entries bug mentionned.

We need more investigation, but may be a solution would be somthing like:
we do increase the VAT excluded price precision at least to use it as the
base to compute the VAT included line totals, at least in the VAT included
modules or when using a line with VAT excluded price if VAT included is
refactored into the core as I suggest.
And then, may be also force the VAT computation to be rounded at 2 digits at
every line before the sum. We would thus have similar VAT line amounts and a
proper VAT total (we would not match exactly VAT exluded prices of the
ecommerce invoice, but we don't care as much here).
I'm not sure if that would need to be done in the tax_included module only
or generally. But it might be done this way always because as Sebastien
explains it seems that there is an issue with VAT computation even if it
doesn't show up as long as you stick with 2 digit floating precision
everywhere.

What do you think about it? Does it demonstrate 4,61 and 6,34 are irrational
numbers just like Pi or sqrt(2) ?
-> we could use Sebastien's case for an executable test case such as
OERPScenario or embedded test.


Raphaël Valyi and Sebastien Beau
http://www.akretion.com

Follow ups