ABC XYZ анализ: что это простыми словами, пример на 20 товарах и расчёт в Excel

«У меня всего двадцать позиций. Зачем мне какой-то ABC XYZ анализ? Я и так знаю, что продаётся». Так говорит Гульнара, владелица небольшого интернет-магазина кофе и чая в Казани. Персонаж вымышленный. А вот ситуация знакомая многим.

В январе у Гульнары закончился бразильский кофе в зёрнах — тот самый, который покупают каждую неделю одни и те же люди. Поставщик привёз новую партию через девять дней. Зато на складе спокойно лежали сорок медных турок, заказанных «на всякий случай», и коробка подарочных наборов, не распроданных к Новому году. Деньги были вложены. Просто не туда.

Интуиция владельца хорошо работает на пяти товарах. На двадцати она уже путает «популярное» с «приносящим деньги» и «продаётся часто» с «продаётся ровно». ABC XYZ анализ разделяет эти вещи. Он отвечает на два вопроса по каждой позиции: какую долю выручки она даёт и насколько предсказуем её спрос. Дальше — простая матрица 3 × 3, где у каждой клетки свой порядок закупки.

Разберём метод на вымышленном магазине Гульнары: 20 позиций, шесть месяцев продаж, все расчёты по шагам. В конце будет инструкция для Excel с формулами, которую можно повторить на своих данных за вечер.

ABC XYZ анализ: что это такое простыми словами?

Представьте склад, где каждая коробка подписана двумя буквами. Первая говорит, насколько коробка важна для кассы. Вторая — можно ли заранее сказать, сколько из неё возьмут в следующем месяце. ABC XYZ анализ и есть способ расставить эти буквы по цифрам, а не на глаз.

ABC-анализ вырос из принципа Парето: небольшая часть товаров обычно даёт большую часть выручки. Позиции сортируют по выручке от большей к меньшей и считают, какую долю общего оборота каждая добавляет нарастающим итогом. Пока накопленная доля не дошла до 80%, товары получают букву A. От 80% до 95% — B. Всё, что дальше, — C.

XYZ-анализ смотрит на другое. Ему неважно, много денег приносит товар или мало. Его интересует, насколько продажи одного месяца похожи на продажи другого. Мерой служит коэффициент вариации: отношение стандартного отклонения к среднему значению. Если кофе в зёрнах продаётся по 115–125 пачек каждый месяц, коэффициент маленький. Если ручная кофемолка уходит то 15, то 40 штук, он большой.

По отдельности оба метода полезны, но слепы. ABC скажет, что кофемолка важна для выручки. Он не скажет, что заказывать её «как обычно» опасно: в одном месяце дефицит, в другом лишняя партия на складе. XYZ подсветит нестабильность, но не отличит дорогую проблему от копеечной. Поэтому их и соединяют.

Буква ABC отвечает на вопрос «насколько это важно», буква XYZ — «насколько это предсказуемо». Решение о закупке всегда принимается по паре.

Где ещё применяют ту же логику

Метод придумывали под товары и склад, но работает он везде, где есть «объекты» и «деньги по периодам»:

Магазин Гульнары: 20 позиций и шесть месяцев продаж

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

Сначала ABC. Для каждой позиции перемножаем цену на проданное количество, получаем выручку за полгода. Складываем всё — выходит 5 442 890 ₽. Делим выручку позиции на этот итог — это её доля. Сортируем по убыванию и считаем накопленную долю: сумму долей от первой строки до текущей.

Шаг 1. ABC-анализ продаж по выручке

Посмотрите, что получилось. Семь позиций из двадцати — 35% ассортимента — дают 78,7% выручки. Ещё шесть добавляют 16,3%. Последние семь вместе приносят около 5% денег.

И первое открытие для Гульнары. Капсулы «Эспрессо» по 420 ₽ она считала мелочью: «Это же не кофемолки». А они на третьем месте, выше кофемолок. Покупают их часто и помногу. Турка медная, которой она забила полку, — тринадцатая, на самой границе B и C: накопленная доля 94,97%.

Формула доли простая:

Доля позиции = выручка позиции / выручка всех позиций × 100%.

Накопленная доля строки N = доля строки 1 + доля строки 2 + … + доля строки N (после сортировки по убыванию выручки).

Есть тонкость с позицией, которая пересекает границу. У нас седьмая строка заканчивается на 78,7%, восьмая — уже на 83,5%. Одни аналитики относят к A только товары до 80% включительно. Другие включают в A и ту позицию, на которой граница пересечена. Оба подхода встречаются. Главное — выбрать один и применять его каждый раз одинаково, иначе сравнение кварталов между собой потеряет смысл.

Шаг 2. XYZ-анализ: насколько ровно продаётся каждая позиция

Теперь забываем про деньги и смотрим на штуки по месяцам. Для каждой позиции считаем среднее за шесть месяцев и стандартное отклонение — насколько в среднем месяц отклоняется от этого среднего. Делим одно на другое.

Коэффициент вариации = стандартное отклонение / среднее × 100%.

Стандартное отклонение = √( Σ (продажи месяца − среднее)² / число месяцев ). Это отклонение по всей совокупности: в Excel его считает функция СТАНДОТКЛОН.Г, и все цифры примера ниже получены именно так.

Разберём одну строку руками, чтобы формула перестала пугать. Капсулы «Эспрессо»: 210, 250, 190, 260, 200, 240 упаковок.

Так же считаем остальные позиции. Excel сделает это одной формулой, о ней ниже. Результат для всех двадцати:

Пороги здесь самые распространённые: до 10% — X, от 10% до 25% — Y, больше 25% — Z. В литературе и в программах учёта встречаются и другие, например 15/50, а для товаров с сезонностью границы сдвигают ещё сильнее. Это ориентиры. Если у вас весь ассортимент попал в Z, проблема не в товарах, а в пороге — или в периоде.

Тут тоже есть пограничный случай. Капсулы «Лунго» — 25,5%. Формально Z. По сути — на краю Y. Такие позиции стоит пометить отдельно и не делать из буквы приговор: полпроцента разницы легко даёт одна акция.

А теперь присмотритесь к посуде: кофемолке, гейзерной кофеварке, турке, кружке-термосу. Их продажи скачут одинаково. Август с октябрём высокие, сентябрь с ноябрём низкие. Это не случайность покупателей. В августе и октябре Гульнара делала рассылку с промокодом на посуду. Коэффициент вариации честно показал нестабильность. Но причина её — в самом магазине. Про такие ловушки поговорим в разделе ошибок.

Матрица 3 × 3: куда попала каждая позиция

Соединяем буквы. У каждой позиции теперь два признака, а у магазина — карта из девяти клеток.

Четыре позиции в AX дают вместе около 51% выручки. Почти половина денег магазина держится на товарах, спрос на которые можно предсказать с точностью до нескольких пачек. Ровно их Гульнара и упустила в январе. А CZ — четыре позиции и около 1,9% выручки — занимали на складе больше места, чем весь чай.

Что делать с каждой из девяти групп?

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

И как это выглядит у Гульнары на практике.

ABC XYZ анализ складских запасов: при чём тут деньги

На складе лежат не коробки, а замороженные деньги. Возьмём медные турки: 40 штук на полке при закупочной цене, допустим, 1 100 ₽ — это 44 000 ₽, которые лежат месяцами. При продажах около семи штук в месяц такого запаса хватит почти на полгода. Тем временем на кофе группы AX денег на страховой резерв не нашлось.

Анализ запасов по ABC и XYZ как раз и нужен, чтобы перераспределить оборотные деньги: больше в AX и AY, где товар точно уйдёт, меньше в CZ и BZ, где он может пролежать. Объём денег при этом может остаться прежним. Меняется то, где они лежат.

Для запасов полезно добавить к расчёту ещё два столбца: текущий остаток в штуках и «на сколько месяцев хватит» — остаток, делённый на средние продажи. Для AX обычно хватает запаса на две-четыре недели. Если позиция CZ обеспечена на восемь месяцев вперёд, это видно сразу, без всякой матрицы.

Как сделать ABC XYZ анализ в Excel: пошаговая инструкция

Теперь то же самое своими руками. Понадобится выгрузка продаж из учётной системы, CRM или платформы магазина: артикул и название, количество по месяцам, сумма. Формулы ниже даны для русской версии Excel, в скобках — английские названия функций.

Несколько практических мелочей, на которых спотыкаются чаще всего.

Разделитель аргументов. В русской версии Excel он обычно точка с запятой, в английской — запятая. Если формула не принимается, дело почти всегда в этом.

Какое стандартное отклонение брать. СТАНДОТКЛОН.Г считает по всей совокупности — делит на число месяцев. СТАНДОТКЛОН.В (STDEV.S) — по выборке, делит на число месяцев минус один, и коэффициент выходит чуть выше. В нашем примере использован СТАНДОТКЛОН.Г. С СТАНДОТКЛОН.В у капсул «Эспрессо» вышло бы 12,8% вместо 11,7%, а кофе «Эфиопия» с 27,1% вместо 24,7% перешёл бы из Y в Z. Для XYZ чаще берут первый вариант. Важнее, чтобы вы не меняли его от расчёта к расчёту.

Нулевые месяцы. Если у позиции все месяцы нулевые, СРЗНАЧ даст ноль, и формула вернёт ошибку деления. Оберните её: =ЕСЛИОШИБКА(СТАНДОТКЛОН.Г(B2:G2)/СРЗНАЧ(B2:G2);"нет продаж") (IFERROR).

Пороги в отдельных ячейках. Вместо 80% и 95% прямо в формуле вынесите их в ячейки, например Q1 и Q2, и ссылайтесь на них: =ЕСЛИ(K2<=$Q$1;"A";…). Тогда порог можно поменять одним движением и посмотреть, как перестроится матрица.

Условное форматирование. Подсветите AX зелёным, CZ — серым, AZ — жёлтым. На двадцати строках это украшение. На двух тысячах — единственный способ увидеть картину глазами.

ABC XYZ анализ клиентов: та же таблица, другие строки

Замените товары на покупателей — и метод работает так же. Для оптовой компании или магазина с постоянными клиентами это иногда даже полезнее, чем анализ ассортимента.

ABC по клиентам: сортируем покупателей по выручке за период, считаем накопленную долю. Обычно выясняется, что десяток клиентов из сотни даёт больше половины оборота. XYZ по клиентам: берём выручку или число заказов по месяцам и считаем тот же коэффициент вариации. X — клиент покупает регулярно, Z — от случая к случаю.

Отличие от товаров одно, но важное. Товар не обижается, когда его выводят из ассортимента. Клиент группы C, с которым стали разговаривать хуже, просто уйдёт — и вы не узнаете, мог ли он вырасти. Поэтому по клиентам матрица задаёт уровень сервиса и приоритет, а не решение «работать или нет».

И ещё одно. Для ABC клиентов цифры берутся из учёта и CRM. А вот причины, по которым клиент из AX вдруг стал заказывать реже, в таблице не видны. Их слышно в разговорах: жалоба на сроки, упоминание другого поставщика, смена закупщика. Как руководитель находит такие сигналы в звонках, мы разбирали в статье про отчёт отдела продаж — с формулами и показателями по этапам.

Семь ошибок, которые ломают матрицу

Метод простой, поэтому ошибки в нём тоже простые. И очень частые.

Слишком короткий период

ABC за один месяц покажет случайность: крупный заказ одного клиента выведет в A товар, который больше никто не купит. XYZ за три месяца вообще мало что значит — по трём точкам не отличить колебание от тенденции. Ориентир: ABC — не меньше квартала, XYZ — от шести месяцев, лучше двенадцать.

Сезонность, принятая за хаос

Подарочный набор у Гульнары получил коэффициент вариации 139,6% — самый высокий в магазине. По формальному правилу это BZ: работать под заказ, запасов не держать. Но спрос на него как раз предсказуем: каждый декабрь он взлетает. Сезонные товары нужно либо считать отдельно, внутри сезона, либо сравнивать с тем же месяцем прошлого года. Иначе метод посоветует не держать запас именно тогда, когда он нужнее всего.

Свои акции как «непредсказуемый спрос»

Посуда скачет в ритме рассылок с промокодом. XYZ видит нестабильность, но не знает о вашем календаре акций. Перед выводами отметьте месяцы промо и посмотрите, как ведёт себя товар без них. Часто Z превращается в Y.

Нули из-за дефицита

В сентябре набор пробников продал ноль штук. Его не разлюбили — он просто закончился на складе. Нулевой месяц из-за отсутствия товара занижает и выручку в ABC, и стабильность в XYZ. Такие месяцы лучше исключить из расчёта или пометить, иначе метод «подтвердит», что товар не нужен, хотя его просто не было.

Новинки в общей куче

Позиция, которая появилась два месяца назад, почти всегда окажется в C и Z: мало истории, мало продаж, спрос ещё не устоялся. Выводить её по итогам матрицы рано. Новинки считают отдельно или дают им испытательный срок — например, полгода.

Одна метрика на всё

ABC по выручке и ABC по прибыли — разные таблицы. Капсулы с низкой наценкой могут быть в A по выручке и в B по марже, а кофемолка — наоборот. Для решений об ассортименте полезно сделать оба расчёта и сравнить. Для запасов чаще хватает выручки или количества. Можно считать ABC и по штукам, но тогда ложка мерная за 120 ₽ окажется рядом с кофемолкой за 3 200 ₽ — это уже другая картина.

Вывод сопутствующих товаров

Шоколад горький — 0,1% выручки, группа CZ. Формально — на вывод. Но если его берут вместе с кофе, а без него часть покупателей пойдёт искать «всё в одном месте» к соседям, экономия на полке обойдётся дороже. Перед тем как убрать позицию C, проверьте, в каких чеках она встречается.

Что ABC XYZ анализ не покажет — и где нужна аналитика глубже

Честно о границах метода. Матрица отвечает на вопрос «что» и почти никогда — на вопрос «почему».

Она не объяснит, почему кофе «Эфиопия» продаётся рывками: из-за рекламы, из-за того что его то нет на складе, то он есть, или потому что карточка товара без описания вкуса. Она не скажет, сколько выручки вы потеряли в дни дефицита. Она не учитывает срок поставки: товар AX с поставкой за два дня и товар AX с поставкой за шесть недель требуют разного запаса. И она ничего не знает о марже, если вы сами её не добавили.

Двадцать позиций Гульнара посчитает в Excel за вечер. Сложности начинаются, когда позиций тысячи, продажи идут через сайт и маркетплейсы, остатки лежат в складской программе, а заказы — в 1С. Тогда основная работа уходит на то, чтобы свести всё в одну таблицу, где артикулы совпадают, а нули из-за дефицита отличаются от нулей из-за отсутствия спроса.

Такие задачи решает аналитика для бизнеса от КАРАДЕСК. Для магазинов есть отдельное направление — аналитика интернет-магазина: она соединяет данные сайта и счётчиков с заказами и оплатами, каталогом, остатками, рекламными расходами и возвратами. Вокруг заказа смотрит деньги — в том числе цены против рынка, а также остатки и упущенные продажи. Данные берутся там, где они уже есть: счётчики сайта, заказы, оплаты, каталог, маркетплейсы.

Если товар ещё и возят своими машинами или через перевозчиков, полезна аналитика логистики: сроки и стоимость доставки, загрузка транспорта, маршруты, возвраты — по данным учётной и складской программ и счетов перевозчиков. А сверка цен и прайсов сверяет цены и остатки по артикулам между прайсами поставщиков, учётом, сайтом и маркетплейсами и находит двойники в каталоге.

Чего эта аналитика не делает. Она не меняет данные в 1С, CRM или других системах — доступ только на чтение. Выводы — это рекомендации с источником, а решения о закупке и ассортименте принимает руководство. Результат не гарантируется.

По цене направления такие. Разбор интернет-магазина — от 29 900 ₽. Разбор логистики — от 39 900 ₽. Сверка цен и прайсов — разово от 14 900 ₽, регулярное отслеживание от 9 900 ₽ в месяц. Точная стоимость фиксируется до старта после бесплатной оценки и зависит от объёма данных и формата. Работа идёт по NDA, с доступом только на чтение.

Возражения: «нам это не нужно», «у нас всё в голове», «программа сама посчитает»

«У нас мало позиций, я всё знаю». Возможно. Но проверка займёт вечер. Гульнара тоже «знала» — и считала капсулы мелочью, хотя они третьи по выручке. Если расчёт подтвердит интуицию, вы потеряли пару часов. Если нет — нашли, куда уходят деньги.

«Наша учётная программа уже умеет делать ABC». Отлично, тогда не нужно считать руками. Но проверьте, какие пороги там стоят, за какой период считает отчёт и как программа обходится с нулями из-за дефицита и с новинками. Готовый отчёт с неверными настройками опаснее, чем отсутствие отчёта: ему верят.

«Рынок меняется, прошлые продажи ничего не говорят о будущем». Отчасти верно. Поэтому матрицу пересчитывают — обычно раз в квартал, а для быстрых категорий раз в месяц. Интересно, где товар сейчас и куда он движется: позиция, которая за полгода перешла из AX в BY, — сигнал раньше, чем провал в выручке.

«Выводить товары группы C страшно — вдруг покупатели уйдут». Правильный страх. Именно поэтому C — это повод проверить, а не приговор. Сопутствующие товары, новинки, позиции для «полной корзины» остаются. Уходит то, что не продаётся, не тянет за собой другие покупки и занимает место.

Чек-лист на один вечер

Короткий итог для тех, кто листал

По сути, это две сортировки одной таблицы. Первая — по деньгам, вторая — по стабильности. На пересечении — девять групп, и для каждой понятно, как закупать, сколько держать на складе и сколько внимания тратить. В магазине Гульнары он показал три вещи, которых владелица не видела: капсулы важнее, чем казались; половина выручки держится на четырёх ровных позициях, которые нельзя допускать до дефицита; а посуда «скачет» из-за её же рассылок.

Посчитать можно в Excel по инструкции выше. А когда ассортимент и данные перерастут одну таблицу, придёт время для аналитики, которая сведёт продажи с остатками и возвратами из разных систем. Если интересно, как ИИ уже помогает магазинам в других задачах — звонках, заказах, ответах покупателям, — посмотрите статью «ИИ для интернет-магазина».