АВС анализ, как рассчитать АВС в Excel?

    Здравствуйте коллеги!

    И так, АВС анализ. И как обычно, сначала теория, затем практика и готовый шаблон, который всю работу сделает за вас менее чем за 2 минуты.

    Более ста лет назад выдающийся итальянский математик, экономист и социолог Вильфредо Парето, исследуя распределение доходов и богатства в Англии и Флоренции в XVII, XVII и XIX вв., пришел к выводу, что 20% наиболее богатых семей принадлежит 80% собственности и доходов.

    Так возникло правило Парето 20/80, которое звучит так: «20 % усилий дают 80 % результата, а остальные 80 % усилий — лишь 20 % результата»

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

    И продажи в этом правиле не исключение!

    • 20% покупателей делают 80% выручки
    • 20% ассортимента приносят 80% прибыли и т.д.

    На основе данного правила базируется АВС анализ, где А, В – это первые 80% продаж (выручки, объема продаж или прибыли), С – это оставшиеся 20%.

    Стандартное распределение АВС выполняется следующим образом:

    • А – 50%
    • В – 30%
    • С – 20%

    Это условное распределение и его можно менять главное сохранять пропорцию 20/80. Т.е. Мы можем назначить группу А на 10%, В на 70%, но С мы всегда оставляем 20%. Есть варианты ABCd – анализа. Принцип тот же, если в первом случае мы распределяем 80% в А и В, то во втором мы распределяем 20% в С и d (на d обычно ставят последние 5% и менее)

     

    Для чего используется АВС анализ в продажах и как рассчитать АВС?

    Назначений АВС анализа множество:

    • ценообразование,
    • мерчандайзинг,
    • сортировка ассортимента в каталогах интернет магазинов и т.д.

    Я объясню АВС на примере анализа эффективности ассортимента. Для эффективности ассортимента нам потребуется статистика продаж за определенный период (месяц, квартал, год) И вот тут нужно выбрать по какому показателю мы будем анализировать ассортимент, по:

    • Выручке
    • Объему продаж
    • Прибыли

    Если нас интересует эффективность ассортимента по объему продаж (количеству проданного товара), мы берем сумму проданного товара в шт/кг/л за период, если нас интересует эффективность ассортимента по выручке, берем сумму выручки за период и т.д. Я возьму данные по выручке.

    АВС анализ

     

    И первый шаг: Сортируем выручку по убыванию

    АВС анализ, формула для расчета

     

    Второй шаг:  

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

    авс анализ, как посчитать авс

     

    Третий шаг:

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

    Как написать формулу и протянуть ее по всему ассортименту? Ставим знак «=» кликаем первое значение ставим символ деления «/» и кликаем на сумму выручки и нажимаем F4 что бы зафиксировать значение суммы выручки, и при протягивании формулы она не съехала.

    В моем случае формула выглядит вот так: =B2/$B$62

    авс анализ

     

    Шаг четвертый:

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

    авс анализ пошаговое руководство

     

    Шаг пятый:

    Рассчитываем и проставляем АВС

    Возьмем стандартные значения А – 50%, В – 30% и С – 20%. Для расчета АВС в excel, нам потребуется функция «ЕСЛИ»

    И так, у нас есть накопительные итоги именно по ним мы распределяем АВС.

    Если накопительный итог больше 80% тогда проставляем «С», если накопительный итог больше 50%, тогда проставляем «В» и на все остальные значения проставляем «А»

    Т.е. как вы заметили мы используем функцию «ЕСЛИ» дважды, и формула в excel выглядит следующим образом:

    =ЕСЛИ(D2>0,8;"C";ЕСЛИ(D2>0,5;"B";"A"))

     

    авс анализ пошаговое руководство

     

    Ну вот мы создали АВС анализ, теперь самое важное – КАК ИНТЕРПРЕТИРОВАТЬ ДАННЫЕ?

    Группу «А» обычно не трогают, так как данная группа – это и есть основной ассортимент, который приносит наибольшую выручку.

    На группу «В» можно обратить внимание, так как есть инструменты по увеличению продаж (например ценообразование), и после применения этих инструментов группа «В» может попасть в группу «А»

    А вот группа «С» требует особого внимания. Данная группа – это ассортимент который, говоря простым языком, плохо продается. Вам нужно тщательно проанализировать продажи товаров по этой группе (товар не выложен, завышена цена, только вошел в товарную матрицу и тд) Если на точках продажи все нормально, значит товар не интересен потребителям! Соответственно нужно провести ротацию ассортимента, т.е. заменить на аналог или просто вывести из ассортимента.

    ВНИМАНИЕ!!!

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

     

    ТЕПЕРЬ ОЧЕНЬ ВАЖНЫЙ МОМЕНТ!!!

    АВС анализ становится более информативным, если проводить его сразу по трем главным показателям продаж (выручка, объем продаж и прибыль) т.к., например, выручка может относится к группе «В», объем к группе «А», а прибыль к группе «С».

    О чем нам это говорит? Данная вариация показывает, что на товар В-А-С слишком низкая цена. Потому что, Выручка нормальная, по объему хорошо продается, но плохо дает прибыль. Если мы увеличим цену на этот товар, мы сможем перераспределить показатели. Мы можем увидеть вот такие результаты: В-В-А или В-В-В или С-В-А и т.д.

    Т.е. Мы можем при помощи совмещенного АВС по трем показателям управлять продажами! А это уже профессионализм ;)

    АВС анализ можно сделать ЕЩЁ более информативным, присоединив к нему XYZ анализ. XYZ анализ показывает стабильность продаж за выбранный период, где:

    • X – стабильные продажи,
    • Y – Продажи требуют внимания,
    • Z – НЕ стабильные продажи

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

    Еще один пример, товар с показателем С-С-А-Y. Мы видим, что товар дает нам прибыль, но выручка и объем продаж плохие, стабильность средняя. По данному товару необходимо снизить цену. И показатели перерассчитаются: В-В-А-X, а это уже лучше, чем первоначальный показатель, неправда ли?

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

    Управлять продажами можно только при помощи объединенного АВС (по трем важным показателям) и XYZ анализов.

     

    Как совместить АВС по трем показателям и добавить XYZ анализ?

    Самый простой способ,

    1. делаете отдельно три АВС по каждому показателю
    2. рассчитываете XYZ (ссылка на урок)
    3. соединяете результат в одной таблице.

    Есть способ посложнее, при помощи макросов. Чем хорош этот способ? Все расчеты займут не более 2 минут.

    Кроме этого, бывают случаи, когда необходимо рассчитать АВС не по всему ассортименту, а по одной конкретной группе товаров или нескольким товарным группам. Представьте, сколько времени у Вас займет расчет? При помощи макроса Вы будете рассчитывать АВС по трем показателям и XYZ за 1 секунду, просто выбрав группу товара.

    Вот как это работает (видео не ускоренно, так работает макрос, который всю работу сделает за Вас меньше чем за 2 минуты):

    Хотите такой шаблон для себя?

    Вы можете его скачать на этом сайте, перейдите по кнопке ниже, сделайте пожертвование не меньше указанной суммы, и Вы получите уникальный инструмент расчёта совмещенных АВС и XYZ анализов, который вам позволить экономить очень много времени при расчетах.

     

     

     

    Понравилась статья? Отблагодари автора ;)

    © 2013-2019 НВ ВЕБ МАРКЕТИНГ. ИП Ульянкин Дмитрий Владимирович ИНН 402806997547