ABC-анализ и XYZ-анализ в Excel часто используются вместе, так как используют одни и те же исходные данные. Рассмотрим практический пример совместного использования двух видов анализа.
Торговая компания внедряет систему цен для покупателей, предусматривающую следующие категории цен (по увеличению скидки): базовую, оптовую, крупнооптовую, специальную.
Все покупатели должны быть ранжированы по двум показателям: объёму продаж и стабильности продаж.
По объёму продаж вводятся следующие категории:
— категория А — лучшие 20% покупателей по объёму;
— категория В — последующие 50%,
— категория С — оставшиеся 30%.
По стабильности продаж:
— категория X — лучшие по стабильности (коэффициент вариации от 0% до 10%)
— категория Y — средние по стабильности (от 10% до 25%)
— категория Z — худшие по стабильности (более 25%).
Обе категории будут использованы для определения ценовой категории следующим образом:
— Специальную цену могут получить только покупатели категории AX.
— Крупнооптовую цену могут получить покупатели категорий АY и BX.
— Оптовую цену могут получить покупатели категорий AZ и BY.
— Все остальные покупатели получают базовую категорию цен.
В примере будет рассмотрено определение категорий по объёму и стабильности продаж для каждого покупателя и определение общей ценовой категории. За ранжирование покупателей по объёму отвечает ABC-анализ, за ранжирование по стабильности — XYZ-анализ, в конце примера результаты обоих методов объединяются.
Скачайте и откройте файл с практическим примером. ABC-XYZ analiz Excel prostoi primer
На листе Категории слева расположены справочные таблицы для категорий. Справа – вспомогательные таблицы, задающие границы диапазонов категорий (будут в дальнейшем использованы для расчёта).
На листе Данные и расчёты расположены исходные данные по продажам за период и колонки расчётов.
ABC-анализ в Excel
- В ячейках столбца «Ранг в продажах» вычисляется процентный ранг покупателя, для этого используется функция ПРОЦЕНТРАНГ (Подробнее о функции ПРОЦЕНТРАНГ читайте в соответствующей статье учебника Excel). Данная функция вычисляет процентный ранг и вычитает его из единицы, соответственно формула в ячейках имеет вид «=1-ПРОЦЕНТРАНГ($N$2:$N$37;N2)» (обратите внимание на абсолютную ссылку в формуле).
- В ячейках столбца Код АВС происходит присваивание поставщику категории по объёму продаж. Формула имеет вид «=ВПР(O2;диапазоны_авс;2)». Функция ВПР берёт численные значения процентного ранга из столбца «Ранг в продажах» и присваивает соответствующую категорию А, В или С, сравнивая ранг с пороговыми значениями в таблице «Диапазоны весов в продажах» листа «Категории».
XYZ-анализ в Excel
- Вычисляется коэффициент вариации с помощью функций СТАНДОТКЛОНП и СРЗНАЧ. =СТАНДОТКЛОНП(B2:M2)/СРЗНАЧ(B2:M2).
- В ячейках столбца Код вариации происходит присваивание поставщику категории по стабильности продаж. Функция ВПР сравнивает код вариации и с пороговыми значениями в таблице «Диапазоны коэффициента вариации» листа «Категории».
Объединение результатов АВС-анализа и XYZ-анализа
В столбце «Общий код» с помощью текстовой функции СЦЕП?ТЬ объединяются соответствующие коды.
Ссылки на учебник Excel:
Функции поиска: ВПР, ГПР, СМЕЩ
Текстовые функции: СЦЕП?ТЬ
?нтерфейс: ячейки, столбцы, строки, диапазоны, строка формул
Статистические функции: СРЗНАЧ, СТАНДОТКЛОНП
Смотрите также: