Rounding Issues
Many systems contain different options for calculating VAT on products. This can occasionally cause discrepancies between the values shown in one system and the values calculated in another.
There are a few reasons for this, such as some systems rounding VAT up and some rounding VAT down and some systems calculating VAT at the Item, Row or Total level while others do not.
Below is a breakdown of a typical invoice, highlighting the impact this can have.
Item Line Method
| Product Line | Quantity | UnitPrice | Total | VAT |
|---|---|---|---|---|
| Product A | 1 | 32.50 | 32.50 | 6.50 |
| Product B | 1 | 33.33 | 33.33 | 6.67 |
| Product C | 1 | 33.33 | 33.33 | 6.67 |
| Product D | 1 | 33.33 | 33.33 | 6.67 |
| Product E | 1 | 20.83 | 20.83 | 4.17 |
| Product F | 1 | 12.50 | 12.50 | 2.50 |
| Product G | 1 | 8.33 | 8.33 | 1.67 |
| Product H | 1 | 8.33 | 8.33 | 1.67 |
| Product I | 1 | 8.33 | 8.33 | 1.67 |
| Product J | 1 | 8.33 | 8.33 | 1.67 |
| Product K | 1 | 8.33 | 8.33 | 1.67 |
| Product L | 4 | 10.00 | 40.00 | 8.00 |
| Product M | 1 | 8.33 | 8.33 | 1.67 |
| Totals | 255.80 | 51.20 |
In this case, if we add up the UnitPrice and VAT columns we get the following totals:
- Total Net UnitPrice: £255.80
- Total VAT: £51.20
- Gross Total: £307.00
Order Total Method
If we work out the VAT based on 20% of the Total Net UnitPrice, we end up with the following:
- Total Net UnitPrice: £255.80
- Total VAT: £51.16
- Gross Total: £306.96
Comparing this to the results of the Line Method, we are left with a Gross Total discrepancy of 4p between the two methods.
To correct this, we have the following options:
- Adjust the source system to work out VAT in the same way as the accounting system
Wherever possible, we recommend ensuring that both the system where Invoices/Orders originate and your accounting system both use the same method of rounding.
- Accept the discrepancy
Some customers chose to process a batch invoice/payment at month end to settle these outstanding balances.
- Adjust one of the item lines to make the gross total equal
The final option is to add a complex mapping that works out what the total will be against what the total should be and adjust accordingly.
For example, reusing the earlier invoice:
| Product Line | Quantity | UnitPrice | Total | VAT |
|---|---|---|---|---|
| Product A | 1 | 32.50 | 32.50 | 6.50 |
| Product B | 1 | 33.33 | 33.33 | 6.67 |
| Product C | 1 | 33.33 | 33.33 | 6.67 |
| Product D | 1 | 33.33 | 33.33 | 6.67 |
| Product E | 1 | 20.83 | 20.83 | 4.17 |
| Product F | 1 | 12.50 | 12.50 | 2.50 |
| Product G | 1 | 8.33 | 8.33 | 1.67 |
| Product H | 1 | 8.33 | 8.33 | 1.67 |
| Product I | 1 | 8.33 | 8.33 | 1.67 |
| Product J | 1 | 8.33 | 8.33 | 1.67 |
| Product K | 1 | 8.33 | 8.33 | 1.67 |
| Product L | 4 | 10.00 | 40.00 | 8.00 |
| Product M | 1 | 8.30 | 8.30 | 1.66 |
| Totals | 255.77 | 51.19 |
In this case, we've adjusted Product M to have a lower UnitPrice. This gives us the following totals:
- Total Net UnitPrice: £255.77
- Total VAT: £51.19
- Gross Total: £306.96
This would mean that even though one of the items is technically out by a few pence, the overall Gross Total would match between the Item Line Method and the Order Total Method.
Our most commonly integration systems, such as Sage 50 use the Item Line Method, so it's important to check what method your eCommerce/CRM it set up as. It is also worth noting that this is not something we would include as standard, or recommend as a primary option for integrations.