Rounding
Rounding in ERP systems is horrific. I’ve already written a long and boring post about VAT rounding which you’ll hopefully never have to read.
Last updated
Rounding in ERP systems is horrific. I’ve already written a long and boring post about VAT rounding which you’ll hopefully never have to read.
Last updated
Hopefully you’ll never have to read this article either, but if you’re here it’s probably because you’ve noticed a rounding ‘discrepancy’ and want to understand where it comes from. If that’s the case, you should probably read first, as it lays out some groundwork with (almost) digestible examples, and it's actually quite entertaining in a nerdy kind of way.
This article is more of a practical manual about how rounding is implemented across Pakk and the practical implementations.
Since December 2021, Pakk has supported ‘precise prices’. What does this mean? Prior to December 2021, all prices and monetary values where represented, stored and presented only to the nearest cent. That meant that £2.58 was representable, whilst £2.584 was not.
Whilst this is fine in 99% of use cases, there are some industries and products that require greater, sub-cent, precision - particularly for logging supplier purchase orders and invoices.
So in December 2021 we introduced higher precision prices. The new way of representing monetary values allows for us to 6 numbers after the point (which means an extra 4 decimal places of precision on top of regular cents). For example, £4.123456 can be represented in the new system without any rounding. Prior to the change, this amount would have been represented as £4.12.
The introduction of the ability to represent very precise monetary values had a big impact on rounding in the system. Prior to the change, all calculations always resulted in a rounded cent amount with only 2 numbers after the decimal point. VAT calculations, percentage discount calculations etc would all yield “real” monetary amounts (by real I mean a normal amount like £2.56 that can actually be charged and collected from a customer - you wouldn’t be able to collect £2.564, for example).
With precise representation of monetary amounts, the decision of where rounding should take place rises to the surface in a surprising number of areas and the resulting complexity can be hard to get one’s head around.
As of March 2023 we support configurable rounding methods. The available methods are as follows:
Line (default): This is the method that we've used exclusively up until now. Essentially it involves rounding at line level.
Transaction: The 'latest' possible rounding method we support. Basically it means rounding the transaction inc. VAT total.
Unit: The 'earliest' possible rounding method we support. Here we round the unit inc. VAT price.
Here's the precise mechanism:
Calculate the line Ex. subtotal
Round that
Calculate the amount of VAT
Round that
Add the (rounded) line Ex. to the (rounded) line VAT for the line Inc, which is rounded by definition (because it is the sum of two rounded amounts).
The advantage of this technique is that all line amounts (ex, VAT, inc) are 'real' amounts, which means that all amount filtering down through the system (think reporting, accounting etc) are 'real' amounts and you can basically forget about rounding from then on. It's also a fairly 'balanced' rounding technique in that it's 'late' enough that you don't get large compounding errors for transaction lines (the maximum rounding discrepancy per line is half a penny/cent).
The chief disadvantage of this technique is that there are sone Inc. VAT prices that are impossible to achieve when a single unit is purchased. For example £69.99 Inc. VAT where VAT is 20%:
The Ex. price would be £58.325 (Note that this is no problem for the system to achieve).
On a single line transaction: 1 x £58.325 is the line Ex. which gets rounded to £58.33
This means that the line Inc. is £70 instead of £69.99
Note that if two units are purchased: 2 x £58.325 = £116.65 x 20% = £139.98 which is correct (i.e. it is equivalent to £69.99 x 2)
Here is the precise mechanism:
Round the final transaction Inc. VAT total
Account for any rounding discrepancy (i.e. Rounded Inc. - VAT - Ex.) by writing to internal 'Rounding Adjustment' account
The main advantage of this technique is that because rounding is left to the very end, the maximum discrepancy is half a penny/cent per transaction. This is the least possible amount of compounding error. In industries/businesses where transactions involve lines with a large quantity of low value items, this method can avoid comparatively large compounding errors.
It also have has the advantage that any pretty price (e.g. £69.99) can be achieved.
Note that the principal disadvantage of this technique affects companies selling to the public who need to present a rounded Inc. VAT price. Look at the following example:
A product whose Ex. price is £55
With a VAT rate of 17.5%, the Inc. price is £64.625, but the customer will be presented with £64.63
The customer expects to pay £129.26 for 2
But he will actually pay £55 x 2 x 17.5% = £129.25
The potential discrepancy here is mainly a function of the VAT rate and line quantity. For 'round' VAT rates and lowish line quantities, discrepancies are quite rare and are small when they do occur. We'd encourage you to run some simulations on a spreadsheet to see how it affects your products.
Here is the precise mechanism:
Calculate the unit Inc. price and round that.
Calculate the VAT amount based on the original Ex. VAT price and round that
Deduct the rounded VAT amount from the rounded unit Inc. price to arrive at a rounded Ex. price
This is a variant of 'unit' rounding that prioritises getting the tax 'right' by accounting for the rounding discrepancy by back-calculating the Ex. price. A worked example will be clearer:
A product is given an Ex. price of £58.325 in order to achieve an Inc. price of £69.99
Note that this number is already round, so no need to round it
The correct tax amount here is £11.665, which we round to £11.67
£69.99 - £11.67 = £58.32 - this is the new Ex. price
What has happened here? In essence, we have placed the 'burden' of rounding on the merchant rather than the tax authority? That's not the only way we could have done this adjustment - in fact there are two alternatives:
Place the 'burden' on the tax authority by rounding the original Ex. price and then calculating the tax by calculating Inc - Tax. In this example, the Ex. rounds to £58.33 which means the VAT is £11.66. The merchant gets a penny more revenue but the tax authority gets a penny less. This isn't a massive problem for this example, but for large line quantities on small prices, the difference can be huge and we don't think the tax authorities would be best please (in any case, we wouldn't recommend this rounding method for business like that).
Only round once and accept that Ex. + VAT will not precisely equal Inc. VAT (in the example above, this will be a half penny discrepancy per unit) and account for this internally in the 'Rounding Adjustment' account.
We don't like either of these options, which is why we choose to double round, give the tax authority the extra revenue and always have Ex. + VAT. = Inc.
The admin panel will let you enter prices to 6dp and will display enough numbers after the decimal point to faithfully reproduce what was entered. So, if you enter 3.2457 as the price of a product, you will see 3.2457 - not 3.25 nor 3.245700.
The admin panel gives you the option to see or 'hide' precise (unrounded) amounts. This is a per-user option that can be accessed in your personal user options (click on your email address in the aux bar). If you choose to 'hide' unrounded amounts, you will still see precision prices (e.g. purchase price, sell price) as its important for you to know when you've entered a precision price, but all other calculated amounts (e.g. line totals, subtotals) will be rounded for you. Note that this doesn't affect the underlying representation of the amount, just the way you see it.
We'd expect most users to keep the option to display 'precision amounts' off most of the time, as it's easier to just see rounded amounts. Toggling precision display on is mostly useful if you are trying to track down a rounding discrepancy you don't understand.
Remember, you can choose whether your site displays Ex. prices/amounts, Inc. prices/amounts or both in your Website Settings.
Ex. prices are always shown to their original precision (e.g if you enter £5.643, that will be shown)
Inc. prices are always rounded for display irrespective of the rounding technique your account uses. For example, if an Inc. VAT price is £69.985, it will be displayed to the customer as £69.99
Amounts in the cart and checkout (line totals, subtotals, grand totals) are shown rounded
By extras, I’m referring to the bits and pieces that get added into an order after the lines are subtotalled - so shipping, payment surcharges and discounts. The calculation of these amounts respect your account rounding preference setting.