Для анализа ассортимента товаров, «перспективности» клиентов, поставщиков, дебиторов применяются методы ABC и XYZ (очень редко).
В основе ABC-анализа – известный принцип Парето, который гласит: 20% усилий дает 80% результата. Преобразованный и детализированный, данный закон нашел применение в разработке рассматриваемых нами методов.
ABC анализ в Excel
Метод ABC позволяет рассортировать список значений на три группы, которые оказывают разное влияние на конечный результат.
Благодаря анализу ABC пользователь сможет:
- выделить позиции, имеющие наибольший «вес» в суммарном результате;
- анализировать группы позиций вместо огромного списка;
- работать по одному алгоритму с позициями одной группы.
Значения в перечне после применения метода ABC распределяются в три группы:
- А – наиболее важные для итога (20% дает 80% результата (выручки, к примеру)).
- В – средние по важности (30% - 15%).
- С – наименее важные (50% - 5%).
Указанные значения не являются обязательными. Методы определения границ АВС-групп будут отличаться при анализе различных показателей. Но если выявляются значительные отклонения, стоит задуматься: что не так.
Условия для применения ABC-анализа:
- анализируемые объекты имеют числовую характеристику;
- список для анализа состоит из однородных позиций (нельзя сопоставлять стиральные машины и лампочки, эти товары занимают очень разные ценовые диапазоны);
- выбраны максимально объективные значения (ранжировать параметры по месячной выручке правильнее, чем по дневной).
Для каких значений можно применять методику АВС-анализа:
- товарный ассортимент (анализируем прибыль),
- клиентская база (анализируем объем заказов),
- база поставщиков (анализируем объем поставок),
- дебиторов (анализируем сумму задолженности).
Метод ранжирования очень простой. Но оперировать большими объемами данных без специальных программ проблематично. Табличный процессор Excel значительно упрощает АВС-анализ.
Общая схема проведения:
- Обозначить цель анализа. Определить объект (что анализируем) и параметр (по какому принципу будем сортировать по группам).
- Выполнить сортировку параметров по убыванию.
- Суммировать числовые данные (параметры – выручку, сумму задолженности, объем заказов и т.д.).
- Найти долю каждого параметра в общей сумме.
- Посчитать долю нарастающим итогом для каждого значения списка.
- Найти значение в перечне, в котором доля нарастающим итогом близко к 80%. Это нижняя граница группы А. Верхняя – первая в списке.
- Найти значение в перечне, в котором доля нарастающим итогом близко к 95% (+15%). Это нижняя граница группы В.
- Для С – все, что ниже.
- Посчитать число значений для каждой категории и общее количество позиций в перечне.
- Найти доли каждой категории в общем количестве.
АВС анализ товарного ассортимента в Excel
Составим учебную таблицу с 2 столбцами и 15 строками. Внесем наименования условных товаров и данные о продажах за год (в денежном выражении). Необходимо ранжировать ассортимент по доходу (какие товары дают больше прибыли).
1. Отсортируем данные в таблице. Выделяем весь диапазон (кроме шапки) и нажимаем «Сортировка» на вкладке «Данные». В открывшемся диалоговом окне в поле «Сортировать по» выбираем «Доход». В поле «Порядок» - «По убыванию».
2. Добавляем в таблицу итоговую строку. Нам нужно найти общую сумму значений в столбце «Доход».
3. Рассчитаем долю каждого элемента в общей сумме. Создаем третий столбец «Доля» и назначаем для его ячеек процентный формат. Вводим в первую ячейку формулу: =B2/$B$17 (ссылку на «сумму» обязательно делаем абсолютной). «Протягиваем» до последней ячейки столбца.
4. Посчитаем долю нарастающим итогом. Добавим в таблицу 4 столбец «Накопленная доля». Для первой позиции она будет равна индивидуальной доле. Для второй позиции – индивидуальная доля + доля нарастающим итогом для предыдущей позиции. Вводим во вторую ячейку формулу: =C3+D2. «Протягиваем» до конца столбца. Для последних позиций должно быть 100%.
5. Присваиваем позициям ту или иную группу. До 80% - в группу А. До 95% - В. Остальное – С.
6. Чтобы было удобно пользоваться результатами анализа, проставляем напротив каждой позиции соответствующие буквы.
Вот мы и закончили АВС-анализ с помощью средств Excel. Дальнейшие действия пользователя – применение полученных данных на практике.
XYZ анализ: пример расчета в Excel
Данный метод нередко применяют в дополнение к АВС-анализу. В литературе даже встречается объединенный термин АВС-XYZ-анализ.
За аббревиатурой XYZ скрывается уровень прогнозируемости анализируемого объекта. Этот показатель принято измерять коэффициентом вариации, который характеризует меру разброса данных вокруг средней величины.
Коэффициент вариации – относительный показатель, не имеющий конкретных единиц измерения. Достаточно информативный. Даже сам по себе. НО! Тенденция, сезонность в динамике значительно увеличивают коэффициент вариации. В результате понижается показатель прогнозируемости. Ошибка может повлечь неправильные решения. Это огромный минус XYZ-метода. Тем не менее…
Возможные объекты для анализа: объем продаж, число поставщиков, выручка и т.п. Чаще всего метод применяется для определения товаров, на которые есть устойчивый спрос.
Алгоритм XYZ-анализа:
- Расчет коэффициента вариации уровня спроса для каждой товарной категории. Аналитик оценивает процентное отклонение объема продаж от среднего значения.
- Сортировка товарного ассортимента по коэффициенту вариации.
- Классификация позиций по трем группам – X, Y или Z.
Критерии для классификации и характеристика групп:
- «Х» - 0-10% (коэффициент вариации) – товары с самым устойчивым спросом.
- «Y» - 10-25% - товары с изменчивым объемом продаж.
- «Z» - от 25% - товары, имеющие случайный спрос.
Составим учебную таблицу для проведения XYZ-анализа.
1. Рассчитаем коэффициент вариации по каждой товарной группе. Формула расчета изменчивости объема продаж: =СТАНДОТКЛОНП(B3:H3)/СРЗНАЧ(B3:H3).
2. Классифицируем значения – определим товары в группы «X», «Y» или «Z». Воспользуемся встроенной функцией «ЕСЛИ»: =ЕСЛИ(I3<=10%;"X";ЕСЛИ(I3<=25%;"Y";"Z")).
В группу «Х» попали товары, которые имеют самый устойчивый спрос. Среднемесячный объем продаж отклоняется всего на 7% (товар1) и 9% (товар8). Если есть запасы этих позиций на складе, компании следует выложить продукцию на прилавок.
Запасы товаров из группы «Z» можно сократить. Или вообще перейти по этим наименованиям на предварительный заказ.
Источник: exceltable.com
