All processes occurring in business are interconnected. There is both a direct and indirect connection between them. Various economic parameters change under the influence of various factors. Factor analysis (FA) allows you to identify these indicators, analyze them, and study the degree of influence.
The concept of factor analysis
Factor analysis is a multidimensional technique that allows you to study the relationships between the parameters of variables. In the process, the structure of covariance or correlation matrices is studied. Factor analysis is used in a variety of sciences: psychometrics, psychology, economics. The basics of this method were developed by psychologist F. Galton.
Objectives of the
To obtain reliable results, a person needs to compare indicators on several scales. In the process, the correlation of the obtained values, their similarities and differences is determined. Let's consider the basic tasks of factor analysis:
- Detection of existing values.
- Selection of parameters for a complete analysis of values.
- Classification of indicators for system work.
- Detection of relationships between resultant and factor values.
- Determining the degree of influence of each factor.
- Analysis of the role of each value.
- Application of the factor model.
Every parameter that affects the final value must be examined.
Factor analysis techniques
FA methods can be used both in combination and separately.
Deterministic Analysis
Deterministic analysis is used most often. This is due to the fact that it is quite simple. Allows you to identify the logic of the impact of the company’s main factors and analyze their impact in quantitative terms. As a result of the DA, you can understand what factors should be changed to improve the company's performance. Advantages of the method: versatility, ease of use.
Stochastic analysis
Stochastic analysis allows you to analyze existing indirect relationships. That is, there is a study of indirect factors. The method is used if it is impossible to find direct connections. Stochastic analysis is considered complementary. It is only used in certain cases.
What is meant by indirect connections? With a direct connection, when the argument changes, the value of the factor will also change. An indirect connection involves a change in the argument followed by a change in several indicators at once. The method is considered auxiliary. This is due to the fact that experts recommend studying direct connections first. They allow you to create a more objective picture.
Stages and features of factor analysis
Analysis for each factor gives objective results. However, it is used extremely rarely. This is due to the fact that complex calculations are performed in the process. To carry them out you will need special software.
Let's consider the stages of FA:
- Establishing the purpose of the calculations.
- Selection of values that directly or indirectly affect the final result.
- Classification of factors for complex research.
- Detecting the relationship between the selected parameters and the final indicator.
- Modeling of mutual relationships between the result and the factors influencing it.
- Determining the degree of impact of the values and assessing the role of each parameter.
- Use of the generated factor table in the activities of the enterprise.
FOR YOUR INFORMATION! Factor analysis involves very complex calculations. Therefore, it is better to entrust it to a professional.
IMPORTANT! When carrying out calculations, it is extremely important to correctly select factors that influence the results of the enterprise. The selection of factors depends on the specific area.
Factor analysis of profitability
A profitability analysis is carried out to analyze the rationality of resource allocation. As a result, it is possible to determine which factors most influence the final result. As a result, only those factors that best influence efficiency can be retained. Based on the data obtained, you can change the company's pricing policy. The following factors may influence the cost of production:
- fixed costs;
- variable costs;
- profit.
Reducing costs provokes an increase in profits. In this case, the cost does not change. We can conclude that profitability is affected by existing costs, as well as the volume of products sold. Factor analysis allows us to determine the degree of influence of these parameters. When does it make sense to do it? The main reason for this is to reduce or increase profitability.
Factor analysis is carried out using the following formula:
Rв= ((W-SB -KRB-URB)/W) - (WB-SB-KRB-URB)/WB, Where:
VT – revenue for the current period;
SB – cost price for the current period;
KRB – commercial expenses for the current period;
URB – management expenses for the previous period;
VB – revenue for the previous period;
KRB – commercial expenses for the previous period.
Other formulas
Let's consider the formula for calculating the degree of impact of cost on profitability:
Rс= ((W-SBot -KRB-URB)/W) - (W-SB-KRB-URB)/W,
CBO is the cost of production for the current period.
Formula for calculating the impact of management expenses:
RUR= ((W-SB -KRB-URot)/W) - (W-SB-KRB-URB)/W,
URot is management expenses.
The formula for calculating the impact of business costs is:
Rк= ((W-SB -KRo-URB)/W) - (W-SB-KRB-URB)/W,
CR is commercial expenses for the previous time.
The total impact of all factors is calculated using the following formula:
Rob=Rv+Rс+Rur+Rk.
IMPORTANT! When making calculations, it makes sense to calculate the influence of each factor separately. Overall PA results are of little value.
Example
Let's consider the organization's indicators for two months (for two periods, in rubles). In July, the organization's income amounted to 10 thousand, production costs - 5 thousand, administrative expenses - 2 thousand, commercial expenses - 1 thousand. In August, the company's income amounted to 12 thousand, production costs - 5.5 thousand, administrative expenses - 1.5 thousand, commercial expenses - 1 thousand. The following calculations are carried out:
R=((12 thousand-5.5 thousand-1 thousand-2 thousand)/12 thousand)-((10 thousand-5.5 thousand-1 thousand-2 thousand)/10 thousand)=0.29-0, 15=0.14
From these calculations we can conclude that the organization’s profit increased by 14%.
Factor analysis of profit
P = RR + RF + RVN, where:
P – profit or loss;
РР – profit from sales;
RF – results of financial activities;
RVN is the balance of income and expenses from non-operating activities.
Then you need to determine the result from the sale of goods:
PP = N – S1 – S2, where:
N – revenue from the sale of goods at selling prices;
S1 – cost of products sold;
S2 – commercial and administrative expenses.
The key factor in calculating profit is the sales turnover of the company.
FOR YOUR INFORMATION! Factor analysis is extremely difficult to perform manually. You can use special programs for it. The simplest program for calculations and automatic analysis is Microsoft Excel. It has tools for analysis.
All phenomena and processes of economic activity of enterprises are interconnected and interdependent. Some of them are directly related to each other, others indirectly. Hence, an important methodological issue in economic analysis is the study and measurement of the influence of factors on the value of the economic indicators under study.
Factor analysis in the educational literature is interpreted as a section of multivariate statistical analysis that combines methods for assessing the dimension of many observed variables by studying the structure of covariance or correlation matrices.
Factor analysis begins its history in psychometrics and is currently widely used not only in psychology, but also in neurophysiology, sociology, political science, economics, statistics and other sciences. The basic ideas of factor analysis were laid down by the English psychologist and anthropologist F. Galton. The development and implementation of factor analysis in psychology was carried out by such scientists as: C. Spearman, L. Thurstone and R. Cattell. Mathematical factor analysis was developed Hotelling, Harman, Kaiser, Thurstone, Tucker and other scientists.
This type of analysis allows the researcher to solve two main problems: to describe the subject of measurement compactly and at the same time comprehensively. Using factor analysis, it is possible to identify factors responsible for the presence of linear statistical relationships of correlations between observed variables.
Goals of factor analysis
For example, when analyzing assessments obtained on several scales, a researcher notes that they are similar to each other and have a high correlation coefficient, in which case he can assume that there is some latent variable, which can be used to explain the observed similarity of the obtained estimates. Such a latent variable is called a factor that influences numerous indicators of other variables, which leads to the opportunity and need to mark it as the most general, higher order.
Thus, we can distinguish two goals of factor analysis:
- determination of relationships between variables, their classification, i.e. “objective R-classification”;
- reducing the number of variables.
To identify the most significant factors and, as a consequence, the factor structure, it is most justified to use principal component analysis. The essence of this method is to replace correlated components with uncorrelated factors. Another important characteristic of the method is the ability to limit oneself to the most informative principal components and exclude the rest from the analysis, which simplifies the interpretation of the results. The advantage of this method is also that it is the only mathematically based method of factor analysis.
Factor analysis- methodology for a comprehensive and systematic study and measurement of the impact of factors on the value of the effective indicator.
Types of factor analysis
The following types of factor analysis exist:
1) Deterministic (functional) - the effective indicator is presented in the form of a product, quotient or algebraic sum of factors.
2) Stochastic (correlation) - the relationship between effective and factor indicators is incomplete or probabilistic.
3) Direct (deductive) - from the general to the specific.
4) Reverse (inductive) - from the particular to the general.
5) Single-stage and multi-stage.
6) Static and dynamic.
7) Retrospective and prospective.
Factor analysis can also be exploration- it is carried out when studying the latent factor structure without assumptions about the number of factors and their loadings and confirmation, designed to test hypotheses about the number of factors and their loadings. The practical implementation of factor analysis begins with checking its conditions.
Mandatory conditions for factor analysis:
- All signs must be quantitative;
- The number of features must be twice the number of variables;
- The sample must be homogeneous;
- The original variables must be distributed symmetrically;
- Factor analysis is carried out on correlated variables.
During the analysis, variables that are highly correlated with each other are combined into one factor, as a result, the variance is redistributed between the components and the most simple and clear structure of factors is obtained. After combining, the correlation of components within each factor with each other will be higher than their correlation with components from other factors. This procedure also makes it possible to isolate latent variables, which is especially important when analyzing social ideas and values.
Stages of factor analysis
As a rule, factor analysis is carried out in several stages.
Stages of factor analysis:
Stage 1. Selection of factors.
Stage 2. Classification and systematization of factors.
Stage 3. Modeling the relationships between performance and factor indicators.
Stage 4. Calculation of the influence of factors and assessment of the role of each of them in changing the value of the performance indicator.
Stage 5. Practical use of the factor model (calculation of reserves for growth of the effective indicator).
Based on the nature of the relationship between the indicators, there are deterministic methods And stochastic factor analysis
Deterministic factor analysis is a technique for studying the influence of factors whose connection with the effective indicator is functional in nature, that is, when the effective indicator of the factor model is presented in the form of a product, quotient or algebraic sum of factors.
Methods of deterministic factor analysis: Chain substitution method; Absolute difference method; Relative difference method; Integral method; Logarithm method.
This type of factor analysis is the most common because, being quite simple to use (compared to stochastic analysis), it allows you to understand the logic of the action of the main factors of enterprise development, quantify their influence, understand which factors and in what proportion it is possible and advisable to change for increasing production efficiency.
Stochastic analysis is a methodology for studying factors whose connection with a performance indicator, unlike a functional one, is incomplete and probabilistic (correlation). If with a functional (complete) dependence with a change in the argument there is always a corresponding change in the function, then with a correlation connection a change in the argument can give several values of the increase in the function depending on the combination of other factors that determine this indicator.
Methods of stochastic factor analysis: Pair correlation method; Multiple correlation analysis; Matrix models; Mathematical programming; Operations Research Method; Game theory.
It is also necessary to distinguish between static and dynamic factor analysis. The first type is used when studying the influence of factors on performance indicators on the corresponding date. Another type is a technique for studying cause-and-effect relationships in dynamics.
And finally, factor analysis can be retrospective, which studies the reasons for the increase in performance indicators over past periods, and prospective, which examines the behavior of factors and performance indicators in the future.
Careful planning is essential to the success of any business. Its basis is factor analysis of various indicators, which allows us to substantiate plans and assess the quality of accounting and control systems. Based on the results, tactics and strategy of the enterprise are developed. Most often, factor analysis is carried out in relation to profit in order to determine how this indicator is affected by the quality and volume of products and labor productivity. For trading enterprises, sales analysis is most important.
The task of studying financial results is to monitor the implementation of plans and determine what objective and subjective factors influence the level of income. The calculation process uses accounting data and information from the business plan. Based on the results, reserves are determined to increase net income.
Calculations are carried out according to:
- gross, taxable,
- basic goods (services, works)
- income from other sales
- non-operating income
Research objectives:
- determine deviations for each characteristic
- explore the change and structure of each indicator
- evaluate the performance of an enterprise for a certain period
The structure and composition of income, dynamics in comparison with previous time periods, the impact of the chosen accounting policy on each type of profit and the amount of deductions for dividends and taxes are analyzed.
It is important to take into account all the factors affecting the result of business activity:
- income from transactions with currencies, deposits, bonds, shares
- losses from hopeless losses, penalties, fines, penalties
- rental income, received penalties, fines, penalties
- losses from negative profits of previous periods and natural disasters
- costs of paying taxes and contributions to extra-budgetary funds
The main indicator of successful work is high profitability. A study of the dependence of this indicator for the entire enterprise and for each area of activity is required. The profitability of sales, return on invested capital, investments and costs are assessed. Calculations are carried out for each type of profit (gross, sales, net).
Factor analysis consists of several stages:
- selection of factors
- their systematization and classification
- modeling relationships between a factor and a result
- determination of each factor and calculation of its influence on the result of economic activity
- developing recommendations to use the results in practice
Key elements: changes in profitability, income and expenses.
For factorial research, you can use other indicators, for example profitability:
- investments (ratio of the amount in the “bottom line” to the amount of own funds)
- equity
- assets (the ratio of the amount in the “bottom line” to the total volume of the first section of the balance sheet)
- (ratio of the amount in the “bottom line” to the volume of working capital)
- sales (ratio of the amount in the “bottom line” to revenue)
The difference between the amounts for the base and current year is calculated, and the factors that influenced the changes are identified.
Research of factors influencing sales profitability
Sales profitability depends on:
- volume of goods sold
- structure of goods sold
- production costs
- average price level
- business expenses
During the research process, each factor and its impact are assessed.
General indicator of changes in income from sales of goods:
ΔР = Р1 – Р0, where
- P1 – profit of the current period
- Р0 – profit of the previous period
When calculating the impact of the volume of goods sold on profitability, the increase in volume (in percentage) is first calculated:
ΔQ = Q1 / Q0 * 100 – 100, where
- Q1 – revenue of the current period in base prices
- Q0 – revenue of the previous period
ΔР1 = Р0 * ΔQ / 100, where
- ΔР1 – change in the volume of goods sold
Comparison of data from the base and reporting time periods can create problems, especially if the products are heterogeneous. The problem is solved by using the prices of the previous period as a basis.
The impact on cost is calculated using the formula:
ΔР2 = С0 – С1, where
- C0 – cost of goods sold in the reporting period in prices of the previous period
- C1 – cost of goods sold in the reporting period at current prices
This formula is also used to calculate the impact of selling and administrative expenses.
Changes in sales value are calculated using the formula:
ΔР3 = Q1 – Q2, where
- Q1 – revenue of the current period in current prices
- Q2 – revenue of the current period at base prices
To calculate the impact of product structure on profit, the formula is used:
ΔР4 = ΔР – ΔР1 – ΔР2 – ΔР3
To determine the impact of all factors, the formula is used:
ΔР = Р1 – Р0 = ΔР1 + ΔР2 + ΔР3 + ΔР4
Based on the results, reserves are determined that allow. This may be an increase in the volume of products sold, a reduction in the total cost or its individual components, an improvement in the structure (quality, range) of manufactured (sold) products.
Calculation example
To make calculations, you need to take data from the balance sheet for the current and base year.
An example of calculating indicators of factor analysis of sales profit if:
- revenue 60,000 and 55,000 (at current prices) or 45,833 (at base year prices)
- production cost 40,000 and 35,000
- commercial expenses 3,000 and 2,000
- administrative expenses 5,000 and 4,000
- total cost 48,000 and 41,000
- selling price change index 1.2
- profit 12,000 and 14,000
(the first indicator refers to the base period, the second - to the reporting period).
Profit change:
ΔР = Р1 – Р0 = 12,000 – 14,000 = -2,000
Revenue of the current period in prices of the past: 55,000 / 1.2 = 45,833.
Increase/decrease in sales volume:
ΔQ = Q1 / Q0 * 100 = 45,833 / 60,000 * 100 – 100 = -24%
Impact of volume reduction:
ΔР1 = Р0 * ΔQ / 100 = 12,000 * (-24) / 100 = -1,480
Impact of incomplete (production) cost:
ΔР2 = С0 – С1 = 40,000 – 35,000 * 1.2 = -2,000
Impact of business expenses:
ΔР2 = С0 – С1 = 3,000 – 2,000 * 1.2 = 600
Impact of management expenses:
ΔР2 = С0 – С1 = 5,000 – 4,000 * 1.2 = 200
Impact of changes in selling price:
ΔР3 = Q1 – Q2 = 55,000 – 45,833 = 9,167
Impact of structure:
ΔР4 = ΔР – ΔР1 – ΔР2 – ΔР3 = -2,000 – 1,480 – 2,000 + 600 + 200 + 9,167 = 4,467
Influence of all factors:
ΔР = ΔР1 + ΔР2 + ΔР3 + ΔР4 = -1,480 – 2,000 + 600 + 200 + 9,167 + 3,467 = 9,114
The results show that profit in the reporting period decreased due to a decrease in sales volumes and an increase in production costs. The change in the structure and cost of products during sales had a positive impact.
Research on Factors Affecting Gross Profit
When calculating gross profit, the following costs are not taken into account:
- commercial
- managerial
- non-operating
- operating rooms
- tax
- emergency
- other
In the example discussed in the previous section, 3 will change:
- the cost will be 2,000
- influence of structure 3 667
- influence of all factors 8 314
The amounts will be smaller, since commercial and administrative costs that change the total cost are not taken into account.
Research of factors influencing the amount of net profit
All factors influencing this indicator are divided into internal and external. The first group includes accounting methods, methods for forming a cost structure, the second group includes the influence of climate, changes in tariffs and prices for raw materials, changes in contracts, force majeure circumstances. Net profit is calculated by subtracting production costs, administrative and commercial costs, other expenses, and taxes from revenue.
The formula used for calculations is:
∆Rch = ∆P + ∆C + ∆K + ∆U + ∆P + ∆NP, where
- ∆Р – change in revenue
- ∆С – change in cost
- ∆К – change in commercial costs
- ∆У – change in management costs
- ∆П – change in other income/expenses
- ∆NP – change in size after adjustment
When calculating changes in individual factors, the formula is used:
ΔИ2 = И0 – И1, where
- И0 – costs of the current period in prices of the past
- I1 – costs of the reporting period in current prices
A similar study is carried out on income from additional activities, for example, participation in other enterprises, deposits, deposits in bonds. This allows you to determine the factors influencing profitability and the advisability of investing. For example, if income from interest on deposits has decreased, you should not use this type of investment in the future.
When working with the “bottom line”, a study is also carried out on the quality and use of net profit. This indicator can be improved by reducing the gap between the balance sheet figure and the actual amount of funds. To achieve this, the method and methods of writing off costs and forming reserves are changing.
To study the use of earned funds, a formula is used to calculate the profitability of one share:
Pa = (Pch – Dpr) / Qо, where
- Pa – profitability of one share
- Pch – net profit
- Dpr – amount of dividends per preferred share
- Qо – number of outstanding ordinary shares
Net profit is used for:
- dividend payments
- formation of savings and reserves
- contributions to social and charitable funds
Factor analysis can also be performed on these indicators to compare volumes and variances across two or more periods.
Factor analysis makes it possible to more deeply and in detail assess the state of an enterprise’s finances by identifying factors that have the greatest impact on business profitability. Based on the results, you can determine exactly what measures are required.
Write your question in the form below
You remember that all phenomena and processes of the economic activity of an enterprise are interconnected and interdependent. Some of them are directly related to each other, others indirectly.
For example, the amount of profit from core activities directly depends on the volume and structure of sales, price and unit cost of production. All other factors influence this indicator indirectly.
Each phenomenon can be considered both as a cause and as a consequence.
For example, labor productivity can be considered, on the one hand, as the reason for changes in production volume, production costs, and on the other hand, as a result of changes in the degree of mechanization and automation of production, improvement in labor organization, etc.
Each performance indicator depends on numerous and varied factors. The more detailed the influence of a factor on the value of the performance indicator is studied, the more accurate the results of the analysis and assessment of the quality of the enterprise’s work. Therefore, the study and measurement of the influence of factors on the value of the studied economic indicators is an important methodological issue of economic analysis. Without a deep and comprehensive study of factors, it is impossible to draw reasonable conclusions about the results of activities, identify production reserves, and justify plans and management decisions.
The following are distinguished: types of factor analysis:
Deterministic and stochastic;
Direct and reverse;
Single-stage and multi-stage;
Retrospective (historical) and prospective (forecast).
Deterministic factor analysis is a technique for studying the influence of factors whose connection with the performance indicator is functional in nature. That is, when the effective indicator is presented in the form of a product, quotient or algebraic sum of factors.
Stochastic analysis is a technique for studying factors whose connection with an effective indicator is incomplete, probabilistic (correlation).
What is the difference between functional and correlation dependence?
With functional dependence, with a change in the argument, a certain change in the function always occurs. With a stochastic connection, a change in the argument can produce several changes in the function, depending on the combination of other factors that determine this indicator.
For example, labor productivity at the same level of capital-labor ratio may be different at different enterprises.
At direct factorial analysis, research is carried out in a deductive manner from the general to the specific.
Reverse factorial analysis carries out the study of cause-and-effect relationships using the inductive method - from particular individual factors to general ones.
Single stage Factor analysis is used to study factors of only one level (one level) of subordination without detailing them into their component parts.
For example: profitability = profit / production volume.
At multi-stage Factor analysis is used to refine factors into their component elements in order to study their behavior.
For example: profit = sales volume – costs
The detailing of the factors can be continued further, that is, the influence of factors of different levels of subordination is studied.
Static factor analysis is used to study the influence of factors on performance indicators on a specific date.
Dynamic factor analysis is a technique for studying cause-and-effect relationships in dynamics.
Retrospective factor analysis studies the reasons for changes in performance indicators over past periods.
Prospective factor analysis examines the behavior of factors and performance indicators in the future.
To conduct factor analysis, it is necessary to establish which indicators will be studied and how they are related to each other.
The selection of factors for analysis is carried out on the basis of the theoretical and practical knowledge of the analyst. In this case, they usually proceed from the principle: the larger the complex of factors studied, the more accurate the results of the analysis will be. but the factors should be considered not as a simple set of numbers, but taking into account the interaction, highlighting the main and secondary connections.
The relationship between factors and the resulting characteristic can be direct or inverse, linear or curvilinear. To select the type of connection, theoretical and practical experience, methods for comparing parallel and time series, analytical grouping of information, graphs, etc. are used.
The defining stage of factor analysis is modeling.
Modeling– this is one of the methods of scientific knowledge, with the help of which a model (conventional image) of the object of study is created. Its essence lies in the fact that the relationship between the indicator being studied and the factor indicator is conveyed in the form of a specific mathematical equation.
In deterministic factor analysis, the following are distinguished: types of factor models:
1. Additive models are used in cases where the effective indicator is an algebraic sum of several factor indicators.
For example, the cost model by element : P = MZ + ZP + SS + A + Rproch,
Where P is the total amount of expenses of the enterprise, MZ is material costs, ZP is wages, SS is social insurance contributions, A is depreciation, Rproch is other expenses.
2. Multiplicative models, in which the effective indicator is the product of several factors.
For example, determining an employee’s wages using a piece-rate form of remuneration: ZP = St x K.
Where ZP is the salary, St is the rate for 1 product, K is the number of products produced.
3. Multiple models, in which the resulting characteristic is obtained by dividing one factor indicator by another.
For example PT =VVP: Chpp,
Where PT is labor productivity, VVP is the volume of output, NPP is the number of industrial production personnel.
1. Mixed (combined) models– combination in various combinations of previous models.
To determine the magnitude of the influence of individual factors on changes in performance indicators, the following are used: Factor analysis methods:
1. chain substitution;
2. absolute differences;
3. relative differences;
5. proportional division;
6. integral;
7. logarithm
The first four methods, based on the elimination method, are most often used.
Elimination– exclusion of the influence of all factors on the value of the result, except for one - the one being studied.
This method is based on the fact that all factors change independently of each other: first one changes, and all others remain unchanged, then the second, third, etc. change. with the rest remaining unchanged, this makes it possible to determine the magnitude of the influence of each factor on the value of the indicator under study separately.
The most versatile is chain substitution method . It allows you to determine the influence of individual factors on the change in the effective indicator by gradually replacing the basic value of each factor indicator in the scope of the effective indicator with the actual one.
Calculations are carried out according to the following scheme.
Scheme of factor analysis using the chain substitution method
|
product of factors |
magnitude of factor influence |
|||||
|
Null substitution |
||||||
|
First substitution. First factor |
||||||
|
Second substitution. Second factor |
||||||
|
Third substitution. Third factor. |
||||||
|
Fourth substitution. Fourth factor |
||||||
B – basic value of the indicator, F – actual value of the indicator, P – result.
The following data on the operation of the enterprise for the month is available.
Table 6.
Data on the operation of the enterprise in January 2007.
|
index |
deviation from plan |
||
|
commercial products, thousand UAH (TP) |
|||
|
average number of workers, people. (CR) |
|||
|
average number of days of work per employee (D) |
|||
|
average duration of 1 working day, hour. (H) |
|||
|
average hourly output per worker, thousand UAH/hour, (V) |
Let us conduct a factor analysis of the implementation of the plan for the production of commercial products using the method of absolute differences.
In this case, the effective attribute is the volume of marketable products. It is influenced by factors: the number of workers, the number of days worked by one worker, the duration of one working day, average hourly output.
Therefore, the factor model will look like:
TP = CR x D x H x H.
Please note that in the factor model used in the chain substitution method, quantitative factors are indicated first, and qualitative factors second.
We will calculate the influence of factors in the table.
Table 7.
Factor analysis of changes in the volume of commercial output
|
substitution number and factor name |
factors influencing the indicator |
product of factors |
magnitude of factor influence |
|||
|
1. Number of workers |
||||||
|
2. number of days |
||||||
|
3. length of day |
||||||
|
4. production |
||||||
Absolute difference method is a simplified version of the method of chain substitutions, when in each substitution the absolute value of the factor whose influence is calculated is replaced by the deviation of its actual value from the planned one. This method is used only in multiplicative models.
Continuation of example 5.
Let us carry out a factor analysis of changes in commercial products using the method of absolute differences.
1. We measure the influence of the number of workers:
(200-250)x8x12.5=-100,000(UAH)
2. the impact of changes in the average number of days worked by one worker: 200 x (22-20) x 8 x 12.5 = 40,000 (UAH)
3. impact of changes in working hours:
200x22x(7-8)x12.5 = - 55000 (UAH)
4. impact of changes in average hourly output:
200 x22x7x(15.5 -12.5)= 92400 (UAH).
Relative difference method used to analyze multiplicative and additive-multiplicative models like
The change in the performance indicator is determined as follows:
According to this rule, to calculate the influence of the first factor, it is necessary to multiply the basic value of the effective indicator by the relative increase in the first factor, expressed as a decimal fraction.
To calculate the influence of the second factor, you need to add the change due to the first factor to the basic value of the effective indicator and then multiply the resulting amount by the relative increase in the second factor.
The influence of the third factor is determined in a similar way: to the planned value of the effective indicator we add its increase due to the first and second factors and multiply the resulting amount by the relative increase of the third factor, etc.
Let's calculate the influence of factors on changes in the volume of commercial output using the method of relative differences.
1) due to changes in the number of workers:
500,000 x (-50:250)= - 100,000 (UAH)
2) by changing the number of days
(500,000 - 100,000)x(2:20)= 40,000(UAH)
3) by changing the duration of the working day:
(500,000 – 100,000 + 40,000)x(-1:8)= - 55,000 (UAH)
4) due to changes in output:
(500,000 – 100,000 + 40,000 – 55,000)x(3:12.5) =92,400 (UAH).
Index method is based on the analysis of relative dynamics indicators, expressing the ratio of the actual level of the indicator in the reporting period to its level in the base period.
Using aggregate indices, it is possible to assess the influence of only two factors on changes in the level of a performance indicator in multiplicative and multiple models.
If we subtract the denominator from the numerator of the formula that forms the index, then absolute increases in the effective characteristic will be obtained due to the influence of each factor.
If the last three factors in our example are combined into one complex factor - the average monthly output of one worker, then we can solve this problem using the index method:
The average monthly output of one worker is planned = 20X8X12.5 = 2000 UAH.
Actual average monthly output per worker = 22X7X15.5 = 2387 UAH.
The commodity production index has the form:
477,4: 500 = 0,955
Δpq = 477.4 – 500 = - 22.6 (thousand UAH)
The actual output of commercial products compared to the planned one decreased by 0.5%, which amounted to 22.6 thousand UAH.
The impact of changes in average monthly output is determined using the physical volume index according to the formula:
Δpq (q) = 596750 – 500000 = 96750 UAH.
The impact of changes in the number of workers is determined based on the number index:
= 
Δpq (p) = 477400 - 596750 = - 119350 UAH.
Thus, due to the change in output, the enterprise's commercial output increased by UAH 96,750, and due to the change in the number of workers, it decreased by UAH 119,350.
In order to effectively manage sales, it is necessary to correctly assess the factors that influence revenue. You can find many examples of factor analysis of revenue in Excel online. However, most of them are written to show methodological aspects and do not have much practical use.
The purpose of this article is to show how to develop a revenue factor model to suit your business needs. In practice, such a model can be quite complex, and in order not to waste time performing factor analysis in Excel, we will use the Fincontrollex® Variances Analysis Tool add-in, which allows you to fully automate this process. Thanks to this approach, we can focus on analyzing data rather than developing formulas in Excel.
How to develop a revenue factor model
You are selling a product that has a price. In order to calculate revenue, you need to multiply the number (or volume) of products sold by their price:
This is the basic revenue calculation model. All other models are its derivatives and detail the factor of volume, price, or highlight the influence of other factors according to a given condition. The final form of the formula will depend on the sales business process that needs to be managed. Let's look at the most common of them.
If you sell several types of products at different prices, then you can manage your sales mix. To do this, divide the volume factor by the total sales volume of all products and the share of each product in the total volume:
In practice, sales managers often do not understand the essence of this factor, which leads to misunderstanding of the analysis results. This factor should be interpreted as a change in the structure of sales volumes towards products with higher or lower prices. For example, if the assortment factor had a positive impact, this means that the share of higher-priced products in the sales structure has increased, and the share of lower-priced products has decreased. If the assortment factor had a negative impact, then the picture will be the opposite: an increase in the share of products with lower prices and a decrease in the share of products with higher prices. The influence of this factor should not be confused with a change in price, since when calculating the assortment factor, the influence of other factors is eliminated (excluded).
If your company uses different ways to deliver its products to the market (sales channels), then in order to assess the impact of the sales channel structure, you need to add the share of each channel to the revenue formula. For example, a factor model of revenue for chains and retail outlets may look like:
Horizontal and vertical growth
In a network trading structure, there is often a need to evaluate changes in volumes due to the opening of new points or changes in sales volumes at existing points. Such an assessment can be made by identifying factors of horizontal and vertical changes in sales volumes.
Horizontal changes are changes in sales volumes due to the opening of new outlets.
Vertical changes are changes in sales volumes at existing points.
To highlight these changes, the following conditions must be added to the revenue model. If in the current month there was a sale at a retail outlet where the products were not sold before, this is a horizontal change. If the product was already sold at the point, then we are talking about a vertical change.
Introduction of new products
If a new product is introduced into the assortment, then it is advisable to evaluate the impact of this decision on total revenue. To do this, it is necessary to exclude the influence of the product from all factors and allocate revenue for this product as a separate factor.
To do this, it is necessary to add a condition to check whether the product is new in all factors of the model. A product is considered new if it was not sold in the previous period.
You can separate revenue from a new product into a separate factor using the following condition:
As a result, you will receive factors cleared of the influence of new products and a separate amount of revenue for new products. This will allow you to accurately estimate the increase in revenue due to the introduction of new products.
Removal of products from the assortment
The assessment of the effect of removing products from the range is carried out similarly to the assessment of the effect of introducing new products, with the only difference being that the influence of the withdrawn product is excluded from the factors. A product is considered discontinued if it was sold in the previous period but not in the current period.
You can separate revenue from a withdrawn product into a separate factor using the following condition:
As a result, you will be able to estimate the decrease in revenue due to the removal of products from the range.
By simultaneously assessing the introduction of new and the withdrawal of old products, you can evaluate the effectiveness of changing the assortment.
Conversion management
If you work in the retail business, you probably know that not all store visitors make a purchase. In order to estimate what percentage of visitors make a purchase, you need to calculate the conversion rate:
The conversion rate depends on the efficiency of the staff who close the sales. If you want to value it, you should add it to the factor model. The number of customers is used to calculate the conversion rate, so in order to highlight this factor in the revenue factor model, it is necessary to add an indicator that relates the price to the number of customers. For example, the size of the average check.
Managing the size of the average check
By offering related products, you can increase your overall sales. In order to evaluate the effectiveness of this process, it is necessary to calculate the size of the average check:
The size of the average check, as well as the conversion rate, depends on the efficiency of the staff who close sales. Therefore, if you want to evaluate the effectiveness of these indicators, then you should add them to the factor model.
To stimulate sales, you can offer discounts. The size of the discount may depend on various conditions: sales volumes, payment terms, etc. In order to evaluate the impact of a discount on revenue, you need to add this factor to the model:
When analyzing a discount, you should not forget about the main purpose of providing a discount - increasing sales volume. Therefore, the discount factor must be assessed together with the volume factor.
If you are a product manufacturer, then you have the opportunity to stimulate the intensity of sales of your products in retail chains with the help of retro bonuses. A retro bonus is a reward paid to distributors and dealers for promoting products. To assess the effectiveness of sales promotion using retro bonuses, the percentage of retro bonuses to revenue is usually calculated (excluding discounts). In order to assess the impact of retro bonuses on revenue, you need to add this factor to the model:
Putting it all together: a factor model of FMCG manufacturer revenue
As an example, let's look at the factor model of an FMCG manufacturer's revenue. It is common for FMCG manufacturers to sell their products through distribution channels, providing additional volume discounts. To reflect this feature, we will add to the basic revenue calculation model the formulas for managing the assortment, discounts and retro bonuses that we discussed above.
Our factor model contains five factors: total sales volume, assortment, price, discount and retro bonus. The order of calculation of factors depends on the degree of control the enterprise has over these indicators (from most to least). Therefore, we will calculate them in the following sequence:
- Overall volume
- Range
- Retro bonus
- Discount
The factor model is ready, and now you can move on to factor analysis in Excel.
Factor analysis of revenue in Excel is easy!
Performing factor analysis in Excel is quite a labor-intensive task even for an experienced user. Therefore, to significantly simplify it, we will use a special add-in for Excel. To activate the free trial version, you will need your email, which will receive a message with an activation key and a download link.
This add-in will save you from having to enter formulas for calculating each factor in an Excel workbook; it will independently create a summary report for all factors and a detailed report for products, and also, if you use Excel 2016 or Office 365, will build a chart waterfall(if you are not familiar with this diagram, be sure to read the article as this diagram is an excellent way to show the results of factor analysis).
In this article we will not dwell on all the capabilities of this add-in, because... You can watch the video review below or read it yourself, and let’s immediately move on to setting up the factor model.
As initial data, we use conditional data from the product sales report for January and February. You can download the archive with the example using this link.
To launch an add-in on the ribbon Excel go to the tab website and in the group Variances Analysis Tool click the button Execute. The add-in window will open.
Enter the model name
The next step is to enter the mathematical formula of the factor model. To do this, enter our factor model in the formula field and press the button Enter.
The add-in will automatically determine the names of all factors and fill in the first column of the factor settings table with them. All we have to do is adjust the parameters of these factors.
Now let’s set up the order in which factors are calculated. Factors are calculated in the order they appear in the table: the factor at the top of the table will be calculated first, and the factor at the bottom of the table will be calculated last. By dragging the factors in the first column, you need to adjust the calculation order that we defined in the previous section. To do this, left-click on the name of the factor in the first column and, without releasing the mouse button, drag the factor to the required line and release the button. As a result, we should have a sequence like the one in the picture below.
We still have the last parameter unconfigured: the range with product names. Let's set it up. On tape Fincontrollex® Variances Analysis Tool on the tab home in Group Model click the button Name range.
Everything is ready and now you can perform factor analysis. To do this on the tape Fincontrollex® Variances Analysis Tool on the tab home in Group Analysis click the button Execute. In a couple of seconds you will receive the result of the factor analysis, which will be created in a new Excel workbook.
Based on the results of factor analysis, we can conclude that the increase in total sales volume in February compared to January was achieved due to a decrease in base prices and a change in the sales mix towards products with a lower price. In order to understand which products have undergone major changes, you can additionally analyze the “Details” sheet in the report.
Conclusion
We looked at the basic revenue model and the basic formulas for aligning it with business needs. These formulas are provided as an example and can serve as a starting point for developing a revenue factor model to suit your goals and objectives. Using the add-in Fincontrollex® Variances Analysis Tool for factor analysis allows you to analyze models of any complexity. This allows you to focus on managing the factors that impact revenue in your business.
An article is allowed for free publication only if the content remains unchanged and there is a link to the source. Use of images outside of this article is not permitted and is a violation of copyright.