Abc анализ

XYZ-анализ: пример расчета в Excel

Данный метод нередко применяют в дополнение к АВС-анализу. В литературе даже встречается объединенный термин АВС-XYZ-анализ.

За аббревиатурой XYZ скрывается уровень прогнозируемости анализируемого объекта. Этот показатель принято измерять коэффициентом вариации, который характеризует меру разброса данных вокруг средней величины.

Коэффициент вариации – относительный показатель, не имеющий конкретных единиц измерения. Достаточно информативный. Даже сам по себе. НО! Тенденция, сезонность в динамике значительно увеличивают коэффициент вариации. В результате понижается показатель прогнозируемости. Ошибка может повлечь неправильные решения. Это огромный минус XYZ-метода. Тем не менее…

Возможные объекты для анализа: объем продаж, число поставщиков, выручка и т.п. Чаще всего метод применяется для определения товаров, на которые есть устойчивый спрос.

Алгоритм XYZ-анализа:

1. Расчет коэффициента вариации уровня спроса для каждой товарной категории. Аналитик оценивает процентное отклонение объема продаж от среднего значения.
2. Сортировка товарного ассортимента по коэффициенту вариации.
3. Классификация позиций по трем группам – X, Y или Z.

Критерии для классификации и характеристика групп:

1. «Х» — 0-10% (коэффициент вариации) – товары с самым устойчивым спросом.
2. «Y» — 10-25% — товары с изменчивым объемом продаж.
3. «Z» — от 25% — товары, имеющие случайный спрос.

Составим учебную таблицу для проведения XYZ-анализа.

1. Рассчитаем коэффициент вариации по каждой товарной группе. Формула расчета изменчивости объема продаж: =СТАНДОТКЛОНП(B3:H3)/СРЗНАЧ(B3:H3).
2. Классифицируем значения – определим товары в группы «X», «Y» или «Z». Воспользуемся встроенной функцией «ЕСЛИ»: =ЕСЛИ(I3

В группу «Х» попали товары, которые имеют самый устойчивый спрос. Среднемесячный объем продаж отклоняется всего на 7% (товар1) и 9% (товар8). Если есть запасы этих позиций на складе, компании следует выложить продукцию на прилавок.

Скачать примеры ABC и XYZ анализов

Запасы товаров из группы «Z» можно сократить. Или вообще перейти по этим наименованиям на предварительный заказ.

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

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

Программа Excel имеет в своем багаже инструменты, которые позволяют облегчить проведение такого рода анализа. Давайте разберемся, как ими пользоваться, и что же собой представляет ABC-анализ.

Основные функции табличного редактора Excel, позволяющие формировать данные для анализа результатов работы компании

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

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

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

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

Работают со сводными таблицами из вкладки меню функции «Анализ» (рис. 1). На этой вкладке также настраиваются параметры сводной таблицы и источники данных (откуда берется информация).

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

Обратите внимание!

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

Функция ВПР табличного редактора Excel помогает консолидировать данные для бизнес-анализа тем пользователям, которые недостаточно хорошо знают функционал сводных таблиц.

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

К сведению

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

Для начала работы с функцией ВПР ставим курсор в выбранную ячейку конечной таблицы и с помощью мастера функций выбираем значение ВПР — откроется диалоговое окно, в котором можно выбрать параметры исходной таблицы (рис. 2).

Для наглядности приведем еще пример локального применения функции ВПР при решении задачи построения оперативного бизнес-отчета из собственной практики.

Функция ЕСЛИ, предусмотренная функционалом Excel, также популярна у бизнес-аналитиков, но она применяется чаще всего не при анализе информации, а при построении различного рода прогнозов и сценариев результатов деятельности компании. Суть функции в том, что в заданной ячейке выводится один результат при выполнении определенного условия и другой — при невыполнении этого условия.

Инструмент для работы, а не волшебная палочка

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

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

Приведем ряд примечаний, которые должен иметь ввиду аналитик.

Результатом многомерного АВС анализа является сортировка товара по группировкам. Для трехмерного анализа таких групп будет 27, для двумерного 9. Аналитик должен хорошо ориентироваться в этом разнообразии групп, выделять важные группы, знать что необходимо применить к каждой из них. Следует отметить, что среди товаров могут встречаться не только те, которые плохо продаются, но и те, которые не продаются вообще или которые продаются в убыток. То есть товары, которые наносят урон похуже группы С. Для этого часто добавляют еще одну группу – D. Получается ABCD-анализ. Также в группу А может попасть более 50% ассортимента – в этом случае бывает полезно из группы А выделить подгруппу А+(Провести АВС анализ для группы А). Также следует помнить, что анализ производят за определенный период, и на основании данных этого периода строят прогноз на будущее. Здесь следует учитывать сезонные товары, которые хорошо продавались, например, зимой, и не учитывать их при заказе на летние сезоны. Также бывает, что некоторые позиции выпадают из продаж. Это происходит по разным причинам: нет у поставщика, ошибка в закупках и прочее. Тогда получается, что в течение некоторого времени товар не продавался и общий объём продаж будет ниже потенциально возможного. Если это позиция из группы А, то по результатам расчёта она легко может попасть в группу В или даже С.

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

Версия для печати  

ABC-анализ материалов

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

Цель такой классификации – повышение точности управления материалами, сокращение их запасов и логистических издержек.

Синонимами ABC-анализа являются: «принцип Парето», «правило 20/80», «первоначальный анализ».

При ABC-анализе потребности материалов исследуется доля и кумулята (нарастающий итог) материалов в общей потребности предприятия в материалах. Происходит распределение материалов на три группы:

• в группе А 20% номенклатуры материалов составляют 80% их объема в стоимостном выражении;
• в группе В 30% номенклатуры материалов – 15% их объема;
• в группе С 50% номенклатуры материалов – 5% их объема.

Указанный порядок классификации не является догмой. В зависимости от ситуации может быть и другая классификация, например, по группе А: 8/80, 15/85, 10/70 и т. п. Кроме того, приоритеты материалов нередко меняются, так как потребность в них, а порой и цены меняются, поэтому материалы могут перейти в другую группу. Таким образом, распределение материалов по группам носит динамический, а не статический характер.

Для материалов, новых для закупок, АВС-анализ производится на основе плановой потребности предприятия в материалах и действующих цен. Для уже потребляемых – на основе данных о поставках материалов в натуральном и стоимостном выражении за прошлый период.

Совмещение

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

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

Совмещенная таблица

Разделение проходит таким образом:

• продукция групп А и В составляет максимальный оборот предприятия. Лучше, чтобы такие товары всегда были в наличии;
• АХ и ВХ отображают постоянный спрос и большой оборот. Следует предусматривать регулярное пополнение и наличие, но не делать чрезмерный запас;
• AY и BY обладают недостаточной стабильностью расхода и необходимостью увеличения страхового запаса;
• AZ и BZ имеют высокий оборот, но малую прогнозируемость расхода. Рекомендуется откорректировать систему заказов продукции;
• группа С является основой 80% товарного ряда, поэтому использование XYZ-анализа способно снизить сроки, которые тратятся сотрудниками на контроль и управление продукцией, находящейся в группе;
• СХ и продукция этой группы применяется для системы заказов с регулярной периодичностью и снижением страхового запаса продукции;
• CY характеризуется возможностью перехода на систему с постоянным объемом заказов с созданием страхового запаса;
• CZ составляет перечень новой продукции, а также товары периодичного и спроса под заказ. Их можно удалять из перечня продаваемых и следует регулярно держать на контроле, потому что они являются неликвидными или сложно реализуемыми и могут приводить к убытку.

Анализ ABC позволяет понять на каких объектах бизнеса требуется сосредоточить наибольшее внимание. Используется определенный порядок расчета и выполнения

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

Аналитический способ ABC-анализа

Ниже представлен аналитический способ АВС-анализа по касательным. Метод включает в себя следующие шаги:

1. Определить цели анализа.
2. Определить объекты и факторы анализа.
3. Собрать и подготовить данные для АВС-анализа.
4. Отсортировать набор данных в порядке убывания значения фактора.
5. Рассчитать следующие параметры, необходимые для построения кривой Парето:
   • рассчитать порядковые номера объектов i, где i∈;
   • рассчитать доли фактора каждого объекта в общей сумме факторов P_i;
   • рассчитать кумулятивную сумму долей факторов объектов F_i (если необходимо).

Примечание. Полученные значения F_i являются координатами точек кривой Парето по оси ординат.

Какие факторы можно проанализировать при помощи способа

Представленные способы анализов помогают проанализировать следующие показатели:

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

Наибольший и самый частый плательщик

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

• большие категории (А);
• средние (В);
• небольшие (С).

Определенного понимания, каких покупателей можно отнести к одному сегменту, а каких — к другому, не существует. Для специфика каждого вида деятельности и его масштабов применяется определенное разделение.

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

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

Количество покупателей в воронке продаж

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

Воронка продаж

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

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

Клиенты, которые требуют концентрации усилий

Клиент характеризуется тем, какое количество прибыли было от него получено, стабильность этой прибыли. Определить показатели наиболее эффективно помогает объединение XYZ и АБС анализов. Итоговые данные выделяют группы потенциальных покупателей, при работе с которыми можно применять различные методы отношений:

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

Кто и сколько должен

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

Порядок проведения

ABC-анализ клиентов

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

Почему именно соотношение 80% на 20% является оптимальным? Рассмотрим другие возможные ситуации.

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

Противоположная ситуация, когда 80% прибыли приносят 50-60% покупателей, также недопустима. В таком случае выходит, что половина или даже больше половины клиентов относятся к категории А и они требуют высокий уровень обслуживания. Для ведения такого бизнеса придется иметь больше продавцов, техники, расширять офис, в итоге это приведет к тому, что каждый из покупателей «элитарной» категории принесет фирме малую долю прибыли.

Вот почему необходимо стремиться к соблюдению принципа Парето. 15-20% прибыли на 80% клиентов следует принять за золотое правило, а отклонение от него — считать дисбалансом.

По прошлым данным или на будущее?

Частый вопрос: можно ли провести ABC анализ на будущее? Да, если есть программное обеспечение, которое позволяет строить прогнозы. Классический ABC анализ проводится на прошлый период, и в этом есть определённый риск. Мы сами выбираем интервал времени, за который проводим abc анализ для управления запасами. И в этот период ассортимент обновлялся: одни товары покидали его, другие поступали в продажу. Какие-то продавались на протяжении всего года, какие-то полгода, а какие-то только в сезон. Если мы выбрали для анализа несезонные месяцы для сезонных товаров, то они, скорее всего, попадут в группу С

При проведении abc-анализа по прошлым данным важно выбрать правильный период. 

Чтобы не попасть в эту ловушку, можно проводить abc анализ на будущее и спрогнозировать спрос по разным товарам, выручку или прибыль исходя из тех данных, что у нас есть. Мы можем смоделировать, какие товары и в какую группу попадут в определённый период времени. Для этого нужен прогнозный модуль. А дальше принцип abc анализа такой же: строим прогнозы, сумму по критериям и разделяем товары на группы А, В и С. 

Применение abc анализа на будущее позволяет решить проблемы с дефицитами, акциями, введением товаров в ассортимент. Но проводить такой анализ сложнее, потому что он требует специального софта и занимает много времени. В Forecast NOW! предусмотрен переключатель «факт» либо «прогноз». Мы можем построить прогноз на будущее и посмотреть, в какую группу попадут товары по тем или иным критериям.

Резюмириуем:

• Мы рекомендуем проведение анализа abc по всему ассортименту. Для отдельных задач допустимо по разным поставщикам.
• Правильно проводить ABC анализ  по разным магазинам. При необходимости можно объединять между собой магазины по размерам и географии.
• Классический ABC анализ проводится на прошлом периоде. Если есть возможность и это не сильно затратно, можно проводить анализ на будущее. Обычно такой анализ проводится либо по выручке, либо по прибыли. 

О методе ABC-анализа

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

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

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

АВС-анализ товарного ассортимента

Правильная последовательность выполнения ABC-анализа обычно такая:

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

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

Выгрузить из базы данных численную информацию по товарам и выбранным показателям за длительный период. Обычно, 1-2 года

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

Если у вас более детальные данные, можно воспользоваться Сводными таблицами для приведения информации в нужный вид.

Для каждого показателя отдельно провести классификацию (анализ АБЦ). Я распределю товары на категории три раза: по выручке, по прибыли и по количеству чеков. Получится три отдельные таблицы.

Сведите полученные распределения в одну таблицу. Для этого можно использовать сводные таблицы или функцию ВПР. У вас получится так:

Теперь, сопоставляя собранную информацию, можно делать выводы. Например:

• Samsung Galaxy J2 Prime – при большой выручке, товар не принес значительной прибыли, было не так много чеков. Данная позиция, возможно, слишком дорогая для данного рынка сбыта и ее приходится распродавать без запаса маржи
• Xiaomi Redmi Note 5 – при небольшом количестве чеков, принес хорошую выручку и значительную прибыль. Товар оптимален с точки зрения экономики предприятия
• Xiaomi Redmi 6 – при средней выручке, приносит прибыль и активно покупается. Дешевая модель с высокой рентабельностью, которая распродается массово. Стоит внимательно относиться к запасам данного продукта

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

Проведенный нами АВС анализ товарного ассортимента – пример того, как правильная манипуляция показателями раскрывает суть процессов, а Microsoft Excel – выступает удобным инструментом для этого.

Кроме того, аналитики часто выполняют XYZ-анализ, или комбинированный ABC-XYZ для более глубокого понимания процессов. 

Кстати, все данные, приведенные в таблицах с примерами – вымышленные, не имеют ничего общего с реальностью, получены с помощью генератора случайных чисел и не отражают объективной реальности для перечисленных продуктов!

А у меня на этом всё. Если хотите что-то спросить, уточнить или добавить – пишите комментарии!

ABC анализ по касательным

Чтобы решить проблему выше, был разработан метод АВС анализа по касательным. Как его сделать?

1. Первый шаг –  такой же, как и в предыдущем методе. Мы сортируем товары по выручке, проценту суммарной выручке, которую обеспечил товар, и накопительному проценту 
2. Строим кривую. По оси Y – накопительное значение. По оси Х – количество товара в штуках (в процентах).
3. Соединяем точку начала и точку конца кривой и проводим касательную параллельно этой кривой (красные линии). 
4. Из точки касания проводим линию к концу графика и строим ещё одну касательную к этой линии (зелёные линии). 
5. Таким образом, мы разделили нашу кривую на три сектора – это и будут группы А,В,С. 

Суть XYZ анализа

Метод отвечает на вопрос: доход сформировался за счет стабильного спроса или разовой продажи элитной позиции.

В выгрузку ABC нужно добавить:

• данные по месяцам/кварталам;
• значения в натуральных единицах (продажи/списания в штуках) 

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

Каждому товару присваивают код. Значения кодов бизнес устанавливает сам учитывая особенности своих продуктов и покупателей.

Разные единицы измерения товаров (штуки, килограммы, литры и т.д.) не мешают расчетам.