ABC-анализ ассортимента: пример в Excel
ABC-анализ показывает, какие товары приносят основную выручку или прибыль, а какие — почти ничего. Это простой и мощный инструмент для управления ассортиментом. Разберём расчёт в Excel по шагам.
Принцип
ABC-анализ основан на принципе Парето: небольшая часть товаров обычно даёт большую часть результата. Товары сортируют по вкладу и делят на три группы:
- A — самые важные, дают около 80% выручки;
- B — средние, около 15%;
- C — остальные, около 5%.
Расчёт в Excel по шагам
Исходные данные: столбец A — товар, столбец B — выручка за период.
- Отсортируйте таблицу по выручке от большей к меньшей.
- Доля товара в C2:
=B2/СУММ($B$2:$B$500). - Накопленная доля в D2:
=C2, в D3:=D2+C3и протяните вниз. - Группа в E2:
=ЕСЛИ(D2<=0,8;"A";ЕСЛИ(D2<=0,95;"B";"C")).
| Товар | Выручка | Доля | Накопленная | Группа |
|---|---|---|---|---|
| Товар 1 | 4 200 000 | 35% | 35% | A |
| Товар 2 | 3 000 000 | 25% | 60% | A |
| Товар 3 | 2 040 000 | 17% | 77% | A |
| Товар 4 | 1 200 000 | 10% | 87% | B |
| Товар 5 | 840 000 | 7% | 94% | B |
| Товар 6–20 | 720 000 | 6% | 100% | C |
Пример условный.
Что делать с результатами
| Группа | Действия |
|---|---|
| A | Никогда не допускать отсутствия; ежедневный мониторинг цен конкурентов; лучшие условия у поставщиков |
| B | Регулярный контроль, поиск возможностей перевести в A |
| C | Проверить, зачем они нужны: дополняют ассортимент или просто занимают деньги и место |
ABC + XYZ
XYZ-анализ делит товары по стабильности спроса: X — стабильный, Y — колеблющийся, Z — непредсказуемый. Комбинация даёт 9 групп: например, AX — главные и стабильные товары (держать запас, контролировать цену), CZ — малозначимые и непредсказуемые (кандидаты на вывод).
Связь с ценами и конкурентами
Товары группы A — первые кандидаты на мониторинг цен: потеря конкурентоспособности по ним сильнее всего бьёт по выручке. Сделайте ABC-анализ и по прибыли — товары A по выручке с низкой прибылью стоит проверить через юнит-экономику.
Если данные о продажах разбросаны по нескольким таблицам и системам, сначала их нужно свести — см. как объединить таблицы. Мы можем сделать это и настроить автоматический отчёт — объединение данных.
ABC-анализ простыми словами
Представьте, что у вас 200 товаров. ABC-анализ — это способ быстро ответить на вопрос: какие из них по-настоящему кормят бизнес? Обычно оказывается, что несколько десятков товаров дают основную выручку, а остальные — малую долю. Эти «важные» товары — группа A, им нужно максимум внимания.
Сколько процентов в ABC-анализе
Классические границы — 80/15/5: группа A даёт первые 80% выручки, B — следующие 15%, C — последние 5%. Встречаются и другие варианты, например 70/20/10 или 50/30/20. Выбирайте один и используйте его постоянно, чтобы результаты разных периодов можно было сравнивать.
Как сделать ABC-XYZ анализ в Excel
XYZ-анализ оценивает стабильность продаж по коэффициенту вариации — насколько продажи колеблются от месяца к месяцу.
- Подготовьте таблицу: товар, продажи по месяцам (например, 6 столбцов).
- Среднее:
=СРЗНАЧ(B2:G2). - Стандартное отклонение:
=СТАНДОТКЛОН.Г(B2:G2). - Коэффициент вариации:
=стандартное отклонение / среднее. - Группа:
=ЕСЛИ(K2<=0,1;"X";ЕСЛИ(K2<=0,25;"Y";"Z"))— типичные границы 10% и 25%. - Объедините с ABC:
=E2&L2— получите группы AX, AY, BZ и т. д.
Матрица ABC-XYZ: что делать с каждой группой
| X (стабильно) | Y (колеблется) | Z (непредсказуемо) | |
|---|---|---|---|
| A | Постоянный запас, ежедневный контроль цен | Запас с учётом сезона | Анализ причин скачков, заказ под спрос |
| B | Регулярные поставки | Гибкие заказы | Минимальный запас |
| C | Автоматический заказ | Под заказ | Кандидаты на вывод |
ABC-анализ в логистике и на складе
На складе ABC-анализ делают по количеству отгрузок: товары группы A размещают ближе к зоне сборки, чтобы сократить путь сборщика. В закупках — по объёму затрат: контроль цен поставщиков в первую очередь для группы A.
ABC-анализ для продавца на маркетплейсе
Для маркетплейса полезно делать анализ сразу по двум показателям — выручке и прибыли. Товар может быть в группе A по выручке и в группе C по прибыли: много продаж, но почти ноль заработка из-за комиссий и демпинга конкурентов. Для таких товаров ежедневный мониторинг цен и пересчёт юнит-экономики особенно важны.
ABC-анализ можно сделать и в учётной системе, например в 1С, если в ней есть соответствующий отчёт. Excel удобен, когда данные приходят из нескольких источников — их сначала сводят в одну таблицу (см. объединение таблиц).
Читайте также
SKU: что это такое простыми словами
Чем SKU отличается от артикула и штрихкода и как его использовать в учёте.
Читать →Артикул товара: что это и как его составить
Зачем нужен артикул, какие бывают и как придумать удобную систему.
Читать →Штрихкод товара: виды, EAN-13 и зачем он нужен
Как устроен штрихкод, зачем он маркетплейсам и как использовать его в данных.
Читать →Как найти поставщика: способы и проверка
Где искать поставщиков и как сравнить их предложения по данным.
Читать →Частые вопросы
Какие пороги использовать для групп A, B и C?
Классика: A — товары, дающие первые 80% выручки, B — следующие 15%, C — последние 5%. Пороги можно адаптировать под свой бизнес.
По какому показателю делать ABC-анализ?
Чаще всего по выручке. Полезно сделать и по прибыли: товар из группы A по выручке может оказаться в C по прибыли.
Сколько процентов в ABC-анализе?
Классика — 80/15/5: A — первые 80% выручки, B — следующие 15%, C — последние 5%. Используются и другие пропорции, главное — применять одну и ту же постоянно.
Как сделать ABC-XYZ анализ в Excel?
Для ABC отсортируйте товары по выручке и посчитайте накопленную долю. Для XYZ посчитайте коэффициент вариации продаж по месяцам (стандартное отклонение / среднее). Затем объедините группы — получится матрица AX…CZ.
Нужны данные для вашего бизнеса? Опишите задачу
Пришлите ссылку на сайт или маркетплейс и напишите, какие данные нужны. Мы оценим задачу, назовём срок и стоимость и покажем пример результата на ваших данных до оплаты.
- Ответим в течение рабочего дня
- Пробная выгрузка до начала работ
- Разовый сбор или регулярный отчёт по расписанию
- Только открытые источники — без персональных данных
Или напрямую: +7 705 277 80 80 · WhatsApp · info@life-com.kz