В сегодняшней статье мы рассмотрим простой пример ABC-анализа продаж товаров в Excel.
Данный анализ является полезным для проведения в торговых точках для сокращения, либо ротации ассортимента товарных категорий.
ABC-анализ — метод, позволяющий классифицировать ресурсы фирмы по степени их важности. В его основе лежит принцип Парето — 20 % всех товаров дают 80 % оборота.
Существует множество различных вариантов процентных соотношений категорий A, B и C:
80-15-5;
70-20-10;
50-30-20.
Для анализа в рамках этой статьи, мы будем пользоваться последней классификацией 50% - А, 30% - B, 20% - С.
Зачастую, при принятии решения о ранжировании позиций на ABC-категории, необходимо учитывать несколько факторов. Такими факторами могут быть:
- Продажи в рублях – наиболее очевидный фактор;
- Продажи в закупочных ценах – например, если у нас предусмотрены дополнительные бонусы поставщика;
- Маржинальная доходность – разница между продажами и суммой затрат на себестоимость, потерь, логистику и т. д.;
- Габариты позиций – не самый очевидный фактор, но, например, в магазине бытовой электроники на площади, которую занимают три телевизора можно разместить целый стеллаж с мобильными телефонами.
- И так далее.
В данной статье мы рассмотрим вариант ABC-анализа по двум факторам: продажи в рублях и доходность в рублях.
Кажется, что торговому предприятию первостепенно максимизировать доходность, поэтому логично рассматривать только фактор доходности, но это не так.
Дело в том, что позиции, которые имеют большие продажи могут служить для нас трафикогенерирующими, но при этом не быть самыми доходными. И их тоже необходимо учитывать.
Например, в заведениях быстрого питания наиболее доходными товарами будут находиться в категории безалкогольных напитков, но очевидно, что нельзя оставить в ассортименте только их, потому что менее доходное, но с большими продажами основное блюдо будет генерировать трафик.
Таким образом, мы показали необходимость рассматривать продажи в рублях и доходность в рублях вместе.
На листе «Исходные данные» приложенного к статье файла Excel имеется выгрузка продаж и доходности позиций по магазинам за месяц. Построим из этой выгрузки сводную таблицу со средними показателями продаж и доходности позиций и отсортируем позиции по убыванию продаж. Среднее берется для того, чтобы учесть разную географию позиций. В реальном примере нам также следует привести продажи позиций на количество дней с продажами или остаткам.
Рис. №1. Средний продажи и доходность позиций на 1 магазин присутствия.
Нас интересует вопрос, каким образом реализовать ABC-анализ для двух факторов.
Первый вариант, это проводить раздельный ABC-анализ и присваивать каждой позиции две буквы по шкале продажи в рублях и доходность в рублях. Например две АА будут говорить о высокодоходной позиции с большой долей в продажах, а BC о позиции со средней доходностью с низкими продажами.
Данный вариант является приемлемым, когда мы хотим рассмотреть небольшое количество факторов, но однозначно не даёт ответа на вопрос, какой нам ассортимент выводить или ротировать. Например, не понятно, как осуществлять выбор между двумя товарами с рейтингом BC или CB.
Вторым вариантом является вывод интегрального показателя. Когда в одном показателе мы учитываем все рассматриваемые показатели.
Понятно, что мы не можем просто сложить продажи руб. с доходностью руб. и делать ABC-анализ по общей сумме, так как по умолчанию доходность это только лишь часть продаж, и при сложении мы будем учитывать продажи с большим весом.
На помощь, в этом случае приходит нормирование показателя. Одним из способов нормирования является линейная нормировка. Её формула представлена ниже:
Для того, чтобы нормировать показатель продаж конкретного товара нам необходимо из значения продаж данного товара вычесть минимум продаж группы и разделить полученное значение на разницу между максимумом и минимумом продаж группы.
Произведем нормирование наших показателей продаж руб. и доходности руб. в столбцах D и E листа «Сводная таблица» (Рис №2).
Рис. №2. Нормирование показателей.
Таким образом, все наши показатели приняли значение от 0 до 1, где 0 будет соответствовать минимуму продаж, а 1 – максимуму.
Как мы видим, динамика между фактическим и нормированным показателем абсолютно идентична.
Далее нам следует их сложить, при чём мы можем их складывать как с равным весом, так и с произвольным.
В целях данного примера пример одинаковый вес показателя продаж руб. и доходности.
Мы получили интегральный показатель по товарам от 0 до 2.
Проранжируем товары по убыванию данного показателя и выведем сумму нарастающим итогом по столбцу Сумма показателей на листе «Итоговый показатель» (рис. №3).
Рис. №3. Сумма нарастающим итогом и доля нарастающим итогом в показателе.
Теперь нам осталось присвоить рейтинг позициям. До 50% нарастающим итогом – позиции А, от 50 до 80% нарастающим итогом – позиции группы B, остальные – позиции группы C.
Запишем формулой к столбцу D:
=ЕСЛИ(D2<=50%;"A";ЕСЛИ(D2<=80%;"B";"C"))
Рис. №4. Ранжирование позиций по категориям ABC.
В процентном соотношении позиции распределились следующим образом: доля позиций А – 20,7% (48% показателя), доля позиций B – 20,7% (30% показателя), доля позиций C – 58,6% (22% показателя).
Рис. №5. Распределение количества позиций и доли в обобщенном показателе.
Такое распределение по позициям группы ABC позволяет нам принимать решения о том, какие из позиций следует сократить, либо ротировать на новинки, если они, например, приносят нам потери или замедляют оборот средств.
В данной статье мы рассмотрели, что такое ABC-анализ продаж, какие факторы он может учитывать и каким образом можно провести ABC-анализ с учётом нескольких факторов используя нормирование показателей.