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

Исходные условия
Дифференциация ассортимента осуществляется на основе среднего запаса по позиции:
Норма запаса до оптимизации – 30 дн.
Затраты на хранение:
М – стоимость хранения единицы запаса – 150 руб/ед.

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

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

Сортировка
Выделяем всю таблицу с данным начиная со строки «всего» и с помощью настраиваемой сортировки упорядочиваем ассортимент по убыванию доли позиции в запасе

Рассчитываем кумулятивное накопление доли позиции в совокупном запасе
Ячейку первой позиции упорядоченного списка приравниваем значению ячейки слева («равно соседу слева»)
Со второй строки ячейка равна сумме ячейка слева + ячейка слева (= «сосед сверху» + «сосед слева») и эту формулу тиражируем до конца таблицы

Далее осуществляем дифференциацию ассортимента с использованием эмпирического метода

Автоматическое распределение по группам
Если значение ячейки слева (кумулятивное накопление меньше 50% то присваивается группа А, если меньше 80% то В, остальным – С)

Оценка результативности оптимизации
После оптимизации по АВС установлены нормы по группам: А – 10 дн., В – 25 дн. С – 45 дн.
Автоматическая расстановка норм запасов в соответствии с группой:

Оценка результативности оптимизации
Далее считаем средний запас и затраты на хранение с учетом новых норм

Метод суммы
Деление на группы осуществляется по критерию суммы
ДО (доля объектов) + ВР (вклад в результат)
Доля объектов рассчитывается по формуле

Автоматизация расчета ДО
Значок «$» перед столбцом/строкой фиксирует ее и при тиражировании EXEL не осуществляет автоматическое его изменение. В данном случае оператор «СЧЕТ» в формуле автоматически посчитывает количество объектов в заданном интервале
Кумулятивное накопление ДО рассчитывается стандартным способом

Критерии деления на группы при использовании
метода суммы

Автоматическое разбиение на группы

Оценка результативности оптимизации
Далее автоматически расставляем новые нормы запасов и рассчитываем новые средние запасы и затраты на хранение (формулы можно копировать из эмпирического метода)

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

Разбиение на группы

Определяем количество периодов (в формуле соответствует значению n)

Рассчитываем среднеквартальный объем продаж

Рассчитываем коэффициент вариации

Распределяем по группам

Совместный анализ АВС&XYZ