Rounding issue causing customer over-billing

@WalkingBlackBear commented on Wed Aug 08 2018

Description

There is a calculation error happening, due to rounding. Here is the example, where I noticed it.

I have a 10% flat fee per item set up on our test hub, but on $0.50 in product, it’s charging $0.06 - when it should quite obviously be $0.05. Ditto the lettuce. The fee is on $5.25, but it’s charging $0.54, when even rounding up on $0.525 would be $0.53.

There are two fees in this example, and initially I thought that could have been the cause. One is set up as an enterprise fee for that producer’s products, (the fundraising fee is correct), the other fees showing (sales fee) are a flat 10% fee on every item. I thought maybe there was some case of fee adding on fee, but that math doesn’t work either. The shipping case I was testing was a voluntary 1% flat charge (the $0.06 shipping fee), which is correct on $5.75 - that would round up to 6 cents.

Then I discovered where the problem begins. It's here:

Let’s look at the tomatoes @ $0.25. 10% on each unit is a $0.03 fee, with rounding. So, while 2 of them is $0.50, which looks like a $0.05 fee should apply, the fee is added to each unit ordered individually, not the quantity as a whole. So the unit price = $0.25 (cost) + $0.25 (fundraising fee) + $0.03 (10% hub fee) = $0.53, times quantity of 2 = $1.06, not $1.05.

So, technically, the fee is correct - except any customer looking at it is going to say they were overcharged, because 10% of 50 cents is not 6 cents, but 5 cents.

From a financial accounting standpoint, the fee should be calculated on the total price of the quantity ordered. Otherwise 10% of $0.50 can turn out to be $0.06 (increasingly inflated with every additional item ordered) instead of the actual value of $0.05. So if I had 4 on the order instead of 2, the fee would have been $0.12 instead of $0.10. Now we're charging 12%, when our stated fee is 10%. This calculation error will continue to grow with every additional product ordered, at an effective rate (in this case) of 1% per item ordered.

Expected Behavior

The fee SHOULD be calculated in the cart, based on the final price of the total quantity ordered. So if someone orders $0.50 worth of tomatoes, and knows they are charged 10% on top of that, they will see a sales fee of $0.05 - not $0.06.

Actual Behavior

The sales fee is added - and ROUNDED - at the unit level, as seen in the price breakdown in the pie chart. So when buying quantities, the fee becomes inflated as the number ordered increases.

Steps to Reproduce

Create a product and set a price on it with a 5 at the end. (IE: $0.25)
Create an enterprise fee - flat percentage (10%) per item.
Order multiples of that item and checkout.
See inflated total fees on the final invoice.

Context

This bug causes customers to feel overcharged when the fees on the total of their order don't match what is showing on their invoice. This can lead to angry/unsatisfied customers.

Severity

bug-s3: technically there is a workaround if someone manually inspects every invoice and applies adjustments manually.

Your Environment

Version used: OFN-CANADA
Browser name and version: Chrome 68
Operating System and version (desktop or mobile): macOS High Sierra 10.13.6 (desktop)

Possible Fix

Likely the easiest fix would be to calculate fees to 3 decimal places and do any final rounding to the total on the invoice. In this instance, if the fee was shown as $0.025 instead of $0.03 and totaled on the invoice, it would have worked out to the correct $0.05. If a third item was ordered, the fee total would have been $0.075, rounded to $0.08, which is acceptable. On four items, it would come out to the correct $0.10 instead of the $0.12 that is shown in the example.

Related Issues/Other discussion

https://github.com/openfoodfoundation/wishlist/issues/329 https://github.com/openfoodfoundation/openfoodnetwork/issues/847 https://community.openfoodnetwork.org/t/tax-calculation-issue/547 https://community.openfoodnetwork.org/t/fee-calculation-error/1394

@myriamboure commented on Wed Aug 08 2018

Thank you @WalkingBlackBear for opening that bug! As we are all learning by peers'feedback, can I suggest 2 things:

Just a slight suggestion, the expected behavior is not that fee is calculated in the cart, I think I want to see if I order 10 unity of a product, at the end of the line on the shopfront, the total amount I have to pay for that product, including the fee. Expected behavior would be "The breakfown fee and total fee per item and on order are accurate and consistent on a user prospective so if user recalculate them the amounts are right". Same for "actual behavior". The fact that it should be calculated in the cart or not is a potential solution, not an expected behavior ;-) Also I disagree that the fee should be calculated in the cart, as we want to display it on product level for transparency reason... I think we should just not round prior to calculation, but we talk about solutions here ;-)
"From a financial accounting standpoint, the fee should be calculated on the total price of the quantity ordered." I disagree, from a financial prospective, in France at least it is legally compulsory to include any markup in each product unit price. It depends of the nature of the fee, when it is a markup, it's at product line. If that's a service fee then it can be, it depends. A problem we have in OFN is that we don't distinguish markups from commissions (but we should as calculation formula are different), and discounts.

Also on the potential solution you suggest, you don't tell what we do to the total at the item line level in the shopfront, here that displays 1.06 CAD. Should it display 3 decimals as well? Even in the invoice we need a total per item line, so we need to specify that. Maybe if we display 3 digits at the product breakdown level and fee calculation, then there shouldn't be more issues in calculation and we can keep on displaying 2 digits on item line totals as well as total order amounts? I think it's fine to see 3 digits in details like price breakdown, but might be a bit strange on user prospective to do that on totals.

I would like to suggest another potential solution to fix the problem, that might be harder, but maybe a dev can tell us :-) Part of me feels like we will need to do that anyway at some point as the way we do it know is not "clean"... Can the calculation be done always with the non rounded amounts, and then the rounding being done just for display purpose? At price breakdown and fee calculation level, but as well, at total price level on a given product line in the shopfront, in the cart, in the invoice, and totals for a whole basket on shopfront, cart and invoice, and totals for tax declaration/aggregation in alternative invoice model and tax reports. ??? Would love some inputs from devs on that...

@WalkingBlackBear commented on Sat Aug 11 2018

Thanks Myriam. I see where you're coming from, and that's where international perspectives are important.

I like your idea for doing the calculations on the raw values, but displaying the rounded ones. It allows for the unit-level accountability you need, but would still show the correct final amounts in the cart.

@daniellemoorhead commented on Mon Aug 20 2018

@myriamboure ☝️

@myriamboure commented on Wed Aug 22 2018

@WalkingBlackBear can you update then the issue description if you agree and understand my comments? On actual/expected behavior don't talk about potential tech solution, but move it to possible fix section and add the other option I suggested? As you are on lead on the issue I let you do the update. Then we will need to ask some feedback from devs about the 2 or 3 possible solution.

@kirstenalarsen commented on Wed Sep 04 2019

hi. I have a new report on this from users noticing it in Germany. I will point them to this discussion. Should / could we t-shirt size to see if it might qualify for 'top-5-s3s'? @myriamboure @WalkingBlackBear ??

image (2)

@kirstenalarsen commented on Thu Jun 04 2020

Wondering if we should be storing more like 5-6 decimal places in the database to allow for more accuracy in calculation of fees and taxes etc?

@luisramos0 commented on Wed Jul 08 2020

I can replicate this:

Would this be better?

There's still the implicit 0.28*2 = 0.55 Shall we display item price with 3 decimals 0.275?

Same question for the price breakdown in the shop page. What's the expected behavior here? 0.25 + 0.025 = 0.0275

And what about the price? Will it show 0.28? Maybe we should just make the price be 0.28 and charge 0.56 for 2 items?

For the examples shown above, we need expected numeric values for the shopfront, the price breakdown and the cart. My suggestion would be:

shop page - display price 0.28 GBP
price breakdown - 0.25 + 0.025 = 0.28 GBP
cart - item price 0.28 * 2 = 0.56 GBP Correct order total 6.35 GBP This basically means, we shouldn't allow hubs to sell items at prices like 0.275 with fees. If the fee makes the price a 3 decimals number, the sale price is the rounded value.

I agree with Myriam, there's a difference between markups from commissions, if this is TAX we can't just round it up like I am proposing.

We need an easy way out of this one... thoughts?

@RachL commented on Wed Jul 08 2020

@luisramos0 Is there an easy way to round only when displaying the number? As Myriam and Kirsten suggested above?

Price must always be shown with 2 decimals only I think, so we cannot show 3 decimal numbers. Yet those number must be rounded correctly.

In this case :

price breakdown - 0.25 + 0.025 = 0.275 GBP => this would be what is stored in database, but displayed in shopfront we would do 0.28
cart - item price 0.275 *2 = 0,55 => Displayed as 0.55
Correct order total 6.33 GBP

This way we can handle tax and fee with the same rounding strategy?

@luisramos0 commented on Wed Jul 08 2020

thanks for you feedback, for clarity I think we should ignore what's on the DB and get the acceptance criteria right. I am saying this because your numbers dont make sense to me: you suggest the item total to be 0.55 but then order total 6.35? did you mean 6.33? also, you say we cant display 3 decimals but in your example the item unit price you have 0.275.

Did you mean this? shop page - display price 0.28 GBP price breakdown - 0.25 + 0.025 = 0.28 GBP cart - item price 0.28 * 2 = 0.55 GBP Correct order total 6.33 GBP

@RachL commented on Wed Jul 08 2020

@luisramos0 Yes sorry I copy-pasted the total but I shouldn't have. The rest is correct I think. Let me try to put it in another way:

Why I mentioned the database, is that I would like to separate what the buyer sees and what OFN calculates. This is why my example has 0.275. That's what we calculate. Not what the buyer sees. The buyers sees 0.28.

I think this is what you have meant by

If the fee makes the price a 3 decimals number, the sale price is the rounded value.

But I don't understand why in your example 0.28 * 2 = 0.56 (I understand the math^^ but that's where the over-billing occurs, right?so the sale price should be 0.55, no?)

The buyer should see this in my opinion:

shop page - display price 0.28 GBP
price breakdown - 0.25 + 0.03 = 0.28 GBP => that's what the users sees, but we should be doing 0.25 + 0.025 = 0.275 rounded to 0.28
cart - item price 0.28 2 = 0.55 GBP => because we are actually doing 0.275 2
Correct order total 6.33 GBP

Is this possible?

@luisramos0 commented on Wed Jul 08 2020

ok, that's a valid proposal now :+1:

In my proposal the final price for the user is actually 0.28. There's no overcharging, 0.28 is the price. If the hub wants a lower price than can adapt the item price of the fee in the backoffice. This is just a proposal I dont have enough context to decide on this one.

The problem with your proposal is that hidden math problem/discount in the cart page: 0.28 * 2 = 0.55 Maybe we can make this better by making the price breakdown in your proposal: 0.25 + 0.025 = 0.28 GBP ?

@RachL commented on Wed Jul 08 2020

Maybe we can make this better by making the price breakdown in your proposal: 0.25 + 0.025 = 0.28 GBP ?

Maybe. But I think you would still have the weird feeling on the cart page. I'm not sure that in this case the buyer would think to go click the pie chart on the shop. I think that the cart page needs to show a breackdown to make it really clear. It will require some redesign work. My proposal would be to ignore this problem for now and only fix the overbilling problem. It's not perfect, but it would allow to break down all the cases more closely, which is not something we will do as part of an s3.

Thoughts?

ping @lin-d-hop @kirstenalarsen

@luisramos0 commented on Wed Jul 08 2020

yeah, makes sense. the other option would be to show 3 decimals on the item unit price and on the breakdown: shop page - display price 0.28 GBP price breakdown - 0.25 + 0.025 = 0.28 GBP cart - item price 0.275 * 2 = 0.55 GBP Correct order total 6.33 GBP

I think I like this one better because it's only the unit price that has the 3 decimals, its multiplication by the quantity will always be rounded, in this case 0.55.

@lin-d-hop commented on Tue Jul 14 2020

Adding my thoughts.... I guess there are two aspects to this: a) Stopping over charging b) Stopping confused shoppers (particularly if they ever think they are being over charged)

I am happy with both of the final proposed solutions suggested by @RachL and @luisramos0

The advantage of @luisramos0 proposal is that since the system allows a price/fee to be to 3 decimal places we will allow the shopper to see that in the price breakdown and item price... but I wonder if once we allow that we'll find that it comes up much more often than we expect with our tax and % fee structures and create a confusing experience for shoppers? If this only happened in the price breakdown I wouldn't be concerned but I feel we might hear about it more if it shows in the cart too.

In the case of @RachLs proposal I guess we'll still see users getting in touch wondering why they were charged a penny less, but if this always appears that they are being under charged we won't hear about it often.

Soooo..... to be honest both solutions are fine by me (which is not useful).

Things that would persuade me either way: 1) If one is more work than the other 2) If most carts will now show line items to 3 decimal places then I would prefer to just round earlier as per @RachLs proposal.

@luisramos0 commented on Wed Jul 29 2020

ok, thanks for your feedback Lynnne. It's the same effort for both options.

I still prefer 3 decimals on the item price, specially in the cart: Basically I think 0.275 2 = 0.55 GBP is better than 0.28 2 = 0.55 GBP

@RachL can you make the call?

@kirstenalarsen commented on Wed Aug 05 2020

My accountant reckons this suggestion was the best

"If this fix from the thread can be done, this seems reasonable:

Possible Fix Likely the easiest fix would be to calculate fees to 3 decimal places and do any final rounding to the total on the invoice. In this instance, if the fee was shown as $0.025 instead of $0.03 and totaled on the invoice, it would have worked out to the correct $0.05. If a third item was ordered, the fee total would have been $0.075, rounded to $0.08, which is acceptable. On four items, it would come out to the correct $0.10 instead of the $0.12 that is shown in the example.

It is a kind of weird system where the 10% is calculated on an itemised basis rather than reading the total of the order. Ideally, the system would read the order value and not the items for this charge. The terms and conditions should include a line on how rounding of the fees is done (rounded up or down to the nearest cent per order)."

Interesting comment on the terms of service - that does seem a good way to cover ourselves which ever way we go. @luisramos0 @lin-d-hop @RachL

@luisramos0 commented on Wed Aug 05 2020

@kirstenalarsen maybe we should go back to acceptance criteria based on the example proposals.

Is this your accountants suggestion?

shop page - display price 0.275 GBP price breakdown - 0.25 + 0.025 = 0.275 GBP cart - item price 0.275 * 2 = 0.55 GBP Correct order total 6.33 GBP

and for 3 items: shop page - display price 0.275 GBP price breakdown - 0.25 + 0.025 = 0.275 GBP cart - item price 0.275 * 3 = 0.825 GBP Correct order total 6.61 GBP (rounded up from 6.605)

@luisramos0 commented on Sun Aug 16 2020

I think this is not ready for dev. Can we please move this to the bug backlog until we have a final decision on the solution?

@Erioldoesdesign commented on Wed Aug 19 2020

tl;dr I like the three decimal places if it's majority food hubs as the user here and not shoppers/eaters. They are more interested in this level of detail and number nuance than 'typical shoppers/eaters'. And it sounds like it needs to be on totals/invoices first, and then if this doesn't solve the problem, we look at how we display this more granularly in other places. However, three decimal places is...kind of hard for human brains (and sometimes calculators) to comprehend.

Whatever UI is then implemented and where it goes (in the pie breakdown or inline in columns as you add items) and in-browser based invoices, there needs to be some kind of tooltip or place to find out what it means in detail. Which I would suggest as a text link that opens up docs/user info in a new tab/window and we can leverage some of the number examples from the issue comments here to explain why we display three decimal points for accuracy.

Which links to this via accountant/Kirsten:

The terms and conditions should include a line on how rounding of the fees is done (rounded up or down to the nearest cent per order)."

Longer thoughts:

Some user assumptions I've gathered from this issue.

This is 99.9% food hub as buyer problem which includes complexities like tax, cost for price point etc.
The shoppers/eaters are unlikely to 'split hairs' on half of pennies/cents on the rounding up. Which is good, because showing humans digits like '0.275' is likely to confuse people enough that they get in contact with customer support and/or just don't shop and/or shrug and if it doesn't get out of pennies/cents they'll just purchase for a 'good price'

I was having trouble figuring out where this problem was coming from because the UI examples are both /admin/orders and /shop (with the pie chart) and also...whether everything always has fees or not or it depends on whether your an eater/shopper vs a foodhub buyer...and can vary because producers can add fees and so can foodhubs etc etc.

I think I'm still unclear on who needs to be notified where and at what point because it seems to be an issue that runs across multiple pages and have many varients depending on fees associated with items. So I don't think my thoughts here have 'cracked the puzzle' but hopefully added a bit of where we might see hiccups with users implementing 3 decimal places...

@lin-d-hop commented on Wed Aug 19 2020

Thanks @Erioldoesdesign To clarify one of your assumptions - This is shopper facing.

If 3 decimal places is enough to make a shopper stop shopping then I change my vote to 2 decimal places everywhere the shopper can see.

@Matt-Yorkley commented on Wed Aug 19 2020

Is this about displaying 3 decimal places, or making sure the underlying calculations are done with 3 decimal places for higher accuracy?

@luisramos0 commented on Wed Aug 19 2020

Both my proposal and Rachel's do not involve re-calculating things, just displaying, the 3 decimal places are already available.

@luisramos0 commented on Fri Aug 21 2020

I moved this back to S3 prioritized bugs until ready for dev.

Maybe we can move another S3 to Dev Ready?

@Erioldoesdesign commented on Tue Sep 01 2020

So in the interests of lean design resource I think we should do this:

The solution that @luisramos0 suggested be developed and released when ready.
We do very lightweight short 15-20 min conversations when it is live with 3-5 users around how they use it (if at all) We'd need one user that has already indicated that they use/notice unit pricing and it informs their purchase process.
We do our best lightweight effort to notice if and when users might not complete an order because of the 3 decimal places. This will be difficult to track but if Customer Support are in conversation with anyone and it's raised then we can tally. (It's more likely the people who will drop off and not buy because of this will just abandon and we'll never know' No time limit on this being done asap.

If we get indication people are struggling we do this follow-up improvement:

We add a tooltip/info message that says something like 'We display three decimal places for some prices to be able to calculate an accurate total price for you.' I would like a 'find out more' text link in this that takes a user to docs with some of the examples from this issue. Note: I do not expect users to read this or click on it.

This is only likely to help minimally but! my main hunch is that most people don't really notice until 1 cent becomes closer to 5 cents discrepency so as long as in the calculation things look like the amount people want to pay, we can take the risk.

But just saying three decimal places won't magically help most regular people understand whats happening but it's likely most regular people also, don't care unless they are more than 1 cent out of pocket.

@lin-d-hop commented on Tue Sep 01 2020

On 2) Is this something we should put out to support teams to ask if on the phone to an established user?

On 3) Very interested in this and how we compare to other things eg 'how many shoppers drop off because mobile checkout is ugly?'.... How do we differentiate on different clunky things? Big thinking required (my head hurts).

@Erioldoesdesign commented on Tue Sep 01 2020

@lin-d-hop re. 2) yes please, that would be immensely helpful, this user test wouldn't be a complicated one but I should write up a guiding script with non-leading questions. Essentially, on the page where the users see the 3 decimals, asking 'Is there anything on this page you don't understand or isn't clear?' and basically wait and see if and when they name the 3 decimal places as that.

Number 3) is mostly measured by the above user conversations... as you say, it's also impossible to tell what tiny piece of the UX/UI will be the 'straw that breaks the users back' and has them drop off. Maybe something like crazy egg or another heat mapping tool could help?

@Erioldoesdesign commented on Tue Sep 15 2020

Just to be extra clear, from my pov this is ready for dev, take a look at my comment 2 above for how we monitor what 3 decimal places does with users.

@luisramos0 commented on Fri Oct 02 2020

Hello, I came back to this with the plan to implement my proposal as agreed BUT now I dont think I can replicate this anymore :see_no_evil:

AFAICT this looks like there's no big issue for the customer. The unit prices are rounded up and the total is correct.

So I think we are back to the original problem described by @WalkingBlackBear in the description where in the admin order edit we see:

And this may create doubts for the manager because 3 items of an item with price 1.75 cannot possibly give me a feee of 0.54, but it does because 1.75 10% is 1.925 that is rounded up and used for the total calculation. Then we multiply by the quantity 3 and get (1.933)=5.79 which gives us fee total of 0.54 (5.79-5.25).

So instead of 1.75 10% 10$ = 5.78 we get (1.75 10% ).rounded (and displayed to the customer as unit price! 1.93) 3 = 5.79

I am sorry, I am not sure what to do here? From a customer perspective I think it's all fine. For the manager it can be confusing. Maybe we can add some info to the adjustments table on the admin edit page to clarify the fees are applied on a per item basis and rounded before being multiplied by the quantity?

@luisramos0 commented on Tue Oct 06 2020

Looking at this again I thought maybe @filipefurtad0 (or anyone else who is willing) can you give this situation a try in a staging server? If you confirm what I say in my previous comment we should probably move this back to the "Bug Backlog" until we decide what is the solution for the admin orders edit page.

@RachL commented on Fri Oct 09 2020

@luisramos0 I'm not sure to understand on the shopper side: did you take the same example as above? because the whole discussion was about 0.28 x 2 = 0.56 being wrong because it is not 0.28 but 0.275. Which causes the shopper to pay more than what he should. The screenshot you've shared looks like you did manage to reproduce it?

@luisramos0 commented on Fri Oct 09 2020

My initial attempt at this (see my first comment) is that the sum was 6.33 and not 6.35. It was incorrect, the sums didnt match. Now I dont see that, I see 0.28 x 2 = 0.56 and the total 6.35, the numbers match.

"Which causes the shopper to pay more than what he should" If the item price is 0.25 with a 10% fee, I dont think there's a "correct" price: we can decide we show 0.275 (it's not normal to have prices with 3 decimal places) or 0.28 (we need to make sure the manager understands the rounded up price).

As far as I understand, I think it's acceptable to show 0.28 on the shopfront as the item price and use that for all the other calculations like line item total and order total. I think this is fine as long as we make it clear for the manager in the backoffice that the item at 0.25 with the fee of 10% will have a price of 0.28. This is the logic I used but I understand that you may want to change it :+1: for example, saying you want the item price to be 0.275 and only round prices at line item total and order total level. That's open for discussion, the first approach I describe looks like it's going to be much easier.

I think this needs to go back to the S3 backlog as it is not ready for dev right now.

@filipefurtad0 commented on Mon Oct 12 2020

Hi everyone,

I had a look with at this issue on a staging server, with three similar scenarios, varying only the item values and the value of the fee. In each scenario, the values obtained by the app are compared by those obtained by external caclulation (yellow underlining).

1) Overbilling - and reproducing the original post from this issue

Transport Fee: 10% prod A - 0.25 x 2 items prod B - 1.75 x 3 items

2) Underbilling

Transport Fee: 15% prod A - 0.88 x 3 items prod B - 1.75 x 2 items

3) Overbilling with smaller values (making use of more significant digits)

Transport Fee: 4.5% prod A - 0.88 x 3 items prod B - 1.75 x 2 items

Summary

I can reproduce the original issue, but I cannot observe a scenario in which the sum shown in the cart page is incorrect (as you observed in your first comment @luisramos0 ). In all three scenarios above the sum of values from line items are correct on the cart page (scenarios 1, 2, 3)
under certain range of values, the values get rounded down, causing "under-billing" and making the customer to actually pay less than expected (2)
the app allows for both the prices and enterprise fees to have values as low as 0.01 (on the respective units, EUR or GBP and %). Scenario 3 shows an example where overbilling occurs as well, having fees with decimal percentages.

Hope this sheds some light on potential ways to address this issue.

@Erioldoesdesign commented on Tue Oct 13 2020

Just leaving a note here in reference back to a comment @luisramos0 made re. making sure in the back office, users understand that price shown e.g. 0.28 is a rounded up version of 0.275.

Sounds like perhaps theres a little lightweight design to discover the best places for this to be placed in the UI/Back Office so it's understood by users.

But standing down until a product manager signals for design eyes on that 👀

@Matt-Yorkley commented on Sun Jan 03 2021

Just a quick note; when rounding in relation to taxes, Spree specifically uses this implementation:

def round_to_two_places(amount)
  BigDecimal(amount.to_s).round(2, BigDecimal::ROUND_HALF_UP)
end

but I think there are some places in the codebase where we handle rounding but don't use the exact same rounding strategy...

openfoodfoundation / wishlist