Узнайте, как провести ABC XYZ анализ в Excel для классификации ассортимента. Расчет матрицы поможет выявить ключевые товары и неликвид для оптимизации управления запасами.
ABC XYZ анализ в Excel — это инструмент классификации ассортимента, который позволяет разделить товары на группы по степени важности (вклад в выручку или прибыль) и предсказуемости спроса. Совмещая эти две методики, вы получаете матрицу из девяти ячеек, которая дает прямой ответ: какие позиции нельзя упускать из виду, а какие только морозят деньги на складе. Мы в БрайтБорд рекомендуем начинать наведение порядка в закупках именно с этого расчета.
Без четкой структуры управление запасами превращается в интуитивное гадание. Вы закупаете лишний товар, опасаясь дефицита, но на полках все равно появляются пустые места по ходовым позициям, а неликвид годами пылится в углу. Этот шаблон расчета поможет увидеть перекосы и перестать инвестировать в «мертвый» груз.
Ручной расчет в Excel полезен для старта, но при большом ассортименте он отнимает часы рабочего времени. Попробуйте БрайтБорд для автоматизации классификации и автозаказа.
Прежде чем открывать таблицы, разберемся, что именно мы ищем. Новички часто путают эти два метода или используют их по отдельности, теряя половину смысла.
Чтобы говорить на одном языке, определим ключевые понятия:
Начнем с ранжирования по важности. Вам понадобится выгрузка продаж за определенный период, например, за 3 или 6 месяцев.
Создайте таблицу со следующими столбцами:
Теперь используем формулу ABC анализа в Excel. Границы обычно определяют так:
Используйте функцию ЕСЛИ (IF), чтобы автоматически проставить букву в соседнем столбце в зависимости от значения накопительной доли.
Переходим к оценке стабильности. Для этого нам недостаточно одной цифры «итого», нужна детализация продаж по периодам (неделям или месяцам).
Добавьте к списку товаров данные о продажах в штуках за каждый месяц или неделю анализируемого периода. Например, 6 столбцов: «Январь», «Февраль» и т.д.
Здесь понадобятся две формулы:
СРЗНАЧ (AVERAGE) по столбцам с продажами за месяцы.СТАНДОТКЛОН.В (STDEV.P) по тем же столбцам.Коэффициент вариации в ABC XYZ анализ считается так: разделите «Стандартное отклонение» на «Среднее значение». Вы получите дробное число. Переведите его в проценты для наглядности.
Классические границы для ритейла выглядят так, но могут меняться в зависимости от отрасли:
Снова используйте функцию ЕСЛИ, чтобы Excel сам проставил буквы на основе полученного коэффициента.
Сложно поддерживать актуальность данных при тысячах SKU? БрайтБорд делает ABC XYZ анализ автоматически, обновляя статусы товаров каждый день без участия менеджера.
Теперь у вас есть два столбца с литерами: один с A/B/C, второй с X/Y/Z. Объедините их в третьем столбце с помощью функции сцепки (=A1&B1), чтобы получить финальные статусы: AX, BY, CZ и так далее.
Слепо верить формулам опасно. Проверьте результат глазами:
Важно помнить, что этот отчет — фотография прошлого. Если в прошлом месяце был сезонный всплеск или крупный оптовый заказ, товар может временно мигрировать в группу А или Y. Управление запасами требует регулярного пересчета матрицы, а не разовой акции.
Когда отчет ABC XYZ анализ продаж готов, разделите стратегии работы:
Первый делит товары по их вкладу в результат (выручку, прибыль), показывая «важность». Второй оценивает стабильность спроса и предсказуемость продаж. Вместе они дают объемную картину для управления запасами.
Полноценный XYZ сделать не получится, так как для оценки стабильности нужна динамика и разбивка по периодам. Без этого вы сможете провести только ABC ранжирование по итоговому результату.
Оптимально — от 3 до 12 месяцев. Слишком короткий период даст много статистического шума, а слишком длинный (год) может сгладить важные сезонные колебания.
Зависит от динамики вашего рынка. Для продуктов питания — раз в месяц или даже неделю. Для запчастей или стройматериалов — раз в квартал. Автоматизированные системы делают это ежедневно.
Это товары с нерегулярным спросом или единичными продажами. Коэффициент вариации здесь очень высок. Прогнозировать их математически сложно, высок риск ошибки прогноза.
Да, методика универсальна. Вместо товаров берем список клиентов. Группа А — это ключевые заказчики, приносящие основную выручку. Группа X — те, кто покупает регулярно и стабильно.
Рассказываем, как работает распределение запасов для балансировки остатков в сети. Узнайте, как планировать перемещения, чтобы разморозить деньги без лишних затрат на логистику.
Рассказываем, как проводится ревизия товарной матрицы на основе ABC/XYZ-анализа. Узнайте, как выявить неликвид и провести ротацию позиций для освобождения оборотных средств.
Читайте, как провести ABC XYZ анализ в Excel для оценки стабильности спроса. Пошаговый алгоритм расчета поможет найти неликвид и сфокусироваться на прибыльных товарах.