Анализ продаж в розничной торговле: топ-10 методов + 9 шаблонов

Содержание статьи

1. 2. 3. 4. 5. 6.

Казалось бы, бизнес успешен. Но — нет. Сергей живет не на свои деньги. Он пользуется чужими деньгами, то есть живет в долг. Для оценки эффективности своего бизнеса он использует показатели «Доходы» и «Расходы» и считает, что этого достаточно. Но если он будет работать по такой схеме дальше, то в лучшем случае бизнес будет стагнировать, в худшем — он обанкротится. А когда это случится, Сергей горестно воскликнет: «Ну что я делал не так?!» и уйдет из бизнеса.

Каждый год закрываются тысячи предприятий. Только за первые шесть месяцев в 2021 году перестали существовать 545,8 тыс. индивидуальных предприятий. Чаще бизнесы закрывались лишь в 2013 году.

С начала 2021 года количество субъектов малого бизнеса уменьшилось на 2359, о чем свидетельствуют данные на сайте ФНС:

Статистика по малому бизнесу на начало 2021 года

Статистика по малому бизнесу на 10.08.2021

Банкротство предприятий происходит по разным причинам. Иногда потому, что предприниматель не умеет правильно оценить рентабельность и эффективность работы, а значит, не успевает спрогнозировать падение бизнеса и принять меры по его оздоровлению (как наш герой Сергей). Рассказываем, какие показатели нужно оценивать, чтобы не пополнить ряды банкротов.

ABC-анализ – объемно-стоимостной анализ запасов материалов или товаров

Число групп при проведении АБЦ-анализа может быть любым, но наибольшее распространение получило выделение трех групп – A, B и C, что и дало название методу. Вся номенклатура запасов разбивается на эти три группы в зависимости:

от доли в общей стоимости запасов (если речь идет о материалах);
от доли в общем объеме реализации (если речь идет о товарах).

В группу A входят материалы с наибольшим удельным весом в стоимости материалов – около 70-80%, в группу B – 10-20%, в группу C – 1-5%.

Пример объемно-стоимостного анализа (ABC-анализа) материалов

Проведем объемно-стоимостной анализ материалов, используемых в производстве резиновой смеси в ООО “ЧРЗ”. Информация о потребности в разных видах сырья приведена в таблице.

В соответствии с принципом Парето материалы группы A подлежат более тщательному учету и более частой инвентаризации, для этих ресурсов обязателен расчет оптимальной партии заказа. Запасы группы B относятся к второстепенным и требуют меньшего внимания. Материалы группы C представлены широким ассортиментом оставшихся позиций, которые закупаются в небольших количествах и объем запаса которых контролируется менее точными и более дешевыми методами.Классификация материалов A, B, C представлены в таблице:

ABC-анализ позволяет сосредоточиться на контроле за наиболее важными видами товарно-материальных запасов и тем самым рациональнее использовать ресурсы, время и повысить эффективность управления запасами.

Что такое рентабельность продаж и зачем ее считать

Рентабельность продаж — это отношение прибыли к выручке. Она показывает, сколько прибыли получает компания с каждого рубля выручки. Если рентабельность за месяц равна 30%, то в одном рубле выручки 30 копеек прибыли.

Коэффициент рентабельности продаж помогает контролировать развитие бизнеса и вовремя принимать меры, если эффективность снижается. Рассмотрим на примере, как это работает.

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

	Январь	Февраль	Март
Выручка	454 545 ₽	714 285 ₽	1 250 000 ₽
Чистая прибыль	100 000 ₽	150 000 ₽	200 000 ₽

Кажется, что все хорошо. Добавим данные о рентабельности.

	Январь	Февраль	Март
Выручка	454 545 ₽	714 285 ₽	1 250 000 ₽
Чистая прибыль	100 000 ₽	150 000 ₽	200 000 ₽
Рентабельность по чистой прибыли	22%	21%	16%

Из таблицы видно, что рентабельность продаж падает. Получается, с каждым месяцем компания работает менее эффективно и теряет часть прибыли. Если бы в марте компания работала с такой же рентабельностью, как и в январе, то при той же выручке получила бы 275 000 ₽ чистой прибыли вместо 200 000 ₽.

Расчет рентабельности продаж помог обнаружить проблему. Теперь бизнес может понять, в каком направлении стоит работать.

Подробнее о видах анализа

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

Что такое ABC

Анализ ABC – это метод управления ресурсами предприятия на основе классификации исследуемых объектов по степени важности для бизнеса. Его используют для определения эффективности товарных запасов и инвестиций, выявления экономического потенциала клиентов и поставщиков

Аналитик ранжирует изучаемые объекты на основе различных параметров: объема затрат, прибыли, рисков

Затем группирует их по классам важности: A, B, C.. Метод ABC основан на принципе Парето, согласно которому 20% усилий обеспечивают 80% результата

Поэтому позиции класса A образуют 80% прибыли и требуют непрерывного наблюдения, т.к. в случае их утраты компания понесет большие убытки. К категории B относят объекты, формирующие следующие 10% результата и требующие средней степени контроля. Позиции класса С малоприбыльны и наименее весомы, поэтому за ними редко наблюдают

Метод ABC основан на принципе Парето, согласно которому 20% усилий обеспечивают 80% результата. Поэтому позиции класса A образуют 80% прибыли и требуют непрерывного наблюдения, т.к. в случае их утраты компания понесет большие убытки. К категории B относят объекты, формирующие следующие 10% результата и требующие средней степени контроля. Позиции класса С малоприбыльны и наименее весомы, поэтому за ними редко наблюдают.

Что такое XYZ

Анализ XYZ – это способ ранжирования товарно-материальных запасов на основе показателей спроса. Методика помогает определить, сколько товаров разместить на складе, чтобы они не залеживались и приносили предприятию прибыль.

Анализ XYZ позволяет определить количество товара на складе.

Аналитик распределяет товарные позиции по категориям X, Y, Z в зависимости от уровня предсказуемости спроса и его колебания (отклонения действительных показателей от прогнозных значений). Товары класса X покупают постоянно, их оборот легко спрогнозировать. Позиции из категории Y имеют цикличный, но предсказуемый спрос. А рассчитать вероятность покупательской активности в отношении товаров группы Z нельзя из-за отсутствия тенденций или невозможности установить причину спроса.

Расширенные версии анализа

Метод ABC помогает не только распределить значения по степени важности, но и оценить частоту заказов (сколько месяцев в году сохраняется спрос на товары). Например, дорогой товар попадает в категорию A при условии, что он пользуется спросом только в течение одного сезона

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

Например, дорогой товар попадает в категорию A при условии, что он пользуется спросом только в течение одного сезона. В этом случае бессмысленно делать регулярные закупки. Ранжирование по частотности заказов в рамках стандартного ABC-анализа позволит выявить этот нюанс, а в дальнейшем оптимизировать затраты.

Этап 8: Изучение поведения потребителей

Для разработки стратегии недостаточно знать общие характеристики типа возраста и дохода

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

Анализ потребителей проводится в нескольких направлениях:

мотивация, определение потребностей и целей;
ожидания касательно стандартов обслуживания и цен;
ключевые критерии (что хочет получить от товара/услуги в первую очередь);
причины недовольства и претензий (что не устраивает в продукте или сервисе);
процесс принятия решений (чем руководствуются — логикой или эмоциями, с кем советуются).

Понимание своей целевой аудитории — важный шаг к тому, чтобы построить сильный бренд, которому будут доверять.

ABC анализ в сводной таблице в Excel ассортимента по выручке

Для начала необходимо определиться с периодом анализа. Наиболее удачный вариант — взять в анализ 12 месяцев, чтобы учесть все сезонные колебания. Однако, иногда нужен анализ более короткого периода — например, в нашем примере для магазина садового инвентаря мы возьмем период 6 месяцев (наивысший спрос в дачный сезон).

Далее нужно подготовить исходные данные для анализа. Это может быть “сырая” база с транзакциями, выгруженная из учетной системы, или уже обработанная для анализа таблица.

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

наименование товара или наименование группы товара — в зависимости от того, до какой степени детализации нужно провести анализ. Если ваш ассортимент огромен, то, возможно, целесообразнее будет анализировать товары по группам, а не по конкретным SKU.
выручка по каждому товару или группе товаров.

Для анализа конкретно по выручке этих данных будет достаточно. Если хотите провести анализ по прибыли (или марже), то нужно иметь либо уже готовые данные по прибыли в разрезе товаров, либо издержки по каждому товару (себестоимость производства или стоимость закупки).

В нашем примере АВС-анализа таблица с исходными данными выглядит так.

В таблице содержится различная информация, но для анализа будем использовать только два столбца: Наименование товара и Стоимость.

1. Создадим сводную таблицу для АВС анализа

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

В поле Строки помещаем наименование товаров, а в поле Значения — сумму по полю Стоимость.

Кстати, при добавлении данных в поле Значения по умолчанию считается количество (в большинстве версий эксель). Чтобы количество превратить в сумму, щелкните на стрелочке и выберите Параметры полей значений, и в открывшемся окне нужно выбрать операцию Сумма.

Как создать сводную таблицу

Также желательно убрать пустую строку внизу таблицы, которая всегда по умолчанию создается в сводных таблицах. Для этого в фильтре столбца снимите “галочку” с пункта (пусто).

Мы получили список товаров и суммы выручки за каждый из них.

2. Получим доли каждого товара

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

Для этого добавим столбец Стоимость в поле Значения еще раз, просто перетянув его еще раз.

По умолчанию у нас посчиталось количество значений. Как в предыдущем пункте, превратим количество в сумму.

Получили два одинаковых столбца с суммами выручки. Теперь из второго столбца с выручкой нужно сделать доли от выручки по данному товару в общей выручке.

Для этого щелкните правой кнопкой мыши в любом месте второго столбца с суммой и выберите: Дополнительные вычисления — % от суммы по столбцу.

Получили доли выручки от каждого товара. Переименуем столбец с процентами, назовем его Доля, %.

3. Сортируем по убыванию доли выручки

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

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

Для этого щелкнем на фильтре столбца Названия строк (т.е. столбца с наименованиями товаров) и выберем Дополнительные параметры сортировки.

В окне сортировки нужно выбрать переключатель “по убыванию”, и в выпадающем списке выбрать столбец Доля, %.

Чтобы было понятнее, мы отсортировали столбец Наименование товара по убыванию значений в столбце Доля, %. Столбец с суммой выручки также отсортировался, его дополнительно сортировать не нужно.

На этом этапе уже видно, какие товары попали в группы А, В и С. Однако, это можно увидеть без расчетов лишь потому, что таблица в нашем примере маленькая. А что если у ней сотни или тысячи строк?

4. Получаем долю выручки нарастающим итогом

Как в пункте 2, снова добавляем поле стоимость в поле Значения, и вместо показателя Количество указываем Сумма. Сразу лучше переименовать поле (в примере — Доля нараст. итогом, %).

Теперь опять щелкаем правой кнопкой мыши на любом месте нового поля — Дополнительные вычисления — % от суммы с нарастающим итогом в поле — появляется окно Дополнительные вычисления — нажимаем Ок.

В поле Доля нарастающим итогом считаются доли выручки из предыдущего столбца нарастающим итогом, на картинке показан смысл:

Мы практически достигли цели провести ABC анализ в сводной таблице в Excel. Ведь нам нужно было узнать, какие товары дают примерно 80% выручки, какие — еще 15% (т.е. от 80 до 95%), а какие оставшиеся 5% (от 95 до 100%). И поле “Доля нараст. итогом, %” это показывает.

Возможности ABC-анализа

Сфера применения анализа АВС поистине беспредельна, и не стоит думать, что его возможности ограничены ранжированием по одному-двум показателям. Метод позволяет учитывать сразу несколько критериев – правда, в этом случае сам анализ и интерпретация результатов займут больше времени.

Например, сравните однородные ресурсы по разным параметрам и затем сопоставьте ранжированные списки друг с другом, проведя оптимизацию.

Вот примеры того, как это может работать:

Оцените ассортимент вашей фирмы по ряду показателей: прибыльность, объем продаж, площадь хранения товарных запасов. Может оказаться, что товар, обеспечивающий стабильные доходы, занимает слишком много места на складе, так что на общем фоне он утратит свою привлекательность – освобождение свободного места и переход на другие, более компактные, товары даст вам прирост прибыли.

АВС-анализ по складу позволит оптимизировать работу кладовщиков: если самые ходовые товары будут у них под рукой, то формирование заказов заметно ускорится.

Подталкивайте клиентов к покупке, представляя им выдержки из аналитических материалов. Опубликуйте топ-продаж, что может стать дополнительным мотиватором для покупателей, получивших подтверждение, что этот товар популярен и соответствует своей цене. Более того, попробуйте в обзоры популярных товаров добавлять и то, что давно лежит мертвым грузом в местах хранения

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

Точка безубыточности

Точку безубыточности также называют точкой рентабельности или нулевой точкой. Она отражает ситуацию, когда при работе бизнеса нет ни прибыли, ни убытка. Иными словами, он только обслуживает себя сам.

Точка безубыточности может быть:

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

Точка безубыточности считается следующим образом:

рассчитывается маржинальность = (Выручка — Переменные расходы) / Выручка * 100%
определяется точка безубыточности в рублях = Постоянные расходы / Маржинальность * 100%
рассчитывается точка безубыточности в единицах продукции = Точка безубыточности в рублях / Стоимость единицы продукции

Этап 6: Исследование основных каналов дистрибуции

Маркетологи, которые анализируют ситуацию на рынке и участвуют в разработке бизнес-планов, часто подчёркивают, что этот пункт — один из самых значимых.

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

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

Что нужно выяснить:

Какие торговые предприятия работают на определённой территории (специализированные и фирменные магазины, супермаркеты, киоски и т. д.), их соотношение.
Расположение, размер и уровень продаж основных магазинов.
Интенсивность дистрибуции — процент точек, в которых можно купить товары конкретного производителя в данном регионе.
Какой контингент чаще покупает в магазинах того или иного типа (место жительства, средняя сумма чека, процент постоянных клиентов).
Уровень густоты дистрибуции — отношение общего количества магазинов к плотности населения на исследуемой территории.

Ключевой показатель эффективности системы дистрибуции — уровень расходов. Он рассчитывается по формуле:

ВД = Т + Сп + С9 + Г

где: ВД — затраты на дистрибуцию, Т — расходы на транспорт, Сп — фиксированные складские расходы, С9 — переменные складские расходы, Г — сумма не выполненных в срок заказов (в денежном выражении).

Что такое рефид?

Рефид (от «refeed» – подпитка) – это углеводная загрузка организма во время низкоуглеводной диеты. Как и читмил, он является запланированным отступлением от привычного рациона, но проводится только для ускорения обмена веществ, а не психологической разгрузки.

Механизм действия рефида обусловлен изменением уровня гормона лептина. Этот гормон снижает чувство голода и влияет на активность метаболизма. Уменьшение его концентрации приводит к замедлению обмена веществ.

Синтез гормона сытости происходит в адипоцитах (жировых клетках). Во время диеты объем жирового депо уменьшается, что приводит к снижению уровня лептина и замедлению метаболизма. Это провоцирует усиление чувства голода и эффект «плато» (остановку похудения на фоне привыкания к ограничению калорий).

Кратковременная углеводная загрузка позволяет:

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

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

остановка веса на фоне сниженной калорийности рациона (диетическое «плато»);
резкое ухудшение спортивных показателей (количества повторений силовых упражнений, поднимаемый вес, выносливость при кардионагрузке);
снижение иммунитета, эндокринные нарушения, хроническая усталость и потеря концентрации внимания.

Если в рационе диеты отсутствует дефицит углеводов и калорий, то проведение рефида не даст существенного физиологического результата.

Уровень гормона сытости тесно связан с метаболизмом глюкозы. Это означает, что для рефида следует использовать только углеводные продукты со средним или высоким гликемическим индексом. К ним относятся:

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

Жирная пища, напротив, кратковременно (на 12-24 часа) снижает уровень лептина, поэтому употреблять фастфуд, мороженое, жареное мясо, молочный шоколад и другие кондитерские изделия нельзя.

Суточная калорийность рациона при рефиде чаще всего увеличивается всего на 20%. Избыточные калории спортсмены и худеющие получают только из углеводов. Например, при норме в 1500 ккал общая пищевая ценность меню повышается до 1800 ккал, а количество углеводов увеличивается на 75 г (4 ккал в 1 г), что соответствует 50-70% привычной нормы.

Некоторые спортсмены повышают содержание углеводов вдвое за счет снижения доли белков (на 10-15%) и жиров (на 25-35%).

Половину порции избыточных углеводов можно съесть до тренировки, а вторую половину – сразу после занятия. 37,5 г углеводов примерно соответствует небольшой порции овсяной каши (140-150 г) с несколькими кусочками кураги и 1 ч.л. меда.

Увеличивать интенсивность тренинга не нужно, т.к. в этом случае эффективность рефида снижается.

Отличия читмила и рефида:

Критерий	Читмил	Рефид
Продолжительность	1 прием пищи	От 12 часов до 2 суток
Основная цель	Сброс психологического напряжения	Повышение концентрации лептина и стимуляция жиросжигания
Рекомендуемая прибавка калорийности	+50%	+20-30% (реже – менее 20% или до 50%)
Разрешенная пища	Любые запрещенные продукты, кроме алкогольных напитков	Продукты, богатые углеводами (сладкие каши, цукаты, сладкие фрукты, мед, сухофрукты)
Как часто можно делать	Раз в 1-3 недели	По необходимости
Когда необходим	для снижения напряжения и при высоком риске срыва при стабильном дефиците калорий 300-500 ккал худеющим, регулярно посещающим спортивный зал и применяющим читмил в качестве выходного	при подготовке к соревнованиям после жесткой белковой или низкоуглеводной диеты в стадии «плато» при снижении показателей силы и выносливости при появлении хронической усталости, снижении концентрации внимания и других последствиях жесткой диеты

Что это такое?

Классификация экономических анализов позволяет выбрать понятный способ определить или посчитать стратегические показатели развития компании. Различные методы предназначены для того, чтобы собственники понимали, в каком направлении предпочтительно двигаться и проводить планирование деятельности.

Оценка методики

Один из наиболее известных методов, АБС анализ — это выделение из огромного количества объектов в бизнесе те, на которых необходимо максимально сосредоточить собственные силы и внимание, исходя из конкретных и приоритетных целей

Важно! АБЦ анализ, согласно указанному правилу Парето, можно определить, как надежный контроль двадцати процентов позиций, предоставляет возможность контроля системы на 80 процентов. Это могут быть запасы продукции и комплектующих, ассортимент товаров компании и прочие показатели

Это могут быть запасы продукции и комплектующих, ассортимент товаров компании и прочие показатели.

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

Выполнить ABC анализ — значит сделать ранжирование товарного склада или ассортимента на несколько параметров и характеристик. Также такое деление можно отнести и к поставщикам, клиентам, продолжительным периодом продаж, то есть ко всему, что предполагает наличие большого количество данных для статистики. В результате проведения можно разделить рассматриваемые объекты по степени влияния на итоговый результат.

Многомерный способ

Метод расчета основан на принципе дисбаланса, который характеризуется построением графика в зависимости от совокупного эффекта и количества исходных параметров. Представленный график имеет наименование кривая Лоренца, Парето или АBC.

В результате получается ранжирование и группировка групп товаров в зависимости от их значимости в общем деле.

Обратите внимание! Частое применение АВС анализ получил на примере логистики. Например, когда требуется отследить объем отгрузок конкретных позиций и частоту обращений к этой позиции

Также можно распределить покупателей согласно количества совершенных ими заказов

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

Достаточно распространенным можно назвать и аналогичный XYZ-анализ, который позволяет определить уровень стабильности и вариативности в сфере реализации продукции. С его помощью происходит группировка объектов деятельности согласно равномерности реализации, определяя колебания в разные периоды и распределяя объекты по их прогнозируемости. Отличие АBC от XYZ заключается в том, что первый помогает выделить наиболее продаваемую продукцию, а второй — выявить стабильность спроса на нее.

Выводы о методах

Бизнес факторы в методе подразделяются на три категории:

Категория А, которая состоит из 20% максимально ценных ресурсов, которые приносят результат в деятельности на уровне 80 %;
Категория В, которая составляет 30% ресурсов, приносящих 15% результатов;
Категория С равна 50 процентам, результативность которых приносит только пять процентов.

Сбор статистики по продажам и прибыли

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

Первым шагом мы собираем статистику продаж по каждой актуальной товарной позиции компании. Статистику продаж мы собираем за 2 периода: предшествующий и текущий год. Все артикулы мы разделили на товарные категории, по которым нам интересно посмотреть динамику.

Рис.1 Пример сбора статистики продаж по товарным позициям

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

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

Рис.2 Пример сбора статистики продаж по направлениям и регионам продаж

CPL — цена за лид

CPL (Cost Per Lead) показывает стоимость лида. Лидом, как правило, считаются контактные данные заинтересованного пользователя. На сайтах лиды обычно собираются через формы.

Кроме того, используется для оценки рекламы в формате Lead Ads во «ВКонтакте», Facebook и myTarget, он нацелен на сбор заявок от заинтересованных пользователей прямо в соцсетях.

Для какой рекламы используется: performance, медийная и видеореклама.

Где отображается: в статистике рекламных кабинетов соцсетей для формата Lead Ads. Чтобы оценить CPL других рекламных форматов в соцсетях, нужно настроить отслеживание заполнения форм в Google Analytics и Яндекс.Метрике, подключить коллтрекинг и собирать контакты в CRM-системе.

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

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

Примечание

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

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

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

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

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

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

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

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

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

С помощью формулы =ИНДЕКС($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%.