ABC XYZ анализ в Excel: пример расчета и готовый шаблон

ABC XYZ анализ - это метод классификации товаров, который помогает понять, на чем вы зарабатываете (часть ABC) и насколько стабильно эти товары покупают (часть XYZ). Мы в БрайтБорд часто видим, как компании тратят бюджет на закупку неликвида, игнорируя хиты продаж, просто потому что не видят полной картины. Совмещенный анализ решает эту проблему, разделяя весь ассортимент на 9 понятных групп.

Коротко о главном

• ABC делит товары по важности (выручке или прибыли): А - лидеры, С - аутсайдеры.
• XYZ делит товары по предсказуемости спроса: X - стабильные, Z - хаотичные.
• Матрица на выходе дает группы вроде AX (дойные коровы) или CZ (претенденты на вывод).
• Для старта достаточно Excel, но при росте номенклатуры лучше использовать автоматизацию.

Многие предприниматели управляют складом «по ощущениям». Это приводит к двум бедам: замороженные деньги в товарах, которые никто не берет, и пустые полки там, где должен лежать ходовой товар. Чтобы этого избежать, нужно не просто смотреть на сумму продаж, а видеть структуру запасов через призму стабильности.

Зачем нужен совмещенный ABC XYZ анализ?

По отдельности эти методики дают однобокую картину.

• ABC анализ покажет, какой товар принес вам миллион рублей. Но он не скажет, продали ли вы его одной крупной партией случайно или продавали равномерно весь год.
• XYZ анализ покажет, что товар продается суперстабильно. Но он может продаваться стабильно плохо (на 100 рублей в месяц), и вы не заметите этого без ABC.

Именно совмещенный ABC XYZ анализ позволяет увидеть реальное состояние склада. Вы перестаете закупать «мертвый груз» и фокусируетесь на том, что реально кормит бизнес.

Термины на пальцах

Перед тем как открыть Excel, разберем три понятия:

• Коэффициент вариации - главная формула для XYZ. Показывает, насколько сильно скачут продажи от месяца к месяцу. Чем он ниже, тем стабильнее спрос.
• Накопительный итог - сумма показателей (выручки) от первого товара к последнему в отсортированном списке. Нужен для границ групп A, B и C.
• Период анализа - время, за которое мы берем данные. Для розницы это обычно 3, 6 или 12 месяцев.

Подготовка данных: ABC XYZ анализ в Excel

Чтобы построение матрицы ABC XYZ анализа прошло успешно, вам нужна чистая выгрузка из вашей учетной системы.

Какие столбцы нужны:

1. Артикул/Наименование - уникальный идентификатор товара.
2. Продажи по периодам - например, "Январь", "Февраль", "Март" (в штуках). Это нужно для XYZ.
3. Сумма продаж/Прибыль - итоговая цифра за весь период. Это нужно для ABC.

Важно исключить из выгрузки услуги (доставку), товары под заказ (если анализируете складскую программу) и новинки, которые продаются меньше 3 месяцев, так как у них еще нет статистики для Z-класса.

Как сделать ABC XYZ анализ: пошаговый алгоритм

Давайте разберем, как сделать расчет в Excel без сложных макросов. Мы пойдем от простого к сложному.

Шаг 1. Считаем группы ABC

Этот этап отвечает за деньги. Обычно мы делим товары так: группа А - это 80% выручки, В - следующие 15%, С - оставшиеся 5%.

1. Отсортируйте список товаров по убыванию столбца «Сумма продаж» (от большего к меньшему).
2. Посчитайте общую выручку по всем товарам.
3. Создайте столбец «Доля». Формула: =Продажи_товара / Общая_выручка.
4. Создайте столбец «Накопительная доля». Для первой строки это просто доля. Для второй: =Доля_текущая + Накопительная_предыдущая.
5. Присвойте буквы. Можно использовать формулу ЕСЛИ: =ЕСЛИ(Накопительная<0,8; "A"; ЕСЛИ(Накопительная<0,95; "B"; "C")).

Теперь у вас готов классический отчет ABC анализа. Переходим к стабильности.

Шаг 2. Считаем группы XYZ

Здесь нам важен коэффициент вариации в ABC XYZ анализе. Он показывает разброс данных.

1. Вам понадобятся данные о продажах по месяцам (столбцы Янв, Фев, Мар и т.д.).
2. Посчитайте средние продажи за период. Формула в Excel: =СРЗНАЧ(диапазон_месяцев).
3. Посчитайте стандартное отклонение. Формула: =СТАНДОТКЛОН.В(диапазон_месяцев).
4. Вычислите коэффициент вариации. Формула: =Стандартное_отклонение / Среднее_значение.
5. Присвойте группы. Классические границы:

• X (0–10%): Спрос почти не меняется.
• Y (10–25%): Есть колебания (например, сезонность или акции).
• Z (более 25%): Спрос непредсказуем.

Формула для Excel: =ЕСЛИ(Коэф_вариации<=0,1; "X"; ЕСЛИ(Коэф_вариации<=0,25; "Y"; "Z")).

Шаг 3. Финальная матрица ABC XYZ анализа

Чтобы получить совмещенный результат, просто объедините итоги двух колонок. Создайте новый столбец с формулой =Сцеп(Ячейка_ABC; Ячейка_XYZ). Вы получите значения: AX, AY, AZ, BX... и так далее.

Частые ошибки при расчете

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

1. Нулевые остатки (OOS). Если товара не было на складе полмесяца, продажи упали. Excel посчитает это как падение спроса (группа Z), хотя спрос был, просто не было товара. Перед анализом нужно очищать данные от дней отсутствия.
2. Сезонность. Если вы делаете анализ товара с яркой сезонностью (елки, купальники) на годовом отрезке, они почти всегда улетят в группу Z из-за скачков продаж зимой и летом. Такие товары анализируют внутри сезона.
3. Слишком короткий период. Нельзя строить анализ ассортимента на данных за 2 недели. Минимальный горизонт для достоверности - 3-4 периода поставки.

Правильно настроенная матрица показывает, где лежат ваши деньги, а где вы просто оплачиваете аренду полки для пыльного товара.