Как сделать план продаж в экселе

Аналитика данных: как построить дашборд в Excel

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

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

ОНЛАЙН-ШКОЛА ВИЗУАЛИЗАЦИИ ДАННЫХ EXCELLENT

Дашборд помогает решать задачи менеджерам по продажам, HR-специалистам, бухгалтерам, маркетологам, руководителям

Дашборд ― динамический отчёт, который состоит из структурированного набора данных и их визуализации на основе диаграмм, графиков и таблиц.

Основные задачи дашборда:

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

Построение дашбордов ― такой же hard skill, как владение формулами в Excel. По статистике, пользователь Excel среднего уровня может освоить этот навык за 20 часов обучения и практики.

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

Чаще всего созданием дашборда занимается аналитик — он обрабатывает огромные массивы данных, оформляет их в красивый и понятный дашборд и передаёт заказчику задачи. Это могут быть руководители, менеджеры по продажам, HR-специалисты, бухгалтеры, маркетологи.

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

Существует большое количество сервисов для бизнес―аналитики, такие как Tableau, Power BI, Qlik, DataLens, Google Data Studio. Самым доступным можно назвать Excel.

Главное и самое интересное в дашборде ― интерактивность.

Настроить интерактивность можно с помощью следующих приёмов:

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

Создаём классический дашборд для руководителя отдела продаж

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

Советуем проделать все шаги вместе с нами. Как говорит гуру мотивации Наполеон Хилл, «мастерство приходит только с практикой и не может появиться лишь в ходе чтения инструкций». Файл с данными для тренировки можно скачать здесь.

Собираем данные

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

Плоская таблица (flat table) ― двумерный массив данных, состоящий из столбцов и строк. Столбцы ― это информационные атрибуты таблицы, строки ― отдельные записи, состоящие из множества атрибутов.

Пример плоской таблицы:

В примере выше атрибуты — это «Наименование», «День», «Год», «Склад», «Продажи (тыс. руб)», «Менеджер», «Заказчик». Они вынесены в заголовок таблицы.

Эта таблица послужит основой для построения нашего дашборда по продажам.

Выбираем макет дашборда и цели

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

Также необходимо определиться с макетом — структурой — дашборда. Для начала достаточно будет прикинуть её на листе формата А4.

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

Количество информационных блоков может быть разным: это зависит от того, сколько метрик надо отразить на дашборде. Главное — соблюдать выравнивание по сетке.

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

Помимо симметрии важно учитывать и логику расположения информационных блоков. Это связано с нашим восприятием: мы привыкли читать слева направо, поэтому наиболее важные метрики необходимо располагать слева направо и сверху и вниз, менее важные ― справа внизу:

Построим несколько сводных таблиц по продажам

— на основе таблицы с данными, приведённой выше в качестве примера плоской таблицы.

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

Должно получиться вот так:

Также построим таблицу для ключевых показателей «Продажи», «Средний чек», «Количество продаж»:

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

Создадим диаграммы на основе сводных таблиц

В нашем дашборде будем использовать три типа диаграмм:

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

Выделим диапазон таблицы, перейдём на ленте в раздел Вставка → Диаграммы → Вставка диаграммы → Выберем нужный тип диаграммы → ОК:

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

Переместим построенные диаграммы на отдельный лист

… и распределим их согласно выбранному на втором шаге макету:

Добавим ключевые показатели (KPI)

После размещения диаграмм необходимо вставить поля с ключевыми показателями: перейдём на ленте в раздел Вставка ⟶ Фигуры и вставим 3 текстбокса:

Как составить план продаж на месяц в Excel c графиком прогноза

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

Как составить план продаж на месяц: пример

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

Реализация за прошлый год:

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

Так как предприятие развивающееся, для прогнозирования продаж можно использовать линейный тренд. Математическое уравнение: y = b*x + a. Где

• y – продажи;
• х – порядковый номер периода;
• а – минимальная граница;
• b – повышение каждого следующего значения во временном ряду.

Значение линейного тренда в Excel рассчитывается с помощью функции ЛИНЕЙН. Составим табличку для определения коэффициентов уравнения (y и х нам уже известны).

Для быстрого вызова функции нажимаем F2, а потом сочетание клавиш Ctrl + Shift + Ins. А комбинацией SHIFT+F3 открываем окно с аргументами функции ЛИНЕЙН (курсор стоит в ячейке G 2, под аргументом b). Заполняем:

Выделяем сразу 2 ячейки: G2 и H2 (значения аргументов b и а). Активной должна быть ячейка с формулой. Нажимаем F2, а потом сочетание клавиш Ctrl + Shift + Enter. Получаем значения для неизвестных коэффициентов уравнения:

Найдем для каждого периода анализируемого временного интервала значение y. Подставим рассчитанные коэффициенты в уравнение.

Следующий этап – расчет отклонений значений фактических продаж от значений линейного тренда:

Это значение нам необходимо для расчета сезонности. Далее находим средний показатель реализации за все периоды с помощью функции СРЗНАЧ.

Рассчитаем индекс сезонности для каждого периода. Формула расчета: объем продаж за период / средний объем.

С помощью функции СРЗНАЧ найдем общий индекс сезонности:

Спрогнозируем уровень продаж на будущий месяц. Учтем рост объема реализации и сезонность.

Сначала найдем значение тренда для будущего периода. Для этого в столбце с номерами периодов добавим число 13 – новый месяц. Продлим формулу в столбце «Значение тренда» на одну ячейку вниз:

Умножим значение тренда на индекс сезонности соответствующего месяца (в примере – «января»). Получим рассчитанный объем реализации товара в новом периоде:

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

График прогноза с линией тренда:

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

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

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

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

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

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

Для расчета процента выполнения плана нужно фактические показатели разделить на плановые, установить для ячеек в Excel процентный формат.

Прогноз продаж в Excel

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

Исходные данные
Для расчета прогноза потребуются данные о продажах за ранние периоды. Чем больше данных, тем точнее будет прогноз. Желательно, чтобы были помесячные данные хотя бы за два года. На мой взгляд это тот минимум, на основании которого можно построить весьма точный прогноз с учетом прошлого опыта. Именно из таких данных и будем исходить. Предположим, что у нас есть данные с января 2013 года по август 2015, в табличном виде:

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

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

Т.е. у нас должно быть именно в указанном порядке 7 столбцов: Период; Продажи компании, руб.; Прогноз; Оптимистичный; Пессимистичный; Коэффициент сезонности; Отклонение . И чтобы все получилось они должны идти точно в таком же порядке, как на картинке выше.

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

В самый низ таблицы, после последней фактической даты, я добавил даты, на которые необходимо построить прогноз( от сен.2015 до авг.2016 ).

Расчет прогноза
Для расчета непосредственно прогноза в Excel есть специальная функция, которая основываясь на данных предыдущих периодов предсказывает вероятные значения для указанной даты. Она так и называется – ПРЕДСКАЗ (FORECAST) . Функция основана на линейной регрессии и специально предназначена именно для прогнозирования продаж, потребления товара и пр. В столбец Прогноз (столбец C – сразу после столбца с суммами продаж) в ячейку C34 записываем функцию (и распространяем на все прогнозируемые даты – C34:C45 ):
=ПРЕДСКАЗ( A34 ; $B$2:$B$33 ; $A$2:$A$33 )
=FORECAST(A34,$B$2:$B$33,$A$2:$A$33)
Сама функция требует указания следующих входных данных:

• х — Дата, значение для которой необходимо спрогонозировать ( A34 )
• Известные значения y — ссылка на ячейки таблицы с суммами продаж за известные периоды ($B$2:$B$33)
• Известные значения x — ссылка на ячейки таблицы с дата продаж за известные периоды ($A$2:$A$33)

С одной стороны, мы уже имеем готовый прогноз, а с другой. Данная функция пока не учитывает фактор сезонности. А это в продажах в большинстве случаев немаловажный фактор. Поэтому желательно потратить еще чуточку времени и сделать так, чтобы прогноз получился еще больше приближен к реальности. Для учета фактора сезонности сначала необходимо вычислить коэффициент сезонности для каждого месяца. Для этого добавим в столбец Коэффициент сезонности следующую формулу:
=(( $B$2:$B$13 + $B$14:$B$25 )/СУММ( $B$2:$B$25 ))*12
=(($B$2:$B$13+$B$14:$B$25)/SUM($B$2:$B$25))*12
Формула вводится в ячейку как формула массива и сразу в 12 ячеек(чтобы получить коэффициенты для каждого месяца года). Для этого сначала выделяем ячейки F2:F13 -переходим в строку формул и вводим формулу выше. После указания верных ссылок на нужные ячейки завершаем ввод формулы одновременным нажатием трех клавиш: Ctrl+Shift+Enter. Если этого не сделать, то функция вернет значение ошибки #ЗНАЧ! (#VALUE!)

Подробнее про принцип работы формулы: она берет отдельно сумму каждого месяца за 2013 и 2014 год, складывает их. Делит полученное значение на общую сумму продаж за весь период целых месяцев(т.е. 24 месяца) и умножает на 12, чтобы получить коэффициент именно за один месяц. И так для каждого месяца. Т.е. для ячейки F2 расчет будет выглядеть следующим образом:
=(( 56 769 + 68 521 )/ 1 542 293 )*12
=(( сумма за янв.2013 + сумма за янв.2014 )/ общая сумма за два года(янв.2013 – дек.2014) )*12

В результате для января получим коэффициент 0,974834224106574 , для февраля — 0,989928632237843 и т.д. Я для наглядности назначил ячейкам процентный формат(правая кнопка мыши —Формат ячеек -вкладка Число —Процентный (Format cells —Number —Percent) , два знака после запятой):

Теперь добавим учет этих коэффициентов для расчета прогноза в имеющуюся функцию ПРЕДСКАЗ(ячейки C34:C45 ):
=ПРЕДСКАЗ( A34 ; $B$2:$B$33 ; $A$2:$A$33 )*ИНДЕКС( $F$2:$F$13 ;МЕСЯЦ( A34 ))
=FORECAST(A34,$B$2:$B$33,$A$2:$A$33)*INDEX($F$2:$F$13,MONTH(A34))
Здесь применяется функция ИНДЕКС (INDEX) , в которой первым аргументом указываем ссылку на 12 ячеек с коэффициентами сезонности( $F$2:$F$13 ), а вторым – номер месяца, чтобы вернуть коэффициент именно для нужного месяца(для этого используем функцию МЕСЯЦ (MONTH) , которая возвращает только номер месяца из указанной даты). Для сентября 2015 это будет выглядеть так:
=ПРЕДСКАЗ( A34 ; $B$2:$B$33 ; $A$2:$A$33 )*ИНДЕКС(<97,48%:98,99%:90,38%:94,66%:100,86%:99,02%:100,66%:110,39%: 100,47% :104,82%:105,13%:97,14%>; 9)

Основную задачу выполнили – у нас есть прогноз на будущие периоды. Теперь осталось в дополнение к самому прогнозу, создать допустимые верхние и нижние границы, которые часто еще называют оптимистичный прогноз и пессимистичный(но по сути это просто возможное отклонение от прогнозных данных). Такой прогноз даст нам возможность более гибко планировать тактику на будущие периоды.
Для того, чтобы построить такие прогнозы необходимо рассчитать допустимое отклонение от прогнозируемых значений. Здесь так же будем использовать имеющиеся в Excel функции. В ячейку G2 запишем формулу:
=ДОВЕРИТ(0,05; СТАНДОТКЛОН( C34:C45 ); СЧЁТ( C34:C45 ))
=CONFIDENCE(0.05,STDEV(C34:C45),COUNT(C34:C45))
ДОВЕРИТ (CONFIDENCE) – возвращает доверительный интервал, используя нормальное распределение.

• алфа – уровень значимости для вычисления доверительного уровня. Используемое в формуле 0,05 означает доверительный уровень в 95%. В большинстве случаев это оптимальное значение
• станд_откл – стандартное отклонение генеральной совокупности. Должно быть известно. Но т.к. мы этими данными не располагаем – то это значение вычисляем при помощи функции СТАНДОТКЛОН (STDEV) , передавая ей для расчетов спрогнозированные данные
• размер – указывается целое число, обозначающее количество данных для выборки. Как правило равно количеству спрогнозированных данных. У нас количество определяется функцией СЧЁТ, которая подсчитывает количество чисел в указанных ячейках.

Теперь в ячейки столбцов Оптимистичный и Пессимистичный(D и E), начиная со строки 34, запишем такие формулы:
Оптимистичный: = $C34 + $G$2
Пессимистичный: = $C34 — $G$2

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

График
Но было бы кощунством с нашей стороны проделать такую работу и не использовать возможности Excel для построения красивого графика. Придется добавить немного шаманства(на деле, мы уже начали шаманить, когда стали записывать прогноз в отдельный столбец, а не продолжать его в том же столбце, что и фактические продажи). В ячейки C33, D33 и E33 скопируем значение из ячейки B33, чтобы они все имели одинаковые значения:

Теперь выделяем все данные ( A1:E45 ), переходим на вкладку Вставка (Insert) – группа Диаграммы (Charts) —График (Line) . И получим такую картину:

Наглядно и сразу понятно что к чему и чего можно ожидать.

• Синим – фактические продажи
• Оранжевый – прогноз
• Серый – Оптимистичный прогноз
• Желтый – Пессимистичный

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

Быстрый прогноз в Excel 2016 и выше
Начиная с версии 2016 в Excel появилась замечательная возможность создать прогноз двумя кликами мыши. При этом сразу с оптимистичным и пессимистичным развитием событий и графиком. За основу возьмем все те же исходные данные из двух столбцов:

Выделяем необходимые данные из двух столбцов -переходим на вкладку Данные (Data) -группа Прогноз (Forecast) —Лист прогноза (Forecast Sheet) :

В появившемся окне раскрываем пункт Параметры (Options) и настраиваем:

• Завершение прогноза (Forecase End) – указывается дата, которой должен заканчиваться прогноз. Я советую всегда проверять эту дату, т.к. по умолчанию Excel почти всегда выставляет некую среднюю дату, которая отличается от необходимой.
• Начало прогноза (Forecase Start) – указывается дата, с которой необходимо начать строить прогноз. Как правило это последняя дата фактических данных. Если указать дату, которая будет раньше последней даты фактических данных, то для построения прогноза будут использоваться данные только ДО этой даты (так же это называется «ретроспективным прогнозированием»).
• Доверительный интервал (Confidence interval) – этот пункт поможет понять, насколько точно построен прогноз. Чем больше будет доверительный интервал, тем меньше точность прогноза и чем меньше доверительный интервал – тем выше точность прогноза. Что вполне логично. По умолчанию определяется для 95% точек, хотя его можно изменить в соответствующем поле. Если интервал создавать не нужно – снять галочку.
• Сезонность (Seasonality) – как понятно из названия, отвечает за определение фактора сезонности. Лучше оставлять автоматическим, при котором сезонность определяется на основании всех точек месяцев(т.е. 12). Но если этот фактор необходимо рассчитывать из иного количества точек, то необходимо выбрать Установка вручную и указать нужное количество точек. Но следует учитывать, что если точек будет недостаточно – то прогноз может быть очень неточным и график в итоге будет иметь вид, далекий от ожидаемого.
• Диапазон временной шкалы (Timeline Range) – указывается диапазон значений с датами фактических продаж, на основании которых необходимо построить прогноз. По размерам должен совпадать с параметром Диапазон значений.
• Диапазон значений (Values Range) – указывается диапазон значений с суммами фактических продаж, на основании которых необходимо построить прогноз. По размерам должен совпадать с параметром Диапазон временной шкалы.
• Заполнить отсутствующие точки с помощью (Fill Missing Poins Using) – если каких-то данных не хватает(например, имеются пропуски в ячейках с суммами), то можно выбрать чем эти данные заполнить. По умолчанию используется интерполяция. Это означает, что отсутствующие данные вычисляется как взвешенное среднее соседних ячеек, если отсутствует менее 30 % точек. Если необходимо заполнять отсутствующие точки нулями, то необходимо выбрать из выпадающего списка пункт Нули.
• Объединить дубликаты с помощью (Aggregate Duplicates Using) – если в фактических данных есть повторяющиеся даты, то Excel объединит их в одну точку с этой датой, а в качестве суммы подставит среднее арифметическое для этой даты. Это оптимальный вариант, но так же допускается выбрать из списка и другую функцию: Количество, СЧЁТЗ, Максимум, Медиана, Минимум, Сумма.
• Включить статистические данные прогноза (Include Forecast Statistics) – при включении данного пункта на листе с таблицей графика правее основных данных будет создана таблица с дополнительной статистической информации о прогнозе. В таблице при помощи функции ПРЕДСКАЗ.ЕTS.СТАТ будут рассчитаны коэффициенты сглаживания (Альфа, Бета, Гамма), и метрики ошибок (MASE, SMAPE, MAE, RMSE).

После нажатия кнопки Создать (Create) будет создан новый лист, в котором будет создана таблица со всеми необходимыми данными и формулами и готовым графиком:

если при создании был отмечен пункт Включить статистические данные прогноза (Include Forecast Statistics) , то правее таблицы основных данных будет так же создана таблица статистических данных:

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

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

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

• Для чего необходим план продаж;
• Как его рассчитать и оформить;
• Как добиться от сотрудников выполнения плана.

Содержание

• Зачем нужен план продаж
• Виды планирования
• Правила создания плана продаж
• Порядок разработки плана продаж
• Методы расчета плана продаж
• Анализ временных рядов
• Оформление плана продаж
• Структуризация плана
• Распространенные ошибки
• Как добиться выполнения плана

Зачем нужен план продаж

Нужен ли план продаж на предприятии? Ответ однозначный – да. И не только тем, кто продает конкретные товары, но и работникам сферы услуг он тоже просто необходим.

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

Виды планирования

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

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

Существует несколько видов планирования:

• Перспективное – долгосрочная стратегия на 5-10 лет;
• Текущее – разрабатывается на год, уточняет и корректирует показатели перспективного планирования;
• Оперативно-производственное – задачи делятся на более короткие отрезки (квартал, месяц и т. д.).

Правила создания плана продаж

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

Например, это могут быть:

• Сезонность;
• Динамика развития и тенденции на рынке;
• Причины спада в прошлые периоды;
• Конкуренция;
• Изменения в политике, экономике и законодательстве;
• Изменение ассортимента и цен;
• Каналы сбыта и потенциальные покупатели;
• Сотрудники;
• Реклама.

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

Порядок разработки плана продаж

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

Чтобы получить адекватный результат и ничего не упустить необходимо:

1. Проанализировать тенденции в политике и макроэкономике. Как изменяется ВВП страны? Что происходит с ценами на нефть, газ и с курсом валют? Не лишним будет ознакомиться с мнениями экспертов, ведущих экономических СМИ.
2. Изучить ситуацию на рынке. Увеличится или уменьшится спрос? Появились ли новые конкуренты и потенциальные клиенты?
3. Вывести статистику продаж по прошлым периодам. За год в целом и за каждый месяц в частности.
4. Проанализировать причины спада и роста. Это может быть сезонность, изменения в политике компании, новый ассортимент, кадровые изменения. Составляя план на будущий год, обязательно опирайтесь на значимые моменты.
5. Составить статистику продаж отдельно по продавцам и отделам. Ориентироваться на лидеров будет слишком оптимистично, но постарайтесь немного приблизить к ним среднее значение.
6. Сформировать базу постоянных покупателей. Сколько прибыли они приносят, как часто и за какими товарами они приходят? Конечно, этот этап не относится к компаниям, ориентированным на разовые продажи.
7. Поставить цель. На основе сделанного ранее анализа уже можно представить какие были продажи в прошлом году, и на сколько их возможно увеличить в будущем. Лучше поставить две цели: выполнимую и идеальную. Именно наличие второй будет напоминать, что не стоит останавливаться на достигнутом.
8. Обсудить план с подчиненными. Установить сроки и личностные инструкции.
9. Составить бюджет. Имея наглядный план продаж, проще рассчитать, сколько придется потратить на закупки, рекламу, на премии сотрудникам.

Методы расчета плана продаж

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

1. Субъективные: опросы, анкеты, решения, основанные на опыте предпринимателя;
2. Объективные: тестовые продажи, анализ ранних периодов, статистика спроса.

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

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

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

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

Анализ временных рядов

Скользящее среднее

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

Пример. В магазине канцтоваров за 2016 год продали 2700 шариковых ручек, за 2015 – 3140, за 2014 – 2900. Прогноз на 2017 год: (2700+3140+2900)/3=2910.

Экспоненциальное сглаживание

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

Константа сглаживания (КС) может быть от 0 до 1. При среднем уровне продаж она равна 0,2-0,4, а во время роста (например, праздничные дни) – 0,7-0,9. Наиболее подходящее значение КС определяется опытным путем – выбирается значение с наименьшей погрешностью за прошедшие периоды.

Формула: КС * Фактический спрос за текущий период + (1-КС) * Прогноз на текущий период.

Пример. За месяц в магазине канцтоваров продали 640 блокнотов при предыдущем прогнозе 610, КС – 0,3. Прогноз на следующий месяц: 0,3*640 + (1-0,3)*610= 619.

Декомпозиция и коэффициент сезонности

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

Шаг 1. Определение сезонной динамики. Наглядным цифровым показателем здесь выступит коэффициент сезонности.

Чтобы его рассчитать, нужно:

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

Пример. За прошлый год магазин осуществил продаж на 850 000 руб. Из них 44 000 в январе, 50 000 в феврале и так далее. Среднемесячное значение 850000/12 = 70 830 руб. Коэффициент сезонности января: 44000/70830=0,62, для февраля: 50000/70830=0,71.

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

Шаг 2. Определить цель. Например, вы ставите цель увеличить продажи на 20%. Расчет произвести просто: к сумме продаж за прошлый год нужно прибавить 20%.

Шаг 3. Составить план продаж на месяц. Общий план на год далее предстоит разбить на более мелкие периоды – в нашем примере это месяцы.

1. Годовую цель разделить на 12 – получится средний план на месяц.
2. Умножить средний план на коэффициент сезонности по каждому месяцу.

Пример. Средний план на месяц: 1020000/12 = 85 000 руб. План на январь: 85000*0,62 = 52 700 руб., план на февраль: 85000*0,71 = 60 350 руб.

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

Оформление плана продаж

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

Перечислим все основные по порядку:

1. Шапка, состоящая из заголовка («План продаж отдела….») и указания автора («Составлен…» далее должность и Ф.И.О. составившего план).
2. Первый пункт – сотрудники и достижения. Здесь стоит перечислить всех работников отдела, указать потребность в новых кадрах, если есть таковая, а также упомянуть ключевые достижения за прошлый период.
3. Второй пункт – итоги прошлого периода. Для наглядности можно включить в документ график роста и спада продаж, привести итоговые значения не только по отделу в целом, но и по каждому сотруднику в частности, в процентном соотношении указать, насколько был перевыполнен или недовыполнен прошлый план.
4. Третий пункт – план на будущий период. Указывается сумма плана, перечисляются основные планируемые сделки, клиенты, которые уже готовы заключить контракт и прочие моменты, обеспечивающие гарантию прибыли в новом периоде.
5. Четвертый пункт – необходимые мероприятия. Далее, речь идет о действиях, которые еще только предстоит выполнить для достижения цели. Это могут быть изменения ценовой политики, рекламные акции, обновление технической базы компании и многие другие.
6. Дата и подписи утвердивших план руководителей.

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

Структуризация плана

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

Опираясь на особенности своего дела, поставьте цели сразу в нескольких направлениях:

• Доля регионального и макрорынка;
• Объем продаж в целом;
• Финансовая прибыль.

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

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

Подобная детализация плана необходима на каждом предприятии.

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

• Регионы (где и сколько будет продано);
• Продавцы (кто и сколько продаст);
• Товары (сколько чего будет продано);
• Время (когда и сколько будет продано);
• Каналы сбыта (кому и сколько будет продано);
• Характер продаж (сколько продаж гарантировано, а сколько только запланировано).

Распространенные ошибки

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

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

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

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

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

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

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

Будьте уверены, что план вы составили верно, если по итогам периода он оказался выполнен на 85-105%.

Как добиться выполнения плана

Одно дело составлять план для себя. Это может сделать стремящийся увеличить прибыль предприниматель или менеджер, нацеленный на карьерный рост.

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

Лучше прислушайтесь к советам опытных предпринимателей:

1. Коротко, но максимально полно сформулируйте, что вы хотите от своих работников. Лучше донести это до них в письменной форме.
2. Стимулируйте финансово. Лучшие работники достойны премии.
3. Установите бонусы не только за 100% выполнение, но и каждое преодоление некоего минимального порога (например, 60%). Пусть работник и не выполнил план, но видно, что он старался.
4. Штрафуйте за систематические нарушения.
5. Финансово зависеть от выполнения планов должна вся вертикаль сотрудников (от рядового продавца до топ-менеджера).
6. Уважайте и цените своих сотрудников и стремитесь к тому, чтобы они любили свое место работы и были заинтересованы в развитии и процветании фирмы.

Excel для финансиста

Поиск на сайте

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

Планирование продаж — отправная точка создания бюджета в Excel. Практически все коммерческие компании начинают планирование в Excel с этого шага.

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

�?ндекс сезонности показывает, насколько конкретный период «выбивается» из основной тенденции повышения или понижения продаж.

Планирование будет состоять в следующем:

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

Скачайте и откройте файл примера Planirovanie-prodazh-s-uchetom-sezonnosti. В диапазоне С6:С17 расположены фактические данные продаж за 3 года (разбитые на 12 кварталов, это упрощённо; в реальности лучше оперировать месячными данными). Необходимо рассчитать значения продаж на следующие два года.

Расчёт и построение линии тренда

Расчёт линии тренда производится следующим образом. На основе данных диапазона В6:С17 строится обычная диаграмма типа график (можно также использовать гистограмму). На диаграмме можно увидеть колебания выручки, явно привязанные к сезонам.

Затем нужно нажать правой клавишей мыши на линии графика, в открывшемся контекстном меню выберите Добавить линию тренда… В открывшемся окне нужно выбрать параметры: Линейная, отметить Показывать уравнение на диаграмме. На графике появится прямая линия тренда и уравнение, её описывающее.

Planirovanie-prodazh-s-uchetom-sezonnostiС помощью этого уравнения рассчитываются данные тренда по каждому периоду. В ячейку А6 записана соответствующая формула «=A6*4359+117264», аналогично рассчитываются все значения столбца Е, включая плановые значения линии тренда в диапазоне Е18:Е25.

Расчёт фактического и планового индекса сезонности

Фактический индекс сезонности в Excel рассчитывается как отношение выручки за период к соответствующему значению линии тренда. В ячейке F6 записана формула «=C6/E6», аналогично рассчитаны значения ячеек F7:F17.

Плановый индекс сезонности рассчитывается несколько иначе. В ячейке F18 это значение рассчитано формулой «=СРЗНАЧ(F6;F10;F14)/СРЗНАЧ($F$6:$F$17)»: взято усреднённое значение фактических индексов сезонности за несколько одинаковых периодов (1 квартал) и разделено на среднее по всем индексам сезонности за весь период. Аналогичным образом рассчитываются плановые индексы сезонности по остальным периодам.

Расчёт прогнозных данных

Прогноз выручки рассчитывается на основе линии тренда и плановых индексов сезонности, эти величины нужно просто перемножить: в ячейке D18 формула «=E18*F18».

Отображение данных на одном графике

Обратите внимание на то, что фактические и прогнозные данные разнесены по разным столбцам таблицы. Это сделано специально для того, чтобы легко отобразить эти данные на графике разными цветами. Ещё одна хитрость: в ячейку С18 занесена формула «=D18», это нужно, чтобы фактические и прогнозные данные на графике отображались одной линией, если здесь будет пусто – на графике будет разрыв. Таблица готова, на основе диапазона B6:D25 строится обычная диаграмма-график.

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