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

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

  • Категория A – первые 80% суммы
  • Категория B – следующие 15%
  • Категория C – оставшиеся 5%

Второй подход – XYZ – это классификация тех же показателей на стабильность (надежность) с помощью коэффициента вариации (читайте о нем в этой статье):

  • Колебания менее 10% — X (стабильный)
  • 10-25% — Y (умеренно колеблющийся)
  • Свыше 25% — Z (сильные колебания, ненадежный)

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

ABC XYZ анализ номенклатуры

Пусть у нас есть номенклатура, в которой нужно выявить, какие товары «кормят» компанию, а какие – лишь замораживают оборотные средства. ABC XYZ анализ в логистике, закупках и продажах раскрывается наилучшим образом.

Для XYZ-анализа выгрузим из базы данных продажи за сезонные 5 месяцев (в валюте, помесячно). Для ABC – суммарную прибыль, которую принесли товары за тот же период.

таблица продаж

Следуем последовательности:

  1. Выполним ABC-анализ полученной прибыли:
    • Сортируем столбец B (Прибыль) по убыванию

      сортировка таблицы

    • В отдельной колонке считаем долю каждого товара в суммарной прибыли. Не забываем применить к столбцу процентный формат:

      доля от суммарного числа

    • В следующей колонке вычислим нарастающий процент, т.е. сумма доли товара и всех предыдущих долей:

      нарастающая доля

    • В следующем столбце распределим все товары на категории согласно нарастающей доле: =ЕСЛИ(J3<=80%;«A»;ЕСЛИ(J3<=95%;«B»;«C»))

      категории ABC

    • С помощью условного форматирования Выделим категорию А зелёным, B – жёлтым, C – красным

      условное форматирование ABC

  2. Проведем XYZ-анализ продажи на предмет устойчивости
    • В новой колонке вычислим коэффициент вариации продаж: =СТАНДОТКЛОН.Г(C3:G3)/СРЗНАЧ(C3:G3)

      коэффициент вариации

    • В последнем столбце выполним XYZ-распределение на основе коэффициента вариации: =ЕСЛИ(M3<=10%;«X»;ЕСЛИ(M3<=25%;«Y»;«Z»))

      категории XYZ

    • Применим условное форматирование, выделим X – зеленым, Y – жёлтым, Z – красным.

      условное форматирование XYZ

  3. Отсортируем столбцы ABC и XYZ по алфавиту

    сортировка результатов

  4. Скроем лишние столбцы и посмотрим, какая получилась картинка

    результат анализа ABC-XYZ

  5. Можно свести результаты в такой вид:

    свод ABC-XYZ

Какие выводы можно делать по этой таблице? Пройдемся по группам:

  • AX – товар стабильно приносит значительную прибыль. Позаботьтесь о наличии страхового запаса
  • BX и AY – хорошая продукция для сопутствующих продаж
  • CZ – позиции, которые не имеют стабильного спроса и не приносят прибыль. Можно исключать из ассортимента
  • BZ и CY – очень слабое влияние на коммерческую деятельность
  • Остальные – промежуточные звенья, не хорошие, и не плохие. Желательно переводить на предзаказ

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

Сегодня у меня всё. Как всегда, готов отвечать на вопросы, шлю лучики благодарности всем, кто советуем мои статьи друзьям и знакомым. До встречи!