آموزش آنالیز مغایرت گیری مالی با اکسل

 

Your goal is to become a data analytics professional. How can you make a profession out of your present skills?

 

The first step is to identify the most pressing issues, which are prevalent in many small and medium-sized businesses. For the greatest data analysis, financial control, and other features, they all require a system that processes raw entry data automatically.

To solve many problems even when designing the system mentioned above, you need to create a bearing or support. As a matter of fact, your best creativity to solve a problem is to construct a support such as the Brooklyn Bridge, a hybrid cable-stayed suspension bridge in New York City. You can review other examples at the below links:

Point (J) in article of “Executivemethods for solving of the problems (part 1)”

And article of  “TheSupporter Systems (sleepers) in the Nature for Tunneling”

The most important support for designing a system is a trustworthy database. The next step is to create independent supports by going to empty space outside of the system. For instance, in Microsoft Excel, you can add new columns between raw entry data and use nested IF statements, ideally forming your raw data.

مدل آنالیز طراحی شده با اکسل : راس گیری فاکتور وچک همزمان برای تا 200 مشتری

در زنجیره تامین, خصوصا شرکت های پخش و هلدینگ ها همراه با زیر مجموعه هایی شامل تولید – بازرگانی- توزیع و همچنین شرکت های تولیدی که محصول نهایی آنها بعنوان بخشی از مواد اولیه شرکت های تولیدی دیگر استفاده میگردد, معمولا دارای مشتریان زیاد (تامین کننده و خریدار) از صنایع گوناگون میباشند. از طرفی با توجه به گردش مالی بسیار بالا, مجبور به دریافت و پرداخت های اسنادی و موعدی هستند. یکی از مهمترین راههای سنجش رفتار مالی مشتریانی که جهت بدهی خود چک (اسناد پرداختنی موعدی) تحویل میدهند ,استفاده از راس گیری فاکتور و چک میباشد.

این مدل آنالیز, فرصتی را برای مدیران و تصمیم گیران شرکتها ایجاد میکند تا بطور همزمان بتوانند راس محاسبه شده برای فاکتورها و چک ها را تا 200 مشتری و برای حداکثر یک دوره سه ماهه فقط روی یک صفحه اکسل (یا در فرمت  pdfو قابل پرینت) بصورت داشبورد مدیریتی مشاهده و کنترل نمایند.

ضمنا در این مدل مشکل جمع و تفریق تاریخ های شمسی کاملا مرتفع شده بطوریکه بدون استفاده از کدهای VBA و تعریف ماژول های جدید, شما قادر خواهید بود هر دو عملیات جمع و تقریق را دقیقا مشابه تقویم میلادی دراکسل محاسبه نمایید.

راس گیری فاکتور وچک

راس گیری چک, محاسبه میانگین وزنی برای رسیدن به یک زمان واحد وصول از میان تعدادی چک صادر شده در زمان های مختلف میباشد.

 اما گاهی اوقات مشتری برای چندین فاکتور صادر شده در زمانهای مختلف, چندین فقره چک با تاریخهای وصول متفاوت تحویل میدهد. در این حالت ما نیاز داریم اول راس فاکتور ها را محاسبه نموده بطوریکه این زمان راس بعنوان مبدا زمانی محاسبه راس چکها در نظر گرفته خواهد شد. سپس راس زمانی چکها را محاسبه نماییم.

بعنوان مثال: فرض کنیم مشتری X در زمانهای مختلف از ما خرید نموده و شش فاکتور برای او بشرح زیر صادر شده است:

همانطور که مشاهده می کنید, مبدا زمانی کلیه فاکتورها تاریخ 03/02/1400 میباشد. حالا باید فاصله زمانی هر فاکتور را از این مبدا بدست آورده و در مبلغ فاکتور (بستانکاری) ضرب نماییم:

در این مرحله نتایج را با هم جمع می کنیم:

سپس جمع حاصل را بر جمع کل مبلغ فاکتور ها تقسیم می کنیم:

در نتیجه راس زمانی فاکتور ها برابر 27 روز است که اگر به مبدا زمان اضافه نماییم, تاریخ مبدا برای محاسبه راس چکها معادل 30/02//1400 خواهد بود.

این تاریخ راس فاکتورها بعنوان مبدا زمانی جهت محاسبه راس چکها در نظر گرفته میشود.

حالا فرض کنیم مشتری X جهت تسویه فاکتورها, تعداد 5 فقره چک بشرح زیر تحویل میدهد:

دقیقا شبیه به محاسبه راس فاکتورها, فاصله زمانی چکها را از تاریخ مبدا (تاریخ راس فاکتورها) محاسبه نموده و در مبلغ چکها ضرب می کنیم:

سپس نتایج بدست امده را جمع می کنیم:

و عدد حاصله را بر جمع کل مبلغ چکها تقسیم می نماییم:

در نتیجه راس چکها برابر 209 روز است که اگر به مبدا زمان اضافه نماییم, تاریخ راس چکها برابر 24/09//1400 خواهد بود.

حال فرض کنیم نرخ بهره سالیانه مورد توافق طرقین معادل 36 در صد باشد, میزان سودی که مشتری X خواهد پرداخت بشرح زیر محاسبه میگردد:

در نتیجه کل مبلغ پرداختی مشتری X برابر خواهد شد با:

راس گیری فاکتور وچک همزمان برای تا 200 مشتری

در این مدل آنالیز طراحی شده با اکسل, شما قادر خواهید بود تا همزمان برای 200 مشتری در طول یک دوره سه ماهه راس چک برای همه مشتریان را روی یک صفحه اکسل بصورت یک داشبورد مدیریتی بررسی و کنترل نمایید.

روش استفاده از این مدل گام به گام و بترتیب در ادامه می آید:

1. از نرم افزار حسابداری, لیست نام کلیه مشتریان را روی اکسل خروجی گرفته و در شیت Data Base این مدل وارد می کنیم

2. ورود اطلاعات مربوط به بستانکاری:

 اگر از نرم افزار حسابداری استفاده میکنید بطورمثال: نرم افزار سپیدار, شما میتوانید از منوی "شرکت" وارد قسمت "فهرست" شوید سپس فرم "گزارش ساز" را انتخاب نموده و در صفحه باز شده تیک علامت "(+) مشتریان و فروش" را زده و از “صورت‌حساب مشتریان” بعد از تایید فرم کوچکی که باز میشود,تنها از مشتریانی که مانده حساب صفر دارند,گزارش خروجی اکسل صادرنمایید.

 حالامجددا وارد "سیستم مشتریان و فروش" شده و از قسمت عملیات “مرور فروش” را انتخاب نمایید. سپس بازه تاریخی مورد نظر را وارد نمایید. و از تب "اسناد فروش" و یا گزينه "فاكتور فروش", از کلیه صفحات مربوط به فاکتور های فروش خروجی اکسل تهیه کنید. دراین مرحله در گزارش اکسل, فاکتورهای برگشتی (مرجوعی) را فیلتر نموده و همه آنها را از گزارش حذف نمایید.

در این مرحله گزارش اکسل فاکتور های فروش باید با گزارش اکسل صورت حساب مشتریان همسان سازی شود و مغایرت ها حذف  گردد. شما میتوانید برای این عملیات همسان سازی, از مدل آنالیز مغایرت گیری (1 ) استفاده نمایید.

حالا گزارش کامل فروش شما آماده هست و میتوانید آن را وارد شیت بستانکار مدل آنالیز راس گیری وارد نمایید.

یک نمونه مثال بشرح زیر میباشد:

نکته بسیار مهم صحت سنجی این گزارش است. با توجه به اینکه اکثر شرکت ها اسناد حسابداری را بصورت دستی وارد می کنند, شما میتوانید گزارش نهایی فروش فوق را مستقیما از "دفتر معین ویا تفضیلی" - "بدهی های جاری" از "حساب درآمد و فروش" تهیه نمایید.

 پس از ورود به سیستم سپیدار در منوی سمت راست ، وارد قسمت حسابداری شده و در قسمت عملیات روی گزینه “مرور حسابها” كلیك كنید . فرمی كه در اختیار شما قرار می گیرد، می توانید اعمال فیلتر نموده و ازگزارش مربوط به "حساب درآمد و فروش"خروجی اکسل تهیه نمایید.

جهت صحت سنجی و کنترل فاکتور های فروش, می توانید اطلاعات گزارش کامل فروش (اکسل اول) را بهمراه اطلاعات مربوط به گزارش "حساب درآمد و فروش" (اکسل دوم) وارد مدل آنالیز مغایرت گیری (1 ) نمایید و سیستم بطور خودکار مغایرت ها را به شما نشان میدهد.

3. ورود اطلاعات مربوط به بدهکاری:

مشابه قسمت قبل (ورود اطلاعات مربوط به بستانکاری), از سیستم “صورت‌حساب مشتریان” اما این بار از کلیه مبالغ پرداخت شده (نقد وچک) توسط مشتریانی که مانده حساب آنها صفر است, گزارش اکسل تهیه نمایید.

 سپس از  سیستم "اسناد فروش", فقط از فاکتورهای برگشتی (مرجوعی) گزارش اکسل بگیرید. نهایتا اطلاعات هر دو اکسل را در یک ستون ترکیب نمایید. حالا گزارش کامل بدهی شما آماده هست.

جهت صحت سنجی, شما میتوانید گزارش نهایی بدهی فوق را مستقیما از سیستم حسابداری و "دفتر معین ویا تفضیلی" - "دارایی های جاری" و از "حساب صندوق " ,"حساب بانک " , "حساب اسناد دریافتنی" و نهایتا " حساب هزینه ها (مربوط به فاکتورهای برگشتی)" گزارش اکسل صادر نمایید. سپس با استفاده از مدل آنالیز مغایرت گیری (1 ) هر دو گزارش فوق را مقایسه و کنترل کنید.  

یک نمونه مثال بشرح زیر میباشد:

4. راس گیری چکها برای همه مشتریان

در نتیجه نهایتا پس از ورود اطلاعات بستانکاری و بدهکاری سیستم بصورت خودکار, راس چک ها را برای کلیه مشتریان همراه با سود توافقی محاسبه و اعلام می کند:

ویدیو کلیپ زیر مربوط به روش کار این مدل می باشد:

Income statement simulation model

 

ABSTRACT

 

By using “Sensitivity Analysis” in Microsoft Excel, we will be able to analyze an algorithm with a maximum of two independent variables (two-way data table). The purpose of this project is to present an analytic model made by Microsoft Excel where this model allows us to analyze an algorithm with up to four independent variables. An example of an “Income Statement Simulation Model” to obtain a maximum of 10 scenarios has been presented in which this model shows us how the independent variables of Sales growth rate, COGS growth rate, Operating expenses growth rate, and Interest growth rate should be combined to reach a target net income based on the data of previous year.

Introduction

 As you know, there are three important financial statements to show the financial performance of a company in a specific period of the time which are as follows:

- Income Statement

- Balance sheet

- Cash flow statement

 An income statement which sometimes is referred to as a “profit and loss statement (P&L)” is included some independent variables such as revenue, Cost of Goods Sold, expenses, gains, and losses where an algorithm is establishing a logical relationship among them to reach a net income. This document can help to business owners make decision about the company’s strategies how the combination of increase or decrease on the growth rate of these independent variables will lead the company to earn more profit.

Income Statement Simulation Model

 Suppose we need to know that if we want to reach a growth rate of ”X” on previous year of net income our company in the next year, how the independent variables of “Sales growth rate”, “Cost of Goods Sold growth rate” , “Operating expenses growth rate” and “Interest growth rate” will move. Definitely the speed of this analysis is very important because every time we have to change and compare these variables with the conditions of macroeconomic indicators such as Inflation, GDP, Interest, Unemployment, CPI, and so on.

Basically, we will determine a target net income in accordance with a growth rate of ”X” for the next year and  we will examine many scenarios combined with above four independent variables to reach our target net income. This Income Statement Simulation model can help us to increase the acceleration of our analysis with the least error to find out the best optimized solutions.

 How does this model work?

 First of all, we need to import four levels of the data, such as the INPUTS, into the cells of this model as follows:

1. The data from the income statement of your company related to the previous year. 

2. To enter the range for all four independent variables (Min & Max)

3. To choose a minimum for the error

4. To determine the growth rate for the target net income

 Now, we have a maximum of 10 scenarios (OUTPUTS) for a combination of four independent variables, including “sales growth rate”, “Cost of Goods Sold growth rate”, “Operating expenses growth rate” and “, Interest growth rate”, where the impact of these variables will be approximately equal to our target net income and also the OUTPUTS are included amounts of minimum, average, and maximum for these valuables. For comparing the average results of this model and defined target net income based on the percentage of error, we are referred to the cells of “A16:B18”.

Please see the below video:

When we start our analysis with this model, for 10 combinations of these variables, we will also obtain 10 scenarios for income statement as follows:

Please see the below video:

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

WhatsApp: +98 9109250225

A Predictive Model to Discover the Relationship among Stocks, Forex, and Commodities: Application of DEMATEL Method

 

“Search, follow, and focus everywhere that there is very high daily frequency, high Beta and high market cap then take your analysis to organize your own portfolio”

ABSTRACT

 To determine the relationship between stocks, forex, and commodities, a prediction model utilizing Microsoft Excel has been created. Three variables are measured to determine the relationship: the distance between points, the growth curve, and the direction of the motion. A portfolio of 20 assets has been chosen, comprising stocks, forex, and commodities. The daily return rate (change %) calculation is the only criterion to find the association. The DEMATEL method of decision-making has been used to determine the importance of the influence assets collectively as well as the high and low influencers, where the criterion is the direction of the motion and the possibilities are 20 assets because the direction of the motion is the most crucial characteristic to quantify the relationship. The priority of the influence assets as a whole as well as the influential relationships has been established for future study using the DEMATEL technique.

Introduction

 One of the simplest statistical calculations you can carry out in Excel is correlation. Even though it is simple, it is a huge aid in understanding the relationships between two or more variables. It's crucial to understand that correlation simply reveals how closely two variables are related to one another. However, a correlation does not always predicate causation. It implies that the statement that altering one variable will alter another is not always valid. Therefore, we have to use different techniques to detect a causal relationship such as the nonlinear regression, Structural Equation Modeling (SEM), factor analysis, multiple regression analysis, path analysis, and so on. Python, R, or SCALA are the most common programming languages to learn for predictive modeling that is based on machine learning.

In this project, a predictive model using Microsoft Excel has been designed to extract the influential relationships among the assets.

 Predictive Model

A predictive model is a type of data analysis that projects activity, behavior, and trends using both recent and historical data.  Of course, when it comes to the fundamentals of prediction, we resort to traditional and fuzzy logic and state, "If p, then q." Everything begins with the relationship between these two hypotheses, "p" and "q," and is then continued and evolved by the world's cause-and-effect chain. Since there is no such thing as absolute zero in the cosmos, this network of networks will never come to an end. When I first heard about classical logic in high school, I remember wondering why the English letters "p" and "q" were used. A few years later, after learning about fuzzy logic, I speculated that these two letters might act as an "eyeglass" to help us see and understand our surroundings more clearly.

In this project, a predictive model has been designed using Microsoft Excel.

How is the data processed by this model?

There are four fields for data entry in Step 1: "Date" and "Price" for each asset. Excel requires four columns since we wish to compare two assets.

Step 2: Because there are typically certain gaps between the dates of two assets, the system automatically matches the dates of both assets into one column when we enter historical data for any asset (date and price). This acts as a kind of timing index.

Step 3: In front of the index date column, the price of each asset will be split into one column.

Step 4: The rate of return will be calculated for each asset’s price

Step 5: According to the return rate, the direction of motion for both assets and two consecutive days is evaluated; if the assets are moving in the same way, the result will be "1," otherwise "0." The total rows of return rate will then be divided by the total number of "1"s. The outcome will be regarded as the direction's likelihood.

Step 6: When calculating the return rate for both assets in a single day, the distance between the points will be taken into account. If the difference between the two points is less than the distance change, the result will be "1," otherwise "0." The total rows of return rate will then be divided by the total number of "1"s. The outcome will be regarded as the distance's probability.

Step 7: The growth curve between two assets will be measured, where if the difference return rate between two assets in one day and the difference return rate between two assets in the next day is less than the growth rate, it will be "1" otherwise "0". Then total numbers of ‘1" will be divided by total rows of return rate. The result will be considered as the probability for the growth curve.

Remark: It appears that steps 6 and 7 are equivalent. Even though there is a significant difference in the return rates of two assets, it is still feasible that their growth curves are similar. As a result, I must separate the growth curve and the distance appropriately. (Please view the video below.)

Step 8: The measurement of the correlation using Microsoft Excel for the time series of return rates for both assets

Step 9: Using the sensitivity analysis in Microsoft Excel to reach the distance and growth rate with more than 95% probability

Step 10: The aforementioned processes (5), (6), (7), and (8) are once more computed to forecast a one-day delay between two assets, where we say: "If asset X is high, then asset Y will be high in the next day" and vice versa. Each of the three variables' probabilities will be taken into account.

The figure below illustrates each of the aforementioned steps:

Finding

Comparing the currencies "EUR/HKD" and "EUR/AED" is one of the better examples that have been discovered.

We can observe that this model and the prior 10-year historical data have a strong relationship; therefore, we may use them as the index for future investigations.

Please see below video:

Let me verify the information above using the following historical diagram that I have taken from the Yahoo Finance website:

Historical data of EUR/HKD (more than 10 years) 

Historical data of EUR/AED (more than 10 years) 

As you can see, all three variables have a good relationship with each other, so we can say that for a relationship rate greater than 80%, there are the following connections:

If "EUR/HKD" is high or low, then "EUR/AED" is high or low.

And

If "EUR/AED" is high or low, "EUR/HKD" is high or low.

 Or:

Of course, if a one-day delay is predicted, the proportion will drop significantly.

 DEMATEL Method

This project's goal is to use the DEMATEL technique, where a portfolio of assets has only been evaluated using one criterion, the return rate. This method aids in depicting the ad hoc relationship between the alternative assets as well as prioritizing them.

The Battelle Memorial Institute's Geneva Research Center launched the Decision Making Trial and Evaluation Laboratory (DEMATEL). DEMATEL plots a causal link map and offers visual answers to issues, making it possible to separate many criteria into a cause-and-effect diagram.

The procedure of the DEMATEL technique is composed of five steps, as follows:

Step 1: Obtain the pair-wise comparison matrix and establish a measurement scale, such as a Likert scale.

Step 2: Extract the direct relationship matrix of influential factors.

Step 3: Calculate the normalized direction-relation matrix.

Where:

N = Normalized Direction-Relation Matrix

D = Direct Relation Matrix

d_ij = a member of Direct Relation Matrix

Step 4: Compute the Total Relation Matrix from below function:

Where:

 I = the identity matrix

N = Normalized Direction-Relation Matrix

T = Total Relation Matrix

 

Step 5: Obtain the causal relationship.

The i^th row and j^th column of the complete relationship matrix T should be designated as R_i and C_j. The sum (R_i + C_j) indicates the degree of significance that element i (i = j) has within the entire system. But (R_i - C_j) shows the net impact of element i with (i = j). The net cause is revealed when the difference is positive, and the net effect is shown when the difference is negative. The values of (R_i + C_j) are plotted along the x axis, while (R_i - C_j) are plotted along the y axis.

Application of DEMATEL to evaluate the relationships among the assets

The DEMATEL method's general steps have been used as follows:

Prior to beginning step 1, we must choose our portfolio of assets, which should include the company's stocks, currency pairings, cryptocurrencies, and commodities. Since there are only a few widely used currencies and commodities, picking them up doesn't need to take too much time, but choosing stocks does require a plan for research and analysis.

Typically, we order the stocks based on the six factors listed below:

1. High market capitalization (high market cap)
2. High beta (beta > 1.1)
3. Very high daily frequency on the daily prices
4. Very large range of changes (return rate) per day during a period of historical data
5. To track the change in volume per day during a period of historical data
6. Price-to-Earnings Ratio (PE ratio)

The top four factors are more crucial than the rest.

I have chosen 20 assets for this project to create an initial matrix, and they are as follows:

A ={X_1, X_2, X_{3 …}X₂₀}

The following are two different initial matrix types that have been created:

- DEMATEL approach on asset return rates with no delay

- One-day delay in the return rates of two assets using the DEMATEL approach

Both types of assets are examined for their direction of motion during two successive days, and the likelihood that a relationship will exist between the two is computed in order to create the initial matrix. It is obvious that future studies will delve into detail and depth using the variables of distance between the points and growth curve.

For both categories, a Likert scale has been taken into consideration as follows:

DEMATEL approach on asset return rates with no delay

Because the probability results represent a biconditional logical connective among the asset return rates in this procedure, the pair-wise comparison matrix is symmetric. As a result, step (1) is as follows:

Step 2: By establishing the measurement scale, we will have the below Direct Relation Matrix:

Step 3: Normalized Direction-Relation Matrix

Step 4: Total Relation Matrix

Step 5: Obtain the causal relation

As you can see, the most important asset in this portfolio has been denoted by “X₅”.

One-day delay in the return rates of two assets using the DEMATEL approach

In this instance, the return rate between two assets with a one-day delay is analyzed in both directions. As a result, we can predict the return rate of an item by a probability percentage using the return rate of another asset recorded the day prior.

Step 1: To develop the initial matrix

For example, we can say that if the return rate of X₁ is low or high, then the return rate of X₁₃ will be low or high in the next day by a likelihood of 63.2%, and if the return rate of X₁₃ is low or high, then the return rate of X₁ will be low or high in the next day by a likelihood of 45.7%".

Step 2: Obtain Direct Relation Matrix

Step 3: Normalized Direction-Relation Matrix

Step 4: Total Relation Matrix

Step 5: Obtain the causal relation

Finally, the causal relationship can be plotted as follows:

Conclusion

What, in fact, is X₁₂ or X₇? Undoubtedly, it relies on the way I select my portfolio. In order to arrive at a logical conclusion, we actually need to further investigate, analysis, and compare a large number of different portfolios.

 The strategy for future research can be summed up as follows:

- Going to details by defining a threshold limited to select a new portfolio and examine the distance point to point and growth curve

- Select numerous additional portfolios and contrast them to verify the earlier findings.

- Pick the time period for the historical data. To determine whether or not the outcomes are the same, we must choose different time windows (what are the start time and finish time?).

- To forecast a delay of two days, one week, or even one month as opposed to one.

Finally, a plan for future study should be developed to evaluate particular cause-and-effect assets like X₁₂ and X₇. This plan should include reviewing annual reports, 10-K and 10-Q reports, income statements, balance sheets, and other documents as well as using tools such as DCF and MC methods for further analysis.