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

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

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

Цели прогнозирования объема продаж

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

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

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

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

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

Объема продаж можно разделить на три основные группы:

Методы экспертных оценок;
и прогнозирования временных рядов;
казуальные (причинно-следственные) методы.

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

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

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

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

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

Необходимо разделять прогнозы на долгосрочные (на 1, 3, 5 или больше лет) и кратко- или среднесрочные (неделя, месяц, квартал). Прогнозы на более длинные периоды обычно менее точные (хотя и не всегда). Это понятно, ведь больше факторов могут на протяжении длительного периода времени скорректировать ожидаемые результаты в ту или другую сторону. Однако вполне реально сделать точные прогнозы деятельности вашего предприятия для любого промежутка времени.

Точный прогноз – это прогноз, который имеет отклонение от реальных объемов продаж в пределах 10% в большую или меньшую сторону. Например, вы спрогнозировали, что за период 3 месяцев продадите продукции в размере 1000 шт. В конце периода вы получили результат 900, или же 1100 шт. или любую цифру в этом промежутке. Такой прогноз можно считать точным и качественным. Если отклонения существенны (вместо прогнозируемой цифры 1000 шт. получен результат – 500 шт.) – это свидетельствует о некорректном, слишком завышенном прогнозе, или же о форс-мажорных обстоятельствах, которые повлекли за собой резкое падение объемов продаж.

Как построить точный прогноз

Эта работа состоит из нескольких этапов:

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

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

Пример: вы занимаетесь продажами безалкогольных напитков. В начале мая вы должны рассчитать прогноз продаж на июль. У вас есть данные по продажам за каждый месяц предыдущего года, в частности, в апреле (5 000 шт.) и июле (12 000 шт.) прошлого года, а также за апрель этого года (7 000 шт.). Для того, чтобы определить коэффициент сезонности, нужно получить соотношение продаж за июль прошлого года к количеству продаж в апреле того же года. Полученную цифру (коэффициент сезонности) нужно перемножить на данные по количеству продаж за апрель этого года. В результате получим прогноз продаж на июль, взвешенный на коэффициент сезонности: 12 000/5 000 = 2,4. 7 000*2,4 = 16 800 шт. – прогноз на июль. Если другие факторы, которые влияют на объем продаж, остались неизменными, этот прогноз будет очень точным.

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

Пример: данные свидетельствуют: при повышении цены на 1% спрос на вашу продукцию падает на 2,5%. Вы планируете в июне повысить цену на 10%, это повлечет падение спроса на 25%. В прошлом году в этот же период цена оставалась неизменной. Продажи апреля составили 400 шт., коэффициент сезонности – 3,0. Рассчитываем прогноз на июль: 400*3*(100%-25%)=900 шт.

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

Пример: раньше вы поставляли продукцию лишь в своем городе. Начиная со следующего месяца, вы начинаете сотрудничество с посредниками из других городов и открываете дополнительно 5 точек продаж. На данный момент 10 точек продаж сбывают 2 000 шт. продукции на месяц. Таким образом, ожидаемые продажи 15 точек дадут результат около 3 000 шт.

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

Пример: вы имеете коэффициенты сезонности и эластичности спроса на вашу продукцию. Допустимо, суммарные продажи позапрошлого года составили 10 000 шт., суммарные продажи в прошлом году составили 14 000 шт. с учетом коэффициента эластичности прогноз на прошлый год должен был составить 9 000 шт. Однако увеличение объемов сбыта позволило увеличить объемы продаж вдвое (вдвое расширили штат работников отдела продаж и открыли еще 2 точки продаж дополнительно до двух существующих, как и было до этого). Это увеличивает прогноз до 18 000 шт. Следовательно, фактическое отклонение составило 4 000 шт. из них 2 000 шт. не было продано по причине непредвиденные обстоятельств – форс-мажор (проблемы с поставщиками сырья на протяжении двух месяцев). Отклонение, связанное с влиянием внешних факторов, составило 2 000 шт. (18 000 – 14 000 – 2 000). Коэффициент влияния составит таким образом: 1-(2 000 влияние внешних факторов /18 000 прогноз -2 000 форс-мажор)=0,875

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

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

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

Вместо громоздких формул для прогноза спроса на продукцию мы используем один график в 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, а на количество месяцев внутри отрезка. Так вы более точно рассчитаете ежемесячный прирост продаж.

На сегодняшний день наука достаточно далеко продвинулась в разработке технологий прогнозирования. Специалистам хорошо известны методы нейросетевого прогнозирования, нечёткой логики и т.п. Разработаны соответствующие программные пакеты, но на практике они, к сожалению, не всегда доступны рядовому пользователю, а в то же время многие из этих проблем можно достаточно успешно решать, используя методы исследования операций, в частности имитационное моделирование, теорию игр, регрессионный и трендовый анализ, реализуя эти алгоритмы в широко известном и распространённом пакете прикладных программ 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 (раб.)

После моего прихода в компанию «Избёнка», осуществляющую розничную торговлю натуральной молочной продукцией, я думал, что помесячное прогнозирование остались для меня – в прошлом. Ведь из-за того, что стратегическим принципом компании является торговля только натуральной молочной продукцией, потребность в прогнозировании больше недели – отпадает. Всё дело в коротких сроках годности такой продукции – обычно это 3-5 дней. Однако мне пришлось вспоминать прошлые навыки, когда я столкнулся с необходимостью делать заказ на сыр. Дело в том, что он вызревает как раз около месяца. А чтобы через месяц получить у поставщика нужный объём, мне нужно уже сейчас сказать ему, сколько мне понадобится, чтобы он запустил в производственный процесс необходимое для этого количество сырья. А в случае поставок с большим сроком реагирования – будь то: из-за длинного транспортного плеча или трудоёмкого процесса производства возникает необходимость прогнозировать спрос на длительные сроки. Срок реагирования – это время от момента, когда приняли решение о необходимости закупки позиции, и до момента, когда её можно использовать на производстве или отпускать клиенту. При этом в компаниях обычно осуществляется помесячное планирование, поэтому и прогноз желательно иметь в разбивке по месяцам. Выполнение этого же условия требуется при револьверных поставках – то есть таких поставках, когда период между ними меньше срока реагирования:

	январь	февраль	март	апрель	май	июнь
поставка №1	отгрузка	доставка	приёмка
поставка №2		отгрузка	доставка	приёмка
поставка №3			отгрузка	доставка	приёмка
поставка №4				отгрузка	доставка	приёмка

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

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

Упрощённый классический метод.

Первоначально необходимо определить общую динамику продаж: то есть для имеющегося ряда прошлого спроса S i надо построить линейный тренд – долговременную тенденцию изменения временного ряда, выражаемую прямой линией. В Microsoft Excel его уравнение можно получить, добавив на диаграмму временного ряда линейный тренд, а значение тренда для любого месяца Т i – используя функцию ТЕНДЕНЦИЯ. Именно благодаря этим значениям мы сможем рассчитать коэффициенты сезонности K i для каждого i -того месяца в прошлом. Для этого надо разделить значение фактического спроса за каждый месяц на значение линейного тренда за этот же месяц:

Тогда при наличии статистики хотя бы за два-три года появляется возможность рассчитать коэффициенты сезонности для каждого месяца года K m , где: К 1 – коэффициент сезонности января, К 2 – коэффициент сезонности февраля, К 3 – коэффициент сезонности марта, и так далее... Делается это за счёт усреднения всех полученных коэффициентов сезонности за все года для соответствующего месяца:

,

где L m – количество соответствующих месяцев в истории спроса.

Теперь, когда у нас есть эти коэффициенты, мы можем получить прогноз спроса P i на любой будущий i -тый месяц, умножив соответствующий этому месяцу коэффициент сезонности K m на значение тренда Т i для этого месяца: P i = K m · T i .

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

Индуктивный метод Разгуляева.

Данный метод был разработан мной для вычисления прогноза спроса в случаях, когда расчёт по классическому методу занимает слишком много времени из-за большого объёма информации или, вообще, невозможен из-за того, что тренд уходит в минус. Впоследствии он был реализован в нескольких автоматизированных системах, включая "Invertor" для "Эксель" и "Прогноз продаж" для "1С" (http://www.forecastsupply.ru/). Формула расчёта может показаться сначала сложной:

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

Но если мы обратим внимание на условие после каждой формулы в фигурной скобке, то увидим, что из всего этого каскада формул нам нужна только одна – та, которая подходит под наш объём имеющейся статистики прошлого спроса. Причём каждая формула состоит только из сложения, умножения и деления. Значок ∑ S i – просто означает, что нам надо просуммировать все значения прошлого спроса, начиная с месяца, номер которого указан снизу этого значка, и заканчивая месяцем, номер которого указан сверху этого значка. Таким образом, получается, что и для понимания, и для реализации в корпоративной информационной системе оказывается проще именно этот метод. И если численные методы нахождения тренда в корпоративной информационной системе компании могут реализовать только программисты со специальным математическим образованием, да и то – для них это будет задачка на неделю-две, то уж сложение, умножение и деление – вам внедрит в течение суток любой программист!.. Да и считаться данный алгоритм будет на порядок быстрее.

Кроме этого у данного метода есть ещё ряд существенных плюсов – он никогда не будет прогнозировать отрицательные значения спроса при положительной прошлой статистике в отличие от классического метода. А ещё он – гибче, то есть быстрее реагирует на проявления динамики спроса. При этом, как и классический метод, индуктивный метод Разгуляева учитывает: как общую динамику спроса, причём, не привязываясь к линейности тренда, так и повторяющиеся из года в год сезонные влияния. Единственный его «недостаток» заключается в том, что вы не можете посчитать прогноз спроса на любой месяц, не посчитав предварительно прогноз спроса на все предыдущие месяца. То есть, если сейчас закончился январь, и вы хотите спрогнозировать спрос в мае, то вам сначала надо будет по имеющейся статистике спрогнозировать спрос в феврале, затем внести это значение в статистику, и на её основании спрогнозировать спрос в марте. После этого по такой же схеме спрогнозировать спрос в апреле и только затем в мае. Однако на практике нам редко нужно прогнозировать спрос через полгода, но не прогнозировать спрос в следующем месяце, поэтому данный недостаток – не так критичен. Ещё одну проблему в применении данного метода – возможность равенства нулю одного из знаменателей в формуле, легко решает переход на формулу строчкой выше, где диапазон суммирования в знаменателе будет – значительно больше, и такой ситуации точно не возникнет. Ачтобы лучше разобраться с формулами, можно скачать Excel -файлы с примерами реализации обоих методов по ссылке:

Оценка точности прогноза.

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

где: P – это прогноз, а S – факт за тот же месяц.

Однако когда спрашиваешь пользователя этой формулы: «А чему равна ошибка, если факт равен нулю?» – то он попадает в понятное затруднение, ведь на ноль делить нельзя. Некоторые отвечают, что в таком случае D = 100% – мол, мы полностью ошиблись. Однако простой пример показывает, что такой ответ тоже – не верен:

вариант	прогноз	факт	ошибка прогноза
№1			100%
№2			300%
№3

Оказывается, что в варианте развития событий №2, когда мы лучше угадали спрос, чем в варианте №1, ошибка по данной формуле оказалась – больше. То есть ошиблась уже сама формула. Есть и другая проблема, если мы посмотрим на варианты №2 и №3, то увидим, что имеем дело с зеркальной ситуацией в прогнозе и факте, а ошибка при этом отличается – в разы!.. То есть при такой оценке ошибки прогноза нам лучше его заведомо делать менее точным, занижая показатель – тогда ошибка будет меньше!.. Хотя понятно, что чем точнее будет прогноз – тем лучше будет и закупка. Поэтому для расчёта ошибки советую использовать следующую формулу:

.

№1

100%

№2

№3

Как мы видим в варианте №1 ошибка становится равной 100%, причём это уже – не наше предположение, а чистый расчёт, который можно доверить машине. Зеркальные же варианты №2 и №3 – имеют и одинаковую ошибку, причём эта ошибка меньше ошибки самого плохого варианта №1. Единственная ситуация, когда данная формула не сможет дать однозначный ответ – это равенство знаменателя нулю. Но максимум из прогноза и факта равен нулю, только когда они оба равны нулю. В таком случае получается, что мы спрогнозировали отсутствие спроса, и его, действительно, не было – то есть ошибка тоже равна нулю – мы сделали совершенно точное предсказание.

Получение истории спроса.

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

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

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

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

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

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

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

Валерий Разгуляев

Перепечатка и перепостинг статьи вместе с этим текстом, указанием автора, и ссылки на перво

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

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

Способ 1: линия тренда

Одним из самых популярных видов графического прогнозирования в Экселе является экстраполяция выполненная построением линии тренда.

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

Способ 2: оператор ПРЕДСКАЗ

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

ПРЕДСКАЗ(X;известные_значения_y;известные значения_x)

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

«Известные значения y» — база известных значений функции. В нашем случае в её роли выступает величина прибыли за предыдущие периоды.

«Известные значения x» — это аргументы, которым соответствуют известные значения функции. В их роли у нас выступает нумерация годов, за которые была собрана информация о прибыли предыдущих лет.

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

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

Давайте разберем нюансы применения оператора ПРЕДСКАЗ на конкретном примере. Возьмем всю ту же таблицу. Нам нужно будет узнать прогноз прибыли на 2018 год.

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

Способ 3: оператор ТЕНДЕНЦИЯ

Для прогнозирования можно использовать ещё одну функцию – ТЕНДЕНЦИЯ . Она также относится к категории статистических операторов. Её синтаксис во многом напоминает синтаксис инструмента ПРЕДСКАЗ и выглядит следующим образом:

ТЕНДЕНЦИЯ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

Как видим, аргументы «Известные значения y» и «Известные значения x» полностью соответствуют аналогичным элементам оператора ПРЕДСКАЗ , а аргумент «Новые значения x» соответствует аргументу «X» предыдущего инструмента. Кроме того, у ТЕНДЕНЦИЯ имеется дополнительный аргумент «Константа» , но он не является обязательным и используется только при наличии постоянных факторов.

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

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

Способ 4: оператор РОСТ

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

РОСТ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

Как видим, аргументы у данной функции в точности повторяют аргументы оператора ТЕНДЕНЦИЯ , так что второй раз на их описании останавливаться не будем, а сразу перейдем к применению этого инструмента на практике.

Способ 5: оператор ЛИНЕЙН

Оператор ЛИНЕЙН при вычислении использует метод линейного приближения. Его не стоит путать с методом линейной зависимости, используемым инструментом ТЕНДЕНЦИЯ . Его синтаксис имеет такой вид:

ЛИНЕЙН(Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

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

Как видим, прогнозируемая величина прибыли, рассчитанная методом линейного приближения, в 2019 году составит 4614,9 тыс. рублей.

Способ 6: оператор ЛГРФПРИБЛ

Последний инструмент, который мы рассмотрим, будет ЛГРФПРИБЛ . Этот оператор производит расчеты на основе метода экспоненциального приближения. Его синтаксис имеет следующую структуру:

ЛГРФПРИБЛ (Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

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

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

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