ABC-анализ с помощью надстройки MS EXCEL ABC Analysis Tool

Выполним ABC-анализ для определения ключевых клиентов и ранжирования номенклатуры товаров, используя надстройку MS EXCEL Fincontrollex® ABC Analysis Tool.

ABC-анализ (англ. ABC-analysis) – это метод классификации товаров, клиентов или ресурсов по уровню их значимости и влияния на заданный показатель деятельности компании (например, на выручку, затраты и пр.).

АВС-анализ является относительно простым, эффективным и поэтому популярным инструментом, который помогает финансовым аналитикам и менеджерам сфокусироваться на самом важном. Результатом АВС-анализа является классификация этих объектов по степени их влияния на определенный результат деятельности компании (на выручку, суммарные издержки на обслуживание клиентов, затраты на склад). При проведении АВС-анализа, как правило, выделяют 3 класса объектов: класс А – наиболее важные объекты, класс В – промежуточные (имеющие потенциал), С - наименее важные объекты, управление которыми не требует повышенного контроля.

Примечание: Название инструмента созвучно с методом учета затрат Activity Based Costing (Расчёт себестоимости по видам деятельности или Учет затрат по видам работ), но не имеет ничего общего с ним.

О методе АВС-анализа

Для тех, кто не знаком с методом АВС-анализа или хочет освежить в памяти его детали, рассмотрим применение этого метода на примере определения ключевых клиентов.

Задача: Ранжировать клиентов по степени их влияния на выручку компании:

  • Класс А - наиболее ценные клиенты, на которых приходится 80 % выручки (как правило, доля таких клиентов составляет около 20% от общего количества);
  • В Класс В попадает порядка 30 % клиентов, но они обеспечивают только около 15 % продаж;
  • Остальные клиенты (50%) попадают в Класс С наименее важных клиентов, суммарно обеспечивая лишь 5% продаж.

Примечание: В основе АВС-анализа лежит принцип Парето, который заключается в следующем: 20% усилий дают 80% результата, а остальные 80% усилий — лишь 20% результата. Часто под «усилиями» имеют в виду клиентов, товары, расходы (например, 20% самых крупных клиентов обеспечивают 80% выручки).

Как правило, при проведении АВС-анализа строят диаграмму Парето.

Примечание: Построить диаграмму Парето в MS EXCEL 2010 и более ранних версиях можно с помощью надстройки Пакет анализа или настроив соответствующим образом стандартную диаграмму типа Гистограмма с группировкой.

Предложенная классификация клиентов основана на некоторых допущениях, связанных с распределением выручки по клиентам. Прежде чем их озвучить отметим следующие моменты:

  • Произвольность установления границ классов. Например, в класс А попадают клиенты, обеспечивающие 80% выручки. Почему не 70% или 90%?
  • Если компания небольшая или только развивается, то клиентов может быть всего один-два десятка. Имеет ли смысл их классифицировать?
  • Если все клиенты приносят примерно одинаковую выручку (например, отличие составляет +/- 10%), то не повредит ли компании отнесение 50% клиентов в категорию «наименее важные»?
  • В этом примере клиенты ранжируются по выручке, но возможно имеет смысл их также группировать по затратам, которые несет компания на их обслуживание? Другими словами, может потребоваться классификация сразу по нескольким параметрам.
  • Как правило, АВС-анализ проводят за определенный период, например за год. Вполне может сложиться такая ситуация, что один из клиентов ранее (более года назад) обеспечивал значительную долю выручку компании (пусть 30%), но в связи со своим инвестиционным циклом за последний период практически не приобретал товары компании. Однако, известно, что этот клиент планирует в следующем году начать пользоваться продукцией компании в прежнем объеме. При формальном подходе, АВС-анализ может классифицировать данного клиента в класс С, хотя, очевидно, что внимание менеджеров должно быть по-прежнему сосредоточено на нем.

Из вышесказанного можно сделать несколько выводов, которые помогут менеджеру применять ABC-анализ более эффективно:

  • Ответственность за корректность исходных данных для ABC-анализа и постановку задачи лежит на сотруднике, проводящем анализ. Другими словами, некорректные исходные данные могут привести только некорректным выводам.
  • Значения границ классов должны быть обоснованы (о различных методах определения границ классов читайте далее в этой статье).
  • Распределение данных должно иметь определенную форму, пригодную для ABC-анализа.

Последний пункт требует пояснения.

Предположим в компании несколько десятков клиентов, причем выручка по клиентам распределена примерно равномерно.

То есть, доли клиентов, приносящих небольшую и, напротив, более значительную прибыль примерно одинаковы (16% клиентов приносят по 23 млн. руб. каждый, а 10% - по 50 млн. руб.). Разница в суммарной выручке этих классов незначительная (если клиентов 1000, то суммарная выручка одного класса равна 3680 млн.руб., а другого 5000, т.е. значения достаточно близки). Это означает лишь одно: если выручка по клиентам распределена примерно равномерно – то распределять клиентов по классам бессмысленно. Соответствующая диаграмма Парето имеет следующий вид (форма линии близка к прямой).

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

Такая диаграмма получится, если распределение имеет существенно скошенную форму.

Такое распределение соответствует случаю, когда большинство клиентов (порядка 80%) вносит вклад лишь примерно в 20% выручки. И лишь отдельные клиенты-звезды вносят вклад в выручку, существенно перекрывая суммарный вклад остальных клиентов.

Вывод: Перед применением метода ABC-анализа исследуйте распределение исследуемого показателя (в данном случае выручки) по объектам (клиентам).

Теперь перейдем к вычислениям. Сначала проведем АВС – анализ стандартными средствами MS EXCEL, затем с использованием надстройки MS EXCEL Fincontrollex® ABC Analysis Tool.

АВС – анализ стандартными средствами MS EXCEL

В качестве примера возьмем компанию, занимающуюся продажей товаров с широким ассортиментом (около 4 тыс. наименований). В качестве исходных данных возьмем объемы продаж по каждой позиции (цена * количество) за определенный период.

Примечание: АВС-анализ также можно проводить для определения ключевых клиентов, оптимизации складских заказов и бюджетных расходов компании. 

Прежде чем, приступить к расчетам ответим на несколько вопросов, которые помогут нам эффективно использовать АВС – анализ.

  1. Какова цель анализа? Увеличить выручку компании.
  2. Какие действия по итогам анализа будут предприняты? Обеспечить обязательное наличие на складе товаров, вносящих в выручку основной вклад (для исключения потерь выручки).
  3. Что является объектом анализа и параметром анализа? Объект анализа - перечень товаров, которые вносят наибольший вклад в выручку (выручка - параметр анализа).

Алгоритм выполнения АВС – анализа:

  • Сортируем список товаров по убыванию их вклада в выручку.
  • Формируем столбец с выручкой накопительным итогом (для каждой позиции товара складываем его выручку со всеми выручками от предыдущих, более прибыльных товаров).
  • Определяем долю выручки для каждого товара накопительным итогом (значения столбца, рассчитанного выше, делим на общую выручку всех товаров). По этому столбцу будем определять границы классов.
  • Определяем границы классов в долях от выручки. В данном случае используем стандартные значения долей (в %): 80%, 15% и 5%. Т.е. группа наиболее прибыльных товаров должна вносить суммарный вклад в выручку в размере 80%. Все товары, у которых доля выручки накопительным итогом менее или равна 80%, входят в класс А.
  • Выделяем классы А, В и С: присваиваем значения классов соответствующим товарам.

Теперь реализуем этот алгоритм на листе MS EXCEL (см. файл примера, лист АВС формулами).

Отсортировать список товаров можно с помощью функции РАНГ() – каждому товару будет присвоен ранг в зависимости от его вклада в выручку. Товару, обеспечивающему максимальную выручку, будет присвоен ранг = 1.

С помощью формулы =СУММЕСЛИ($H$7:$H$4699;"<="&$B7;$F$7) формируем столбец с выручкой накопительным итогом. У товара, обеспечивающего максимальную выручку (первый в списке), значение выручки накопительным итогом будет совпадать с его выручкой. У второго товара выручка накопительным итогом будет равна его собственной выручке + выручка первого товара, и т.д.

Затем вычислим для каждого товара долю в общей выручке накопительным итогом.

С помощью формулы =ИНДЕКС($N$7:$N$9;ПОИСКПОЗ(J7;$P$7:$P$9;1)) присвоим названия классов каждому товару:

  • товары, у которых доля выручки накопительным итогом менее или равна 80%, входят в класс А;
  • товары, у которых доля выручки накопительным итогом более 80% и менее 95% (80%+15%), входят в класс В;
  • остальные товары принадлежат классу С.

Для наглядности товары, принадлежащие классу А, можно выделить Условным форматированием, а также построить диаграмму Парето (по оси Х указывается количество проданного товара, по оси Y - % выручки накопительным итогом).

Примечание: Границы классов выделены на диаграмме бордовыми линиями (технически это сделано с помощью горизонтальных и вертикальных планок погрешностей).

Можно также рассчитать сколько позиций товаров входит в каждый класс. Так в класс А входит 342 товара. В класс А входят товары, которые обеспечивают 79,96% выручки (максимальный % меньше 80%). Общая сумма выручки, приходящаяся на эти товары равна 2 116 687,3 руб. Максимальная выручка (у первого товара в классе) равна 76 631,1 руб., а минимальная 1 574,0 руб. (у последнего товара в классе). Часть информации можно найти в таблице в строках расположенных на границах классов (строки 348 и 349).

Как видно из примера, вышеуказанные вычисления, относятся довольно трудоемкими. Есть ли возможность ускорить выполнение АВС-анализа? Безусловно, есть, и одним из решений является надстройка ABC Analysis Tool от компании fincontrollex.com. Ниже рассмотрим ее подробнее.

Примечание: АВС-анализ относится к числу стандартных и часто используемых инструментов, поэтому он доступен во многих популярных программах бухгалтерского и управленческого учета. Например, в программе «1С: Управление торговлей» (версия 10) существует возможность для проведения анализа клиентов и номенклатуры товаров по следующим параметрам: сумма выручки, сумма валовой прибыли, количество товаров. Причем границы классов не вычисляются, а задаются произвольно, по умолчанию используются значения 80%, 15%, 5%.

Установка надстройки ABC Analysis Tool

Скачать надстройку можно с сайта ]]>http://fincontrollex.com]]>, выбрав ее в меню Продукты или соответствующую иконку на главной странице сайта.

На сайте также можно найти вводную статью о надстройке (]]>http://fincontrollex.com/?page=products&id=4&lang=ru]]>) и подробную справку (]]>http://help.fincontrollex.com/ABC_Analysis_Tool/1/ru/]]>).

На странице продукта нажмите кнопку «Скачать бесплатно». Надстройка будет скачана на компьютер в формате архива zip. В архиве содержится 2 файла надстройки *.xll: x64 – для 64 и x86 – для 32 – разрядной версии MS EXCEL. Чтобы узнать версию Вашего MS EXCEL в меню Файл выберите пункт Справка.

Запустите файл надстройки. После установки в MS EXCEL появится новая вкладка fincontrollex.com (если ранее у Вас были установлены другие надстройки от fincontrollex.com, то меню надстройки также будет добавлено на эту вкладку).

Нажмите кнопку Активировать, затем в появившемся окне выберите «Получить бесплатно лицензионный ключ для активации пробной версии».

Будет открыта страница бесплатной активации сайта fincontrollex.com. После ввода Вашего адреса электронной почты, Вам через несколько минут будет прислан ключ активации.

Ключ активации нужно скопировать в буфер обмена (CTRL+C) и, нажав поле «Активация продукта»,

вставить ключ в соответствующее поле (CTRL+V):

Нажмите кнопку Активировать. Установка надстройки завершена.

АВС – анализ с помощью надстройки ABC Analysis Tool

Для тестирования надстройки используем тот же пример, который мы рассматривали при выполнении АВС-анализа формулами и стандартными средствами MS EXCEL. Это нам позволит, во-первых сравнить трудозатраты на выполнение расчетов и построение диаграммы, а во-вторых проверить корректность работы самой надстройки.

Вызовем диалоговое окно надстройки, чтобы ввести необходимые параметры. Для этого на вкладке fincontrollex.com в группе ABC Analysis Tool нажмите кнопку «Анализ». Появится диалоговое окно надстройки.

Верхнее поле Наименование служит для ввода ссылки на диапазон ячеек с наименованием объектов исследования, т.е. в нашем случае – названий товаров. Нажмем кнопку рядом с полем Наименование. Диалоговое окно надстройки исчезнет и появится окно для ввода ссылки на ячейки. Вводить в ручную адреса ячеек мы не будем, а выделим курсором мыши нужный диапазон $C$7:$C$4699. Нажмем Ок.

Примечание: Если диапазон ячеек очень большой (наш случай), то для ввода ссылки кликните мышкой на самую верхнюю ячейку диапазона, затем нажмите комбинацию клавиш CRTL+SHIFT+Стрелка вниз – будет выделен весь диапазон наименований (предполагается, что диапазон не содержит пустых ячеек).

Поле Значение служит для ввода ссылки на диапазон ячеек со значениями выручки (показатель, по которому будут определяться классы товаров). Введем ссылку на диапазон $F$7:$F$4699.

Примечание: Если размеры диапазонов не совпадают, то надстройка выведет соответствующее предупреждение. Делаем вывод, что программа имеет защиту «от дурака», что, безусловно, является большим плюсом надстройки.

Нажав, кнопку ОК, сразу же будут выполнены все вычисления и в диалоговом окне будет построена диаграмма (2-3 секунды для таблицы с 4000 позициями на моем компьютере).

Форма диаграммы Парето с выделенными на ней классами, построенная в диалоговом окне надстройки, позволяет пользователю быстро оценить корректность применения АВС-анализа к имеющемуся массиву данных. Как было сказано выше, если выручка распределена по товарам примерно равномерно, то выделять различные классы некорректно. Убедиться в этом можно взглянув на соответствующую диаграмму.

Как видно из диаграммы, самые прибыльные товары (товары Класса А) вносят существенный вклад в общую выручку ≈74%, но составляют уже 45% от общего количества. Классы В и С вносят примерно одинаковый вклад в выручку, а количество товаров отличается только в 2 раза. Причем средний вклад товаров класса А лишь в 4 раза больше, чем вклад товаров Класса С. В классическом случае диаграммы Парето (20% усилий - 80% результата) средние вклады этих классов отличаются в 40-50 раз! Именно это обстоятельство и позволяет выделить немногочисленный Класс А, чтобы сфокусировать внимание менеджера на основных товарах.

Надстройка ABC Analysis Tool позволяет быстро определить целесообразность выделения классов товаров на основании построенной диаграммы. Безусловно, это очень удобно и является преимуществом надстройки. Возможно, разработчикам стоит включить в надстройку некий индикатор, который бы информировал пользователя о корректности применения АВС-анализа для его данных.

По умолчанию, надстройка определяет границы классов (групп товаров) методом касательных, который является наиболее гибким среди десятков других. Суть метода заключается в определении границ групп по точкам изгиба кривой Парето. Этот метод позволяет точно определить границы группы на основании изменения скорости роста суммы и количества показателей.

Ранее в наших вычислениях мы использовали метод для определения границ групп с использованием классического соотношения: 80%; 15%; 5%. Поэтому, для сравнения результатов вычислений нам потребуется переключить надстройку в ручной режим. Для того, чтобы сделать это, раскроем ]]>список переключения режимов]]> в верхней части таблицы и выберем пункт «Вручную».

После перехода в ручной режим в столбце Показатель,% станут доступны для изменения соответствующие ячейки. Для того чтобы изменить границы группы (классов), выделим соответствующую ячейку группы и нажмем клавишу клавиатуры F2 или сделаем двойной щелчок левой кнопкой мыши.

Введем следующие значения: для класса А – 80%; для класса В – 15% и для класса С – 5%. После внесения изменений, значения в остальных столбцах таблицы будут сразу же пересчитаны и изменения отобразятся на диаграмме Парето.

Теперь нажмем кнопку Выполнить. Будет создана новая книга MS EXCEL с двумя листами Свод и Подробно.

На листе Свод будет отображена диаграмма и таблица, которые мы видели в диалоговом окне.

На листе Подробно приведены промежуточные вычисления: отсортированный список товаров, соответствующие значения выручки, выручка накопительным итогом в % (отношение выручки товара к суммарной выручке, выраженное в процентах) и номер позиции, выраженный в % (номер позиции товара, деленный на общее число товаров). Каждый товар отнесет к определенной группе (А, В или С).

Разберем подробнее информацию, содержащуюся на листе Свод.

Как видно из таблицы, в группу А входят товары, которые обеспечивают 80,02% выручки. Число этих товаров 343, что составляет 7,31% от общего числа товаров (=343/4693=7,31%). Общая сумма выручки, приходящаяся на эти товары равна 2 118 256,7 руб. Максимальная выручка (у первого товара в группе) равна 76 631,1 руб., а минимальная 1 569,4 руб. (у последнего товара в группе).

Если сравнить работу Надстройки с вычислениями произведенными нами выше с помощью стандартных средств MS EXCEL, то разница будет состоять в том, что к группе А отнесено не 342 товара, а 343. Разница не принципиальная и объяснимая. К группе А мы относили товары суммарная доля в выручке которых НЕ БОЛЕЕ 80% (у нас получилось 79,96%). Надстройка ABC Analysis Tool делает более точные вычисления: определяет границу по товару, у которого суммарная доля в выручке БЛИЖЕ ВСЕГО к 80% (безусловно 80,02% ближе к 80%, чем 79,96%). Так как в группу включен дополнительной товар, то и все значения отличаются на величину его выручки.

Теперь вернемся к автоматическому режиму методом касательных. В этом случае границы групп определены из соотношения 90,08%; 7,87% и 2,05%. В группу А теперь включено значительно больше товаров 617 против 343. Группы А и В значительно расширились за счет товаров группы С.

Примечание: К сожалению, при повторном вызове окна надстройки поля Наименование и Значения не сохраняют ранее введенные ссылки на диапазоны. Необходимо их указывать заново.

Трудно определить какой вариант расчета предпочтительней. Все зависит от конкретной бизнес-ситуации. Наверное, поэтому методов выделения групп существует порядка десяти. Наиболее часто используемыми методами являются: эмпирический метод, метод суммы и метод касательных. В эмпирическом методе разделение классов происходит в классической пропорции 80%; 15%; 5%. В методе суммы складывается доля объектов и их совокупная доля в результате — таким образом значение суммы находится в диапазоне от 0 до 200 %. Группы выделяют так: группа А - 100 %, В - 45 %, С - остальное. Однако, самым гибким методом является метод касательных, в котором к кривой АВС проводится касательная, отделяя сначала группу А, а затем С. К сожалению, реализовать его можно только с помощью отдельной программы (макроса), что собственно и делает надстройка ABC Analysis Tool.

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

Разделение и объединение групп хорошо объяснено в справке к надстройке, которая содержит анимированные gif для визуализации действия пользователя.

Подведем итоги

Надстройка ABC Analysis Tool всего за несколько секунд позволяет выполнить АВС-анализ. При этом обладает следующей функциональностью:

  • анализ двумя самыми популярными методами: эмпирическим и методом касательных;
  • автоматическое построение диаграммы Парето с разделением групп;
  • классификация на произвольное количество групп;
  • в отдельном файле формируется подробная и сводная таблица с результатами вычислений.

Выводы

  1. Грамотное использование АВС-анализа может быть использовано для повышения эффективности компании. Это достигается путем концентрации работы над наиболее важными элементами и за счет экономии ресурсов на менее приоритетных направлениях.
  2. Надстройка ABC Analysis Tool от ]]>fincontrollex.com]]> является быстрым и удобным средством выполнения АВС-анализа, что сэкономит Вам много времени и нервных клеток, а также поможет избежать возможных ошибок вычислений;
  3. Корректность работы надстройки в ручном режиме (эмпирический метод) проверена и не вызывает вопросов;
  4. Отдельно хочется отметить справку к надстройке: анимированные gif и подробные пояснения делают освоение настройки очень простым и быстрым; 
  5. Сайт ]]>www.excel2.ru]]> рекомендует финансовым аналитикам и менеджерам использовать надстройку ABC Analysis Tool от Fincontrollex.com поскольку она существенно облегчает выполнение АВС-анализа по сравнению с использованием встроенных средств MS EXCEL.

 


Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (2 оценок)