Xyz-анализ: особенности, преимущества, этапы и выводы

Проведение XYZ-анализа

Выполнение XYZ-анализа предполагает следующую последовательность действий:

• Выбор объекта и изучаемого параметра,
• Определение временного периода для изучения изменения параметра в выбранных объектах,
• Расчет коэффициента вариативности по каждому объекту,
• Сортировка объектов на основании выявленного коэффициента,
• Выделение трех основных групп объектов.

Объекты:

1. Х – устойчивая группа с коэффициентов до 10%,
2. Y – группа с изменчивым, но предсказуемым поведением с коэффициентом 10-25%,
3. Z – группа отличающаяся непредсказуемостью (случайные, спонтанные или разовые покупки) с коэффициентом выше 25%

В сводной матрице выделяются несколько групп клиентов:

Категория клиентов/продуктов, которых необходимо удерживать и развивать и переводить в категорию выше либо увеличивать товарные запасы на складе (AX, AY, BX, CX)
Категории клиентов/продуктов для анализа (АZ) это довольно нестабильная группа, которой требуется особое внимание, так как она может как улучшить, так и ухудшить показатели.
Категория снижения активности или отказа (CZ) клиенты/продукты, которые не приносят прибыли, но расходуют ресурсы.

XYZ-анализ – частота и стабильность потребности (спроса)

Логическим продолжением ABC-анализа является XYZ-анализ. Основная цель этого вида анализа – определить частоту и стабильность потребности в том или ином материале. Все запасы разделяются на три группы в зависимости от устойчивости спроса на ту или иную позицию ассортимента. Устойчивость спроса исчисляется через коэффициент вариации.

Пример XYZ-анализа материальных ресурсов производственного предприятия

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

Таблица “Расчет коэффициента вариации по видам материалов”:

К группе X относятся позиции номенклатуры, имеющие постоянный и стабильный спрос (коэффициент вариации не более 10-15%), к группе Y – позиции, спрос на которые подвержен колебаниям (коэффициент вариации 15-25%), к группе Z – Объекты, спрос на которые имеет случайный характер (коэффициент вариации более 25%).
По результатам расчетов в группу X попадают материалы с коэффициентом вариации 0-11.52%, в группу Y – альтакс с коэффициентом вариации 17.58% и каолин (16.45%), а в группу Z – материалы с коэффициентами вариации 26.5 – 37.97%.Таблица “Распределение материалов по группам в зависимости от устойчивости потребности в них”:

XYZ анализ в логистике. Инструменты для облегчения работы

Как можно облегчить работу по созданию отчета и XYZ анализа? Используйте специальные инструменты и сервисы:

• Excel – самый популярный и доступный инструмент для отчетов. Но – многие данные приходится вносить и править вручную и на этих этапах теряется много времени.
• Сложные учетные системы – отличный вариант: многофункциональны, етсь хранение данных, присутствуют визуализации. Но – если в вашей компании таких систем нет, то нужно время на внедрение и бюджет на IT-специалистов, которые будут работать с системами и строить для вас отчеты.
• В последнее время становятся популярны «легкие» отчетно-аналитические системы (типа Tableau).

Такие системы называют легкими, потому что:

• Легко устанавливать на любое устройство – за 2 минуты.
• Легко освоить – программа специально разработана для тех, кто не обладает специальными техническими знаниями, но хочет делать красивые и понятные отчеты.
• Легко начать строить свои отчеты самостоятельно – без помощи программистов!

ABC-анализ применяется в бизнесе для следующих целей

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

Интерпретируем результаты XYZ анализа товарного ассортимента

Теперь нужно понять, что же мы такое посчитали.

Коэффициент вариации нужно понимать следующим образом: чем он ниже, тем более стабильный спрос на товар.

Зная коэффициент вариации по каждому товару, можно разбить их на группы:

Группа Х — стабильный спрос, объем продаж от месяца к месяцу почти не изменяются: КВ < 0,1.

Группа Y — условно стабильный спрос, т. е. объемы продаж меняются, но без резких скачков: 0,25 > КВ > 0,1.

Группа Z — спрос нестабилен, покупатели приобретают товар от случая к случаю: КВ > 0,25.

Теперь нам осталось отсортировать нашу таблицу по столбца “Коэффициент вариации” по возрастанию.

Для этого выделим всю таблицу с заголовками, и выберем вкладку Данные — Сортировка. В выпадающем списке “Сортировать по” нужно выбрать “Коэффициент вариации”, Сортировка — Значения, Порядок — по возрастанию.

И в нашем примере получается следующая картина.

Таким образом, мы видим, что грабли, которые в АВС-анализе попали в категорию В, показывают самый устойчивый спрос. Именно поэтому у нас постоянная нехватка этого товара. Теперь мы понимаем, что грабли нам нужно всегда иметь на складе в достаточном количестве.

А мотокультиваторы, которые принесли существенную выручку, оказались в самом низу таблицы, показав самый непостоянный спрос. Теперь мы понимаем, что такой товар нужно покупать на склад штучно, либо вообще под заказ покупателя.

На этом простом примере мы рассмотрели возможности XYZ-анализа. Однако, как уже написано выше, этот анализ предпочтительно сочетать с ABC-анализом. 

Вам может быть интересно:

• Основные методы начисления амортизации формулы
• Медиана, среднее арифметическое и мода – как посчитать в Excel
• Точка безубыточности в Excel при помощи Подбора параметра
• Что такое прибыль. Виды прибыли
• Как найти процент выполнения плана формула Excel

Планы роста

Оборот Регионального лизингового центра до мая 2017 года включительно составлял  1,7 млн руб. Курс в Oy-li Андрей Пастухов проходил в июне-июле. Развитие отдела продаж с учетом новым знаний увеличило выручку практически сразу: «В первый месяц после обучения мы получили 8-кратный рост за месяц. Мы такую выручку прежде ни разу не получали! – радуется результатам Андрей. – В период с августа до конца года оборот превысил 2,15 млн руб. Если сравнивать с периодом до обучения, то рост – более 25%».

В 2018 году в компании ставят цель достичь оборота 14 млн руб. «На текущий момент план выполнен на 36%, в принципе, если продолжим двигаться такими темпами, то к нужной сумме точно придем, — уверен Андрей. – Отмечу, что если раньше с выполнением плана за месяц случались провалы, то сейчас показатели выполняются всегда. А в последние 4 месяца идет почти 2-кратное перевыполнение плана. Отличные результаты!»

Этапы XYZ-анализа

1-й шаг. Выбираем объект анализа (группа, категория, позиция) и параметр, по которому будем сравнивать объекты (в нашем примере – продажи за год). Традиционно в рознице объектами XYZ-анализа являются товарная категория или товарная единица. За основу анализа берется период продаж не менее трех месяцев.

2-й шаг. Определяем количество периодов, по которым будет проводиться анализ: неделя, декада, месяц, квартал, сезон, полугодие, год. Чем больше период, тем лучше и вернее будет статистика спроса

Важно: для анализа надо брать не менее трех периодов, по которым ведется отчетность. Если товар имеет оборачиваемость более месяца, надо взять период, как минимум в три раза превышающий оборачиваемость

Например, оборачиваемость категории – 2 месяца. Тогда за основу надо взять период не менее 6 месяцев.

3-й шаг. Определяем коэффициент вариации – среднее квадратическое отклонение – для каждого объекта анализа. Коэффициент вариации – величина, показывающая, насколько продажа товара отклоняется от среднестатистической. Иными словами, он  показывает, стабилен спрос на товар или нет.

СПРАВКА

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

Коэффициент вариации используют для сравнения рассеивания двух и более признаков, имеющих различные единицы измерения. Коэффициент вариации представляет собой относительную меру рассеивания, выраженную в процентах. Формула для расчета коэффициента вариации можно представить в следующем виде:
 

Категории:

Категория X – это товары, характеризуются стабильностью продаж и, как следствие, высокими возможностями прогноза продаж. Коэффициент вариации не превышает 10%.  Колебания спроса незначительны, спрос на них устойчив, следовательно, можно по этим товарам делать оптимальные запасы и использовать математические методы прогноза спроса и оптимального запаса.

Категория Y – товары, имеющие колебания в спросе и, как следствие, средний прогноз продаж. Коэффициент вариации составляет от 10 до 25%. Отклонение от средней величины продаж существует, но оно колеблется в разумных пределах – 25%.

Категория Z – товары с нерегулярным потреблением, какие-либо тенденции отсутствуют, точность прогноза продаж невысокая. Коэффициент вариации превышает 25% и даже может быть более 100%. Это может быть группа товаров, которую привозят по заказу клиентов или недавно поступила в продажу.

Обратите внимание: как и в случае в АВС-анализом, это рекомендованные сочетания – можно делать допуск коэффициента вариации по группе, например,  Х – 0–15%, Y – 15–40%, Z – от 40%. Обратите внимание: можно увеличить число групп анализа

Обратите внимание: можно увеличить число групп анализа

Преимущества и недостатки

У совместного ABC XYZ анализа есть следующие преимущества:

• Широкий спектр применения. Его можно использовать для расчета показателей продаж, определения ключевых клиентов, спроса на товар, вклада продавцов в командную работу и т. п.
• Простота. Выполнить расчёты несложно и для них не нужно десять аналитиков — реально обойтись своими силами. Понадобится только таблица в Excel или любой другой подобной программе. Есть CRM-системы, которые считают заданные показатели в автоматическом режиме. При правильной настройке останется только нажать кнопку, чтобы экспортировать данные в нужном формате.
• Эффективность. Это базовое исследование, которое время от времени стоит повторять в каждой организации.

Как и любой другой метод, ABC XYZ анализ имеет свои недостатки:

• И снова простота. Он не даёт возможности составить всестороннюю картину.
• Не стоит использовать как прямое руководство к действию, например, тут же расставаться с клиентами, совершающими редкие покупки или избавляться от товаров со случайным спросом. Часто требуется дополнительный анализ другими методами.

Использование ABC XYZ анализа

Сфер применения ABC XYZ анализа в бизнесе существует множество:

• Прибыль. В частности можно рассматривать различные факторы, которые на неё влияют;
• Спрос на товары или услуги и его колебания;
• Складские остатки с последующей их оптимизацией;
• Клиенты и их разделение на крупных, средних и мелких по объёму совершаемых покупок;
• Поставки, например, анализ поставщиков;
• Внутренний анализ. Если показатели деятельности команды измеряются количественно, например, в отделе продаж, можно измерить вклад каждого продавца в выполнение плана;
• Дебиторская задолженность и её классификация.

Сквозная аналитика

 от 990 рублей в месяц

• Автоматически собирайте данные с рекламных площадок, сервисов и CRM в удобные отчеты
• Анализируйте воронку продаж от показов до ROI
• Настройте интеграции c CRM и другими сервисами: более 50 готовых решений
• Оптимизируйте свой маркетинг с помощью подробных отчетов: дашборды, графики, диаграммы
• Кастомизируйте таблицы, добавляйте свои метрики. Стройте отчеты моментально за любые периоды

Узнать подробнее

Совмещение ABC- и XYZ-анализа – эффективная политика управления запасами

В итоге совмещения ABC- и XYZ-анализа появляются девять групп материально-производственных запасов, каждую из которых характеризуют определенная степень влияния на величину затрат и устойчивость этого влияния.
Наибольшее внимание должно быть уделено контролю затрат на материалы из группы A-X (техуглерод П-514, мел, регенерат РШТ), так как они оказывают наиболее сильное и устойчивое влияние на общую величину материальных затрат.Таблица “Распределение материалов по группам в зависимости от степени влияния на величину затрат и устойчивости этого влияния”:

Эффективная политика управления запасами заключается в распределении усилий по контролю за наличием и движением определенных групп запасов в соответствии с их значением для результата деятельности компании.

Определимся с периодом анализа

Стандартно за период анализа берут 12 месяцев. Это связано с тем, что продажи многих товаров имеют сезонность, и чтобы учесть все возможные колебания, берут период, захватывающий все времена года.

Однако, этот подход достаточно спорный для управления складскими запасами. Если ваша компания не заполняет склад на год вперед, то период анализа 12 месяцев будет не очень показательным. 

Поэтому сначала вы понимаете, на сколько месяцев/недель вперед вы заполняете склад. В нашем примере нужно заполнить склад магазина дачных товаров на полгода (дачный сезон). И используете для анализа именно этот период.

Отсюда вытекает важное правильно анализа (в принципе, любого, не только XYZ-анализа): период анализа должен быть одинаковым для всех товаров!

Поэтому, если какой-то товар недавно появился в вашем ассортименте, и не попадает в весь анализируемый период — выбрасывайте его из общего анализа, поскольку данные по нему будут не корректными в сравнении с другими товарами. Такие товары нужно анализировать отдельно.

XYZ анализ в логистике. Как сделать такой анализ?

Задача XYZ анализа – разбить весь товар на 3 группы – в зависимости от равномерности спроса и возможности спрогнозировать этот спрос. Как же определить тот или иной товар в нужную группу? Необходимо рассчитать для него коэффициент вариации спроса:

где xi – значение спроса по товару за период,
х — среднее значение спроса за период n,
n – величина периода, за который проводится оценка,
v – коэффициент вариации спроса.

Рассчитываем такой коэффициент для каждой товарной позиции и потом, в зависимости от величины коэффициента, распределяем товары по группам:

• X – коэффициент 0-10% (спрос регулярный и прогнозируемый),
• Y – коэффициент 10-25% (спрос нерегулярный, но прогнозируемый),
• Z – коэффициент больше 25% (спрос нерегулярный и непрогнозируемый).

СЧИТАТЬ, НЕ ПЕРЕСЧИТАТЬ

Теоретический блок пройден, основы изучены. Можем переходить к самому расчёту. И у меня для Вас две новости.

Плохая – я не дам Вам формул для расчёта. Хорошая – я Вам дам готовую excel-таблицу для внедрения.

Поэтому объясню основные смыслы, а расчёт в экселе сделается самостоятельно, когда Вы проставите цифры.

1. Сбор информации

Если у Вас нет истории покупок, то Вы читаете эту статью на будущее, так как практический любой анализ делается на основе данных за прошлый период.

Поэтому для успешной реализации этих двух анализов Вам нужно собрать информацию. В зависимости от цели они могут быть разные, я же показываю Вам практически не сменных лидеров любой таблицы:

1. Клиенты/товары
2. Сумма продаж
3. Количество продаж
4. Маржинальность

Эти данные Вы собираете за выбранный промежуток времени. Чем он будет длиннее, тем лучше. Так как Вы будете видеть динамику компании.

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

2. Анализ данных

После того как Вы собрали всю информацию воедино, начинается самая скучная работа – это всё нужно расформировать по необходимым столбцам.

Процесс этот не быстрый, но тем не менее не требует особых знаний. Вы, конечно, можете пойти более умным путём и сразу сделать так, чтобы Ваша CRM-система выгружала такой готовый отчет. Как говорится, всё в Ваших руках. Главное, чтобы был результат.

Интеграция анализов

И уже затем, когда всё будет стоять на своих местах, Вы можете начинать делать выводы по столбцу, который называется “совмещенный”.

Именно он покажет Вам синергию ABC-анализа и XYZ-анализа. Как Вы уже догадались, для Вас самый лучшие клиенты и товары – это AX (покупают много и часто), а самые плохие – CZ/DZ (покупают мало и редко/нерентабельны).

Что делать с каждый группой, решайте сами. И не обязательно делать вывод, что нужно убирать “плохих” клиентов и товары.

Например, своему клиенту мы категорически запретили убирать товары, которые не приносят денег.

Так как знаем, что убрав эти продукты клиент будет вынужден их покупать у компании-конкурента, а значит так мы теряем с ним полный контакт и конкурент может его незаметно перетащить на основной (маржинальный) продукт

3. Обновление и миграция

Ваша компания развивается, у Вас меняются менеджеры, продукты, условия. Меняется всё, кроме Вас – руководителя ;-).

Поэтому нужно постоянно актуализировать базу и смотреть ситуацию в Вашей компании на данный момент времени. Промежуток изменений зависит напрямую от скорости изменений на рынке.

То же самое касается клиентов. Помимо того, что к Вам постоянно приходят новые, старые тоже меняются.

Одни вырастают до масштабов федеральных компаний и их закупки становятся похожими на уральские горы (большие и масштабные), другие наоборот становятся банкротами, так как не смогли справиться с конкурентами и новыми трендами маркетинга.

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

Это будет также показателем того, насколько Вы правильную работу ведёте с клиентами. Ведь их покупательская способность также зависит от того, как Вы с ними работаете.

Миграция клиентов

Чтобы оценить потенциал клиента для миграции в более “хорошую” группу, Вам нужно следовать двум стратегиям.

В B2B рынке Вам нужно узнать у своих клиентов что и сколько они покупают у конкурентов (+ что бы хотели покупать).

В B2C рынке просто нужно понять, сколько клиент берёт продукта у Вас (может он берёт у Вас 50% и 50% у конкурента), исходя из его нормы.

Что такое АВС-анализ?

В основе АВС-анализа лежит принцип Парето, который утверждает, что 80% результата дает всего лишь 20% вложений. Исходя из этого положения, АВС-анализ выделяет 3 категории факторов:

• А – 20 % наиболее ценных ресурсов, которые дают 80% результата,
• В – 30% ресурсов, которые приносят 15% результата,
• С – 50% ресурсов, которые дают всего лишь 5% результатов.

Это классический вариант анализа. Существует и более современная модель, которая предполагает добавление еще одной категории D и изменение некоторых характеристик:

• А – 50% от общего объема
• В – 30% от общего объема,
• С —  15% от общего объема,
• D – 5% от общего объема.

Если говорить о клиентах и доходах, которые они приносят компании, то идеальная картинка должна выглядеть следующим образом:

• Клиенты категории А приносят 80% общего дохода и составляют 20% по количеству от общего объема,
• Клиенты категории В приносят 15% от общего объема продаж и составляют 16%  от общего количества покупателей,
• Клиенты категории С приносят 5% от продаж и составляют 64% от общего количества.
• Если ваша картинка отклоняется от идеальной, то это говорить об управляемых продажах достаточно сложно, необходимо проанализировать ситуации и проработать свою клиентскую базу.

Пример XYZ анализа объемов продаж продукции в Excel

Для того чтобы лучше понять смысл XYZ анализа рассмотрим реальный пример. В нашем случае есть магазин сотовых телефонов и имеются объем продаж различных марок. Для корректного применения анализа необходимо, чтобы период продаж рассмотрения был не менее 4 месяцев.

Ассортимент и объем продаж продукции

Далее необходимо рассчитать изменчивость объема продаж по каждой товарной группе. На рисунке ниже показан итог расчета коэффициента вариации  по продукции. Формула расчета в Excel будет иметь следующий вид:

Коэффициент вариации объемов продаж =СТАНДОТКЛОН(B5:G5)/СРЗНАЧ(B5:G5)

Расчет коэффициента вариации продаж товаров

Сейчас необходимо классифицировать товары в группу – “X”,”Y” или “Z”. Для этого напишем формулу определяющую класс товара, и воспользуемся встроенной формулой «ЕСЛИ» в Excel. Формула будет иметь следующий вид:

Группа товара =ЕСЛИ(H5<10%;”X”;ЕСЛИ(H5<25%;”Y”;”Z”))

Группировка продукции по классам XYZ в Excel

Чтобы было наглядно видно структуру устойчивости продаж необходимо провести группировку по классам: главное меню Excel → «Данные» → «Сортировка».

XYZ анализ продаж продукции в Excel. Пример расчета

В результате мы получим следующую классификацию товаров. Телефоны «Lenovo» имеют самый устойчивый спрос, так как коэффициент вариации составляет всего 8%. То есть объем продаж в среднем по месяцам отклоняется на 8%

Компании следует уделить особое внимание складским запасам данных товаров. В группу «Y» попали довольно много различных видов продукции, компании следует оперативно отслеживать складские запасы. Товары группы «Z» продаются не регулярно,  их запасы могут быть сокращены и фирма может работать по предзаказу

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

Товары группы «Z» продаются не регулярно,  их запасы могут быть сокращены и фирма может работать по предзаказу. XYZ анализ хорошо себя зарекомендовал на практике управления запасами и ассортиментом товаров, также его часто объединяют с ABC анализом, который позволяет провести двухкритериальную группировку товаров. Более подробно о ABC анализе вы можете узнать в моей статье: “ABC анализ продаж. Пример расчета в Excel“.

Резюме

Метод XYZ анализа позволяет прогнозировать устойчивость спроса на продукции, объемы продаж и запасов. Использование метода высвобождает дополнительные ресурсы компании и оптимизирует их бизнес-процессы.

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Оценка стоимости бизнеса	Финансовый анализ по МСФО	Финансовый анализ по РСБУ
Расчет NPV, IRR в Excel	Оценка акций и облигаций

Неликвиды на складе: Pro и Lite версия. Редактирование реквизита в таблице документа. Для конфигурации УТ 11.х, ERP 2.x, КА 2.х 1С Предприятие 8.3

Внешний отчет для анализа товаров, находящихся на складе без движения. Работает для конфигураций 1С УТ 11.х, ERP 2.x, КА 2.х.
Отчет отображает товары на складах, движения по которым не производились в течении заданного периода дней.
Подключается как внешний отчет в конфигурации 1С Управление торговлей 11.
В Pro версии можно установить общий реквизит номенклатуры, для дальнейшего включения в отбор в установке цен номенклатуры.
Редактирование в табличном документе ускоряет процесс работы с неликвидами номенклатуры на складах.
Отчет можно использовать для стоимостной оценки товаров на складах.
Полный доступ к тексту модулей и СКД.

1 стартмани

Что это такое?

АВС XYZ-анализ, как видно из наименования, состоит из двух частей. По сути, это два вида анализа, которые целесообразно проводить во взаимосвязи, для получения объемной, полной информации по клиентам.

Спектр применения этих инструментов широк: АВС-анализ — контрагенты, запасы на складах, длительность периода продаж; XYZ анализ — отдельные товары, услуги, поведение клиентов; АВС XYZ-анализ — ассортимент и его прибыльность, целевая база клиентов и величина заказов, поставщики и величина поставок, «дебиторка» и величина задолженности в динамике.

В ходе АВС-анализа выделяют из однотипного массива объектов наиболее важные и полезные для бизнеса

На них необходимо обратить первоочередное внимание. Это наиболее ценные в настоящем времени клиенты. Результатом анализа станет разделение клиентов на категории по величине закупок, продаж

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

Таким образом, АВС XYZ-анализ позволяет выделить группы клиентов, приносящих больше всего выручки, средний и малый объем выручки; одновременно наглядно показывает, какие из них покупают товар постоянно, какие — изредка, а кто совершает лишь единичные покупки.

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

Почему одного только АВС-анализа недостаточно для эффективного управления бизнесом, и нужно использовать XYZ-анализ?

Представим ситуацию: мы провели аналитику и выделили товары группы А. Казалось бы, вот они — наши “курочки, несущие золотые яйца”. Затариваем склад этими товарами и грезим баснословными выручками… 

Однако, через несколько дней нам звонит завскладом или менеджер по продажам и кричит, что товара “Грабли” нет в наличии уже давно, при том, что спрос на него высок. Мы даем распоряжение — закупить товар Х! 

И тут выясняется, что товар “Грабли” в нужном количестве просто некуда положить. Полки склада заняты товаром “Мотокультиватор”, который почему-то покупают не так охотно. 

Мы чешем затылок: зачем мы накупили столько мотокультиваторов, если их покупают редко? А вот почему: товар “Мотокультиватор” попал в группу А, и мы решили сосредоточить на нем свои усилия. Но не учли маленькую деталь: в исследуемом периоде было продано только 3 мотокультиватора, и только в апреле. В группу А этот товар попал только потому, что у него высокая цена, которая существенно отразилась на выручке. А граблей мы продали 88 штук, и их покупали в течение всего сезона. Но грабли дешевые и поэтому попали только в группу В, товаров из которой мы закупили на склад значительно меньше.

Поэтому на помощь нам приходит XYZ-анализ.

XYZ анализ товарного ассортимента на примере

Рассмотрим пример проведения XYZ анализа товарного ассортимента в эксель и заодно рассмотрим его основные этапы.

ABC анализ в сводной таблице в Excel ассортимента по выручке

Для начала необходимо определиться с периодом анализа. Наиболее удачный вариант — взять в анализ 12 месяцев, чтобы учесть все сезонные колебания. Однако, иногда нужен анализ более короткого периода — например, в нашем примере для магазина садового инвентаря мы возьмем период 6 месяцев (наивысший спрос в дачный сезон). 

Далее нужно подготовить исходные данные для анализа. Это может быть “сырая” база с транзакциями, выгруженная из учетной системы, или уже обработанная для анализа таблица. 

В любом случае, если мы анализируем товарный ассортимент по выручке, исходная таблица должна содержать следующие данные:

• наименование товара или наименование группы товара — в зависимости от того, до какой степени детализации нужно провести анализ. Если ваш ассортимент огромен, то, возможно, целесообразнее будет анализировать товары по группам, а не по конкретным SKU.
• выручка по каждому товару или группе товаров.

Для анализа конкретно по выручке этих данных будет достаточно. Если хотите провести анализ по прибыли (или марже), то нужно иметь либо уже готовые данные по прибыли в разрезе товаров, либо издержки по каждому товару (себестоимость производства или стоимость закупки).

В нашем примере АВС-анализа таблица с исходными данными выглядит так.

В таблице содержится различная информация, но для анализа будем использовать только два столбца: Наименование товара и Стоимость.

1. Создадим сводную таблицу для АВС анализа

Для начала нужно создать сводную таблицу. Выделяем исходную таблицу вместе с заголовками, далее вкладка Вставка — Сводная таблица — выбираем на Новый лист.

В поле Строки помещаем наименование товаров, а в поле Значения — сумму по полю Стоимость.

Кстати, при добавлении данных в поле Значения по умолчанию считается количество (в большинстве версий эксель). Чтобы количество превратить в сумму, щелкните на стрелочке и выберите Параметры полей значений, и в открывшемся окне нужно выбрать операцию Сумма.

Как создать сводную таблицу

Также желательно убрать пустую строку внизу таблицы, которая всегда по умолчанию создается в сводных таблицах. Для этого в фильтре столбца снимите “галочку” с пункта (пусто).

Мы получили список товаров и суммы выручки за каждый из них.

2. Получим доли каждого товара

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

Для этого добавим столбец Стоимость в поле Значения еще раз, просто перетянув его еще раз.

По умолчанию у нас посчиталось количество значений. Как в предыдущем пункте, превратим количество в сумму.

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

Для этого щелкните правой кнопкой мыши в любом месте второго столбца с суммой и выберите: Дополнительные вычисления — % от суммы по столбцу.

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

3. Сортируем по убыванию доли выручки

Вспомним, что нам нужно получить в итоге АВС-анализа ассортимента — это разделить товары на категории по убыванию их полезности. 

Поэтому теперь нам нужно отсортировать список товаров по убыванию доли их выручки в общей  выручке. Таким образом, чтобы товары с самым большими долями сконцентрировались вверху.

Для этого щелкнем на фильтре столбца Названия строк (т.е. столбца с наименованиями товаров) и выберем Дополнительные параметры сортировки.

В окне сортировки нужно выбрать переключатель “по убыванию”, и в выпадающем списке выбрать столбец Доля, %. 

Чтобы было понятнее, мы отсортировали столбец Наименование товара по убыванию значений в столбце Доля, %. Столбец с суммой выручки также отсортировался, его дополнительно сортировать не нужно.

На этом этапе уже видно, какие товары попали в группы А, В и С. Однако, это можно увидеть без расчетов лишь потому, что таблица в нашем примере маленькая. А что если у ней сотни или тысячи строк?

4. Получаем долю выручки нарастающим итогом

Как в пункте 2, снова добавляем поле стоимость в поле Значения, и вместо показателя Количество указываем Сумма. Сразу лучше переименовать поле (в примере —  Доля нараст. итогом, %). 

Теперь опять щелкаем правой кнопкой мыши на любом месте нового поля — Дополнительные вычисления — % от суммы с нарастающим итогом в поле — появляется окно Дополнительные вычисления — нажимаем Ок.

В поле Доля нарастающим итогом считаются доли выручки из предыдущего столбца нарастающим итогом, на картинке показан смысл:

Мы практически достигли цели провести ABC анализ в сводной таблице в Excel. Ведь нам нужно было узнать, какие товары дают примерно 80% выручки, какие — еще 15% (т.е. от 80 до 95%), а какие оставшиеся 5% (от 95 до 100%). И поле “Доля нараст. итогом, %” это показывает.

Пример XYZ анализа объемов продаж продукции в Excel

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Бесплатный Экспресс-курс «Оценка инвестиционных проектов с нуля в Excel» от Ждановых. Получить доступ

Для того чтобы лучше понять смысл XYZ анализа рассмотрим реальный пример. В нашем случае есть магазин сотовых телефонов и имеются объем продаж различных марок. Для корректного применения анализа необходимо, чтобы период продаж рассмотрения был не менее 4 месяцев.

Ассортимент и объем продаж продукции

Далее необходимо рассчитать изменчивость объема продаж по каждой товарной группе. На рисунке ниже показан итог расчета коэффициента вариации  по продукции. Формула расчета в Excel будет иметь следующий вид:

Коэффициент вариации объемов продаж =СТАНДОТКЛОН(B5:G5)/СРЗНАЧ(B5:G5)

Расчет коэффициента вариации продаж товаров

Сейчас необходимо классифицировать товары в группу – «X»,»Y» или «Z». Для этого напишем формулу определяющую класс товара, и воспользуемся встроенной формулой «ЕСЛИ» в Excel. Формула будет иметь следующий вид:

Группа товара =ЕСЛИ(H5<10%;»X»;ЕСЛИ(H5<25%;»Y»;»Z»))

Группировка продукции по классам XYZ в Excel

Чтобы было наглядно видно структуру устойчивости продаж необходимо провести группировку по классам: главное меню Excel → «Данные» → «Сортировка».

XYZ анализ продаж продукции в Excel. Пример расчета

В результате мы получим следующую классификацию товаров. Телефоны «Lenovo» имеют самый устойчивый спрос, так как коэффициент вариации составляет всего 8%. То есть объем продаж в среднем по месяцам отклоняется на 8%

Компании следует уделить особое внимание складским запасам данных товаров. В группу «Y» попали довольно много различных видов продукции, компании следует оперативно отслеживать складские запасы

Товары группы «Z» продаются не регулярно,  их запасы могут быть сокращены и фирма может работать по предзаказу. XYZ анализ хорошо себя зарекомендовал на практике управления запасами и ассортиментом товаров, также его часто объединяют с ABC анализом, который позволяет провести двухкритериальную группировку товаров. Более подробно о ABC анализе вы можете узнать в моей статье: «ABC анализ продаж. Пример расчета в Excel«.

Резюме

Метод XYZ анализа позволяет прогнозировать устойчивость спроса на продукции, объемы продаж и запасов. Использование метода высвобождает дополнительные ресурсы компании и оптимизирует их бизнес-процессы.

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Бесплатный Экспресс-курс «Оценка инвестиционных проектов с нуля в Excel» от Ждановых. Получить доступ

Оценка стоимости бизнеса	Финансовый анализ по МСФО	Финансовый анализ по РСБУ
Расчет NPV, IRR в Excel	Оценка акций и облигаций

Итоги

Проводить АВС и XYZ –анализ можно по специальным формулам, но гораздо проще это сделать в готовой excel таблице, в которую достаточно просто вставить свои данные.

АВС и XYZ –анализ позволяют понять:

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

Метод АВС и XYZ –анализ наглядный и эффективный, но все же имеет свои минусы. Возможно они не такие критичные, но о них стоит знать:

• Простота. Все данные, которые выдает анализ в принципе лежат на поверхности, просто упорядочиваются. Поэтому эта методика не дойдет для глубокого анализа и аналитики.
• Только цифры. Расчет производится только на основе цифр, а это может не учитывать ряд дополнительных фактором, поэтому дополнительно придется еще анализировать и головой.
• Сложность прогнозирования, так как не учитываются внешние факторы. Анализ показывает динамику ситуации из прошлого в настоящее, но прогнозировать что-то сложно, так как не учитывается экономические особенности, изменение покупательской способности, возникновение конкурентов и так далее.

Благодаря совмещенному АВС и XYZ-анализу есть возможность управлять ассортиментом, клиентской базой, персоналом и любыми другими измеримыми показателями. Чаще всего, конечно, он применяется для управления продажами.

Итоги

Приведем еще пару ситуаций, в которых пригодится: 

1. Запасы материалов в строительной компании. Потребность в некоторых видах сырья сильно колеблется по периодам. Закупают слишком много – оно простаивает, из-за чего приходится снимать еще один склад. Закупают слишком мало – оно неожиданно заканчивается, что отрицательно сказывается на сроках. 
2. Клиенты. Фирме понадобилось выявить нестабильных клиентов. Именно им будет предложена особая акция: скидка при повторном заказе через 30 дней после предыдущего.

В целом xyz-анализ подойдет для разбора любых трат и любых доходов. Вводите его как можно скорее.