2

I have a data set which consists of BigDecimal numbers. I want to present this in a report. I have managed to generate a report with the following format. This is somewhat similar to a trial balance in accounting. But it is not! The data in the table have 4 decimal points in their values. But in the report it should be rounded to two decimal places. The report has to be accurate as well, hence the usage of BigDecimal. 

+--------+---------+--------+
|Account | Debits  | Credits|
+--------+---------+--------+
|A       | 0.2760  | 0.1630 |
+--------+---------+--------+
|B       | 0.2770  | 0.2640 |
+--------+---------+--------+
|Total   | 0.5530  | 0.4270 |
+--------+---------+--------+

The real issue is when it comes to displaying the totals. The totals won't add up properly, after rounded.

+--------+---------+--------+
|Account | Debits  | Credits|
+--------+---------+--------+
|A       | 0.28    | 0.16   |
+--------+---------+--------+
|B       | 0.28    | 0.26   |
+--------+---------+--------+
|Total   | 0.55    | 0.43   |
+--------+---------+--------+

I am using the HALF_EVEN rounding mode because it is used for bookkeeping and also because this case involves financials as well.

I can add it up after rounding the values. then the report calculations will be OK. But it will not represent the actual value which is in the database (.553 is close to .55, but if I add it up after rounding the result would be .56, .553 is not close to .56).

Is there anyway to overcome this issue? I looked around to see how trial balance reports are made but I could not find any proper solution for this?

EDIT 1

I have seen trial balance reports in financial systems like this, do they make the calculations after rounding? I keep comparing this to Trial Balance report since this report is similar to one.

EDIT 2
Is it OK to make the calculation on rounded values? I have learnt that making the calculations on the rounded value is not recommended since the original values get discarded.

EDIT 3 Based on @MarcioB, @user3679868 and @JoopEggen decided to make the calculations based on the rounded values. The difference .047 (.56 - .553) is written off apparently (Audit standard)!

Cœur
  • 37,241
  • 25
  • 195
  • 267
LalakaJ
  • 531
  • 7
  • 16
  • 1
    Representing currency as floating point is a terrible idea. You will run into many conversion errors and there are numbers which may not be represented with floating point. Instead, represent current as a long and have the value 1 be some meaningful fraction of currency amount. For example, 1 = 1/10,000 of a dollar – DwB Jul 29 '14 at 12:36
  • I am not representing them as floating points. They are of type `BigDecimal`. And these are in cents. cent is the smallest unit of currency here. 100cents=1rupee. Decimal places are due to interest rate calculations. – LalakaJ Jul 29 '14 at 12:41
  • 3
    **Ask bookkeeping**. The problem is that totalling must be done last, but then you get a discrepancy with the other precision's case. Normally there is a (swiss bank?) account for differences. – Joop Eggen Jul 29 '14 at 13:14
  • @JoopEggen Thanks! There is an account for rounding off errors. Where several cents, sometimes rupees get posted after transaction (currency exchg etc.). So it is **OK to do the calculation based on rounded values** right?! Thanks again Joop Eggen – LalakaJ Jul 29 '14 at 13:20
  • Fine, thanks for the confirmation. Though totals should be kept accurate. – Joop Eggen Jul 29 '14 at 14:01
  • @JoopEggen Yeah! Of course.. – LalakaJ Jul 29 '14 at 14:38

2 Answers2

1

If you use rounding in one table and don't use it on the other, you will always have this problem, regardless of the rounding method.

Here we use the full number for the calculus, and truncate it in the reports.

MarcioB
  • 1,538
  • 1
  • 9
  • 11
  • All are in the same table. Only the results (Total) are calculated at runtime. We use the original values for the calculation, not the rounded value as @user3679868 suggested. – LalakaJ Jul 29 '14 at 12:45
  • Then you could just truncate the result value. There is no way to represent a 4 decimal point number in a 2 decimal point without losing precision somewhere. – MarcioB Jul 29 '14 at 12:51
  • Then .5530 => .55. But still the .277 and .267 would round up to .28, then some one who's reading the report would add (.28+.28) and come up with .56. I will have to answer for that missing points ;) . Unless I do the result based on rounded values. Then the report would tally perfectly. I updated the question to check if it is accepted to have a calculations made on rounded values for sake of reporting? – LalakaJ Jul 29 '14 at 12:57
  • As you said in the question, calculating with rounded values discard the original value. What I'm trying to say is, round everything or truncate everything. If not, the results will always look wrong when the full number (2 points instead of 4) is not shown. – MarcioB Jul 29 '14 at 13:03
  • If I round everything, the data and the result, (.28 + .28) becomes equal to .55. Even if I truncate everything .27 + .27 becomes .55 right? This is my concern! – LalakaJ Jul 29 '14 at 13:09
  • 1
    should I round/truncate the data in the table and **then** perform the calculations? – LalakaJ Jul 29 '14 at 13:11
  • 1
    Exactly, thats how I do it here. But it's your decision, round or truncate everything. – MarcioB Jul 29 '14 at 13:16
  • Rounding and make the calculation is the way to go. Thanks for the suggestion. Writing off the balance. I updated the question as well. – LalakaJ Jul 29 '14 at 13:27
1

Because you are rounding each account if you want a total that is equal to the sum of the accounts you will have to compute the total using the rounded values for the accounts.

user3679868
  • 693
  • 4
  • 6
  • Then the final value will not represent the exact value of the data table, isn't it? (.553 is close to .55, but if I add it up after rounding the result would be .56 (.28+.28), .553 is not close to .56). – LalakaJ Jul 29 '14 at 12:43
  • Is it OK to make the calculation on rounded values. I thought using the original values was the way to go? – LalakaJ Jul 29 '14 at 12:46
  • 1
    It depends on are the numbers are used. But you can't have both a rounded sum that matches the sum of rounded values and a rounded sum that matches the sum of the real values. You have to choose between one or the other. – user3679868 Jul 29 '14 at 12:56
  • What do you think is best. **Truncating** all the data and then performing the calculations or **rounding** all the data and then performing the calculations – LalakaJ Jul 29 '14 at 13:02
  • Rounding and make the calculation is the way to go. Thanks for the suggestion. Writing off the balance. I updated the question as well. – LalakaJ Jul 29 '14 at 13:27