Suppose you
are expected to control the balance of the accounts between the sales of the
store network and the accounts receivable in the banking network as follows:
The above flowchart says to us, you should release the customer’s order to
conform to the invoice when you receive the bank receipt sent by the customer.
Nowadays, people are able to make fake receipts by using Artificial
Intelligence tools. On the other hand, it is possible that a customer will send
you repeated receipts. Thus, you need to take a rapid check of the invoices and
all bank accounts before sending the goods to the customer.
This analytic model helps you to find out all discrepancies very
soon.
By using this model, you will take
two types of the discrepancy analysis as follows:
1. When there are different amounts
of data on the sales and accounts receivable in the banking network
2. When there are the same amounts
of data on the sales and accounts receivable in the banking network but the
number of repeated amounts is different between the data on the sales and
accounts receivable in the banking network
Note: This
model does not cover the below conditions:
- If your
customer settles the sales invoice in partial payments during a period of time
- The partial
payments made by your customer are not exactly equal to the total sum mentioned
in the credit section of your bank account
These
conditions will be covered by a new analytic model that I will post next time.
(See Discrepancy Analysis (2): A Model Made by Microsoft Excel)
How does this
model work?
You only need to collect the sales
data from all stores and banking data from your entire bank network accompanied
by the dates and copy them into appropriate columns of this model.
Note: There are varieties of applications for this analytic model, in which you can compare two columns of texts or two columns of dates. Therefore, before starting, you need to define four titles for columns as shown in the below figure:
Examples:
I copy and paste the data into 4
columns. As you can see, firstly there is not any discrepancy. But I manually
changed some of the amounts in column sales and then changed some of the
amounts in column account receivable. You can see the results in Discrepancy
Analysis_2.
In the next step, I will repeat some
of the numbers in both columns. You can find the results in Discrepancy
analysis_ 1. For instance, when you see “3 or -3”, it means that there is this
amount in both columns but in one column 3 times more than another column.
Please see below video:
You can also utilize this model to
find out the discrepancies among the texts.
Please see below video:
If you have any questions, please do
not hesitate to send them to my email address: Soleimani_gh@hotmail.com