CreateRounder

[Bigred]

Hello there,

Does anyone know anything about the "CreateRounder" function within Report Designer? I'm trying to round some numbers up to the nearest 10 (for a picking list in Manufacturing) and can't do it with "Round" or "RoundDP". I ahve a feeling "CreateRounder" might be able to help but can't for the life of me find any help, either within the program or on the internet, on how to use the function.

The format should be;

CreateRounder(method, direction, multipleOf, adjustment)

Any help or pointing in the right direction would be greatly appreciated.

Thanks in advance,

John

[brucedenney]

Divide by 1o, round up them multiply by 10

[Bigred]

Bruce,

Brilliant. Works an absolute treat. Does exactly what I was after.

Thanks very much!

John

[ChrisS]

Hi

We're experiencing a similar issue with Sage. We're on Sage 50 Plus Accounts 2015 and have a rounding issue as follows. We became VAT registered yesterday and are using a simplified VAT invoice that we created in Report Designer as our business is ecommerce retail and most of our customers are private individuals, who do not require a traditional VAT invoice i.e. Net value + VAT = Gross Value. So in Report Designer we take the net unit price and (as Sage themselves actually suggest!) use the following formula to increase the figure to the gross value (that customer is familiar with, as this is the figure they see on our website)

INVOICE_ITEM.FOREIGN_UNIT_PRICE*(INVOICE_ITEM.TAX_RATE+100)/100

So this then converts the product price from net to gross on the invoice product line but as we found on the following example, a rounding issue arises...

Product Price (Net) £2.33
Vat @ 20% £0.46
Total £2.79

The above figures are correct in terms of our website, what the customer expects to pay and the Grand total visible on the invoice is £2.79 which is correct (using INVOICE.FOREIGN_INVOICE_GROSS). However when you look closer at the actual product line itself on the invoice the product price shows as £2.80 (because the VAT element is actually £0.466 so it gets rounded up).

We have tried using round and round dp to no avail on the grossing up calculation and wondered whether perhaps the createrounder formula may be more suitable? However like the other query Sage provide no help advice anywhere to explain what terminology is required for each field - CreateRounder(method, direction, multipleOf, adjustment). So for example what is method? Direction - down, decrease??

Any help would be most appreciated in terms of how we should apply a rounding formula to the gross up calculation formula, its only a nominal rounding issue of a few pence so financially not a problem but professionally it looks terrible sending a customer an invoice that simply does not add up! The whole situation could be avoided if sage provided invoice gross figure field on a product by product basis, as opposed to just an overall one!

Many thanks

[brucedenney]

This is actually nothing to do with the rounder function.

This is a issue is in the maths.

If the net price is £2.33 then the VAT is £0.47 and the Gross amount is £2.80
If the net price is £2.32 then the VAT is £0.46 and the Gross amount is £2.78

So it is impossible to have an item that sells for £2.79 inc VAT.

Your web site is doing something that is impossible and Sage is not.

There will also be issues with multiple, so 10 items @ 2.33 have a gross cost of £27.96 not £28.00

To fudge the maths you need to amend the VAT amount on the line item, you need to be wary, because if you edit the item, Sage will (very kindly) recalculate the VAT "correctly"

My advice is to fix the website so it applies VAT correctly.

Apart from helping on here I also to custom integrations and quite often we have had to import orders from websites into Sage, where the tax is calculated using invalid rules for the UK. Common issues include working only in Gross amounts, Calculating Sales taxes on the invoice totals not on the line items and so forth. The best place to fix this sort of issue is where it originates in the webshop, as that is what doesn't comply with VAT legislation.

[ChrisS]

Hi Bruce

Many thanks for your reply. When we took a step back we see exactly where you are coming from. A £2.79 item, as you say, cannot be sold for £2.79 inc VAT. When we looked at this further (most of our items start at 99p up to £20) 99p does not work, nor does £1.05, £1.11 etc so we decided to revisit the source as you suggested and repriced all items that did not work....basically we removed all odd number pricing. Hey presto.....all single item sales that we import into Sage 50 now work a treat and we get no rounding issues on invoices. However, this is just on single item sales, when we start receiving multiple item sales we do still get occasional rounding issues. Not everytime, just on some.

For example, we sell one item for £2.24 inc VAT. We have just sold two of these to a customer for £4.48 total and when this has imported into Sage 50, Sage 50 has broken down as £3.74 NET, £0.74 VAT and £4.48 total. So fine. Gross invoice total is correct (INVOICE.FOREIGN_INVOICE_GROSS) on our invoice as £4.48, gross individual item price is OK at £2.24 however total gross item price on that line are totalling at £4.49....so a penny too much. I think where the problem lies is 20% VAT on £3.74 NET equates to £0.748.....so whilst Sage 50 appears to have treated the VAT as £0.74 (rounding down) on it's side....Sage Report Designer has taken the £0.748 and rounded up. Is this not an area where, perhaps, we do need to be thinking about using some form of rounding formula then? Or are there any settings where we can get Sage Report designer to treat the figures exactly as Sage 50 does?

Obviously many other websites use irregular pricing and seem to produce invoices fine, so we're guessing there must be a relatively easy workaround here somewhere.

[brucedenney]

VAT is calculated at the line level.

This results in rounding issues at the line level, if the website took the vat exclusive price for the line and multiplied it up and correctly calculated the VAT at the line level then there would be no problem. If people buy more of an item, then rounding may come into play and you have the inclusive price per unit change.

If you have the correct gross price for the line in Sage, you can reverse calculate the net price for the line the VAT amount is then the gross less the net.