Thursday, May 12, 2016

The complexities of reproducing Dynamics GP timesheet calculations

By Steve Endow

Many years ago, I developed a small product for Dynamics GP called PA Timesheet Recalc.  It solves a slightly obscure problem for companies that use Dynamics GP Project Accounting Timesheets for salaried employees.

I regret developing it for a few reasons, but the biggest reason is that supporting a Dynamics GP Project Accounting customization is a pain.  It seems that every single customer uses Project Accounting slightly differently, so I've had to make several adjustments and a few customizations to support the oddities of customers' PA processes.

On top of that general challenge, I ended up with a customization that modifies a pretty complex set of data.  The Project Accounting Timesheet Entry window might look pretty straightforward, but it's like a mathematical circus behind the scenes.

Just one of the many complexities is rounding.  Yup, rounding.

You would think that rounding numbers is pretty cut and dry, stuff you learn in elementary school.  But if you've been developing for very long, you should know that there are several types of rounding, and the sequence of rounding affects calculations.

Here's a simple example.

Let's say that Jane's Unit Cost is $29.45, and her overhead is 17%.

If Jane works 2.5 hours, what is the total cost?

Well, that's pretty simple:  29.45 x 2.5 x 1.17 = $86.14

If that was your answer, you'd be wrong.

The total cost, from Dynamics GP's perspective, is $86.15.

How is this possible?  Let's break it down.

29.45 x 2.5 = 73.625

73.625 x 1.17 = 84.14125

So using this math, we would round the result to $84.14

But that's not how GP calculates it.  GP performs an interim rounding step.

29.45 x 2.5 = 73.625  ==> Rounded to 73.63

73.63 x 1.17 = 84.1471  ==> Rounded to 84.15

The rounding performed after the first calculation results in a difference of $0.01.

While this difference is not a lot of money, it is the difference between being "right" and "wrong" from GP's perspective.  And if you have larger numbers, the difference can be more than one penny.  And an insidious part of this problem is that you could have 50 timesheet lines that calculate fine, because their values don't happen to trigger this rounding issue.

But if you have hundreds of PA Timesheets with dozens of lines, you will get a pile of differences.

So if you ever have to recalculate Dynamics GP data, be careful and double check.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

No comments: