Как сделать ABC-анализ: выгрузите продажи по товарам за период, отсортируйте по выручке или марже, посчитайте долю каждой позиции и накопленный итог, а затем разделите список на три группы — A, B и C. На это уходит час в Excel, если данные чистые. Ниже — пошаговая инструкция с формулами и условным примером на 10 товарах, а также честный разговор о том, почему разовый расчёт перестаёт работать уже через месяц.
- ABC-анализ продаж делит ассортимент по вкладу в результат: A — немногие позиции, которые дают основную часть денег, C — длинный хвост с малым вкладом.
- Считать стоит дважды: по выручке и по марже. Совпадают группы далеко не всегда.
- Для Excel хватит выгрузки «товар — количество — выручка — себестоимость» за 6–12 месяцев.
- Разовый расчёт полезен, но устаревает вместе с остатками и ценами — регулярный анализ лучше автоматизировать.
Что показывает ABC-анализ продаж и чего он не покажет
ABC-анализ отвечает на один вопрос: какие товары приносят основную часть денег. Метод опирается на принцип Парето — обычно небольшая доля ассортимента даёт большую долю выручки. Границы групп каждая компания выбирает сама, чаще всего берут 80, 15 и 5 процентов накопленного итога.
Чего ABC не покажет: стабилен ли спрос, сколько товара лежит на складе и как быстро он продаётся. Позиция из группы A может продаваться рывками раз в квартал, а позиция из группы C — быть сопутствующей, без которой не купят товар-лидер. Поэтому ABC-анализ ассортимента — отправная точка, а не приговор: в товарной аналитике его всегда смотрят рядом с остатками, оборачиваемостью и маржой. Как его сочетают с оценкой стабильности спроса, мы разбирали в статье про матрицу ABC и XYZ; здесь сосредоточимся на самой механике расчёта.
Какие данные нужны для ABC-анализа в Excel
Для расчёта нужна таблица, где одна строка — один товар. Минимальный набор колонок:
- Артикул и наименование — уникальный код, а не только название: в названиях бывают опечатки и дубли.
- Количество продаж за период — для контроля и последующего XYZ.
- Выручка без НДС или с НДС — главное, одинаково для всех позиций.
- Себестоимость проданного — чтобы посчитать маржу.
- Категория и поставщик — чтобы потом смотреть группы в разрезах.
Если учёт ведётся в 1С, такую выгрузку дают отчёты по продажам или по валовой прибыли — в разных конфигурациях они называются по-разному. Период берите не короче 6 месяцев, а для сезонного товара — 12, иначе зимняя куртка в августе окажется в группе C.
Смешивать возвраты и продажи. Если в выгрузке возвраты идут отдельными строками с минусом или не учтены вовсе, выручка по позиции искажается. Перед расчётом убедитесь, что у каждого товара одна строка с чистой выручкой за вычетом возвратов.
ABC-анализ: пошаговая инструкция в Excel
Ниже — последовательность, которая работает в любой версии Excel и в Google Sheets. Формулы приведены для русской локали с точкой с запятой; данные начинаются со второй строки, выручка — в колонке B, всего 10 товаров.
- Отсортируйте по убываниюВыделите таблицу и отсортируйте по колонке выручки от большего к меньшему. Без этого накопленный итог не имеет смысла.
- Посчитайте долю каждой позицииВ колонке C: =B2/СУММ($B$2:$B$11). Формат ячейки — процентный. Сумма долей должна дать 100%.
- Посчитайте накопленную долюВ D2 впишите =C2, в D3 — =D2+C3 и протяните вниз. В последней строке должно получиться 100%.
- Присвойте группуВ колонке E: =ЕСЛИ(D2<=0,8;"A";ЕСЛИ(D2<=0,95;"B";"C")). Границы можно вынести в отдельные ячейки, чтобы менять их без правки формулы.
- Проверьте итогСводной таблицей посчитайте число позиций и сумму выручки в каждой группе. Если в A оказалось больше половины ассортимента, проверьте исходные данные или границы.
- Повторите по маржеДобавьте колонку «маржа = выручка − себестоимость» и сделайте шаги 1–5 заново уже по ней.
Пример ABC-анализа на 10 товарах
Возьмём условный магазин с десятью позициями и общей выручкой 10 000 000 ₸ за полгода. Цифры придуманы для наглядности, но структура таблицы — ровно такая, какая получится у вас после шагов выше.
| Товар | Выручка, ₸ | Доля | Накопленная доля | Группа |
|---|---|---|---|---|
| Товар 1 | 3 200 000 | 32% | 32% | A |
| Товар 2 | 2 400 000 | 24% | 56% | A |
| Товар 3 | 1 600 000 | 16% | 72% | A |
| Товар 4 | 900 000 | 9% | 81% | B |
| Товар 5 | 700 000 | 7% | 88% | B |
| Товар 6 | 500 000 | 5% | 93% | B |
| Товар 7 | 300 000 | 3% | 96% | C |
| Товар 8 | 200 000 | 2% | 98% | C |
| Товар 9 | 120 000 | 1,2% | 99,2% | C |
| Товар 10 | 80 000 | 0,8% | 100% | C |
Три позиции из десяти дают 72% выручки — это группа A. Товар 4 пересекает границу 80% и уходит в B: так работает формула «меньше или равно». Некоторые аналитики относят пограничную позицию к верхней группе — договоритесь о правиле один раз и не меняйте его от расчёта к расчёту, иначе группы будут «прыгать» без реальных причин.
ABC по выручке и по марже: почему результаты расходятся
Выручка показывает оборот, но не заработок. Представим, что в нашем примере товар 2 — популярная позиция с минимальной наценкой, и его маржа составляет условные 5% от выручки, то есть 120 000 ₸. Товар 6 продаётся хуже, но с маржой 40% — это 200 000 ₸. В ABC по выручке товар 2 — твёрдое A, в ABC по марже он опускается вниз списка, а товар 6 поднимается.
Что это значит для решений:
- A по выручке и A по марже — ядро ассортимента. Наличие таких позиций контролируют в первую очередь.
- A по выручке, C по марже — «трафиковые» товары. Их держат ради потока покупателей, но не наращивают закупку бездумно и регулярно пересматривают цену и условия поставщика.
- C по выручке, A или B по марже — нишевые позиции с хорошей наценкой. Выводить их по одному обороту — ошибка.
- C и там, и там — кандидаты на сокращение, но только после проверки, не тянут ли они продажи других товаров.
Маржа по товарам — мостик к финансовой картине: если понадобится видеть прибыль не только по SKU, но и по направлениям, клиентам и в связке с движением денег, это уже задача финансовой аналитики на данных 1С.
ПРАВИЛОРешение о закупке принимают по марже, а не по обороту. Выручка отвечает на вопрос «что популярно», маржа — «на чём мы зарабатываем».
Что делать с группами A, B и C после расчёта
Таблица с буквами сама по себе ничего не меняет. Ценность появляется, когда за каждой группой закреплено действие и ответственный.
- Группа A. Ежедневный контроль остатков, приоритет в закупке, страховой запас. Отсутствие такой позиции на полке или на сайте — прямые потерянные деньги.
- Группа B. Плановая закупка по графику, контроль раз в неделю. Здесь часто прячутся будущие лидеры — следите за динамикой.
- Группа C. Минимальный запас или поставка под заказ, ревизия раз в квартал. Позиции, которые одновременно в C и долго лежат на складе, — первые на распродажу.
Отдельно посмотрите группы в разрезе поставщиков. Если у одного поставщика почти весь ассортимент в C по марже, это аргумент для переговоров об условиях — или для поиска замены.
- Период — не меньше 6 месяцев, для сезонных товаров — 12.
- Возвраты вычтены, у каждого товара одна строка.
- Артикулы уникальны, дубли наименований объединены.
- Новинки, которые продаются меньше трёх месяцев, вынесены отдельно.
- Посчитаны два варианта: по выручке и по марже.
- Границы групп зафиксированы и записаны.
- Для каждой группы назначено действие и ответственный.
- Дата следующего пересчёта стоит в календаре.
Почему разовый ABC-анализ в Excel быстро устаревает
Файл, который вы собрали сегодня, отражает прошлые полгода. Через месяц меняются цены поставщиков, появляются новинки, лидер может уйти в дефицит, а позиция из C — внезапно выстрелить. Если пересчитывать вручную раз в квартал, закупщик принимает решения по картинке трёхмесячной давности.
Вторая проблема — трудоёмкость. Каждый пересчёт — это выгрузка из 1С, чистка дублей, сведение возвратов, подтягивание себестоимости. На ассортименте в несколько тысяч SKU и нескольких складах это уже не час, а день работы, и ошибка в одной формуле незаметно портит весь результат.
Третья — разрыв с остатками. ABC показывает вклад в продажи, но не показывает, сколько денег заморожено в товаре, который не продаётся. Для этого нужен второй файл с остатками и оборачиваемостью, и сводить их вручную ещё сложнее.
Подключим 1С и магазин и соберём ABC/XYZ по выручке и марже, оборачиваемость и неликвиды в деньгах на одном дашборде, который обновляется каждый день.
Посмотреть товарную аналитикуКак перейти от Excel к регулярному анализу
Если ABC-анализ продаж нужен раз в год для стратегической сессии, Excel вполне справится. Если закупщик принимает решения каждую неделю, расчёт стоит автоматизировать. В WeMake формат «Ассортимент» стоит от 400 000 ₸ и занимает около 3 недель: ABC/XYZ по выручке, марже и стабильности спроса, оборачиваемость, неликвиды и маржа по SKU из 1С и магазина. Первый дашборд — через две недели, обновление — ежедневно, без ручных выгрузок.
Порядок перехода такой. Сначала сделайте расчёт вручную по этой инструкции — так вы поймёте, какие разрезы действительно нужны и где в данных грязь. Затем зафиксируйте правила: границы групп, период, учёт возвратов и новинок. С этим набором правил подключение автоматической аналитики пройдёт быстрее: справочники товаров сверяются на старте, чтобы одна позиция в 1С и на сайте считалась одним SKU. Остальные форматы и вилки — на странице цен на аналитику.
Как сделать ABC-анализ, если товаров больше тысячи?
Механика та же, но вручную легко ошибиться. Сначала проверьте артикулы на дубли, затем считайте в сводных таблицах, а не в протянутых формулах. При регулярной работе с тысячами SKU расчёт лучше автоматизировать.
Какие границы групп выбрать: 80/15/5 или другие?
80/15/5 — самый распространённый вариант, но не закон. Важнее, чтобы границы не менялись от расчёта к расчёту, иначе вы не сможете сравнивать результаты между периодами.
ABC-анализ по выручке или по марже?
По обоим. Выручка показывает, что популярно, маржа — на чём компания зарабатывает. Решения о закупке и выводе товаров принимайте с оглядкой на маржу.
Можно ли сделать ABC-анализ в Google Sheets?
Да, формулы те же: доля, накопленный итог и ЕСЛИ для присвоения группы. Ограничение одно — производительность на больших таблицах.
Как часто пересчитывать ABC-анализ продаж?
Для активного ассортимента — ежемесячно или чаще. Для стабильного — раз в квартал. Если расчёт автоматизирован, группы обновляются вместе с данными каждый день.
