[Epic][Pricing][MOC][B to B] : Method Of Calculating a cart total without discount

[RosaBenouamer]

→ As a merchant, I want the amount of my customer's

US linked :
Lot 1 :

• [US-Pricing][MOC][Without rounding][B to B] : MOCCT-001 : Buy one article #32592
• [US-Pricing][MOC][Without rounding][B to B] : MOCCT-001 : Buy one article - Connection to the BDD #34160
• [US-Pricing][MOC][Without rounding][B to B] : MOCCT-002 : Buy one article with variable quantity #32274
• [US-Pricing][MOC][Without rounding][B to B]: MOCCT-002 : Buy one article with variable quantity - Connection to the BDD #34163
• [US-Pricing][MOC][Without rounding][B to B] : MOCCT-003 : Buy one article with variable VAT #33684
• [US-Pricing][MOC][Without rounding][B to B] : MOCCT-003 : Buy one article with variable VAT - Connection to the BDD #34164
• [US-Pricing][MOC][Without rounding] [B to B]: MOCCT-004 : Buy several articles #32602
• [US-Pricing][MOC][Without rounding][B_to_B] : MOCCT-004 : Buy several articles - Connection to the BDD #34165
• [US-Pricing][MOC][Without rounding][B to B] : MOCCT-005 : Buy several articles with shipping #33691
• [US-Pricing][MOC][Without rounding] : MOCCT-005 : Connection to the BDD #34166

[cruftex]

@RosaBenouamer

Just saw your issue. Thanks for looking into that!
I started writing some specs on what to do about the many calculation and rounding issues.

I want the amount of my customer's cart to be calculated before taxes and discounts are applied so that my invoices are correct from an accounting point of view

That does work only if you have prices excluding tax. If the customer gets shown the prices including tax, you need to sum up the cart total with prices including tax to minimise the rounding error. So the procedure needs to be different depending on the VAT included / excluded setting. Example for VAT included pricing calculation:

• There are 119 cart items (positions) with a total, tax inclusive price, of 0,01 Euro
• The sum total is 1,19 Euro
• The invoice VAT at 19% is 19 cents when calculated from the sum total (correct), the calculation is VAT = sum total / (rate + 1) * rate
• The invoice VAT would be 0 cents when calculated for each position (wrong)

[RosaBenouamer]

Hello @cruftex,

we are yet working on the new pricing :-)

I try to understand you example 👍
You say you have : 119 cart items (positions) with a total, tax inclusive price, of 0,01 Euro
==> 0.01 is the price tax inclusive for only on item ?
Isn'it ?
If it is how do you find 119 for the total ?

[cruftex]

@RosaBenouamer

0.01 is the price tax inclusive for only on item ?
Yes

Is is 1,19 Euro, of course. I correct it

[cruftex]

More formal in pseudo code:

unit_price = inclusiveVat ? displayedVatInclusiveUnitPrice : displayedVatExclusiveUnitPrice
position_price = qty * unit_price
total_product_price = position_price1 + position_price2 + ...
if (inclusveCat) {
   vat_inclusive_price = total_product_price
   included_vat = round(totral_product_price / (1 + vat_rate) * vat_rate)
} else {
   included_vat =  round(total_product_price * vat_rate)
   vat_inclusive_price = total_product_price + included_vat
} 

My recommendation: remove the tax_excl and tax_incl prices on the position level and likewise in the order details. There is only one price which needs to be displayed and calculated with on the level of a position. This price is either tax inclusive or tax exclusive.

The shipping costs need to be included as well in the total.

If there are different tax rates, you need to do the sum total for each tax rate each.

Rounding:
For VAT calculation, you must only round once

Discounts:
There is no accounting practise on how to apply a discount. That is an agreement between the merchant and the buyer. But logically, a per order discount makes sense to be applied at the end and not on position level.

[RosaBenouamer]

@cruftex this point is very interesting.
I understand that no sens to have 0 on each item :-)
When you talk about VAT by item it is on invoices ? or somewhere else ?

[cruftex]

When you talk about VAT by item it is on invoices ? or somewhere else ?

I don't understand the question.

Prices need to match everywhere, in the BO, FO and on the invoice.

The VAT rate is defined on product level.

Whether the pricing is VAT inclusive or VAT exclusive is, as far as I know, defined per customer group.

[cruftex]

@RosaBenouamer
I now put my (in)complete write up for the topic here:
#32969

Hope this is helpful. Thanks for working on this!

[RosaBenouamer]

@cruftex thank you for your feedback. I will take a look :-)

[RosaBenouamer]

Hello @cruftex,

"Discounts:
There is no accounting practise on how to apply a discount. That is an agreement between the merchant and the buyer. But logically, a per order discount makes sense to be applied at the end and not on position level."
If we apply the discount on the order. Wich rate could we use to calcul vat_rate ? Do you have an idea ? Indeed the rate is by product not by order :-)

[cruftex]

@RosaBenouamer
Not sure whether I understand the question correctly. You need to show the discounted invoice total per tax rate.

[RosaBenouamer]

@cruftex :
"My recommendation: remove the tax_excl and tax_incl prices on the position level and likewise in the order details."
What I understand is to take off all the unit price is useful because you are calculating on the line and it avoid some incoherence.

• "In the order details" for instance take off Base price and Unit price because 6.33 * 2 = 12.66 which is different of Total 12.67

• "remove the tax_excl and tax_incl prices on the position level" do you talk about all other place in FO and BO like for the same reasons ?

here

[cruftex]

My basic rule is: If something is displayed, that amount is used further calculations.

Options I can think of:

• Correct calculation of the total based on the rounded and displayed unit price; 6.33 * 2 = 12.66
• Calculate and display with increased precision (that is what I was talking about "offer precision" in my write up): 6,333 * 2 = 12.666

"remove the tax_excl and tax_incl prices on the position level" do you talk about all other place in FO and BO like for the same reasons ?

Sorry, the rule should better say:
"Remove separate tax_excl and tax_incl or included taxes prices on everything which is not the invoice total and either show the tax_excl or tax_incl price"

With the invoice above, positions are okay since there is only tax exclusive prices.

What is a problem though, is the breakdown into Products and Shipping. The separate total tax will not add up.
For accounting purposes you need the breakdown into tax rates not product types.

[Hlavtox]

Guys, in Czech republic, it's pretty standard to display both prices on the product, with and without VAT. We should find out what is the proper way of calculating multiple quantity price in this case.

[cruftex]

@Hlavtox: Can you please give examples? I saw in your shop you are only displaying VAT incl. price, e.g.:
https://www.trendo.cz/granitove-drezy-s-odkapem/25236-kuchynsky-drez-franke-basis-bfg-611-78-onyx-1140285169-7612981053680.html

[cruftex]

@RosaBenouamer
I cannot really follow the user stories. I don't know what Step_CP means or "Fixed VAT" for example and MOCCT-001 is not accessible. Do you want help and feedback?

Regarding the user stories, I am already confused with the first one:

User Story : As a merchant I need to be able to correctly calculate the price of a product depending of the combination of the product.

That is kind of a functional description wrapped in a user story.

What about this:

As a merchant, selling to consumers, to one country, I want to enter product prices with VAT included

As a merchant, selling to companies, I want to enter product prices without VAT

[Hlavtox]

@cruftex I don't have it on our stores, but some examples from TOP 50 stores in our country:

• https://www.alza.cz/iphone-13-128gb-cerna-d6731111.htm
  • Product is 17990 | 14868, 3 pieces
  • Cart shows 53970 | 44603,31
  • Cart total is calculated from the VAT inclusive price with the second price calculated from that total.
  • But, if you calculate it from product's tax exclusive price, 14868 * 3 = 44604
  • What is correct? A business customer looking at VAT inclusive prices could say that the total is wrong.
• https://www.smarty.cz/Apple-iPhone-15-Pro-128GB-modry-titan-p152663
  • Product is 29990 | 24785, 25 pieces
  • Cart shows 749750 | 619625
  • Cart total is calculated from displayed product prices.
  • But 749750 / 1,21 = 619628,09
  • What is correct? Both, but I don't think the financial bureau would like this calculation.
• https://www.mironet.cz/samsung-soundbar-hws40t-zg-cerna-2kanalovy-soundbar-100-w-bluetooth-opticky-vstup+dp479160/
  • Product is 1290 | 1066, 25 pieces
  • Cart shows 32250,13
  • Cart calculated from the VAT inclusive price of the product, without tax is not displayed
  • But if you calculate 1066 * 1,21 * 25 = 32246,5
  • What is correct? A business customer looking at VAT inclusive prices could say that the total is wrong.

[RosaBenouamer]

Hello @cruftex,

Thank you for your feed back.

@cruftex : here the googlesheet with futur formulas. I make you a copy. The document is not finished.
https://docs.google.com/spreadsheets/d/1IhWe05v7LOz5dd4azCCawJ1c7-ooFma1SCuKvcdWnJM/edit#gid=1935476460
I put your example of the 28 June. I totally agree with you results.

The formula are described in the specification not finished
https://app.gitbook.com/o/-MAz0PPl5s9ulE9xyliu/s/eRh5ljXXvELkmmdiRmg8/functional-documentation/business-rules/pricing/method-of-calculating-a-cart-total.

If you are available we can plan a meet so I can present you the googlesheet so we can validate some concret example.
Are you ok ?

[RosaBenouamer]

@cruftex
I understand your point of view. But why the price est 1290 instead of 1289,86 but the result avec 2 decimals ? 32250,13
#32227 (comment)

Either we have to display 1289,86 as a unit price and so 1066 * 1,21 * 25 = 32246,5 is ok
or if for a reason I ignore we prefer to display 1290 I imagine the rounding for the result has to be 0 decimal to and so the result will be 32250

Isn't it ?

[RosaBenouamer]

Moreover today for b to b we have
Solution 1 :

The calcul seems to be coherent for b to b but is inconsistency with b to c

indeed for b to c

But if you calculate 26 650 * 1.21 = 32 246.5 not 32 250 as display

[RosaBenouamer]

Solution 2 : The alternative to have a coherent display in b to c is

but indeed as you say @ cruftex it could be surprising for the b to b customer to have 32 247 instead of 32250

[RosaBenouamer]

Solution 3 who implies difference between difference beteween TTC in B to C and B to B (not a good idea in my mind) is an hybrid solution

b to c

and b to b

[RosaBenouamer]

• [Epic][Pricing][MOC][B to B] : Method Of Calculating a cart total without discount #32227 (comment)

[cruftex]

@RosaBenouamer

Unfortunately, you added three times the same spreadsheet. Was that intentional?

But why the price est 1290 instead of 1289,86 but the result avec 2 decimals ? 32250,13

Oh, I missed that. I quickly checked that in the shop, when I put the product in the cart and then I see this:

290 x 25 is not 32250,13, also 1290,01 x 25 is not 32250,13. So the shop is calculating not transparently. IMHO this shop is also doing it wrong. Therefor a good example how not to do it.

[RosaBenouamer]

@cruftex I correct the wrong spreadsheet :-)

Dont't worry for the 1290,01 I think that the question is the same :-)

[cruftex]

@RosaBenouamer

I quickly put that in LibreOffice Calc, when I copy it here, I can actually include it with Markdown tables. What about this:

Price with VAT	VAT	Unit Price	Count	Total	incl Vat	excl VAT	included VAT
Y	21,00 %	1.290,00	25	32.250,00	32.250,00	26.652,89	5597,11
N	21,00 %	1.066,00	25	26.650,00	32.246,50	26.650,00	5596,50

[RosaBenouamer]

@cruftex so you are for the third solution even if incl Vat are different.
Today I was on the second solution
I will check with an accountable the best solution :-)

[cruftex]

@RosaBenouamer
The accountant will tell you most likely:

• You have to calculate with the price that you offered to the customer (which is either including or excluding VAT). That means a price of 6,66 Euros is 6,66000000000.... Euros and not, for example 6,66123 Euros.
• You have to minimise (rounding) errors

There is a problem, though:
The unit price in PS can have 6 decimal digits. The displayed price has (typically) 2 digits, while the calculation is based (currently) on the set unit price with 6 digits. This behaviour and some other inconsistency leads to the most errors and confusion.

To get a consistent calculation, the calculation needs to be done with the prices as displayed.

But, if you have low unit prices, you might want to calculate with more precision than two digits. If this is the case and your prices have more precision, you need to display more than two digits, so the calculation is transparent to the customer. Think about gasoline prices. Just now, the litre price is at 1.956 Euros.

So, there needs to be a setting for precision of the displayed unit prices, or, prices need to displayed and used exactly as entered.

Actually, I would recommend to first write down a collection of user stories in natural language that can be understood by everyone, before writing down functional specifications, meaning, before writing how things should be done, we should know all the "whys".

[RosaBenouamer]

@cruftex : this sentence
"To get a consistent calculation, the calculation needs to be done with the prices as displayed." make totally sens and I agree with that.

"Actually, I would recommend to first write down a collection of user stories in natural language that can be understood by everyone, before writing down functional specifications, meaning, before writing how things should be done, we should know all the "whys"."
==> yes it's the goal of use cases :-) Take a look here
If they are not clear I will try to add more explanation in "natural language" :-)

[cruftex]

@RosaBenouamer

==> yes it's the goal of use cases :-) Take a look here

I get a 404 :(

[RosaBenouamer]

@jolelievre do you have an idea about 404 error. Is the Link ok for you ?

[RosaBenouamer]

@cruftex I have create a discussion to talk about your point 👍 #34262

[RosaBenouamer]

This ticket is blocked by:
#34262
#34327