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.

 

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

 

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:

See this Video on youtube:

You can also utilize this model to find out the discrepancies among the texts.

Please see below video:

See this Video on youtube:

If you have any questions, please do not hesitate to send them to my email address: Soleimani_gh@hotmail.com

Solving COMPLEX CAPTCHAs by Using an Analytic Model Made by Microsoft Excel

 

Nowadays, there are three types of CAPTCHAs, including text-based, picture-based, and sound-based. One of the most complicated picture-based CAPTCHAs is to match a given number which is equal to the total sum of many other numbers into a picture.

Suppose CAPTCHA gives you a picture of twelve dices and asks you to match a given number such as 48 which is equal to the total sum of some dices in the picture.

It could be impossible because you have to solve 2^12 scenarios (4096 scenarios) in a very short time.

This analytic model can help us to find the answer.

As you can see, we enter the given number into cell “B2” then all numbers of the dices into cell “B3”. If the status is “OK”, this is the answer. Otherwise, the answer is negative or “REJECTED”.

Please see the video of this model:

First, I assume all 12 dices are equal to 6. Then I changed some numbers where the total sum would be equal to the given number.

When the status returns to “OK”, we can obtain one answer or more, which are the alternative answers. 

If you have any question, please do not hesitate to send it to my email:

Soleimani_gh@hotmail.com

Decision Making Methods: Shanon Entropy, AHP, ANP, ISM, SAW, TOPSIS, VIKOR, ELECTRE, DEMATEL

You can find attached the links to the excel spreadsheets which include at least a sample of the decision-making methods below:

 AHP: 

 https://docs.google.com/spreadsheets/d/16k7sfeXzS5vjxZT0R8syksFKlBhXGzty/edit#gid=107806602

 ANP: https://docs.google.com/spreadsheets/d/1j766DLGXSkyODCr08VGN3z5y9roc4llX/edit#gid=2105940568

 ISM: https://docs.google.com/spreadsheets/d/1U1ZaBVDtkN29mZz6ur-HbsotZVNy82OS/edit#gid=1639052690

 SAW: https://docs.google.com/spreadsheets/d/1Jjnoz79mxKbD5GbkyD8rgJdmok81hTGh/edit#gid=398772179

 TOPSIS: https://docs.google.com/spreadsheets/d/1Jjnoz79mxKbD5GbkyD8rgJdmok81hTGh/edit#gid=398772179

 VIKOR: https://docs.google.com/spreadsheets/d/1Jjnoz79mxKbD5GbkyD8rgJdmok81hTGh/edit#gid=398772179

 ELECTRE: https://docs.google.com/spreadsheets/d/1Jjnoz79mxKbD5GbkyD8rgJdmok81hTGh/edit#gid=398772179

 DEMATEL: https://docs.google.com/spreadsheets/d/1jrHhC2PJJRqF6FTmB2CQgB-GgHvsWuvq/edit#gid=476016403

 10 C Model Decision Making method of ELECTRE: https://docs.google.com/spreadsheets/d/1JI0u4Gu3frs2kBmNmdbyb05xvYrW8BfX/edit#gid=404290178

  If you need any support do not hesitate to send your request to my email: soleimani_gh@hotmail.com

Artificial Intelligence by Using Microsoft Excel Controlling Accounts

Suppose your job is controlling and auditing accounts to find out the discrepancies between account receivables (sales) and saving accounts in banking systems. Now, consider a manufacturing company which has the network of chain stores (retail chains) and saving accounts in different banks so that all payments made by customers (Sales) in each store can be distributes and deposited among different saving accounts (below figure) something like a network. There are two types of payment: 1. ATM system 2. POS machines. Assume you are willing to compare the payments of customers (account receivables) with different saving accounts in banks for preparing a trial balance to ensure accuracy and reach the balance to zero for a period of three months. What are your challenges? For example: 1. It is possible, there will be simultaneously the transactions which show exactly the same numbers among different stores and banks. 2. The POS systems usually accumulate the payments made by customers. It means that you will not be able to control the accounts item to item and one by one. 3. Sometimes a customer send to you a payment several times. You are expected to recognize these frauds and to claim on the customer. And maybe other challenges. I have designed a model which is able to resolve all above challenges. I name this model something like the Artificial Intelligence by using Microsoft Excel. If you are interested in having this model, please do not hesitate to send your request to my email: soleimani_gh@hotmail.com Phone number: +98 9109250225

Creating New Logic’s Laws by Using Coding in Microsoft Excel

Law (1):

(p → q) I ((p → q) → r) I (((p → q) → r)) → s) I ((((p → q) → r)) → s) → t) I……

This Logic always “TRUE”

Examples:

Law (1) for three propositions (variables)

Law (1) for four propositions (variables)

Law (1) for five propositions (variables)

Law (2):

(p → q) & ((p → q) → r) & (((p → q) → r)) → s) & ((((p → q) → r)) → s) → t) &……

This logic, under below conditions always is “TRUE”:

·     -    p = False and other propositions (variables) are “TRUE”

·      -   p and q = False and other propositions (variables) are “TRUE”

·     -    All propositions (variables) are “TRUE”

Examples:

Law (2) for three propositions (variables)

Law (2) for four propositions (variables)

Law (2) for five propositions (variables)

To be continued .....

Analysis and Design Open Oscillatory Systems with Externally Forced Harmonic Motion (3)

 Following the article of Analysis and Design Open Oscillatory Systems with Forced Harmonic Motion (2), the purpose of this article is to present a model for finding the characteristics of an oscillatory system with forced harmonic motion where the acceleration will be equal zero. In this case, there is an interesting point that is related to an important difference between SHM and an oscillatory system with forced harmonic motion.

If an object is oscillating under simple harmonic motion, its linear velocity will be zero at the highest and lowest points where we have maximum displacement which is named the amplitude (A).On the other hand; at the maximum level of the displacement (x = A), the acceleration has also its maximum magnitude while at the middle (x = 0), acceleration is zero due to stop at those points in order to change direction while velocity gains its maximum magnitude at the equilibrium point (x = 0). At the extreme ends (x = A), when we have the maximum force and kinetic energy, the acceleration has its maximum magnitude. Therefore, the maximum of acceleration magnitude in simple harmonic motion occurs at maximum displacement (A) and acceleration at the middle is zero when we have the displacement equal to zero just like the below diagrams: 

But there is a different story about the oscillatory systems with forced harmonic motion. In this case, at the some points where the displacement is not zero (x = a), we have the acceleration equal to zero. Please see below diagrams:

Now, the question is: What are the characteristics of an oscillatory system with forced harmonic motion where we have the acceleration equal to zero at the point of x = a?

Below model is able to answer above question:

The components of above model are as follows:

1. In right side on cells L6:M6, we have inputs including given data of “Fm”, and “t”.

2. In left side on cells H6:J7, we have other inputs including lower and upper ranges for independent variables of “m”, “b” and “ω″/ω” to reach the answers for the acceleration equal to zero which are the characteristics of driven oscillatory system. Here, there are lower and upper ranges which are changed by click on cell A2 and also this change will again go back by click on cell B2 (Go & Back).

3. On cells I9:I18, we have outputs which are the characteristics for driven oscillatory system responding to the acceleration equal to zero.

You can see below clip as the example for this model:

All researchers and individual people, who are interested in having this model, don’t hesitate to send their request to below addresses: