Xyz анализ продаж. пример расчета в excel

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

Приведем пример ABC анализа. Для его проведения используем офисную программу MS Excel.

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

1. Создадим таблицу в Excel с двумя столбцами: наименование клиента и выручка в руб. Возьмем 15 клиентов.
2. Отсортируем клиентов по убыванию выручки. Для этого необходимо выбрать вкладку Данные — Сортировка.
3. Теперь нам необходимо вычислить долю от общей прибыли. Для этого пропишем формулу в ячейке С3 = B2/$B$17 и для ячеек этого столбца выберем процентный формат ячеек.
4. Следующим шагом рассчитаем накопительную долю для каждого клиента. Для первого клиента накопительная доля будет равна доле, для последующих же она будет вычисляться как сумма его доли и предыдущего клиента. То есть для Клиента 8 в ячейке D3 будет прописана формула = С3+D2. Протянем эту формулу на оставшиеся ячейки. Таким образом у вас получится список клиентов выстроенных по возрастанию процентного соотношения.
5. Теперь вы с легкостью можете распределить данный список на категории A, B и С.
6. Далее для каждой группы клиентов определим долю от общей выручки и процент от общего числа клиентов.

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

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

Таким образом у нас получилось, что 33% наших клиентов принося нам максимум прибыли более 80%, 27% клиентов приносят нам 15% выручки и 40% приносят нам 6% выручки.

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

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

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

Играть в своей «лиге»

Сразу оговоримся: при интерпретации итога анализа следует понимать, что, скажем, товары группы Z, относящиеся к категории A (и в этом необычность комплексного анализа) будут ценнее, чем продукция типа X для категории B. Более того, прямое их сопоставление не вполне корректно — это все равно что, условно говоря, рассматривать возможности футбольных команд из разноуровневых лиг. Поэтому, анализируя перспективы по товарам категорий A, B и C, линейно сопоставлять их распределение по группам X, Y и Z — неправильно. Важна последовательность в интерпретации результатов по изделиям в привязке к своим «лигам».

Итак, подведем краткие итоги:

— товары категории X — «флагманы» продаж, их закупка у поставщиков должна быть стабильной, каналы поставки налажены и, по возможности, диверсифицированы (на случай «санкций» и иного рода явлений, не подконтрольных бизнесу);

— продукция класса Y также обязательно должна присутствовать на прилавке, выполняя поддерживающую функцию по отношению к товарам X и стимулируя общий спрос;

— товары типа Z можно если не исключать из оборота, то пробовать заменять экспериментальными образцами, которые могут, в потенциале, приобрести статус продукции категорий X и Y.

Все эти выводы имеют место быть при условии, что речь идет об анализе товаров в рамках одной группы — A, B или C. Как мы уже сказали выше, выявлять «усредненные» индикаторы здесь особого смысла не имеет.

Почему не стоит использовать Excel для ABC XYZ-анализа

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

Проблемы начинаются сразу же:

Представьте, что вам нужно сделать ABC XYZ-анализ для хотя бы 50000 товарных позиций. Уверены, что хотите сделать это вручную?
При большом количестве данных, даже на открытие файла потребуется затратить время. Что уж говорить про вычисления.
Excel позволяет использовать только 1 048 576 строк

Если данных больше, то уже не важно, насколько быстро он будет работать.
Проблема с открытием больших файлов — довольно распространенное явление в работе Excel. Связана она с использованием вычислительных функций, условным форматированием, созданием больших массивов данных и сводных таблиц и др.
Повторное использование и формирование отчетов затруднено из-за «тормозов» и больших массивов данных

После каждого действия приходится ждать.

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

ABC/XYZ в услугах

Есть компании где с десяток услуг и всё понятно без всякого анализа. Но как быть, если услуг более сотни или даже тысячи?

Одним из основных примеров такого ранжирования являются компании перевозчики грузов. Вот тут — то всё очень и очень непросто. Делить услуги можно параметрам:

• тоннажа,
• дальности,
• по типу груза и т.д.

+ В каждом параметре «сидит» несколько вариаций (порой их более сотни). Сложность в том, корректно ли будет сравнение когда тоннаж, дальность и тип груза одинаковые, а регион Подмосковья и Хабаровска отличаются? Отличия в платёжеспособности, деловой активности, развитость инфраструктуры в разы. Самый распространённый выход – деление на зоны, то есть, добавление некой надстройки в анализ.

В недвижимости ситуация ещё более запутана. (впрочем, это не только услуги) У крупных застройщиков добавляются конкретные объекты недвижимости (квартиры и нежилые помещения: …). Других особенностей в применении ABC/XYZ методике для компаний из сектора услуг почти нет.

Области его применения

Именно за практичность этого инструмента его любят многие предприниматели и специалисты. Особенно часто эта технология применяется в маркетинге, а именно: От стратегического маркетинга и распределения усилий допустим по направлениям целого холдинга, до ассортиментной политики. Также среди областей применения есть и маркетинговые исследования, где подобный инструментарий выделен отдельно. Добавлю, результаты работы с этим инструментом — благодарная «почва» где результат видно почти сразу, так как можно сильно экономить практически на всём: от закупок до рекламы. Помимо самого маркетинга ещё он применяется в такой области как: Склад и управление им: например, организация товарного запаса в соответствии с существующими товарными потоками. Количество областей применения можно продолжать, но только в том случае, если все функции «заточены» под маркетинг. По идее, даже бухгалтерия может ориентироваться на него.

Вместо заключения

Этот инструмент Вам ничего не напоминает? Почти все матрицы, как более ранние, так и более поздние. основаны на некоторых математических законах. В том числе, на законе Парето. Стратег и/или маркетолог всегда должен работать именно с ним, так как в нём сокрыты рычаги для работы над тонкими настройками в стратегии организации.

Выбор объекта и критерия анализа

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

Целью его применения является концентрирование внимания на наиболее значимых, приоритетных ассортиментных позициях, заказчиках, поставщиках.

АВС-анализ состоит из нескольких шагов:

1. Выбор объекта и критерия анализа.
2. Расчет нарастающего итога значения критерия анализа.
3. Выделение классификационных групп АВС-анализа.

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

Какие могут быть цели и как они влияют на то, каким образом мы будем проводить анализ? Рассмотрим пример:

Нам необходимо оценить, у каких поставщиков мы делаем основную долю закупок, для того чтобы проработать максимально выгодные условия для компании. В этом случае объектом АВС-анализа будут поставщики, а критерием анализа — объем закупки у поставщиков за определенный период в стоимостном выражении. Почему именно в стоимостном выражении? Во-первых, от разных поставщиков мы можем получать разные товары/материалы: в штуках, куб. метрах, килограммах — сравнивать разные измерения достаточно сложно. А во-вторых, и это самое главное, наша цель — проработать максимально выгодные условия для компании. Теперь представим, что мы закупаем что-то в очень большом количестве, но эта номенклатура имеет низкую стоимость. Очевидно, что, если мы будем проводить АВС-анализ по количеству закупаемых позиций, этот поставщик попадет в топ-20, но будет ли улучшение условий работы с этим поставщиком значительно способствовать улучшению в целом для компании? Очевидно, что нет, потому что улучшение условий — это отсрочка платежа, например, а оценить ее выгоду можно только в стоимостном выражении.

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

Если говорить об управлении запасами, то его задача  — обеспечить продажи. АВС-анализ проводится в данном случае с целью определения приоритетных позиций для обеспечения наличия товара под продажи. А продажи всегда важны именно в стоимостном выражении — выполнение плана продаж оценивают в нем. Если мы проведем АВС-анализ по продажам в количественном выражении без учета его стоимости, мы выделим товар, который продается в большом объеме, но его влияние на объем продаж в целом в стоимостном выражении будет незначительным. В результате такое выделение приоритетных позиций не позволит выполнить основную задачу управления запасами.

В управлении запасами АВС-анализ — это инструмент, с помощью которого мы определяем стратегию пополнения запасов с целью обеспечения продаж с оптимальными затратами. Объектом анализа в данном случае могут выступать номенклатурные группы, номенклатурные позиции и товарные категории.

Критериями в данном случае могут быть: объем продаж в стоимостном выражении, прибыль от продаж, доля прибыли, доля в обороте, количество запросов (количество заказов, в которых была номенклатурная позиция), рентабельность продаж.

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