ABC/XYZ-анализ в Excel: от продаж до матрицы ассортимента
Разбираем, как разделить товары по вкладу в результат и стабильности спроса, чтобы управлять закупками не по ощущениям.
Что показывает ABC/XYZ-анализ
ABC и XYZ отвечают на разные вопросы. ABC показывает экономическую значимость позиции: какую долю выручки, прибыли или оборота она формирует. XYZ показывает предсказуемость спроса: насколько сильно продажи колеблются от периода к периоду.
Вместе методы дают матрицу из девяти групп — от AX до CZ. Например, AX обычно означает важный товар со стабильным спросом, а CZ — позицию с небольшим вкладом и высокой нестабильностью. Такая классификация полезнее простого рейтинга продаж, потому что помогает принимать разные решения по запасам и закупкам.
Как сделать ABC-анализ
Сначала выберите показатель: чаще всего используют выручку, валовую прибыль или количество продаж. Отсортируйте товары по убыванию показателя, посчитайте долю каждой позиции в общем результате и накопительную долю.
Практичный ориентир: группа A формирует первые примерно 80% результата, B — следующие 15%, C — оставшиеся 5%. Это не закон: границы можно менять под ассортимент. В Excel доля позиции считается как значение строки / общий итог, а накопительная доля — суммой долей от первой строки до текущей.
Как сделать XYZ-анализ
Для XYZ нужны продажи по нескольким сопоставимым периодам — например, по неделям или месяцам. Для каждой позиции рассчитывают среднее значение, стандартное отклонение и коэффициент вариации: стандартное отклонение / среднее × 100%.
Чем ниже коэффициент вариации, тем стабильнее спрос. Часто X назначают стабильным позициям, Y — товарам с умеренными колебаниями, Z — нерегулярным. Пороговые значения лучше выбирать по реальному распределению вашего ассортимента, а не копировать без проверки из чужого шаблона.
Создать ABC/XYZ-анализ в Excel
Опишите ассортимент и периоды — SmartXLSX подготовит структуру расчёта и готовый XLSX.
Открыть инструмент →Как читать матрицу AX–CZ
AX и BX обычно требуют высокой доступности: они важны и достаточно прогнозируемы. AY и BY значимы, но запас для них стоит планировать осторожнее. AZ и BZ дают заметный вклад, однако спрос нестабилен — здесь особенно полезны причины всплесков, сезонность и ручной контроль закупок.
CX, CY и CZ не обязательно нужно удалять. Среди них могут быть товары для ассортимента, допродаж или новых направлений. Матрица — инструмент приоритизации, а не автоматическое решение о выводе товара.
Ошибки, которые искажают результат
Не смешивайте периоды разной длины и не сравнивайте товары, которые продавались разное количество времени, без корректировки. Новый товар с двумя неделями истории нельзя честно сравнить по вариативности с товаром, который продаётся год.
Ещё одна ошибка — делать ABC только по выручке, если товары сильно отличаются по марже. В таком случае полезно построить второй ABC по валовой прибыли и сравнить группы. Также проверяйте нулевые средние: коэффициент вариации для них теряет смысл.
Как оформить рабочий файл
Удобная книга состоит из листа с исходными продажами, расчётного листа и итоговой матрицы. В итогах достаточно SKU, названия, выручки или прибыли, ABC-класса, коэффициента вариации, XYZ-класса и объединённой группы.
Добавьте фильтры и условное форматирование, но не превращайте отчёт в разноцветную панель. Главная задача — быстро отфильтровать, например, все AZ-позиции и принять решение по закупке. SmartXLSX может собрать такую структуру под ваши периоды и показатели автоматически.
Частые вопросы
Сколько периодов нужно для XYZ?
Чем больше сопоставимых периодов, тем надёжнее оценка. Для оперативного анализа часто берут 8–12 недель или 6–12 месяцев, учитывая сезонность бизнеса.
ABC лучше считать по выручке или прибыли?
Зависит от задачи. Для оборота — по выручке, для экономической ценности — по валовой прибыли. Полезно сравнивать оба варианта.
Можно анализировать Wildberries и Ozon?
Да. Нужна история продаж по SKU и периодам; при необходимости можно использовать прибыль вместо выручки.