Применение ABC-анализа в Microsoft Excel - TurboComputer.ru
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд (пока оценок нет)
Загрузка...

Применение ABC-анализа в Microsoft Excel

ABC и XYZ анализ в Excel с примером расчета товарного ассортимента

Для анализа ассортимента товаров, «перспективности» клиентов, поставщиков, дебиторов применяются методы ABC и XYZ (очень редко).

В основе ABC-анализа – известный принцип Парето, который гласит: 20% усилий дает 80% результата. Преобразованный и детализированный, данный закон нашел применение в разработке рассматриваемых нами методов.

ABC-анализ в Excel

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

Благодаря анализу ABC пользователь сможет:

  • выделить позиции, имеющие наибольший «вес» в суммарном результате;
  • анализировать группы позиций вместо огромного списка;
  • работать по одному алгоритму с позициями одной группы.

Значения в перечне после применения метода ABC распределяются в три группы:

  1. А – наиболее важные для итога (20% дает 80% результата (выручки, к примеру)).
  2. В – средние по важности (30% – 15%).
  3. С – наименее важные (50% – 5%).

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

Условия для применения ABC-анализа:

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

Для каких значений можно применять методику АВС-анализа:

  • товарный ассортимент (анализируем прибыль),
  • клиентская база (анализируем объем заказов),
  • база поставщиков (анализируем объем поставок),
  • дебиторов (анализируем сумму задолженности).

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

Общая схема проведения:

  1. Обозначить цель анализа. Определить объект (что анализируем) и параметр (по какому принципу будем сортировать по группам).
  2. Выполнить сортировку параметров по убыванию.
  3. Суммировать числовые данные (параметры – выручку, сумму задолженности, объем заказов и т.д.).
  4. Найти долю каждого параметра в общей сумме.
  5. Посчитать долю нарастающим итогом для каждого значения списка.
  6. Найти значение в перечне, в котором доля нарастающим итогом близко к 80%. Это нижняя граница группы А. Верхняя – первая в списке.
  7. Найти значение в перечне, в котором доля нарастающим итогом близко к 95% (+15%). Это нижняя граница группы В.
  8. Для С – все, что ниже.
  9. Посчитать число значений для каждой категории и общее количество позиций в перечне.
  10. Найти доли каждой категории в общем количестве.



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

Составим учебную таблицу с 2 столбцами и 15 строками. Внесем наименования условных товаров и данные о продажах за год (в денежном выражении). Необходимо ранжировать ассортимент по доходу (какие товары дают больше прибыли).

  1. Отсортируем данные в таблице. Выделяем весь диапазон (кроме шапки) и нажимаем «Сортировка» на вкладке «Данные». В открывшемся диалоговом окне в поле «Сортировать по» выбираем «Доход». В поле «Порядок» – «По убыванию».
  2. Добавляем в таблицу итоговую строку. Нам нужно найти общую сумму значений в столбце «Доход».
  3. Рассчитаем долю каждого элемента в общей сумме. Создаем третий столбец «Доля» и назначаем для его ячеек процентный формат. Вводим в первую ячейку формулу: =B2/$B$17 (ссылку на «сумму» обязательно делаем абсолютной). «Протягиваем» до последней ячейки столбца.
  4. Посчитаем долю нарастающим итогом. Добавим в таблицу 4 столбец «Накопленная доля». Для первой позиции она будет равна индивидуальной доле. Для второй позиции – индивидуальная доля + доля нарастающим итогом для предыдущей позиции. Вводим во вторую ячейку формулу: =C3+D2. «Протягиваем» до конца столбца. Для последних позиций должно быть 100%.
  5. Присваиваем позициям ту или иную группу. До 80% – в группу А. До 95% – В. Остальное – С.
  6. Чтобы было удобно пользоваться результатами анализа, проставляем напротив каждой позиции соответствующие буквы.

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

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

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

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

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

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

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

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

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

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

ABC-анализ в Эксель: теория и рабочий пример

Здравствуйте. Сегодня учимся делать АБЦ анализ в Excel. Начнем с определений. АВС-анализ – это способ классификации ресурсов по степени их влияния на процессы, в которые они вовлечены. Например, товарного ассортимента на коммерческую деятельность. Т.е. определить, какие товары приносят максимальную прибыль, а какие – лишь отнимают операционное время ваших работников.

В основе abc-анализа лежит метод ABC и закон Парето: 20% усилий дают 80% результата. Наша задача – разбить перечень ресурсов на 3 категории:

  • А – суммарная доля в общем результате – 80%
  • B – суммарная доля – еще 15%
  • C – оставшиеся 5%

Давайте сделаем АБС анализ ассортимента по объему продаж за год. Действуем по алгоритму:

    Выгружаем из базы данных продажи за год в разрезе товаров:

Сортируем список по убыванию суммарных продаж

В новом столбце считаем долю каждого товара в суммарных продажах. В каждой строке делим соответствующие продажи на суммарные: =B2/СУММ($B$2:$B$23)

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

В последнем столбце определяем категорию с помощью функции ЕСЛИ: =ЕСЛИ(D2

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

Теперь нам хорошо видно, что первые 13 товаров делают 80% всех продаж. Однако, этих данных мало, чтобы провести реальный анализ и принять определенные решения касаемо того или иного продукта. Поэтому, дальше я расскажу, как сделать абс-анализ в Excel информативным и полезным.

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

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

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

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

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

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

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

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

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

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

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

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

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

    Vilfredo Federico Damaso

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Шаг №4 — сортировка

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Во-вторых, я думаю, многим из вас интересно не только уметь механически проводить 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 анализ это искатель точек роста, это искатель новых идеи и стратегий!

    ABC XYZ – анализ в Excel одним нажатием клавиши

    Из статьи вы узнаете о бесплатной надстройке Excel для ABC- XYZ- анализа.

    Эта надстройка поможет вам одним нажатием клавиши сделать ABC – анализ и XYZ – анализ.

    Данная надстройка когда-то стала идеей для создания Forecast4AC PRO.

    В статье мы расскажем:

    • О возможностях надстройки для ABC XYZ анализа;
    • Как с её помощью сделать ABC анализ;
    • Как сделать ABC XYZ анализ;
    • И где можно бесплатно скачать надстройку.

    Скачать надстройку вы можете на странице сайта Клуб Закупщиков скачать

    После скачивания открываем надстройку и нажимаем кнопку “Добавить A&Z анализ в меню”. У вас появляется в меню Excel “Надстройки” кнопка “A-Z анализ”.

    Если кнопка не нажимается и меню не появляется, то включаем макросы. Как включить макросы в Excel вы можете прочитать в статье “Как включить макросы в Excel”

    Меню добавили, рассмотрим возможности.

    Начнем с настроек АВС анализа.

    1. Дополнительно к группам ABC можно добавить группы AA, D и E. Для этого ставим галочки для соответствующей группы.
    2. Задать границы для каждой из групп

    Если в группе “AA” стоит 15%, в группе “А” 50%, в “В” 80%, “С” 95%, “D” 99%, то

    В группу “AA” попадут позиции, которые делают значительную часть объема продаж (или другого анализируемого показателя) больше заданной границы, в нашем случае – больше 15% от общего объема. Если вы используете эту группу, то товары, которые в нее попадают, исключаются из ABCDE анализа, и анализ сделается по оставшимся товарам.

    • В группу “А” попадают позиции, которые делают 50% от общего объема продаж (или другого анализируемого показателя).
    • Группа “B” — позиции, которые по объему продаж (или другому показателю) делают от 50% до 80% от общего объема продаж .
    • Группа “C” — позиции, которые по объему продаж (или другому показателю) делают от 80% до 95% от общего объема продаж.
    • Группа “D” —от 95% до 99% от общего объема продаж.
    • Группа “E” —оставшийся 1% от общего объема продаж.

    Теперь рассмотрим настройки для XYZ – анализа:

    О применении XYZ – анализа в прогнозировании мы писали в статье “XYZ – анализ – коэффициент вариации – подготовка данных к прогнозу”

    Перейдем к настройкам.

    1. Как и в ABC анализе, есть возможность задать границы групп XYZ;

    2. А также вместе ABC XYZ анализом, возможно вывести сигму, среднее, коэффициент вариации:

    Перейдем к рассмотрению примеров расчетов анализов с помощью надстройки.

    1. ABC – анализ.

    Для этого данные должны иметь следующее представление (в нашем примере наименование товаров и объемы продаж за 2012 год в столбец, во вложенном файле лист “ABC анализ”):

    Устанавливаем курсор в первую ячейку столбца и нажимаем на кнопку A-Z анализ:

    Программа в соседний столбец выведет группы по каждой анализируемой позиции:

    2. ABC XYZ анализ.

    Для этого данные должны иметь следующее представление (1 строка Excel — 1 временной ряд, количество рядов не ограничено, во вложенном файле — лист “ABC_XYZ анализ”):

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

    Если данных много и мышкой обводить их неудобно, то для быстрого выделения можно воспользоваться сочетанием клавиш ctrl+shift+end.

    Устанавливаем курсор в левую верхнюю ячейку с данными:

    и нажимаем сочетание клавиш ctrl+shift+end, Excel автоматически выделит область, начиная с левой верхней ячейки и заканчивая правой нижней, как на картинке ниже:

    Теперь нажимаем кнопку “A-Z анализ”, и надстройка для каждого временного ряда делает XYZ – анализ, а также автоматически суммирует данные по каждому ряду и делает ABC – анализ.

    В результате в продолжение данных для каждого временного ряда программа выводит:

    • в первом столбце — группы ABC,
    • во втором — группы XYZ,
    • в третьем (если стоит галочка) — сигму,
    • в 4 — среднюю,
    • 5 — коэффициент вариации.

    Итак, одним нажатием клавиши, используя надстройку от Клуба Закупщиков, вы сможете сделать ABC- и XYZ – анализ.

    Надстройку вы можете скачать с сайта Клуб Закупщиков скачать

    После скачивания открываем надстройку и нажимаем кнопку “Добавить A&Z анализ в меню”. У вас появляется в меню Excel “Надстройки” кнопка “A-Z анализ”.

    Присоединяйтесь к нам!

    Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

    • Novo Forecast Lite – автоматический расчет прогноза в Excel .
    • 4analytics – ABC-XYZ-анализ и анализ выбросов в Excel.
    • Qlik Sense Desktop и QlikView Personal Edition – BI-системы для анализа и визуализации данных.

    Тестируйте возможности платных решений:

    • Novo Forecast PRO – прогнозирование в Excel для больших массивов данных.

    Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

    ABC анализ продаж. Пример расчета в Excel

    ABC анализ продаж. Определение

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

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

    При проведении анализа продукции все товары делятся на три группы:

    • Группа «А» – максимально ценные товары, занимают 20% ассортимента продукции, и приносят 80% прибыли от продаж;
    • Группа «В» – малоценные товары, занимают 30% ассортимента продукции, и обеспечивают 15% продаж;
    • Группа «С» – не востребованные товары, занимают 50% ассортимента, и обеспечивают 5% прибылей от продаж.

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

    ABC анализ продаж продукции. Этапы проведения

    Этапы проведения ABC-анализа номенклатуры товаров и объема продаж компании (предприятия) следующие:

    1. Определение номенклатуры продукции предприятия.
    2. Расчет нормы прибыли по каждой товарной группе.
    3. Определение эффективности каждой группы.
    4. Ранжирование товаров и их классификация (ABC) по ценности для предприятия.

    Пример ABC анализа продаж продукции в Excel

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

    Номенклатура продукции для проведения ABC-анализа в Excel

    Далее необходимо осуществить сортировку товаров по прибыльности. Заходим в главном меню Excel → «Данные» → «Сортировка». Результатом будет сортировка групп товаров по рентабельности от самого прибыльного до самого убыточного.

    На следующем этапе необходимо определить долю по каждому виду товара. Для этого воспользуемся формулами в Excel.

    Доля продаж каждого вида товара =B5/СУММ($B$5:$B$15)

    Определение доли продукции в объеме продаж компании

    На следующем этапе рассчитывается доля групп накопительным итогом по формуле:

    Доля товара в номенклатуре накопительным итогом =C6+D5

    Оценка доли прибыли накопительным итогом для группы товаров

    После этого необходимо определить границу до 80% для группы товаров «А», 80-95% для группы товаров «В» и 95-100% для товаров «С». На рисунке ниже представлен результат группировки товаров по трем группам для магазина сотовых телефонов. Так марки Samsung, Nokia, Fly и LG дают 80% всех продаж, Alcatel, HTC, Lenovo обеспечивают 15% продаж и Philips, Sony, Apple, ASUS приносят 5% выручки от реализации.

    ABC анализ продаж продукции. Пример расчета в Excel

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

    30% всех товаров приносят компании 80% прибыли.

    Преимущества ABC-анализа

    Данный метод хорошо себя зарекомендовал на практике, и имеет следующие преимущества:

    1. Простота использованияи скорость проведения анализа для повышения эффективности продаж. Методику ABC-анализа можно использовать на любом предприятии, так как она не требует больших вычислительных мощностей и баз данных. Все расчеты по номенклатуре товара могут быть произведены в таблице в Excel.
    2. Надежность результатов. Полученные результаты устойчивы во времени и позволяют предприятию сфокусировать свои ресурсы и капитал в развитие наиболее перспективных продуктов. Управление номенклатурой наиболее ценных товаров позволяет создать финансовую устойчивость предприятия.
    3. Оптимизация ресурсов и времени. Использование методики позволяет высвободить дополнительные ресурсы как финансовые, так и временные.
    4. Универсальность анализа. Возможность применения методики ABC-анализа для других сфер деятельности предприятия.

    Другие направления использования ABC-анализа в компании

    Спектр использования данного метода повышения эффективности в хозяйственных системах крайне широк:

    • Оптимизация номенклатуры товаров.
    • Выделение ключевых поставщиков, подрядчиков, клиентов.
    • Повышение эффективности организации складских запасов.
    • Оптимизация производственного процесса.
    • Бюджетирование и управление маркетинговыми затратами.

    Недостатки ABC-анализа

    Помимо преимуществ методики ей присущи также и недостатки:

    1. Одномерность метода. ABC-анализ довольно простой аналитический метод и не позволяет группировать сложные многомерные объекты.
    2. Группировка товаров только на основе количественных показателей. Метод не строится только на количественной оценке нормы дохода по каждой номенклатуре товара и не оценивает качественную составляющую каждого товара, например, товары различной категории.
    3. Отсутствие группы убыточных товаров. Помимо товаров приносящих прибыль компании есть также товары, которые приносят убыток. В данном методе такие товары не отражены, в результате на практике, ABC-анализ трансформируется в ABCD анализ, где в группу “D” входят нерентабельные группы товаров.
    4. Влияние внешних факторов на продажи. Несмотря на довольно устойчивую структуру продаж по данной модели, на оценку объема продаж в будущем сильное влияние оказывают внешние экономические факторы: сезонность, неравномерность потребления и спроса, покупательная способность, влияние конкурентов и т.д. Влияние данных факторов не отражается в модели ABC-анализ.

    Резюме

    ABC-анализ продаж позволяет выделить целевые группы товаров, которые обеспечивают 80% прибыли компании. Данный метод повышает эффективность функционирования предприятия, проводит анализ и оптимизацию ресурсов, что в свою очередь отражается на финансовую устойчивости и рентабельности компании. Разобранный пример показывает простоту использования модели ABC для анализа ассортимента товаров и продаж. Метод может широко использоваться в других сферах компании для выделения целевых групп: клиентов, поставщиков, подрядчиков, персонала и т.д.

    Автор: к.э.н. Жданов Иван Юрьевич

    Пример ABC-анализа ассортимента
    расчет в Excel по шагам

    Покажем на примере как работает методика ABC-анализа. Возьмем ассортимент из 30 условных товаров.

    1. Цель анализа — оптимизация ассортимента.
    2. Объект анализа — товары.
    3. Параметр по которому будем производить разбиение на группы — выручка.
    4. Cписок товаров отсортировали в порядке убывания выручки.
    5. Подсчитали общую сумму выручки по всем товарам.

    Вычислили для каждого товара долю нарастающим итогом.

  • Подсчитали количество наименований товаров в каждой группе. A — 7, B — 10, C — 13.
  • Общее количество товаров в нашем примере 30.
  • Подсчитали долю количества наименований товаров в каждой группе. A — 23.3%, B — 33.3%, C — 43.3%.
  • Сравнили результат ABC-анализа с рекомендуемыми значениями.

    • Группа A80% выручки, 20% наименований
    • Группа B15% выручки, 30% наименований
    • Группа C5% выручки, 50% наименований

    Для списка товаров из нашего примера:

    • Группа A79% выручки, 23.3% наименований
    • Группа B16% выручки, 33.3% наименований
    • Группа C5% выручки, 43.3% наименований
  • Надо отметить, что зная выручку по каждому товару, можно получить еще много полезной информации, а не только разбиение на 3 группы. Как это можно сделать смотрите в таблице указанной ниже.

    Разработка Excel-таблиц
    экономической и управленческой
    тематики. Условия тут >>>

    Избавьтесь от утомительных
    расчетов с помощью этих Excel-таблиц >>>

    1. Платежный календарь
    2. Расчет себестоимости
    3. Расчет инвестиционных проектов
    4. Финансовый анализ
    5. Точка безубыточности. Рентабельность продаж
      Подробнее…

    Платежный календарь. График и прогноз платежей и поступлений

    • Платежные Календари на месяц, 3 месяца и год
    • Деб.и Кред. задолженность
    • Отсрочка, просроченные, с наступающим сроком
    • Контроль оплаты
    • Расчет ожидаемого остатка
    • Кассовый разрыв
      Подробнее…


    Расчет себестоимости и рентабельности продукции (услуг)

    • Себестоимость
    • Рентабельность
    • Маржинальный анализ
    • Точка безубыточности
    • Расходы в 10 валютах
      Подробнее…


    Расчет инвестиционных проектов

    • Дисконтир. потоки
    • WACC, NPV, IRR, ROI, PI
    • Срок окупаемости
    • Устойчивость проекта
    • Расчет и Сравнение семи проектов
      Подробнее…


    Финансовый анализ МСФО

    • Вертикальный и горизонтальный Анализ баланса и P&L
    • 36 коэффициентов
    • Динамика за 5 периодов
    • Риск банкротства
    • ДДС прямым и косвенным методом
    • Отчет об источниках и использовании денежных средств
      Подробнее…


    Финансовый анализ РСБУ (Россия)

    • Вертикальный и горизонтальный Анализ баланса и ОПУ
    • 70 коэффициентов
    • Динамика за 8 периодов
    • Риск банкротства
      Подробнее…


    Точка безубыточности. Рентабельность продаж

    • Расчет доходности при большом ассортименте
    • Прибыль
    • Наценка
    • Минимальная наценка
    • Маржинальный анализ
    • Точка безубыточности
      Подробнее…


    Оценка стоимости бизнеса

    • Все три основных подхода
    • Доходный
    • Рыночный (сравнительный)
    • Затратный (имущественный)
      Подробнее…


    Диаграмма Ганта. С семью дополнительными полезными функциями

    • Позволяет назначать ответственных
    • Контролировать выполнение этапа
    • Строит диаграмму ответственных
      Подробнее…

    Посмотрите полный список таблиц >>>

    Разработка Excel-таблиц
    экономической и управленческой
    тематики. Условия тут >>>

    Читайте также:  Разворот таблицы в Microsoft Excel
    Ссылка на основную публикацию
    Adblock
    detector