Be carried out according to several criteria at once. Evaluation of the operation by several indicators

Topic: Making decisions on several criteria indicators.

In practice, one usually has to choose managerial decision not by one criterion, but by several. Therefore, their values ​​in a comparative assessment are multidirectional, i.e. on one indicator, the alternative wins, and on others it loses.

Under these conditions, it is necessary to reduce the considered system of evaluation of indicators to one complex one, on the basis of which a decision will be made.

To build a comprehensive assessment, two problems need to be solved:

The first problem is that the criteria indicators under consideration are of unequal significance;

The second problem is characterized by the fact that the indicators are evaluated in different units of measurement, and in order to build a comprehensive assessment, it is necessary to switch to a single meter.

The first problem is solved by applying one of the four modifications of the method of expert assessments, namely the method of pairwise comparison, which allows us to give quantification significance. The essence of the pairwise comparison method is that an expert (specialist, potential investor, consumer) conducts a pairwise assessment of the criteria indicators under consideration, determining for himself their degree of importance in the form of a score. After that, having carried out the appropriate processing of the information received, the coefficient of significance is calculated for each of the criteria indicators under consideration.

The second problem is solved by using a single meter for private indicators. Most often, a scoring is used as such a meter. In this case, the assessment is carried out in two approaches:

- first approach used in the absence of statistical data on the value of the indicators under consideration;

- second approach used in the presence of statistical data (limits of change) on the value of the indicators under consideration.

When using the first approach to convert to points, proceed as follows: best value considered indicator is taken equal to 1 point, and the worst values ​​in shares of this point. This approach is simple, gives an objective assessment, but at the same time does not take into account the best achievements that lie outside the options considered.

To eliminate this shortcoming, information is needed on the limits of change of the indicator under consideration. If available, the second approach is used. In this case, a conversion scale is built to convert to points. In this case, the scoring system is selected using the provisions of the theory of statistics according to the Sturges formula:

n = 1 + 3,322 lgN, where

N is the number of statistical observations;

n is the accepted scoring system obtained using the rounding rules.

The conversion into points is carried out on the basis of the constructed conversion scale using the tabular data interpolation procedure.

Exercise:

Of the 6 options for alternative solutions, each of which is evaluated by 5 criteria indicators, it is necessary to choose the best option.

Evaluate using 2 approaches:

in the absence of statistical data on the value of the indicators under consideration;

if available.

Limits of change of indicators are established for the following number of observations (N):

for even variants N = 8;

The assessment of significance should be performed on the basis of a paired assessment according to the performer.

Table 1.

Task options

№ tasks
No. of alternatives
№ tasks
No. of alternatives
№ tasks
No. of alternatives
№ tasks
No. of alternatives
№ tasks
No. of alternatives
№ tasks
No. of alternatives

Table 2.

Initial data:

Alternative Solutions

indicators

A6

X 1

X 2

X 3

X 4

X 5

13.12.5. Integral criteria: assessment of the quality of ecosystems by several indicators

Water quality classes according to hydrobiological and microbiological indicators are determined by the "Rules for monitoring the quality of water in weirs and streams" [GOST 17.1.3.07–82], which regulate the content of monitoring programs for hydrological, hydrochemical and hydrobiological indicators, the frequency of monitoring, as well as the designation and location of sampling points
(Table 13.7). According to this document, the degree of water pollution is assessed taking into account the saprobity index according to Pantle and Bukk in the modification of Sladechek, the Goodnight-Whitley and Parele oligochaete index, the Woodiwiss biotic index and the traditional set of microbiological indicators

Integral indicator according to E.V. Balushkina was developed and used to assess the state of ecosystems in water bodies subject to mixed organic and toxic pollution. Passed extensive testing in the system of Lake Ladoga - r. The Neva is the eastern part of the Gulf of Finland (Balushkina et al., 1996). The integral indicator IP is calculated by the formula:

IP \u003d K 1 * S t + K 2 * OI + K 3 * K ch + K 4 / BI,

where S t is the saprotoxity index of V.A. Yakovlev (K 1 = 25); OI is the Goodnight and Whitley oligochaete index, equal to the ratio of the number of oligochaetes to the total number of zoobenthos in percent (K2 = 1); Kch is Balushkina's chironomid index (K3 = 8.7); 1 / BI is the reciprocal of the Woodiwiss biotic index (K 4 @ 100).

E.V. Balushkina believes that the integral indicator she obtained included all the best features of parental indices and takes into account the characteristics of benthic communities as much as possible: the presence of indicator species of saprobotoxness, the ratio of indicator groups of animals of a higher taxonomic rank, the degree of dominance of individual groups and the structure of the community as a whole.

The combined index of the state of the community according to A.I. Bakanov. When assessing the state of benthic communities in a number of rivers, lakes and reservoirs in Russia, the author used the following indicators to quantify the state of benthos: number (N), ind./m 2 ; biomass (B), g/m 2 ; number of species (S); species diversity according to Shannon (H), bit/spec.; oligochaete Parele index (OIP, %), equal to the ratio of the number of tubificid oligochaetes to the total number of benthos, average saprobity (SS), calculated as the weighted average saprobity of the first three benthic organisms dominant in abundance. To combine the values ​​of the listed indicators and replace them with a single number, the resulting indicator is proposed - the combined index of the state of the community (KISS; [Bakanov, 1997]), found by the usual method of calculating integral rank indicators:

where R i is the rank of the station according to the i-th indicator, P i is the "weight" of this indicator, k is the number of indicators.

First, all stations are ranked by each indicator, and rank 1 is assigned to the maximum values ​​of N, B, H, and S. If at several stations the values ​​of any indicator were the same, then they were characterized by one average rank. The article gives different versions of the final formula (4.22) (we emphasize that the formulas do not include the absolute values ​​of the indicators, but their ranks):

KISS = (2B + N + H + S) / 5, where the biomass is given a "weight" equal to 2, since the magnitude of the energy flow passing through the community is associated with it, which is extremely important for assessing its state;

KISS = (2SS + 1.5OIP + 1.5B + ​​N + H + S)/8, where it is believed that the average saprobity is most closely related to pollution.

The smaller the value of KISS, the better the state of the community.

Since the state of the community depends both on natural environmental factors (depth, soil, currents, etc.) and on the presence, nature and intensity of pollution, the combined pollution index (CPI; [Bakanov, 1999]) is additionally calculated, including rank values three indicators:

KIZ \u003d (SS + RIP + B) / 3. (4.23)

The ranking of indicators is carried out in the reverse order (from the minimum values ​​to the maximum)

KISS and KIZ are relative indices that rank stations on a scale in which the best condition of the community according to the selected set of indicators is characterized by the minimum values ​​of the indices, the worst – by the maximum. In addition to the values ​​characterizing the values ​​of indicators at a particular station, their average values ​​are calculated for the entire set of stations. The variation of the index values ​​at individual stations relative to the average makes it possible to judge whether things are worse or better at them compared to the general trend.

The calculation of the Spearman rank correlation coefficient between the values ​​of KISS and KIZ shows how much pollution affects the state of zoobenthos communities. If there is a significant positive correlation between the values ​​of these indices, then the state of benthic animal communities is largely determined by the presence of pollution (otherwise, it is determined by natural environmental factors).

Previous

In this article, you will learn about 5 useful properties of pivot tables that will help you quickly and in detail analyze the situation (using the example of customer base analysis). You will learn:

1. How to group data;
2. What indicators can be calculated when summarizing data;
3. How to simultaneously calculate several indicators for one parameter when summarizing data;
4. What additional features calculation when you roll up the data you can use?
5. About the possibility of sorting.

And based on this analysis, we will touch on the most powerful technique for planning sales promotions in the FMCG markets.

Let's start with a pivot table. Let's take a simple table of sales to customers by day.

Place the cursor in the upper left corner of our table, then go to the "Insert" menu and click on the "Pivot Table" button:

In the Create PivotTable dialog box, click OK:

We got a pivot table on a new sheet:

1st useful feature of a pivot table for business analysis - data grouping

So, we have shipments to customers by day, we want to understand in what range of shipments we have the maximum sales. To do this, we need to group shipments into ranges.

Drag the "Shipping amount" field to the "Line names" area of ​​the pivot table (hold down the "Shipping_amount" field with the left mouse button and drag it to the "Line names" section of the pivot table):

We have displayed all shipments in the left column of the summary. Now we set the cursor to our shipments (as in the figure):

Go to the Excel menu "Data" and click on the "Group" button

In the dialog box that appears, set the grouping step to "5000" (you can enter any) and click "OK"

We get grouped sales volumes with a given step:

To the group looked nice and perceived, press again "Group" button and manually set equal values, for the value "starting from" - "-15,000" (below the minimum value, a multiple of 5000) "to" - "45,000" (greater than the maximum group, a multiple of 5000).

We get grouped data by shipment amount:

2nd useful feature of pivot tables for business analysis -
the ability to calculate various summary parameters by fields from the source table

So, shipments have been grouped, now let's see what sales volumes fall on each range of shipments. To do this, let's sum the shipments in the summary.

Left-click the "Shipping_amount" field and drag it to the summary "Values" field:

The summary by default calculated "Quantity by field Shipment_amount", i.e. the number of records in our original table on the "Data" sheet. Because Since our table contains information on sales to customers by day, our indicator "Quantity in the field Shipment_amount" is the number of shipments to customers.

As a result, in the pivot table we see the number of shipments to customers in different shipment ranges:

How can we get the amount of shipments from the number of shipments?

We left-click on the field "Quantity by field Shipment amount" in the area of ​​​​the pivot table "Values", and in the menu that opens, select "Parameters of value fields ..."

In the window that opens, select the data reduction operation we are interested in (Sum, quantity, average, maximum, minimum ...). Select the operation we need "sum" and click "OK".

We get the total sales volume for each shipment range:

Those. we see how much sales fall on shipments in the range from 0 to 5,000 rubles, from 5,000 to 10,000 rubles. etc. And it is clear that the maximum volume of shipments falls on the range from zero to 5000 rubles.

3 property - the ability for one field to calculate various data reduction operations

Now we would like to see how many shipments and what average shipments we have in each of the ranges. To do this, we use the pivot table to calculate the number of shipments and average shipments.

In the area of ​​\u200b\u200bthe pivot table "Values" we drag the field "Shipping_amount" 2 more times and in the parameters of the value field for the second select "quantity" and for the third field select "average".

We get for each range of shipments the sales volume, the number of shipments and the average shipment:

Now we can see in which range of shipments the maximum sales volume and the maximum number of shipments. In our example, this is for the range from 0 to 5000 rubles. and the volume of sales and the number of shipments as much as possible.

4th property of pivot tables - the ability to carry out additional calculations

For clarity of data analysis, let's add 2 more parameters - "Share by sales volume for each group" and "Share of the number of shipments for each group".

To do this, in the field of the pivot table "Values" drag the field "Shipping amount" 2 more times

Moreover, for one parameter in the menu "Parameters of the field of values" () we will select the operation "sum", and for the second operation "quantity".

We get the following table:

Now once again we go to the "Parameters of the value fields" and enter the tab "Additional calculations":

Select in the field "Additional calculations" the item "Share of the total amount"

We get a table in which for each range of shipments to customers we see the volume of sales, the number of shipments, the average shipment, the share of sales for each group and the share of the number of shipments for each group:

5 useful property - sorting

Now, for clarity, from the maximum to the minimum group by sales volume, we will sort. To do this, place the cursor in the field with the volume of sales by groups and click on the "sort from maximum to minimum" button:

It can be seen that the maximum group in terms of sales volume and the number of shipments is the group "from 0 to 5000 rubles." average sales in this group are 1971 rubles.

Note! The average shipment across all customers is significantly different from 86% of shipments. Moreover, it differs significantly

• for all groups, the average shipment is 2,803 rubles. (in line total).
• And for 86% of shipments, 1,971 rubles.

This is a serious difference, and if we stimulate sales based on 86% of shipments and the average for them - 1,971 rubles, then our actions will be more accurate, and the effect is much higher, because. We will be able to interest the maximum number of customers.

This example shows a powerful technique for mass market promotion planning and sales forecasting that can help you make a big impact and make a difference.

If you have any questions, please contact.

Accurate predictions for you!

Join us!

Download Free Forecasting and Business Intelligence Apps:

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

Test the features of paid solutions:

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

Above, we considered the problem of operations research, where it was required to choose a solution in such a way as to maximize (or minimize) a single performance indicator W. it is desirable to make more indicators, others - less.

As a rule, the effectiveness of large, complex operations cannot be exhaustively characterized by a single indicator; to help him, he has to attract others, additional ones.

For example, when evaluating performance industrial enterprise There are a number of factors to take into account, such as:

Profit,

The total volume of production ("shaft"),

Cost price, etc.

When analyzing a combat operation, in addition to the main indicator characterizing its effectiveness (for example, the mathematical expectation of damage inflicted on the enemy), a number of additional ones have to be taken into account, such as:

own losses,

Operation execution time

Ammunition consumption, etc.

This multiplicity of performance indicators, of which some are desirable to maximize and others to be minimized, is characteristic of any somewhat complex task of operations research. The question arises: how to be?

First of all, it must be emphasized that the requirements put forward are, generally speaking, incompatible. A decision that maximizes one indicator usually does not maximize or minimize other indicators scientific research does not fit. Any of the formulations “achieving the maximum effect at a given cost” or “achieving a given effect at a minimum cost” is correct.

In the general case, there is no solution that would turn one indicator into a maximum and at the same time into a maximum (or minimum) another indicator, moreover, such a solution does not exist for several indicators. However, quantitative analysis efficiency can be very useful in the case of several performance indicators.

First of all, it allows one to reject in advance clearly irrational solutions that are inferior to the best options for all indicators.

Let's illustrate what has been said with an example. Let the combat operation O be analyzed, evaluated by two indicators:

W is the probability of completing a combat mission ("efficiency");

S is the cost of funds spent.

Obviously, it is desirable to turn the first indicator to a maximum, and the second to a minimum.

Suppose for simplicity that a finite number of 20 different solutions are offered to choose from; denote them. For each of them, the values ​​of both indicators W and

For clarity, we depict each solution as a point on a plane with coordinates W and S (Fig. 1.1).

Looking at the figure, we see that some solutions are “uncompetitive” and must be discarded in advance. Indeed, those options that have an advantage in efficiency W over other options with the same cost S must lie on the right boundary of the region options. The same options, which, with equal efficiency, have a lower cost, should lie on the lower boundary of the region of possible options.

What options should be preferred when evaluating the effectiveness of the two indicators? Obviously, those that lie simultaneously on the right and on the lower boundary of the region (see the dotted line in Fig. 1.1). Indeed, for each of the options that do not lie on this section of the border, there will always be another option that is not inferior to it in efficiency, but cheaper, or, conversely, not inferior to it in terms of cheapness, but more efficient. Thus, out of the 20 previously put forward options, the majority drop out of the competition, and we only have to analyze the remaining four options: . Of these, the most effective, but relatively expensive; - the cheapest, but not so effective. It is up to the decision maker to figure out at what price we are willing to pay for a certain increase in efficiency, or, conversely, what share of efficiency we are willing to sacrifice so as not to incur too large material losses.

A similar preview of options (albeit without such a visual geometric interpretation) can be made in the case of many indicators:

Such a pre-screening procedure for non-competitive solutions should always precede the solution of an operations research problem with multiple indicators. Although this does not remove the need for compromise, it significantly reduces the set of decisions within which the choice is made.

In view of the fact that a comprehensive assessment of an operation by several indicators at once is difficult and requires reflection, in practice they often try to artificially combine several indicators into one generalized indicator (or criterion). Often, a fraction is taken as such a generalized (composite) criterion; in the numerator put those indicators that it is desirable to increase, and in the denominator - those that it is desirable to reduce:

For example, if we are talking about a military operation, the numerator puts such values ​​as “the probability of completing a combat mission” or “losses of the enemy”; in the denominator - "own losses", "ammunition consumption", "operation time", etc.

A common disadvantage of "composite criteria" of the type (5.1) is that the lack of efficiency in one indicator can always be compensated for by another (for example, a low probability of completing a combat mission due to low ammunition consumption, etc.). Criteria of this kind are jokingly reminiscent of Leo Tolstoy's "criterion for evaluating a person" in the form of a fraction, where the numerator is the true merits of a person, and the denominator is his opinion about himself. The inconsistency of such a criterion is obvious: if we take it seriously, then a person with almost no merit, but completely without conceit, will have an infinitely greater value!

Often "composite criteria" are offered not as a fraction, but as a "weighted sum" of individual performance indicators:

where are positive or negative coefficients. Positive ones are set at those indicators that it is desirable to maximize; negative for those that it is desirable to minimize. The absolute values ​​of the coefficients ("weights") correspond to the degree of importance of the indicators.

It is easy to see that a composite criterion of the form (5.2) essentially does not differ in any way from a criterion of the form (5.1) and has the same drawbacks (the possibility of mutual compensation of heterogeneous indicators). Therefore, the uncritical use of any kind of "composite" criteria is fraught with dangers and may lead to incorrect recommendations. However, in some cases, when the "weights" are not chosen arbitrarily, but are selected so that the composite criterion performs its function best, it is possible to obtain some results of limited value with its help.

In some cases, a problem with several indicators can be reduced to a problem with a single indicator, if you select only one (main) indicator of efficiency and strive to turn it into a maximum, and impose only some restrictions on the rest, auxiliary indicators:

These restrictions, of course, will be included in the set of given conditions.

For example, when optimizing the work plan of an industrial enterprise, it can be required that the profit is maximum, the assortment plan is fulfilled, and the cost of production is not higher than the specified one. When planning a bombing raid, one can demand that the damage inflicted on the enemy be maximum, but at the same time one's own losses and the cost of the operation should not go beyond known limits.

With such a formulation of the problem, all performance indicators, except for one, the main one, are transferred to the category of specified operation conditions. Solutions that do not fit within the given boundaries are immediately discarded as uncompetitive. The recommendations received will obviously depend on how the constraints on the supporting indicators are chosen. To determine how much this affects the final recommendations for choosing a solution, it is useful to vary the restrictions within reasonable limits.

Finally, another way of constructing a compromise solution is possible, which can be called the “method of successive concessions”.

Let's assume that the performance indicators are arranged in descending order of importance: first the main one, then the other, auxiliary ones: For simplicity, we will assume that each of them needs to be turned into a maximum (if this is not the case, it is enough to change the sign of the indicator). The procedure for constructing a compromise solution is as follows. First, a solution is sought that maximizes the main performance indicator. Then, based on practical considerations and the accuracy with which the initial data are known (and often it is small), some “concession” that we agree to allow in order to maximize the second indicator is assigned. We impose a restriction on the indicator so that it is not less than where W is the maximum possible value and, under this restriction, we look for a solution that converts to a maximum.

This way of constructing a compromise solution is good because here it is immediately clear at the price of what “concession” in one indicator a gain in another is acquired.

We note that the freedom to choose a solution, acquired at the price of even insignificant “concessions”, may turn out to be significant, since the efficiency of the solution usually changes very little in the region of the maximum.

One way or another, with any method of formalization, the task of quantitative justification of the decision by several indicators remains not completely defined, and the final choice of the decision is determined by the will of the "commander" (as we will conventionally call the person responsible for the choice). The job of the researcher is to provide the commander with a sufficient amount of data, I allow. to him to comprehensively evaluate the advantages and disadvantages of each solution and, based on them, make the final choice.

This is a chapter from a book: Michael Girvin. Ctrl+Shift+Enter. Mastering array formulas in Excel.

Selections based on one or more conditions. A number of Excel functions use comparison operators. For example, SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, and AVERAGEIFS. These functions make selections based on one or more conditions (criteria). The problem is that these functions can only add, count, and average. And if you want to impose conditions on the search, for example, the maximum value or standard deviation? In these cases, since there is no built-in function, you must invent an array formula. Often this is due to the use of the array comparison operator. The first example in this chapter shows how to calculate the minimum value under one condition.

Let's use the IF function to select the elements of an array that meet a condition. On fig. 4.1 in the left table there is a column with the names of cities and a column with time. It is required to find the minimum time for each city and place this value in the corresponding cell of the right table. The selection condition is the name of the city. If you use the MIN function, you can find the minimum value of column B. But how do you select only those numbers that apply only to Auckland? And how do you copy the formulas down the column? Since Excel does not have a built-in MINESLI function, you need to write an original formula that combines the IF and MIN functions.

Rice. 4.1. Purpose of the formula: to select the minimum time for each city

Download note in format or in format

As shown in fig. 4.2, you should start entering the formula in cell E3 with the MIN function. But you can't put in an argument number1 all values ​​of column B!? You want to select only those values ​​that are related to Auckland.

As shown in fig. 4.3, in the next step, enter the IF function as an argument number1 for MIN. You put an IF inside a MIN.

By positioning the cursor at the point where the argument is entered log_expression function IF (Fig. 4.4), you select the range with the names of cities A3:A8, and then press F4 to make cell references absolute (see, for example, for more details). Then you type the comparative operator, the equals sign. Finally, you'll select the cell to the left of the formula, D3, leaving the reference to it relative. The formulated condition will allow you to select only Aucklands when viewing the range A3:A8.

Rice. 4.4. Create an array operator in an argument log_expression IF functions

So you've created an array operator with a comparison operator. At any time during array processing, the array operator is a comparison operator, so its result will be an array of TRUE and FALSE values. To verify this, select the array (to do this, click on the argument in the tooltip log_expression) and press F9 (Fig. 4.5). Usually you use one argument log_expression, returning either TRUE or FALSE; here, the resulting array will return multiple TRUE and FALSE values, so the MIN function will select the minimum number only for those cities that match the TRUE value.

Rice. 4.5. To see an array of TRUE and FALSE values, click the argument in the tooltip log_expression and press F9