Применение abc-анализа в microsoft excel

Методика АВС анализа

Чтобы провести ABC анализ, нужно выгрузить данные из учетных систем в денежном и количественном выражении. Если вы изучаете ассортимент, понадобится информация о движении товара на складе, если анализируете клиентов — продажи.

Программы для проведения ABC анализа — Excel, Google Таблицы, SAP, 1С.

Как структурировать информацию для ABC анализа

1. Данные в таблицах выстраивают в порядке убывания. 
2. Определяют долю каждой позиции в общем объеме.
3. Складывают доли и объединяют в группы по заранее утвержденным признакам А, В и С. 

В XIX веке экономисты сформулировали правило: 20% покупателей приносят 80% прибыли. Оно легло в основу метода ABC анализа.

Классический ABC анализ состоит из трёх пропорций:

Код группы	Объект	Параметр
А — самые ценные	20% всех запасов/клиентов	80% валового дохода
В — средние	30% всех запасов/клиентов	15% валового дохода
С — наименее ценные	50% всех запасов/клиентов	5% валового дохода

Для каждого кода соотношение объекта и параметра может быть любым. Главное в этих утверждениях не числовые значения, а сам факт сильного различия групп между собой. При выборе матрицы нужно учитывать особенности бизнеса, ассортимент, динамику продаж конкретных позиций. Могут также понадобится дополнительные группы:

D — новый товар. По нему еще не сформировалась статистика продаж. Его нельзя привязывать к группам А, В, С т.к. это исказит результат. 

Е — уникальный единичный товар, который выпускали по отдельным заказам клиентов.

О — в исследуемом периоде товар убрали из матрицы. Приходов на склад и продаж больше не планируют.

ABC и XYZ анализ: суть, методика, расшифровка результатов

Недостатки АВС анализа

Исследование не учитывает динамику продаж. В группу С могут попасть товары с низкой ценой, которые продаются регулярно. На них всегда будет спрос, но из-за небольшой стоимости они не могут конкурировать с элитными позициями. 

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

Чтобы исключить такие ошибки в интерпретации данных к выгрузке ABС добавляют показатель динамики продаж.

Принцип ABC-анализа и причины его эффективности

В основе методики анализа лежит утверждение, называемое «метод Парето». Смысл его в том, что основная группа товаров, говоря точнее, группа, приносящая наибольшую прибыль, при продаже 20% товарной массы дает 80% оборотных средств. Главной задачей аналитика становится определение этой важнейшей группы товаров.

ABC-анализ: практика применения и направления развития

Метод можно применять не только к товарам, но и:

• к услугам;
• к поставщикам;
• к покупателям, клиентам.

Главное достоинство этого аналитического метода — в универсальности применения. Эффективность метода, получение корректных, практически значимых результатов достигается соблюдением определенных правил анализа.

Для решения каких задач применяется функционально-стоимостный анализ (ABC-анализ)?

При проведении ABC-анализа необходимо учесть следующее:

• анализ проводится только по одному денежному показателю (прибыль, доходы, затраты, задолженность контрагентов, и пр.);
• объект анализа, например, товар или клиент, иной объект, должен быть связан с определенным значением денежного показателя (сколько выручки приносит данный товар, сколько задолжал определенный клиент, какова сумма закупок у определенного поставщика и пр.);
• если фирма работает по нескольким видам деятельности, берутся данные в рамках только одного вида деятельности (например, продажа канцтоваров отдельно, а закупки книжной продукции отдельно), иначе говоря, важна сопоставимость данных.

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

Кстати говоря! Метод ABC-анализа часто используется в ПО, предназначенном для работы с клиентской базой — различных CRM-системах.

Анализ товаров и услуг

Ситуация: дохода от продаж часто не хватает для закупки сырья и выплат сотрудникам, и компания берет кредиты с большим процентом.

Цель: проанализировать доход от торговли и выяснить его колебание в процентном отношении.

Понадобятся следующие данные (доход выражен в тыс. рублей):

месяц	янв	фев	март	апр	май	июнь	июль	авг	сент	окт	нояб	дек
доход	115	88	108	97	117	79	107	77	93	96	101	75

Первое действие: посчитать среднее арифметическое. Мы складываем все данные в таблице и делим их на количество месяцев: 115 + 88 + 108 + 97 + 117 + 79 + 107 + 77 + 93 + 96 + 101 + 75 / 12 = 1153 / 12. Получается 96,083. 

Второе действие: посчитать среднеквадратичное отклонение по формуле:

Что нам известно:

n – количество величин. В нашем случае оно составляет 12 – по числу месяцев.

x̅ – среднее арифметическое, которое мы наши в предыдущем действии.

xi – значение одной из величин. В нашем случае всего 12 величин, каждая из них – данные за какой-то из месяцев

Σ – означает, что мы должны провести расчет (xi — x̅)2 каждой из 12 величин (обозначается буквой n). То есть: (115 — 96,083)2 + (88 — 96,083)2 + (108 — 93,083)2 и т. д. Мы рассчитываем отдельно по январю, февралю, марту и т. д.

Вставляем данные в формулу: (115 — 96,083)2 + (88 — 96,083)2 + (108 — 93,083)2 + (97 — 96,083)2 + (117 — 96,083)2 + (79 — 96,083)2 + (107 — 96,083)2 + (77 — 96,083)2 + (93 — 96,083)2 + (96 — 96,083)2 + (101 — 96,083)2 + (75 — 96,083)2 / 12. Получается примерно 194. Осталось извлечь квадратный корень из этого числа – получается 13,92.

Теперь нужно поделить получившееся число на среднее арифметическое: 13,92 / 96,08 = 0,144.

Мы опустили тысячные, но нам и не нужно точного числа. Дело в том, что xyz-анализ подходит не только для расчета данных по нескольким объектам (клиентам, например) и сопоставления результатов. Достаточно и одного объекта исследования, который затем можно оценить с данными по всей нише.

В нашем случае коэффициенты вариации подразделяются на:

• Категория X. 0 — 0,1. Отлично.
• Категория Y. 0,1 — 0,25. Сносно.
• Категория Z. 0,25 — 1. Плохо.

Мы вставляем туда 0,144 и видим: дела у фирмы еще не так плохи – она находится в категории «Сносно». 

Эпилог: в компании решили искать причину такого нежелательного колебания продаж. Выяснилось, что система мотивации работает неправильно: менеджеры продают хорошо только под угрозой проверок и увольнения, но в остальные месяцы работают “для галочки”. Особенно это актуально для предпраздничного декабря.

Узнав об этом, владелец бизнеса дал задание начальнику отдела кадров изменить систему мотивации, привязав зарплату сотрудников к количеству выполненного ими плана.

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

1. Бузукова Е.А. Анализ ассортимента и стабильности продаж с использованием ABC-анализа и XYZ-анализа // Управление продажами. 2006. №3. С.166-185.
2. Коников А.И., Коников Г.А. Расширение функциональных возможностей анализа ABC- XYZ // Логистика и управление цепями поставок. 2014. №3(62). С. 54-57.
3. Кутузова М.В. Использование XYZ- анализа в деловой практике торговых организаций // Современные тенденции в экономике и управлении: новый взгляд. 2014. №30. С. 259-264.
4. Резниченко Н.В., Наумов А.А., Баженов Р.И. Совершенствование ассортимента блюд кафе и системы закупок компонентов на основе ABC-XYZ анализа // Экономика и менеджмент инновационных технологий. 2014. №12(39). С. 14-24.
5. Остроушко А.А., Баженов Р.И. Анализ ассортимента электротоваров с использованием ABC-анализа // Экономика и менеджмент инновационных технологий. 2014. № 10 (37). С. 73-81.
6. Жилкин С.А., Баженов Р.И. Совершенствование ассортимента товаров медицинского назначения на основе ABC-анализа // Экономика и менеджмент инновационных технологий. 2014. № 12 (39). С. 103-110.
7. Татаринова Е.Д., Наумов А.А., Баженов Р.И. Совершенствование ассортимента продажи товаров на основе ABC-XYZ-анализа // Экономика и менеджмент инновационных технологий. 2015. № 2 (41). С. 112-122.
8. Перминова Н.А., Баженов Р.И. Совершенствование ассортимента обуви магазина «Велес» на основе АВС-XYZ-анализа // Экономика и менеджмент инновационных технологий. 2015. № 3 (42). С. 68-80.
9. Сивко А.И., Кириченко И.Н., Оксанич О.В. Определение коэффициента сезонности для лекарственных средств разных фармакотерапевтических групп // Труды Одесского политехнического университета. 2006. №1. С. 291-295.
10. Демченко Л.И., Лапина Е.В. Корректировка ассортиментной политики торговой фирмы с помощью матрицы совмещенного АВС- и XYZ – анализа // Вестник мясного скотоводства. 2009. Т. 3. № 62. С. 64-70.
11. Лисовский П.А. Формирование ассортимента аптечной сети методом каскадного использования АВС- и XYZ- анализа // Вестник ИНЖЭКОНа. Серия: Экономика. 2009. Т. 29. № 2. С. 260-264.
12. Buliński J., Waszkiewicz C., Buraczewski P. Utilization of ABC/XYZ analysis in stock planning in the enterprise //Annals of Warsaw University of Life Sciences–SGGW. 2013. №. 61. С. 89-96.
13. Scholz-Reiter B., Heger J., Meinecke C., Bergmann J. Integration of demand forecasts in ABC-XYZ analysis: practical investigation at an industrial company //International Journal of Productivity and Performance Management. 2012. Т. 61. №. 4. С. 445-451.
14. ABC-анализ / Википедия. URL: https://ru.wikipedia.org/wiki/ABC-анализ (дата обращения 07.05.2015)
15. XYZ-анализ / Википедия. URL: https://ru.wikipedia.org/wiki/XYZ-анализ (дата обращения 07.05.2015)

Анализ складских запасов ABC/XYZ: зачем нужен

Торговая организация из месяца в месяц закупает примерно одни и те же товары. Одни продаются слету. Другие пылятся на полках витрин и заполоняют складские помещения.

Производственное предприятие сменило техпроцесс. Из-за этого потребность в одном из материалов сократилась в разы. Но снабжение им продолжается в прежнем объеме.

И тот, и другой пример говорят о неэффективных контроле и управлении запасами. Кажется, это же очевидно:

• не покупай то, что плохо продается;
• не бери то, в чем нет надобности.

Все верно. Да только, когда ассортимент товаров и материалов исчисляется сотнями или даже тысячами, то отследить подобные вещи бывает непросто. На помощь приходит комплексный инструмент – анализ АВС/XYZ. Его несомненное преимущество – несложность сбора информационной базы и простота проведения. Причем применение Excel облегчает задачу в разы. А вот результаты, которых можно достичь, если сочетать расчеты с последующими управленческими решениями на их основе:

• выявить запасы-лидеры в обращении и те, что имеют тенденцию пролеживать на складах. В сочетании с их вкладом в доходы/прибыль понять, как с последними работать дальше;
• избавиться от явных неликвидов и тем самым оптимизировать складские затраты, а еще вернуть в оборот деньги от их продажи пусть даже по символической цене;
• формализовать внутренний контроль в сфере запасов за счет выделения девяти групп. Для каждой из них применить свой подход с точки зрения закупок и стимулирования сбыта/потребления.

Чтобы провести анализ, потребуются:

• для товаров – отчет о продажах;
• для материалов – о списании в производство.

Вот еще несколько советов по исходной информации:

• отчеты нужны в разрезе номенклатурных позиций;
• хорошо, чтобы кроме рублевых значений были данные еще и в натуральных величинах. Это повысит точность результата для XYZ-методики;
• для XYZ понадобятся данные за несколько периодов. Например, за год, но в помесячном разрезе или поквартальном. Чем короче период и чем их больше по количеству, тем надежнее итог вычислений;
• отличной заменой специальных отчетов о продажах и списании в производство являются карточки счета из программы 1С. Для товаров по 41-му, для материалов по 10-му. Сформируйте их с развернутой аналитикой и смотрите в кредитовый оборот. Именно там отражается выбытие ценностей.

АВС/XYZ-анализ реализуется в три этапа. Сначала последовательно применяется каждый метод – АВС и XYZ. Затем полученные результаты сводятся воедино и становятся основой для принятия управленческих решений.

Остановимся на каждом этапе. Заранее условимся: описывать их будем применительно к товарным группам и процессу продажи. Материалы и списание в производство оставим в стороне. По ним схема расчетов идентична, а выводы во многом схожи.

Суть АВС-анализа

Возьмём магазин канцтоваров. Чтобы не усложнять, ограничим ассортимент 10 позициями.

Получаем вот такую таблицу.

Ассортимент магазина канцтоваров для АВС-анализа

Товар	Прибыль, рублей
Авторучки	150 000
Маркеры	200 000
Тетради в линейку	50 000
Тетради в клетку	45 000
Общие тетради	30 000
Альбомы для рисования, А4	15 000
Блокноты	20 000
Нотные тетради	5 000
Дневники	3 000
Пеналы	10 000

Дальше мы действуем в такой последовательности:

1. Сортируем товары и прибыль, которую они нам приносят, по убыванию. Вручную это делать не надо — умная электронная табличка сама справится.
2. Вычисляем долю каждого товара в общей прибыли бизнеса — это графа 3 «Доля в общей прибыли» в табличке ниже.
3. А теперь самое интересное: от товара к товару считаем их суммарную долю в прибыли нарастающим итогом. Доля маркеров, которые заняли первое место по приносимой прибыли, — 33,78%. На втором месте — авторучки с 28,41% прибыли. Вместе эти два товара приносят бизнесу 66,29% прибыли. И так далее. Спросите — зачем, ведь и так известно, что в итоге получится 100%? А в том-то и дело, что нас интересуют промежуточные значения. Ведь мы хотим знать, какие именно товары формируют 80% прибыли и какова роль остальных. Ответ — в таблице, которая у нас получилась. Долю отдельных товаров мы видим в третьей колонке. Но сама по себе она ещё ни о чём не говорит. Мы же сортируем товары по группам по их совокупной доле прибыли. Вот эту совокупную долю умная табличка и считает в 4-й колонке «Суммарная доля».
4. Ну и последнее — сортируем товары по группам. Всё, что в сумме меньше или равно 80%, — группа А. Это главные «кормильцы» бизнеса. Как только мы достигли порога в 80%, первый товар, суммарная доля прибыли с участием которого превышает 80%, относится уже к группе В. В нашем примере это тетради в клетку. Они увеличивают суммарную долю общей прибыли компании с 75,76% до 84,28%. Когда очередной товар увеличит общую долю прибыли до 95% и более процентов, это уже первый товар из группы С. В нашем примере это альбомы для рисования — после них суммарная доля прибыли увеличивается до 96,59%. Всё, что осталось, — это тоже группа С.

Так выглядит результат АВС-анализа. Товары распределились по трём группам: лидеры — А, середнячки — В, аутсайдеры — С

Как видите, 75,76% прибыли магазин делает на маркерах, авторучках и тетрадях в линейку. Тетради в клетку, общие тетради и блокноты приносят бизнесу 17,99% прибыли. Оставшиеся четыре позиции — 6,25%.

В классическом варианте АВС-анализа соотношение между группами А, В и С — 80/15/5. 20% прибыли, которые бизнес, согласно принципу Парето, получает от 80% товаров, в АВС-анализе дополнительно детализированы — 15/5.

У нас получилось иное соотношение — 75,76/17,99/6,25. Ничего страшного. Реалии бизнеса не всегда вписываются в классику. Главное — чтобы общая сумма была 100%. Это самопроверка.

А + В + С = 100%.

В классическом варианте: А = 80%, В = 15%, С = 5%. А/B/C = 80/15/5.

В нашем примере: А = 75,76%, В = 17,99%, С = 6,25%.

75,76% + 17,99% + 6,25% = 100%. Значит, всё правильно.

Ценные бумаги

Легкий вариант

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

Цель: сделать xyz-анализ степени доходности и степени риска ценных бумаг каждой компании и прийти к выводу о покупке.

Для начала возьмем и сопоставим две компании. Имеются следующие данные по доходности ценных бумаг на последние 10 лет.

Год	2010	2011	2012	2013	2014	2015	2016	2017	2018	2019
Комп 1	5,65 %	8,74 %	6,70 %	4,38 %	-2,73 %	-3,14 %	1,35 %	3,94 %	2,26 %	3,84 %
Комп 2	11,62 %	9,95 %	10,37 %	5,14 %	-3,02 %	-5,36 %	-1,19 %	1,73 %	5,83 %	6,38 %

Как видно, на обе компании негативно повлиял экономический кризис 2014-2015 года. 

Первое действие – найти среднее арифметическое. С помощью него мы также определим среднюю доходность ценных бумаг обеих фирмы.

• Складываем данные по первой и делим на количество лет: 30,99 / 10 = 3,099
• Делаем то же со второй: 41,45 / 10 = 4,14

Средний ожидаемый доход от второй больше на 25 %.

Второе действие – посчитать среднеквадратичное отклонение по формуле выше:

• Компания № 1. (5,65 — 3,099)2 + (8,74 — 3,099)2 + (6,70 — 3,099)2 + (4,38 — 3,099)2 + (-2,73 — 3,099)2 + (-3,14 — 3,099)2 + (1,35 — 3,099)2 + (3,94 — 3,099)2 + (2,26 — 3,099)2 + (3,84 — 3,099)2 / 10 =13,08. Извлекаем корень из числа: 3,61.
• Компания № 2. (11,62 — 4,14)2 + (9,95 — 4,14)2 + (10,37 — 4,14)2 + (5,14 — 4,14)2 + (-3,02 — 4,14)2 + (-5,36 — 4,14)2 + (-1,19 — 4,14)2 + (1,73 — 4,14)2 + (5,83 — 4,14)2 + (6,38 — 4,14)2 /10 = 55,95 + 33,75 + 38,81 + 1 + 51,26 + 90,25 + 28,4 + 5,8 + 1,69 + 5,01 / 10 = 311,92 / 10 = 31,19. Извлекаем корень из числа: 5,58.

Третье действие: снова делим получившиеся числа на соответствующие средние арифметические: 

• Компания № 1. 3,61 / 3,099 = 1,16.
• Компания № 2. 5,58 / 4,14 = 1,34.

Как видим, колебание второй больше, что является явным минусом: доход становится более непредсказуемым.

Затем таким же образом рассчитываются данные по остальным 18-ти компаниям. Когда работа закончена, нужно будет расположить все 20 коэффициентов вариаций по возрастающей. А затем – разделить их на 3 категории:

• Первые 20 % (то есть 4 компании) – Категория X.
• Следующие 60 % – Категория Y.
• Последние 20 % – Категория Z.

Затем следует дополнить xyz-анализ abc-анализом. А именно: взять среднее арифметическое каждой из 20 компаний и расположить их по убывающей:

• Категория A: 20 %.
• Категория B: 60 %.
• Категория C: 20 %.

Выбирать для инвестирования нужно ту компанию, которая попала одновременно в «X», «A».

Усложненный вариант

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

Цель: провести xyz-анализ и выбрать лучший из трех сценариев.

Вот данные. Каждый столбик – один сценарий по каждой фирме и вероятность его исполнения.

Сценарий	1	2	3
Вероятность	0,45	0,15	0,40
Компания № 1	2 %	1 %	4 %
Компания № 2	6 %	1 %	7 %

Первое действие такое же: найти среднее арифметическое:

• Компания № 1. 2 — 1 + 4 / 3 = 1,66.
• Компания № 2. 6 — 1 + 7 / 3 = 4.

Для второго действия есть более сложная формула:

Здесь всего два новых элемента

• M (X) вместо x̅. Это – математическое ожидание (M) случайной величины (X). Для его расчета нужно будет совершить пару действий. 
• pi – вероятность исхода. Дана в таблице. Обратите, что сумма всех трех вероятностей равна 1.

Сначала рассчитаем M (X) для ценных бумаг каждой компании. Для этого нужно каждый прогноз (2 %, — 1 %…) умножить на его вероятность (0,45, 0,15…), а затем сложить все показатели.

• Компания № 1. 2 * 0,45 — 1*0,15 + 4 * 0,4 = 2,35.
• Компания № 2. 6 * 0,45 — 1 * 0,15 + 7 * 0,4 = 5,35.

Теперь можно вставлять результаты в основную формулу:

• Компания № 1. (2 — 2,35)2 * 0,45 + (-1 — 2,35)2 * 0,15 + (4 — 2,35)2 * 0,4 = 0,05 + 1,68 + 1,08 = 2,81. Не забываем, что то число находится в корне. Извлечем его: 1,67.
• Компания № 2. (6 — 5,35)2 * 0,45 + (-1 — 5,35)2 * 0,15 + (7 — 5,35)2 * 0,4 = 0,42 + 6,04 + 1,08 = 7,54. Извлечем корень: 2,74.

Третье действие: выявляем коэффициент вариации:

• Компания № 1. 1,67 / 1,66 = 1.
• Компания № 2. 2,74 / 4 = 0,68.

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

ABC-анализ для других сфер

Разберем еще два примера.

Поставщики

Строительство воздухоопорных сооружений требует покупки разных стройматериалов: оболочки для купола, металлических креплений, анкерной системы, напорно-обогревательных установок. Фирма, которая этим занимается, вынуждена иметь дело с разными поставщиками.

Последняя сделка едва ли не вывела ее счета в минус, так как оборудование и стройматериалы стоили слишком дорого. 

Было предложено провести ABC-анализ поставщиков – перечислить их. Откроет список тот, которому компания за последний год заплатила больше всего. Параллельно будет составлен другой список. Первым в нем будет тот, кто за тот же период поставлял компании больше всего оборудования. 

Затем оба списка делят по принципу 20/60/20, который описан выше.

Результаты показали, что многие поставщики, на которых ушло много денег, на самом деле предоставили не так уж много стройматериалов. И основная причина – их территориальная удаленность, которая вынуждает больше тратить на доставку. Это стало толчком для кардинальных изменений в политике фирмы.

Сотрудники

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

Предыдущий РОП оценивал разговоры менеджеров через CRM-систему и выставлял баллы, которые влияли на финальную зарплату. Было решено, что те сотрудники, которые получали меньше всего штрафов за ошибки, должны претендовать на должность РОПа.

Чтобы определить их, был применен ABC-анализ – 20 % работников с наименьшими штрафами возглавили общий список. Это 4 человека. 

Однако тот, кто ошибался меньше всего, не обладал при этом лидерскими качествами. Зато третий по счету в списке не раз проявлял твердость воли и инициативность на собраниях и решениях корпоративных проблем. Было решено назначит его.

Примеры можно продолжать. ABC-анализ гибко подстроится под любые условия и любую нишу. А как вы хотели бы использовать его?

Подводный камень XYZ-анализа

При всем удобстве XYZ-анализа у него есть огромный недостаток. Коэффициент вариации сильно зависит от сезонности спроса. И это снижает точность прогнозирования.

Мы не случайно выбрали для примера канцелярку. Спрос на этот товар зависит от сезона: продажи активизируются в августе, в преддверии учебного года. А с началом летних каникул в июне — спад.

Поэтому если мы исключим из анализа статистику продаж по июню, получим совсем другие результаты.

Результат XYZ-анализа без учета сезонного падения спроса в июне — распределение товаров между группами отличается от варианта с учетом июньской статистики продаж. Разнятся и коэффициенты вариации одних и тех же товаров.

Чтобы прогноз был максимально точным, надо разбить период XYZ-анализа на сезоны. И каждый сезон анализировать отдельно. А свои высокие и низкие сезоны бизнесмены знают.

ABC-анализ для сегментирования покупателей

Суть

Цель – разбить клиентов на три группы в зависимости от того, кто принес компании больше денег.

Сначала идет составление списка покупателей, который будет начинаться самым прибыльным и заканчиваться самым невыгодным. Допустим, в нем 432 наименования. Тогда мы делим 432 на 100 и получаем 1% покупателей: 4,32

Затем умножаем 4,32 на 20%, и получается 86 или 87 клиентов (не столь важно, в какую сторону округлять)

Эти 86 человек – 20 % самых прибыльных клиентов. Первые в списке тех, кто приносит компании больше всего денег. Это – Категория A.

Затем 4,32 умножаем на 60 % и получаем 259 / 260. Это – те, кто принес среднее количество денег – Категория B. 

Последнее действие: снова умножить 4,32 на 20 %. В Категорию C также войдут 86 / 87 человек, но это будут самые невыгодные покупатели.

Пример. Фирма доставки третий месяц терпит убытки. Руководство уже давно определило 20–30 самых выгодных клиентов и пытается ориентироваться на них. Однако остальная клиентская база не проанализирована. В ней выделились те, кто часто обращается и заказывает доставку разных типов стройматериалов – однако в целом не приносит много денег.

Заметив это, руководство решило провести ABC анализ. Выяснилось, что самые «заметные» покупатели были в конце «B» и в начале «C». Менее заметные уже в первую покупку принести компании немалую сумму – но остались незамеченными, поэтому поднять up-sales и cross-sales с помощью них не удалось. 

Было принято решение менять стратегию работы с каждой отдельной Категорией.

ABC: работа с покупателями

Начинается работа с Категории А, так как к ней принадлежат обычно самые лояльные компании люди, терпимые к ее ошибкам и готовые протестировать новый продукт:

• среди них выделяются те, кто лучше всего подойдет для скорейшего увеличения продаж. Подтолкнуть их к новой стадии сотрудничества поможет скидка, акция, подарок;
• некоторые из них с каждым разом покупают все меньше – хотя продолжают оставаться в топе. Нельзя допустить, чтобы они перешли в Категорию B.

Что касается ее, то нужно выделить тех, кто ближе всего к переходу в «A», и работать прежде всего с ними. В «C» очень много мертвых клиентов. Однако менеджеры продолжают им звонить, и в итоге расходов на них больше, чем доходов.

Пример. Руководство фирмы доставки перебросило самых лучших менеджеров на обслуживание Категории A. В «B» были предложены скидки самым многообещающим клиентам, хотя уже вскоре стало очевидно, что здесь не обойтись без капитальных улучшений работы компании.

Усложнение анализа: XYZ

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

Пользуемся им так же, как и ABC:

• 20 % самых активных – Категория «X»;
• 60 % людей со средней активностью – «Y»;
• и последние 20% пассивных – «Z».

Причем теперь можно группировать покупателей еще продуманнее: выделять Категории AX (больше всего и чаще всего покупали), «AZ» (крупные, но редкие сделки), «CY» (мелкие, но частотные покупки) и т. д.

Пример. В фирме не понимали, почему некоторые люди из Категории B почти не воспринимали скидки, обещанные при крупном заказе. Выяснилось, что многие из них предпочитали небольшие, но частые заказы – то есть относились к Категории BX. Следственно, нужны были другие механизмы. 

Было принято решение предоставить 15 % скидку при повторном заказе в том же месяце – чтобы увеличить частотность. Прежде всего эту скидку рекламировали «AY», «AZ», «BZ». А вот «CY», «CZ» причислили к «мертвым клиентам», на которые не стоит тратить сил.

Как видим, основные достоинства ABC-анализа – простота и широкие возможности для применения результатов на практике. 

Определение понятия «XYZ-анализ»

Эта разновидность рейтингового анализа помогает систематизировать продукт, основываясь на усредненном показателе стабильности реализации и отклонениях от него.

Процессуально, определяются коэффициенты варьирования для анализируемых признаков, характеризуется соотношение фактического значения и среднеарифметического (эталонного) за рассматриваемый период.

XYZ-анализ позволяет вычислить и рассчитать разницу в значениях опытных объектов (товарный портфель, клиентская база и др.) за рассматриваемые временные периоды, их отклонения от «эталонного» показателя.

В результате, возможно сделать заключение о динамике (продаж, например), оценить стабильность процесса, выявить наличие каких-либо отклонений, произвести систематизацию.

Последовательность проведения XYZ-анализа

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

1. Высчитывается коэффициент вариации. Посчитать возможно несколькими способами. Первый – математически, вручную. По ряду данных (например, «Картофель») высчитывается среднее арифметическое значение. В нашем случае, несложно посчитать значение по данному продукту, равное 247. Дальше, находится величина отклонения ежемесячного значения реализации от «эталонного» 247, в процентах. Например, для января отклонение от эталона составит: 1-(240/247) = 2,83%. Также высчитывается значение для остальных месяцев. Среднее арифметическое коэффициентов отклонения и есть искомая величина итогового коэффициента вариации по продукту (например, «Картофель», 2,97% по полугодию).

1. Программно, в «Экселе», эти действия производятся с помощью функций. Для вычисления «эталонного» значения по ряду данных применяем функцию СТАНДОТКЛОНП. Синтаксис приведён на графическом фрагменте решения данной задачи, ниже. Таким же способом считаем коэффициент вариации для других продуктов.

1. Полученные коэффициенты вариаций ранжируются от меньшего значения к большему. В группе X находятся объекты с коэффициентом вариации от 0% до 10% — стабильные продажи, возможно более адекватно спрогнозировать их реализацию. Если отклонение составит от 10% до 25 % — это группа Y. Реализацию продуктов этой группы сложнее спрогнозировать, но этот параметр показывает некоторые отклонения (например, сезонность продукта). Группа Z характеризуется нерегулярным потреблением, отсутствием каких-либо тенденций и показывает коэффициент вариации более 25 %. Синтаксис эксель-формулы приведён в примере ниже:

В практике, продажи и доходность испытывают на себе воздействия многих факторов: сезонность, частоту цикла поставок, изменения ценообразования, маркетинговые активности, прочие факторы. Все это, с различными последствиями, резонирует в результатах продаж, приводя к высокой волатильности коэффициента вариации. Данным XYZ-анализа можно доверять в случае, если рассматриваемый период времени достаточно продолжительный. Величина оборачиваемости товара (в днях) должна быть существенно меньше (в разы) длительности анализируемого временного отрезка.

Разграничение товаров по группам

АВС-анализ строится на базе принципа «Правило Парето», чей девиз гласит: «20% усилий приносит 80% результатов».

Классификация продуктов происходит по трем группам, именуемым «А», «В» и «С». Где:

• На продукты группы «А» приходится 80% продаж, и приносится столько же прибыли от общего объема. Как правило, это 20% от общего числа ресурсов.
• На продукты группы «В» приходится 15% продаж. Затрачивается около 35% ресурсов.
• Сектор «С» – это 5% продаж с потреблением до 60% ресурсов.

Что именно ставить в основу анализа (валовые показатели продаж или прибыль) – решать руководителю.

Сектор «А» – это главные ресурсы, «авангард» компании, которые приносят максимум прибыли. Если продажи данной категории товаров упадут, фирма понесет серьезные потери. Этому сегменту нужно уделять максимум внимания и контроля. Для него проводятся прогнозы и мониторинг. Основная часть инвестиций направляется на развитие именно этого сектора.

Группа «В» обеспечивает стабильные продажи, поэтому также важна для фирмы. Рост прибыли от этой категории может и не наблюдаться, но зато доход стабильный. Перспективность характеризуется краткосрочной тенденцией. Инвестиции необходимы только для поддержания сектора на плаву, но не для развития.

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

Пример проведенного АВС-анализа

А теперь рассмотрим пример ABC-анализа продаж, как его можно проводить в таблице Excel.

При составлении таблицы можно вводить данные о сырье или продуктах. В роли показателя могут выступать объемы прибыли, продаж или инвестиций.

Составьте общий список товаров, выставив выбранный показатель:

Порядковый номер	Наименование	Показатель (руб.)
1	Продукт 1	100
2	Продукт 2	150
3	Продукт 3	200
4	Продукт 4	50
		530

Выполните сортировку позиций по уровню убывания продаж:

Порядковый номер	Наименование	Показатель (руб.)
1	Продукт 1	200
2	Продукт 2	150
3	Продукт 3	100
4	Продукт 4	50
		530

Затем выполните процесс деления каждой из позиции на процент приходящихся на нее продаж:

Порядковый номер	Наименование	Показатель (руб.)	Вклад в %
1	Продукт 1	200	40%
2	Продукт 2	150	30%
3	Продукт 3	100	20%
4	Продукт 4	50	10%
		500	100%

В следующем столбце посчитайте совокупный процент, начиная отсчет с верхних позиций:

Порядковый номер	Наименование	Показатель (руб.)	Вклад в %	Совокупный %
1	Продукт 1	200	40%	40%
2	Продукт 2	150	30%	70%
3	Продукт 3	100	20%	90%
4	Продукт 4	50	10%	100%
		500	100%

В завершение, каждой из позиции присвойте группу, основываясь на данных столбца с совокупным процентом:

• На позиции с самого начала до уровня 80% – группа «А».
• С 80% до уровня 95% – группа «В».
• С 95% до 100% – группа «С».

Порядковый номер	Наименование	Показатель (руб.)	Вклад в %	Совокупный %	Категория
1	Продукт 1	200	40%	40%	«А»
2	Продукт 2	150	30%	70%	«А»
3	Продукт 3	100	20%	90%	«В»
4	Продукт 4	50	10%	100%	«С»
		500	100%

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