ABC и XYZ анализ в Excel

Автор: | 06.05.2022

ABC и XYZ анализ в Excel: таблицы, примеры расчетов, формулы

4 вопроса до начала ABC-анализа

  1. Задача. Зачем вы проводите исследования? Увеличить выручку компании, исключить возможность упущенной выгоды и т.д.
  2. Результат. Как применить полученные значения? Оптимизируем складские запасы, пересматриваем условия договоров и т.д.
  3. Источники данных. Как вы будете собирать необработанные данные: объект и параметр анализа? Объектом анализа является перечень товаров, параметром — выручка в количественном и денежном выражении.
  4. Матрица. Какое процентное распределение ABC XYZ должно быть включено в расчет? Классический вариант, основанный на принципе Парето: 80% доходов приносят 20% ключевых клиентов. Чтобы назначить распределение по группам, необходимо знать специфику компании, жизненные циклы и сезонность. Ошибки в матрице могут привести к убыточной C, которая группирует важных покупателей с нечастыми покупками.

ABC-анализ в Excel: пошаговая инструкция, рабочие образцы с формулами

ABC-анализ ассортимента будет проводиться на примере компании, занимающейся продажей запчастей для сельхозтехники.

Количество товаров превышает 5000 наименований. Объединяем их в группы по типам номенклатуры.

Загружаем данные за 2020 год из учетной системы:

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

ABC-анализ в Excel: примерABC-анализ в Excel: пример

Сортировка выручки по убыванию

Выделите диапазон ячеек: вся таблица с заголовками без строки «Итого».

В раскрывающемся меню выберите:

Данные — Сортировать — Сортировать по:

  • столбец «Выручка»
  • сортировка «Ценности»
  • в порядке убывания

Щелкните «ОК».

Система строит таблицу в порядке убывания доходов в столбце D.

ABC-анализ в Excel: сортировка чеков по убываниюABC-анализ в Excel: сортировка чеков по убыванию ABC-анализ в Excel: сортировка по убываниюABC-анализ в Excel: сортировка по убыванию

Доля каждой строки в общем параметре

Определите долю каждой статьи в выручке:

  • добавьте столбец «Поделиться» (E). Формат ячейки — процентный;
  • в строке 2 для продукта 6 мы вставляем формулу: выручка от продукта 6 / общая выручка;
  • мы удлиняем формулу для всех продуктов.

Добавьте столбец F и рассчитайте квоту как совокупную сумму: сложите текущее значение со всеми предыдущими.

ABC-анализ в Excel: рассчитайте долю каждого продуктаABC Analysis в Excel — формулы для расчета доли дохода от каждого продукта

Символ & предупреждает Excel, что формулу нельзя переместить:

  • & перед буквой — столбцами;
  • & перед цифрой — построчно.

ABC-анализ в ExcelABC-анализ в Excel: доля каждого продукта в выручке

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

Определяем группу

Создайте столбец группы. Каждому продукту мы присваиваем значения A, B, C в соответствии с долей выручки.

Руководство утвердило матрицу:

Группа Диапазон
А до 70%
Б 70–90%
С 90-100%

В ячейке G2 запишите формулу = ЕСЛИ (F2 = 90%; «C»; «B»)). Расширение формулы для всех продуктов.

В примере для наглядности проценты указаны цифрами. 

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

ABC-анализ в Excel: распределение групп, формулыABC-анализ в Excel: распределение групп, формулы ABC-анализ в Excel: распределение по группам - результат обработкиABC-анализ в Excel: распределение по группам — результат обработки

В столбце G каждой группе товаров присвоен код A, B, C.

В группу А входят товары, приносящие основную прибыль.

Группа Б — продукция компании, на которую существует неравномерный спрос.

Группа C: продукты, приносящие только 10% дохода.

XYZ-анализ в Excel: оценка динамики продаж

Поиск XYZ позволит вам увидеть изменения спроса на продукцию компании.

Выгружаем данные из учётной системы

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

XYZ-анализ в Excel: продажи по кварталамXYZ-анализ в Excel: продажи по кварталам

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

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

Создайте столбец «Средние продажи». В строке 3 введите формулу = СРЕДНЕЕ (B3: E3) и скопируйте ее для всех позиций товаров.

XYZ-анализ в Excel: формула для расчета средних продажXYZ-анализ в Excel: формула для расчета средних продаж

Создайте столбец стандартного отклонения. Стандартное отклонение / средние продажи.

В строке 3 введите формулу = СТАНДОТКЛОН (B3: E3) и скопируйте ее для всех заголовков.

XYZ-анализ в Excel: формула для расчета стандартного отклоненияXYZ-анализ в Excel: формула для расчета стандартного отклонения

Создайте столбец вариации,%. Вводим формулу:

Стандартное отклонение столбца / Средние продажи столбца

  • XYZ-анализ в Excel: формула для расчета коэффициента вариацииXYZ-анализ в Excel: формула для расчета коэффициента вариации
  • XYZ-анализ в Excel: рассчитанный коэффициент вариацииXYZ-анализ в Excel: рассчитанный коэффициент вариации

XYZ-анализ: таблицы Excel. Пример

Присваиваем значения XZY и соединяем с ABC

Руководство утвердило матрицу анализа XYZ:

Группа Диапазон
X — постоянный спрос до 15%
Y — волатильный спрос, сезонность от 15% до 50%
Z — случайный вопрос более 50%

Классифицируем полученные результаты с помощью функции «ЕСЛИ» программы Excel».

В ячейке J3 введите формулу: = ЕСЛИ (I3 = 50%; «Z»; «Y»)). Копируем формулу для всех товарных условий.

  • XYZ-анализ в Excel: группы товаров методом XYZ - формулаXYZ-анализ в Excel: группы товаров методом XYZ — формула
  • XYZ-анализ в Excel: группы товаров по методу XYZ - результатXYZ-анализ в Excel: группы товаров по методу XYZ — результат

XYZ-анализ в Excel: группы товаров по методу XYZ

Создайте столбец группы, используя метод ABC. Извлеките код группы из таблицы синтаксического анализа ABC, используя формулу: = ВПР (A3; ABC! $ A $ 1: $ G $ 12; 7; 0)

Как настроить формулу ВПР:

Задача функции: из кода продукта в исходной таблице найти значение A, B или C и передать его отчетную таблицу XYZ.

A3 — это параметр, по которому мы ищем значение, например «Продукт 6».

ABC! $ A $ 1: $ G $ 12 — ссылка на диапазон исходной таблицы. В нем, строго в первом столбце, должен быть параметр, с которым ищем значение «Товар 6».

7 — порядковый номер столбца, в котором находятся исходные значения (коды A, B, C)

0 — значение ЛОЖЬ. Для Ecxel это признак того, что желаемый результат должен удовлетворять всем 3 предыдущим условиям.

  • Анализ ABC и XYZ: электронные таблицы Excel
  • Анализ ABC и XYZ: электронные таблицы Excel
  • Анализ ABC и XYZ: электронные таблицы Excel

Анализ ABC и XYZ: электронные таблицы Excel

Для каждого продукта мы получаем двойное кодирование анализа ABC и XYZ.

Для наглядности вы можете закрепить левую часть кода для каждого продукта.

В столбце L для каждой строки введите формулу = K & J.

  • ABC-анализ в сводной таблице Excel
  • ABC-анализ в сводной таблице Excel

ABC-анализ в сводной таблице Excel

Продукты AX — это товары с высокой маржой, которые приносят 70% дохода. На них есть стабильный спрос.

Продукция CZ пользуется самым низким спросом. Сюда могут приходить как неликвиды, так и элитные активы, пользующиеся редким спросом. Требуется дополнительный анализ.

Добавить комментарий

Ваш адрес email не будет опубликован.