Как провести АВС-анализ ассортимента продукции

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

МЕТОДИКА АВС-АНАЛИЗА АССОРТИМЕНТА ПРОДУКЦИИ

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

Сам метод АВС-анализа базируется на правиле Парето, согласно которому 20 % реализуемого ассортимента продукции дают 80 % дохода от реализации, а оставшиеся 80 % реализуемого ассортимента дают компании только 20 % дохода от реализации.

Поскольку любое коммерческое предприятие ведет свою деятельность на рынке сбыта с учетом факторов конкуренции, то при проведении АВС-анализа принято выделять три группы ассортимента:

• группа А — включает в себя 20 % ассортимента продукции, который дает предприятию 50 % дохода от реализации;

• группа В — содержит 30 % ассортимента продукции, приносящих предприятию 25 % дохода от реализации;

• группа С — в нее входит 50 % оставшегося ассортимента продукции, который дает предприятию 25 % дохода от реализации.

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

При ранжировании предметов анализа следует учитывать главную цель проведения АВС-анализа:

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

• если анализ нужен для принятия решения об увеличении величины прибыли от продаж, то целью анализа выбирают торговую наценку или доходы от реализации продукции;

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

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

В случае с одной категорией мы имеем только 3 варианта ранжирования. При использовании второй категории получаем уже девять аналитических групп ранжирования (АА, АВ, АС, ВА, ВВ, ВС, СА, СВ, СС). Добавив третью категорию, создаем 27 групп ранжирования (рис. 1).

Для качественного АВС-анализа в принципе вполне достаточно ограничиваться двумя-тремя категориями показателей.

Сейчас большинство программного обеспечения класса CRM-систем имеет встроенный инструмент для проведения АВС-анализа. Да и во многих учетных программах имеется блок «СРМ и маркетинг», где также есть отчеты по АВС-анализу продаж.

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

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

Шаг 1. Формируем отчет о реализации продукции в Excel

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

Шаг 2. Рассчитываем удельный вес ассортиментной единицы продукции по каждому критерию анализа

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

Значение показателя ассортиментной единицы / Общая сумма показателей ассортиментных единиц × 100 %.

В результате получаем по каждому добавленному столбцу значение удельного веса (в процентах) каждой ассортиментной единицы анализируемой продукции.

Шаг 3. Определяем группу значимости по каждой ассортиментной единице продукции

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

Далее в каждую аналитическую таблицу добавляем еще одну колонку, где в ячейке по строке первой ассортиментной единицы пропишем расчетную формулу с использованием функции ЕСЛИ. Поскольку мы хотим получить данные по трем групповым значениям (А, В и С), формула будет составная:

=ЕСЛИ(Значение ячейки с удельным весом ассортиментной единицы>Нижняя граница группы А в%;"A";ЕСЛИ(Значение ячейки с удельным весом ассортиментной единицы >Нижняя граница группы В в%;"B";"C")).

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

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

• Столбец — номер или буквенное обозначение, в котором расположены обозначения групп значимости, присвоенные ассортиментным единицам в первой части этого шага;
• Сортировка — значения;
• Порядок — от А до Я (так как в выбранном столбце у нас указаны А, В, С).

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

Шаг 4. Формируем сводную таблицу по всей номенклатуре продукции и выбранным критериям АВС-анализа

Создаем на отдельном листе Excel таблицу, в которую скопируем перечень ассортиментных единиц продукции из выгруженного ранее отчета. Добавим новые три столбца для каждого критерия анализа и с помощью формулы ВПР заполним ячейки показателями групп значимости по выбранным критериям. Формула ВПР:

=ВПР(Диапазон перечня ассортиментных единиц из первого столбца таблицы ;Наименование листа с которого берутся данные и диапазон просматриваемых редактором данных;Номер столбца в листе откуда берутся данные;ЛОЖЬ).

Формула нужна, чтобы редактор Excel выбрал обозначения групп в соответствии с наименованием ассортиментной единицы продукции и автоматически подставил их во все столбцы новой таблицы. В формуле присутствует значение ЛОЖЬ, так как нам необходимо строгое соответствие по наименованию ассортиментной единице продукции.

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

Шаг 5. Сортируем данные и формируем структурированную сводную таблицу АВС-анализа ассортимента продукции

Чтобы произвести комплексное ранжирование по трем выбранным критериям, используем функционал Excel по сортировке данных (Данные/Сортировка) и в появившемся диалоговом окне укажем расширенные условия сортировки:

• Сортировать — указываем сначала столбец с наиболее важным для компании критерием (например, натуральный объем реализации продукции), затем столбец с критерием средней важности (допустим, это выручка от продаж в рублях) и столбец с наименее важным критерием (доход от реализации продукции);
• Сортировка — для каждого столбца выбираем «значение»;
• Порядок — для каждого столбца выбираем «От А до Я».

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