A Simulation Model for Analysis of Bond Valuation

 

A bond is a type of asset in which a government or a company issues these securities as a long-term debt to borrow money from institutional investors (banks) or the public sector.

This model's goal is to examine a bond's value using seven independent variables: the bond's current price (bond value), year-to-date (YTM), coupon rate, year, month, day, and time period (n).

This model analyzes seven independent variables non-concurrently while simultaneously solving an algorithm of an equation with three independent variables and producing the maximum and minimum of this function for a specified domain and range.

Finding the YTM (return rate) for a current price equal to bond value without the need for trial and error is one of the most important uses of this approach.

A Template for Income statement simulation model

 

A Template for Discounted Cash Flow Analysis (DCF) plus Monte Carlo Method to Analyze Share Price of a Company

 

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

 

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