Microsoft Excel gives the user a whole toolkit for analysis financial activities enterprises, carrying out statistical calculations and forecasting.

Built-in functions, formulas, program add-ons allow you to automate the lion's share of the work. Thanks to automation, the user only needs to substitute new data, and based on them, ready-made reports will be automatically generated, which many people make for hours.

An example of a financial analysis of an enterprise in Excel

The task is to study the results of financial activity and the state of the enterprise. Goals:

estimate market value firms;
identify ways of effective development;
analyze solvency, creditworthiness.

Based on the results of financial activity, the manager develops a strategy for the further development of the enterprise.

Analysis financial condition enterprises implies

analysis of the balance sheet and income statement;
balance sheet liquidity analysis;
analysis of solvency, financial stability of the enterprise;
analysis of business activity, asset status.

Consider the techniques for analyzing a balance sheet in Excel.

First, we draw up a balance sheet (for example, schematically, without using all the data from Form 1).

Let's analyze the structure of assets and liabilities, the dynamics of changes in the value of articles - we will build a comparative analytical balance.

Using the simplest formulas, we displayed the dynamics by balance sheet items. In the same way, you can compare the balance sheets of different enterprises.

What results does the analytical balance give:

The balance sheet at the end of the reporting period has become more in comparison with the initial period.
Non-current assets are incremented at a higher rate than current assets.
The company's own capital is greater than its borrowed capital. Moreover, the growth rate of own exceeds the dynamics of borrowed.
Accounts payable and accounts receivable are growing at about the same pace.

Statistical Data Analysis in Excel

For implementation statistical methods Excel has a huge set of tools. Some of them are built-in functions. Specialized data processing methods are available in the Analysis ToolPak add-in.

Consider popular statistical functions.

In the example, most of the data is above average because asymmetry is greater than "0".

EXCESS compares the maximum of the experimental distribution with the maximum of the normal distribution.

In the example, the maximum distribution of the experimental data is above the normal distribution.

Send your good work in the knowledge base is simple. Use the form below

Students, graduate students, young scientists who use the knowledge base in their studies and work will be very grateful to you.

Hosted at http://www.allbest.ru/

FINANCIAL ANALYSIS INEXCEL

Task number 1

PMT or PMT function description (rate; nper; ps; bs; type)

Returns the amount of a periodic payment based on a constant payment amount and a constant interest rate.

rate is the interest rate on the loan.

nper -- total number loan payments.

ps is the value reduced to the current moment, or the total amount that is this moment equal to a series of future payments, also called the principal amount.

type is the number 0 (zero) or 1 indicating when the payment should be made.

Consider an example of calculating a 30-year mortgage loan with an interest rate of 8% per annum with an initial payment of 20% and a monthly (annual) payment using the PMT function

Function PMT (PMT) calculates the amount of a constant periodic payment of an annuity (for example, regular payments on a loan) at a constant interest rate.

Note that it is very important to be consistent in choosing the units of measurement for setting the RATE and NPER arguments. For example, if you do monthly payments for a four-year loan at the rate of 12% per annum, then use 12%/12 to set the RATE argument, and 4*12 to set the NPER argument. If you are making annual payments on the same loan, use 12% for the RATE argument and 4 for the NPER argument.

To find the total amount paid out during the payment interval, multiply the value returned by the PMT function by the value of NPER. A payment interval is a sequence of constant cash payments made over a continuous period.

In features related to payout intervals, the money you pay out, such as a savings deposit, is represented negative number, and the money you receive, such as dividend checks, is represented as a positive number.

For example, a $1,000 bank deposit is represented by -1000 if you are a depositor and 1000 if you are a bank representative.

INDIVIDUAL TASK. Calculate the n-year (total number of payment periods) mortgage loan for the purchase of an apartment for R rubles. with annual rate i % and initial payment A%, . Make a calculation for monthly and annual payments. Find the amount of periodic monthly and annual payments, the total amount of monthly and annual payments, the total amount of monthly and annual commissions.

To complete the task, fill in the table with your initial data:

Apartment price - R

Annual rate i%

Loan maturity n

Initial payment A%

The initial contribution in monetary terms is calculated by the formula:

apartment price*A%

Annual payments are calculated by the function

(PMT(rate; nper; ps; bs; type) or PPLAT(rate; term; -loan);

monthly payments

PMT(rate/12; term*12; -loan)), or PMT(rate/12; term*12; -loan)

where loan (ps) is the current value, i.e. the total amount that future payments will amount to (in our example, this is the difference between the cost of the apartment and the initial payment).

Total monthly = monthly*term*12

Total annual = annual*term

Monthly fees = total monthly - loan

Annual fees = total annual - loan

ASSIGNMENT OPTIONS

TASK #2

NPV (rate; value1; value2; ...) or refinery (rate; value1; value2; ...)

Returns the net present value of an investment using a discount rate and future payouts (negative values) and future receipts (positive values).

rate -- the discount rate for one period.

Value1, value2,... -- 1 to 29 arguments representing expenses and income.

Value1, value2, ... must be evenly distributed in time, payments must be made at the end of each period.

The NPV uses the order of the arguments value1, value2, ... to determine the order of receipts and payments. Make sure your payments and receipts are entered in the correct order.

Example 1


	Description
	Annual discount rate
	Initial investment costs for one year, counting from the current moment
	First year income
	Second year income
	Third year income
	Description (result)
NPV(A2; A3; A4; A5; A6)	Net present value of investment (1,188.44)

In the example, the initial cost of 10,000 rubles. were included as one of the values because the payment was made at the end of the first period.

Example 2


	Description
	Annual discount rate. It may represent the rate of inflation or the interest rate on competing investments.
	Initial Investment Costs
	First year income
	Second year income
	Third year income
	Fourth year income
	income for the fifth year
	Description (result)
NPV(A2; A4:A8)+A3	The net present value of this investment (1,922.06)
NPV(A2; A4:A8; -9000)+A3	Net present value of this investment with a loss of 9,000 in the sixth year (-3,749.47)

In this example, the initial cost of 40,000 rubles. were not included as one of the values because the payment was made at the beginning of the first period.

Consider the following problem. They ask you to lend 10,000 rubles and promise to return 2,000 rubles in a year, and 4,000 rubles in two years. Three years later - 7,000 rubles. At what annual interest rate is this deal profitable?

In the given calculation, the formula is entered into the cell in cell B7

= Refinery (V6; V2:V4)

Initially, an arbitrary percentage is entered in cell B6, for example 3%. After that, select the Service command, Parameter selection and fill in the Parameter selection dialog box that opens.

In the field Set in the cell, we give a link to cell B7, in which the net current amount of the deposit is calculated using the formula

= Refinery (V6; V2:V4)

In the Value field, enter 10000 - the amount of the loan. In the By changing the value of the cell field, we give a link to cell B6, in which the annual interest rate is calculated. After pressing the OK button, the selection tool will determine at what annual interest rate the net current amount of the deposit is 10,000 rubles. The result of the calculation is displayed in cell B6.

In our case, the annual discount rate is 11.79%.

Conclusion: if banks offer a large annual interest rate, then the proposed transaction is not profitable.

INDIVIDUAL TASK: You are asked to lend P rubles and promise to return P1 rubles. in a year, Р2 rub. - in two years, etc. and, finally, RN rub. after N years. At what annual interest rate does this deal make sense? (NPV(rate; value1; value2; ...). To specify the interest rate, use the parameter selection method.

TASK #3

PS(rate; nper; plt; bs; type) or PZ(rate; nper; pt; bs; type)

Returns the present (to date) value of an investment. Present (present) value is the total amount that currently equal to a number of future payments. For example, when you borrow money, the loan amount is the present (present) value to the lender.

For example, if a car loan is received at 10 percent per annum and monthly payments are made, then the interest rate for the month will be 10% / 12 or 0.83%. As the value of the rate argument, enter 10%/12 or 0.83% or 0.0083 into the formula.

For example, if a car loan is received for 4 years and monthly payments are made, then the loan has 4*12 (or 48) periods. As the value of the argument nper in the formula, you need to enter the number 48

pmt is a payment made in each period and does not change for the entire time the annuity is paid. Usually payments include principal payments and interest payments, but do not include other fees or taxes. For example, a monthly payment on a four-year loan of 10,000 rubles. at 12 percent per annum will be 263.33 rubles. Enter the number -263.33 into the formula as the value of the payout argument.

bs -- the required value of the future value or balance of funds after the last payment. If the argument is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0). For example, if you intend to accumulate 50,000 rubles. to pay for a special project for 18 years, then 50,000 rubles. this is the future value.

Example

The result is negative because it represents the money to be paid, the outgoing cash flow. If the annuity were required to pay 60,000, this investment would not be profitable, since the present value (59,777.15) of the annuity is less than this amount.

· Note. To get the monthly interest rate, divide the annual rate by 12. To find out the number of payments, multiply the number of years of the loan by 12.

An annuity is a series of regular cash payments made over a long period. For example, a car loan or mortgage are annuities.

In annuity functions, cash paid out, such as a savings deposit, is represented as a negative number; cash received, such as dividend checks, is represented as a positive number. For example, a deposit in a bank in the amount of 1000 rubles. represented by -1000 for the depositor and 1000 for the bank.

Consider the following problem. Let's say that you are asked to borrow 10,000 rubles and promise to return 2,000 rubles within 6 years. Will this deal be profitable at an annual rate of 7%?

In the calculation, the formula is entered in cell B5

\u003d PZ (B4; B2; -B3)

The PZ function returns the current amount of the deposit based on constant periodic payments. The PZ function is similar to the refinery function. The main difference between the two is that the PP function allows cash contributions to occur either at the end or at the beginning of the period. In addition, unlike the NPP function, cash contributions to the PZ function must be constant for the entire investment period.

INDIVIDUAL TASK. You are asked to lend R rub. and promise to return for A rub. annually for N years. At what interest rate does this deal make sense?

To solve the problem, use the function

(PS (rate; kper; plt; bs; type) or PZ (rate; term; -annual payments)). First, an arbitrary rate is taken in the function, then it is refined by the parameter selection method.

TASK #4

HPMT (rate; period; nper; ps; bs; type)

Returns the amount of interest payments on an investment over a given period based on a constant periodic payment amount and a constant interest rate.

rate -- the interest rate for the period.

period is the period for which you want to find interest payments; must be between 1 and nper.

nper is the total number of annuity payment periods.

Example


	Description
	Annual interest rate
	The period for which you want to find the interest
	Loan term (in years)
	Current loan value
	Description (result)
HPMP (A2/12; A3*3; A4; A5)	Interest payments for the first month on the above terms (-22.41)
HPMT (A2; 3; A4; A5)	Interest payments for Last year on the above terms (interest is accrued annually) (-292.45)

OSPLT(rate; period; nper; ps; bs; type)

Returns the amount of the principal payment for an investment over a given period based on constant periodic payments and a constant interest rate.

rate -- the interest rate for the period.

period -- sets the period, the value must be in the range from 1 to "nper".

nper is the total number of annuity payment periods.

ps -- the present value or the total amount, which is currently equivalent to a number of future payments.

bs - the required value of the future value, or the balance of funds after the last payment. If the argument bs is omitted, then it is assumed to be 0 (zero), i.e. for a loan, for example, the value of bs is 0.

type is a number 0 or 1 indicating when the payout should be made.

Notes

Make sure you are consistent in your choice of units of measure for the "rate" and "nper" arguments. If you are making monthly payments on a four-year loan at 12 percent per annum, then use 12%/12 for the rate argument and 4*12 for the nper argument. If you are making annual payments on the same loan, then use 12% for the rate argument and 4 for the nper argument.

Example

Let's consider an example of calculating principal payments, interest payments, total annual payments and the balance of the debt on the example of a loan of 1,000,000 rubles for a period of 5 years at an annual rate of 2%.

excel spreadsheet formula

the annual fee is calculated in cell B3 using the formula

=PLAT(interest; term; - loan amount)

For the first year, the interest payment in cell B7 is calculated using the formula

=D6*$B$1

Main board $B$3-B7

The remaining debt in cell D7 is calculated by the formula

=D6-C7

In the remaining years, these fees are determined by dragging the fill marker of the selected range B7:D7 down the columns.

Note that the principal fee and the interest fee could be directly found using the functions FOSPLAT and PPROU, respectively.

INDIVIDUAL TASK. Calculate the annual principal payments, the interest fee, the total annual payment and the balance of the debt on the example of a loan R rub. at an annual rate of i% for a period of N years.

Use functions

(PMT(rate; nper; ps; bs; type), PRPT(rate; period; nper; ps; bs; type), SPLT(rate; period; nper; ps; bs; type))

PPLAT(rate; term; -loan), PPROTS(rate; period; term; - loan), OSNPLAT(rate; period; term; -loan).

Remaining debt = debt - OSNPLAT

TASK #5

NPER (rate; plt; ps; bs; type)

Returns the total number of payment periods for an investment, based on periodic constant payments and a constant interest rate.

rate -- the interest rate for the period.

pmt is the payment made in each period; this value cannot change during the entire payment period. Typically, a payment consists of a principal payment and an interest payment and does not include taxes and fees.

ps -- the present value or the total amount, which is currently equivalent to a number of future payments.

bs -- the required value of the future value or balance of funds after the last payment. If the argument bs is omitted, then it is assumed to be 0 (for example, bs for a loan is 0).

For example, if you borrow 1,000 rubles at an annual rate of 1% and are going to pay 100 rubles a year, then the number of payments is calculated as follows:

NPER(1%; -100; 1000)

As a result, we get the answer: 11.

INDIVIDUAL TASK. You borrow R rubles. at an annual rate of i% and are going to pay for A rub. in year. How many years will these payments take? Find 2 ways

1st way - use functions

PS (rate; nper; plt; bs; type) or PZ (rate; term; - annual contribution)

2nd way - use the NPER function (rate; -annual deposit; loan)

TASK #6

BS(rate; nper; pmt; ps; type) or BS(rate; nper; plm; ps; type)

Returns the future value of an investment based on periodic, constant (equal amounts) payments and a constant interest rate.

rate -- the interest rate for the period.

nper is the total number of payment periods.

pmt is the payment made in each period; this value cannot change during the entire payment period. Usually, the PMT consists of the principal payment and interest payment, but does not include other taxes and fees. If the argument is omitted, the value of the ps argument must be specified.

ps is the present value or the total amount, which is currently equal to a number of future payments. If the argument n is omitted, then it is assumed to be 0. In this case, the value of the argument plt must be specified.

type is a number 0 or 1 indicating when the payout should be made. If the type argument is omitted, it is set to 0.

Note. The annual interest rate is divided by 12, since compound interest is calculated monthly.

Let's give an example of using the KB function. Let's say you want to set aside money for a special project that will be done in a year. Suppose you are going to invest 1000 rubles at an annual rate of 6%. You are going to invest 100 rubles at the beginning of each month throughout the year. How much money will be in the account at the end of 12 months?

Using the formula

BZ(6%/12; 12; -100; -1000; 1)

we get the answer 2,301.4 rubles.

INDIVIDUAL TASK. You are going to invest on A. e. for H years at an annual rate of I%. How much money will be in the account after n years?

use function

BS(rate; nper; plt; ps; type)) or BS(rate; term; - payout)

TASK №7

Compile a statement of the sale of goods by N stores from month A to month B. Find the place of the store by total revenue (RANK() function), average store revenue per month (AVERAGE (array of revenue by months)), percentage of store profit in total revenue (total store revenue/total revenue of all stores). Build 2 diagrams (1 - percentage of profit to total revenue, 2 - sales volumes).

The cost of goods for each store is different.

Revenue volumes for the first store are taken from the first digit, for the second store - from the second digit (the first digit moved to the end of the list), for the third store - from the third digit (the first and second digits are at the end of the list), etc.

		Cost of goods	Sales volumes (thousand units)
	September	44,45,46,47,201,202	24,25,26,27,36,38

September
		39,38,40,41,49, 36	25,27,28,22,23,29

	September


		201,205,305,205,11,14,22	70,71,72,73,74,99,85
September
September		420,430,401,400, 300

	September

Hosted on Allbest.ru

The financial analysis:

Using the table data, you can perform basic financial analysis budget institution(organizations). To do this, transfer the data from the financial statements to the calculation tables, then click the button ...
This page contains a free automatic financial analysis table, similar to an Excel (xls) spreadsheet. Simply enter the original balance sheet and income statement data into…
On this page you can conduct an express analysis of the financial economic activity and the state of the enterprise. Just enter the initial data for three reporting dates and an automatic calculation will take place (if not, ...
Financial stability is determined, firstly, with the help of coefficients, and secondly, with the help of a three-component indicator of financial stability.
Structure of liabilities - ratio of shares various kinds liabilities and equity in the total liabilities of the enterprise. Liabilities are sources of own and ...
When preparing diploma, term papers, master's and other academic work in financial analysis, it is very often necessary to conduct an analysis, having data for three years only at the end ...
This entry contains a unique free generator financial financial statements for three years. If you need a balance sheet and income statement for your job and…
On this page, you can analyze financial results commercial enterprise.. It allows you to analyze the dynamics and…
On the site site you can perform two tasks: Firstly, you can conduct a financial analysis online And secondly, below on this page all types of analysis are described that ...
This record contains a simple online spreadsheet that allows you to evaluate the creditworthiness of an organization. The assessment of creditworthiness is carried out according to the methods of Nevolina E.V. and OAO Sberbank of Russia. Sincerely, Alexander…

Vertical Analysis- this is a method for diagnosing the financial condition of an organization and assessing the dynamics of changes in the structure. The purpose and essence of the vertical analysis of financial statements is the analysis of changes in the structure financial indicators for the period under review. This analysis used to evaluate the structure of the balance sheet, income statement and statement of movements Money. In the article, we will consider how the vertical analysis of the balance sheet and the income statement is carried out using the example of the enterprise PJSC "KAMAZ".

The directions for conducting a vertical analysis of the balance sheet of an organization are as follows:

Assessment of the structural change in the company's assets / liabilities.
Calculation of the change in the share of borrowed capital of the organization.
Determination of the composition of working and non-working capital.
Comparison between the capital structure of different companies or companies in different industries.

Vertical analysis can be applied not only to the balance sheet, but also to the income statement ( form number 2) when determining the structure of income and expenses. For example, to diagnose the structure of revenue or profit from sales, etc. Vertical analysis can similarly be used for the statement of changes in equity ( form number 3) and cash flow statement ( form №4), but generally vertical analysis is limited to the balance sheet and income statement.

Comparison of vertical balance sheet analysis with other methods of financial analysis

Vertical analysis is one of the tools (methods) for analyzing the financial statements of an organization for diagnosing a negative trend in indicators, a decrease in financial stability due to an increase in the share of borrowed capital, etc. In addition to it, other methods are also used ⇓.

Name of financial statement analysis	Directions of application	Advantages	Flaws
Vertical Analysis (analogue: structural analysis)	It is used to determine the capital structure of the organization, financial indicators and changes in the structure over time	Allows you to track structural changes in the assets and liabilities of the company	Used for diagnosis Does not assess the financial condition of the enterprise
Horizontal Analysis (analogue: trend analysis)	Used to assess the direction and forecast the dynamics of changes in financial indicators	Allows you to evaluate the dynamics of changes in financial indicators over the years	Serves more for diagnosis than acceptance management decisions and assessment of the financial condition
Ratio analysis	Assessment of financial indicators characterizing: profitability, financial stability, turnover and liquidity of the organization	Gives an assessment of the effectiveness of certain indicators of the economic activity of the enterprise. The introduced standards make it possible to identify problematic indicators and make management decisions Used to assess the financial performance of enterprises in the same industry	It is difficult to determine the probability of bankruptcy risk and the level of financial reliability
Scoring (rating) assessment	Comprehensive assessment of the company's financial condition, solvency and financial reliability. Application of models for assessing the probability of bankruptcy, rating models, scoring and expert methods	A complex criterion based on the model for assessing the financial condition allows you to determine the probability of bankruptcy risk	The final estimate may be distorted due to the peak overestimation of one of the model indicators

An example of a vertical balance sheet analysis for PJSC KAMAZ in Excel

Consider an example of a vertical analysis of the balance sheet for the company PJSC "KAMAZ". To do this, you need to download the balance sheet from the official website of the company or follow the link →.

Let's carry out a vertical analysis of non-current assets, for this it is necessary to assess what part / share is occupied by its components.

Share of intangible assets (F9) =C9/$C$18

Share of research and development results(F10) = C10/$C$18

Share of fixed assets(F13) = C13/$C$18

The share of profitable investments in material assets(F14) = C14/$C$18

Share of financial investments(F15) = C15/$C$18

Share of deferred tax assets(F16) = C16/$C$18

Share of other non-current assets(F17) = C17/$C$18

You can see that the sum of all parts will give 100%. The figure below shows an example of a vertical analysis of non-current assets in the balance sheet ⇓.

At the next stage, it is possible to single out the maximum and minimum shares in the formation of non-current assets for 2014.

The maximum share in the formation of non-current assets (66.3%) belongs to fixed assets, the minimum share belongs to the results of research and development (0.4%). To reflect the dynamics of structure change, it is necessary to construct an area diagram ⇓.

It can be noted that by 2016 there has been a decrease in the share of fixed assets (FA) from 66.3% to 36.1% and an increase in the share of financial investments from 7% to 43%. A decrease in the share of fixed assets may indicate a decrease in the company's investment in production and the development of long-term potential. Fixed assets include: buildings, structures, equipment, vehicles, tool and inventory.

Vertical analysis of income statement

The versatility of the method allows it to be used to analyze the income statement (form No. 2) and determine how the share of expenses and incomes changed during the formation of revenue. For example, let's take the previous financial statements of KAMAZ PJSC and reflect the change in revenue indicators for 2015 and 2016. You can see that the revenue is 100%.

Income from subsidies received (E8) =C8/C7

Cost of sales (E9) =C9/$C$7

Gross Profit (E10) =C10/$C$7

All other lines of the income statement are calculated in the same way. The figure below shows an example of applying the ⇓ method.

As can be seen from the figure, the cost of production (costs) is higher than revenue, but positive revenue is maintained at the expense of income in the form of subsidies.

From 2015 to 2016, there was an increase in the share of gross profit from 4.6% to 9.%, a decrease in sales profit from 6.2% to 4.4%, a decrease in profit before tax from 4.7% to 1.3%.

conclusions

Vertical analysis is used as a method of analyzing financial indicators from the balance sheet, income statement, and can also be used for the cash flow statement and for the capital flow statement. The method is used to assess the dynamics of the structure of assets and liabilities of the balance sheet. To conduct a comprehensive financial analysis, it must be used in conjunction with horizontal and coefficient analysis, as well as assessment by bankruptcy models.

Do financial analysis of the state of the enterprise in Excel QFinAnalysis program will help you in 5 minutes.

From this article you will learn:

About the tasks that you can solve with the help of the program;
About the possibilities financial analysis in QFinAnalysis;
How to work with the program;

You can also download an Excel file with an example of calculating the financial condition using QFinAnalysis.

With QFinAnalysis you can decide the following tasks of financial analysis of the enterprise:

identify deviations in business activities that may carry a potential risk and require careful assessment,
form financial plans company development,
improve the efficiency of resource use,
develop a policy of relations with creditors and debtors,
make an assessment of solvency using regulated methods for assessing the probability of bankruptcy,
calculate the effectiveness of your own business,
manage the capital structure.

The QFinAnalysis program will help you to do:

Diagnostics of the probability of bankruptcy - 23 modern methods for calculating the probability of bankruptcy, including methods using the apparatus of artificial intelligence;
Analysis of financial indicators - approx. 20 financial ratios(liquidity, business activity, financial stability, profitability, etc.);
Evaluation of the company's solvency - regulated analysis of solvency according to the methodology of the Government of the Russian Federation;
Calculation of net assets of the enterprise;

Analysis of the liquidity of the company's balance sheet;

Horizontal vertical analysis of the company's balance sheet;

Forecasting the bankruptcy of an enterprise according to the most popular Western methods (Altman, Beaver, Taffler, etc.) and adapted domestic methods (IGEA, Kazan School, Zaitseva, Savitskaya, etc.).

How to work with the program?

Having opened QFinAnalysis, we find ourselves in a sheet with a menu from which we can go to any sheet with the program. by clicking on the links of the relevant sections:

By going to any of the sections, you will see comments that will help you interpret the values of ratios and indicators of financial condition:

To make financial analysis of the state of the enterprise using QFinAnalysis, you need to enter the company's balance sheet indicators into the program:

The balance sheet of the enterprise in the old form is entered in the "Balance" sheet:

Balance in new form enter in the sheet "New balance":

We go through the pages and get:

1. Analysis of the dynamics of the balance sheet and share by items in the sheet "analytical balance sheet"

2. Enterprise analysis coefficients for:

Financial stability assessments;
Liquidity assessments;
Profitability estimates;
business activity assessments;

– list "coefficients":

3. Liquidity assessment - "liquid analysis" sheet:

4. Models of multiple discriminant analysis (MDA-models) for assessing the risk of bankruptcy of an enterprise:

Two-factor Altman model
Two-factor model of Fedotova
Five-factor Altman model
Modified Altman model for Russia
Four factor Taffler model
Four factor Fox model
Springate's four-factor model
Four factor model of IGEA
Saifulin-Kadykov model
The Parenoi-Dolgolaev model
Model of the Republic of Belarus
Model Savitskaya

5. Logistic models (Logit-models) for assessing the risk of bankruptcy of an enterprise show the probability of bankruptcy in percent

Altman-Sabato Model (2007)
Lina-Piesse Model (2004)
JuHa-Tehong Model (2000)
JuHa-Tehong Model (2000)
Gruzchinsky Model (2003)
Gruzchinsky Model (2003)

6. Expert model "Zaitseva", in which each indicator can be weighted manually - sheet "expert model"

Model of the Kazan State Technological University (KSTU) for industrial enterprises (which allows you to determine the creditworthiness class);
Beaver Model;

8. Model of the government of the Russian Federation (assessment of the solvency and balance sheet structure of the enterprise):

9. Calculation of the net assets of the enterprise - sheet "NA":

Novo Forecast Lite- automatic forecast calculation V excel.
4analytics- ABC-XYZ analysis and analysis of emissions in Excel.
Qlik Sense Desktop and QlikViewPersonal Edition - BI systems for data analysis and visualization.

Test the features of paid solutions:

Novo Forecast PRO- forecasting in Excel for large data arrays.

An example of a financial analysis of an enterprise in Excel

Statistical Data Analysis in Excel

Send your good work in the knowledge base is simple. Use the form below

ASSIGNMENT OPTIONS

TASK #2

NPV (rate; value1; value2; ...) or refinery (rate; value1; value2; ...)

Returns the net present value of an investment using a discount rate and future payouts (negative values) and future receipts (positive values).

rate -- the discount rate for one period.

Value1, value2,... -- 1 to 29 arguments representing expenses and income.

Value1, value2, ... must be evenly distributed in time, payments must be made at the end of each period.

The NPV uses the order of the arguments value1, value2, ... to determine the order of receipts and payments. Make sure your payments and receipts are entered in the correct order.

Example 1

In the example, the initial cost of 10,000 rubles. were included as one of the values ​​because the payment was made at the end of the first period.

Example 2

In this example, the initial cost of 40,000 rubles. were not included as one of the values ​​because the payment was made at the beginning of the first period.

Consider the following problem. They ask you to lend 10,000 rubles and promise to return 2,000 rubles in a year, and 4,000 rubles in two years. Three years later - 7,000 rubles. At what annual interest rate is this deal profitable?

In the given calculation, the formula is entered into the cell in cell B7

= Refinery (V6; V2:V4)

Initially, an arbitrary percentage is entered in cell B6, for example 3%. After that, select the Service command, Parameter selection and fill in the Parameter selection dialog box that opens.

In the field Set in the cell, we give a link to cell B7, in which the net current amount of the deposit is calculated using the formula

= Refinery (V6; V2:V4)

In our case, the annual discount rate is 11.79%.

Conclusion: if banks offer a large annual interest rate, then the proposed transaction is not profitable.

TASK #3

PS(rate; nper; plt; bs; type) or PZ(rate; nper; pt; bs; type)

Returns the present (to date) value of an investment. Present (present) value is the total amount that currently equal to a number of future payments. For example, when you borrow money, the loan amount is the present (present) value to the lender.

For example, if a car loan is received at 10 percent per annum and monthly payments are made, then the interest rate for the month will be 10% / 12 or 0.83%. As the value of the rate argument, enter 10%/12 or 0.83% or 0.0083 into the formula.

For example, if a car loan is received for 4 years and monthly payments are made, then the loan has 4*12 (or 48) periods. As the value of the argument nper in the formula, you need to enter the number 48

Example

The result is negative because it represents the money to be paid, the outgoing cash flow. If the annuity were required to pay 60,000, this investment would not be profitable, since the present value (59,777.15) of the annuity is less than this amount.

· Note. To get the monthly interest rate, divide the annual rate by 12. To find out the number of payments, multiply the number of years of the loan by 12.

An annuity is a series of regular cash payments made over a long period. For example, a car loan or mortgage are annuities.

Consider the following problem. Let's say that you are asked to borrow 10,000 rubles and promise to return 2,000 rubles within 6 years. Will this deal be profitable at an annual rate of 7%?

In the calculation, the formula is entered in cell B5

\u003d PZ (B4; B2; -B3)

INDIVIDUAL TASK. You are asked to lend R rub. and promise to return for A rub. annually for N years. At what interest rate does this deal make sense?

To solve the problem, use the function

(PS (rate; kper; plt; bs; type) or PZ (rate; term; -annual payments)). First, an arbitrary rate is taken in the function, then it is refined by the parameter selection method.

TASK #4

HPMT (rate; period; nper; ps; bs; type)

Returns the amount of interest payments on an investment over a given period based on a constant periodic payment amount and a constant interest rate.

rate -- the interest rate for the period.

period is the period for which you want to find interest payments; must be between 1 and nper.

nper is the total number of annuity payment periods.

Example

OSPLT(rate; period; nper; ps; bs; type)

Returns the amount of the principal payment for an investment over a given period based on constant periodic payments and a constant interest rate.

rate -- the interest rate for the period.

period -- sets the period, the value must be in the range from 1 to "nper".

nper is the total number of annuity payment periods.

ps -- the present value or the total amount, which is currently equivalent to a number of future payments.

bs - the required value of the future value, or the balance of funds after the last payment. If the argument bs is omitted, then it is assumed to be 0 (zero), i.e. for a loan, for example, the value of bs is 0.

type is a number 0 or 1 indicating when the payout should be made.

Notes

Example

Let's consider an example of calculating principal payments, interest payments, total annual payments and the balance of the debt on the example of a loan of 1,000,000 rubles for a period of 5 years at an annual rate of 2%.

excel spreadsheet formula

the annual fee is calculated in cell B3 using the formula

=PLAT(interest; term; - loan amount)

For the first year, the interest payment in cell B7 is calculated using the formula

=D6*$B$1

Main board $B$3-B7

The remaining debt in cell D7 is calculated by the formula

=D6-C7

In the remaining years, these fees are determined by dragging the fill marker of the selected range B7:D7 down the columns.

Note that the principal fee and the interest fee could be directly found using the functions FOSPLAT and PPROU, respectively.

INDIVIDUAL TASK. Calculate the annual principal payments, the interest fee, the total annual payment and the balance of the debt on the example of a loan R rub. at an annual rate of i% for a period of N years.

Use functions

Similar Documents

The financial analysis:

Comparison of vertical balance sheet analysis with other methods of financial analysis

An example of a vertical balance sheet analysis for PJSC KAMAZ in Excel

Vertical analysis of income statement

How to work with the program?

Perhaps you will be interested

In the example, the initial cost of 10,000 rubles. were included as one of the values because the payment was made at the end of the first period.

In this example, the initial cost of 40,000 rubles. were not included as one of the values because the payment was made at the beginning of the first period.