исследованиях

Рассмотрим применение информационных технологий в АВС – анализе на торгово-розничном предприятии.

Основные задачи для проведения анализа:

1. Выделить для дальнейшего финансирования основные товарные позиции, формирующие оборот Компании.

2. Выделить основные товарные позиции, приносящие прибыль Компании.

3. Подобрать оптимальный ассортимент товаров с целью: сокращения количества нереализованных продаж, возникающих вследствие отсутствия необходимых товаров; уменьшения излишек товаров и предотвращения закупки «неходовых» товаров; снижения риска списания товаров в связи с окончанием срока годности; минимизации суммарных затрат, связанных с запасами.

Рассмотрим пример. Исходные данные для анализа приведены в табл. 22:

Таблица 22

Исходные данные для проведения АВС – анализа

Наименование товара	Издержки на ед. продукции, руб.	Стоимость ед. продукции, руб.	Кол-во проданных ед. продукции, шт.	Объём продаж, руб.	Прибыль, руб.
Товар 1
Товар 2
Товар 3
Товар 4
Товар 5
Товар 6
Товар 7
Товар 8
Товар 9
Товар 10
ВСЕГО

Алгоритм решения задачи:

Шаг 1. Рассматриваемым объектом анализа является номенклатура товара.

Шаг 2. Рассматриваемые параметры: объем продаж; прибыль; количество проданных единиц продукции.

Шаг 3 и 4. Сортировка и определение групп АВС.

1. Определение групп АВС, формирующих оборота Компании по 1-му способу «Расчёт доли параметра».

Перенесем данные 1 (наименование товара) и 5 (объем продаж, руб.) столбца таблицы № 3 в Excel.

Далее необходимо рассчитать долю от общей суммы параметров выбранного объекта. Для этого в ячейке С2 ставим знак «=», выделяем ячейку В2, ставим знак «/» (деление), выделяем ячейку В12, ставим знак «*» (умножение) на 100 (рис. 51).

Рис. 51. Ввод формулы для расчета доли от общей суммы

Те же самые действия осуществляем с каждым последующим товаром, в результате получится таблица 23.

Таблица 23

АВС - анализ объёма продаж

Наименование товара	Объём продаж, руб.	%-ое соотношение
Товар 1		13,67543631
Товар 2		14,89971347
Товар 3		6,251628028
Товар 4		5,470174525
Товар 5		2,18806981
Товар 6		1,406616306
Товар 7		0,833550404
Товар 8		3,750976817
Товар 9		2,29226361
Товар 10		49,23157072
ВСЕГО

Далее необходимо ранжировать значение столбца «%-ое соотношение» по убыванию. Для этого необходимо выделить исходные данные С2:С14; Данные – Сортировка, появляется окно «Сортировка диапазона» (рис. 52).

Рис. 52. Окно «Сортировка данных»

В появившемся окне: Сортировка по – заголовок столбца, по которому выполняется сортировка (%-ое соотношение): по возрастанию/по убыванию – установить в положении по убыванию; Затем - заголовок «Объем продаж», по убыванию, В последнюю очередь по – не активизировать; Идентификация поля по подписям – активизировать (рис. 53). ОК.

Рис. 53. Окно «Сортировка диапазона» с необходимыми параметрами

В результате данных действия таблица имеет следующий вид (табл. 25).

Таблица 25

АВС - анализ объёма продаж

Наименование товара	Объём продаж, руб.	%-ое соотношение
Товар 10		49,23157072
Товар 2		14,89971347
Товар 1		13,67543631
Товар 3		6,251628028
Товар 4		5,470174525
Товар 8		3,750976817
Товар 9		2,29226361
Товар 5		2,18806981
Товар 6		1,406616306
Товар 7		0,833550404
ВСЕГО

Для присвоения групп А, В и С необходимо рассучить полученную долю объектов с накопительным итогом: группа А – первые 60-65%, т.е. складываем значения третьего столбца начиная с первой строки, пока не получим 60-65%. Для этого в любой ячейке рабочего листа ставим курсор, знак «=», далее знак «автосумма» и выделяем значения, пока не получим 60-65% (рис. 54).

Рис. 54. Расчет группы А

В результате получается сумма первых двух значений 3 столбца дает 64,131%, следовательно, товар 10 и товар 2 относятся к группе А.

Далее рассчитываем группу В: к полученному значению прибавляем следующие значения 3 столбца, пока не получим 80-85%: в любой ячейке знак «=», выделяем ячейку D3 (со значением полученной суммы), ставим знак «+» и поочередно прибавляем следующие значения пока не получим необходимую сумму (рис. 55).

Рис. 55. Расчет группы В

В результате получаем, что товар 1 и товар 3 относятся к группе В, т.к. дают в сумме 84,085%.

Остальные товары относятся к группе С (табл. 26).

Таблица 26

АВС - анализ объёма продаж

Наименование товара	Объём продаж, руб.	%-ое соотношение	Наименование группы
Товар 10		49,23157072	А
Товар 2		14,89971347	А
Товар 1		13,67543631	В
Товар 3		6,251628028	В
Товар 4		5,470174525	С
Товар 8		3,750976817	С
Товар 9		2,29226361	С
Товар 5		2,18806981	С
Товар 6		1,406616306	С
Товар 7		0,833550404	С
ВСЕГО

Вывод: Товары 2 и 10 являются основными товарами, формирующими около 65% оборота Компании.

Определим группы АВС, приносящих основную прибыль Компании по способу «Математический метод».

Таким образом, значение групп АВС распределится следующим образом в интервальных рядах::

- диапазон группы А: VA= { 36450 и более };

- диапазон группы В: VВ= { от 12150 до 36450};

- диапазон группы С: VС= { менее 12150 }.

Перенесем данные 1 (наименование товара) и 6 (прибыль, руб.) столбца таблицы 22 в Excel.

Далее необходимо ранжировать значение столбца Прибыль, руб.» по убыванию. Для этого необходимо выделить исходные данные В16:В25; Данные – Сортировка, появляется окно «Сортировка диапазона».

В появившемся окне: Сортировка по – заголовок столбца, по которому выполняется сортировка (Прибыль, руб.): по возрастанию/по убыванию – установить в положении по убыванию; Затем и В последнюю очередь по – не активизировать; Идентификация поля по подписям – активизировать (рис. 56). ОК.

Рис. 56. Окно «Сортировка диапазона» с необходимыми параметрами

В результате таблица примет следующий вид:

Наименование товара	Прибыль, руб.
Товар 10
Товар 1
Товар 2
Товар 4
Товар 8
Товар 5
Товар 9
Товар 6
Товар 3
Товар 7
ВСЕГО

Для присвоения значения группы А необходимо складывать значения первых строк полученной таблицы пока не получим «36450 и более»:

Для этого в любой ячейке рабочего листа ставим курсор, знак «=», далее знак «автосумма» и выделяем значения, пока не получим необходимое число (рис. 57).

Рис. 57. Расчет группы А

В результате получается сумма первых двух значений дает 39900, следовательно, товар 10 и товар 1 относятся к группе А.

Далее рассчитываем группу В: прибавляем следующие значения пока не получим сумму в пределах от от 12150 до 36450: в любой ячейке знак «=», далее знак «автосумма» и выделяем значения, пока не получим необходимое число (рис. 58).

Рис. 58. Расчет группы В

В результате Товар 2, Товар 4 и Товар 8 в сумме дают 14300, следовательно, они относятся к группе В. Остальные товары относятся к группе С (табл. 27).

Таблица 27

АВС-анализ прибыли

Наименование товара	Прибыль, руб.	Наименование группы
Товар 10		А
Товар 1		А
Товар 2		В
Товар 4		В
Товар 8		В
Товар 5		С
Товар 9		С
Товар 6		С
Товар 3		С
Товар 7		С
ВСЕГО

Вывод: Товары 1 и 10 являются основными товарами, приносящими 65% прибыли Компании.

Пример. Рассмотрим следующую методику оценки ассортимента товара методом АВС-анализа. По степени важности и величине требуемого внимания всех покупателей к продукции фирмы «Х» можно условно разделить на три следующие группы: покупатели (20%) в сумме дающие 80% объема продаж (группа А); покупатели (20%) в сумме дающие 15% объема продаж (группа В); покупатели (65%) в сумме дающие 5% объема продаж (группа С).

На рис. 59 приведено количество покупателей в этих группах за период с 2006-2008 годы. Группа А являясь наиболее перспективной и имеет постоянную тенденцию роста. Это свидетельствует о высокой эффективности продаж фирмы «Х», так как основная масса усилий и времени тратится на клиентов приносящих около 80 % от объема продаж металлопродукции. В группах В наблюдается прирост покупателей, этот рост может приводить к увеличению доли рынка, но при этом нужно учитывать возможное уменьшение рентабельности продаж. В тоже время число мелких покупателей, составляющих группу С постоянно повышается (см. рис. 59)

Рис. 59. Динамика потребителей по группам «Х» и их тренды

У нас в распоряжении имеются следующие данные о результатах продаж торговой фирмы «Х» за октябрь месяц 2008 г (табл. 28).

Таблица 28

Исходные данные продаж металла торговой фирмы «Х» за 2008 г.

Товарная группа	Шифр товара	Объем продаж, ед.	Товарооборот, тыс. руб.
Группа I	Товар 101		409,50
Группа I	Товар 102		279,50
Группа I	Товар 103		1029,00
Группа I	Товар 104		13856,40
Группа I	Товар 105		8970,10
Группа I	Товар 106		3500,00
Группа I	Товар 107		4004,00
Группа I	Товар 108		11718,00
Группа I	Товар 109		24519,00
Группа I	Товар 110		435,20
Группа I	Товар 111		338,00
Группа I	Товар 112		2431,10
Группа I	Товар 113		728,00
Группа I	Товар 114		2253,60
Группа II	Товар 201		1457,00
Группа II	Товар 202		483,20
Группа II	Товар 203		117,00
Группа II	Товар 204		880,15
Группа II	Товар 205		576,00
Группа II	Товар 206		1329,90
Группа II	Товар 207		18595,50
Группа II	Товар 208		16536,00
Группа II	Товар 209		14806,80
Группа II	Товар 210		60591,10
Группа II	Товар 211		5989,00
Группа II	Товар 212		3770,10
Группа III	Товар 301		805,00
Группа III	Товар 302		2513,70
Группа III	Товар 303		56,80
Группа III	Товар 304		172,90
Группа III	Товар 305		40825,40
Группа III	Товар 306		50808,45
Группа III	Товар 307		300,00
Группа III	Товар 308		341,25
Группа III	Товар 309		437,00
Группа III	Товар 310		4092,00
Группа III	Товар 311		3461,40
Итого			303417,05

Как видно, в структурном отношении фирменный ассортимент продукции «Х» включает 3 товарные группы. Первая группа включает 14 товаров, вторая — 12 и третья — 11. Очевидно, что товары различных групп могут быть совершенно разнотипными и не поддаваться сравнению по своим физическим характеристикам (атрибутам). В этом плане объем продаж товаров в значительной степени будет отражать специфику атрибутов каждого из товаров. Однако мы уже выбрали один, общий для всех товаров, показатель - товарооборот за период. Ранжируем товары по данному показателю: отсортируем товары в порядке убывания их товарооборота (см. таблицу 29, графа 4).

Таблица 29

Товарная группа	Шифр товара	Объем продаж,ед.	Товарооборот, тыс. руб.	Доля в товарообороте, %	Накопленная доля в товарообороте, %	№ позиции	АВС-признак	Доля числа позиций в номенклатуре, %
Группа II	Товар 210		60591,10	19,97	19,97		А
Группа III	Товар 306		50808,45	16,75	36,72		А
Группа III	Товар 305		40825,40	13,46	50,17		А
Группа I	Товар 109		24519,00	8,08	58,25		А-В
Группа II	Товар 207		18595,50	6,13	64,38		B
Группа II	Товар 208		16536,00	5,45	69,83		B
Группа II	Товар 209		14806,80	4,88	74,71		B
Группа I	Товар 104		13856,40	4,57	79,28		B
Группа I	Товар 108		11718,00	3,86	83,14		B-C
Группа I	Товар 105		8970,10	2,96	86,10		B-C
Группа II	Товар 211		5989,00	1,97	88,07		B-C
Группа III	Товар 310		4092,00	1,35	89,42		B-C
Группа I	Товар 107		4004,00	1,32	90,74		B-C
Группа II	Товар 212		3770,10	1,24	91,98		B-C
Группа I	Товар 106		3500,00	1,15	93,13		B-C
Группа III	Товар 311		3461,40	1,14	94,27		B-C
Группа III	Товар 302		2513,70	0,83	95,10		B-C
Группа I	Товар 112		2431,10	0,80	95,90		B-C
Группа I	Товар 114		2253,60	0,74	96,65		C
Группа II	Товар 201		1457,00	0,48	97,13		C
Группа II	Товар 206		1329,90	0,44	97,57		C
Группа I	Товар 103		1029,00	0,34	97,90		C
Группа II	Товар 204		880,15	0,29	98,19		C
Группа III	Товар 301		805,00	0,27	98,46		C
Группа I	Товар 113		728,00	0,24	98,70		C
Группа II	Товар 205		576,00	0,19	98,89		C
Группа II	Товар 202		483,20	0,16	99,05		C
Группа III	Товар 309		437,00	0,14	99,19		C
Группа I	Товар 110		435,20	0,14	99,34		C
Группа I	Товар 101		409,50	0,13	99,47		C
Группа III	Товар 308		341,25	0,11	99,58		C
Группа I	Товар 111		338,00	0,11	99,70		C
Группа III	Товар 307		300,00	0,10	99,79		C
Группа I	Товар 102		279,50	0,09	99,89		C
Группа III	Товар 304		172,90	0,06	99,94		C
Группа II	Товар 203		117,00	0,04	99,98		C
Группа III	Товар 303		56,80	0,02	100,00		C

Введем необходимые обозначения показателей. Обозначим число товаров ассортимента через n, величину товарооборота через Xj, где j = l,...,n — индекс (порядковый номер) товара, присвоенный ему после ранжирования (см. таблицу 29, графа 8). Пусть, также, X — величина товарооборота фирмы «Х» за отчетный период. Далее, вычислим долю оборота каждого товара в обороте фирмы: (см. таблицу 29, графа 5).

Дальнейший шаг — расчет накопленной (кумулятивной) доли в товарообороте. Показатель характеризует вклад набора товаров в оборот фирмы «Х». А, учитывая, что товары ранжированы в порядке убывания своих вкладов в товарооборот фирмы, получаем, что накопленная доля в любой строке таблицы 29 характеризует вклад наиболее значимых товаров в оборот фирмы. Имеем: (см. таблицу 29, графа 6).

Именно накопленная доля и будет являться той количественной характеристикой, которая позволит нам объединить товары в, так называемые, АВС-группы. Выделим товары, которые обеспечили фирме «Х» не менее 80 % товарооборота. Для этого осуществляем проверку Dj < 80 %, j=l,2,3,... (см. таблицу 29, графа 6). Последним в данном списке оказывается Товар 104. Итак, мы получили две группы товаров: первая группа обеспечивает 80 % товарооборота, вторая — 20 %. Однако это очень «грубое» разбиение, которое пока еще не даст нам необходимой аналитической информации для принятия каких-либо правильных управленческих решений в плане оптимизации фирменного ассортимента.

Разобьем каждую из полученных групп также по правилу «80—20». Для первой группы получаем следующую контрольную точку - накопленную долю в товарообороте: 80 % х 80 % = 64 %, для второй — 20 % х 80 % = 16 %. Таким образом, разбиваем товары на 4 группы: первая группа обеспечивает до 64 % товарооборота (список товаров заканчивает Товар 109, см. таблицу 29, графа 6), вторая группа обеспечивает следующие 16 % товарооборота (список товаров заканчивает Товар 104), третья группа обеспечивает еще 16 % товарооборота (список товаров заканчивает Товар 112), четвертая группа обеспечивает оставшиеся 4 % товарооборота фирмы «Х».

Полученные группы товаров уже становятся более «понятными» в плане оценки своей значимости. Чаще всего требуется провести еще одно, дополнительное разбиение первой группы, так как 64-процентный вклад товаров этой группы в товарооборот довольно значителен и статус отдельных товаров, входящих в группу требует все же своего уточнения. Продолжим разбиение. Разбиваем первую группу по правилу «80-20». Новая контрольная точка — 80 % х 64 % = 51,2 %. Новую группу заканчивает Товар 305. Группа обеспечивает 51,2 % товарооборота. Теперь приписываем символические названия группам и товарам, входящим в них (см. таблицу 29, графа 7 и таблицу 30).

Таблица 30

Характеристики групп товаров

Группа	Доля группы в товарообороте	Накопленная доля групп в товарообороте	Характеристика товаров, входящих в группу
А	51,2 %	51,2 %	Товары-лидеры, определяющие коммерческое и финансовое состояние фирмы, ее статус на рынке – металлы для строительства (арматура, трубы). Обычно это товары, которые достаточно давно находятся в ассортименте фирмы и хорошо известны потребителям. Чаще всего они приносят и наибольшую прибыль фирме. Даже в том случае, если товары и не совсем устраивают руководство фирмы в плане своей рентабельности, вывод их из ассортимента может быть связан со значительным риском для фирмы.
А-В	12,8 %	64 %	Важнейшие товары прокатного ассортимента, а также потенциальные товары-лидеры либо бывшие товары-лидеры, потерявшие свой прежний статус временно из-за неблагоприятного влияния сезонных явлений или, быть может, уже навсегда (профили, ленты, сетка). Очень важная группа для анализа переходных явлений.
В	16 %	80 %	Сопутствующие товары (проволока, профлист), имеющие большое значение для фирмы, а также товары, недавно введенные в ассортимент или товары, потерявшие свой былой статус лидеров продаж.
В-С	16 %	96 %	Несущественные для фирмы сопутствующие товары, а также товары, недавно введенные в ассортимент или товары, потерявшие свой былой статус лидеров продаж.
С	4 %	100 %	Товары, которые без всякого риска можно вывести из ассортимента (если только какие-либо из них не обладают уникальными атрибутами, играющими важную роль для потребителей, что, конечно же, в большинстве случаев маловероятно), а также товары, только что введенные в ассортимент.

Обратите внимание, сколько позиций входит в группу «А», «А-В» и сколько — в группу «С» ассортимента фирмы «Х». Четыре позиции обеспечивают 64 % товарооборота и 19 позиций — только 4 %. Это «нормальная» ситуация для большинства предприятий торговли, где нет должного управления ассортиментом. На самом деле чаще всего на практике ситуация оказывается еще хуже, чем в этом примере.

Однако анализ табличных данных может быть не очень удобным делом, если ассортимент включает сотни товаров. Требуется более наглядный и интегрированный способ оценки ассортимента. Поэтому, следующий шаг анализа — построение АВС-диаграммы и оценка степени концентрации объема товарооборота на ассортиментной линии. Для этого рассчитаем доли числа позиций, входящих в ABC — группы в общей номенклатуре товаров (см. таблицу 29, графа 9):

Теперь построим ABC-диаграмму (рис. 60), на которой по оси абсцисс отложены доли числа позиций fj от общего числа товаров фирменного ассортимента (ассортиментная линия), а по оси ординат — накопленные доли оборота товаров в товарообороте фирмы Dj, j=l,...,n.

Обратите внимание, что если бы распределение продаж на ассортиментной линии было бы равномерным (1 % от общего числа товаров обеспечивал бы 1% товарооборота, 10 % - 10 %, 50 % -50 % и т. д.), то мы бы получили диагональ, соединяющую точки (0 %; 0 %) и (100 %; 100 %) плоскости (см. ABC-диаграмму).

Рис. 60. Доля числа позиций в ассортименте - ассортиментная

линия АВС-диаграммы

Однако мы получили кривую, наглядно характеризующую фактическую неравномерность распределения товарооборота на ассортиментной линии. Количественная оценка такой неравномерности может быть выражена следующим показателем концентрации товарооборота на ассортиментной линии:

где S_T — площадь криволинейной трапеции, ограниченной сверху кривой распределения товарооборота на ассортиментной линии, а Dд — площадь прямоугольного треугольника, расположенного ниже диагонали равномерного распределения товарооборота на ассортиментной линии. Используя метод трапеций для приближенного вычисления S_T, получим следующую формулу:

Значения показателя Н меняются в диапазоне от 0 % до 100 %. Если распределение товарооборота является равномерным, то

Если же, к примеру, из всего ассортимента, только один товар продавался за период, то получим, что Н =100%. Таким образом, чем шире ассортимент, чем больше номенклатура товаров и чем меньше при этом товаров, вносящих значительный вклад в товарооборот предприятия, тем ближе к 100 % значение показателя Н.

Используя данные таблицы 29 и формулу (H), получаем, что . Значение показателя говорит о существенной концентрации товарооборота на ассортиментной линии. Т. е. деятельность фирмы «Х» обеспечивается продажами всего нескольких позиций. Хорошо это или плохо? Все зависит от специфики рынка, на котором действует фирма «Х» и ситуации, сложившейся на нем в настоящий момент. Однако в большинстве случаев такие показатели работы фирмы будут означать, что требуется проведение серьезных мероприятий по совершенствованию структуры ассортимента и выработке в целом более эффективной ассортиментной политики фирмы.

Приведем подробное описание технологии получения решения данной задачи с помощью табличного редактора Excel пакета прикладных программ MS Office.

1. Запустим Excel, выбрав Microsoft Excel из подменю Программы главного меню Windows. Открыв чистый лист Excel, создадим текстовую форму – таблицу и введем значения по условию задачи (рис. 61)

Рис. 61. Таблица с исходными данными продаж металла фирмой «Х»

2. Рассчитаем общий товарооборот по всем группам товаров. Для этого необходимо поставить курсор в ячейку D39 и выбрать знак «Автосумма» на панели инструментов. Выделить ячейки с D2 по D38 и нажать Enter (см. рис. 62). В ячейки D39 появится значение общего товарооборота 303417,05.

Рис. 62. Расчет общего товарооборота

3. Отсортируем товары в порядке убывания товарооборота. Сначала скопируем входную таблицу: выделить ячейки А1:D3, нажать «Копировать» на панели инструментов, затем поставить курсор в ячейку F1 и выбрать «Вставить» на панели инструментов (см. рис. 63).

Рис. 63. Копирование входной таблицы данных

Затем выделяем ячейки F1:I38, заходим на закладку «Данные», в раскрывшемся меню выбираем «Сортировка» (см. рис. 64).

Рис. 64. Выбор функции «Сортировка»

Появляется диалоговое окно «Сортировка диапазона». В строке «Сортировать по» нажимаем на стрелочку для раскрытия списка, где выбираем «Товарооборот», затем ставим бубочку «по убыванию», ОК (см. рис. 65).

Рис. 65. Выбор параметров в диалоговом окне «Сортировка диапазона»

4. Рассчитаем долю каждого товара в товарообороте организации. Для этого поставим курсор в ячейку J2, выберем знак «=», затем ячейку I2, знак «/», наберем число общего товарооборота 303417,05, знак «*» и число 100, Enter. Появляется значение 19,97 (см. рис. 66).

Рис. 66. Расчет доли товара 210 в товарообороте фирмы «Х»

Далее ставим курсор в правый нижний угол ячейки J2 и тянем вниз до ячейки J38. Таким образом, мы получили значения доли товаров (см. рис. 67).

Рис. 67. Расчет доли товаров в товарообороте

5. Рассчитаем накопленную долю товаров в товарообороте. В ячейку К2 ставим значение 19,97, затем в ячейке К3 пишем формулу: знак «=», выбираем ячейку J3, знак «+», выбираем ячейку К2, Enter. Получили значение 36,72. Далее ставим курсор в правый нижний угол ячейки К3 и тянем вниз до К38. Та ким образом, находим значения накопленных частот (см. рис. 68).

Рис. 68. Расчет накопленной доли в товарообороте

6. Проведем группировку товара. Для этого поставим курсор в ячейку L2 и зададим формулу: ставим знак «=», выбираем «Мастер функций», в появившемся диалоговом окне «Мастер функций шаг 1 из 2» выбираем из окна «Категория» - логические, а из окна «Выберете функцию» - ЕСЛИ, ОК (см. рис. 69).

Рис. 69. Выбор функции для группировки товара

Появляется диалоговое окно «Аргументы функции». В строке «Лог_выражение» нажимаем курсором на правую красную стрелочку, выбираем ячейку К2, ставим знак «<80», Enter; в строке «Значение_если_истина» ставим курсор и число 80, Enter, в строке «Значение_если_ложь» - 100, Enter, ОК (см. рис. 70).

Рис. 70. Заполнение диалогового окна «Аргументы функции»

В ячейке L2 появится значение 80, далее курсором тянем за правый нижний угол этой ячейки до L38. Получили первую группировку (см. рис. 71).

Рис. 71. Первый этап группировки товара.

Далее каждую из полученных групп делим еще на 2 группы. Для этого ставим курсор в ячейку М2, знак «=», выбираем «Мастер функций», в появившемся диалоговом окне «Мастер функций шаг 1 из 2» выбираем из окна «Категория» - логические, а из окна «Выберете функцию» - ЕСЛИ, ОК. Далее заполним строки появившегося диалогового окна «Аргументы функции». В строке «Лог_выражение» нажимаем курсором на правую красную стрелочку, выбираем ячейку К2, ставим знак «<», число 64, Enter; в строке «Значение_если_истина» ставим курсор и число 64, Enter, в строке «Значение_если_ложь» - 80, Enter, ОК (см. рис. 72.).

Рис. 72. Заполнение диалогового окна «Аргументы функции»

В ячейке М2 появится значение 64, далее курсором тянем за правый нижний угол до ячейки М9 (см. рис. 73).

Рис. 73. Группировка товара, дающего 80% товарооборота

Далее ставим курсор в ячейку М10 знак «=», выбираем «Мастер функций», в появившемся диалоговом окне «Мастер функций шаг 1 из 2» выбираем из окна «Категория» - логические, а из окна «Выберете функцию» - ЕСЛИ, ОК. Далее заполним строки появившегося диалогового окна «Аргументы функции». В строке «Лог_выражение» нажимаем курсором на правую красную стрелочку, выбираем ячейку К10, ставим знак «<», число 96, Enter; в строке «Значение_если_истина» ставим курсор и число 96, Enter, в строке «Значение_если_ложь» - 100, Enter, ОК (см. рис. 74).

Рис. 74. Заполнение диалогового окна «Аргументы функции»

В ячейке М10 получаем значение 96, тянем за нижний правый угол до ячейки М38. Таким образом, мы провели второй этап группировки товара (см. рис. 75).

Рис. 75. Второй этап группировки товара.

Далее проведем третий, завершающий этап группировки. В ходе решения мы разбиваем товары, составляющие первые 64% товарооборота, еще на 2 группы. Для этого необходимо поставить курсор в ячейку N2, выбрать знак «=», затем «Мастер функций», в появившемся диалоговом окне «Мастер функций шаг 1 из 2» выбираем из окна «Категория» - логические, а из окна «Выберете функцию» - ЕСЛИ, ОК. Далее заполним строки появившегося диалогового окна «Аргументы функции». В строке «Лог_выражение» нажимаем курсором на правую красную стрелочку, выбираем ячейку К2, ставим знак «<», число 51,2, Enter; в строке «Значение_если_истина» ставим курсор и число 51,2, Enter, в строке «Значение_если_ложь» - 64, Enter, ОК (см. рис. 76.).

Рис. 76. Заполнение диалогового окна «Аргументы функции»

Таким образом, в ячейке N2 получаем значение 51,20, тянем вниз до ячейки N5.

Рис. 77. Третий этап группировки товара.

Рис. 78. Группировка товара по доле в товарообороте

Далее выделим курсором ячейки М6:М38, нажмем «Копировать» на панели инструментов, затем ставим курсор в ячейку N6, выбираем «Вставить» на панели инструментов. Итак, получаем столбец N, где представлена окончательная группировка товара (см. рис. 78.).

Проставим номера позиций. Для этого в ячейке О2 ставим значение 1, в О3-значение 2, выделим ячейки О2:О3 и тянем за нижний правый уголок до ячейки О38, получаем номера позиций. Значение ячейки О38 составляет 37.

7. Определим символические значения групп товаров. В столбце АВС-признак ставим курсор в ячейку Р2, выбираем знак «=», затем «Мастер функций», в появившемся диалоговом окне «Мастер функций шаг 1 из 2» выбираем из окна «Категория» - логические, а из окна «Выберете функцию» - ЕСЛИ, ОК. Далее заполним строки появившегося диалогового окна «Аргументы функции». В строке «Лог_выражение» нажимаем курсором на правую красную стрелочку, выбираем ячейку К2, ставим знак «<», число 51,2, Enter; в строке «Значение_если_истина» ставим курсор и значение «А», Enter, в строке «Значение_если_ложь» - «А-В», Enter, ОК (см. рис. 79.).

В ячейке Р2 появится значение «А». Ставим курсор в ячейку Р2 и тянем за правый нижний уголок до ячейки Р5, таким образом получаем символические значения товаров, обеспечивающих 64% товарооборота.

Рис. 79. Заполнение диалогового окна «Аргументы функции»

Далее определим группу товаров «В» и «В-С». Поставим курсор в ячейку Р6, выбираем знак «=», затем «Мастер функций», в появившемся диалоговом окне «Мастер функций шаг 1 из 2» выбираем из окна «Категория» - логические, а из окна «Выберете функцию» - ЕСЛИ, ОК. Далее заполним строки появившегося диалогового окна «Аргументы функции». В строке «Лог_выражение» нажимаем курсором на правую красную стрелочку, выбираем ячейку К6, ставим знак «<», число 80, Enter; в строке «Значение_если_истина» ставим курсор и значение «В», Enter, в строке «Значение_если_ложь» - «В-С», Enter, ОК. В ячейке Р6 получили значение «В», далее тянем за нижний правый уголок до ячейки Р19. Таким образом, получили символические значения товаров, обеспечивающих следующие 32% товарооборота (см. рис. 80).

Рис. 80. Символические значения групп товаров

Далее определим последнюю группу товаров – «С». Ставим курсор в ячейку Р20, выбираем знак «=», затем «Мастер функций», в появившемся диалоговом окне «Мастер функций шаг 1 из 2» выбираем из окна «Категория» - логические, а из окна «Выберете функцию» - ЕСЛИ, ОК. Далее заполним строки диалогового окна «Аргументы функции». В строке «Лог_выражение» нажимаем курсором на правую красную стрелочку, выбираем ячейку К20, ставим знак «<», число 100,01, Enter; в строке «Значение_если_истина» ставим курсор и значение «С», Enter, в строке «Значение_если_ложь» - «ЛОЖЬ», Enter, ОК (см. рис. 81).

Рис. 81. Заполнение диалогового окна «Аргументы функции»

В ячейке Р20 получили значение «С», далее тянем за нижний правый уголок до ячейки Р38. Таким образом, получили символические значения товаров, обеспечивающих оставшиеся 4% товарооборота. Таким образом, получили значения столбца АВС - признак (см. рис. 82).

Рис. 82. Символические значения групп товаров

8. Следующий шаг - расчет доли числа позиций, входящих в ABC — группы в общей номенклатуре товаров. Для этого необходимо поставить курсор в ячейку Q2, знак «=», выбрать ячейку О2, поставить знак «/», число 37 (общее количество товаров), знак «*», число 100, Enter. Таким образом, получили значение ячейки Q2 – 3%. Далее, тянем за нижний правый уголок до ячейки Q38, получаем значения доли числа позиций (см. рис. 83).

Рис. 83. Расчет доли числа позиций

9. Построим АВС - диаграмму. Поставим курсор в ячейку К2, на панели задач выберем закладку «Вставка», в раскрывшемся списке значение «Строки». Затем в ячейках К2, Q2, K40, Q40 поставим значения «0». Далее выделяем ячейки К2:К40, нажимаем «Ctrl» и, удерживая эту кнопку, выделяем ячейки Q2:Q40. После, на панели инструментов выбираем «Мастер диаграмм», в появившемся диалоговом окне «Мастер диаграмм (шаг 1 из 4)…» выбираем тип диаграммы «Точечная» (см. рис. 84.), нажимаем «Далее».

Рис. 84. Выбор типа диаграммы

В открывшемся диалоговом окне «Мастер диаграмм (шаг 2 из 4)…» на закладке «Диапазон данных» ставим метку «столбцах». На закладке «Ряд» в строке «значения Х:» нажмем на правую красную стрелочку и выберем ячейки Q2:Q40, нажимаем на красную стрелочку, после в строке «Значения У:» нажимаем на красную стрелочку, выделяем ячейки К2:К40, нажимаем на кранную стрелочку. Ставим курсор в строке «Имя» и пишем «АВС-диаграмма» (см.рис. 85). Нажимаем «Далее».

Рис. 85. Заполнение диалогового окна «Исходные данные»

В раскрывшемся диалоговом окне «Мастер диаграмм (шаг 3 из 4): параметры диаграммы» на закладке «Заголовки» заполняем строки: «Ось Х (категорий):» - пишем «Доля числа позиций в ассортименте, %»; «Ось У (значений):» - «Накопленная доля в товарообороте, %» (см. рис. 86.)

Рис. 86. Заполнение диалогового окна «Мастер диаграмм (шаг 3 из 4)…»

Далее переходим на закладку «Оси». В строках «Ось Х (категорий)» и «Ось У (значений)» ставим галочки.

Затем переходим на закладку «Линии сетки» в строке «Ось У (значений)» ставим галочку «основные линии» (см. рис. 87).

Рис. 87. Заполнение закладки «Линии сетки»

На закладке «Легенда» убрать галочку в строке «Добавить легенду». На закладке «Подписи данных» тоже галочки не ставим. Нажимаем «Далее». Появляется диалоговое окно «Мастер диаграмм (шаг 4 из 4)…», ставим метку в строке «имеющемся:», нажимаем «Готово» (см. рис. 88.).

Рис. 88. Окно «Мастер диаграмм (шаг 4 из 4): размещение диаграммы)

Получили АВС-диаграмму (см. рис. 89.).

Рис. 89. АВС-диаграмма

Затем на области построения диаграммы ставим курсор на ось ординат и, нажав правую кнопку мыши, в раскрывшемся меню выбираем «Формат оси…» (см. рис. 90).

Рис. 90. Выбор «Формат оси…»

В появившемся окне заходим на закладку «Шкала» и ставим значения, как на рис. 91. Нажимаем ОК.

Рис. 91. Заполнение закладки «Шкала» по оси ординат

Затем ставим курсор на ось абсцисс и, нажав правой кнопкой мыши, выбираем «Формат оси», закладку «Шкала» и заполняем строки как на рис. 92. Нажимаем ОК.

Рис. 92. Заполнение закладки «Шкала» по оси абсцисс

Затем ставим курсор мыши на диаграмму, нажимаем левую кнопку, появляются значения координат точек. Из точек с координатами (14;64,38) и (51;96,65) мы опускаем линии, параллельные оси ординат (см. рис. 93).

Рис. 93. АВС - диаграмма

По нашим расчетам, 14% товарных позиций образуют 64% товарооборота – это группа «А», следующие 37% - 96% товарооборота – группа «В», и целых 49% товарных позиций образуют оставшиеся 4% товарооборота – группа «С». Наглядно это представлено на рис. 94.

Рис. 94. АВС – диаграмма

Список литературы

1. Гусаров В.М. Статистика: учебное пособие для вузов / В.М.Гусаров. – М.: ЮНИТИ-ДАНА, 2001. – 427 с.

2. Орлова И.В. Экономико-математические методы и модели. Выполнение расчетов в среде Excel / Практикум: учебное пособие для вузов / И.В.Орлова. – М.: ЗАО «Финстатинформ», 2000. – 136 с.

3. Толстик Н.В., Матегорина Н.М. Статистика: учебное пособие Н.В.Толстик, Н.М.Матегорина. – Ростов н/д: из-во «Феникс», 2005. – 480с.

4. Статистика. Компьютерные лабораторные работы: Методические указания к лабораторной работе № 1 «Автоматизированный априорный анализ статистической совокупности в среде MS Excel». – М.: Вузовский учебник, 2005. – 72с.

5. Статистика. Компьютерные лабораторные работы: Методические указания к лабораторной работе № 2 «Автоматизированный корреляционно-регрессионный анализ взаимосвязи статистических данных в среде MS Excel». – М.: Вузовский учебник, 2006. – 70с.

Дата добавления: 2015-07-08