Что показывает ABC-анализ ассортимента
ABC-анализ — это ранжирование товаров по их доле в результате с делением на три группы. В основе лежит принцип Парето: небольшая часть ассортимента приносит большую часть денег. Группа A — артикулы, которые вместе дают первые 80% выручки, B — следующие 15%, C — последние 5%. Границы можно сдвигать, но их нужно зафиксировать и не менять от отчёта к отчёту.
Перед расчётом решите два вопроса. Первый — по какому показателю ранжировать. Выручка показывает оборот, маржа — заработок, количество в штуках — нагрузку на склад и логистику. Для решений по ассортименту в одежде я обычно считаю ABC по марже и сверяю с ABC по выручке: товар, который попал в A по выручке и в C по марже, продаётся в основном со скидкой. Второй вопрос — уровень детализации. В fashion-рознице разумно считать по модели-цвету, а не по отдельному размеру, иначе размерная сетка раздробит спрос.
Как сделать ABC-анализ в Excel по шагам
Для расчёта не нужны надстройки или сложные модели. Достаточно выгрузки продаж из учётной системы и нескольких стандартных функций. Перед началом уберите из выгрузки возвраты, служебные артикулы, подарочные карты и упаковку: они искажают доли и не относятся к ассортименту.
- Выгрузите продажи за период в таблицу: артикул в столбце A, выручка или маржа в столбце B. Период берите не короче сезона.
- Отсортируйте таблицу по столбцу B по убыванию.
- В столбце C посчитайте долю каждого артикула в итоге.
- В столбце D посчитайте накопленную долю: сумму долей от первой строки до текущей.
- В столбце E присвойте группу по накопленной доле: до 80% — A, до 95% — B, остальное — C.
- Постройте сводную таблицу: число артикулов и выручка по группам. Это и есть результат анализа.
C2 =B2/СУММ($B$2:$B$101)
D2 =СУММ($C$2:C2)
E2 =ЕСЛИ(D2<=0,8;"A";ЕСЛИ(D2<=0,95;"B";"C"))Формулы протягивают вниз до последней строки. Если артикулов много и список меняется, удобнее оформить диапазон как умную таблицу: ссылки будут расширяться сами. Ниже — условный пример для десяти артикулов женской и мужской одежды.
| Артикул | Выручка | Доля | Накопленная доля | Группа |
|---|---|---|---|---|
| Футболка базовая белая | 240 | 24% | 24% | A |
| Джинсы прямые синие | 190 | 19% | 43% | A |
| Худи серое | 150 | 15% | 58% | A |
| Футболка базовая чёрная | 120 | 12% | 70% | A |
| Рубашка оксфорд | 100 | 10% | 80% | A |
| Платье миди | 70 | 7% | 87% | B |
| Кардиган | 50 | 5% | 92% | B |
| Юбка плиссе | 30 | 3% | 95% | B |
| Жилет стёганый | 30 | 3% | 98% | C |
| Шарф | 20 | 2% | 100% | C |
В примере пять артикулов из десяти дают 80% выручки, и все они относятся к базовому ассортименту. Для закупщика это сигнал: именно по этим моделям нельзя допускать пустых размеров. Жилет и шарф попали в группу C, но это ещё не повод их выводить: сначала стоит проверить, сколько недель они были в продаже и были ли в наличии.
XYZ-анализ и стабильность спроса
XYZ-анализ отвечает на другой вопрос: насколько предсказуемо продаётся товар. Для этого берут продажи по периодам — неделям или месяцам — и считают коэффициент вариации, то есть отношение стандартного отклонения к среднему значению.
Чем меньше коэффициент, тем ровнее спрос. Распространённые границы групп: X — до 10%, Y — от 10 до 25%, Z — выше 25%. Для одежды продажи колеблются сильнее, чем для продуктов питания, поэтому на практике границы часто расширяют. Выберите их по своим данным так, чтобы в группе X оказался действительно стабильный базовый ассортимент.
Условный пример: белая футболка продаётся по 90, 100 и 110 штук в месяц при среднем 100. Стандартное отклонение около 8, коэффициент вариации около 8% — группа X. Худи, которое летом продаётся по 20 штук, а зимой по 180, при том же среднем 100 получит коэффициент 80% и попадёт в Z, хотя его спрос вполне предсказуем по сезону.
R2 =СТАНДОТКЛОНП(F2:Q2)/СРЗНАЧ(F2:Q2)
S2 =ЕСЛИ(R2<=0,1;"X";ЕСЛИ(R2<=0,25;"Y";"Z"))Считайте XYZ в штуках, а не в деньгах: скидки и изменения цен добавляют к выручке колебания, которые не связаны со спросом. Месяцы, когда товара не было в наличии, исключите из расчёта, иначе нулевые продажи из-за дефицита попадут в «нестабильный спрос».
Матрица ABC-XYZ для решений по ассортименту
Когда у каждого артикула есть обе буквы, их объединяют в матрицу из девяти ячеек. В Excel это одна формула сцепления, например =E2&S2, и сводная таблица по полученным кодам. Дальше для каждой ячейки нужно правило, которое команда применяет без споров.
| Группа | X — стабильный спрос | Y — колебания | Z — нерегулярный спрос |
|---|---|---|---|
| A | Не допускать дефицита, регулярное автопополнение | Страховой запас, еженедельный контроль | Заказ небольшими партиями, частый пересмотр прогноза |
| B | Плановое пополнение по графику | Пополнение с поправкой на сезон | Заказ под подтверждённый спрос |
| C | Минимальная глубина, проверить цену | Сократить число цветов и размеров | Кандидаты на вывод или распродажу |
Самые важные ячейки — AX и CZ. AX — основа оборота, и каждый день без остатка стоит денег. CZ — товары, которые занимают место и бюджет закупок, почти ничего не принося. Ячейку AZ стоит разобрать отдельно: часто это хиты, которые продавались рывками из-за перебоев с поставками.
Пересчитывать матрицу имеет смысл раз в сезон для решений о закупке и раз в месяц для решений о пополнении базового ассортимента. Сохраняйте результаты прошлых расчётов: переход артикула из A в B за два сезона подряд говорит о падении спроса больше, чем одна текущая буква.
Чего нельзя сделать с помощью ABC-XYZ-анализа
Метод пришёл из управления запасами, где ассортимент почти не меняется. В fashion-рознице у него есть заметные ограничения, и о них стоит помнить до того, как выводить товары из матрицы.
- Сезонность. Пуховик продаётся несколько месяцев в году, поэтому годовой XYZ всегда отправит его в Z. Сезонные категории анализируйте внутри сезона или сравнивайте с тем же сезоном прошлого года.
- Новинки. Товар, который продаётся шесть недель, нельзя сравнивать с базовой моделью, которая лежит на полке год. Для новинок смотрите sell-through и продажи в неделю, а не место в ABC.
- Дефицит. Если товара не было в размере или в магазине, низкие продажи отражают наличие, а не спрос.
- Скидки. Выручка при уценке растёт в штуках и падает в марже. ABC по выручке без ABC по марже может сохранить в ассортименте убыточный товар.
- Роль товара. Некоторые артикулы из группы C нужны для образа или ценовой линейки: без них хуже продаются товары из группы A.
Поэтому ABC-XYZ — хорошая отправная точка для разговора о матрице, но не автоматическое решение. Я использую её как фильтр: она показывает, какие двадцать артикулов стоит обсудить на встрече, а решение принимают с учётом сезона, наличия и роли товара.