ABC-анализ ассортимента продукции предприятия с помощью Excel

С помощью каких инструментов Excel можно провести АВС-анализ ассортимента продукции?

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

В какой последовательности нужно проводить АВС-анализ ассортимента продукции с помощью Excel?

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

Рассмотрим порядок АВС-анализа на примере. Все расчетные таблицы вы можете скачать на «Сервисе форм» и, подставив свои данные, использовать в работе.

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

На практике пропорции не будут точно соответствовать значениям 20/80. Кроме того, чтобы удержать долю рынка, недостаточно уделять внимание только 20 % ассортимента продукции.

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

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

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

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

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

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

Табличный редактор Excel содержит необходимые для АВС-анализа формулы работы с исходными данными ЕСЛИ, ВПР/ГПР и функционал сортировки табличных данных.

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

• какой ассортимент продукции в компании приоритетен (группа А) и требует повышенного внимания;

• какая часть ассортимента продукции находится в средней группе значимости (группа В);

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

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

Рассмотрим эти шаги подробнее.

Шаг 1. Выгружаем данные об ассортименте продукции в файл Excel.

На этом этапе:

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

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

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

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

Шаг 3. Сортируем ассортимент продукции по группам.

Третий этап АВС-анализа состоит из двух частей:

1. Сначала выводим группу значимости для каждой ассортиментной единицы продукции.

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

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

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

Например, я для рассматриваемого примера АВС-анализа взял такие параметры:

• ассортиментные единицы продукции, удельный вес которых выше 5 %, относятся к группе А;

• ассортиментные единицы продукции, которые находятся в диапазоне выше 2,5 % и до 5 % относятся к группе В;

• соответственно, в группе С окажутся те ассортиментные единицы продукции, удельный вес которых не больше 2,5 %.

2. Ранжируем ассортиментные единицы продукции по указанным группам.

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

• Столбец → Сортировать по (номер или буквенное обозначение столбца, в котором расположены обозначения групп значимости, которые мы присвоили ассортиментным единицам в первой части этого этапа);

• Сортировка — Значения ячеек;

• Порядок — От А до Я (потому что в выбранном столбце у нас указаны А, В, С).

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