Discrepancy Analysis (2): A Model Made by Microsoft Excel

 

Sometimes finding the discrepancies between the accounts is easy, but that can be very complicated as a real case in which to extract the area of discrepancies would be impossible.

For instance, suppose your customer settles the sales invoice in partial payments during a period of time. On the other hand, it is possible; the partial payments made by your customer are not exactly equal to the total sum mentioned in the credit section of your bank account.

In fact, here we have two big problems with auditing the accounts:

 1.  In the credit section of a bank account, we have the total sum which has been divided into many partial payments on the debit section of a bank account.

 2. The above total sum is not exactly equal to the partial payments.

 

Following to the article of Discrepancy Analysis (1): A Model Made by Microsoft Excel , here is a new analytic model that it can help us to fix these two problems.

 

How does this model process the data?

 

The process of data will be accomplished into three phases:

 

1. To accumulate the data of Debit and Credit (Contraction)

 

2. To solve many equations simultaneously

 

3. To extend the data of Debit and Credit (Expansion)

 

Phase (1) when you enter data into the appropriate columns, the system automatically sums all amounts related to each day. Then, depending on your choice, this model accumulates the amounts the rows of debit and credit. For instance, suppose you have 400 rows of data for debit and 460 rows of data for credit. You are willing to get only 10 rows for debit and credit. When you enter your order of 10, the system automatically accumulates all 400 and 460 rows into 10 rows.

 

Phase (2) the data of phase (1) is transferred into two columns of “Debit” and “Credit” but maximum 10 rows. The chosen number of rows depends on the capacity of system (Type of processor, RAM and so on).

Here, we have 1024 equations that should be solved simultaneously.

Suppose there are below data on two columns:

The general format of equations can be as follows:

Phase (3) the result of phase (2) will give us some scenarios which are the solved equations but accumulated .Therefore, we will choose a scenario and transfer it into a model of phase (3) to extend the data (Expansion).

 By doing each three phases, we will finish a step. We have to repeat several steps to find out the date and the location where the discrepancies occurred.

 How does this model work?

 Firstly, we should enter the data of Debit and Credit into two columns. Secondly, we need to change two independent variables of “error” and “milestone” to achieve the best scenario.

 

What are the variables of “error” and “milestone”?

 “error” is following below formula:

By changing “milestone”, we are able to obtain the desired accumulated data in ideal row numbers. The final step is to copy and paste the results into the model of expansion and to repeat previous steps.

Let me take some examples:

 1. I will fill out the data in column Debit and also the same data in column Credit. Therefore, we do not have any discrepancy in the first step.

 2. Suppose the operator of accounting software took a mistake to record $ 10,000 instead of $ 1000 (Yellow highlighted). We have to change Milestone until we achieve a fixed date. Here is the location of the discrepancy.

 Please see the video below:

3. Now, I change $ 5000 to $50,000 in row 1540 and column Credit. By the same try, we will find the area of the discrepancy.

 

Please see the video below:

As you can see, we do not need to go to phase (3).

 4. In this example, suppose you have received a total amount for three invoices sent to the customer while there is the discrepancy between the total receiving and three invoices.

On column credit and row 1545, I add $2,500,000 but on column debit and rows

1545, 1546 and 1547, I add $ 1,500,000, $ 500,000 and $488,905. Here, when I change the error by more than $9000, I can access to a focus area, otherwise I receive three locations that show three areas of the discrepancies.

 Please see the video below:

 

NOTE:

Since this model is under test, interested people who have real

cases, do not hesitate to send their data to my email:

soleimani_gh@hotmail.com

Do not worry about the security of your data, because you can multiply or divide all the data of columns debit and credit by a positive real number such as 0.058, 3.7, 6, and so on, and then send it to me.