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

Результатов различных процессов на основе данных предыдущих периодов времени. В этой статье мы рассмотрим прогнозирование в Excel при помощи очень мощного и удобного инструмента — встроенных статистических функций ЛИНЕЙН и ЛГРФПРИБЛ.

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

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

Что можно научиться прогнозировать? Очень многое! В принципе, можно научиться прогнозировать любые самые разнообразные результаты процессов в повседневной жизни и работе. Всегда, когда возникает вопрос: «А что будет, если…?» зовите на помощь Excel, рассчитывайте прогноз и проверяйте его достоверность!

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

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

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

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

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

Прежде чем начать решать практическую задачу, хочу обратить внимание на один весьма важный момент. Научиться выполнять прогнозирование в Excel с использованием вышеназванных функций ЛИНЕЙН и ЛГРФПРИБЛ технически не очень сложно. Гораздо сложнее научиться анализировать процесс, приводящий к результату и находить простые факторы, влияющие на него. При этом желательно (но не обязательно) понимать — КАК зависит результат (функция) от каждого из факторов (переменных). Линейная это зависимость или может быть степенная или какая-нибудь другая? Понимание физического смысла процесса поможет вам правильно выбрать переменные. Подбор аппроксимирующей функции следует производить при полном понимании логики и смысла процесса, приводящего к результату.

Подготовка к прогнозированию в Excel.

1. Четко формулируем название и единицу измерения интересующего нас результата процесса. Это и есть искомая функция — y , аналитическое выражение которой мы будем определять с помощью MS Excel.

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

2. Производим анализ процесса и выявляем факторы — аргументы функции — x 1 , x 2 , ... x n — наиболее сильно на наш взгляд влияющие на результат – значения функции y . Внимательно назначаем единицы измерений для переменных.

В примере это:

x 1 — суммарная длина всех прокатных профилей в метрах, из которых изготавливается заказ

x 2 — общая масса всех прокатных профилей в килограммах

x 3 — суммарная площадь всех листов в метрах квадратных

x 4 — общая масса всех листов в килограммах

3. Собираем статистику – фактические данные – в виде таблицы.

В примере – это фактические данные о металлопрокате и фактических сроках выполненных ранее заказов.

Очень важно при выборе переменных x 1 , x 2 , ... x n учесть их доступность. То есть, значения этих факторов должны быть у вас в виде достоверных статистических данных. Очень желательно, чтобы получение значений статистических данных было простым, понятным и нетрудоемким процессом.

Переходим непосредственно к рассмотрению примера.

Небольшой участок завода производит строительные металлоконструкции. Входным сырьем является листовой и профильный металлопрокат. Мощность участка в рассматриваемом периоде времени неизменна. В наличии есть статистические данные о сроках изготовления 13-и заказов (k =13) и количестве использованного металлопроката. Попробуем найти зависимость срока изготовления заказа от суммарной длины и массы профильного проката и суммарной площади и массы листового проката.

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

Примечательно, что найденная зависимость связывает в одной формуле параметры с различными единицами измерения. Это нормально. Найденные коэффициенты не являются безразмерными. Например, размерность коэффициента b – рабочие дни, а коэффициента m 1 – рабочие дни/м.

1. Запускаем MS Excel и заполняем ячейки B4...F16 таблицы Excel исходными статистическими данными. В столбцы пишем значения переменных x i и фактические значения функции y , располагая данные, относящиеся к одному заказу в одной строке.

2. Так как функции ЛИНЕЙН и ЛГРФПРИБЛ — функции выводящие результаты в виде массива , то их ввод имеет некоторые особенности. Выделяем область размером 5×5 ячеек — ячейки I9...M13. Количество выделенных строк всегда — 5, а количество столбцов должно быть равно количеству переменных x i плюс 1. В нашем случае это: 4+1=5.

3. Нажимаем на клавиатуре клавишу F2 и набираем формулу

в ячейках I9...M13: =ЛИНЕЙН(F4:F16;B4:E16;ИСТИНА;ИСТИНА)

4. После набора формулы необходимо для ее ввода нажать сочетание клавиш Ctrl+Shift+Enter. (Знак «+» нажимать не нужно, в записи он означает, что клавиши нажимаются последовательно при удержании нажатыми всех предыдущих.)

5. Считываем результаты работы функции ЛИНЕЙН в ячейках I9...M13.

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

Общий вид уравнения аппроксимирующей функции y , представлен в объединенных ячейках I2...M2.

Значения коэффициентов b , m 1 , m 2 , m 3 , m 4 считываем соответственно

в ячейке M9: b =4,38464164

в ячейке L9: m 1 =0,002493053

в ячейке K9: m 2 =0,000101103

в ячейке J9: m 3 =-0,084844006

в ячейке I9: m 4 =0,002428953

6. Для определения расчетных значений функции y — срока изготовления заказа — вводим формулу

в ячейку G4: =$L$9*B4+$K$9*C4+$J$9*D4+$I$9*E4+$M$9 =5,0

y =b +m 1 *x 1 +m 2 *x 2 +m 3 *x 3 +m 4 *x 4

7. Копируем эту формулу во все ячейки столбца от G5 до G17 «протягиванием» и сверяем расчетные значения с фактическими. Совпадение очень хорошее!

8. Предварительные действия все выполнены. Уравнение аппроксимирующей функции y найдено. Пробуем выполнить прогнозирование в Excel срока изготовления нового заказа. Вписываем исходные данные.

8.1. Длину прокатных профилей по проекту x 1 в метрах пишем

в ячейку B17: 2820

8.2. Массу прокатных профилей x 2 в килограммах пишем

в ячейку C17: 62000

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

в ячейку D17: 110,0

8.4. Общую массу листового проката x 4 в килограммах вписываем

в ячейку E17: 7000

9. Расчетный срок изготовления заказа y в рабочих днях считываем

в ячейке G17: =$L$9*B17+$K$9*C17+$J$9*D17+$I$9*E17+$M$9 =25,4

Прогнозирование в Excel выполнено. На основе статистических данных мы рассчитали предположительный срок выполнения нового заказа — 25,4 рабочих дней. Остается выполнить заказ и сверить фактическое время с прогнозным.

Анализ результатов.

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

Обратимся к другим данным в массиве, которые вывела функция ЛИНЕЙН.

Во второй строке массива в ячейках I10…M10 выведены стандартные ошибки se 4 , se 3 , se 2 , se 1 , se b для расположенных выше в первой строке массива соответствующих коэффициентов уравнения аппроксимирующей функции m 4 , m 3 , m 2 , m 1 , b .

В третьей строке в ячейке I11 выведено значение коэффициента множественной детерминации r 2 , а в ячейке J11 — стандартная ошибка для функции — se y .

В четвертой строке в ячейке I12 находится, так называемое F -наблюдаемое значение, а в ячейке J12 — df – количество степеней свободы.

Наконец, в пятой строке в ячейках I13 и J13 соответственно размещены ss reg — регрессионная сумма квадратов и ss resid — остаточная сумма квадратов.

На что следует в регрессионной статистике обратить особое внимание? Что для нас наиболее важно?

1. На сколько достоверно прогнозирует срок изготовления полученное уравнение функции y ? При высокой достоверности аппроксимации значение коэффициента детерминации r 2 близко к максимуму — к 1! Если r 2 <0,7…0,8, то различия между фактическими и расчетными значениями функции будут значительными, и применять полученную формулу для прогнозирования, скорее всего, нельзя.

В нашем примере r 2 =0,999388788. Это означает, что найденное уравнение функции y чрезвычайно точно определяет срок изготовления заказа по четырем входным данным. Вышесказанное подтверждается сравнительным анализом значений в ячейках F4…F16 и G4…G16 и указывает на существенную зависимость между сроком изготовления и данными о входящем в заказ металлопрокате.

2. Определим важность и полезность каждой из четырех переменных x 1 , x 2 , x 3 , x 4 в полученной формуле с помощью, так называемой, t -статистики.

2.1. Рассчитываем t 4 , t 3 , t 2 , t 1 , соответственно

в ячейке I16: t 4 = I9/I10 =26,44474886

в ячейке J16: t 3 = J9/J10 =-11,79198416

в ячейке K16: t 2 = K9/K10 =3,76748771

в ячейке L16: t 1 = L9/L10 =3,949105515

t i = m i / se i

2.2. Вычисляем двустороннее критическое значение t крит с уровнем достоверности α =0,05 (предполагается 5% ошибок) и количеством степеней свободы df =8

в ячейке M16: t крит =СТЬЮДРАСПОБР(0,05; J12) =2,306004133

Так как для всех t i справедливо неравенство | t i |> t крит , то это означает, что все выбранные переменные x i полезны при расчете сроков изготовления заказов – y .

Наиболее значимой переменной при прогнозировании в Excel сроков изготовления заказов y является x 4 , так как | t 4 |>| t 3 |>| t 1 |>| t 2 | .

3. Не является ли случайным полученное значение коэффициента детерминации r 2 ? Проверим это, используя F -статистику (распределение Фишера), которая характеризует «неслучайность» высокого значения коэффициента r 2 .

3.1. F -наблюдаемое значение считываем

в ячейке I12: 3270,188104

3.2. F -распределение имеет степени свободы v 1 и v 2 .

v 1 = k — df -1 =13-8-1=4

v 2 = df =8

Рассчитаем вероятность получения значения F -распределения большего, чем F -наблюдаемое

в ячейке I12: =FРАСП(I12;4;J12) =6,97468*10 -13

Так как вероятность получения большего значения F -распределения, чем наблюдаемое чрезвычайно мала, то из этого следует вывод — найденное уравнение функции y можно применять для прогнозирования сроков изготовления заказов. Полученное значение коэффициента детерминации r 2 не является случайным!

Заключение.

Применение функции MS Excel ЛГРФПРИБЛ почти не отличается от работы с функцией ЛИНЕЙН кроме вида уравнения искомой функции, которое принимает для рассмотренного примера следующий вид:

y =b *(m 1 x1 ) *(m 2 x2 )*(m 3 x3 )*(m 4 x4 )

Статистика множественной регрессии, которую рассчитывает функция ЛГРФПРИБЛ, базируется на линейной модели:

ln (y )=x 1 *ln (m 1 )+x 2 *ln (m 1 )...+x n *ln (m n )+ln (b )

Это означает, что значения, например, se i нужно сравнивать не с m i , а с ln (m i ) . (Подробнее об этом почитайте в справке MS Excel.)

Если в результате использования функции ЛГРФПРИБЛ коэффициент детерминации r 2 окажется ближе к 1, чем при использовании функции ЛИНЕЙН, то применение аппроксимирующей функции вида

y =b *(m 1 x 1 )*(m 2 x 2 )…*(m n x n ),

несомненно, является более целесообразным.

Если прогнозное значение функции y находится вне интервала фактических статистических значений y , то вероятность ошибки прогноза резко возрастает!

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

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

Отзывы, вопросы и замечания, уважаемые читатели, пишите в комментариях внизу страницы.

ПРОШУ уважающих труд автора СКАЧАТЬ файл ПОСЛЕ ПОДПИСКИ на анонсы статей!

Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (39)
Баги и глюки Excel (3)

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

Скачать файл, используемый в видеоуроке:

Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}

Имеются данные о деятельности предприятия за ретроспективный период (таблица 2.2).

Требуется:

сделать прогноз на следующие три года с использованием метода среднегодовых темпов роста и регрессионного анализа;

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

Таблица 2.2 –Значение грузооборота железной дороги

Грузооборот	Значения по годам, млн. т-км
Грузооборот

1 вариант. Определим прогнозные значения по среднегодовым темпам изменения показателей по формуле 2.1. Для местного грузооборота среднегодовой темп изменения значений будет равен

Прогнозные значения показателей при этом подходе определяются по формуле (2.2). Тогда для местного грузооборота значение на первый плановый год составит

11312,12 млн. т-км.

Остальные значения определяются аналогично. Результаты расчетов сведены в таблицу 2.3.

Таблица 2.3 –Расчет прогнозных значений грузооборота

Грузооборот, млн. т-км	Значение на 7 год	Прогноз на годы
Грузооборот, млн. т-км	Значение на 7 год

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

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

Таблица 2.4 – Значения для расчета коэффициента регрессии

Тогда система уравнений (2.4) примет вид

После решения данной системы получаем значения =8243,143 и=410,607;а уравнение регрессии примет вид

V p = 8243,143+410,607·t

где t - год, на который делается прогноз: t = 8, 9 и 10 год.

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

Таблица 2.5 –Прогноз значений грузооборота

Грузооборот, млн. т-км	Коэффициенты	Прогноз на годы
Грузооборот, млн. т-км

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

Таблица 2.6 –Сравнение результатов прогноза грузооборота

прогноза		Значения по годам
прогноза


	значений

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

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

Рисунок 2.1 – Сравнение результатов прогноза объемов грузооборота

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Необходимо разделять прогнозы на долгосрочные (на 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

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

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