4 вопроса до начала ABC-анализа
- Задача. Зачем вы проводите исследования? Увеличить выручку компании, исключить возможность упущенной выгоды и т.д.
- Результат. Как применить полученные значения? Оптимизируем складские запасы, пересматриваем условия договоров и т.д.
- Источники данных. Как вы будете собирать необработанные данные: объект и параметр анализа? Объектом анализа является перечень товаров, параметром — выручка в количественном и денежном выражении.
- Матрица. Какое процентное распределение ABC XYZ должно быть включено в расчет? Классический вариант, основанный на принципе Парето: 80% доходов приносят 20% ключевых клиентов. Чтобы назначить распределение по группам, необходимо знать специфику компании, жизненные циклы и сезонность. Ошибки в матрице могут привести к убыточной C, которая группирует важных покупателей с нечастыми покупками.
ABC-анализ в Excel: пошаговая инструкция, рабочие образцы с формулами
ABC-анализ ассортимента будет проводиться на примере компании, занимающейся продажей запчастей для сельхозтехники.
Количество товаров превышает 5000 наименований. Объединяем их в группы по типам номенклатуры.
Загружаем данные за 2020 год из учетной системы:
- количество продаж за квартал;
- цена продажи за единицу;
- общая выручка за год в рублях. Важно использовать одну валюту для всего отчета, чтобы избежать влияния курсовой разницы.
ABC-анализ в Excel: пример
Сортировка выручки по убыванию
Выделите диапазон ячеек: вся таблица с заголовками без строки «Итого».
В раскрывающемся меню выберите:
Данные — Сортировать — Сортировать по:
- столбец «Выручка»
- сортировка «Ценности»
- в порядке убывания
Щелкните «ОК».
Система строит таблицу в порядке убывания доходов в столбце D.
ABC-анализ в Excel: сортировка чеков по убыванию 
ABC-анализ в Excel: сортировка по убыванию 
Доля каждой строки в общем параметре
Определите долю каждой статьи в выручке:
- добавьте столбец «Поделиться» (E). Формат ячейки — процентный;
- в строке 2 для продукта 6 мы вставляем формулу: выручка от продукта 6 / общая выручка;
- мы удлиняем формулу для всех продуктов.
Добавьте столбец F и рассчитайте квоту как совокупную сумму: сложите текущее значение со всеми предыдущими.
ABC Analysis в Excel — формулы для расчета доли дохода от каждого продукта
Символ & предупреждает Excel, что формулу нельзя переместить:
- & перед буквой — столбцами;
- & перед цифрой — построчно.
ABC-анализ в Excel: доля каждого продукта в выручке
Перед созданием таблицы ABC проверьте долю каждого элемента в общей стоимости (выручка, запасы, стоимость и т.д.). Нет смысла проводить ABC-анализ, если объект распределен примерно поровну. Каждый показатель в равной степени способствует результату.
Определяем группу
Создайте столбец группы. Каждому продукту мы присваиваем значения A, B, C в соответствии с долей выручки.
Руководство утвердило матрицу:
| Группа | Диапазон |
|---|---|
| А | до 70% |
| Б | 70–90% |
| С | 90-100% |
В ячейке G2 запишите формулу = ЕСЛИ (F2 = 90%; «C»; «B»)). Расширение формулы для всех продуктов.
В примере для наглядности проценты указаны цифрами.
В рабочем файле Excel вместо процентов ссылки на ячейки со значениями массива. При изменении параметров матрицы формула будет автоматически пересчитана для всех товаров.
ABC-анализ в Excel: распределение групп, формулы 
ABC-анализ в Excel: распределение по группам — результат обработки
В столбце G каждой группе товаров присвоен код A, B, C.
В группу А входят товары, приносящие основную прибыль.
Группа Б — продукция компании, на которую существует неравномерный спрос.
Группа C: продукты, приносящие только 10% дохода.
XYZ-анализ в Excel: оценка динамики продаж
Поиск XYZ позволит вам увидеть изменения спроса на продукцию компании.
Выгружаем данные из учётной системы
Создайте таблицу с количеством продаж на 2020 год для каждой группы продуктов за каждый квартал.
XYZ-анализ в Excel: продажи по кварталам
Рассчитываем коэффициент вариации
Вариация: степень разброса значений в числовой последовательности. Он показывает, насколько данные отклоняются от среднего. В области финансов этот отчет оценивает изменчивость, нестабильность и сезонность. Чем он меньше, тем стабильнее оцениваемый параметр (спрос на товары, движение запасов, платежи и т.д.).
Создайте столбец «Средние продажи». В строке 3 введите формулу = СРЕДНЕЕ (B3: E3) и скопируйте ее для всех позиций товаров.
XYZ-анализ в Excel: формула для расчета средних продаж
Создайте столбец стандартного отклонения. Стандартное отклонение / средние продажи.
В строке 3 введите формулу = СТАНДОТКЛОН (B3: E3) и скопируйте ее для всех заголовков.
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
Создайте столбец группы, используя метод 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.
Для наглядности вы можете закрепить левую часть кода для каждого продукта.
В столбце L для каждой строки введите формулу = K & J.
ABC-анализ в сводной таблице Excel
Продукты AX — это товары с высокой маржой, которые приносят 70% дохода. На них есть стабильный спрос.
Продукция CZ пользуется самым низким спросом. Сюда могут приходить как неликвиды, так и элитные активы, пользующиеся редким спросом. Требуется дополнительный анализ.