We have a problem to solve to do with making subledger/GL entries. This is a problem that I'm positive has already been solved in every accounting/ERP package out there....I just can't seem to find anybody who knows the answer.
When certain events occurs in our ERP system, we need to make a number of entries to our GL. A transaction may have multiple debits and credits split up over multiple accounts. The sum of all the debits must equal the sum of all the credits. The problem is that these entries must be made to the GL at exactly 2 decimal places of precision (dollars and cents). The reason that's a problem is because now we have to deal with rounding, and the rounding errors may work out so that the total debits do not equal the total credits.
I'll give an example. Lets say there was a transaction that credited $1.00 to account A, and had to split the debits evenly over accounts B, C, and D. After rounding this works out to debits of $0.33 to each of the B-D accounts. When you sum up the total debits it now only equals $0.99 which is unacceptable. It gets even trickier when you run into situations where the entries are not split up evenly over the accounts in question.
The simplest way would be to just choose one of the entries at random, and add/subtract the difference due to rounding from it so that the totals even out. Our accountants definately didn't like that idea. They don't want us to be randomly adding or subtracting from the entry values without some reasonable logic behind it.
My next suggestion was to create a separate account that all rounding error adjustments would go to. So in the above example, accounts B-D would be debited $0.33, and the extra $0.01 would be debited to this special rounding error account. Our accountants agreed this would work, but they thought the idea of having a separate account for rounding errors was ludicrous. They all agreed that in their whole careers as accountants they have never seen that done, and did not wish to do it here.
My next and final suggestion (this was arrived at with the help of one of my developers), was to implement an algorithm that would track the cumulative rounding error as it went through rounding the various entries that comprised the transaction. Before rounding each entry, it would first add to it the cumulative rounding error. This appears to work, and is a reasonable solution to the problem. I am curious if this is a common solution, and if not how this is done in other packages out there that must make ledger entries.
Here is a code example of the algorithm:
Public Enum EntryTypeEnum
Debit
Credit
End Enum
Public Class LedgerEntry
Public AccountId As Integer
Public Value As Decimal
Public EntryType As EntryTypeEnum
Sub New(ByVal value As Decimal, ByVal accountId As Integer, ByVal entryType As EntryTypeEnum)
Me.Value = value
Me.AccountId = accountId
Me.EntryType = entryType
End Sub
End Class
Public Class LedgerTransaction
Private _CreditBias As Decimal
Private _DebitBias As Decimal
Public Entries As New List(Of LedgerEntry)
Public Sub AddCredit(ByVal val As Double, ByVal accountId As Integer)
Entries.Add(New LedgerEntry(SmartRound(val, _CreditBias), accountId, EntryTypeEnum.Credit))
End Sub
Public Sub AddDebit(ByVal val As Double, ByVal accountId As Integer)
Entries.Add(New LedgerEntry(SmartRound(val, _DebitBias), accountId, EntryTypeEnum.Debit))
End Sub
Private Function SmartRound(ByVal value As Double, ByRef bias As Decimal) As Decimal
Dim result As Decimal
value += bias
result = Decimal.Round(value * 100) / 100
bias = value - result
Return result
End Function
End Class
This is the algorithm we are currently planning on using. It is simple, elegant, and appears to work for all situations. I wrote a quick automated test that loops indefinately through random situations involving a random # of accounts, and a random distribution of values among the credits and debits, and this code appears to pass all situations. The code I used to test it is below:
Private Sub TestLedgerTransaction()
Dim trans As LedgerTransaction
Dim TotalAmount As Double
Dim NumDebits As Integer
Dim NumCredits As Integer
Dim RndGen As New Random
Dim TotalDebits As Decimal
Dim TotalCredits As Decimal
While True
trans = New LedgerTransaction
TotalAmount = RndGen.NextDouble() * 10000
NumDebits = RndGen.Next(1, 6)
NumCredits = RndGen.Next(1, 6)
Dim DebitDist As List(Of Double) = GenDistribution(NumDebits)
Dim CreditDist As List(Of Double) = GenDistribution(NumCredits)
For Each x As Double In DebitDist
trans.AddDebit(TotalAmount * x, 123456)
Next
For Each x As Double In CreditDist
trans.AddCredit(TotalAmount * x, 123456)
Next
TotalDebits = 0
TotalCredits = 0
For Each entry As LedgerEntry In trans.Entries
If Decimal.Floor(entry.Value * 100) <> (entry.Value * 100) Then
Throw New Exception("The value wasn't rounded to 2 decimals.")
End If
If Decimal.Ceiling(entry.Value * 100) <> (entry.Value * 100) Then
Throw New Exception("The value wasn't rounded to 2 decimals.")
End If
If entry.EntryType = EntryTypeEnum.Debit Then
TotalDebits += entry.Value
Else
TotalCredits += entry.Value
End If
Next
If TotalDebits <> TotalCredits Then
Throw New Exception("The Total Credits (" & TotalCredits.ToString() & ") don't equal the Total Debits (" & TotalDebits.ToString() & ").")
End If
End While
End Sub
Public Function GenDistribution(ByVal NumItems As Integer) As List(Of Double)
Dim result As List(Of Double) = New List(Of Double)(NumItems)
Dim ResultSum As Double = 0
Dim RndGen As New Random
Dim dist As Double
For i As Integer = 1 To (NumItems - 1)
dist = RndGen.NextDouble() * (1 - ResultSum)
result.Add(dist)
ResultSum += dist
Next
result.Add(1 - ResultSum)
Return result
End Function