Wednesday, October 28, 2015

A Case of Accounting Control System Solved by a New Idea



One of the most crucial reasons behind financial crisis in the world during the last decades, could be referred to business scandals and not to adhere business ethics in which it resulted in massive investment losses and numerous employee layoffs. According to KPMG’s report, financial reporting fraud (the international misstatement of financial reports) was the most costly to companies. Despite some efforts such as legislation referred to as the Sarbanes-Oxley Act of 2002 (SOX), we saw another financial crisis in 2008. These results show us the lack of an effective accounting control system in some companies around the world. Of course, big companies like Motorola, IBM, and Nike use complex systems to control and evaluate the actions of managers whereas some small companies still suffer not to have an authentic accounting control system. In this case, there are two types of people who have been involved in accounting jobs. Type one, they are someone who participate in business scandal to earn fraud money while type two are the people who want to be honest and truthful to fulfill their job but they are not able to audit the accounts for solving the problem of account balance by using of available software and finally they are disappointed and urged to present financial reporting fraud.
The purpose of this article is, trying to help the people type two such as CEOs, CFOs, Accountants, Auditors, Treasurers, and Controllers who cannot find out real proof of discrepancy among accounts and they have to fake some bills and invoices to reach the balance of accounts and finally prepare false financial report. For this approach, a case has been analyzed and solved by using a new idea derived from article of “EMFPS: How Can We Get the Power Set of a Set by Using of Excel?” posted on link: http://emfps.blogspot.com/2012/08/emfps-how-can-we-get-power-set-of-set.html


 Case:  Finding balance between debit and credit

Assume we have 10 numbers on column of debit and 10 numbers on column of credit.

Question (1) : Which numbers are on columns debit and credit in which the total sum of one or two or three or more them on column of debit equal to total sum of one or two or three or more from numbers on column of credit?

Question (2): The most important concern is to find out the discrepancies. Where are the sources of inequalities? Which areas show us the discrepancies?
The numbers are as follows:






Important Note: “The balance of accounts in accounting is something like (0, 1) in electrical and control engineering where (0) is true and (1) is false. It means, if the balance of accounts is (0) then it is ok. But if there is even $1 discrepancy between accounts, we should doubt because it is possible there is a big case behind of the accounts. Therefore, please do not say: It is negligible.”
Before going to solve above case, we should make time bench marking for all accounts including inventories and so on.
Time Bench marking
What is time bench marking?
Time bench marking is to determine section and overall time that we are willing to audit and examine the accounts and turn all accounts accompanied by their dates including debit, credit and so on adjusted on this range of time (overall time).
Why do we need it?
It helps us to find out the easiest way to control accounts but we usually reach to two big problems in this way (making time bench marking) as follows:
1.      We often find several payments or earnings in the same exact date, therefore we cannot turn these accounts on overall time to make time bench marking. Let me tell you an example as follows:

We have below accounts accompanied by their dates and we want to control them in the range of time between Oct 1, 2015 and Oct 31, 2015.






We have to sum simultaneous payments and earnings to include between above duration.
How can we do it?
At the first, we use DATA – Remove Duplicates in excel for dates of debit then we copy columns of date and debit on columns A and B in excel and also copy the results of dated inferred by DATA – Remove Duplicates on column C. Next, we copy formula:

=sumif($a$1:b50,c1,$b$1:$b$50) on column D and copy to down as follows:




Using the same way for dates of credit:




1.      Sometimes we have to pay to some contractors advance payment or we cannot receive the bill in the time of payment consequently our accounts will not be closed and we will have the mixed dates for our accounts (please see above excel sheets on column D)
How can we solve this problem to adjust on time benchmarking?
- On column A in excel sheet copy all dates between Oct 1, 2015 and Oct 31, 2015
- Above data of dates and debit copy on columns E and F
- Copy on cell B1formula:
 =IFERROR(INDEX($E:$F,MATCH($A1,$E:$E,0),COLUMN(A1)),"")
- Copy above formula to C1, then copy down both cells
Here is the results:






- Do above steps for dates and credit as follows
:






Now, we can return back to solve the case. When we find a problem, firstly we search the answers in GOOGLE. I also searched GOOGLE and found below VB code but I could not use from this code to solve the case. Maybe, this VB code helps you in the future:

Option Explicit

Function RealEqual(A, B, Optional Epsilon As Double = 0.00000001)
    RealEqual = Abs(A - B) <= Epsilon
    End Function
Function ExtendRslt(CurrRslt, NewVal, Separator)
    If CurrRslt = "" Then ExtendRslt = NewVal _
    Else ExtendRslt = CurrRslt & Separator & NewVal
    End Function
Sub recursiveMatch(ByVal MaxSoln As Integer, ByVal TargetVal, InArr(), _
        ByVal HaveRandomNegatives As Boolean, _
        ByVal CurrIdx As Integer, _
        ByVal CurrTotal, ByVal Epsilon As Double, _
        ByRef Rslt(), ByVal CurrRslt As String, ByVal Separator As String)
    Dim I As Integer
    For I = CurrIdx To UBound(InArr)
        If RealEqual(CurrTotal + InArr(I), TargetVal, Epsilon) Then
            Rslt(UBound(Rslt)) = (CurrTotal + InArr(I)) _
                & Separator & Format(Now(), "hh:mm:ss") _
                & Separator & ExtendRslt(CurrRslt, I, Separator)
            If MaxSoln = 0 Then
                If UBound(Rslt) Mod 100 = 0 Then Debug.Print "Rslt(" & UBound(Rslt) & ")=" & Rslt(UBound(Rslt))
            Else
                If UBound(Rslt) >= MaxSoln Then Exit Sub
                End If
            ReDim Preserve Rslt(UBound(Rslt) + 1)
        ElseIf IIf(HaveRandomNegatives, False, CurrTotal + InArr(I) > TargetVal + Epsilon) Then
        ElseIf CurrIdx < UBound(InArr) Then
            recursiveMatch MaxSoln, TargetVal, InArr(), HaveRandomNegatives, _
                I + 1, _
                CurrTotal + InArr(I), Epsilon, Rslt(), _
                ExtendRslt(CurrRslt, I, Separator), _
                Separator
            If MaxSoln <> 0 Then If UBound(Rslt) >= MaxSoln Then Exit Sub
        Else
            'we've run out of possible elements and we _
             still don't have a match
            End If
        Next I
    End Sub
Function ArrLen(Arr()) As Integer
    On Error Resume Next
    ArrLen = UBound(Arr) - LBound(Arr) + 1
    End Function
Function checkRandomNegatives(Arr) As Boolean
    Dim I As Long
    I = LBound(Arr)
    Do While Arr(I) < 0 And I < UBound(Arr): I = I + 1: Loop
    If I = UBound(Arr) Then Exit Function
    Do While Arr(I) >= 0 And I < UBound(Arr): I = I + 1: Loop
    checkRandomNegatives = Arr(I) < 0
    End Function
Sub startSearch()
    'The selection should be a single contiguous range in a single column. _
     The first cell indicates the number of solutions wanted.  Specify zero for all. _
     The 2nd cell is the target value. _
     The rest of the cells are the values available for matching. _
     The output is in the column adjacent to the one containing the input data.
   
    If Not TypeOf Selection Is Range Then GoTo ErrXIT
    If Selection.Areas.Count > 1 Or Selection.Columns.Count > 1 Then GoTo ErrXIT
    If Selection.Rows.Count < 3 Then GoTo ErrXIT
   
    Dim TargetVal, Rslt(), InArr(), StartTime As Date, MaxSoln As Integer, _
        HaveRandomNegatives As Boolean
    StartTime = Now()
    MaxSoln = Selection.Cells(1).Value
    TargetVal = Selection.Cells(2).Value
    InArr = Application.WorksheetFunction.Transpose( _
        Selection.Offset(2, 0).Resize(Selection.Rows.Count - 2).Value)
    HaveRandomNegatives = checkRandomNegatives(InArr)
    If Not HaveRandomNegatives Then
    ElseIf MsgBox("At least 1 negative number is present between positive numbers" _
                & vbNewLine _
            & "It may take a lot longer to search for matches." & vbNewLine _
            & "OK to continue else Cancel", vbOKCancel) = vbCancel Then
        Exit Sub
        End If
    ReDim Rslt(0)
    recursiveMatch MaxSoln, TargetVal, InArr, HaveRandomNegatives, _
        LBound(InArr), 0, 0.00000001, _
        Rslt, "", ", "
    Rslt(UBound(Rslt)) = Format(Now, "hh:mm:ss")
    ReDim Preserve Rslt(UBound(Rslt) + 1)
    Rslt(UBound(Rslt)) = Format(StartTime, "hh:mm:ss")
    Selection.Offset(0, 1).Resize(ArrLen(Rslt), 1).Value = _
        Application.WorksheetFunction.Transpose(Rslt)
    Exit Sub
ErrXIT:
    MsgBox "Please select cells in a single column before using this macro" & vbNewLine _
        & "The selection should be a single contiguous range in a single column." & vbNewLine _
        & "The first cell indicates the number of solutions wanted.  Specify zero for all." & vbNewLine _
        & "The 2nd cell is the target value." & vbNewLine _
        & "The rest of the cells are the values available for matching." & vbNewLine _
        & "The output is in the column adjacent to the one containing the input data."
    End Sub

   Answer to question (1): I applied a new idea derived from article of “EMFPS: How Can We Get the Power Set of a Set by Using of Excel?” posted on link: http://emfps.blogspot.com/2012/08/emfps-how-can-we-get-power-set-of-set.html and solved the case. The results are as follows:








As we can see, there are 19 statuses (rows) where the sum of numbers in each row for debit is equal to the sum of numbers in same row for credit.
 
Answer to question (2): It is solved by using of new idea mentioned in question (1). The results are as follows:



According to this case, total difference between debit and credit is equal to   -3,595,545,920.
Above tables show us that there are 20 sets of numbers or 20 areas for debit and credit in which we can find the discrepancies.