[AccessD] Access rounding scenario

Gustav Brock Gustav at cactus.dk
Tue Aug 23 02:30:11 CDT 2011


Hi Larry

This is a classic. You have two options:

1. Use a higher precision (more decimals) when you calculate from the percentages.

This may be acceptable for example if you perform a final rounding of the total to, say, 25 cent.

2. Calculate to final error and spread this proportionally across the items to correct the error to zero:

Calculated:
30.003
20.002
49.999
Error: 0.004

Corrected:
30.002
20.001
49.997
Error: 0.0

Further, it may be required to handle the special case of an uneven count of equal shares, the classic being three equal shares:

Calculated:
33.333
33.333
33.333
Error: 0.001

You could by random correct one value by the error:

Corrected:
33.333
33.334
33.333
Error: 0.0

However, as accountants prefer predictable results, it's important to stick to rules that will produce identical outputs for identical inputs. 

Thus, you should avoid any random method and find some other like adding the 0.001 error correction to the first or last line, to the line with the highest total costs, the most "important" material (measured by activity, customer classification or turnover, project duration, or whatever that makes sense for your client), or something else you can explain to your client in common language in few words.

These methods typically require a second run by code.

/gustav


>>> lmrazek at lcm-res.com 22-08-2011 18:16 >>>
Hi folks:

Here is the scenario. 

We have a shipping record (with details on where the shipment is going,
etc.).  Linked to this is the details table, containing line item details on
exactly what is in the shipment, plus, on occasion, an extra transport
charge.

For a report, the client would like to have the transport charge applied to
each item in the shipment.

Thus, if the shipment contains the following charges:
Material 1: $200
Material 2: $200
Material 3: $400
Transport:  $100
They'd want the report to show;

Material	Charge	Transport
Material 1: $200		$25
Material 2: $200		$25
Material 3: $400		$50

Currently, I'm trying to do this by calculating a ratio of the materials to
the total (material 1 = .25, material 2=.25, material 3= .50), and
multiplying this by the total transport.

While this works for the above numbers, I'm getting some rounding errors (I
think) when dealing with other numbers and I need the transport charges to
add up correctly.

Any hints about how to do this? 

Larry Mrazek
lmrazek at lcm-res.com 
ph. 314-496-1645





More information about the AccessD mailing list