Geeks With Blogs


Dylan Smith ALM / Architecture / TFS

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
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)

        For Each x As Double In CreditDist
            trans.AddCredit(TotalAmount * x, 123456)

        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
                TotalCredits += entry.Value
            End If

        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)
        ResultSum += dist

    result.Add(1 - ResultSum)

    Return result
End Function
Posted on Thursday, July 20, 2006 11:30 AM | Back to top

Comments on this post: Rounding GL Entries

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © Dylan Smith | Powered by: