ABC-анализ товаров в Excel: как найти свои 20% товаров, дающих 80% прибыли

Известный принцип Парето гласит: 20% товаров дают 80% выручки. Но как найти именно эти 20%? И как управлять остальными 80%? На эти вопросы отвечает ABC-анализ — один из самых мощных инструментов оптимизации ассортимента в розничной торговле и управлении складом.

Почему ABC-анализ работает

Если вы работаете в розничной торговле, E-commerce или управляете складом, то знаете: не все товары одинаково важны. Один товар может приносить половину прибыли, а сотни других товаров работают себе в убыток или едва окупают место на полке.

ABC-анализ позволяет:

• Сократить затраты на хранение — понять, какие товары занимают место впустую
• Оптимизировать закупки — закупать нужные товары, отказаться от убыточных
• Увеличить прибыль — сосредоточиться на товарах, которые действительно приносят деньги
• Управлять складом эффективнее — размещать популярные товары в удобных местах

Что такое ABC-классификация

ABC-классификация делит все товары на три группы:

Группа A (80% прибыли) — малое количество самых ценных товаров. Это могут быть 5-10% от всего ассортимента, но они приносят 70-80% выручки. К этим товарам нужно относиться особенно внимательно.

Группа B (15% прибыли) — товары среднего значения. Обычно это 10-20% товаров, которые приносят 15-20% выручки. Управление стандартное.

Группа C (5% прибыли) — остальной ассортимент. Это может быть 70-80% товаров, но они приносят лишь 5% выручки. Часто это убыточные позиции.

Пример реального бизнеса: магазин электроники

Представьте магазин, в котором продают смартфоны, чехлы, зарядки и плёнки. Вот текущие данные:

Товар	Продажи/месяц	Цена	Выручка
iPhone 15 Pro	15 шт	120 000 ₽	1 800 000 ₽
Samsung Galaxy S24	12 шт	80 000 ₽	960 000 ₽
iPhone 15	8 шт	90 000 ₽	720 000 ₽
Чехол универсальный	180 шт	500 ₽	90 000 ₽
Зарядка быстрая	45 шт	2 000 ₽	90 000 ₽
Защитная плёнка	200 шт	150 ₽	30 000 ₽

Всего выручка: 3 690 000 ₽

Как сделать ABC-анализ в Excel: пошагово

Шаг 1. Подготовка данных

Создайте таблицу в Excel со следующими столбцами:

A: Товар
B: Выручка
C: Доля выручки
D: Накопленная доля
E: Категория

Вводим наши данные:

Строка 1: Заголовки
Строка 2: iPhone 15 Pro | 1800000
Строка 3: Samsung Galaxy S24 | 960000
Строка 4: iPhone 15 | 720000
Строка 5: Чехол универсальный | 90000
Строка 6: Зарядка быстрая | 90000
Строка 7: Защитная плёнка | 30000

Шаг 2. Расчёт доли выручки каждого товара

В ячейке C2 вводим формулу для расчёта доли одного товара от общей выручки:

=B2/SUM($B$2:$B$7)

Копируем эту формулу вниз. В результате получим:

iPhone 15 Pro: 48.78% (1 800 000 / 3 690 000)
Samsung Galaxy S24: 26.02%
iPhone 15: 19.51%
Чехол: 2.44%
Зарядка: 2.44%
Плёнка: 0.81%

Шаг 3. Сортировка по выручке

Это критичный шаг. Выделяем всю таблицу (A1:E7) и сортируем по столбцу B (Выручка) в порядке убывания. После сортировки дорогие товары будут в начале.

Шаг 4. Расчёт накопленной доли (кривая Лоренца)

В ячейке D2:

=C2

В ячейке D3:

=D2+C3

И копируем D3 вниз. Получим:

iPhone 15 Pro: 48.78%
Samsung Galaxy S24: 74.80% (48.78% + 26.02%)
iPhone 15: 94.31% (74.80% + 19.51%)
Чехол: 96.75%
Зарядка: 99.19%
Плёнка: 100.00%

Шаг 5. Присвоение группы (A, B или C)

В ячейке E2 вводим формулу:

=IF(D2<=0.8,"A",IF(D2<=0.95,"B","C"))

Формула работает так:

• Если накопленная доля <= 80%, это товар группы A
• Если <= 95%, это товар группы B
• Иначе товар группы C

Копируем формулу вниз. В нашем примере:

iPhone 15 Pro: A (48.78%)
Samsung Galaxy S24: A (74.80%)
iPhone 15: A (94.31%)
Чехол: B (96.75%)
Зарядка: B (99.19%)
Плёнка: C (100%)

Все три смартфона — это группа A! Они приносят 94.31% выручки. Средние товары — чехол и зарядка. Плёнка — убыток, который занимает место.

Визуализация: диаграмма Парето в Excel

Создаём диаграмму для наглядности:

1. Выделяем столбцы A (Товар) и D (Накопленная доля)
2. Вставляем график (Insert > Chart)
3. Выбираем тип "XY (Scatter)" или "Column"
4. На диаграмме видно, как быстро выручка «накапливается» на первых товарах

График покажет классическую кривую Парето: левая часть диаграммы резко поднимается вверх, а затем плата становится более пологой.

ABC-анализ в розничном магазине: что дальше?

После анализа для каждой группы применяем разные стратегии:

Товары группы A (80% выручки)

• Никогда не кончаются: следим, чтобы на полке всегда было достаточно товара
• Повышаем наценку аккуратно: можем позволить себе небольшое увеличение цены
• Закупаем в больших объёмах: договариваемся о лучшей цене у поставщика
• Размещаем в лучшем месте: в центре магазина, на уровне глаз

Товары группы B (15% выручки)

• Заказываем по стандартной схеме: обычное управление запасами
• Стараемся избежать дефицита: но не переплачиваем за хранение избытка
• Тестируем новые позиции: проверяем, могут ли они вырасти в группу A

Товары группы C (5% выручки)

• Снижаем или прекращаем закупки: проанализируем, почему они не продаются
• Может быть, это опечатка: проверяем, правильно ли считается выручка
• Уходит на скидки: выдаём скидку на остаток и освобождаем место
• Удаляем из ассортимента: если товар не продаётся даже со скидкой

XYZ-анализ: второй слой классификации

ABC-анализ работает с выручкой. Но что, если товар приносит хорошую прибыль, но продаётся нестабильно? На помощь приходит XYZ-анализ, который оценивает стабильность продаж.

Разделяем товары по волатильности:

Группа X — стабильные продажи (коэффициент вариации 0-25%) Группа Y — средняя волатильность (25-75%) Группа Z — нестабильные продажи (75%+)

Формула коэффициента вариации:

=(STDEV(диапазон_продаж)/AVERAGE(диапазон_продаж))

Если товар из группы A, но в группе Z (нестабильный спрос), нужно подстраховаться под сезонность или тренды.

ABC-XYZ матрица: полная картина

Комбинируя ABC и XYZ, получаем 9 позиций:

        X (стабильно)    Y (средне)       Z (скачки)
A       AX               AY               AZ
        (звёзды)         (попечение)      (риск)

B       BX               BY               BZ
        (рабочие)        (наблюдение)     (неточное)

C       CX               CY               CZ
        (переток)        (очистка)        (убыток)

AX — королевские товары: стабильные, прибыльные. Закупаем с уверенностью. AZ — опасные товары: приносят прибыль, но скачут продажи. Подстраховываемся. CZ — балласт: нестабильный спрос и малая выручка. Удаляем.

Ошибки при анализе товаров

Ошибка 1: Анализируем выручку вместо прибыли

Товар может приносить большую выручку, но иметь малую маржу. Например, яйца в супермаркете — большие объёмы продаж, но малая прибыль. Лучше анализировать именно прибыль, а не выручку.

Формула в таком случае:

=B2*Маржа_товара

Ошибка 2: Забываем про сезонность

Ёлки продаются только в декабре, но это не значит, что их нужно убирать из ассортимента. Анализируем товары внутри одного сезона.

Ошибка 3: Делаем анализ раз в год

Тренды в продажах меняются. ABC-анализ нужно делать ежемесячно или хотя бы ежеквартально. В Excel можно автоматизировать этот процесс.

Автоматизация ABC-анализа в Excel

Если товаров сотни или тысячи, анализ вручную невозможен. Создаём шаблон в Excel:

1. Подготавливаем данные в одной таблице (вкладка "Исходные данные")
2. Создаём отчёт на второй вкладке, где формулы автоматически считают доли и присваивают категории
3. Используем VLOOKUP для связи товаров с их категориями
4. Настраиваем фильтры по группам A, B, C для быстрого анализа

=VLOOKUP(A2,'Исходные данные'!$A$2:$E$1000,5,FALSE)

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

Практический пример: управление складом

Вы управляете складом на 10 000 товарных позиций. Место дорогое. ABC-анализ показывает:

• Группа A (2000 товаров, 80% выручки): размещаем на стеллажах на уровне глаз, удобном доступе
• Группа B (3000 товаров, 15% выручки): стандартное размещение
• Группа C (5000 товаров, 5% выручки): хранилище в углу, раз в месяц перевозим на другой склад

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

Выводы

ABC-анализ — это не магия, а простая математика, которая помогает увидеть реальную картину вашего бизнеса. 20% товаров действительно дают 80% прибыли. Вопрос в том, знаете ли вы, какие это товары.

С помощью этого анализа вы:

• Понимаете, на чём зарабатываете деньги
• Избавляетесь от убыточных товаров
• Оптимизируете закупки и хранение
• Увеличиваете общую прибыль

Начните с простого анализа своего каталога прямо сегодня. Даже небольшие изменения в ассортименте часто дают заметный прирост в прибыли.

Используйте ABC-анализ не как разовый отчёт, а как постоянный инструмент управления. И ваш бизнес станет заметно более эффективным.