Fee calculation error

There is a calculation error happening somewhere in checkout. 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.

I thought maybe it was charging the 10% fee on top of the fundraising fees tacked on to those items, but that didn’t work out right either. Or it would be 10% of $1.00 and $6.00, respectively.

When it comes time to audit, these variances would cause us some hot water, since we’d be over-charging based on our stated policies. A penny per product per order per OC per month per year can add up to significant overages.

Anybody able to look behind the scenes and see where this error is coming from - or if it’s somehow unique to this test OC/order?

@sstead @MyriamBoure @tschumilas I would guess that one of you might be most familiar with fees and calculations and able to see what’s happening here - is it a bug or some thing that is not obvious to user? If / when confirmed that it is a bug - @CLFC could be a good opportunity for you to try your hand at a beautifully documented bug report - https://github.com/openfoodfoundation/openfoodnetwork/issues/new

where is the OC @CLFC - which instance? and/or could you provide a screenshot of the OC setup so we can see the fees and where applied?

@CLFC - what is the enterprise name - I can’t find it. Happy to have a look and see what’s up - but I can’t find the above example. (@Kirsten - chris works with the Cloverbelt Local Food Cooperative in NW Ontario - so this is on the Canadian instance … and we are very happy to have him!)

Never mind @CLFC - I see it now. (My mother would have said that the devil must have been sitting on it :smile:) So I’ll take a closer look - but one thing I notice right away is that you have left tax on the fee blank. would this make a difference? Sometimes people charge tax (so in Ontario - HST) on the fee. If there is no tax charged on the fee, you select ‘zero rated’ (not taxable basically). this might matter - but I’ll look closer.

there are two fees and i’m wondering if the order they are calculated in would make the difference - if they are cumulative?

@tschumilas - I would have thought blank and zero rated would be the same? If not, maybe that should be a mandatory field with a zero rated default?

@Kirsten - yes, there are two fees. 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. At least not if the 10% is coming off the combined price … and even if the 10% was being charged on the fee, using the $0.50 tomatoes as an example, with a $0.50 fundraising fee … it’s $1.00 combined, or 2 X $0.05 if they were charged separately.

Now, 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.

It’s just those two sales fees that are both one cent more than they should be that has me stumped. They are both individually incorrect.

Here is the screenshot of the OC.

So I just double checked the other hub I’m working with - and the sales fee calculates spot on. It is also a percentage. I tried it with the fee applied to all products - as in a coordinators fee at the top of the order cycle. and I tried it with the fee applied just to one distributor - in the outgoing section of the OC. Both worked fine. @CLFC - humour me and try it with ‘zero rated’ indicated (I think I made that change to your sales fee - but didn’t test it.) I can’t see anything else wrong with how these are set up.

Can those invoices be re-generated, or will I have to create new orders to test it? I tried the "update and re-calculate fees button, but nothing changed.

I think if you changed something about the fee (ie the tax) - it will automatically change in the OC, but you’ll have to order again. I was just thinking - here’s another thing to try. Try putting that sales fee up under ‘coordinator’ fee (versus beside the products) and see if that matters? I don’t see why it would - I’ve done lots of fees both ways and haven’t had this problem (that I know of).

AND - building on @Kirsten’s thinking - try doing one fee at a time and see if they calculate correctly. I know they are different fees - but they are applied to the same products - right? so maybe there is something about the order they are calculated in. Although that would surprise me because i think @sstead and I had an 'everything you always wanted to know about fees" discussion a year ago or more. I understood that each fee is calculated separately on the ‘naked’ price. BUT maybe when they then get added together something happens with rounding.

FOUND IT!! I know what’s happening now. It’s due to rounding - and WHEN it is happening. THIS is why that little fee breakdown pie chart is so awesome!

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 applies, 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.

Does that qualify this as a bug? From a technical standpoint, I don’t believe so - from 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. (Based on 4x$0.25 being $1.00.)

Year, I think there is a problem in the way things are rounded in OFN… I don’t know it that’s an easy thing to fix, but it deserve some discussion at least. I think we shouldn’t round at unit level, or if we do round maybe 4 digit decimals to avoid issues when summing up things and rounding again. If cusomters see unconsistency and think they are overcharged, this is really annoying to me… so I think it deserves a bug.

@Rachelin your previous projects, did you bug into such things? I’m not sure in term of UX what is best, would it be better to have a price breakdown with less rounding, like in this case display 0,025 CAD instead of 0,03 ? Or display 0,03 but then when calculating total correct the rounding gap and display 1,05 price in total? I think we need to display 0,025 in this case or else it looks “wrong” to me…

Ultimately, it comes down to the price+fee being rounded at the unit level before going into the cart. Technically, the fee should be calculated in the cart, after the unit totals. So a 10% fee on 2 x $0.25 would be the correct 5 cents instead of the (also technically correct) 6 cents we’re seeing here.

Although, as @MyriamBoure suggests, leaving the fee rounding to three or four decimal places instead of two and doing the final rounding in the cart would achieve the same results without having to refactor the entire cost calculation system. Because if the fees were brought to the cart as $0.025/unit (in this case) instead of $0.03, the final calculation would give us the correct $0.05 final fee on two units. That is probably the easiest way to correct it.

I agree with @CLFC (and nicely done for figuring this out!!!). Really the fee should be calculated on the total price of the quantity that is ordered.

ok - does one of you want to write this up as a bug @clfc @tshumilas? I tend to agree (bug) as would be perceived by user as such, and the proposed fix of adjusting the rounding might make it quite manageable.

I think there were some other things about rounding recently - might be worth searching the github issues to see if related?

Found some earlier discussion on the same bug in G#847 and Tax calculation issue where it was concluded that 1p/1c rounding errors were OK.

Maybe the priority now is raised and the issue should be reopened?

I had found this one on Australian invoice some time ago @Kirsten https://github.com/openfoodfoundation/openfoodnetwork/issues/1500 maybe it needs retesting but I think it’s due to rounding issues.
When you create an issue on Github the template is the bug template so you just have to follow the instructions to report the bug @CLFC if you want to do it :slight_smile: You can in potential solutions proposed that 3 digit rounding instead of two (and you can also list others you thought about) and we can ask agreement of others then, should be a pretty quick fix :wink:

I would suggest open a new one and refer to those older ones in the new issue, or reopen but use the bug template to explain clearly the bug :wink:

1 Like