ABC XYZ анализ — удобный метод оценки эффективности работы «жизненно важных» отделов компании: продаж, маркетинга, склада, финансов. Представляем подробную инструкцию, как выполнить ABC и XYZ-анализ в Excel.
С общими принципами проведения ABC и XYZ-анализа можно ознакомиться здесь. А ниже — пошаговая инструкция, как сделать ABC-анализ в Excel.
Содержание:
4 вопроса до начала ABC-анализа
- Цель. Зачем вы проводите исследование? Увеличить выручку компании, исключить возможность упущенной выгоды и т.п.
- Результат. Как вы сможете применить полученные значения? Оптимизируем складские запасы, пересмотрим условия договоров и т.п.
- Источники данных. Как вы соберете исходные данные: объект и параметр анализа? Объект анализа — перечень товаров, параметр — выручка в количественном и денежном выражении.
- Матрица. Какое АВС XYZ процентное распределение закладывать в расчет? Классический вариант на основе принципа Парето: 80% приносят выручки приносят 20% ключевых клиентов. Чтобы назначить распределение по группам, нужно знать специфику работы компании, жизненные циклы и сезональность. Ошибки в матрице могут привести к тому, что в неприбыльной группе С окажутся важные покупатели с редкими закупками.
ABC-анализ в Excel: пошаговая инструкция, рабочие образцы с формулами
Ассортиментный ABC анализ проведем на примере компании по продаже запасный частей для сельскохозяйственной техники.
Количество товара — более 5 000 позиций. Объединяем их в группы по видам номенклатуры.
Из учетной системы выгружаем данные за 2020 год:
- количество продаж с разбивкой по кварталам;
- цена реализации за единицу;
- выручка итого за год в рублях. Важно использовать одну валюту для всего отчета, чтобы исключить влияние курсовых разниц.
Сортировка выручки по убыванию
Выделяем диапазон ячеек: вся таблица вместе с заголовками без строки «Итого».
В ниспадающем меню выбираем:
Данные — Сортировка — Сортировать по:
- столбец «Выручка»
- сортировка «Значения»
- порядок «По убыванию»
Нажимаем «Ок».
Система выстраивает таблицу по убыванию размера выручки в столбце D.
Доля каждой строки в общем параметре
Определяем долю каждой номенклатуры в выручке:
- добавляем графу Доля (Е). Формат ячеек процентный;
- в строку 2 для товара 6 вводим формулу: выручка товара 6 / выручка итого;
- протягиваем формулу вниз по всем товарам.
Добавляем графу F и рассчитываем Долю накопительным итогом: складываем текущее значение со всеми предыдущими.
Символ & предупреждает Excel, что формулу нельзя двигать:
- & перед буквой — по столбцам;
- & перед цифрой — по строкам.
Перед тем как создавать ABC-таблицу проверьте долю каждого товара в общем значении (выручки, запасах, себестоимости и пр.). Проводить ABC аналитику бессмысленно, если объект распределяется примерно в равных долях. Каждый показатель вносит одинаковый вклад в результат.
Определяем группу
Создаем графу Группа. Каждому товару присваиваем значения А, В, С в зависимости от доли в выручке.
Руководство утвердило матрицу:
| Группа | Диапазон |
|---|---|
| A | до 70% |
| B | 70-90% |
| C | 90-100% |
В ячейке G2 прописываем формулу =ЕСЛИ(F2<=70%;"A";ЕСЛИ(F2>=90%;"C";"В")). Протягиваем формулу вниз по всем товарам.
В примере для наглядности проценты заданы цифрами.
В рабочем файле Excel вместо процентов ссылки на ячейки со значениями матрицы. При изменении параметров матрицы формула будет автоматически пересчитываться по всем товарам.
В столбце G каждой номенклатурной группе присвоен код А, В, С.
В группу А попали товары, которые приносят основную прибыль.
В группу В — продукция компании, на которую нерегулярный спрос.
Группа С — товары, которые зарабатывают только 10% от выручки.
XYZ-анализ в Excel: оценка динамики продаж
XYZ исследование позволит увидеть изменения спроса на продукцию компании.
Выгружаем данные из учётной системы
Создаем таблицу с количеством продаж за 2020 год по каждой товарной группе по каждому кварталу.
Рассчитываем коэффициент вариации
Вариация — степень разброса значений в числовой последовательности. Показывает насколько данные отклоняются от средних показателей. В финансах этот коэффициент оценивает изменчивость, волатильность, сезональнальность. Чем он меньше, тем стабильнее оцениваемый параметр (спрос на товар, движение по складу, платежи и т.д.).
Создаем графу Средние продажи. В строку 3 вводим формулу =СРЗНАЧ(B3:E3) и копируем ее для всех товарных позиций.
Создаем графу Стандартное отклонение. Стандартное отклонение / Средние продажи.
В строку 3 вводим формулу =СТАНДОТКЛОН(B3:E3) и копируем ее для всех товарных позиций.
Создаем графу Вариация, %. Вводим формулу:
Столбец Стандартное отклонение / Столбец Средние продажи
XYZ-анализ в Excel: формула расчёта коэффициента вариации 
XYZ-анализ в Excel: рассчитанный коэффициент вариации
Присваиваем значения XZY и соединяем с ABC
Руководство утвердило матрицу XYZ аналитики:
| Группа | Диапазон |
|---|---|
| X — постоянный спрос | до 15% |
| Y — изменчивый спрос, сезональность | от 15% до 50% |
| Z — случайный спрос | больше 50% |
Ранжируем полученные результаты с помощью функции Excel «ЕСЛИ».
В ячейку J3 вводим формулу: =ЕСЛИ(I3<=15%;"X";ЕСЛИ(I3>=50%;"Z";"Y")). Копируем формулу по всем товарным срокам.
XYZ-анализ в Excel: группы товаров по методу XYZ — формула 
XYZ-анализ в Excel: группы товаров по методу XYZ — результат
Создаем графу Группа по методу ABC. Подтягиваем код группы из таблицы ABC анализа с помощью формулы: =ВПР(A3;ABC!$A$1:$G$12;7;0)
Как настроить формулу ВПР:
Задача функции: по коду товара в исходной таблице найти значение А, В или С и перенести его отчётную таблицу XYZ.
А3 — параметр, по которому ищем значение, например «Товар 6».
ABC!$A$1:$G$12 — ссылка на диапазон исходной таблицы. В ней строго в первом столбце должен быть параметр, по которому ищем значение «Товар 6».
7 — порядковый номер столбца, в котором в исходной находятся значения (коды А, В, С)
0 — значение ЛОЖЬ. Для Ecxel признак того, что искомый результат должен соответствовать всем 3-м предыдущим условиям.
По каждому товару получаем двойную кодировку ABC и XYZ аналитики.
Для наглядности можно скрепить лва кода по каждому товару.
В столбец L для каждой строки вводим формулу =K&J.
Товары AX — высокоприбыльные позиции, которые формируют 70% выручки. На них стабильный спрос.
Товары CZ — позиции с самым низким спросом. Сюда могут попасть как неликвиды, так и элитные товары с редким спросом. Требуется дополнительная аналитика.
Подробнее о сути, эффективности и недостатках ABC XYZ анализа читайте здесь.
