Здравствуйте, в этой статье мы постараемся ответить на вопрос: «ABC и XYZ анализ в Excel с примером расчета товарного ассортимента». Если у Вас нет времени на чтение или статья не полностью решает Вашу проблему, можете получить онлайн консультацию квалифицированного юриста в форме ниже.
Если требуется понять, на каких именно продуктах разумнее сконцентрировать усилия, то подойдет ABC-анализ. АВС-матрицу можно строить как для всего ассортимента, ранжируя только товарные группы, так и для каждой отдельно. В таком случае по АВС-категориям распределяются SKU.
Основной точкой расчета является прибыль на группу товаров или конкретный продукт. В таблицу Excel загружается весь перечень, который будет анализироваться, и данные по прибыли для каждого продукта. Затем они ранжируются от большего к меньшему. Далее применяется принцип Парето: только 20 % всех действий (в данном случае – всех товарных категорий) приносят 80% результата.
Анализируемые продукты делятся на три категории:
- А – лидеры продаж, которые принесли компании больше всего прибыли, от 0 до 80 % нарастающим итогом. Составляющих в категории А должно быть не более 20 % от общего числа анализируемых позиций;
- В – крепкие середнячки, которые обеспечивают постоянный приток денег в компанию. Во вторую категорию входит до 30 % от всех позиций, а размер прибыли по нарастающему итогу составляет от 81 до 95 %;
- С – аутсайдеры, которые являются наименее рентабельными. Прибыль от них составляет свыше 96 % по нарастающему итогу. Зато общее количество позиций в последней категории может быть равно половине от общего списка.
Сам по себе АВС-анализ не всегда отражает реальный спрос. Например, большую часть выручки может приносить дорогостоящий товар из премиум-коллекции. Но клиенты покупают его раз в месяц. Зато ежедневно они приходят за низкомаржинальным продуктом. Чтобы «собрать» в единую систему прибыль и спрос на товары, рекомендуется вместе с АВС проводить XYZ-исследование.
Второй анализ проводится по такому же алгоритму. Изучается та же товарная группа, но критерием для ранжирования становится не прибыль, а спрос, то есть частота покупок или количество проданных единиц. Далее продукция делится на три категории:
- X – те 20 % из ассортимента, которые составляют 80 % спроса;
- Y – те товары, которые покупают стабильно;
- Z – позиции, пользующиеся низким спросом.
Алгоритм проведения анализа товарной группы
Динамика продаж является неотъемлемой частью анализа любой товарной группы. Динамика анализируется по нескольким основным показателям: выручка, количество проданного товара, прибыль. Данные для динамики берутся с учетом данных прошлых лет (от 1 до 3, в зависимости от глобальности задачи). Т.е. если необходимо проанализировать продажи за последний квартал, то в совокупности берутся данные за аналогичный квартал прошлого года. Во-первых, это позволит увидеть текущую динамику, а во-вторых, выявить прирост или снижение в сравнении с прошлым годом.
Анализ чеков, так же одна из необходимых составляющих анализа группы товаров. Ранее уже упоминал об этом анализе, здесь же лишь добавлю, что также крайне желательно сравнение с прошлым годом.
ABC — анализ. Статья об авс анализе уже опубликована, её можно прочитать, перейдя по ссылке. Отмечу лишь, что при анализе товарной группы, ABC анализ логичнее проводить по двум параметрам, но это в зависимости от задачи. Обычно использую сочетание выручки и количество проданных единиц товара, но в особо редких случаях свожу количество и прибыль. И зачастую отхожу от общепринятых стандартов 80/15/5 к собственному дроблению 50/35/15.
XYZ анализ ассортимента. Об этом виде анализа статья также опубликована. Здесь лишь отмечу, что xyz анализ идеально распределяет ассортимент на категории предсказуемости спроса.
Доля отсутствующих позиций – в анализе товарной группы тоже необходимый пункт, так как товара, в ходе различных причин, просто могло не быть на складе (ошибка закупщика, большой лаг поставки, перебои у поставщика) и как следствие – отсутствие продаж. При этом стоит учесть не только, сколько и каких позиций отсутствовало, но и какие из них относятся к категории «А» предыдущего анализа. С такими позициями я разбираюсь отдельно – почему, кто виноват, как избежать провалов.
Изменение ассортимента. Здесь учитываются новые позиции, заведенные в ассортимент, либо позиции, исключенные из продажи. Для исключенных позиций крайне желательно просмотреть статистику продаж в предыдущем периоде и понять причину вывода из ассортимента. Этот момент легко можно выяснить, пообщавшись с категорийным менеджером, ответственным за данные позиции.
Представленность позиций в различных ценовых сегментах и соответственно доля продаж в данных сегментах. Здесь же, обычно, анализирую разбивку ассортимента по брендам (если бренд в данной группе товаров имеет какое-то значение, например электроинструмент). Обычно пересекаю два этих параметра в одной таблице (шапка – ценовой сегмент; столбец – бренд) – это и наглядно и понятно, в какой степи провалы.
Ценовой и ассортиментный анализ конкурентов. Выбираю 3-5 основных конкурентов и провожу анализ соответствующей товарной группы, по цене и представленности в ассортименте конкурента. Так заведено, что мы хотим продавать лучше конкурентов, а кто не хочет больше денег, и как следствие цены желательно должны быть ниже и ассортимент соответствующий, хотя тут уже от позиционирования компании зависит. Также здесь учитываем отклонение от средней и минимальной цены конкурентов.
Последним пунктом анализа товарной группы идет выявление предпочтений покупателей. Так как данные продаж сторонних компаний – это сложно доступный инструментарий, приходится перекапывать интернет в поисках опросов предпочтений покупателей и мнений на форумах. Можно еще провести опрос покупателей, но это при сильной необходимости. Да, не просто, а как еще. Зато это один из самых интереснейших моментов в обязанностях маркетолога.
Анализ проблемных зон
Когда мы смотрим на 1-2 минутные графики в спокойное время дня — особенно в спокойные дни, — мы находим, что многие бары имеют ширину всего два — три тика. Все просто: в этом масштабе времени Баму недостаточно волатильности, чтобы последовательно получать прибыль. Он не может уверенно купить на низшем тике и продать на высшем, а в результате застревает посередине.
Если же мы рассмотрим периоды, когда Бам делал деньги, то увидим, что это происходит в течение более активного времени дня и в более оживленные дни. Анализ результатов его торговли показал, что, если объем ES составляет в среднем, по крайней мере, 1 500 контрактов в минуту, он добивается большего успеха, чем когда одноминутный объем ниже этого уровня и намного лучше, чем если средний одноминутный объем опускается ниже 1 000.
Определяем спрос по информации от поставщиков
Поставщики – это такие же предприниматели, как и вы, только продающие оптом. Сегодня, кстати становится модно иметь свой собственный интернет-магазин поставщику, на котором товар могут купить не только компании, но и любые физические лица.
Определять спрос этим способом можно двумя вариантами. Первый – это при общении с поставщиком (или личным менеджером), второй – анализируя прайс-лист. Начнем по порядку.
Вариант первый. Это наиболее простой вариант узнать спрос на товар. Вы у поставщика ни один, и многие ваши конкуренты также затариваются через него. А соответственно, поставщик видит какой товар является ходовым, а какой не очень пользуется спросом. Многие оптовики также не заинтересованы в том, чтобы их склад становился неликвидным и реагируют под рынок, закупая ходовые товары в больших количествах. Поставщик сам может вам с удовольствием рассказать, какой товар у него пользуется спросом, а какой нет. Более того, может даже обратить ваше внимания на ряд нюансов, которые стоит учитывать при продаже. Конечно не нужно слепо доверять словам поставщика и имеет смысл их перепроверить описанными выше способами. И если вы сможете подтвердить его слова, то можно продолжать общение в таком ключе. Недобросовестные поставщики естественно попытаются вас обмануть и выдать неликвид за ходовой товар, тут нужно быть внимательным.
Второй вариант. Это анализ уходимости по прайсу поставщика. Обычно поставщики присылают свой прайс с какой-то периодичностью, например, раз в день или через день. Естественно этот же прайс попадает и к многочисленным конкурентам, которые заказывают интересные и ходовые, в своем магазине, товары. Тут, несомненно, потребуется терпение, т.к. нужно собрать несколько прайс-листов, чтобы проследить динамику изменений у поставщика. Конечно, если у поставщика огромный ассортимент, то вручную это оценить достаточно сложно, но благо, это можно автоматизировать, написав небольшую программу, которая будет это отслеживать за вас и выдавать отчет в простом и понятном виде.
Что нужно знать про ABC-анализ в продажах
Что это такое. ABC-анализ — это способ сегментирования базы данных по определенному признаку. Совершенно любой базы данных по совершенно любому признаку. Он помогает рационализировать базу для любой сферы деятельности компании от производства и продаж до эффективности сотрудников.
Представим, что у нас есть товарная номенклатура на 10 000+ позиций. С помощью ABC-анализа мы можем выделить группу приоритетных товаров, которые приносят нам больше всего выручки. Мы выгрузим базу из учетной системы, сегментируем товары в ней по степени важности и выделим основные, низкорентабельные и нерентабельные группы товаров и услуг. Так мы сможем грамотно управлять политикой закупок и запасов компании.
Для чего используют. Обычно ABC-анализ применяется для ранжирования и оптимизации ассортиментного портфеля. Но на деле этот принцип прекрасно работает со всеми базами, в отношении которых есть достаточное количество статических данных. Например, можно ранжировать:
- перечень поставщиков — по объему поставок
- базу данных покупателей — по активности сделок и прибыльности сотрудничества
- дебиторскую и кредиторскую задолженность — по кредиторам и должникам
- складские запасы — по срокам реализации, занимаемой на складе площади
- длительные периоды продаж и пр.
Метод ABC позволяет рассортировать список значений на три группы, которые оказывают разное влияние на конечный результат.
Благодаря анализу ABC пользователь сможет:
- выделить позиции, имеющие наибольший «вес» в суммарном результате;
- анализировать группы позиций вместо огромного списка;
- работать по одному алгоритму с позициями одной группы.
Значения в перечне после применения метода ABC распределяются в три группы:
- А – наиболее важные для итога (20% дает 80% результата (выручки, к примеру)).
- В – средние по важности (30% — 15%).
- С – наименее важные (50% — 5%).
Указанные значения не являются обязательными. Методы определения границ АВС-групп будут отличаться при анализе различных показателей. Но если выявляются значительные отклонения, стоит задуматься: что не так.
Условия для применения ABC-анализа:
- анализируемые объекты имеют числовую характеристику;
- список для анализа состоит из однородных позиций (нельзя сопоставлять стиральные машины и лампочки, эти товары занимают очень разные ценовые диапазоны);
- выбраны максимально объективные значения (ранжировать параметры по месячной выручке правильнее, чем по дневной).
Для каких значений можно применять методику АВС-анализа:
- товарный ассортимент (анализируем прибыль),
- клиентская база (анализируем объем заказов),
- база поставщиков (анализируем объем поставок),
- дебиторов (анализируем сумму задолженности).
Метод ранжирования очень простой. Но оперировать большими объемами данных без специальных программ проблематично. Табличный процессор Excel значительно упрощает АВС-анализ.
Общая схема проведения:
- Обозначить цель анализа. Определить объект (что анализируем) и параметр (по какому принципу будем сортировать по группам).
- Выполнить сортировку параметров по убыванию.
- Суммировать числовые данные (параметры – выручку, сумму задолженности, объем заказов и т.д.).
- Найти долю каждого параметра в общей сумме.
- Посчитать долю нарастающим итогом для каждого значения списка.
- Найти значение в перечне, в котором доля нарастающим итогом близко к 80%. Это нижняя граница группы А. Верхняя – первая в списке.
- Найти значение в перечне, в котором доля нарастающим итогом близко к 95% (+15%). Это нижняя граница группы В.
- Для С – все, что ниже.
- Посчитать число значений для каждой категории и общее количество позиций в перечне.
- Найти доли каждой категории в общем количестве.
Как провести анализ ассортимента магазина
Проведение АВС анализа включает в себя насколько этапов:
- Определитесь, какую проблему вы хотите решить, что будете потом делать с результатами анализа. От этого зависит, какие показатели вам необходимо подготовить и какой массив информации использовать. В этом примере мы возьмём прибыль.
- Внесём информацию в любой редактор таблиц. В левой колонке – товар, в правой – прибыль, которую он принёс. Идеально для этой задачи подходит Excel. Возможности программы позволяют также добавлять несколько уровней сортировки, что существенно облегчает многофакторный анализ.
- Для каждого товара рассчитайте процент от общей прибыли всех товаров и запишите в третью колонку. После чего упорядочьте результат по этой колонке по убыванию. Наверху окажутся самые прибыльные товары (группа A), в самом низу – плохо продаваемые (группа C).
Другое применение ABC-анализа
Сфера применения анализа АВС и его результатов, ограничивается только вашим воображением. Главным ограничением является только то, что анализ можно провести с учетом только одного или двух параметров. Правда и его можно обойти. Для этого достаточно сопоставлять одни и те же группы по разным параметрам и оптимизировать, то, что вам нужно.
Вот только несколько примеров того, что же можно исследовать:
- Ассортимент вашего магазина можно анализировать не только по прибыльности или объему продаж, но и по площади хранения товарных запасов. Возможно какой-то товар из группы В, хоть и имеет стабильно средние продажи, но занимает слишком много места при хранении. Уменьшите их количество на складе и освободите дополнительное место для хранения более ходовых товаров.
- Результаты по анализу склада можно также применить для расстановки товаров в разных зонах склада, тем самым облегчите работу кладовщику и ускорите сбор заказов.
- Информацию, полученную после анализа оборотности и прибыльности товаров, вы можете разместить в блоке «ТОП-продаж». Это дополнительно подогреет аудиторию и стимулирует продажи этих товаров. Разместите в блок популярных товаров «мертвый груз» склада. Это решение поможет освободить место для новых или более популярных позиций. А возможно, этим товарам нужен был только толчок, чтобы клиенты о них узнали.
- Эффективным окажется и анализ клиентской базы, особенно для оптовых интернет-магазинов. Исходя из результатов, можно будет выделить ключевых клиентов, тесная работа с которыми поможет увеличить их средний чек. Используя полученную информацию, можно составить портрет целевой аудитории.
- Этим способом можно даже оценивать эффективность работы ваших менеджеров. Для этого достаточно провести расчеты по двум параметрам – количество принятых заказов и их сумма. Таким образом, вы увидите, кого стоит премировать, а с кем может быть даже попрощаться.
- Применив эту методику можно даже проанализировать затраты рабочего времени и выявить его пожирателей, сделаете работу персонала еще эффективнее.
Таблица 2. Данные о продажах и прямой себестоимости по товарным группам
Наименование товарной группы |
Продано |
Прямая себестоимость |
Замки дверные, поставщик 1 |
1 119 638,71 |
905 250,17 |
Замки дверные, поставщик 2 |
273 692,17 |
217 867,10 |
Замки гаражные, поставщик 1 |
305 955,00 |
235 776,45 |
Замки гаражные, поставщик 2 |
594 774,92 |
434 154,82 |
Петли дверные |
346 367,71 |
266 370,75 |
Итого |
2 640 428,51 |
2 059 419,29 |
Как видим, по каждой группе товаров прямая себестоимость меньше продажной цены. То есть у компании положительный результат от продажи любой выделенной нами товарной группы. Но так ли это на самом деле?
Помимо прямой себестоимости, есть еще ряд накладных расходов, которые в учете мы относим на затраты периода, но которые напрямую связаны с продажей того или иного товара. Вот эти расходы нам и надо распределить на группы.
Итак, как мы видели на примерах, всю процедуру анализа доходности товарных групп можно условно разделить на шаги, которые рассмотрим ниже.
Но прежде необходимо настроить справочники в используемой учетной системе. Этот шаг является очень важным, так как от корректной настройки справочников учетной системы зависит качество получаемой на выходе информации.
- Выделение товарных групп (группировка товара). Группировка товаров может включать в себя как однородные признаки, так и наименование поставщика, а также разделение поставщиков на внешних и внутренних. Каждая компания определяет для себя детализацию и глубину аналитики по той или иной товарной группе.
- Разделение товарных групп по поставщику или по другим признакам. Например, в организациях, занимающихся внешнеэкономической деятельностью, это могут быть коэффициенты логистики на товар или поставки, проходящие через один таможенный пост (если компания работает с несколькими таможенными постами), и т.д.
- Определение перечня накладных расходов, необходимых для анализа. Здесь опять же все зависит от специфики конкретной компании. Но, как правило, распределяются такие расходы как расходы на закупку товара, хранение, на рекламу, на продвижение той или иной группы товара и т.д.
- Критерий отнесения накладных расходов в ту или иную товарную группу (база распределения). Базой распределения может выступать себестоимость проданного товара (или выручка) в общем объеме себестоимости (выручки) продаж или в объеме продаж по товарной группе. Также базой распределения может быть соотношение количества закупленного или проданного товара, зарплата персонала отдела закупок (продаж) в общем фонде оплаты труда и т.д.
- Выводы. Собственно, для чего мы проделывали всю работу по анализу. Если нет выводов, на основании которых можно и нужно принимать решения, то вся работа по проведению анализа просто теряет смысл.
XYZ анализ ассортимента
XYZ анализ ассортимента проводится для создания более полной картины торгового процесса магазина и разработки аргументированной ассортиментной политики. Критерием XYZ анализа может становиться стабильность продаж, доходность товаров.
Проведение ABC и XYZ анализов должно проводится с выработанной периодичностью – каждый месяц или квартал. При этом необходимо следить, чтобы используемые данные отражали все изменения в товарообороте за период.
Эффективность совмещения ABC и XYZ анализов можно проиллюстрировать следующим условным примером.
Выстраиваем объектно-стоимостную матрицу для выявления наименее эффективных торговых групп в продовольственном супермаркете.
Преимущества и недостатки ABC-анализа
Это означает навязывание жестких условий фермерам, разрушение сельской среды, потерю информации о продукции, контроль цен на конечную продукцию и все более однородное предложение более низкого качества.
Неравномерное развитие розничного товарооборота в ассортиментном разрезе в динамике привело к изменению его структуры, которая характеризуется удельным весом отдельных товарных групп в общем объеме товарооборота.
Как вы видите, прямая себестоимость каждой группы товаров меньше продажной цены. То есть, компания имеет положительный результат от продажи каждой указанной нами группы товаров.
В таблице выше мы также используем дополнительное поле «прогноз», которое позволяет нам делать прогнозы выполнения плана продаж при существующей динамике отгрузок.
Запасы формируются на всех этапах товародвижения: на складах производственных предприятий, в пути, на складах оптовиков и розничных магазинов.
Этап 2. Выделение номенклатурных групп по методу XYZ
Метод учитывает стабильность реализации для товаров или списания в производство для материалов. Позволяет увидеть, что стоит за высокой суммой выручки: разовая продаж супердорогой ассортиментной позиции или постоянный клиентский спрос. В сочетании с АВС дает отличные результаты. Ведь теперь ошибиться в том, где явные складские залежи, а где просто дешевые, но каждодневно продающиеся запасы, окажется невозможно.
Суть в том, чтобы:
- взять ту же выборку по товарам, что и для АВС, но только с разбивкой по месяцам или кварталам года;
- рассчитать в разрезе номенклатурных позиций так называемую вариацию. Ее значение определит искомую стабильность. Считается, что когда она не более 10%, то товар продается от периода к периоду в примерно одинаковых объемах. А если свыше 25%, то в иные месяцы реализация существенно снижается или даже отсутствует;
- на основе вариации распределить товары по трем группам – X, Y, Z.
Отчет о проведенном анализе товарной группы
В конечном итоге, когда товарная группа подверглась тщательному анализу со всех точек зрения, формируется единый документ на 7-10 страниц. Добавляются графики, диаграммы, таблички, выводы и предложения – и все, на суд начальства. Если товарная группа характеризуется падающим трендом и обоснована её значимость для компании, предлагается план мероприятий по «реанимации» группы, который впоследствии претворяется в жизнь. Если же товарная группа не представляет никакой ценности для компании, просчитывается вариант и возможность вывода позиций группы из ассортимента.
Являются ли эти новые затраты в бизнесе ценных бумаг? И цены на продукт были там раньше. Эти затраты рассчитываются изготовителем для обеспечения его работы и расходов. Вы не будете платить дополнительно. Теперь ваше преимущество в том, что мы рассчитываем и показываем новые проценты и евро для ваших инвестиций.
Как вы получаете информацию о будущих расходах? Дополнительная информация о покупке, продаже и текущих расходах предоставляется производителем безопасности. По стоимости продаж мы предполагаем, что вы выбираете одно и то же торговое место и порядок заказа, как при покупке. Мы включаем существующие свободные сделки. Для продажи в валюте, отличной от евро, мы принимаем тот же обменный курс, что и при покупке, и даем вам указание, что в будущем могут быть разные.
Коллега, в этой статье постарался максимально полно отобразить, как проводить анализ товарной группы. Если возникнут вопросы, с удовольствием отвечу на них в комментариях, так что пиши. На этом все.
Блог начальника отдела закупок
Одной из обязанностей категорийного менеджера является управление товарными группами.
Некоторые компании ввели категорийный менеджмент в свою деятельность настолько, насколько позволяют знания и специфика, некоторые только пытаются освоить новую систему работы. И вчерашние менеджеры по закупкам, а ныне категорийные менеджеры, должны подстраиваться под современный рост компании и осваивать новые способы работы. Как сформировать ассортиментную матрицу путем анализа ABC/XYZ, где XYZ не показатели предсказуемости поведения товарной группы, а ее прибыльность, и как сформировать заказ поставщику мы подробно рассматривали здесь.
Имея сформированную товарную матрицу (ассортиментный портфель Компании), можем говорить об управлении товарными подгруппами. Эта работа характерна для категорийного менеджмента, и является трудоемкой и связанной с долей риска. Важно помнить, что в деятельности любой коммерческой организации необходимо в первую очередь ориентироваться на целевого покупателя, определяющего спрос. С учетом спроса целевой аудитории далее учитываются товарооборот, прибыльность, порядок работы с поставщиками, конкурентная среда и т.д. Тем не менее, торговым компаниям интересно повышать рентабельность ассортиментного портфеля, для этого необходимо вводить управление товарными подгруппами.
Анализ товарной группы примеры
Сегодня, простыми словами и на примерах разберем, что такое ABC и XYZ анализ, как применять эти инструменты на практике. Из моего опыта включения или внедрения ABC и XYZ анализа в систему управления ассортиментом и запасами тех или иных компаний, могу определенно сказать, что эффективность и прибыльность компании значительно повышается. Управление ассортиментом становится осознанным и управляемым.
- Что такое АВС анализ ассортимента
- Как применить АВС анализ в управлении запасами
- Что такое расширенный АВС анализ
- Как применить АВС анализ на складе
- Что такое XYZ анализ и каковы условия его применения
- Недостатки XYZ анализа
- В чем преимущества совместного АВС и XYZ анализа.