Accounting Software for Dealers, Traders, and Brokers

 

Introduction

 When we are speaking about accounting software, it is expected to manage, record, and report the day-to-day financial transactions of a firm. Of course, there are many types of accounting software for the specific needs of an industry or business. This accounting software can be used for multi-currency transaction recording.

The purpose of this project is to control and record daily financial transactions and business deals in multi-currency for reporting daily customer's account balances by using accounting software based on Microsoft Excel.

How does this software wotk?

The most powerful and distinguishing feature of this accounting software is its ability to manage and record business deals, and by recording the appropriate format of any deal, the system automatically calculates the complicated tasks.

Let me start with some examples:

Suppose you are managing a network of customers, deals, and financial transactions just like the one cited below:

 You agreed with your customer (X), who will transfer EUR 480,000 on behalf of you due to your Performa Invoice (Buying some industrial machinery), while you will pay him Dirham (AED). Your deal is as follows:

 Company "X" will charge you a fee of 0.4% for your EUR remittance (1- 0.004 = 0.996), and this company will also consider the ask-bid spread of 35 pips on the rate of EUR / AED = 4.0856.

 How can you calculate the cost of this business deal, which is your loss?

 Solution:

Let me record this deal in a cell of Excel as follows:

Purchase 480,000*1.004 EUR @ 4.0856+0.0035 AED

 Or

Purchase 480,000/0.996 EUR @ 4.0856+0.0035 AED

 Please see the below video:

As you can see, this software automatically calculates the amounts of sale, purchase, and Profit or Loss where we have:

Your purchase (Received) of EUR = EUR 481,920

 Your sale (Paid) of AED = AED 1,970,619

 Your loss = AED (9,531)

In the next business deal, you will sell this industrial machinery to company "Y" for 2% profit plus an ask-bid spread of 45 pips, and you will receive Russian rubles (RUB) from company "Y."

We can calculate your profit due to this deal as follows:

The deal will be:

 Sell 480,000*1.02 EUR @ 101.2297+0.0045 RUB

 See below video:

Then, Mr. "Z" will pay you CAD for all amounts of your RUB by 0.135% less than the rate of CAD/RUB = 68.6724.

Definitely, you will gain from this deal as follows:

This deal will be:

 Sell 49,564,264 RUB @ 68.6724/1.00135 CAD

 See below video:

Finally, the time is over, and you should pay your debt to company "X.". In this case, you have to sell to Mr. "W" the amounts of CAD to receive Dirham. He will charge you a remittance fee of 0.3% and an ask-bid spread of minus 30 pips on the rate of CAD/AAED = 2.7778 as follows:

We can record this deal just like the one cited below:

Purchase 1,970,619*1.003 AED @ 2.7778-0.003 CAD

 I will also record this business deal into accounting software, and then we can glance at the final account balance as follows:

Here is a screenshot from account balance:

According to the above balance account:

 You have a debt to company "X" equal to AED 1,970,619

When you receive the industrial machinery from company "X" and deliver it to company "Y", the amounts of EUR 481,920 and EUR 489,600 will automatically be cleared.

You should receive the amount of RUB 49,564,264 from company "Y" due to the delivery of the industrial machinery.

In exchange for receiving CAD 722,724, you are expected to pay the amount of RUB 49,564,264 to Mr. Z.

Your debt to Mr. W is equal to CAD 712,315 and your credit is equal to AED 1,976,531.

 You only need to record the deals in the mentioned format into a cell, and then you will automatically have all account balances on a sheet of Excel. I have named this model Artificial Intelligence by Microsoft Excel.

The next step is to keep track of the accounts that your customers have paid and received in order to update the balances.

For clearing accounts, Mr. "W" will transfer the amount of AED 1,970,619 on behalf of you to the banking account of company "X.".

When you deliver the machinery to company "Y", the amounts of EUR 481,920 and EUR 489,600 will be settled.

The company "y" will pay the amount of RUB 49,564,264 to Mr. Z," and he will pay the amount of CAD 712,315 to Mr. W on behalf of you.

The difference between the amounts of AED 1,970,619 and AED 1,976,531 will be equal to the fee charged for the business deal with Mr. W," which should be cleared.

Now, you will have the updated balances for all your customers.

As a result, your total profit will be equal to CAD 10,409.

Please see the below video:

If you have any questions, do not hesitate to send your request to my email:

soleimani_gh@hotmail.com

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 .....