Pavel Istomin
· Маркетинг

ABC анализ пример в Excel – пошаговая инструкция

Vilfredo Federico Damaso

Первым делом, в этой статье я покажу ABC анализ на примере в Excel. Мы это сделаем всего за 5 маленьких шагов.

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

ABC анализ — это очень простая и одновременно важнейшая методика ранжирования данных по группам.

ABC анализ, в первую очередь, служит для определения «сильных сторон» системы – для выявления точек роста.

Но не будем сильно увлекаться теорией и отправимся практиковать.

Пример ABC анализа в Excel – 5 шагов

Действительно ABC анализ настолько прост, что его можно выполнить всего за 5 элементарных шагов.

Шаг №1 — выгрузка данных

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

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

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

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

  • менеджеры и продажи по ним
  • магазины и их выручка
  • клиенты и продажи по ним
  • сотрудники и их зарплаты
  • филиалы и их расходы
  • и т.д.

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

abc анализ пример в excel-1

Шаг №2 — добавление столбцов

На втором шаге, вам нужно добавить два столбца.

Столбец «%» и столбец «Группа» — как показано на картинке

abc анализ пример в excel-2

Шаг №3 — формула %

В столбец «%» вам нужно добавить формулу и «протянуть» её на все ячейки этого столбца.

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

Например, продажи по «Товар_1» составляют 11,9% от общей суммы продаж в 100 005 000 рублей.

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

Обратите пристальное внимание! Ячейка с общей суммой продаж С17 — закреплена (в формуле она выглядит вот так $C$17, значок доллара обозначает закрепление). Это сделано для того, чтобы когда вы протягивали формулу вниз по столбцу, ячейка С17 оставалась на месте.

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

abc анализ пример в excel-3Шаг №4 — сортировка

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

abc анализ пример в excel-4

Затем отсортируйте столбец (%) по убыванию (от максимального значения к минимальному)

abc анализ пример в excel-5

Шаг №5 — определение групп ABC

Эталонным распределением является 80/15/5

Но нужно понимать, что «в природе» именно такое распределение практически не встречается, значения лишь стремятся к нему и должны быть близки к этому эталону 80/15/5:

Группа А — значения по продажам занимают около 80% от общей суммы продаж

Группа В - значения по продажам занимают около 15% от общей суммы продаж

Группа С — значения по продажам занимают около 5% от общей суммы продаж

Теперь вам нужно эти группы определить.

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

На этой картинке, показано как я определил группу А (сумма продаж 76,9%)

abc анализ пример в excel-6

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

В моём случае, сумма по продажам группы В составляет 16,6%

abc анализ пример в excel-7

Все что осталось ниже группы В и является группой С.

В моём случае продажи группы С составляют 6,3%

abc анализ пример в excel-8

В итоге, очень просто и быстро, у нас получилась вот такая таблица ABC анализа в Excel.

Мы распределили товарную матрицу на группы ABC

abc анализ пример в excel-9

Ну вот и всё коллеги, теперь вы умеете делать ABC анализ в Excel практически в совершенстве. Но это не всё что у меня есть вам сказать по данной теме.

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

abc анализ пример в excel-10

 

Во-вторых, я думаю, многим из вас интересно не только уметь механически проводить ABC анализ в Excel, но и понимать, что дальше с этими данными делать, как их правильно использовать.

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

ABC анализ — примеры использования

ABC анализ делается для того чтобы найти сильные и слабые элементы системы. Когда мы видим сильные и слабые элементы, мы можем найти причины их силы и слабости, мы можем составить оптимальные стратегии управления этими элементами.

Ниже я приведу, некоторые из возможных вариантов использования ABC анализа, которые я использовал в своей практике.

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

Как использовать ABC анализ в закупках

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

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

Увидеть такие товары, вам и позволяет ABC анализ. Товары группы А приносят 80% всех продаж компании, это значит что вы должны уделять самое пристальное внимание этой группе. Они обязательно и всегда должны быть на складе в количестве чуть больше чем необходимо.

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

Вы можете сделать ABC анализ товарных позиций по их объему закупок. Товары группы А, разумеется, будут составлять около 80% всего объема.

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

Как использовать ABC анализ в продажах

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

Дальше вам нужно понять причину, почему одни товары продаются хорошо (группа А), а другие плохо (группа С).

Проводя это «расследование», вы получите огромный пласт новых знаний для роста компании.

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

Или у менеджеров не хватает обучающих материалов по группе С, или вы просто напросто не конкуренты по этому направлению и нужно вводить новые (более жизнеспособные) позиции.

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

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

Как использовать ABC анализ в цифровом маркетинге

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

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

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

Например, продажи по первой фразе (группы А), вам приносят в месяц 1 000 000 рублей/мес. А фразы по последней фразе (группа С) 5 000 рублей.

Если вы увеличите CTR% объявления по первой фразе в два раза, то можно ожидать увеличение объёма продаж минимум на 50% (да, здесь не линейная зависимость), а это + 500 000 рублей.

А если вы те же усилия примените к последней фразе (группы С), то вы получите прирост лишь + 2 500 рублей.

Я думаю разница очевидна.

Более того, не все кампании и фразы работают в плюс. Некоторые просто кушают бюджет и не приносят ничего. С помощью ABC анализа вы сможете определить этих транжир.

Как использовать ABC анализ в SEO

Да, да, ABC анализ можно использовать где угодно, а уж тем более его нужно использовать в SEO.

Вы можете ранжировать ключевые фразы по частотности и конкуренции.

Так 10 статей с частотностью 100 переходов в сутки каждая, принесут вам в итоге 1 000 посетителей в сутки.

Ту же самую 1 000 вы можете получить используя 100 ключей с частотностью 10 переходов в сутки.

Я думаю разница по трудозатратам очевидна — написать 10 статей или 100!

Эпилог

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

ABC анализ это искатель точек роста, это искатель новых идеи и стратегий!

Автор: Павел Истомин
Комментарии
avatar