Rounding Issues with Importing Sales

[ELC-Leo]

Can someone please tell me how to avoid MYOB AccountRight rounding line items incorrectly when importing sales data, in my case Service Sales, but judging from the forums, the issue occurs in other types of sale. I have exhausted the Community Forum. And the few users I found with similar rounding issues have had their threads closed without resolution.

After spending a day on developing a process for importing service sales from a CSV to MYOB AccountRight, I really hope you won't tell me this is not possible. Or am I wasting yet more time trying to lay this all out in writing in the forum…

To set up the import process, I was working off MYOB's information here: https://help.myob.com/wiki/display/ar/Import+and+export+fields#expand-Servicesales\

and https://help.myob.com/wiki/display/ar/Importing+data , as well as Moderator and user advice in the forum. But having got everything working on my side, I now come up against MYOB adding rounding errors. I believe this rounding issue is a known error, and if so, it should have been mentioned in the initial information so that I could have saved myself the time and effort.

The issue is that MYOB amounts are, for line items of certain values, increased or decreased by $0.01.

The best way to explain is to see the issue, so I will attached pictures of my import data and the resulting invoice in MYOB. If possible, I will also attached the .txt file with the CSV data.

Additionally, during some tests the Total Amount field was also one cent off what it should be, such that the amounts in the Amount column did not sum to give the Total Amount!

Thank you for any solution.

[ELC-Leo]

I have since found the following posts in the Myob Essentials forum which might help explain the casue, but haven't helped me towards a solution for my scenario.

https://community.myob.com/t5/Original-MYOB-Essentials/SALES-ROUNDING-IN-MYOB-ESSENTIALS/m-p/535405

https://community.myob.com/t5/Original-MYOB-Essentials/Trouble-with-rounding-up/m-p/682364

[gavin12345]

Hi @ELC-Leo 

I'm not sure about your Inc Tax Amount column. Inc usually stands for Inclusive, so I think this should show 1.00 2.00 etc.

Most rounding issues are with GST inclusive numbers like $5.00. Divide this by 11 to get 0.45454545 and MYOB struggles.

This is what I suggest to see if MYOB can import correctly.

Fix up this invoice onscreen to what you want the amount column to show. Next export this invoice only. Then import the export, without changing anything. If it succeeds, analyse the export file and copy that to the letter. If it fails and gives you the same result as the original, then there is no answer but to use something else.

Regards

Gavin

[ELC-Leo]

Good suggestions, thank you, Gavin. 

I have tried various combinations of tax-inclusive/exclusive figures. But I will try your suggestion of fixing it in MYOB, exporting and reimporting to get a handle on the situation. 

Kind regards

[AlanT]

Hi @ELC-Leo 

I notice in your screen shots that you are displaying your CSV from MS Excel.

Just out of interest, are you able to open the CSV file in Notepad and show the raw import data?

As an Excel programmer, I have had this type of problem before where Excel tries to be too smart and modifies the data so it can display it the way it wants to, but leaves the underlying data as it is.  Therefore, sometimes what you see in Excel looks good, but XL is tricking you.

Regards,

AlanT

[ELC-Leo]

Hi AlanT,

Good suggestion for many, but in this case, I don't think that is the cause.

Here is a screenshot of the CSV data in Notepad (the .txt file is attached to original post).

Kind regards

[Mike_James]

Hi @ELC-Leo , when importing sales, AccountRight takes the net amount and calculates its own GST for the line. However, it also accumulates the net and GST on every line, and adjusts the GST as it goes to ensure that the cumulative total net plus GST (calculated on that total) gives the total inclusive.

It is impossible to replicate this process outside MYOB, or to provide figures which will give the wanted inclusive line amounts, since only the net amount is used during the import. 

I've prepared an analysis showing how the inclusive figures on your invoice are arrived at. 

Note that the GST-inclusive setting is only for display purposes.

[ELC-Leo]

Thanks for the comprehenisve reply, @Mike_James 

I'll cut my losses and accept that it is not possible to use the import fucntion to speed up our sales entry process in MYOB. Thank you.

It just seems so silly for MYOB to say you can import sales, but not mention that it will only work as expected if your line items are for particilar values.

Does anyone know whether Xero has the same issue? (After 20 years of manually entering hundereds of repetative invoices into MYOB each quarter, at this point we need to be working smarter not harder.)

Many of our invoices for each customer each quarter are identical as each other (but not necicarily the same from quarter to quarter, so setting recurring sales would not help save time). If I could have the few hundered invoices autogenerate for each customer, I could then manaully change the few that need to be different. But having just done a test with this quarter's invoice amounts, I see that three quarters of them would need manual adjustment to their line items to correct MYOB's rounding -- which is frustrating, and not the time-saver I was hoping for.

If anyone has suggestions of a program other than MYOB which would make this easier, I'd love to look into it. The problem is that, as in this case, products' limitations might not be mentioned in their documention, and I don't want to waste days testing as I did with MYOB.  

And to MYOB: out of respect to your customers, please add this limitation to your documentation on the pages I mentioned that I used to try to set up our sales import process. That would have saved me great time and frustration.