Как сделать в excel анализ продаж?

Шаблон для анализа продаж и прибыли в компании

Анализ продаж и прибыли компании является одним из важных аспектов деятельности специалиста по маркетингу. Имея под рукой правильно составленный отчет по продажам, вам намного проще будет разрабатывать маркетинговую стратегию развития компании, а ответ на вопрос руководства «Каковы основные причины снижения продаж?» не будет занимать много времени.

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

Вводные моменты по анализу продаж

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

Основные моменты, на которые необходимо обращать внимание при проведении анализе продаж:

  • Динамика продаж по товарам и направлениям, составляющим 80% продаж компании
  • Динамика продаж и прибыли по отношению к аналогичному периоду прошлого года
  • Изменение цены, себестоимости и рентабельности продаж по отдельным позициям, группам товаров
  • Качество роста: динамика продаж в расчете на 1 РТ, в расчете на 1 клиента

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

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

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


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

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

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

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


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

Процесс анализа продаж

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

Анализ выполнения плана продаж

Если в компании ведется планирование и установлен план продаж, то первым шагом рекомендуем оценить выполнение плана продаж по товарным группам и проанализировать качество роста продаж (динамику отгрузок по отношению к аналогичному периоду прошлого года).


Рис.3 Пример анализа выполнения плана продаж по товарным группам

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

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

Анализ динамики продаж по направлениям

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


Рис.4 Пример анализа продаж по направлениям

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

Анализ структуры продаж

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


Рис.5 Пример анализа структуры продаж ассортимента компании

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


Рис.6 Пример анализа себестоимости и рентабельности продаж

АВС анализ

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


Рис.7 Пример АВС анализа ассортимента

АВС анализ проводится в разрезе продаж и прибыли 1 раз в квартал.

Контроль остатков

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


Рис.8 Пример анализа остатков продукции

Отчет по продажам

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


Рис.9 Еженедельный отчет о продажах

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

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


Рис.10 Ежемесячный отчет о продажах

Скачать представленный в статье шаблон для анализа продаж вы можете в разделе «Готовые шаблоны по маркетингу».

ABC и XYZ анализ

В статье мы расскажем вам что такое ABC анализ и XYZ анализ, его применение в бизнесе, как можно их провести при помощи офисной программы Ms Excel.

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

ABC анализ

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

В основе ABC анализа лежит идея Парето. Он утверждал, что вкладывая 20 % вы можете получить 80. На это стоит обратить внимание и двигаться в правильном направлении.

При использовании такого анализа анализируемые объекты условно делятся на 3 вида:

  1. A — самые важные элементы, которые составляют 20% от всех и от которых вы получаете 80% результата.
  2. B – средние элементы. К ним мы относим 30% от общей массы, и они могут принести вам 15% прибыли.
  3. С — остальные элементы. Оставшиеся 50%, которые приносят только 5%.

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

Вывод ABC анализа — разделение анализируемых элементов на группы по степени влияния на работу бизнеса.

Цель ABC анализа — разделение объектов по приносимым ими результатов.

Метод XYZ анализа

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

ABC анализ помогает определить товары, которые приносят максимальную прибыль, а XYZ анализ даст вам информацию об их спросе.

Применение ABC XYZ анализ

Специалисты используют ABC XYZ анализ для анализирования прибыли, просматривая различные факторы, которые влияют на нее.

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

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

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

Анализируя клиентскую базу данных с помощью ABC анализа, вы можете условно разделить всех клиентов на 3 группы: A, B, C. То есть на больших, средних и малых соответственно.

Читать еще:  Как сделать функцию вычитания в excel?

При этом нет единого правила, по которому можно произвести условное разделение. Все зависит от самого бизнеса и его объемов продаж. Так например для малого бизнеса к крупным клиентам можно отнести тех, которые приносят вашей компании примерно 150 тыс. рублей. При этом для крупных компаний с такой суммой клиент отнесется к категории C, а в категории А будет список клиентов, которые приносят компании миллионы.

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

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

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

ABC анализ помогает вам увидеть сколько клиентов приобрели у вас товар, каким образом они узнали о вашей продукции и компании, кто помог им совершить покупку из ваших сотрудников и т. п.

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

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

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

С теми клиентами, которые приобретают ваши товары или услуги, но редко, вы должны поддерживать постоянные контакты. Это необходимо, так как вы можете потерять их.

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

Как сделать ABC анализ

Чтобы провести ABC анализ необходимо выполнить ряд шагов. Он проводится в несколько этапов, которые необходимо выполнять друг за другом.

Перечислим этапы, которые вам необходимо сделать:

  • у анализа должна быть цель, поставьте ее;
  • выберите объекты исследования;
  • выберите показатель, по которому будете делить выбранные объекты;
  • дайте оценку всем объектам по выбранному показателю;
  • рассортируйте объекты по убыванию значения показателя;
  • выявите долю значения показателя каждого объекта;
  • распределите объекты по долям значений показателя нарастающим итогом;
  • разбейте все объекты на три группы: А, B, C. К группе А относятся те, у которых нарастающий итог от 0 до 80%, у кого 80-95% — это клиенты группы В, свыше 95% группа С;
  • вычислите число клиентов, относящихся к каждой группе.

Пример ABC анализа в Excel

Приведем пример ABC анализа. Для его проведения используем офисную программу MS Excel.

Например, вам необходимо проанализировать ваших клиентов относительно прибыли которую они вам приносят.

  1. Создадим таблицу в Excel с двумя столбцами: наименование клиента и выручка в руб. Возьмем 15 клиентов.
  2. Отсортируем клиентов по убыванию выручки. Для этого необходимо выбрать вкладку Данные — Сортировка.
  3. Теперь нам необходимо вычислить долю от общей прибыли. Для этого пропишем формулу в ячейке С3 = B2/$B$17 и для ячеек этого столбца выберем процентный формат ячеек.
  4. Следующим шагом рассчитаем накопительную долю для каждого клиента. Для первого клиента накопительная доля будет равна доле, для последующих же она будет вычисляться как сумма его доли и предыдущего клиента. То есть для Клиента 8 в ячейке D3 будет прописана формула = С3+D2. Протянем эту формулу на оставшиеся ячейки. Таким образом у вас получится список клиентов выстроенных по возрастанию процентного соотношения.
  5. Теперь вы с легкостью можете распределить данный список на категории A, B и С.
  6. Далее для каждой группы клиентов определим долю от общей выручки и процент от общего числа клиентов.

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

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

Таким образом у нас получилось, что 33% наших клиентов принося нам максимум прибыли более 80%, 27% клиентов приносят нам 15% выручки и 40% приносят нам 6% выручки.

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

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

Как сделать XYZ анализ

Проведение данного вида анализа, как и предыдущего, можно разделить на этапы:

  1. необходимо выбрать элементы и показатели для анализа,
  2. выбрать временной промежуток, которое вы хотите проанализировать,
  3. вычислить коэффициент вариации каждого анализируемого элемента,
  4. распределить элементы в зависимости от показателя вариации,
  5. разделить элементы на 3 группы: X, Y, Z.

К категории X относятся элементы с показателями вариации 0-10%. Таких клиентов можно охарактеризовать как устойчивых.

Категория Y — показатели вариации 10-25%. Эти клиенты изменчивы, но их поведение можно спрогнозировать.

Категория Z — вариация от 25% и выше. Сюда относятся случайные клиенты, которые приобрели товар один раз по потребности.

Данные для XYZ анализа должны быть статистического характера. Приведем пример XYZ анализа. Для этого воспользуемся офисной программой MS Excel.

XYZ анализ в Excel

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

Далее рассчитаем по каждому клиенту средние продажи за месяц и стандартное отклонение. Для вычисления среднего значения применим формулу в ячейке I3 =СРЗНАЧ(B3:G3).

Для того, чтобы найти стандартное отклонение воспользуемся формулой J3 = СТАНДОТКЛОН(B3:G3). Затем протяните формулы на оставшиеся ячейки.

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

Введем в ячейку K3 формулу = СТАНДОТКЛОНП(B3:G3)/СРЗНАЧ(B3:G3). Протянем формулу и выберем процентный формат ячеек.

Теперь вам становится видна вариация по каждому клиенту. Разделите их на группы X, Y, Z по ранее указанным критериям.

К группе X относятся клиенты с вариативностью от 0 до 10%, к категории Y от 10 до 25%, к категории Z от 25% и выше. Если большой список клиентов для анализа, то можно изначально отсортировать таблицу по возрастанию и указать категории.

Так как мы проводим анализ на примере 15 клиентов, то пропишем значения категорий вручную. У вас получится примерно так.

Из анализа видно, что у компании из рассмотренных 15 клиентов 8 характеризуются постоянным спросом и покупками товаров, их поведение можно легко спрогнозировать. К категории Y с колеблющимся спросом относятся 2 клиента. И к категории Z относится 5 клиентов. Это те люди, поведение которых сложно спрогнозировать. Их покупки скорее всего были вызваны срочными потребностями и носили единоразовый характер.

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

Мы проанализировали клиентов по прибыльности, которую они приносят компании. Точно также вы можете произвести XYZ анализ ассортимента.

Использование ABC XYZ анализа

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

В таком случае создается единая таблица, содержащая данные по ABC анализу продаж и XYZ анализу, и каждому объекту исследования присваивается одна из 9 категорий:

  • AX — означает наивысшую потребительскую стоимость, стабильное приобретение товаров и хорошую прогнозируемость,
  • AY — высокая потребительская стоимость, но нестабильное приобретение и средняя степень прогнозирования,
  • AZ — высокая потребительская стоимость, спонтанные покупки и невозможно спрогнозировать,
  • BX — средняя потребительская стоимость, стабильное приобретение товаров и хорошая прогнозируемость,
  • BY — средняя потребительская стоимость, но нестабильное приобретение и средняя степень прогнозирования,
  • BZ — средняя потребительская стоимость, спонтанные покупки и невозможно спрогнозировать,
  • CX — низкая потребительская стоимость, стабильное приобретение товаров и хорошая прогнозируемость,
  • CY — низкая потребительская стоимость, но нестабильное приобретение и средняя степень прогнозирования,
  • CZ — низкая потребительская стоимость, спонтанные покупки и невозможно спрогнозировать.
Читать еще:  Как сделать поиск в excel 2013?

ABC XYZ анализ Excel

Приведем пример ABC XYZ анализа. Возьмем ранее рассмотренный пример и создадим в табличном процессоре новую таблицу с указанием категорий с ABC анализа и XYZ анализа. В отдельный столбец поместим категории совмещенные.

Рядом с совмещенной таблице создадим новую и распределим клиентов по необходимым категориям.

Сделаем вывод по последней таблице. Самыми прибыльными для компании являются клиенты, которые стоят на позициях AX. Кроме прибыльности, они производят покупки стабильно, что позволяет сделать прогноз. К такой категории относятся клиенты с номерами 8, 12, 10, 11.

Стоит обратить свое внимание на клиентов под номера 15 и 13. Поработать также можно с клиентами 1, 6, 5, 7. Остальные клиенты не принесут вам много прибыли. Вы можете даже не затрачивать на них время.

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

ABC анализ продаж. Пример расчета в Excel

ABC анализ продаж. Определение

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

Данный вид анализа основывается на закономерности выявленной экономистом Парето: «20% продукции обеспечивают, 80% прибылей компании». Целью компании при проведении такого анализа является определение ключевых товаров, и управление данной 20% группой, которое создаст контроль над 80% денежными поступлениями. Управление продажами и денежными платежами напрямую влияют на финансовую устойчивость и платежеспособность компании.

При проведении анализа продукции все товары делятся на три группы:

  • Группа «А» – максимально ценные товары, занимают 20% ассортимента продукции, и приносят 80% прибыли от продаж;
  • Группа «В» – малоценные товары, занимают 30% ассортимента продукции, и обеспечивают 15% продаж;
  • Группа «С» – не востребованные товары, занимают 50% ассортимента, и обеспечивают 5% прибылей от продаж.

Товары компании группы «А» являются целевыми, и требуют максимального внимания к их производству и реализации: наличия их в складских запасов, оперативные поставки, планирование и организация производства и контроль качества данной продукции.

ABC анализ продаж продукции. Этапы проведения

Этапы проведения ABC-анализа номенклатуры товаров и объема продаж компании (предприятия) следующие:

  1. Определение номенклатуры продукции предприятия.
  2. Расчет нормы прибыли по каждой товарной группе.
  3. Определение эффективности каждой группы.
  4. Ранжирование товаров и их классификация (ABC) по ценности для предприятия.

Пример ABC анализа продаж продукции в Excel

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

Номенклатура продукции для проведения ABC-анализа в Excel

Далее необходимо осуществить сортировку товаров по прибыльности. Заходим в главном меню Excel → «Данные» → «Сортировка». Результатом будет сортировка групп товаров по рентабельности от самого прибыльного до самого убыточного.

На следующем этапе необходимо определить долю по каждому виду товара. Для этого воспользуемся формулами в Excel.

Доля продаж каждого вида товара =B5/СУММ($B$5:$B$15)

Определение доли продукции в объеме продаж компании

На следующем этапе рассчитывается доля групп накопительным итогом по формуле:

Доля товара в номенклатуре накопительным итогом =C6+D5

Оценка доли прибыли накопительным итогом для группы товаров

После этого необходимо определить границу до 80% для группы товаров «А», 80-95% для группы товаров «В» и 95-100% для товаров «С». На рисунке ниже представлен результат группировки товаров по трем группам для магазина сотовых телефонов. Так марки Samsung, Nokia, Fly и LG дают 80% всех продаж, Alcatel, HTC, Lenovo обеспечивают 15% продаж и Philips, Sony, Apple, ASUS приносят 5% выручки от реализации.

ABC анализ продаж продукции. Пример расчета в Excel

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

30% всех товаров приносят компании 80% прибыли.

Преимущества ABC-анализа

Данный метод хорошо себя зарекомендовал на практике, и имеет следующие преимущества:

  1. Простота использованияи скорость проведения анализа для повышения эффективности продаж. Методику ABC-анализа можно использовать на любом предприятии, так как она не требует больших вычислительных мощностей и баз данных. Все расчеты по номенклатуре товара могут быть произведены в таблице в Excel.
  2. Надежность результатов. Полученные результаты устойчивы во времени и позволяют предприятию сфокусировать свои ресурсы и капитал в развитие наиболее перспективных продуктов. Управление номенклатурой наиболее ценных товаров позволяет создать финансовую устойчивость предприятия.
  3. Оптимизация ресурсов и времени. Использование методики позволяет высвободить дополнительные ресурсы как финансовые, так и временные.
  4. Универсальность анализа. Возможность применения методики ABC-анализа для других сфер деятельности предприятия.

Другие направления использования ABC-анализа в компании

Спектр использования данного метода повышения эффективности в хозяйственных системах крайне широк:

  • Оптимизация номенклатуры товаров.
  • Выделение ключевых поставщиков, подрядчиков, клиентов.
  • Повышение эффективности организации складских запасов.
  • Оптимизация производственного процесса.
  • Бюджетирование и управление маркетинговыми затратами.

Недостатки ABC-анализа

Помимо преимуществ методики ей присущи также и недостатки:

  1. Одномерность метода. ABC-анализ довольно простой аналитический метод и не позволяет группировать сложные многомерные объекты.
  2. Группировка товаров только на основе количественных показателей. Метод не строится только на количественной оценке нормы дохода по каждой номенклатуре товара и не оценивает качественную составляющую каждого товара, например, товары различной категории.
  3. Отсутствие группы убыточных товаров. Помимо товаров приносящих прибыль компании есть также товары, которые приносят убыток. В данном методе такие товары не отражены, в результате на практике, ABC-анализ трансформируется в ABCD анализ, где в группу “D” входят нерентабельные группы товаров.
  4. Влияние внешних факторов на продажи. Несмотря на довольно устойчивую структуру продаж по данной модели, на оценку объема продаж в будущем сильное влияние оказывают внешние экономические факторы: сезонность, неравномерность потребления и спроса, покупательная способность, влияние конкурентов и т.д. Влияние данных факторов не отражается в модели ABC-анализ.

Резюме

ABC-анализ продаж позволяет выделить целевые группы товаров, которые обеспечивают 80% прибыли компании. Данный метод повышает эффективность функционирования предприятия, проводит анализ и оптимизацию ресурсов, что в свою очередь отражается на финансовую устойчивости и рентабельности компании. Разобранный пример показывает простоту использования модели ABC для анализа ассортимента товаров и продаж. Метод может широко использоваться в других сферах компании для выделения целевых групп: клиентов, поставщиков, подрядчиков, персонала и т.д.

Автор: к.э.н. Жданов Иван Юрьевич

ABC и XYZ анализ в Excel с примером расчета товарного ассортимента

Для анализа ассортимента товаров, «перспективности» клиентов, поставщиков, дебиторов применяются методы ABC и XYZ (очень редко).

В основе ABC-анализа – известный принцип Парето, который гласит: 20% усилий дает 80% результата. Преобразованный и детализированный, данный закон нашел применение в разработке рассматриваемых нами методов.

ABC-анализ в Excel

Метод ABC позволяет рассортировать список значений на три группы, которые оказывают разное влияние на конечный результат.

Благодаря анализу ABC пользователь сможет:

  • выделить позиции, имеющие наибольший «вес» в суммарном результате;
  • анализировать группы позиций вместо огромного списка;
  • работать по одному алгоритму с позициями одной группы.

Значения в перечне после применения метода ABC распределяются в три группы:

  1. А – наиболее важные для итога (20% дает 80% результата (выручки, к примеру)).
  2. В – средние по важности (30% — 15%).
  3. С – наименее важные (50% — 5%).

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

Условия для применения ABC-анализа:

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

Для каких значений можно применять методику АВС-анализа:

  • товарный ассортимент (анализируем прибыль),
  • клиентская база (анализируем объем заказов),
  • база поставщиков (анализируем объем поставок),
  • дебиторов (анализируем сумму задолженности).

Метод ранжирования очень простой. Но оперировать большими объемами данных без специальных программ проблематично. Табличный процессор Excel значительно упрощает АВС-анализ.

Общая схема проведения:

  1. Обозначить цель анализа. Определить объект (что анализируем) и параметр (по какому принципу будем сортировать по группам).
  2. Выполнить сортировку параметров по убыванию.
  3. Суммировать числовые данные (параметры – выручку, сумму задолженности, объем заказов и т.д.).
  4. Найти долю каждого параметра в общей сумме.
  5. Посчитать долю нарастающим итогом для каждого значения списка.
  6. Найти значение в перечне, в котором доля нарастающим итогом близко к 80%. Это нижняя граница группы А. Верхняя – первая в списке.
  7. Найти значение в перечне, в котором доля нарастающим итогом близко к 95% (+15%). Это нижняя граница группы В.
  8. Для С – все, что ниже.
  9. Посчитать число значений для каждой категории и общее количество позиций в перечне.
  10. Найти доли каждой категории в общем количестве.
Читать еще:  Как сделать чтобы excel открывал xlsx файлы?



АВС-анализ товарного ассортимента в Excel

Составим учебную таблицу с 2 столбцами и 15 строками. Внесем наименования условных товаров и данные о продажах за год (в денежном выражении). Необходимо ранжировать ассортимент по доходу (какие товары дают больше прибыли).

  1. Отсортируем данные в таблице. Выделяем весь диапазон (кроме шапки) и нажимаем «Сортировка» на вкладке «Данные». В открывшемся диалоговом окне в поле «Сортировать по» выбираем «Доход». В поле «Порядок» — «По убыванию».
  2. Добавляем в таблицу итоговую строку. Нам нужно найти общую сумму значений в столбце «Доход».
  3. Рассчитаем долю каждого элемента в общей сумме. Создаем третий столбец «Доля» и назначаем для его ячеек процентный формат. Вводим в первую ячейку формулу: =B2/$B$17 (ссылку на «сумму» обязательно делаем абсолютной). «Протягиваем» до последней ячейки столбца.
  4. Посчитаем долю нарастающим итогом. Добавим в таблицу 4 столбец «Накопленная доля». Для первой позиции она будет равна индивидуальной доле. Для второй позиции – индивидуальная доля + доля нарастающим итогом для предыдущей позиции. Вводим во вторую ячейку формулу: =C3+D2. «Протягиваем» до конца столбца. Для последних позиций должно быть 100%.
  5. Присваиваем позициям ту или иную группу. До 80% — в группу А. До 95% — В. Остальное – С.
  6. Чтобы было удобно пользоваться результатами анализа, проставляем напротив каждой позиции соответствующие буквы.

Вот мы и закончили АВС-анализ с помощью средств Excel. Дальнейшие действия пользователя – применение полученных данных на практике.

XYZ-анализ: пример расчета в Excel

Данный метод нередко применяют в дополнение к АВС-анализу. В литературе даже встречается объединенный термин АВС-XYZ-анализ.

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

Коэффициент вариации – относительный показатель, не имеющий конкретных единиц измерения. Достаточно информативный. Даже сам по себе. НО! Тенденция, сезонность в динамике значительно увеличивают коэффициент вариации. В результате понижается показатель прогнозируемости. Ошибка может повлечь неправильные решения. Это огромный минус XYZ-метода. Тем не менее…

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

  1. Расчет коэффициента вариации уровня спроса для каждой товарной категории. Аналитик оценивает процентное отклонение объема продаж от среднего значения.
  2. Сортировка товарного ассортимента по коэффициенту вариации.
  3. Классификация позиций по трем группам – X, Y или Z.

Критерии для классификации и характеристика групп:

  1. «Х» — 0-10% (коэффициент вариации) – товары с самым устойчивым спросом.
  2. «Y» — 10-25% — товары с изменчивым объемом продаж.
  3. «Z» — от 25% — товары, имеющие случайный спрос.

Составим учебную таблицу для проведения XYZ-анализа.

  1. Рассчитаем коэффициент вариации по каждой товарной группе. Формула расчета изменчивости объема продаж: =СТАНДОТКЛОНП(B3:H3)/СРЗНАЧ(B3:H3).
  2. Классифицируем значения – определим товары в группы «X», «Y» или «Z». Воспользуемся встроенной функцией «ЕСЛИ»: =ЕСЛИ(I3 <=10%;"X";ЕСЛИ(I3<=25%;"Y";"Z")).

В группу «Х» попали товары, которые имеют самый устойчивый спрос. Среднемесячный объем продаж отклоняется всего на 7% (товар1) и 9% (товар8). Если есть запасы этих позиций на складе, компании следует выложить продукцию на прилавок.

Запасы товаров из группы «Z» можно сократить. Или вообще перейти по этим наименованиям на предварительный заказ.

Как проводить анализ продаж: этапы, методы и способы

Здравствуйте! В этой статье мы расскажем про анализ объема продаж компании.

Сегодня вы узнаете:

  • Зачем необходимо проводить анализ объема продаж организации;
  • Как правильно проводить исследование продаж;
  • Какие методы анализа объема продаж существуют.

Содержание

Цель анализа продаж

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

Помимо предупреждения возможных проблем, анализ объема продаж решает следующие задачи:

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

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

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

Основные этапы проведения анализа продаж компании

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

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

Второй этап – определение показателей для анализа эффективности деятельности компании. Мы будем разбирать их более подробно при рассмотрении конкретных методик анализа.

Третий этап – анализ собранной информации и оценка результата.

Четвертый этап – определение влияющих факторов.

Виды анализа объема продаж

В зависимости от цели анализ объема продаж может принимать следующие виды:

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

Каждый из представленных видов мониторинга имеет свой инструментарий. Познакомимся с ним.

Методы анализа продаж

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

KPI – показатели эффективности деятельности компании.

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

Методы анализа динамики объема продаж

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

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

В рамках оценки динамики объема продаж необходимо провести следующие виды работ:

  • Анализ динамики прибыли предприятия – сравните выручку за настоящий и предшествующий периоды. Объем продаж может увеличиться, а выручка упасть. Это возможно, например, при снижении цены на товар;
  • Оценка равномерности продаж. Бывают сезонные товары, спрос на которые необходимо стимулировать в неблагоприятные периоды. Выявить сезонность позволит анализ равномерности продаж. Для этого постойте график объема продаж за несколько сезонов (можно взять год, но не забудьте учесть влияние изменения цен на товар, скидок и других стимулирующих воздействий) и посмотрите, в какие периоды наблюдался значительный рост и спад продаж (в несколько раз). Если такие колебания наблюдаются, то вам необходимо задуматься о стимулировании сбыта в неблагоприятные периоды.

Методы структурного анализа продаж

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

ABC-анализ.

Данный вид исследования направлен на оценку ценности каждого товара в продуктовом портфеле компании. Ценность продукта определяется объемом прибыли, которую продукт приносит в общую копилку.

Согласно ABC-анализу весь ассортимент компании можно поделить на три группы:

  • Группа “A” – товары, которые приносят наибольший объем прибыли;
  • Группа “B” – “середнячки”, они не настолько ценны для компании, но все же в совокупности приносят достаточно большой объем прибыли;
  • Группа “C” – тяжелый груз компании, эти продукты приносят очень скромный доход.

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

Численные границы каждой группы представлены в таблице.

По доли товарной категории в продуктовом портфеле, (%)

Ссылка на основную публикацию
Adblock
detector