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?
Suppose we have two columns credit and debit and 10 numbers on each column as follows:
Is
below equation true?
Suppose we have below accounts on credit and debit columns:Suppose we have two columns credit and debit and 10 numbers on each column as follows:
How many series are there for each
column? Definitely, there are 1024 series for each column because total subsets
of a power set with 10 members is equal 2^10 = 1024
Which series from column credit are
equal to some series in column debit? For example, we have below series for
credit column:
x1+ x2+x10+x5
x3+x7+x1
x4 + x3 + x 6 + x 7 + x8 + x9
And below series for debit column:
y2 + y4 + y6 + y8 + y10
y1 + y3 + y5 + y7
y2 + y5 + y9
x3+x7+x1 = y2 + y4 + y6 + y8 + y10
Question 2:
If we have discrepancy between total credit and total debit, where
is the location of this discrepancy?
For example: If we have:
total credit - total debit = C
?Which one of above series
does show us the location of discrepancy
Is below equation true?
(x4 + x3 + x 6 + x 7 + x8 + x9) – (y1 + y3 + y5 + y7) = C
This model answers to all above questions.
Below figures show you the components of this model:
The components of Inputs are as follows:
1. In left side on cells B3:B12, we fill amount of the
credits.
2. In right side on cells C3:C12, we fill amount of
the debits.
3. On cells B13 and C13, we have total
sum of credits and debits.
4. On cells B14 and B15, we have the
difference between credits and debits.
The components of final results for credit as Outputs are
as follows:
1. On column A, we have number of answers.
2. On each row between columns B to K which is
referred to one of the number of answers and also final results for debit, we have
final results of credits as an answer.
3. On column L, we have total sum of each row between
columns B to K.
The components of final results for debit as Outputs are
as follows:
1. On column M, we have discrepancies which are the
differences between total sums each row of columns B to K (column L) and total
sums each row of columns O to X (column N).
2. On column N, we have total sum of each row between
columns O to X.
3. On each row between columns O to X which is
referred to one of the number of answers and also final results for credit, we
have final results of debits as an answer.
Below figure shows you the overall picture of this
model.
In fact, each row is an answer for above questions in which left side
rows are related to credits and right side rows are related to debits. The
total sum of each row for credits minus the total sum of each row for debits
gives us the discrepancy.
You can also see below clips which are the examples for this model:
Now, let me start an complete sample to control accounting systems as follows:
Introduction
Introduction
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
An complete sample to control accounting systems
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.
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.
All researchers and individual people, who are interested in having this
model, don’t hesitate to send their request to below addresses: