letter-b-between-others-1306674-639x427

ABC-анализ и XYZ-анализ

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:

Функции поиска: ВПР, ГПР, СМЕЩ

Текстовые функции: СЦЕП?ТЬ

?нтерфейс: ячейки, столбцы, строки, диапазоны, строка формул

Статистические функции: СРЗНАЧ, СТАНДОТКЛОНП

Смотрите также:

ABC-анализ

XYZ-анализ

Leave a Reply

Ваш e-mail не будет опубликован.