Roundings - I hate them!

As anyone who has ever used Excel knows, eventually you come across the situation where the total of a column of figures doesn’t add up correctly, or rather, appears not to add up correctly. This normally arises where the formatting shows, say, 2 decimal places but the under-lying data is held to many more decimal places. 

Excel example showing rounding difference
The answer to the above problem is simply to round the calculation of 10 divided by 3 to 2 decimal places, i.e. =Round(10/3,2) and now the total will be 9.99. (Possibly not precisely what you want, but at least mathematically correct.)

Integrating Sage with other systems normally involves exporting data from Sage for importing into another system, or the opposite, exporting data from an external system and importing into Sage.

A recent project involved exporting data from an online shopping system (Shopify.com) and posting the resulting sales orders into Sage 50. In theory it should have been relatively simple; in practice it proved anything but and rounding was the root cause of the difficulties.

Sage calculates discount and VAT on a line by line basis (there is an overall ‘order discount’ option, but it’s not very pretty!). Shopify, on the other hand, calculates discount and VAT on the total order. Inevitably, the obvious happened and when discount and VAT percentages were applied at line level to post to Sage, the total order value in Sage wasn’t the same as the Shopify total order value.

Clearly the total sales value posted to Sage had to be the same as the Shopify total sales value, and the discount and VAT also had to agree.

 
How was this problem solved?

In a nutshell, the technique used was to record the total discount and VAT for the order, and then to cycle through the order lines, one by one, calculating discount and VAT based on the overall discount and VAT percentages. After calculating the discount and VAT for each line it was deducted from the overall discount and VAT – effectively creating a running figure of discount and VAT remaining. On reaching the last order line, discount and VAT were not calculated, but assigned the remaining values. Voila, problem solved!

As with most problems the answer is obvious once you know it, but can be quite elusive until you do! (The trick, of course, is knowing when you are on the last order line… But that’s another story.)