ABC/XYZ-анализ закупок в Excel
Готовый Excel-шаблон: классифицирует сырьё или номенклатуру по вкладу в затраты (ABC) и стабильности потребления (XYZ). Замените примерные строки на свои данные — расчёт и рекомендации автоматические.
Девять управленческих групп вместо одного списка позиций
ABC-анализ сам по себе показывает только вклад в затраты. XYZ сам по себе — только стабильность потребления. По отдельности оба часто вводят в заблуждение: дешёвая, но абсолютно непредсказуемая позиция (CZ) требует не меньше внимания закупщика, чем дорогая и стабильная (AX) — просто другого.
Шаблон считает оба среза сразу и сводит их в матрицу из девяти групп — от AX (дорого и стабильно: рамочный контракт, SLA) до CZ (дёшево и хаотично: рационализация, разовые закупки) — с готовой управленческой рекомендацией для каждой.
Закупщик производственного и торгового профиля
12 месяцев потребления и цена за единицу — больше ничего не нужно
На листе «Данные» — код, наименование, категория, поставщик, единица измерения, цена и потребление по 12 месяцам. Лист «ABC_XYZ» автоматически считает годовую стоимость, долю и накопленную долю (ABC), затем среднее, стандартное отклонение и коэффициент вариации CV = стандартное отклонение / среднее (XYZ).
Пороговые значения — не догма: ABC A до 80% накопленной стоимости, B до 95%; XYZ X — CV ≤ 10%, Y — 10–25%, Z — выше 25%. Их можно поменять на листе «Настройки» под структуру вашей закупки.
Если данных меньше 6 месяцев, позиция помечается «Недостаточно данных» вместо ложной классификации по случайному всплеску закупки — это защита от типичной ошибки: XYZ, посчитанный по трём месяцам, почти всегда врёт.
| Группа | Смысл | Решение для закупки |
|---|---|---|
| AX | Дорогое и стабильное | Рамочный контракт, SLA, страховой запас, регулярные переговоры |
| AZ | Дорогое и непредсказуемое | Разбор причин, резервные поставщики, управляемый запас |
| BX/BY | Средняя стоимость | Стандартный контракт, периодический пересмотр цены |
| CX | Дешёвое и стабильное | Канбан, VMI, автозаказ — минимум внимания |
| CZ | Дешёвое и хаотичное | Рационализация, замены, разовые закупки, чистка номенклатуры |
12 строк сырья пивоварни — от солода до этикетки
В шаблоне уже есть демонстрационный набор: солод, хмель, дрожжи, сахар, упаковка и химия CIP-мойки с реалистичной, но условной динамикой потребления. Хмель, например, попадает в группу AZ — дорого и нестабильно из-за сезонности сортов, а этикетка — в BX: заметная сумма, но предсказуемый расход.
Замените эти 12 строк на свою номенклатуру, и лист «ABC_XYZ» пересчитает всё автоматически — формулы не нужно трогать.
Чего файл не делает
- XYZ корректнее считать по фактическому расходу в производство, а не по датам закупки — если в данных только закупки, сезонность заказа исказит оценку стабильности.
- Не учитывает срок годности, MOQ поставщика и логистические ограничения — это входные данные для решения, не часть формулы.
- Классификация — отправная точка для приоритизации, не автоматическое решение о контракте.
Частые вопросы
Обычный ABC смотрит только на долю в затратах. Этот шаблон добавляет XYZ — стабильность потребления — и даёт готовую матрицу из девяти групп с управленческой рекомендацией для каждой, а не только процент от бюджета.
Файл использует стандартные функции (СРЗНАЧ, СТАНДОТКЛОН.Г) без макросов — открывается в любой современной версии Excel или в Google Sheets.
Да, пороговые значения ABC и XYZ вынесены на отдельный лист «Настройки» — их можно менять без правки формул.
Формулы построчные и масштабируются, но при больших объёмах и необходимости искать дубли номенклатуры и распределять категории между менеджерами быстрее сделать это через «Где мои деньги» — с бесплатным разбором данных, а не только классификацией.
Связанные материалы
Нужно разобрать реальную выгрузку, а не 12 примерных строк?«Где мои деньги» строит ABC-анализ, ищет дубли номенклатуры и ценовые отклонения по всей закупочной выгрузке — без ручной сборки формул.
Открыть инструмент →