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

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

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

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

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

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

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

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

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

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

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


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

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

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

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


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

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

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

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

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


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

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

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

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

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


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

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

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

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


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

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


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

АВС анализ

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


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

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

Читать еще:  Как сделать импорт в excel?

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

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


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

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

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


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

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

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


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

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

Прогнозирование продаж в Excel и алгоритм анализа временного ряда

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

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

Линейный тренд хорошо подходит для формирования плана по продажам для развивающегося предприятия.

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

Пример прогнозирования продаж в Excel

Рассчитаем прогноз по продажам с учетом роста и сезонности. Проанализируем продажи за 12 месяцев предыдущего года и построим прогноз на 3 месяца следующего года с помощью линейного тренда. Каждый месяц это для нашего прогноза 1 период (y).

Уравнение линейного тренда:

y = bx + a

  • y — объемы продаж;
  • x — номер периода;
  • a — точка пересечения с осью y на графике (минимальный порог);
  • b — увеличение последующих значений временного ряда.

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

  1. Рассчитаем значение линейного тренда. Определим коэффициенты уравнения y = bx + a . В ячейке D15 Используем функцию ЛИНЕЙН:
  2. Выделяем ячейку с формулой D15 и соседнюю, правую, ячейку E15 так чтобы активной оставалась D15. Нажимаем кнопку F2. Затем Ctrl + Shift + Enter (чтобы ввести массив функций для обеих ячеек). Таким образом получаем сразу 2 значения коефициентов для (a) и (b).
  3. Рассчитаем для каждого периода у-значение линейного тренда. Для этого в известное уравнение подставим рассчитанные коэффициенты (х – номер периода).
  4. Чтобы определить коэффициенты сезонности, сначала найдем отклонение фактических данных от значений тренда («продажи за год» / «линейный тренд»).
  5. Рассчитаем средние продажи за год. С помощью формулы СРЗНАЧ.
  6. Определим индекс сезонности для каждого месяца (отношение продаж месяца к средней величине). Фактически нужно каждый объем продаж за месяц разделить на средний объем продаж за год.
  7. В ячейке H2 найдем общий индекс сезонности через функцию: =СРЗНАЧ(G2:G13).
  8. Спрогнозируем продажи, учитывая рост объема и сезонность. На 3 месяца вперед. Продлеваем номера периодов временного ряда на 3 значения в столбце I:
  9. Рассчитаем значения тренда для будущих периодов: изменим в уравнении линейной функции значение х. Для этого можно просто скопировать формулу из D2 в J2, J3, J4.
  10. На основе полученных данных составляем прогноз по продажам на следующие 3 месяца (следующего года) с учетом сезонности:

Общая картина составленного прогноза выглядит следующим образом:

График прогноза продаж:



Алгоритм анализа временного ряда и прогнозирования

Алгоритм анализа временного ряда для прогнозирования продаж в Excel можно построить в три шага:

  1. Выделяем трендовую составляющую, используя функцию регрессии.
  2. Определяем сезонную составляющую в виде коэффициентов.
  3. Вычисляем прогнозные значения на определенный период.

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

Чтобы посмотреть общую картину с графиками выше описанного прогноза рекомендуем скачать данный пример:

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

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

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

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

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

Главные моменты, на которые нужно уделять свое внимание при проведении анализе продаж:

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

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

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

Читать еще:  Как сделать скроллинг в excel?

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

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

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

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

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

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

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

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

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

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

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

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

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

Анализ динамики продаж по фронтам

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

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

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

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

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

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

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

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

АВС анализ

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

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

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

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

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

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

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

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

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

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

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

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

​3 крутых Excel отчета для продуктивного планирования продаж для малого бизнеса

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

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

Честно говоря, я так много провел в Excel, что начал думать в терминах таблиц и даже мечтать в колонках и строчках. Много моделей назад, когда я бы аналитиком хэдж-фондов, мой босс, один из двух гениев, которых я когда-либо встречал, дал мне небольшой совет, который определил мое будущее с финансовыми моделями: “Всегда следуй за деньгами”. Я анализировал потенциальные инвестиции в фонд и пытался понять некоторые последние стратегические решения компании. Комментарий моего босса только усилил мое новое увлечение и желание хорошо проработать финансовую модель, которая бы отражала, сколько денег проходит через организацию и насколько эффективно работают менеджеры по продажам, — делится в своей статье Тим Бранк.

Читать еще:  Как сделать кривую производственных возможностей в excel?

Если вы управляете небольшой компанией, то умение связать стратегию с реальными цифрами действительно критично. Например вы уверены, что вы можете увеличивать вашу прибыль каждый месяц на 25% в следующем году. Какими показателями вы будете руководствоваться, чтобы достичь такого роста? Какую вы должны будете иметь CAC (Customer Acquisition Cost — стоимость привлечения клиента) в связи с такой задачей?

Учитывая, какими были эти затраты в прошлом, как много денег вам необходимо потратить на маркетинг/рекламу, чтобы обеспечить заданный рост доходов? Есть ли у вас вообще столько денег, которые вы можете вложить в маркетинг? Кроме того, каким образом остальная организация будет справляться с увеличивающимся потоком новых клиентов?

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

Три следующие модели помогут избежать таких ситуаций:

  1. Отчет по прогнозированию на три года.
  2. Модель последних четырех месяцев.
  3. Когортный анализ доходов.

Прогнозирование на три года

О чем:

Позволяет подсчитать простой доход и модель прогнозирования стоимости.

Описание:

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

Что нужно делать:

  1. Поставить стратегические цели, которых ваш бизнес должен достичь.
  2. Просчитать план роста.
  3. Провести стресс-тест (увеличить и уменьшить запланированную прибыль и посмотреть на способность вашего бизнеса справиться в новых условиях)/
  4. Установить ключевые метрики (KPI).
  5. Спланировать потребность в персонале, исходя из разных сценариев (обычно на 12 месяцев).
  6. Установить черту, сколько минимум/максимум капитала вы планируете привлечь.

Оговорки:

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

Если интересно разобраться, то отчет можно посмотреть или скачать тут.

Модель последних четырех месяцев

О чем:

Траектория для дохода, с учетом ретроспективного анализа последних 4 месяцев.

Что делать:

  1. Визуализировать в виде графиков и функций продаж за последние месяцы (включая стоимость среднего контракта, рост и падения).
  2. Оценить возможный рост дохода и использовать его для прогноза на следующие 12 месяцев.

Оговорки:

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

Если интересно разобраться, то отчет можно посмотреть или скачать тут.

Когорный анализ доходов

О чем:

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

Описание:

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

Что нужно делать:

  1. Распределить свой доход (прошлый и будущий) по когортам.
  2. Разобраться в показателях оттока клиентов и LTV.
  3. Расставить приоритеты в работе отдела продаж: необходимый объем контрактов или рост величины стоимости годового контракта.

Оговорки:

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

Если интересно разобраться, то отчет можно посмотреть или скачать тут.

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

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

Если вам интересна аналитика продаж, рекомендуем также прочитать статью на эту тему на блоге Ringostat.

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