Для анализа ассортимента товаров, «перспективности» клиентов, поставщиков, дебиторов применяются методы ABC и XYZ (очень редко).
В основе ABC-анализа – известный принцип Парето, который гласит: 20% усилий дает 80% результата. Преобразованный и детализированный, данный закон нашел применение в разработке рассматриваемых нами методов.
ABC-анализ в Excel
Метод ABC позволяет рассортировать список значений на три группы, которые оказывают разное влияние на конечный результат.
Благодаря анализу ABC пользователь сможет:
- выделить позиции, имеющие наибольший «вес» в суммарном результате;
- анализировать группы позиций вместо огромного списка;
- работать по одному алгоритму с позициями одной группы.
Значения в перечне после применения метода ABC распределяются в три группы:
- А – наиболее важные для итога (20% дает 80% результата (выручки, к примеру)).
- В – средние по важности (30% — 15%).
- С – наименее важные (50% — 5%).
Указанные значения не являются обязательными. Методы определения границ АВС-групп будут отличаться при анализе различных показателей. Но если выявляются значительные отклонения, стоит задуматься: что не так.
Условия для применения ABC-анализа:
- анализируемые объекты имеют числовую характеристику;
- список для анализа состоит из однородных позиций (нельзя сопоставлять стиральные машины и лампочки, эти товары занимают очень разные ценовые диапазоны);
- выбраны максимально объективные значения (ранжировать параметры по месячной выручке правильнее, чем по дневной).
Для каких значений можно применять методику АВС-анализа:
- товарный ассортимент (анализируем прибыль),
- клиентская база (анализируем объем заказов),
- база поставщиков (анализируем объем поставок),
- дебиторов (анализируем сумму задолженности).
Метод ранжирования очень простой. Но оперировать большими объемами данных без специальных программ проблематично. Табличный процессор Excel значительно упрощает АВС-анализ.
Общая схема проведения:
- Обозначить цель анализа. Определить объект (что анализируем) и параметр (по какому принципу будем сортировать по группам).
- Выполнить сортировку параметров по убыванию.
- Суммировать числовые данные (параметры – выручку, сумму задолженности, объем заказов и т.д.).
- Найти долю каждого параметра в общей сумме.
- Посчитать долю нарастающим итогом для каждого значения списка.
- Найти значение в перечне, в котором доля нарастающим итогом близко к 80%. Это нижняя граница группы А. Верхняя – первая в списке.
- Найти значение в перечне, в котором доля нарастающим итогом близко к 95% (+15%). Это нижняя граница группы В.
- Для С – все, что ниже.
- Посчитать число значений для каждой категории и общее количество позиций в перечне.
- Найти доли каждой категории в общем количестве.
АВС-анализ товарного ассортимента в Excel
Составим учебную таблицу с 2 столбцами и 15 строками. Внесем наименования условных товаров и данные о продажах за год (в денежном выражении). Необходимо ранжировать ассортимент по доходу (какие товары дают больше прибыли).
- Отсортируем данные в таблице. Выделяем весь диапазон (кроме шапки) и нажимаем «Сортировка» на вкладке «Данные». В открывшемся диалоговом окне в поле «Сортировать по» выбираем «Доход». В поле «Порядок» — «По убыванию».
- Добавляем в таблицу итоговую строку. Нам нужно найти общую сумму значений в столбце «Доход».
- Рассчитаем долю каждого элемента в общей сумме. Создаем третий столбец «Доля» и назначаем для его ячеек процентный формат. Вводим в первую ячейку формулу: =B2/$B$17 (ссылку на «сумму» обязательно делаем абсолютной). «Протягиваем» до последней ячейки столбца.
- Посчитаем долю нарастающим итогом. Добавим в таблицу 4 столбец «Накопленная доля». Для первой позиции она будет равна индивидуальной доле. Для второй позиции – индивидуальная доля + доля нарастающим итогом для предыдущей позиции. Вводим во вторую ячейку формулу: =C3+D2. «Протягиваем» до конца столбца. Для последних позиций должно быть 100%.
- Присваиваем позициям ту или иную группу. До 80% — в группу А. До 95% — В. Остальное – С.
- Чтобы было удобно пользоваться результатами анализа, проставляем напротив каждой позиции соответствующие буквы.
Вот мы и закончили АВС-анализ с помощью средств Excel. Дальнейшие действия пользователя – применение полученных данных на практике.
XYZ-анализ: пример расчета в Excel
Данный метод нередко применяют в дополнение к АВС-анализу. В литературе даже встречается объединенный термин АВС-XYZ-анализ.
За аббревиатурой XYZ скрывается уровень прогнозируемости анализируемого объекта. Этот показатель принято измерять коэффициентом вариации, который характеризует меру разброса данных вокруг средней величины.
Коэффициент вариации – относительный показатель, не имеющий конкретных единиц измерения. Достаточно информативный. Даже сам по себе. НО! Тенденция, сезонность в динамике значительно увеличивают коэффициент вариации. В результате понижается показатель прогнозируемости. Ошибка может повлечь неправильные решения. Это огромный минус XYZ-метода. Тем не менее…
Возможные объекты для анализа: объем продаж, число поставщиков, выручка и т.п. Чаще всего метод применяется для определения товаров, на которые есть устойчивый спрос.
Алгоритм XYZ-анализа:
- Расчет коэффициента вариации уровня спроса для каждой товарной категории. Аналитик оценивает процентное отклонение объема продаж от среднего значения.
- Сортировка товарного ассортимента по коэффициенту вариации.
- Классификация позиций по трем группам – X, Y или Z.
Критерии для классификации и характеристика групп:
- «Х» — 0-10% (коэффициент вариации) – товары с самым устойчивым спросом.
- «Y» — 10-25% — товары с изменчивым объемом продаж.
- «Z» — от 25% — товары, имеющие случайный спрос.
Составим учебную таблицу для проведения XYZ-анализа.
- Рассчитаем коэффициент вариации по каждой товарной группе. Формула расчета изменчивости объема продаж: =СТАНДОТКЛОНП(B3:H3)/СРЗНАЧ(B3:H3).
- Классифицируем значения – определим товары в группы «X», «Y» или «Z». Воспользуемся встроенной функцией «ЕСЛИ»: =ЕСЛИ(I3<=10%;»X»;ЕСЛИ(I3<=25%;»Y»;»Z»)).
В группу «Х» попали товары, которые имеют самый устойчивый спрос. Среднемесячный объем продаж отклоняется всего на 7% (товар1) и 9% (товар8). Если есть запасы этих позиций на складе, компании следует выложить продукцию на прилавок.
Скачать примеры ABC и XYZ анализов
Запасы товаров из группы «Z» можно сократить. Или вообще перейти по этим наименованиям на предварительный заказ.
АВС-анализ позволяет классифицировать ресурсы компании по степени их значимости. Основная цель применения ABC анализа — определить наиболее прибыльные или продаваемые товары и услуги, самых выгодных клиентов и т.д. Один из профессиональных приемов анализа — ABC анализ в сводной таблице в Excel.
АВС-анализ основан на принципе Парето, согласно которому 20% ресурсов приносят 80% результата. Таким образом, цель данного анализа — разделить все ресурсы на три группы:
- группа А — примерно 20% наиболее прибыльных / маржинальных / продаваемых и т.д. товаров или услуг, которые приносят 80% результата.
- группа В — примерно 30% середнячков, приносящих еще 15% результата.
- группа С — оставшиеся 50% аутсайдеров, приносящих лишь 5% результата.
В этой статье мы разберем, как можно провести ABC анализ в сводной таблице в Excel.
Для начала необходимо определиться с периодом анализа. Наиболее удачный вариант — взять в анализ 12 месяцев, чтобы учесть все сезонные колебания. Однако, иногда нужен анализ более короткого периода — например, в нашем примере для магазина садового инвентаря мы возьмем период 6 месяцев (наивысший спрос в дачный сезон).
Далее нужно подготовить исходные данные для анализа. Это может быть “сырая” база с транзакциями, выгруженная из учетной системы, или уже обработанная для анализа таблица.
В любом случае, если мы анализируем товарный ассортимент по выручке, исходная таблица должна содержать следующие данные:
- наименование товара или наименование группы товара — в зависимости от того, до какой степени детализации нужно провести анализ. Если ваш ассортимент огромен, то, возможно, целесообразнее будет анализировать товары по группам, а не по конкретным SKU.
- выручка по каждому товару или группе товаров.
Для анализа конкретно по выручке этих данных будет достаточно. Если хотите провести анализ по прибыли (или марже), то нужно иметь либо уже готовые данные по прибыли в разрезе товаров, либо издержки по каждому товару (себестоимость производства или стоимость закупки).
В нашем примере АВС-анализа таблица с исходными данными выглядит так.
В таблице содержится различная информация, но для анализа будем использовать только два столбца: Наименование товара и Стоимость.
1. Создадим сводную таблицу для АВС анализа
Для начала нужно создать сводную таблицу. Выделяем исходную таблицу вместе с заголовками, далее вкладка Вставка — Сводная таблица — выбираем на Новый лист.
В поле Строки помещаем наименование товаров, а в поле Значения — сумму по полю Стоимость.
Кстати, при добавлении данных в поле Значения по умолчанию считается количество (в большинстве версий эксель). Чтобы количество превратить в сумму, щелкните на стрелочке и выберите Параметры полей значений, и в открывшемся окне нужно выбрать операцию Сумма.
Также желательно убрать пустую строку внизу таблицы, которая всегда по умолчанию создается в сводных таблицах. Для этого в фильтре столбца снимите “галочку” с пункта (пусто).
Мы получили список товаров и суммы выручки за каждый из них.
2. Получим доли каждого товара
Теперь нам необходимо посчитать, какую долю занимает выручка по каждому товару в общей выручке.
Для этого добавим столбец Стоимость в поле Значения еще раз, просто перетянув его еще раз.
По умолчанию у нас посчиталось количество значений. Как в предыдущем пункте, превратим количество в сумму.
Получили два одинаковых столбца с суммами выручки. Теперь из второго столбца с выручкой нужно сделать доли от выручки по данному товару в общей выручке.
Для этого щелкните правой кнопкой мыши в любом месте второго столбца с суммой и выберите: Дополнительные вычисления — % от суммы по столбцу.
Получили доли выручки от каждого товара. Переименуем столбец с процентами, назовем его Доля, %.
3. Сортируем по убыванию доли выручки
Вспомним, что нам нужно получить в итоге АВС-анализа ассортимента — это разделить товары на категории по убыванию их полезности.
Поэтому теперь нам нужно отсортировать список товаров по убыванию доли их выручки в общей выручке. Таким образом, чтобы товары с самым большими долями сконцентрировались вверху.
Для этого щелкнем на фильтре столбца Названия строк (т.е. столбца с наименованиями товаров) и выберем Дополнительные параметры сортировки.
В окне сортировки нужно выбрать переключатель “по убыванию”, и в выпадающем списке выбрать столбец Доля, %.
Чтобы было понятнее, мы отсортировали столбец Наименование товара по убыванию значений в столбце Доля, %. Столбец с суммой выручки также отсортировался, его дополнительно сортировать не нужно.
На этом этапе уже видно, какие товары попали в группы А, В и С. Однако, это можно увидеть без расчетов лишь потому, что таблица в нашем примере маленькая. А что если у ней сотни или тысячи строк?
4. Получаем долю выручки нарастающим итогом
Как в пункте 2, снова добавляем поле стоимость в поле Значения, и вместо показателя Количество указываем Сумма. Сразу лучше переименовать поле (в примере — Доля нараст. итогом, %).
Теперь опять щелкаем правой кнопкой мыши на любом месте нового поля — Дополнительные вычисления — % от суммы с нарастающим итогом в поле — появляется окно Дополнительные вычисления — нажимаем Ок.
В поле Доля нарастающим итогом считаются доли выручки из предыдущего столбца нарастающим итогом, на картинке показан смысл:
Мы практически достигли цели провести ABC анализ в сводной таблице в Excel. Ведь нам нужно было узнать, какие товары дают примерно 80% выручки, какие — еще 15% (т.е. от 80 до 95%), а какие оставшиеся 5% (от 95 до 100%). И поле “Доля нараст. итогом, %” это показывает.
ABC анализ в сводной таблице в Excel: получение результата анализа
Остался завершающий штрих. Используем условное форматирование, чтобы подсветить группы А, В и С в нашем анализе.
Выделим значения в столбце Доля нараст. итогом, % (без итогов) и перейдем на вкладку Главная — Условное форматирование — Правила выделения ячеек — Между.
Указываем диапазон процентов для группы А (в примере стандартные от 0 до 80%, вы можете указать свой диапазон) и выбираем форматирование в выпадающем списке.
То же самое проделываем для групп В и С, изменив интервалы и форматирование.
Получим наглядную картину разделения товарного ассортимента на группы АВС-анализа. Зеленая заливка относится к товарам группы А, желтая и красная — В и С соответственно.
Открою маленький секрет, на самом деле достаточно только последнего столбца, и вычислять два предыдущие столбца не обязательно. В примере они показаны лишь для того, что вы могли увидеть логику расчетов. И на самом деле их можно даже удалить, если они вам не нужны.
Чтобы удалить промежуточные вычисления из сводной таблицы, щелкаем правой кнопкой мыши — Поля сводной таблицы — и удаляем их из значений.
Таким образом, мы на примере увидели, как можно провести ABC анализ в сводной таблице в Excel. Если такой же анализ проводить в обычной таблице с формулами, есть вероятность, что таблицу нужно будет постоянно дорабатывать, особенно при добавлении новых товаров в ассортимент. В данном же случае таблица полностью интерактивная, достаточно лишь ее обновлять (правая кнопка мыши — Обновить). На практике АВС-анализ часто сочетают с XYZ-анализом.
О том, что такое XYZ-анализ и как его провести в excel, читайте в статье.
Вам может быть интересно:
ABC XYZ анализ — удобный метод оценки эффективности работы «жизненно важных» отделов компании: продаж, маркетинга, склада, финансов. Представляем подробную инструкцию, как выполнить ABC и XYZ-анализ в Excel.
С общими принципами проведения ABC и XYZ-анализа можно ознакомиться здесь. А ниже — пошаговая инструкция, как сделать ABC-анализ в Excel.
Содержание:
- 4 вопроса до начала ABC-анализа
- ABC-анализ в Excel: пошаговая инструкция, рабочие образцы с формулами
- Сортировка выручки по убыванию
- Доля каждой строки в общем параметре
- Определяем группу
- XYZ-анализ в Excel: оценка динамики продаж
- Выгружаем данные из учётной системы
- Рассчитываем коэффициент вариации
- Присваиваем значения XZY и соединяем с ABC
- Цель. Зачем вы проводите исследование? Увеличить выручку компании, исключить возможность упущенной выгоды и т.п.
- Результат. Как вы сможете применить полученные значения? Оптимизируем складские запасы, пересмотрим условия договоров и т.п.
- Источники данных. Как вы соберете исходные данные: объект и параметр анализа? Объект анализа — перечень товаров, параметр — выручка в количественном и денежном выражении.
- Матрица. Какое АВС XYZ процентное распределение закладывать в расчет? Классический вариант на основе принципа Парето: 80% приносят выручки приносят 20% ключевых клиентов. Чтобы назначить распределение по группам, нужно знать специфику работы компании, жизненные циклы и сезональность. Ошибки в матрице могут привести к тому, что в неприбыльной группе С окажутся важные покупатели с редкими закупками.
ABC-анализ в Excel: пошаговая инструкция, рабочие образцы с формулами
Ассортиментный ABC анализ проведем на примере компании по продаже запасный частей для сельскохозяйственной техники.
Количество товара — более 5 000 позиций. Объединяем их в группы по видам номенклатуры.
Из учетной системы выгружаем данные за 2020 год:
- количество продаж с разбивкой по кварталам;
- цена реализации за единицу;
- выручка итого за год в рублях. Важно использовать одну валюту для всего отчета, чтобы исключить влияние курсовых разниц.
Сортировка выручки по убыванию
Выделяем диапазон ячеек: вся таблица вместе с заголовками без строки «Итого».
В ниспадающем меню выбираем:
Данные — Сортировка — Сортировать по:
- столбец «Выручка»
- сортировка «Значения»
- порядок «По убыванию»
Нажимаем «Ок».
Система выстраивает таблицу по убыванию размера выручки в столбце D.
Доля каждой строки в общем параметре
Определяем долю каждой номенклатуры в выручке:
- добавляем графу Доля (Е). Формат ячеек процентный;
- в строку 2 для товара 6 вводим формулу: выручка товара 6 / выручка итого;
- протягиваем формулу вниз по всем товарам.
Добавляем графу F и рассчитываем Долю накопительным итогом: складываем текущее значение со всеми предыдущими.
Символ & предупреждает Excel, что формулу нельзя двигать:
- & перед буквой — по столбцам;
- & перед цифрой — по строкам.
Перед тем как создавать ABC-таблицу проверьте долю каждого товара в общем значении (выручки, запасах, себестоимости и пр.). Проводить ABC аналитику бессмысленно, если объект распределяется примерно в равных долях. Каждый показатель вносит одинаковый вклад в результат.
Определяем группу
Создаем графу Группа. Каждому товару присваиваем значения А, В, С в зависимости от доли в выручке.
Руководство утвердило матрицу:
| Группа | Диапазон |
|---|---|
| A | до 70% |
| B | 70-90% |
| C | 90-100% |
В ячейке G2 прописываем формулу =ЕСЛИ(F2<=70%;"A";ЕСЛИ(F2>=90%;"C";"В")). Протягиваем формулу вниз по всем товарам.
В примере для наглядности проценты заданы цифрами.
В рабочем файле Excel вместо процентов ссылки на ячейки со значениями матрицы. При изменении параметров матрицы формула будет автоматически пересчитываться по всем товарам.
В столбце G каждой номенклатурной группе присвоен код А, В, С.
В группу А попали товары, которые приносят основную прибыль.
В группу В — продукция компании, на которую нерегулярный спрос.
Группа С — товары, которые зарабатывают только 10% от выручки.
XYZ-анализ в Excel: оценка динамики продаж
XYZ исследование позволит увидеть изменения спроса на продукцию компании.
Выгружаем данные из учётной системы
Создаем таблицу с количеством продаж за 2020 год по каждой товарной группе по каждому кварталу.
Рассчитываем коэффициент вариации
Вариация — степень разброса значений в числовой последовательности. Показывает насколько данные отклоняются от средних показателей. В финансах этот коэффициент оценивает изменчивость, волатильность, сезональнальность. Чем он меньше, тем стабильнее оцениваемый параметр (спрос на товар, движение по складу, платежи и т.д.).
Создаем графу Средние продажи. В строку 3 вводим формулу =СРЗНАЧ(B3:E3) и копируем ее для всех товарных позиций.
Создаем графу Стандартное отклонение. Стандартное отклонение / Средние продажи.
В строку 3 вводим формулу =СТАНДОТКЛОН(B3:E3) и копируем ее для всех товарных позиций.
Создаем графу Вариация, %. Вводим формулу:
Столбец Стандартное отклонение / Столбец Средние продажи
XYZ-анализ в Excel: формула расчёта коэффициента вариации 
XYZ-анализ в Excel: рассчитанный коэффициент вариации
Присваиваем значения XZY и соединяем с ABC
Руководство утвердило матрицу XYZ аналитики:
| Группа | Диапазон |
|---|---|
| X — постоянный спрос | до 15% |
| Y — изменчивый спрос, сезональность | от 15% до 50% |
| Z — случайный спрос | больше 50% |
Ранжируем полученные результаты с помощью функции Excel «ЕСЛИ».
В ячейку J3 вводим формулу: =ЕСЛИ(I3<=15%;"X";ЕСЛИ(I3>=50%;"Z";"Y")). Копируем формулу по всем товарным срокам.
XYZ-анализ в Excel: группы товаров по методу XYZ — формула 
XYZ-анализ в Excel: группы товаров по методу XYZ — результат
Создаем графу Группа по методу ABC. Подтягиваем код группы из таблицы ABC анализа с помощью формулы: =ВПР(A3;ABC!$A$1:$G$12;7;0)
Как настроить формулу ВПР:
Задача функции: по коду товара в исходной таблице найти значение А, В или С и перенести его отчётную таблицу XYZ.
А3 — параметр, по которому ищем значение, например «Товар 6».
ABC!$A$1:$G$12 — ссылка на диапазон исходной таблицы. В ней строго в первом столбце должен быть параметр, по которому ищем значение «Товар 6».
7 — порядковый номер столбца, в котором в исходной находятся значения (коды А, В, С)
0 — значение ЛОЖЬ. Для Ecxel признак того, что искомый результат должен соответствовать всем 3-м предыдущим условиям.
По каждому товару получаем двойную кодировку ABC и XYZ аналитики.
Для наглядности можно скрепить лва кода по каждому товару.
В столбец L для каждой строки вводим формулу =K&J.
Товары AX — высокоприбыльные позиции, которые формируют 70% выручки. На них стабильный спрос.
Товары CZ — позиции с самым низким спросом. Сюда могут попасть как неликвиды, так и элитные товары с редким спросом. Требуется дополнительная аналитика.
Подробнее о сути, эффективности и недостатках ABC XYZ анализа читайте здесь.
Новости
ABC-анализ – это инструмент оценки большого объема данных. В торговле используется для анализа ассортимента и клиентской базы.
В основе лежит принцип Парето:
- 20% ассортимента дают 80% прибыли;
- 20% ассортимента занимают 80% места на складе;
- 20% покупателей оформляют 80% возвратов;
- 20% поставщиков дают 80% товаров;
И еще ряд вариантов классификации, которые вы сами выбираете.
Рассмотрим анализ ассортимента магазина по обороту и прибыли.
По итогам ABC-анализа получаем следующие группы:
- A – 20% товаров приносят 80% отдачи;
- B – 30% ресурсов дают 15% эффективности;
- C – 50% ресурсов составляют 5% прибыли.
Этот анализ необходим для принятия правильных управленческих решений, помогает предприятиям с широким ассортиментом наладить процесс закупок и извлечь максимальную выгоду.
Преимущества ABC-анализа
- Простота использования – только Excel таблица и сами данные, ничего лишнего.
- Функциональность – можно проанализировать что угодно.
- Точность результата – при анализе сложно допустить ошибку.
Недостатки ABC-анализа
- Анализ предыдущей статистики не дает прогнозов на будущее.
- Эффективность результата зависит от качества информации.
- Неактуальность результатов в случае анализа одного критерия.
- При использовании не учитываются внешние факторы спроса потребителей (например, сезонность, форс-мажоры).
Как правильно пользоваться ABC-анализом
- Делайте анализ товаров одной категории. Разбейте товары по группам, если хотите проанализировать весь ассортимент. Но не берите для сравнения неравнозначные товары, например автозапчасти и обувь, так как результаты окажутся некорректными.
- Избавьтесь от дубликатов. Просуммируйте значения удвоенных позиций, чтобы избежать ложных результатов.
- Делайте анализ по нескольким критериям: обороту, прибыли, среднему чеку, рентабельности и т. д. Сводите результаты в одну таблицу для наглядности исследуемых данных.
- Без лишнего фанатизма. Нет смысла делать частый анализ. Идеальный интервал – раз в квартал.
- Не анализируйте новинки. Новые товары ещё не получили нужного количества данных для статистики и её изучения, сводки, в отличие от других продуктов. Дайте новому ассортименту минимум полгода, чтобы получить объективную картину его продаж.
- Сравнивайте прошлые показатели с новыми. Так можно выявить динамику спроса, получить некоторые представления о товаре и его востребованности.
- Не забывайте про акции и распродажи. Взяв периоды продажи продукта по дисконту, можно получить необъективные результаты анализа. Лучше возьмите обычный период продаж.
- Не торопитесь с выводами о группе С. Выявите причину появления продукта в данной группе, потому что чаще всего это новые позиции.
Как провести ABC-анализ ассортимента магазина
Анализ состоит из трёх основных шагов. Для наглядности рассмотрим применение АВС-анализа в продажах строительного магазина.
Шаг 1. Выбираем критерий классификации
Рассмотрим объем продаж и прибыль.
Шаг 2. Расчёт нарастающего итога
Отсортируйте товары по убыванию значения анализируемого критерия. Далее рассчитайте долю занимаемых позиций тем или иным товаром от всего объёма продаж по формуле:
Прибыль товара / общая сумма прибыли по всем позициям * 100%
Ниже мы покажем на конкретном примере, как правильно следует это сделать.
Шаг 3. Выделение групп A, B и C
ABC-анализ делит ваши товары на группы:
- A – 20% товаров приносят 80% продаж;
- B – 30% товаров дают 15% продаж;
- C – 50% товаров составляют 5% продаж.
Пример ABC-анализа товаров в Excel
Сначала загрузите отчет о продажах из учетной программы или Excel таблицы в новую Excel таблицу.
Для примера был выгружен отчёт из сервиса МойСклад, который собирает данные по оборотам, остаткам, движению денег, прибыли и убыткам, продажам и рентабельности в разрезе товаров, контрагентов и сотрудников.
Пример
Проанализируем количество проданных товаров строительного магазина за три месяца.
Отсортируем по убыванию количество проданных товаров (по столбцу «Кол-во»):
Рассчитаем вклад каждого товара в общую сумму по формуле:
количество товара / итоговая сумма.
Присваиваем ячейкам с полученными расчетными данными процентный формат:
Посчитаем вклад каждого товара с нарастающим итогом (сложим проценты из столбца «вклад»):
второй товар + первый → третий товар + второй + первый
И так далее. Значение первого товара остается неизменным.
Применяем эту формулу ко всем ячейкам в столбце и переводим значения в проценты. У последнего товара в списке должно получиться 100%.
Теперь в соседнем столбце разделим товары на группы А, В, С. Для того напишем в строке первого товара следующую встроенную формулу Excel:
где I13 – это ячейка в столбце «нарастающий итог» для первого товара. После применения формулы для всех продуктов выделим каждую группу цветом:
Данный пример детально разобрали на бесплатном курсе «Управление закупками», где вы научитесь анализировать продажи, формировать закупки точно и в срок, правильно строить работу с поставщиками и определять себестоимость товаров – все это в формате 10-минутных видео с разбором каждого шага.
Получили некоторый результат. Что дальше
АВС-анализ помогает работать с товарами, способствует росту прибыли магазина. В соответствии с тем, в какую группу попал тот или иной продукт по результатам анализа, выбирается дальнейший план действий по закупкам товаров.
Руководитель учебного центра МойСклад Алексей Еранов дал несколько рекомендаций, которые нужно применять в зависимости от группы товара:
- В категорию А должны попасть самые доходные позиции. Поэтому следите за остатками, создайте резерв, всегда поддерживайте их наличие. Товары этой группировки требуют регулярной инвентаризации. Выборочно пересчитывать можно еженедельно, а полностью – не реже одного раза в квартал.
- Для товаров группы В ревизию можно проводить реже, но при этом не ослаблять контроль за уровнем остатка.
- В группу С попадают аутсайдеры. Перед тем как сократить ассортимент и исключить товары С, найдите причину низких продаж. Возможно, новый продукт оказался невостребованным у целевой аудитории, или выбрано неудачное расположение в торговом зале, или на сайте сделаны фотографии не с самых лучших ракурсов.
Как анализировать поставщиков
АВС-анализ полезно проводить не только для товаров, но и контрагентов. Это может помочь снизить затраты на закупках.
Что нужно для такого анализа?
Для проведения исследования потребуются данные о годовом обороте каждого поставщика, которые мы вбиваем в чистую таблицу следующим образом:
- 1 столбец – информация о годовом обороте в порядке убывания;
- 2 столбец – расчет доли оборота каждого поставщика в процентах от общего оборота;
- 3 столбец – накопительные значения оборота, в процентах.
В соответствии с полученными результатами делим поставщиков на три группы:
Таким образом, с целью минимизации издержек на закупки следует работать с представителями группы А.
Комплексный ABC-анализ
Анализ категории товара только по одному признаку – это не панацея. Можно анализировать и по двум критериям, например, сначала по количеству проданных товаров, а потом прибыльности с продаж. Процедура не отличается от описанных выше шагов.
Тогда наша таблица будет выглядеть так:
Но в этом случае мы получаем деление товаров не на три группы, а уже на девять. У каждой группы свои особенности, исходя из них, выбираем нужную стратегию продаж.
Совмещенный ABC/XYZ-анализ
Так как ABC-анализ не учитывает периодичность продаж и частоту покупок конкретных товаров, на помощь приходит XYZ-анализ. С его помощью вы сможете разделить товары на группы в зависимости от стабильности спроса.
Какой смысл скрывается под этими буквами:
- X – постоянный спрос на продукт или услугу, максимальная точность прогноза. Коэффициент вариативности 0–10%.
- Y – менее регулярный спрос, уже сложнее спрогнозировать дальнейшие продажи из-за различных факторов: сезонности, дней недели и т.д. Коэффициент вариативности 10–25%.
- Z – самые непредсказуемые по спросу товары – с коэффициентом вариативности больше 25%.
Чтобы провести XYZ-анализ, следует внести список товаров и их помесячный оборот, например, за квартал в Excel-таблицу. Эти данные можно найти в отчёте «Прибыльность по товарам» от сервиса МойСклад.
Рассчитаем коэффициент вариации, применяя формулу:
= СТАНДОТКЛОНП/СРЗНАЧ
Он покажет степень отклонения данных от среднего значения.
После применения функции к каждой строчке столбца переведём в процентный формат получившиеся данные:
Распределение по категориям:
- X – от 0 до 10%;
- Y – от 10 до 25%;
- Z – от 25 до 100% и выше.
Добавим полученные результаты в таблицу с ABC-анализом:
Определим группу товара, присвоив индекс из двух букв:
- первая – по результату ABC-анализа;
- вторая – по результату XYZ-анализа.
Результаты комплексного ABC/XYZ-анализа
Как и в случае с двойным ABC-анализом, мы получаем не три вида товаров в соответствии с проведённой классификацией, а уже девять групп.
Вот что они обозначают:
Что даёт совмещенный анализ?
- Выявить товары с низким спросом, занимающие место на складе.
- Упорядочить определенную группу товаров, если в ассортименте категории продуктов есть позиции, которые уже неактуальны и неэффективны.
- Разработать стратегию и план дальнейших продаж.
Применение ABC и XYZ анализов в управлении закупками
Сервис МойСклад может помочь отсортировать полученные после анализа данные и решить, в какой момент и в каком количестве нам необходимо закупить, например, товары категории A:
Точно также сортируем и строим прогноз для любых групп из ABC/XYZ анализа. Так вы делаете заказы поставщикам осознанно, на основании статистики продаж.
Чтобы не допустить ручных ошибок при учете, избавиться от рутинных операций и сэкономить бюджет, воспользуйтесь возможностями МоегоСклада:
- Автоматизация заказов поставщикам на основании статистики продаж.
- Массовое обновление цен и товаров.
- Выявление реальной прибыли и рентабельности по каждому товару
- Контроль запасов товара и сотрудников.
Чтобы оперативно мониторить и своевременно влиять на динамику реализации продукции, коммерческой службе требуются аналитические отчеты, в которых раскрываются различные аспекты процесса реализации.
Большинство современных учетных программ имеет встроенные наборы аналитических отчетов о продажах, но все они формируют показатели только по заданным параметрам отбора. Для ввода новых показателей нужно привлекать программистов.
Если пользователям такой отчетности требуется часто менять структуру отчетов о продажах или создавать новые отчеты, то для самостоятельного решения подобных задач вполне подойдет всем знакомый табличный редактор Excel.
ИНСТРУМЕНТАРИЙ EXCEL ДЛЯ СОЗДАНИЯ АНАЛИТИЧЕСКИХ ОТЧЕТОВ
В табличном редакторе Excel предусмотрен широкий выбор инструментов, с помощью которых можно создать аналитические отчеты на основе данных о реализации продукции. Для успешной работы с этими инструментами от пользователя требуется определенный уровень подготовки. Представим перечень инструментария для создания аналитических отчетов:
- продвинутый уровень — макросы, Power BI;
- хороший уровень — OLAP-кубы, Power Query/Pivot;
- средний уровень — сводные таблицы, формулы.
Рассмотрим особенности применения каждого из указанных инструментов, а также знания и навыки пользователя, которые нужны для их качественного применения.
Макросы
Работа с макросами основана на применении языка программирования VBA, который можно использовать для расширения возможностей MS Excel и других приложений MS Office. С помощью прописанных в макросе команд можно:
- проводить различные обработки и сортировки данных в файле Excel;
- получать информацию из других файлов;
- создавать сводные таблицы;
- добавлять в создаваемые отчеты дополнительные функции, которые невозможно получить обычными средствами Excel.
Чтобы создавать макросы, пользователь должен отлично знать редактор Excel, владеть языком программирования VBA. Приведу в качестве примера запись макроса, с помощью которого в файле Excel автоматически из массива данных формируется сводная таблица:
Очевидно, что работать с макросами может незначительная часть сотрудников, которые создают отчетность в Excel.
Power BI
Power BI по своей сути является отдельным программным продуктом, в который можно загрузить файлы Excel и произвести дальнейшую обработку с целью анализа и визуализации данных.
Power BI включает в себя весь функционал надстроек Excel (Power Query и Power Pivot плюс улучшенные механизмы визуализации из Power View и Power Map). Преимущества данного инструмента: с отчетами может работать сразу несколько пользователей плюс широкий диапазон визуализации показателей отчетов.
Идет тренд к интеграции Excel c Power BI. Например, в Excel 2019 появилась возможность напрямую загружать данные в функционал Power BI. Для этого в меню выбираем:
Файл > Опубликовать > Опубликовать в Power BI.
Передав файл, нажимаем кнопку «Перейти к Power BI», чтобы просмотреть загруженные данные.
Главные сложности использования Power BI: загруженные таблицы Excel нужно дополнительно обрабатывать для корректного включения их данных в отчеты, а формулы для создания отчетов в этой программе отличаются от формул Excel.
Power BI постоянно развивается, однако на сегодняшний момент использовать его для формирования аналитических отчетов достаточно трудоемко.
OLAP-кубы
OLAP (online analytical processing) — аналитическая технология обработки данных в реальном времени, при которой данные из учетной базы выгружаются в файлы Excel, а затем обрабатываются с помощью другого инструмента Excel (сводных таблиц).
Для начала работы нужно создать подключение файла Excel к данным OLAP-куба (Данные → Получение внешних данных), а затем из открывшегося окна перетащить курсором в табличную часть Excel показатели, которые требуются.
В результате будет получена сводная таблица с отчетными данными. Главное ее преимущество — возможность автоматической актуализации данных при каждом подключении к OLAP-кубу.
Power Query/Pivot
Данные инструменты являются надстройками Excel, поэтому работа с ними происходит непосредственно из меню табличного редактора.
Power Query появился в версии Excel 2013 как отдельная надстройка, требующая подключения, а с версии 2016 г. весь функционал Power Query уже встроен по умолчанию и находится на вкладке «Данные → Получить и преобразовать».
Power Query обладает значительными возможностями для целей создания отчетов. С помощью этой надстройки можно:
- загружать данные в Excel из почти 40 различных источников, среди которых базы данных (SQL, Oracle, Access, Teradata), корпоративные ERP-системы (SAP, Microsoft Dynamics, 1C), интернет-сервисы;
- собирать данные из файлов всех основных типов данных (XLSX, TXT, HTML, XM) — поодиночке и сразу из всех файлов указанной папки;
- зачищать полученные данные от лишних пробелов, столбцов или строк, повторов, служебной информации в заголовках, непечатаемых символов и т. д;
- трансформировать таблицы Excel, приводя их в желаемый вид (фильтровать, сортировать, менять порядок столбцов, транспонировать, добавлять итоги, разворачивать кросс-таблицы в плоские и сворачивать обратно);
- подставлять данные из одной таблицы в другую по совпадению одного или нескольких параметров (полностью заменяет формулу ВПР и ее аналоги).
Главная особенность Power Query: все действия по импорту и трансформации данных запоминаются в виде запроса — последовательности шагов на внутреннем языке программирования Power Query, который лаконично называется «М».
Шаги можно отредактировать, воспроизвести любое количество раз (обновить запрос). Поэтому данный инструмент может служить хорошей альтернативой создания макросов или прописания очень сложных формул при построении отчетов.
Power Pivot — надстройка Excel, предназначенная для разнопланового анализа больших объемов данных. Поэтому результат работы с Power Pivot похож на усложненные сводные таблицы.
Общие принципы работы в Power Pivot:
- внешние данные загружают в Power Pivot, который поддерживает 15 различных источников: распространенные базы данных (SQL, Oracle, Access), файлы Excel, текстовые файлы, веб-каналы данных. Если Power Query использовать как источник данных, то возможности загрузки увеличиваются многократно;
- между загруженными таблицами настраиваются связи, то есть создается Модель Данных. Это позволит строить отчеты по любым полям из имеющихся таблиц так, будто это одна таблица;
- при необходимости в Модель Данных добавляют дополнительные вычисления с помощью вычисляемых столбцов (аналог столбца с формулами в «умной» таблице) и мер (аналог вычисляемого поля в сводной таблице). Нужные вычисления записываются на специальном внутреннем языке Power Pivot, который называется DAX (Data Analysis Expressions);
- на листе Excel по Модели Данных строят интересующие отчеты в виде сводных таблиц и диаграмм.
Сводные таблицы
Первый интерфейс сводных таблиц (сводных отчетов) был включен в состав Excel в 1993 г. (в версии Excel 5.0). Этот инструмент изначально создавался для построения отчетов на основе многомерных данных. Он имеет достаточно широкие функциональные возможности.
Реализованный в Excel инструмент сводных таблиц позволяет расположить измерения многомерных данных в области рабочего листа. Упрощенно можно представлять себе сводную таблицу как отчет, лежащий сверху диапазона ячеек (хотя есть определенная привязка форматов ячеек к полям сводной таблицы).
Сводная таблица Excel имеет четыре области отображения информации: фильтр, столбцы, строки и данные. Измерения данных именуются полями сводной таблицы. Эти поля имеют собственные свойства и формат отображения.
С помощью сводных таблиц можно группировать, сортировать, фильтровать и менять расположение данных с целью получения различных аналитических выборок.
Обновление отчета производится простыми средствами пользовательского интерфейса. Данные автоматически агрегируются по заданным правилам. Не требуется дополнительный или повторный ввод какой-либо информации.
Сводные таблицы Excel являются самым востребованным инструментом при работе с многомерными данными в больших объемах информации. Этот инструмент поддерживает в качестве источника данных как внешние источники данных, так и внутренние диапазоны электронных таблиц.
Для работы со сводными таблицами не нужны знания в области программирования VBA или внутренних языков программирования надстроек Excel.
Формулы Excel
Механизм формул появился в первой версии табличного редактора. С тех пор он значительно расширился. На сегодняшний день функционал формул содержит больше сотни наименований. С учетом того что при создании отчетов формулы могут комбинироваться, количество вариантов трудно подсчитать.
Формулы отлично подходят для создания двухмерных отчетов при обработке небольшого объема данных. Преимущество формул в том, что их легко копировать или транспонировать на другие ячейки отчетов, переделать или защитить от изменений.
В редакторе Excel есть встроенный справочник по формулам, что облегчает работу пользователям со средним уровнем владения Excel. Поэтому я предлагаю рассмотреть возможности использования функционала формул при разработке аналитических отчетов из одного источника данных.
ВОЗМОЖНОСТИ ИСПОЛЬЗОВАНИЯ ФОРМУЛ ДЛЯ РАЗРАБОТКИ АНАЛИТИКИ ПРОДАЖ В EXCEL
Вне зависимости от выбора инструментария Excel при разработке аналитических отчетов о реализации продукции в первую очередь создают новую книгу и загружают в нее исходные данные из учетной программы компании для последующей их обработки.
Удобнее всего сделать это путем формирования в учетной программе реестра продаж с нужными показателями и сохранения его в виде файла формата Excel. Далее отчетность будем создавать на отдельных листах этого файла.
Возьмем самые востребованные данные о продажах, на основе которых создаются аналитические отчеты:
- наименование покупателя;
- наименование продукции;
- дата отгрузки продукции покупателю;
- регион реализации продукции;
- сумма реализации продукции;
- валовая прибыль от реализации продукции;
- маржа (процентное соотношение валовой прибыли к сумме реализации).
Материал публикуется частично. Полностью его можно прочитать в журнале «Планово-экономический отдел» № 10, 2020.