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

Скачать материалы по расчету объемов продаж :

Чем отличается прогноз продаж от плана?

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

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

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

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

Обычно базой для прогнозирования являются данные о предыдущих объемах. Самый элементарный случай прогнозирования выглядит следующим образом:

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

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

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

По времени делятся на:

1. Краткосрочные – на периоды в течение 1 года: на месяц, квартал, полугодие и год.
2. Среднесрочные – это обычно на период от 1 до 3 лет.
3. Долгосрочные – больше 3-5 лет.

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

В практической деятельности применяют три основных метода:

1. Метод экспертных оценок.
2. Анализ временных рядов.
3. Казуальный метод.

Метод экспертных оценок

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

Например, предприниматель, торгующий алкогольными напитками, пивом может спрогнозировать то, как успешно будет развиваться его бизнес в ближайшей перспективе, на основе выводов экспертов в этой области. Если эксперты говорят, что в следующем году рынок «просядет» на 12% (это пример, естественно), то и предприниматель вполне обосновано может просчитать возможное падение своих продаж на уровень около 12%.

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

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

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

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

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

2015 год			2016 год ПРОГНОЗ
Месяц	Продажи, руб.	Рост
Сентябрь

Временные ряды – это данные о продажах (колонка 2) в каждом месяце (колонка 1) прошедшего года. В нашем примере проведен анализ объемов в 2015 году, на основе которого реализация продукции спрогнозирована на 2016 год.

В таблице анализ временных рядов проведен с целью выявления тренда. Мы видим, что в январе товаров продали в магазине на 150212 рублей, а в феврале уже на 160547 рублей. Рост составил 7%.

В колонке 3 рассчитан рост в каждом месяце по сравнению с предыдущим, например, в августе по сравнению с июлем рост продаж составил всего 1%, а в декабре по сравнению с ноябрем уже 6%. При этом среднемесячный прирост в 2015 году составил 4% (последняя строка колонки 3).

Получается, что если в январе 2015 года мы продали товаров на 150212 рублей, то на январь следующего года мы продадим на сумму в объеме 156220 рублей, то есть, на 4% больше.

Годовой же объем реализации в магазине также вырастет на 4%: с 2,3 миллионов рублей до 2,4 миллионов рублей.

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

Анализ временных рядов с учетом сезонности

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

2015 год			2016 год ПРОГНОЗ
Месяц	Продажи, руб.	Рост
Сентябрь

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

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

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

Из чего состоит временной ряд

Уровни временного ряда (Yt) представляют из себя сумму двух компонент:

1. Регулярную составляющую
2. Случайную составляющую

В свою очередь регулярная составляющая состоит из:

1. Тренда
2. Сезонности
3. Циклической составляющей

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

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

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

1. Тренд (Tt)
2. Сезонность (St)
3. Цикличность (Ct)
4. Случайные возмущения (Et)

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

Виды моделей временного ряда

Обычно, выделяют две модели временного ряда и третью — смешанную.

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

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

Построение этих моделей сводится к расчету тренда (Tt), сезонности (St) и случайных возмущений (Et) для каждого уровня ряда (Yt).

Алгоритм построения модели

1. Выравниваем ряд с помощью скользящей средней, то есть сглаживаем ряд и отфильтровываем высокочастотные колебания.
2. Рассчитываем значение сезонной компоненты St.
3. Рассчитываем значения Tt с использованием полученного уравнения тренда.
4. Используя полученные значения St и Tt, находим прогнозные значения уровней временного ряда.
5. Оцениваем качество модели.

Реализация на практике

Итак, мы имеем на руках данные о продажах за 2016 и 2017 год и хотим спрогнозировать продажи на 2018 год.

Шаг 1

Следуя нашему алгоритму, мы должны сгладить временной ряд. Воспользуемся методом скользящей средней. Видим, что в каждом году есть большие пики (май-июнь 2016 и апрель 2017), поэтому возьмем период сглаживания пошире, например, месячную динамику, т.е. 12 месяцев.

Удобнее брать период сглаживания в виде нечетного числа, тогда формула для расчета уровней сглаженного ряда:

yi — фактическое значение i-го уровня ряда,

yt — значение скользящей средней в момент времени t,

2p+1 — длина интервала сглаживания.

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

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

Сглаживаем наши уровни ряда и растягиваем формулу вниз:

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

В качестве сглаженной я выбрала полином третьей степени, так как он лучше всего описывал уровни временного ряда и имел наибольший R^2.

Шаг 2

Так как мы рассматриваем аддитивную модель вида:

Найдем оценки сезонной компоненты как разность между фактическими уровнями ряда и значениями скользящей средней St+Et = Yt-Tt, так как Yt и Tt мы уже знаем.

Используем оценки сезонной компоненты (St+Et) для расчета значений сезонной компоненты St. Для этого найдем средние за каждый интервал (по всем годам) оценки сезонной компоненты St.

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

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

Шаг 3

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

T(t) = - 23294 + 34114 * t - 1593 *t^2 + 26,3 *t^3

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

Шаг 4

Имея рассчитанные значения S(t) и T(t) мы можем рассчитать прогнозные значения уровней ряда Y(t). Для этого накладываем уровни сезонности на тренд.

Теперь построим график известных значений Y(t) и спрогнозированных за 2018 год.

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

Шаг 5

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

yi — спрогнозированные уровни ряда,

yi* — фактические уровни ряда,

n — количество складываемых элементов.

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

Сложив весь столбец с ошибками аппроксимации и поделив на 12, получаем среднюю ошибку аппроксимации 4,13%. Это значение меньше 15% и можем сделать вывод об адекватности модели.

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

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

В данной статье представлен один из возможных алгоритмов построения прогноза объёма реализации для продуктов с сезонным характером продаж. Сразу следует отметить, что перечень таких товаров гораздо шире, чем это кажется. Дело в том, что понятие “сезон” в прогнозировании применим к любым систематическим колебаниям, например, если речь идёт об изучении товарооборота в течение недели под термином “сезон” понимается один день. Кроме того, цикл колебаний может существенно отличаться (как в большую, так и в меньшую сторону) от величины один год. И если удаётся выявить величину цикла этих колебаний, то такой временной ряд можно использовать для прогнозирования с использованием аддитивных и мультипликативных моделей.

Аддитивную модель прогнозирования можно представить в виде формулы:

где: F – прогнозируемое значение; Т – тренд; S – сезонная компонента; Е – ошибка прогноза.

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

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

Рис. 1. Аддитивная и мультипликативные модели прогнозирования.

Алгоритм построения прогнозной модели

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

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

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

3.Рассчитываются ошибки модели как разности между фактическими значениями и значениями модели.

4.Строится модель прогнозирования:

где:
F– прогнозируемое значение;
Т – тренд;
S – сезонная компонента;
Е - ошибка модели.

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

F пр t = a F ф t-1 + (1-а) F м t

где:

F ф t- 1 – фактическое значение объёма продаж в предыдущем году;
F м t - значение модели;
а – константа сглаживания

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

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

Применение алгоритма рассмотрим на следующем примере.

Исходные данные: объёмы реализации продукции за два сезона. В качестве исходной информации для прогнозирования была использована информация об объёмах сбыта мороженого “Пломбир” одной из фирм в Нижнем Новгороде. Данная статистика характеризуется тем, что значения объёма продаж имеют выраженный сезонный характер с возрастающим трендом. Исходная информация представлена в табл. 1.

Таблица 1.
Фактические объёмы реализации продукции

		Объем продаж (руб.)			Объем продаж (руб.)
	сентябрь			сентябрь

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

Реализуем алгоритм построения прогнозной модели, описанный выше. Решение данной задачи рекомендуется осуществлять в среде MS Excel, что позволит существенно сократить количество расчётов и время построения модели.

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

Рис. 2. Сравнительный анализ полиномиального и линейного тренда

На рисунке показано, что полиномиальный тренд аппроксимирует фактические данные гораздо лучше, чем предлагаемый обычно в литературе линейный. Коэффициент детерминации полиномиального тренда (0,7435) гораздо выше, чем линейного (4E-05). Для расчёта тренда рекомендуется использовать опцию “Линия тренда” ППП Excel.

Рис. 3. Опция “Линии тренда”

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

• логарифмический R 2 = 0,0166;
• степенной R 2 =0,0197;
• экспоненциальный R 2 =8Е-05.

2. Вычитая из фактических значений объёмов продаж значения тренда, определим величины сезонной компоненты , используя при этом пакет прикладных программ MS Excel (рис. 4).

Рис. 4. Расчёт значений сезонной компоненты в ППП MS Excel.

Таблица 2.
Расчёт значений сезонной компоненты

Месяцы	Объём продаж	Значение тренда	Сезонная компонента

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

Таблица 3.
Расчёт средних значений сезонной компоненты

Месяцы					Сезонная компонента

3. Рассчитываем ошибки модели как разности между фактическими значениями и значениями модели.

Таблица 4.
Расчёт ошибок

Месяц	Объём продаж	Значение модели	Отклонения

Находим среднеквадратическую ошибку модели (Е) по формуле:

Е= Σ О 2: Σ (T+S) 2

где:
Т- трендовое значение объёма продаж;
S – сезонная компонента;
О - отклонения модели от фактических значений

Е= 0,003739 или 0.37 %

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

Построим модель прогнозирования:

Построенная модель представлена графически на рис. 5.

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

F пр t = a F ф t-1 + (1-а) F м t

где:
F пр t - прогнозное значение объёма продаж;
F ф t-1 – фактическое значение объёма продаж в предыдущем году;
F м t - значение модели;
а – константа сглаживания.

Константу сглаживания рекомендуется определять методом экспертных оценок, как вероятность сохранения существующей рыночной конъюнктуры, т.е. если основные характеристики изменяются / колеблются с той же скоростью / амплитудой что и прежде, значит предпосылок к изменению рыночной конъюнктуры нет, и следовательно а ® 1, если наоборот, то а ® 0.

Рис. 5. Модель прогноза объёма продаж

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

Определяем прогнозное значение модели:

F м t = 1 924,92 + 162,44 =2087 ± 7,8 (руб.)

Фактическое значение объёма продаж в предыдущем году (F ф t-1) составило 2 361руб. Принимаем коэффициент сглаживания 0.8. Получим прогнозное значение объёма продаж:

F пр t = 0,8*2 361 + (1-0.8) *2087 = 2306,2 (руб.)

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

Дмитриев Михаил Николаевич, заведующий кафедрой экономики и предпринимательства Нижегородского архитектурно-строительного университета (ННГАСУ), доктор экономических наук, профессор.
Адрес: 603000, Н. Новгород, ул. Горького, д. 142а, кв. 25.
Тел. 37-92-19 (дом) 30-54-37 (раб.)

Кошечкин Сергей Александрович, кандидат экономических наук, ст. преподаватель кафедры экономики и предпринимательства Нижегородского архитектурно-строительного университета (ННГАСУ).
Адрес: 603148, Н. Новгород, ул. Чаадаева, д. 48, кв. 39.
Тел. 46-79-20 (дом) 30-53-49 (раб.)

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

Вместо громоздких формул для прогноза спроса на продукцию мы используем один график в Excel, который строим исходя из данных о продажах компании. Алгоритм вывели самостоятельно, опираясь на советы знакомых бизнесменов и материалы из интернета. С помощью графика прогнозируем продажи на месяц, несколько месяцев или год. Чтобы повторить опыт, вам потребуется версия Excel 2003–2016 года. Кроме того, в конце статьи вы найдете альтернативный способ, который позволит построить прогноз за несколько минут. Однако он подходит исключительно для версии Excel 2016 года.

Ша г 1. Чтобы прогнозировать спрос на товары, собираем данные о продажах компании

Чтобы приступить к анализу, вам понадобятся данные о продажах компании за весь период ее существования. Чем больше информации, тем точнее прогноз. У нас, к примеру, есть сведения о продажах с января 2013 года по август 2015-го. Заносим их в таблицу (рисунок 1).

Лучшая статья месяца

Мы подготовили статью, которая:

✩покажет, как программы слежения помогают защитить компанию от краж;

✩подскажет, чем на самом деле занимаются менеджеры в рабочее время;

✩объяснит, как организовать слежку за сотрудниками, чтобы не нарушить закон.

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

Шаг 2. Делаем прогноз спроса на продукцию на заданный период

Чтобы спрогнозировать продажи, к примеру, на месяц или на будущий год, используем функцию «ПРЕДСКАЗ» в Excel. Функция основана на линейной регрессии и предназначена для прогнозирования продаж, потребления товара и пр.

В ячейку C34 записываем функцию:

ПРЕДСКАЗ(x; известные_значения_у; известные_значения_х),

х - дата, значение для которой необходимо предсказать (ячейка A34);

Шаг 3. Делаем расчет коэффициента сезонности для прогноза спроса

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

=(($B$2:$B$13+$B$14:$B$25)/СУММ($B$2:$B$25))*12

Далее копируем формулу и вставляем в ячейки F2:F13 как формулу массива. Завершаем ввод сочетанием клавиш: Ctrl+Shift+Enter. Если этого не сделать, функция вернет значение ошибки #ЗНАЧ! В результате для января получим коэффициент 0,974834224106574, для февраля - 0,989928632237843 и т. д. Для наглядности можно назначить ячейкам процентный формат. Правой кнопки мыши выбираем «Формат ячеек», затем вкладку «Число» и далее вкладку «Процентный, два знака после запятой».

• Сезонные спады в бизнесе: 3 способа вытянуть продажи

Шаг 4. Корректируем прогноз спроса на продукцию, учитывая сезонность

Добавим рассчитанные коэффициенты в имеющуюся функцию «ПРЕДСКАЗ» (ячейки C34:C45):

Чтобы скорректировать продажи, учитывая коэффициент, используем функцию «ИНДЕКС» (рисунок 2).

Первым аргументом в функции указываем ссылку на 12 ячеек с коэффициентами сезонности ($F$2:$F$13), вторым - номер месяца, чтобы вернуть коэффициент для нужного месяца (для этого используем функцию «месяц», которая возвращает только номер месяца из указанной даты). Для сентября 2015 года формула индекса выглядит так:

ИНДЕКС($F$3:$F$14;МЕСЯЦ(A35))

Чтобы скорректировать прогноз, нужно значение «ИНДЕКС» умножить на значение «ПРЕДСКАЗ», которое рассчитывали в шаге 2. Вот что мы получим:

ПРЕДСКАЗ(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)

Распространяем функцию на дальнейшие периоды и получаем скорректированный прогноз с учетом сезонности в ячейках C34:С45 (рисунок 1).

Шаг 5. Рассчитываем отклонение и строим два сценария

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

Верхние и нижние границы коридора спроса строим по формуле (ячейка G2 на рисунке 1):

ДОВЕРИТ(0,05 (АЛФА); СТАНДОТКЛОН(C34:C45); СЧЕТ(C34:C45)),

«ДОВЕРИТ» возвращает доверительный интервал, используя нормальное распределение. Функция учитывает колебания продаж компании, включая сезонные.

«АЛФА» - уровень значимости для вычисления доверительного уровня. Показатель 0,05 означает, что мы получим прогноз с точностью 95%.

«СТАНДОТКЛОН» - стандартное отклонение генеральной совокупности. Показывает, насколько прогнозируемые продажи отличаются от реальных.

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

Чтобы получить оптимистичный и пессимистичный сценарии, в ячейки D34 и D35 записываем формулы (рисунок 1).

Оптимистичный: =$C34+$G$2 (прибавляем к сумме прогноза сумму рассчитанного доверительного интервала)

Пессимистичный: =$C34–$G$2 (вычитаем из суммы прогноза сумму доверительного интервала)

Чтобы по полученным данным построить график, в ячейки C33, D33 и E33 копируем значения из ячейки B33. Далее выделяем все данные (A1:E45), переходим на вкладку «Вставка», находим вкладку «Диаграммы» и затем вкладку «График». В итоге получаем график с коридором спроса (рисунок 3).

Вывод. Построив коридор спроса, внимательно следим за продажами в новом году. В 99 % случаев они развиваются в рамках коридора. Если нет - анализируем продажи еще раз и строим новый график.

• Как регулярное изучение спроса повышает динамику продаж на 648%

Мнение эксперта

Метод эффективен для прогнозирования продаж малого количества SKU

Максим Люлин,

генеральный директор «Актион-пресс»

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

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

Мнение эксперта

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

Кирилл Чихачев,

генеральный директор «МЦФЭР-пресс»

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

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

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

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

Методы прогнозирования продаж

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

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

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

• Сколько товара с нерегулярным спросом надо приобрести для поддержания разумного уровня сервиса?

Чрезмерные запасы дорогостоящих товаров с нерегулярным спросом приведут, в лучшем случае, к «закапыванию» в склад большого объема оборотных средств, которые могли быть использованы для других целей. Или к образованию «мёртвых остатков» или неликвидов - в случае, когда речь идет о товарных позициях, коллекции которых обновляются ежегодно: дорогой электроинструмент, крупная бытовая техника премиум-класса, предметы люксовой категории, продаваемые наряду с обычными позициями.

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

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

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

Допустим, время поставки товара с момента его заказа у поставщика до прихода на склад составляет четыре дня, а текущий остаток на складе - 1 штука. Количество проданных наименований в заданный период - 30 штук.

• В каком количестве необходимо произвести закупку товара сейчас с учетом срока поставки товара?

При расчете на основе средних продаж мы бы получили значение средней реализации товара в размере: 30 штук/31 дней = 0,97 штуки в день, а объем продаж за время поставки составил бы около 4 единиц, точнее 0,97 штуки*4 дня = 3,9 штуки.

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

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

Проведенный выше анализ на основе расчета средних продаж не отвечает на эти вопросы.

Поэтому для прогнозирования нерегулярных продаж крайне важно использовать специальные методы, позволяющие производить анализ нерегулярных событий. Сравнительно недавно стали разрабатываться методы на основе так называемой Bootstrapping-статистики. Одним из таких методов, используемых при анализе нерегулярных и разреженных рядов, является метод под названием Bootstrapping Reaction Time (BRT)*.

Отличие BRT-метода от расчета средних состоит в определении наиболее вероятного объема продаж за период поставки заказа, а не расчета среднего дневного объема продаж. В нашем случае этот период поставки составляет четыре дня.

• Какой вариант прогноза продаж наиболее приемлем, исходя из имеющихся данных?

Для поиска ответа составим таблицу всех возможных вариантов на основании имеющихся данных. Для этого разбиваем наш ряд по порядку на периоды реакции (сроки поставки заказа): сначала с 1 по 4 день, потом с2 по 5, потом с 3 по 6 и т.д. - всего 28 возможных вариантов.

В крайней правой колонке мы получили множество вариантов того, какой объем товара может быть продан за выбранный промежуток времени (четыре дня) - получили разброс от 0 до 11 штук. Как понять, какое из этих значений наиболее отвечает нашим требованиям? Для этого составим частотную гистограмму - она покажет, как часто одно или другое значение встречается в выборке:

• Скольким клиентам наша компания готова обеспечить безусловную доступность товара?

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

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

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

• Какой процент клиентов мы можем обслужить - продать товар, отбросив фактор наличия на складе?

Как правило, это значение колеблется в районе 80-91%. Для нашего примера остановимся на уровне доступности - 80%. Оставшихся клиентов - 20% - мы «отбрасываем», считая, что для них мы не готовы хранить большие запасы товара на складе и не будем учитывать в плане закупок.

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

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

Для нашей выборки это значение равняется 8 штукам, что покроет требование 21 из 28 возможных исходов (если бы мы выбрали уровень доступности 70/10, то это было бы значение 5 штук, что покрывало бы 20 возможных исходов из 28 возможных).

В управленческой логике, найденное нами значение в 8 штук можно интерпретировать следующим образом: при обслуживании 8 из 10 клиентов, в течение 4 дней они купят суммарно меньше 8 штук товара, а закупка будет равняться 8 - 1 = 7 штук. Этот результат существенно отличается от значения, полученного при расчете «простой средней».

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