Все способы:
- Использование ABC-анализа
- Способ 1: анализ при помощи сортировки
- Способ 2: использование сложной формулы
- Вопросы и ответы: 7
Одним из ключевых методов менеджмента и логистики является ABC-анализ. С его помощью можно классифицировать ресурсы предприятия, товары, клиентов и т.д. по степени важности. При этом по уровню важности каждой вышеперечисленной единице присваивается одна из трех категорий: A, B или C. Программа Excel имеет в своем багаже инструменты, которые позволяют облегчить проведение такого рода анализа. Давайте разберемся, как ими пользоваться, и что же собой представляет ABC-анализ.
ABC-анализ является своего рода усовершенствованным и приспособленным к современным условиям вариантом принципа Парето. Согласно методике его проведения, все элементы анализа разбиваются на три категории по степени важности:
| Категория | Элементы |
|---|---|
| A | Элементы, имеющие в совокупности более 80% удельного веса |
| B | Элементы, совокупность которых составляет от 5% до 15% удельного веса |
| C | Оставшиеся элементы, общая совокупность которых составляет 5% и менее удельного веса |
Отдельные компании применяют более продвинутые методики и разбивают элементы не на 3, а на 4 или 5 групп, но мы будем опираться на классическую схему ABC-анализа.
Способ 1: анализ при помощи сортировки
В Экселе ABC-анализ выполняется при помощи сортировки. Все элементы отсортировываются от большего к меньшему. Затем подсчитывается накопительный удельный вес каждого элемента, на основании чего ему присваивается определенная категория. Давайте на конкретном примере выясним, как указанная методика применяется на практике.
У нас имеется таблица с перечнем товаров, которые предприятие реализует, и соответствующим количеством выручки от их продажи за определенный период времени. Внизу таблицы подбит итог выручки в целом по всем наименованиям товаров. Стоит задача, используя ABC-анализ, разбить эти товары на группы по их важности для предприятия.
- Выделяем таблицу с данными курсором, зажав левую кнопку мышки, исключая шапку и итоговую строку. Переходим во вкладку «Данные». Производим щелчок по кнопке «Сортировка», расположенной в блоке инструментов «Сортировка и фильтр» на ленте.
Можно также поступить по-другому. Выделяем указанный выше диапазон таблицы, затем перемещаемся во вкладку «Главная» и выполняем щелчок по кнопке «Сортировка и фильтр», расположенной в блоке инструментов «Редактирование» на ленте. Активируется список, в котором выбираем в нем позицию «Настраиваемая сортировка».
- При применении любого из вышеуказанных действий запускается окно настройки сортировки. Смотрим, чтобы около параметра «Мои данные содержат заголовки» была установлена галочка. В случае её отсутствия, устанавливаем.
В поле «Столбец» указываем наименование той колонки, в которой содержатся данные по выручке.
В поле «Сортировка» нужно указать, по какому конкретному критерию будет выполняться сортировка. Оставляем предустановленные настройки – «Значения».
В поле «Порядок» выставляем позицию «По убыванию».
После произведения указанных настроек нажимаем на кнопку «OK» в нижней части окна.
- После выполнения указанного действия все элементы были отсортированы по выручке от большего к меньшему.
- Теперь нам следует рассчитать удельный вес каждого из элементов для общего итога. Создаем для этих целей дополнительный столбец, который так и назовем «Удельный вес». В первой ячейке данной колонки ставим знак «=», после чего указываем ссылку на ячейку, в которой находится сумма выручки от реализации соответствующего товара. Далее устанавливаем знак деления («/»). После этого указываем координаты ячейки, в которой содержится итоговая сумма реализации товаров по всему предприятию.
Учитывая тот факт, что указанную формулу мы будем копировать в другие ячейки столбца «Удельный вес» посредством маркера заполнения, то адрес ссылки на элемент, содержащий итоговую величину выручки по предприятию, нам нужно зафиксировать. Для этого делаем ссылку абсолютной. Выделяем координаты указанной ячейки в формуле и жмем на клавишу F4. Перед координатами, как мы видим, появился знак доллара, что свидетельствует о том, что ссылка стала абсолютной. При этом нужно учесть, что ссылка на величину выручки первого в списке товара (Товар 3) должна оставаться относительной.
Затем, чтобы произвести вычисления, жмем на кнопку Enter.
- Как видим, удельный вес выручки от первого товара, указанного в списке, отобразился в целевой ячейке. Чтобы произвести копирование формулы в диапазон ниже, ставим курсор в правый нижний угол ячейки. Происходит его трансформация в маркер заполнения, имеющий вид небольшого крестика. Жмем левую кнопку мыши и перетягиваем маркер заполнения вниз до конца колонки.
- Как видим, весь столбец заполнен данными, характеризующими удельный вес выручки от реализации каждого товара. Но величина удельного веса отображается в числовом формате, а нам нужно трансформировать его в процентный. Для этого выделяем содержимое столбца «Удельный вес». Затем перемещаемся во вкладку «Главная». На ленте в группе настроек «Число» имеется поле отображающее формат данных. По умолчанию, если вы не производили дополнительных манипуляций, там должен быть установлен формат «Общий». Щелкаем по пиктограмме в виде треугольника, расположенной справа от этого поля. В открывшемся списке форматов выбираем позицию «Процентный».
- Как видим, все значения столбца были преобразованы в процентные величины. Как и положено, в строке «Итого» указано 100%. Удельный вес товаров ожидаемо располагается в столбце от большего к меньшему.
- Теперь нам следует создать столбец, в котором бы отображалась накопленная доля с нарастающим итогом. То есть, в каждой строке к индивидуальному удельному весу конкретного товара будет прибавляться удельный вес всех тех товаров, которые расположены в перечне выше. Для первого товара в списке (Товар 3) индивидуальный удельный вес и накопленная доля будут равными, а вот у всех последующих к индивидуальному показателю нужно будет прибавить накопленную долю предыдущего элемента списка.
Итак, в первой строке переносим в столбец «Накопленная доля» показатель из колонки «Удельный вес».
- Далее устанавливаем курсор во вторую ячейку столбца «Накопленная доля». Тут нам придется применить формулу. Ставим знак «равно» и складываем содержимое ячейки «Удельный вес» этой же строки и содержимое ячейки «Накопленная доля» из строки выше. Все ссылки оставляем относительными, то есть, не производим с ними никаких манипуляций. После этого выполняем щелчок по кнопке Enter для вывода итогового результата.
- Теперь нужно скопировать данную формулу в ячейки данного столбца, которые размещены ниже. Для этого применяем маркер заполнения, к которому мы уже прибегали при копировании формулы в столбце «Удельный вес». При этом, строку «Итого» захватывать не нужно, так как накопленный результат в 100% будет отображаться на последнем товаре из списка. Как видим, все элементы нашего столбца после этого были заполнены.
- После этого создаем столбец «Группа». Нам нужно будет сгруппировать товары по категориям A, B и C согласно указанной накопленной доле. Как мы помним, все элементы распределяются по группам по следующей схеме:
Категория Действие A До 80% B Следующие 15% C Оставшиеся 5% Таким образом, всем товарам, накопленная доля удельного веса которых входит в границу до 80%, присваиваем категорию A. Товарам с накопленным удельным весом от 80% до 95% присваиваем категорию B. Оставшейся группе товаров со значением более 95% накопленного удельного веса присваиваем категорию C.
- Для наглядности можно произвести заливку указанных групп разными цветами. Но это уже по желанию.
Таким образом, мы разбили элементы на группы по уровню важности, используя при этом ABC-анализ. При использовании некоторых других методик, о чем говорилось уже выше, применяют разбиение на большее количество групп, но сам принцип разбиения при этом остается практически неизменным.
Урок: Сортировка и фильтрация в Экселе
Способ 2: использование сложной формулы
Безусловно, применение сортировки – это наиболее распространенный способ проведения ABC-анализа в Экселе. Но в некоторых случаях требуется провести данный анализ без перестановки строк местами в исходной таблице. В этом случае на помощь придет сложная формула. Для примера будем использовать ту же исходную таблицу, что и в первом случае.
- Добавляем к исходной таблице, содержащей наименование товаров и выручку от продажи каждого из них, колонку «Группа». Как видим, в данном случае мы можем не добавлять столбцы с расчетом индивидуальных и накопительных долей.
- Производим выделение первой ячейки в столбце «Группа», после чего выполняем щелчок по кнопке «Вставить функцию», расположенной возле строки формул.
- Производится активация Мастера функций. Перемещаемся в категорию «Ссылки и массивы». Выбираем функцию «ВЫБОР». Делаем щелчок по кнопке «OK».
- Активируется окно аргументов функции ВЫБОР. Синтаксис её представлен следующим образом:
=ВЫБОР(Номер_индекса;Значение1;Значение2;…)Задачей данной функции является вывод одного из указанных значений, в зависимости от номера индекса. Количество значений может достигать 254, но нам понадобится всего три наименования, которые соответствуют категориям ABC-анализа: A, B, С. Можем сразу вводить в поле «Значение1» символ «A», в поле «Значение2» — «B», в поле «Значение3» — «C».
- А вот с аргументом «Номер индекса» придется основательно повозиться, встроив в него несколько дополнительных операторов. Устанавливаем курсор в поле «Номер индекса». Далее жмем по пиктограмме, имеющей вид треугольника, слева от кнопки «Вставить функцию». Открывается список недавно используемых операторов. Нам нужна функция ПОИСКПОЗ. Так как в списке её нет, то жмем по надписи «Другие функции…».
- Снова производится запуск окна Мастера функций. Опять переходим в категорию «Ссылки и массивы». Находим там позицию «ПОИСКПОЗ», выделяем её и делаем щелчок по кнопке «OK».
- Открывается окно аргументов оператора ПОИСКПОЗ. Синтаксис его имеет следующий вид:
=ПОИСКПОЗ(Искомое_значение;Просматриваемый_массив;Тип_сопоставления)Предназначение данной функции – это определение номера позиции указанного элемента. То есть, как раз то, что нам нужно для поля «Номер индекса» функции ВЫБОР.
В поле «Просматриваемый массив» сразу можно задать следующее выражение:
{0:0,8:0,95}Оно должно быть именно в фигурных скобках, как формула массива. Не трудно догадаться, что эти числа (0; 0,8; 0,95) обозначают границы накопленной доли между группами.
Поле «Тип сопоставления» не обязательное и в данном случае мы его заполнять не будем.
В поле «Искомое значение» устанавливаем курсор. Далее снова через описанную выше пиктограмму в виде треугольника перемещаемся в Мастер функций.
- На этот раз в Мастере функций производим перемещение в категорию «Математические». Выбираем наименование «СУММЕСЛИ» и жмем на кнопку «OK».
- Запускается окно аргументов функции СУММЕСЛИ. Указанный оператор суммирует ячейки, отвечающие определенному условию. Его синтаксис такой:
=СУММЕСЛИ(диапазон;критерий;диапазон_суммирования)В поле «Диапазон» вводим адрес колонки «Выручка». Для этих целей устанавливаем курсор в поле, а затем, произведя зажим левой кнопки мыши, выделяем все ячейки соответствующего столбца, исключая значение «Итого». Как видим, адрес тут же отобразился в поле. Кроме того, нам нужно сделать данную ссылку абсолютной. Для этого производим её выделение и жмем на клавишу F4. Адрес выделился знаками доллара.
В поле «Критерий» нам нужно задать условие. Вписываем следующее выражение:
">"&Затем сразу же после него заносим адрес первой ячейки столбца «Выручка». Делаем координаты по горизонтали в данном адресе абсолютными, дописав перед буквой знак доллара с клавиатуры. Координаты по вертикали оставляем относительными, то есть, перед цифрой никакого знака быть не должно.
После этого не жмем на кнопку «OK», а кликаем по наименованию функции ПОИСКПОЗ в строке формул.
- Затем мы возвращаемся в окно аргументов функции ПОИСКПОЗ. Как видим, в поле «Искомое значение» появились данные заданные оператором СУММЕСЛИ. Но это ещё не все. Переходим в это поле и уже к имеющимся данным добавляем знак «+» без кавычек. Затем вносим адрес первой ячейки столбца «Выручка». И опять делаем координаты по горизонтали данной ссылки абсолютными, а по вертикали оставляем относительными.
Далее берем все содержимое поля «Искомое значение» в скобки, после чего ставим знак деления («/»). После этого снова через пиктограмму треугольника переходим к окну выбора функций.
- Как и в прошлый раз в запустившемся Мастере функций ищем нужный оператор в категории «Математические». На этот раз искомая функция называется «СУММ». Выделяем её и жмем на кнопку «OK».
- Открывается окно аргументов оператора СУММ. Его главное предназначение – это суммирование данных в ячейках. Синтаксис этого оператора довольно прост:
=СУММ(Число1;Число2;…)Для наших целей понадобится только поле «Число1». Вводим в него координаты диапазона столбца «Выручка», исключая ячейку, которая содержит итоги. Подобную операцию мы уже проводили в поле «Диапазон» функции СУММЕСЛИ. Как и в тот раз, координаты диапазона делаем абсолютные, выделив их, и нажав на клавишу F4.
После этого жмем по клавише «OK» внизу окна.
- Как видим, комплекс введенных функций произвел вычисление и выдал результат в первую ячейку столбца «Группа». Первому товару была присвоена группа «A». Полная формула, примененная нами для данного вычисления, выглядит следующим образом:
=ВЫБОР(ПОИСКПОЗ((СУММЕСЛИ($B$2:$B$27;">"&$B2)+$B2)/СУММ($B$2:$B$27);{0:0,8:0,95});"A";"B";"C")Но, конечно, в каждом конкретном случае координаты в данной формуле будут отличаться. Поэтому её нельзя считать универсальной. Но, используя то руководство, которое было приведено выше, можно вставить координаты любой таблицы и с успехом применять данный способ в любой ситуации.
- Впрочем, это ещё не все. Мы произвели расчет только для первой строки таблицы. Для того, чтобы полностью заполнить данными столбец «Группа», нужно скопировать эту формулу в диапазон ниже (исключая ячейку строки «Итого») с помощью маркера заполнения, как мы уже делали не раз. После того, как данные будут внесены, ABC-анализ можно считать выполненным.
Как видим, результаты, полученные при помощи варианта с применением сложной формулы, ничуть не отличаются от тех результатов, которые мы проводили путем сортировки. Всем товарам присвоены те же самые категории, только при этом строки не изменили своего начального положения.
Урок: Мастер функций в Экселе
Программа Excel способна значительно облегчить проведение ABC-анализа для пользователя. Это достигается использованием такого инструмента, как сортировка. После этого производится подсчет индивидуального удельного веса, накопленной доли и, собственно, разбиение на группы. В тех случаях, когда изменение первоначального положения строк в таблице не допускается, можно применить метод с использованием сложной формулы.
Пройдите тест, узнайте какой профессии подходите
Работать самостоятельно и не зависеть от других
Работать в команде и рассчитывать на помощь коллег
Организовывать и контролировать процесс работы
Для кого эта статья:
- специалисты по аналитике и управлению запасами
- бизнесмены и директора, заинтересованные в оптимизации процессов
- студенты и начинающие аналитики, желающие освоить Excel и анализ данных
Когда передо мной впервые встала задача оптимизировать товарные запасы в компании, я потратил недели на изучение различных методологий. Но именно ABC-анализ в Excel стал тем универсальным инструментом, который не требовал серьезных вложений и давал мгновенный результат. Эта мощная методика позволяет выделить самые ценные активы и сосредоточить на них основные ресурсы. Не нужно быть аналитическим гуру – достаточно пошагово следовать инструкции, чтобы превратить обычную электронную таблицу в машину принятия бизнес-решений. 📈
Хотите освоить не только ABC-анализ, но и десятки других профессиональных инструментов, которые сделают вас незаменимым специалистом на рынке труда? Курс «Excel для работы» с нуля от Skypro раскроет перед вами все секреты работы с данными – от базовых формул до сложных аналитических моделей. Всего за несколько недель вы научитесь автоматизировать расчеты и анализировать большие объемы информации, что резко повысит вашу эффективность и ценность как профессионала.
Что такое ABC-анализ и чем он полезен для бизнеса
ABC-анализ – это метод классификации ресурсов по степени их важности, основанный на принципе Парето, который утверждает, что 20% усилий дают 80% результата. В контексте бизнеса это означает, что относительно небольшое количество товаров, клиентов или других объектов обычно генерирует большую часть прибыли или оборота.
Суть метода заключается в разделении всех анализируемых объектов на три группы:
- Группа A – наиболее ценные объекты, которые обычно составляют около 20% от общего количества, но дают около 80% результата
- Группа B – объекты средней важности, составляющие примерно 30% от общего количества и дающие около 15% результата
- Группа C – наименее ценные объекты, составляющие примерно 50% от общего количества, но приносящие всего около 5% результата
Андрей Петров, руководитель отдела закупок
Когда я пришел в компанию, отдел закупок работал по старинке – закупали все позиции с одинаковой периодичностью и вниманием. Мы тратили огромное количество времени на управление всем ассортиментом из 5000+ наименований. После внедрения ABC-анализа картина кардинально изменилась. Мы сфокусировались на 850 позициях из группы A, которые давали основную прибыль. Для них мы разработали систему ежедневного мониторинга и предиктивного анализа спроса. Товары из группы B стали проверяться еженедельно с менее строгим контролем, а группа C – лишь раз в месяц по упрощенной схеме. За первый квартал нам удалось снизить товарные запасы на 32%, а нехватка товаров группы A сократилась до исторического минимума в 0,5%. При этом высвободившиеся ресурсы мы направили на развитие новых направлений.
Преимущества использования ABC-анализа для бизнеса:
К 2025 году, по данным исследований McKinsey, компании, применяющие аналитические методы для оптимизации бизнес-процессов, демонстрируют на 23% более высокий рост прибыли по сравнению с конкурентами, игнорирующими аналитику. ABC-анализ – один из наиболее доступных инструментов такой оптимизации. 🔍
Подготовка данных для проведения ABC-анализа в Excel
Качество результатов ABC-анализа напрямую зависит от правильной подготовки исходных данных. Следуйте этим шагам, чтобы избежать распространенных ошибок:
- Определите объект анализа. Это могут быть товары, клиенты, поставщики или любой другой ресурс вашего бизнеса.
- Выберите параметр для анализа. Обычно используют выручку, прибыль, количество продаж или другие количественные показатели.
- Определите период анализа. Рекомендуется использовать данные за значимый промежуток времени (квартал, полгода, год).
- Структурируйте данные. Создайте таблицу с минимум двумя столбцами: идентификатор объекта и значение параметра.
Пример структуры данных для ABC-анализа продаж товаров:
Важные рекомендации по подготовке данных:
- Проверьте данные на наличие ошибок, дублей и аномальных значений
- Убедитесь, что у вас есть все необходимые записи (полнота данных)
- По возможности используйте данные из единой информационной системы
- Для товаров рекомендуется использовать уникальные артикулы, а не только названия
- Если анализируете несколько параметров (например, выручку и прибыль), создайте отдельные столбцы для каждого
Когда данные подготовлены, лучше всего преобразовать их в таблицу Excel (Table). Для этого выделите все данные и нажмите Ctrl+T или воспользуйтесь командой «Вставка» → «Таблица». Это облегчит дальнейшую работу с ними. 📋
Таблицы Excel предоставляют множество преимуществ при анализе данных:
- Автоматическое расширение формул и форматирования при добавлении новых строк
- Возможность применять фильтрацию и сортировку
- Упрощение ссылок в формулах благодаря структурированным ссылкам
- Возможность быстрого создания сводных таблиц
Расчёт ABC-анализа в Excel: формулы и функции
После подготовки исходных данных переходим к непосредственному расчёту ABC-анализа. Я разобью процесс на 5 последовательных шагов, используя конкретные формулы Excel.
Шаг 1: Сортировка данных по убыванию выбранного параметра
Выделите диапазон с данными и отсортируйте по анализируемому параметру от большего к меньшему. Можно использовать сочетание клавиш Alt+A+SS или найти команду «Сортировка» на вкладке «Данные».
Шаг 2: Расчет доли каждой позиции от общей суммы
Добавьте столбец «Доля, %» и рассчитайте процентную долю каждой позиции. Формула будет выглядеть примерно так:
Где D2 – ячейка с значением анализируемого параметра для текущей строки, а $D$2:$D$100 – весь диапазон значений (с абсолютными ссылками).
Шаг 3: Расчет накопительной доли
Добавьте столбец «Накопительная доля, %» и используйте следующую формулу:
Где E2 – ячейка с процентной долей текущей строки, а F1 – ячейка с накопительной долей предыдущей строки.
Шаг 4: Применение условия для определения группы
Добавьте столбец «Группа» и используйте функцию ЕСЛИ с вложенными условиями:
Где F2 – ячейка с накопительной долей текущей строки. Условия могут быть скорректированы в зависимости от специфики вашего бизнеса.
Мария Соколова, аналитик данных
Когда я проводила ABC-анализ для интернет-магазина с ассортиментом более 10 000 товаров, руководство очень удивилось результатам. Оказалось, что всего 7% товаров приносили 80% выручки. При этом на маркетинг и продвижение компания тратила бюджет почти равномерно по всему ассортименту. Но самое интересное началось, когда мы провели двойной ABC-XYZ анализ. Мы обнаружили, что несколько позиций категории A имели очень нестабильные продажи (группа Z). Исследовав их глубже, мы выявили сезонный характер спроса и разработали специальную стратегию продвижения с учетом этой особенности. Через квартал эти товары перешли в категорию AY, а общий оборот магазина вырос на 17%. Клиент был настолько впечатлен, что попросил меня провести тренинг по Excel и аналитике для всего отдела маркетинга.
Шаг 5: Добавление итоговой сводки по группам
Создайте отдельную таблицу с итогами по каждой группе, используя функции СУММЕСЛИМН и СЧЁТЕСЛИМН:
Аналогичные формулы создайте для групп B и C.
Для более сложных случаев, например, если вам нужно провести многоуровневый ABC-анализ или объединить его с XYZ-анализом, можно использовать сводные таблицы. Они обеспечивают гибкость и наглядность при работе с большими объемами данных. 🧮
Не знаете, в какую сторону развивать карьеру? Аналитика данных может стать вашим призванием! Проверьте свою предрасположенность к работе с цифрами и таблицами с помощью Теста на профориентацию от Skypro. Всего за 5 минут вы получите персонализированный отчет о своих сильных сторонах и направлениях для профессионального роста. Возможно, именно аналитические способности – ваш скрытый талант, который поможет построить успешную карьеру!
Визуализация результатов ABC-анализа с помощью диаграмм
Визуализация – ключевой аспект анализа данных, который превращает цифры в наглядные образы, помогающие быстрее понять результаты и принять решения. Для ABC-анализа рекомендую использовать несколько видов диаграмм, каждая из которых акцентирует внимание на разных аспектах.
1. Столбчатая диаграмма с накопительной кривой
Это наиболее информативный формат визуализации ABC-анализа. Для его создания:
- Выделите данные о продажах по каждой позиции и столбец с накопительной долей
- Перейдите на вкладку «Вставка» и выберите «Комбинированная диаграмма»
- Для продаж выберите тип «Гистограмма», для накопительной доли – «График с маркерами»
- Настройте вторичную ось для накопительной доли
На получившейся диаграмме будет наглядно видна граница между группами A, B и C по точкам пересечения накопительной кривой с уровнями 80% и 95%.
2. Круговая диаграмма для представления долей групп
Круговая диаграмма отлично иллюстрирует, какую долю общих продаж занимает каждая группа:
- Создайте сводную таблицу с суммой продаж по каждой группе (A, B, C)
- Выделите данные и выберите «Вставка» → «Круговая диаграмма»
- Добавьте подписи данных с процентами и значениями
Обычно эта диаграмма наглядно демонстрирует принцип Парето – большая часть круга (около 80%) будет занята группой A.
3. Пузырьковая диаграмма для многомерного анализа
Если вы проводите двойной анализ, например ABC-XYZ, пузырьковая диаграмма позволит представить три измерения одновременно:
- По оси X отложите стабильность спроса (коэффициент вариации)
- По оси Y отложите объем продаж
- Размер пузырьков может отражать прибыльность или другой важный показатель
- Разными цветами обозначьте группы A, B и C
Такая диаграмма помогает визуально определить товары, требующие особого внимания: высокие продажи, но нестабильный спрос (AZ), или низкие продажи, но с высокой маржинальностью.
4. Линейчатая диаграмма Парето
Эта диаграмма особенно хороша для представления отчетов руководству:
- Отсортируйте данные по убыванию параметра анализа
- Выделите первые 10-20 позиций и столбец с накопительной долей
- Создайте комбинированную диаграмму: линейчатую для значений и график для накопительной доли
Часто этой диаграммы достаточно, чтобы наглядно продемонстрировать, что всего несколько позиций обеспечивают большую часть результата.
Помните, что Excel предлагает множество инструментов для настройки внешнего вида диаграмм. Используйте заголовки, легенды, подписи данных и цветовое кодирование для улучшения восприятия. Хорошая визуализация должна моментально давать ответы на ключевые вопросы без необходимости детального изучения цифр. 📊
Практические советы по интерпретации данных ABC-анализа
Проведение ABC-анализа – лишь половина дела. Ключевая ценность этого метода раскрывается при правильной интерпретации полученных результатов и их применении в бизнес-решениях. Рассмотрим стратегии работы с каждой группой и типичные ошибки, которых следует избегать.
Стратегии работы с группой A (80% результата)
- Внедрите непрерывный мониторинг продаж и остатков – даже кратковременная нехватка этих товаров критична
- Разработайте систему раннего оповещения о снижении продаж или изменениях в тенденциях
- Создайте повышенный страховой запас для защиты от сбоев в поставках
- Назначьте персональных менеджеров для ключевых позиций группы A
- Инвестируйте в улучшение маркетинга этих товаров – они являются вашим основным источником дохода
- Регулярно анализируйте причины успеха этих позиций для масштабирования опыта
Стратегии работы с группой B (15% результата)
- Установите периодический контроль (еженедельный или двухнедельный)
- Поддерживайте оптимальный уровень запаса без избыточных вложений
- Разрабатывайте стратегии продвижения этих товаров в группу A
- Анализируйте сезонность и корректируйте закупки в соответствии с ней
- Рассмотрите возможности повышения маржинальности этих позиций
Стратегии работы с группой C (5% результата)
- Минимизируйте временные затраты на управление этими позициями
- Автоматизируйте процессы закупки с использованием простых алгоритмов
- Рассмотрите возможность укрупнения заказов для снижения логистических издержек
- Регулярно пересматривайте необходимость наличия каждой позиции в ассортименте
- Для низкорентабельных товаров группы C оцените их косвенный вклад (например, привлечение клиентов)
Типичные ошибки при интерпретации ABC-анализа
Интеграция ABC-анализа с другими методами
Максимальную ценность ABC-анализ приносит в сочетании с другими аналитическими инструментами:
- ABC-XYZ анализ – добавляет измерение стабильности спроса, помогая точнее настроить систему управления запасами
- ABC-FMR анализ – учитывает частоту обращения к товару, что особенно важно для складской логистики
- ABC-VEN анализ – применяется в медицине и фармацевтике для оценки критичности отсутствия товара
Помните, что ABC-анализ – не разовое мероприятие, а постоянно действующий инструмент. В современных условиях быстро меняющегося рынка регулярное обновление анализа критически важно для поддержания конкурентоспособности бизнеса. 🔄
Использование ABC-анализа в Excel – это первый шаг к построению системы управления на основе данных. Овладев этим инструментом, вы научитесь выделять самое важное, отсекать ненужное и фокусироваться на том, что действительно влияет на результат. В мире, где объем информации неуклонно растет, способность выделять действительно значимое превращается из просто полезного навыка в критически важное конкурентное преимущество. Начните с простого ABC-анализа сегодня, и это станет вашим первым шагом к построению экономически эффективного бизнеса завтра.
ABC XYZ анализ — удобный метод оценки эффективности работы «жизненно важных» отделов компании: продаж, маркетинга, склада, финансов. Представляем подробную инструкцию, как выполнить ABC и XYZ-анализ в Excel.
С общими принципами проведения ABC и XYZ-анализа можно ознакомиться здесь. А ниже — пошаговая инструкция, как сделать ABC-анализ в Excel.
Содержание:
- 4 вопроса до начала ABC-анализа
- ABC-анализ в Excel: пошаговая инструкция, рабочие образцы с формулами
- Сортировка выручки по убыванию
- Доля каждой строки в общем параметре
- Определяем группу
- XYZ-анализ в Excel: оценка динамики продаж
- Выгружаем данные из учётной системы
- Рассчитываем коэффициент вариации
- Присваиваем значения XZY и соединяем с ABC
- Цель. Зачем вы проводите исследование? Увеличить выручку компании, исключить возможность упущенной выгоды и т.п.
- Результат. Как вы сможете применить полученные значения? Оптимизируем складские запасы, пересмотрим условия договоров и т.п.
- Источники данных. Как вы соберете исходные данные: объект и параметр анализа? Объект анализа — перечень товаров, параметр — выручка в количественном и денежном выражении.
- Матрица. Какое АВС XYZ процентное распределение закладывать в расчет? Классический вариант на основе принципа Парето: 80% приносят выручки приносят 20% ключевых клиентов. Чтобы назначить распределение по группам, нужно знать специфику работы компании, жизненные циклы и сезональность. Ошибки в матрице могут привести к тому, что в неприбыльной группе С окажутся важные покупатели с редкими закупками.
ABC-анализ в Excel: пошаговая инструкция, рабочие образцы с формулами
Ассортиментный ABC анализ проведем на примере компании по продаже запасный частей для сельскохозяйственной техники.
Количество товара — более 5 000 позиций. Объединяем их в группы по видам номенклатуры.
Из учетной системы выгружаем данные за 2020 год:
- количество продаж с разбивкой по кварталам;
- цена реализации за единицу;
- выручка итого за год в рублях. Важно использовать одну валюту для всего отчета, чтобы исключить влияние курсовых разниц.
Сортировка выручки по убыванию
Выделяем диапазон ячеек: вся таблица вместе с заголовками без строки «Итого».
В ниспадающем меню выбираем:
Данные — Сортировка — Сортировать по:
- столбец «Выручка»
- сортировка «Значения»
- порядок «По убыванию»
Нажимаем «Ок».
Система выстраивает таблицу по убыванию размера выручки в столбце D.
Доля каждой строки в общем параметре
Определяем долю каждой номенклатуры в выручке:
- добавляем графу Доля (Е). Формат ячеек процентный;
- в строку 2 для товара 6 вводим формулу: выручка товара 6 / выручка итого;
- протягиваем формулу вниз по всем товарам.
Добавляем графу F и рассчитываем Долю накопительным итогом: складываем текущее значение со всеми предыдущими.
Символ & предупреждает Excel, что формулу нельзя двигать:
- & перед буквой — по столбцам;
- & перед цифрой — по строкам.
Перед тем как создавать ABC-таблицу проверьте долю каждого товара в общем значении (выручки, запасах, себестоимости и пр.). Проводить ABC аналитику бессмысленно, если объект распределяется примерно в равных долях. Каждый показатель вносит одинаковый вклад в результат.
Определяем группу
Создаем графу Группа. Каждому товару присваиваем значения А, В, С в зависимости от доли в выручке.
Руководство утвердило матрицу:
| Группа | Диапазон |
|---|---|
| A | до 70% |
| B | 70-90% |
| C | 90-100% |
В ячейке G2 прописываем формулу =ЕСЛИ(F2<=70%;"A";ЕСЛИ(F2>=90%;"C";"В")). Протягиваем формулу вниз по всем товарам.
В примере для наглядности проценты заданы цифрами.
В рабочем файле Excel вместо процентов ссылки на ячейки со значениями матрицы. При изменении параметров матрицы формула будет автоматически пересчитываться по всем товарам.
В столбце G каждой номенклатурной группе присвоен код А, В, С.
В группу А попали товары, которые приносят основную прибыль.
В группу В — продукция компании, на которую нерегулярный спрос.
Группа С — товары, которые зарабатывают только 10% от выручки.
XYZ-анализ в Excel: оценка динамики продаж
XYZ исследование позволит увидеть изменения спроса на продукцию компании.
Выгружаем данные из учётной системы
Создаем таблицу с количеством продаж за 2020 год по каждой товарной группе по каждому кварталу.
Рассчитываем коэффициент вариации
Вариация — степень разброса значений в числовой последовательности. Показывает насколько данные отклоняются от средних показателей. В финансах этот коэффициент оценивает изменчивость, волатильность, сезональнальность. Чем он меньше, тем стабильнее оцениваемый параметр (спрос на товар, движение по складу, платежи и т.д.).
Создаем графу Средние продажи. В строку 3 вводим формулу =СРЗНАЧ(B3:E3) и копируем ее для всех товарных позиций.
Создаем графу Стандартное отклонение. Стандартное отклонение / Средние продажи.
В строку 3 вводим формулу =СТАНДОТКЛОН(B3:E3) и копируем ее для всех товарных позиций.
Создаем графу Вариация, %. Вводим формулу:
Столбец Стандартное отклонение / Столбец Средние продажи
-
XYZ-анализ в Excel: формула расчёта коэффициента вариации -
XYZ-анализ в Excel: рассчитанный коэффициент вариации
Присваиваем значения XZY и соединяем с ABC
Руководство утвердило матрицу XYZ аналитики:
| Группа | Диапазон |
|---|---|
| X — постоянный спрос | до 15% |
| Y — изменчивый спрос, сезональность | от 15% до 50% |
| Z — случайный спрос | больше 50% |
Ранжируем полученные результаты с помощью функции Excel «ЕСЛИ».
В ячейку J3 вводим формулу: =ЕСЛИ(I3<=15%;"X";ЕСЛИ(I3>=50%;"Z";"Y")). Копируем формулу по всем товарным срокам.
-
XYZ-анализ в Excel: группы товаров по методу XYZ — формула -
XYZ-анализ в Excel: группы товаров по методу XYZ — результат
Создаем графу Группа по методу ABC. Подтягиваем код группы из таблицы ABC анализа с помощью формулы: =ВПР(A3;ABC!$A$1:$G$12;7;0)
Как настроить формулу ВПР:
Задача функции: по коду товара в исходной таблице найти значение А, В или С и перенести его отчётную таблицу XYZ.
А3 — параметр, по которому ищем значение, например «Товар 6».
ABC!$A$1:$G$12 — ссылка на диапазон исходной таблицы. В ней строго в первом столбце должен быть параметр, по которому ищем значение «Товар 6».
7 — порядковый номер столбца, в котором в исходной находятся значения (коды А, В, С)
0 — значение ЛОЖЬ. Для Ecxel признак того, что искомый результат должен соответствовать всем 3-м предыдущим условиям.
По каждому товару получаем двойную кодировку ABC и XYZ аналитики.
Для наглядности можно скрепить лва кода по каждому товару.
В столбец L для каждой строки вводим формулу =K&J.
Товары AX — высокоприбыльные позиции, которые формируют 70% выручки. На них стабильный спрос.
Товары CZ — позиции с самым низким спросом. Сюда могут попасть как неликвиды, так и элитные товары с редким спросом. Требуется дополнительная аналитика.
Подробнее о сути, эффективности и недостатках ABC XYZ анализа читайте здесь.
Новости
Дата публикации: 26-12-2018 14421
В статье мы расскажем вам что такое ABC анализ и XYZ анализ, его применение в бизнесе, как можно их провести при помощи офисной программы Ms Excel.
Логистические и маркетинговые подразделения многих компаний применяют в своей работе инструменты, которые помогают выстроить ведение бизнеса наилучшим образом. К таким механизмам относится ABC XYZ анализ. Применяя их для оптимизации процессов, протекающих в компании, вам не потребуется нанимать специалистов. Вы сможете сами выстроить наиболее подходящую для вас стратегию развития компании.
Содержание:
- ABC анализ
- Метод XYZ анализа
- Применение ABC XYZ анализ
- Как сделать ABC анализ
- Пример ABC анализа в Excel
- Как сделать XYZ анализ
- XYZ анализ в Excel
- Использование ABC XYZ анализа
- ABC XYZ анализ Excel
ABC анализ
Метод ABC анализа заключается в том, что вы из множества одинаковых элементов, выбираете те, на которые стоит обратить внимание для достижения определенной цели.
Такой анализ применяется в разных областях, например, при анализировании базы данных клиентов, для улучшения подбора товаров, для улучшения продаж и т. д.
В основе ABC анализа лежит идея Парето. Он утверждал, что вкладывая 20 % вы можете получить 80. На это стоит обратить внимание и двигаться в правильном направлении.
При использовании такого анализа анализируемые объекты условно делятся на 3 вида:
- A — самые важные элементы, которые составляют 20% от всех и от которых вы получаете 80% результата.
- B – средние элементы. К ним мы относим 30% от общей массы, и они могут принести вам 15% прибыли.
- С — остальные элементы. Оставшиеся 50%, которые приносят только 5%.
Распределять по группам таким образом можно все, что вы хотите проанализировать: поставщиков, складские запасы, покупателей, продажи и т. д. Анализируемые элементы должны содержать данные статистики.
Вывод ABC анализа — разделение анализируемых элементов на группы по степени влияния на работу бизнеса.
Цель ABC анализа — разделение объектов по приносимым ими результатов.
Метод XYZ анализа
XYZ анализ — метод, который используют для понятия объемов продаж.
То есть с помощью него можно определить уровень продаж определенных товаров или услуг, дать анализ поведения клиентов. С помощью него можно сгруппировать товары по количеству продаж, посмотреть от чего зависит уровень продаж в разное время.
ABC анализ помогает определить товары, которые приносят максимальную прибыль, а XYZ анализ даст вам информацию об их спросе.
Применение ABC XYZ анализ
Специалисты используют ABC XYZ анализ для анализирования прибыли, просматривая различные факторы, которые влияют на нее.
Просматриваются все виды товаров и услуг. Также можно проанализировать сколько клиенты заказывают. Здесь необходимо просмотреть базу клиентов.
Для анализирования объема поставок, анализируется база данных поставщиков. С помощью таких видов анализа вы можете просмотреть дебиторскую задолженность и тех, кто вам должен и какие суммы.
С помощью данных видов анализа вы можете узнать кто приносит вам больше прибыли и платит чаще других.
Анализируя клиентскую базу данных с помощью ABC анализа, вы можете условно разделить всех клиентов на 3 группы: A, B, C. То есть на больших, средних и малых соответственно.
При этом нет единого правила, по которому можно произвести условное разделение. Все зависит от самого бизнеса и его объемов продаж. Так например для малого бизнеса к крупным клиентам можно отнести тех, которые приносят вашей компании примерно 150 тыс. рублей. При этом для крупных компаний с такой суммой клиент отнесется к категории C, а в категории А будет список клиентов, которые приносят компании миллионы.
Применяя к этой же клиенткой базе XYZ анализ, вы сможете увидеть кто из клиентов чаще совершает покупки, а кто купил товар или услугу у вас всего лишь один раз.
За счет применения abc анализа вы можете определить количество целевой аудитории в ваших продажах. Все виды торговли (оптовая, розничная) включают в себя несколько этапов сделки. Процесс покупки товаров или услуг можно представить в виде воронки продаж. Она выглядит как перевернутый конус, разделенный на уровни.
Каждый уровень говорит о статусе клиентов по отношению к продавцу. Например, самый верх конуса (большая часть) потенциальные клиенты, далее идут те, кто обратился в компанию, затем те, которые хотят купить, далее клиенты обсуждающие стоимость и наконец самый пик воронки — покупатели, купившие товар или услугу.
ABC анализ помогает вам увидеть сколько клиентов приобрели у вас товар, каким образом они узнали о вашей продукции и компании, кто помог им совершить покупку из ваших сотрудников и т. п.
Так как вы видите все наглядно, то сможете выбрать правильную стратегию развития бизнеса, промотивировать сотрудников, проанализировать их работу и сделать правильные выводы.
После проведения ABC XYZ вы сможете понять на каких клиентов вам стоит обратить внимание. В приоритете у вас должны быть покупатели, которые приносят вам большую прибыль, причем постоянно, а не единоразово. Чтобы поддержать достигнутый уровень получения прибыли от них, вам следует провести ряд мероприятий.
Ваша работа будет ориентирована на разные группы потенциальных клиентов. Как правило, к самой прибыльной группе относится малое количество клиентов. С ними вам необходимо выстроить маркетинговые отношения.
С теми клиентами, которые приобретают ваши товары или услуги, но редко, вы должны поддерживать постоянные контакты. Это необходимо, так как вы можете потерять их.
Для клиентов, которые совершают большие покупки, но не понятна причина, по которой это произошло, вы должны расширить ассортимент и понять потребность клиентов.
Как сделать ABC анализ
Чтобы провести ABC анализ необходимо выполнить ряд шагов. Он проводится в несколько этапов, которые необходимо выполнять друг за другом.
Перечислим этапы, которые вам необходимо сделать:
- у анализа должна быть цель, поставьте ее;
- выберите объекты исследования;
- выберите показатель, по которому будете делить выбранные объекты;
- дайте оценку всем объектам по выбранному показателю;
- рассортируйте объекты по убыванию значения показателя;
- выявите долю значения показателя каждого объекта;
- распределите объекты по долям значений показателя нарастающим итогом;
- разбейте все объекты на три группы: А, B, C. К группе А относятся те, у которых нарастающий итог от 0 до 80%, у кого 80-95% — это клиенты группы В, свыше 95% группа С;
- вычислите число клиентов, относящихся к каждой группе.
Пример ABC анализа в Excel
Приведем пример ABC анализа. Для его проведения используем офисную программу MS Excel.
Например, вам необходимо проанализировать ваших клиентов относительно прибыли которую они вам приносят.
- Создадим таблицу в Excel с двумя столбцами: наименование клиента и выручка в руб. Возьмем 15 клиентов.
- Отсортируем клиентов по убыванию выручки. Для этого необходимо выбрать вкладку Данные — Сортировка.
- Теперь нам необходимо вычислить долю от общей прибыли. Для этого пропишем формулу в ячейке С3 = B2/$B$17 и для ячеек этого столбца выберем процентный формат ячеек.
- Следующим шагом рассчитаем накопительную долю для каждого клиента. Для первого клиента накопительная доля будет равна доле, для последующих же она будет вычисляться как сумма его доли и предыдущего клиента. То есть для Клиента 8 в ячейке D3 будет прописана формула = С3+D2. Протянем эту формулу на оставшиеся ячейки. Таким образом у вас получится список клиентов выстроенных по возрастанию процентного соотношения.
- Теперь вы с легкостью можете распределить данный список на категории A, B и С.
- Далее для каждой группы клиентов определим долю от общей выручки и процент от общего числа клиентов.
Для того чтобы узнать долю выручки, необходимо сумму выручки по группе разделить на общую сумму выручки и выбрать процентный формат данных для ячейки.
Чтобы найти долю от общего числа клиентов, необходимо разделить количество человек, относящихся к группе на 15, так как мы для анализа взяли 15 клиентов. Выберем также процентный формат ячеек.
Таким образом у нас получилось, что 33% наших клиентов принося нам максимум прибыли более 80%, 27% клиентов приносят нам 15% выручки и 40% приносят нам 6% выручки.
Наши показатели немного отличаются от теории Парето, но не стоит считать наши вычисления ошибкой. Практика показывает, чем больше аудитории мы берем для анализа тем ближе наши показатели к показателям прописанным Парето.
В данном случае мы привели пример АВС анализа, который касается анализа прибыли компании. Вы же можете его проводить и для других областей, например, для анализа ассортимента товара.
Как сделать XYZ анализ
Проведение данного вида анализа, как и предыдущего, можно разделить на этапы:
- необходимо выбрать элементы и показатели для анализа,
- выбрать временной промежуток, которое вы хотите проанализировать,
- вычислить коэффициент вариации каждого анализируемого элемента,
- распределить элементы в зависимости от показателя вариации,
- разделить элементы на 3 группы: X, Y, Z.
К категории X относятся элементы с показателями вариации 0-10%. Таких клиентов можно охарактеризовать как устойчивых.
Категория Y — показатели вариации 10-25%. Эти клиенты изменчивы, но их поведение можно спрогнозировать.
Категория Z — вариация от 25% и выше. Сюда относятся случайные клиенты, которые приобрели товар один раз по потребности.
Данные для XYZ анализа должны быть статистического характера. Приведем пример XYZ анализа. Для этого воспользуемся офисной программой MS Excel.
XYZ анализ в Excel
На первом этапе XYZ анализа в Excel необходимо выбрать данные для анализа и создать таблицу. Например, проанализируем тех же клиентов, что и при ABC анализе, за полгода сотрудничества. Для этого возьмем данных выручки разбитые по месяцам.
Далее рассчитаем по каждому клиенту средние продажи за месяц и стандартное отклонение. Для вычисления среднего значения применим формулу в ячейке I3 =СРЗНАЧ(B3:G3).
Для того, чтобы найти стандартное отклонение воспользуемся формулой J3 = СТАНДОТКЛОН(B3:G3). Затем протяните формулы на оставшиеся ячейки.
Теперь можно рассчитать вариацию каждого клиента. Для расчета вариации применяется сложная формула. Но с помощью встроенных формул Excel рассчитать данный показатель можно быстро.
Введем в ячейку K3 формулу = СТАНДОТКЛОНП(B3:G3)/СРЗНАЧ(B3:G3). Протянем формулу и выберем процентный формат ячеек.
Теперь вам становится видна вариация по каждому клиенту. Разделите их на группы X, Y, Z по ранее указанным критериям.
К группе X относятся клиенты с вариативностью от 0 до 10%, к категории Y от 10 до 25%, к категории Z от 25% и выше. Если большой список клиентов для анализа, то можно изначально отсортировать таблицу по возрастанию и указать категории.
Так как мы проводим анализ на примере 15 клиентов, то пропишем значения категорий вручную. У вас получится примерно так.
Из анализа видно, что у компании из рассмотренных 15 клиентов 8 характеризуются постоянным спросом и покупками товаров, их поведение можно легко спрогнозировать. К категории Y с колеблющимся спросом относятся 2 клиента. И к категории Z относится 5 клиентов. Это те люди, поведение которых сложно спрогнозировать. Их покупки скорее всего были вызваны срочными потребностями и носили единоразовый характер.
Проанализировав эти показатели, можно сказать, что в целом поведение клиентов стабильно и прогнозируемо.
Мы проанализировали клиентов по прибыльности, которую они приносят компании. Точно также вы можете произвести XYZ анализ ассортимента.
Использование ABC XYZ анализа
Зачастую маркетологи и логисты используют два вида анализа совмещенно. Это наилучший инструмент для анализа бизнес-процессов компании.
В таком случае создается единая таблица, содержащая данные по ABC анализу продаж и XYZ анализу, и каждому объекту исследования присваивается одна из 9 категорий:
- AX — означает наивысшую потребительскую стоимость, стабильное приобретение товаров и хорошую прогнозируемость,
- AY — высокая потребительская стоимость, но нестабильное приобретение и средняя степень прогнозирования,
- AZ — высокая потребительская стоимость, спонтанные покупки и невозможно спрогнозировать,
- BX — средняя потребительская стоимость, стабильное приобретение товаров и хорошая прогнозируемость,
- BY — средняя потребительская стоимость, но нестабильное приобретение и средняя степень прогнозирования,
- BZ — средняя потребительская стоимость, спонтанные покупки и невозможно спрогнозировать,
- CX — низкая потребительская стоимость, стабильное приобретение товаров и хорошая прогнозируемость,
- CY — низкая потребительская стоимость, но нестабильное приобретение и средняя степень прогнозирования,
- CZ — низкая потребительская стоимость, спонтанные покупки и невозможно спрогнозировать.
ABC XYZ анализ Excel
Приведем пример ABC XYZ анализа. Возьмем ранее рассмотренный пример и создадим в табличном процессоре новую таблицу с указанием категорий с ABC анализа и XYZ анализа. В отдельный столбец поместим категории совмещенные.
Рядом с совмещенной таблице создадим новую и распределим клиентов по необходимым категориям.
Сделаем вывод по последней таблице. Самыми прибыльными для компании являются клиенты, которые стоят на позициях AX. Кроме прибыльности, они производят покупки стабильно, что позволяет сделать прогноз. К такой категории относятся клиенты с номерами 8, 12, 10, 11.
Стоит обратить свое внимание на клиентов под номера 15 и 13. Поработать также можно с клиентами 1, 6, 5, 7. Остальные клиенты не принесут вам много прибыли. Вы можете даже не затрачивать на них время.
Приведем таблицу, в которой наглядно видно, к какому типу относится тот или иной клиент и стоит ли затрачивать на него свое время.
| X | Y | Z | |
| A | Большой стабильный доход | Большой предсказуемый доход | Большой нерегулярный доход |
| B | Средний стабильныйдоход | Средний предзнакуемый доход | Средний нерегулярный доход |
| C | Маленький стабильный доход | Маленький предсказуемый доход | Маленький нерегулярный доход |
Проведение совмещенного анализа позволяет понять в каком направлении стоит двигаться как в отношениях с клиентами, так и с анализируемыми ресурсами. С помощью него вы сможете откорректировать ассортимент предлагаемых товаров и услуг, выпуская то, что пользуется спросом и принесет вам больше прибыли.
Плюсом применения совмещенного анализа является то, что он может дать характеристику многих бизнес-процессам компании, касающихся как продажи товаров и услуг, так и управлением персонала.
Рассказать друзьям:
#статьи
-
0
Рассказали главное, что нужно знать об этой методике маркетологу и предпринимателю. И показали, как провести ABC/XYZ-анализ в Excel.
Иллюстрация: Merry Mary для Skillbox Media
Шеф-редактор направлений «Маркетинг», «Управление», «Бизнес», «Деньги» Skillbox Media. В прошлом отраслевой журналист и владелец небольшого нишевого ютуб-канала.
ABC/XYZ-анализ — один из самых распространённых способов исследовать и сравнивать объекты в компании. Чаще всего с его помощью анализируют товары, чтобы понять, какие из них приносят большую прибыль. Поэтому ABC/XYZ — базовый инструмент, о котором нужно знать маркетологам и предпринимателям.
В этом материале мы ответим на главные вопросы об ABC/XYZ-анализе.
- Что такое ABC/XYZ-анализ и что в него входит
- Что и как исследуют с помощью этого анализа
- Что делают с результатами ABC/XYZ-анализа
- Как провести ABC/XYZ-анализ: пример в Excel
ABC/XYZ-анализ — кросс-метод, объединяющий два вида анализа: ABC и XYZ.
ABC-анализ — классификация объектов по влиянию на прибыль компании. Например, разделение товаров на группы по объёму выручки, которую они приносят. В основе метода лежит принцип Парето: 20% усилий приносят 80% результата.
В результате ABC-анализа объекты делят на три группы:
- Группа A. Это самые ценные позиции: 20% товаров, приносящих 80% прибыли.
- Группа B. Промежуточные позиции — 30% товаров, приносящие 15% прибыли.
- Группа C. Наименее ценные позиции. Это 80% товаров, которые приносят 5% прибыли.
XYZ-анализ — классификация объектов по частоте использования. Например, разделение товаров на группы по колебанию спроса на них.
В результате XYZ-анализа объекты делят тоже на три группы:
- Группа X. В неё входят объекты с коэффициентом вариативности 0–10%. Это значит, что спрос не меняется более, чем на 10% в месяц.
- Группа Y. В неё входят объекты с коэффициентом вариативности 10–25%.
- Группа Z. В неё входят товары с самым непредсказуемым спросом — объекты с коэффициентом вариативности больше 25%.
ABC/XYZ‑анализ комбинирует эти два анализа. Объекты распределяют не по трём группам, а по девяти:
- AX — большая доля прибыли, стабильный спрос.
- AY — большая доля прибыли, колеблющийся спрос.
- AZ — большая доля прибыли, непредсказуемый спрос.
- BX — средние объёмы прибыли, стабильный спрос.
- BY — средние объёмы прибыли, колеблющийся спрос.
- BZ — средние объёмы прибыли, непредсказуемый спрос.
- CX — малозначительная прибыль, стабильный спрос.
- CY — малозначительная прибыль, колеблющийся спрос.
- CZ — малозначительная прибыль, непредсказуемый спрос.
Эта классификация позволяет понять, какие товары самые ценные для компании, а какие показывают худшие результаты.
ABC- и XYZ-анализ используют вместе, потому что поодиночке они малоэффективны. Маркетолог и основатель маркетингового агентства Cleverra Анастасия Комарова объясняет: «Есть товары с сезонным спросом. Если провести ABC-анализ на пике спроса, можно отказаться от остальных товаров. Но когда сезон закончится, окажется, что у компании нет прибыли — потому что убрали продукты со стабильным спросом».
Мы уже говорили, что чаще всего ABC/XYZ-анализ используют для исследования ассортимента. Руководитель отдела аналитики в Adventum Вадим Шестаков уточняет: ассортимент анализируют с точки зрения прибыли и спроса. По результатам анализа можно понять, каких товаров нужно держать на складе больше, а каких — меньше.
Также ABC/XYZ-анализ можно использовать для классификации любых других объектов в компании: магазинов, бизнес-проектов, сотрудников. СЕО операционного партнёра маркетплейсов XWAY Антон Ларин приводит пример: с помощью ABC/XYZ-метода можно анализировать базы клиентов или поставщиков. Партнёров классифицируют, основываясь на объёме закупок, чтобы выделить среди них стратегически важных для компании.
ABC/XYZ-анализ можно адаптировать для исследования любых объектов.
Обычно при ABC/XYZ-анализе сравнивают однородные объекты по сопоставимым показателям. Можно использовать любые показатели и исследовать сколько угодно объектов. Разберёмся почему.
Однородные объекты нужно сравнивать, чтобы результат анализа был корректным. «Если вы продаёте и одежду, и электронику — оценивайте эффективность продаж товаров этих категорий по отдельности», — говорит Антон Ларин.
Сопоставимые показатели нужны по той же причине — чтобы результаты анализа не были искажены. Об этом говорит маркетолог и основатель маркетингового агентства Cleverra Анастасия Комарова. По её мнению, недопустимо сравнивать товары, если один только запущен в продажу, а другой продаётся уже год.
Показатели, по которым классифицируют объекты, зависят от целей анализа. Для ABC-анализа используют финансовые показатели: оборот, прибыль, средний чек, выручка, маржинальность и другие метрики. При XYZ-анализе, помимо финансовых показателей, могут использовать данные о числе заказов, количестве поставок, числе контактов и так далее.
Обычно метод применяют в компаниях с большим числом объектов: товаров, клиентов, подразделений, точек продаж. Например, ABC/XYZ-анализ может проводить интернет-магазин с ассортиментом в 2000 позиций и производитель, выпускающий 50 видов металлопроката. Чем больше число объектов, тем ближе результат анализа к принципу Парето.
Есть другие мнения. Маркетолог и основатель маркетингового агентства Cleverra Анастасия Комарова считает, что ABC/XYZ-анализ можно применять, если у компании немного продуктов. «Даже два объекта можно сравнить. В этом есть смысл: может выясниться, что один товар обеспечивает большой оборот, но всю прибыль компания получает от другого», — говорит она.
ABC/XYZ-анализ помогает понять, как работать с объектами, чтобы прибыль компании росла. Стратегию подбирают в зависимости от того, в какую группу попал товар. Мы спросили экспертов, как можно работать с результатами ABC/XYZ-анализа.
СЕО операционного партнёра маркетплейсов XWAY Антон Ларин рассказывает, как работают с товарами из разных групп.
- A дополнительно продвигают. Эти товары можно упомянуть в рассылке, в поп-ап-окнах на сайте и пуш-уведомлениях. Ещё можно наполнить ими витрину магазина на маркетплейсе и провести акции с ними.
- B запасают в таком количестве, чтобы покрыть спрос.
- C прекращают продавать. Другая стратегия — уменьшить их запасы.
- X всегда должны быть в наличии на складе. За ними приходит большинство покупателей.
- Y закупают в небольших количествах. Создавать запасы для них обычно бессмысленно.
- Z лучше поставлять по предзаказу — их покупают от случая к случаю. Отказываться от них не стоит — часто продукты группы Z знакомят потребителей с продукцией компании.
Маркетолог и основатель маркетингового агентства Cleverra Анастасия Комарова дополняет: «Для товаров Y и Z нужно чётко определить сезоны повышения спроса, а потом корректировать объёмы производства и продаж под каждый месяц».
Руководитель отдела аналитики Adventum Вадим Шестаков дал несколько рекомендаций по объединённым группам. Он считает, что:
- AX — самые ценные товары, поэтому они всегда должны быть в наличии.
- AY могут перейти в группу AX. Спрос на них можно стимулировать: например, расположив эти товары на первых страницах каталогов.
- CZ требуют дополнительного анализа. В эту группу попадают товары, которые уже перестали пользоваться спросом, — их можно поставлять под заказ.
Эксперты единодушны: принимать решение только на основании ABC/XYZ‑анализа нельзя. Нужно учитывать внешние факторы, которые влияют на показатели.
Не стоит отказываться от товаров группы Z. СЕО операционного партнёра маркетплейсов XWAY Антон Ларин объясняет: в неё могут попасть новые товары. Ещё на спрос влияют форс-мажорные обстоятельства.
Маркетолог и основатель маркетингового агентства Cleverra Анастасия Комарова считает, что нельзя резко масштабировать продажи товаров группы A. Предельная ёмкость рынка неизвестна, поэтому затраты на маркетинг могут не окупиться.
Товары группы C, по мнению Анастасии Комаровой, нужно дополнительно проанализировать. Отказаться от них стоит, только если нельзя их «реанимировать» и если они не влияют на продажи товаров из других групп. Она приводит два примера, почему это важно.
Первый пример — компания продаёт дешёвый товар. Раньше он был прибыльным. Потом расходы на производство увеличились, но компания не проиндексировала цены — и теперь товар убыточен. Можно провести анализ цен конкурентов и опросить клиентов, будут ли они покупать продукт после индексации цен. Если да — оставляем товар и повышаем его стоимость.
Второй пример — услугу постпродажного сервиса оказывают в минус. Но именно из-за неё покупают сложный технический товар, который приносит прибыль, — он находится в группе A. Отказываться от услуги нельзя: это решение убьёт весь бизнес.
ABC/XYZ-анализ можно провести в Excel или «Google Документах». Мы покажем процесс на примере магазина садовых принадлежностей. Проводить анализ будем в три этапа: ABC-анализ, XYZ-анализ и построение матрицы ABC/XYZ.
Заведите таблицу с пятью столбцами. В первом столбце укажите все объекты, которые собираетесь сравнивать.
Во втором столбце будет показатель, по которому вы сравниваете товары. В нашем случае — выручка от продаж каждого товара за месяц.
Скриншот: «Google Таблицы» / Skillbox Media
В третьем столбце считаем долю каждого товара в общем объёме выручки. Для этого есть формула:
Чтобы не считать доли вручную, можно использовать формулу: =номер ячейки с выручкой товара/общая выручка*100.
Например, для садовых тачек формула будет выглядеть так: =B2/500000*100.
Поместим её в столбец с долями и растянем до конца таблицы для автоматического расчёта.
Скриншот: «Google Таблицы» / Skillbox Media
Дальше нужно расположить товары в порядке убывания в доле выручки. Выделяем три столбца с данными, переходим во вкладку «Данные» → «Сортировать диапазон» → «Сортировать диапазон по столбцу C (Я > А)».
Скриншот: «Google Таблицы» / Skillbox Media
Скриншот: «Google Таблицы» / Skillbox Media
В четвёртом столбце считаем совокупный процент нарастающим итогом. Это значит, что к каждой доле мы прибавляем все предыдущие. В нашем примере на первом месте мотопомпы с долей 20% — это и будет совокупный процент. На втором месте насосы с долей 16%: прибавляем к их доле долю мотопомп и получаем 36%. На третьем месте шланги для полива с долей 15%: совокупный процент для них — 51%.
Чтобы не считать процент вручную, выделяйте ячейки, долю в которых нужно суммировать. Данные о сумме чисел в них появятся внизу листа.
Скриншот: «Google Таблицы» / Skillbox Media
Пятый столбец — определение группы товара в зависимости от выручки. Её определяют по совокупному проценту. Товары с показателем меньше 80% относятся к группе A, 80–95% — к группе B, 95–100% — к группе C.
Проставить значения можно по формуле =ЕСЛИ (D2<=80;»A»; ЕСЛИ (D2<=95;»B»; ЕСЛИ (D2>=95;»С»))) — внесите её в первую ячейку, где должна быть группа, и растяните на всю таблицу. D2 в формуле — номер ячейки с совокупным процентом: у вас эта ячейка может быть другой.
Скриншот: «Google Таблицы» / Skillbox Media
В таблице для XYZ-анализа больше столбцов. Создать её можно на втором листе.
Первый столбец — товары: их можно скопировать из таблицы для ABC‑анализа.
Дальше идут данные о выручке за период оценки. Период разбивают на равные промежутки и вносят в ячейки показатели каждого из них. Мы возьмём год и будем указывать выручку для каждого месяца.
Скриншот: «Google Таблицы» / Skillbox Media
Период оценки и данные могут быть другими. Например, можно взять летние месяцы и разбить их на недели. Или сравнивать объекты не по выручке, а по количеству заказов или чистой прибыли.
Следующий столбец, в нашем случае четырнадцатый (N), содержит коэффициент вариации. Эта метрика показывает, насколько показатели отклоняются от среднего значения. Посчитать её разом для всей таблицы можно по формуле =СТАНДОТКЛОН(B2:M2)/СРЗНАЧ (B2:M2)*100 — тогда данные будут выражены в процентах. B2:M2 в этой формуле — диапазон, в котором указаны данные: у вас он может быть другим.
Скриншот: «Google Таблицы» / Skillbox Media
В следующем столбце определяем, к какой группе относится каждый товар. Товары с коэффициентом 0–10% попадают в группу X, 10–25% — в группу Y, более 25% — в группу Z.
Проставить значения разом можно по формуле =ЕСЛИ (N2<=10;»X»; ЕСЛИ (N2<=25;»Y»; ЕСЛИ (N2>=25;»Z»))) — указываем в ней номер ячейки, в которой прописан коэффициент вариации. Потом протягиваем до конца таблицы — и получаем распределение по группам.
Скриншот: «Google Таблицы» / Skillbox Media
Каждому товару присваивают индекс из двух букв: первую дают по результату ABC-анализа, вторую — по результату XYZ-анализа. В нашем примере индекс мотопомп — AY, а индекс канистр — BX.
Для наглядности можно построить матрицу ABC/XYZ-анализа. Это таблица, в которой в строках пишут названия групп по ABC-анализу, в столбцах — названия групп по XYZ-анализу. На пересечениях помещают соответствующие товары.
Скриншот: «Google Таблицы» / Skillbox Media
ABC/XYZ-анализ объединяет два метода: ABC и XYZ. ABC делит объекты на три группы по влиянию на прибыль, XYZ — по частоте использования. Вместе они позволяют распределить объекты по девяти группам и понять, какие из них самые ценные для компании.
Чаще всего ABC/XYZ-анализ используют для исследования ассортимента. Но его можно адаптировать для анализа любых других объектов: клиентов, проектов, точек продаж. Главное — сравнивать однородные объекты по сопоставимым показателям. Показатели могут быть любыми: прибыль, выручка, маржинальность и так далее.
В зависимости от того, к какой группе относится объект, выбирают стратегию работы с ним. Например, для товаров AX создают запас на складах. У объектов в группе CZ меньше всего ценности — их перестают продавать или делают что-то, чтобы повысить спрос. При выборе стратегии ориентируются не только на ABC/XYZ-анализ, но и на внешние факторы: это помогает не ошибиться.
Другие материалы Skillbox Media о маркетинге
- Пошаговая инструкция и советы, как установить и настроить «Яндекс.Метрику»
- Как продавать на Ozon: про модели работы, управление ценами, продвижение товаров
- Подборка вопросов про «Директ» — сколько денег нужно на старте и стоит ли включать автоматическое управление
- Руководство по хештегам во «ВКонтакте»: работают ли они и как правильно их ставить
- Обзор профессии менеджера маркетплейсов: чем он занимается и зачем компании такой специалист
Профессия Интернет-маркетолог
Освойте востребованную профессию с нуля и добавьте более 60 проектов в портфолио. Учитесь онлайн, в удобном вам темпе.
Узнать больше →
Курс: «Профессия Интернет-маркетолог»
Узнать больше