Change the Rounding Function for Tax

(0) Share

Report

Posted on by Bill Campbell

I thought I had it made when I found https://community.dynamics.com/gp/f/32/t/90686

and it referenced chaning the Tax Detail rounding to the nearest Currency Decimal.

I immediately jumped into the TEST environment and was dashed against the rocks when I learned our settings were already there.

Problem

When a POP or SOP transaction is recorded the GST / Tax detail is calculated on the line items and not on the Sub Total. The issue is related to how GP ROUNDS.

I have a SOP document that generates 10 lines and total tax ( one line at a time ) of 634.92

The Sub Total for the invoice generates a tax amount of 633.52

Now, the only way you know this is if you do the math on the subtotal calculation of GST on a calculator and it does not agree with the amount that the system is presenting. It appears just to make matters more than interesting the system presents the accumlated amount from the Line Item calculation.

This is not a problem for Sales Invoices done in Sales >> Transactions - as there is only ONE line and the calculation is the calculation.

Anyone with any comments? How do you justify this to a client. How do you tell a client that is coming from a customized application, that is just looking for reasons to 'hate' the system more, that 'Oh, sorry that is how the programmers at Microsoft worked around the problem - they figured no would notice'

Sorry, but I have got to get something figured out - this client is pissed.

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (0)

Tim Foster 8,515 on at

Like (0)

Report

Bill,

Can you show me the numbers?

I'm having trouble with the gap between your tax totals.

What province is the stuff being sold into? We sell in nearly every one.

Thanks

Tim

Was this reply helpful? Yes No
Bill Campbell 12 on at

Like (0)

Report

Tim, let me pull together a set of lines and the details that go along with them.

I may be off on the values in the note - the problem still exists - there is a difference.

We are selling in Alberta - straight and simple - 5% not that complicated.

Was this reply helpful? Yes No
L Vail 65,271 on at

Like (0)

Report

Bill,

That problem has existed in GP from the beginning. I know they (gp) know about it, but I do not think they intend to correct it.

Kind regards,

Leslie

Was this reply helpful? Yes No
Bill Campbell 12 on at

Like (0)

Report

So how do you explain it to the customer ? What do you say - I know they know but so they are not going to fix it - it does not really matter ?

This is a client that is coming off of a fully customized system and is now thinking they have invested in a piece of crap and the staff are looking for any reason to make waves. I just spent 1/2 hour on the phone with the owner who wanted to ask if the issues were critical or was the staff making waves?

I work daily with the staff - he pays the bills - what do I say?

Sorry for the rant.

Was this reply helpful? Yes No
Tim Foster 8,515 on at

Like (0)

Report

Bill

I just ran this SQL against a similar HST tax detail:

SELECT DERIV.*,SDH.TAXAMNT

FROM (SELECT SOPNUMBE,SUM(XTNDPRCE) XTNDPRCE,SUM(XTNDPRCE)*.05 AS CALCTAX,SUM(TAXAMNT) TAXAMNT,(SUM(XTNDPRCE)*.05)-SUM(TAXAMNT) GAP

FROM SOP30300

WHERE SOPNUMBE IN (SELECT SOPNUMBE

FROM SOP30200

WHERE [STATE]='<some state>' AND SOPTYPE=3 AND DOCDATE>'2015-01-01' AND TAXAMNT!=0 AND FRTAMNT=0)

AND TAXSCHID='<a flat 5%>'

GROUP BY SOPNUMBE) DERIV INNER JOIN SOP30200 SDH ON DERIV.SOPNUMBE=SDH.SOPNUMBE

Hundreds Invoices and a lot of sales dollars and items and I have no rounding issues.

What am I missing?

Tim

Was this reply helpful? Yes No
L Vail 65,271 on at

Like (0)

Report

It sounds like you are getting some big differences. In my limited experience, the differences were just cents. Are you experiencing bigger numbers?

Kind regards,

Leslie

Was this reply helpful? Yes No
Bill Campbell 12 on at

Like (0)

Report

Sorry was slamed this week and did not get back to this until Friday. So, yes you are correct the differences are small - pennies in most cases - and therefore not a huge issue. However when you have a PO that has 100's of lines, and the transactions get to the $30,000 that starts to add up. Small amounts I understand - rounding - but really Microsoft if they know about this, should only calculate tax one way and present the same on the report.

Tim I am working on collecting a specific set of examples - however, client is just leaving for 1 week ( sorry ) so I will do my best to post the best details I can gather.

Just to be clear when we have 100's of lines of items that are costed at 1.356 per unit when ordering a 1,000 at a time and you have a number of these on the invoice, the client and I proved that doing the math shows a difference and that is just not right.

Was this reply helpful? Yes No
Bill Campbell 12 on at

Like (0)

Report

Not sure that it will matter, I looked over this script and while I follow it - I think I forgot that the client is not using SOP. (1) all the sales documents are generated 3rd party and imported to Sales Invoices (2) not all the items are inventory, some are non-inventory that are added to the Sales Invoice from the 3rd party application. The non-inventory details are being stored in the GP database - so there is a consistent use of Vendor, Price, Purchase Cost and such - just not counting inventory items.

Hope this clears up the SOP / Sales Invoice matter.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Bill,

Were you able to find a resolution to your rounding issue? I am having the same problems with the tax rounding by line item and not by invoice subtotal.

Was this reply helpful? Yes No
Suggested answer

L Vail 65,271 on at

Like (0)

Report

Billl,

It sounds like you need more decimal places. This happened to me, and this is what I did.

I turned it into a blog post.

Kind regards,

Leslie

Going Beyond 5 Decimals

Recently I was asked how to get Dynamics GP to calculate a unit price using more than five decimals. They were tired of constantly dealing with the few pennies of rounding differences.

Multicurrency to the rescue!

Since Multicurrency uses more precision when doing the translation, you can set up a new currency that will use many more decimals. Here’s how it went.

The problem:

The actual unit price of an item was $0.00491256. The system could only use the first five decimals, so they ended up with a price of .00491. If they were to sell 10,000 of these, the extended price would be $49.10 instead of the correct price of $49.13. Sure, GP can easily dispense with that extra 3 cents, but it’s a pain.

The solution we employed:

Create a new currency (I’ll call it $NEW) with an exchange rate of .000001 set to multiply. Enter the sale using the $NEW currency. When the currency is translated into the functional currency, the desired value of $0.00491256 is used as the unit price.

When entering the transaction using the $NEW currency, you would use a unit price is $4,912.56. When the price is translated into USD, you end up with this: $4,912.56 * .000001 = .00491256. Problem solved.

Just as an aside, changing the unit of measure schedule was not a viable alternative in this case.

Until next time!

Was this reply helpful? Yes No