1. Основные методические положения.

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

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

В сглаженном виде новый прогноз (для периода времени t+1) можно представлять как взвешенное среднее последнего наблюдения величины в момент времени t и ее прежнего прогноза на этот же период t. Причем вес α присваивается наблюдаемому значению, а вес (1- α) – прогнозу; при этом полагается, что 0< α<1. Это правило в общем виде можно записать следующим образом.

Новый прогноз = [α*(последнее наблюдение)]+[(1- α)*последний прогноз]

где - прогнозируемое значение на следующий период;

α – постоянная сглаживания;

Y t – наблюдение величины за текущий период t;

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

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

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

Таблица 2.1

Сравнение влияния разных значений постоянных сглаживания

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

2. Практический пример проведения экспоненциального сглаживания.

Представлены данные компании по объему продаж (тыс. шт.) за семь лет, постоянная сглаживания взята равной 0,1 и 0,6. Данные за 7 лет составляют тестовую часть; по ним необходимо оценить эффективность каждой из моделей. Для экспоненциального сглаживания рядов начальное значение берется равным 500 (первое значение фактических данных или среднее значение за 3 -5 периодов записывается в сглаженное значения за 2 квартал).

Таблица 2.2

Исходные данные

Время	Действительное значение (фактическое)	Сглаженное значение	Ошибка прогноза
год	квартал	0,1	0,1
Excel	по формуле
			#Н/Д		0,00
		500,00		-150,00
		485,00	485,00	-235,00
		461,50	461,50	-61,50
			455,35	455,35	-5,35
		454,82	454,82	-104,82
		444,33	444,33	-244,33
		419,90	419,90	-119,90
			407,91	407,91	-57,91
		402,12	402,12	-202,12
		381,91	381,91	-231,91
		358,72	358,72	41,28
			362,84	362,84	187,16
		381,56	381,56	-31,56
		378,40	378,40	-128,40
		365,56	365,56	184,44
			384,01	384,01	165,99
		400,61	400,61	-0,61
		400,55	400,55	-50,55
		395,49	395,49	204,51
			415,94	415,94	334,06
		449,35	449,35	50,65
		454,41	454,41	-54,41
		448,97	448,97	201,03
			469,07	469,07	380,93

На рис. 2.1 представлен прогноз на основе экспоненциального сглаживания с постоянной сглаживания, равной 0,1.

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

Решение в Excel.

1. Выберите меню «Сервис» – «Анализ данных». В списке «Инструменты анализа» выберите значение «Экспоненциальное сглаживание». Если в меню «Сервис» нет анализа данных, то необходимо установить «Пакет анализа». Для этого найти в «Параметрах» пункт «Настройки» и в появившемся диалоговом окне установить флажок на «Пакет анализа», нажать ОК.

2. На экране раскроется диалоговое окно, представленное на рис. 2.2.

3. В поле «входной интервал» введите значения исходных данных (плюс одна свободная ячейка).

4. Установите флажок «метки» (если в диапазоне ввода указаны названия столбцов).

5. Введите в поле «фактор затухания» значение (1-α).

6. В поле «входной интервал» введите значение ячейки, в которой хотели бы увидеть полученные значения.

7. Установите флажок «Опции» - «Вывод графика» для автоматического его построения.

Рис. 2.2. Диалоговое окно для экспоненциального сглаживания

3. Задание лабораторной работы.

Имеются исходные данные об объемах добычи нефтедобывающего предприятия за 2 года, представленные в таблице 2.3:

Таблица 2.3

Исходные данные

Проведите экспоненциальное сглаживание рядов. Коэффициент экспоненциального сглаживания примите равным 0,1; 0,2; 0,3. Полученные результаты прокомментируйте. Можно использовать статистические данные, представленные в приложении 1.

Тема 3. Сглаживание и прогнозирование временных рядов на основе трендовых моделей

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

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

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

3.1. Простые средние

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

где n число наблюдений.

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

3.2. Метод скользящих средних

Данный метод основан на представлении ряда в виде суммы достаточно гладкого тренда и случайного компонента. В основе метода лежит идея расчета теоретического значения на основе локального приближения. Для построения оценки тренда в точке t по значениям ряда из временного интервала рассчитывают теоретическое значение ряда. Наибольшее распространение в практике сглаживания рядов получил случай, когда все веса для элементов интервала равны между собой. По этой причине этот метод называют методом скользящих средних, так как при выполнении процедуры происходит скольжение окном шириной (2 m + 1) по всему ряду. Ширину окна обычно берут нечетной, так как теоретическое значение рассчитывается для центрального значения: количество слагаемых k = 2m + 1 с одинаковым числом уровней слева и справа от момента t.

Формула для расчета скользящей средней в этом случае принимает вид:

Дисперсия cкользящей средней определяется как σ 2 /k, где через σ 2 обозначена дисперсия исходных членов ряда, а k интервал сглаживания, поэтому чем больше интервал сглаживания, тем сильнее усреднение данных и менее изменчива выделяемая тенденция. Чаще всего сглаживание производят по трем, пяти и семи членам исходного ряда. При этом следует учитывать следующие особенности скользящей средней: если рассмотреть ряд с периодическими колебаниями постоянной длины, то при сглаживании на основе скользящей средней с интервалом сглаживания, равным или кратным периоду, колебания полностью устранятся. Нередко сглаживание на основе скользящей средней столь сильно преобразует ряд, что выделенная тенденция развития проявляется лишь в самых общих чертах, а более мелкие, но важные для анализа детали (волны, изгибы и т. д.) исчезают; после сглаживания мелкие волны могут иногда поменять направление на противоположное на месте «пиков» появляются «ямы», и наоборот. Все это требует осторожности в применении простой скользящей средней и заставляет искать более тонкие методы описания.

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

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

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

Здесь экспоненциальная средняя, заменяющая наблюдаемое значение ряда y t (в сглаживании участвуют все данные, полученные к текущему моменту t ), α параметр сглаживания, характеризующий вес текущего (самого нового) наблюдения; 0 < α <1.

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

Легко получить, что Последнее соотношение позволяет дать следующую интерпретацию экспоненциальной средней: если прогноз значения ряда y t , то разность есть погрешность прогноза. Таким образом, прогноз для следующего момента времени t + 1 учитывает ставшую известной в момент t ошибку прогноза.

Параметр сглаживания α является взвешивающим фактором. В случае, если α близко к единице, то в прогнозе существенно учитывается величина ошибки последнего прогнозирования. При малых значениях α прогнозируемая величина близка к предыдущему прогнозу. Выбор параметра сглаживания представляет собой достаточно сложную проблему. Общие соображения таковы: метод хорош для прогнозирования достаточно гладких рядов. В этом случае можно выбрать сглаживающую константу путем минимизации ошибки прогноза на один шаг вперед, оцененной по последней трети ряда. Некоторые специалисты не рекомендуют использовать большие значения параметра сглаживания. На рис. 3.1 показан пример сглаженного ряда методом экспоненциального сглаживания при α= 0,1.

Рис. 3.1. Результат экспоненциального сглаживания при α =0,1
(1 исходный ряд; 2 сглаженный ряд; 3 остатки)

3.4. Экспоненциальное сглаживание
с учетом тренда (метод Хольта)

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

В методе Хольта используются три расчетных формулы:

Экспоненциально сглаженный ряд (оценка текущего уровня)

(3.2)

Оценка тренда

(3.3)

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

(3.4)

где α, β постоянные сглаживания из интервала .

Уравнение (3.2) похоже на уравнение (3.1) для простого экспоненциального сглаживания за исключением члена, учитывающего тренд. Постоянная β нужна для сглаживания оценки тренда. В уравнении прогноза (3.3) оценка тренда умножается на число периодов р , на которое строится прогноз, а затем это произведение складывается с текущим уровнем сглаженных данных.

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

На рис. 3.2 приведен пример сглаживания ряда по методу Хольта при значениях α и β , равных 0,1.

Рис. 3.2. Результат сглаживания по методу Хольта
при α = 0,1 и β = 0,1

3.5. Экспоненциальное сглаживание с учетом тренда и сезонных вариаций (метод Винтерса)

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

Экспоненциально сглаженный ряд

(3.5)

Оценка тренда

(3.6)

Оценка сезонности

(3.7)

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

(3.8)

где α, β, γ постоянные сглаживания для уровня, тренда и сезонности, соответственно; s - длительность периода сезонного колебания.

Уравнение (3.5) корректирует сглаженные ряды. В этом уравнении член учитывает сезонность в исходных данных. После учета сезонности и тренда в уравнениях (3.6), (3.7) оценки сглаживаются, а в уравнении (3.8) делается прогноз.

Так же, как и в предыдущем способе, веса α, β, γ могут выбираться субъективно или путем минимизации ошибки прогнозирования. Перед применением уравнения (3.5) необходимо определить начальные значения для сглаженного ряда L t , тренда T t , коэффициентов сезонности S t . Обычно начальное значение сглаженного ряда принимается равным первому наблюдению, тогда тренд равен нулю, а коэффициенты сезонности устанавливаются равными единице.

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

Рис. 3.3. Результат сглаживания по методу Винтерса
при α = 0,1 ;β = 0,1; γ = 0,1 (1- исходный ряд; 2 сглаженный ряд; 3 остатки)

3.6. Прогнозирование на основе трендовых моделей

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

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

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

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

где y i наблюдаемое значение; y i * теоретические значения модели; номер наблюдения.

При моделировании тенденции исходного временного ряда с помощью линейного тренда примем, что

Поделив первое уравнение на n , приходим к следующему

Подставив полученное выражение во второе уравнение системы (3.10), для коэффициента b * получим:

3.7. Проверка соответствия модели

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

Рис. 3.4. График линейной регрессии

Уравнение для этого случая имеет вид: у =1455,3 + 13,4 х . Визуальный анализ этого рисунка показывает, что для ряда наблюдений имеются значительные отклонения от теоретической кривой. График остатков показан на рис. 3.5.

Рис. 3.5. График остатков

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

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

где SS 0 , SS 1 , SS 2 определяют соответственно общую, регрессионную и остаточную суммы квадратов.

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

Каждая из этих сумм характеризуется соответствующим числом степеней свободы (ЧСС), которое определяет число единиц данных, независимых друг от друга. Иначе говоря, ЧСС связано с числом наблюдений n и числом вычисляемых по совокупности данных параметров. В рассматриваемом случае для расчета SS 0 определяется только одна постоянная (среднее значение), следовательно ЧСС для SS 0 составит (n – 1), ЧСС для SS 2 – (n – 2) и ЧСС для SS 1 составит n – (n – 1)=1 , так как в уравнении регрессии имеется n – 1 постоянных точек. Так же, как и суммы квадратов, ЧСС связаны соотношением

Суммы квадратов, связанные с разложением дисперсии, вместе с соответствующими ЧСС могут быть размещены в так называемой таблице анализа дисперсий (таблица ANOVA ANalysis Of VAriance) (табл. 3.1).

Таблица 3.1

Таблица ANOVA

Источник	Сумма квадратов		Средний квадрат
Регрессия			SS 2 / (n-2)

С помощью введенной аббревиатуры для сумм квадратов определим коэффициент детерминации как отношение суммы квадратов регрессии к общей сумме квадратов в виде

(3.13)

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

3.8. Регрессионная модель прогноза

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

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

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

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

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

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

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

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

Постановку задачи в этом случае можно сформулировать следующим образом: каким будет наилучший прогноз y T+1 при условии, что в модели y = a + bx параметры а и b оценены точно, а значение x T+1 известно.

Тогда прогнозное значение можно определить как

Ошибка прогноза при этом составит

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

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

Хотя а и b известны, ошибка прогноза появляется за счет того, что у T+1 может не лежать на линии регрессии из-за ошибки ε T+1 , подчиняющейся нормальному распределению с нулевым средним и дисперсией σ 2 . Для проверки качества прогноза введем нормализованную величину

Тогда можно определить 95 %-ный доверительный интервал в следующем виде:

где β 0,05 квантили нормального распределения.

Границы 95 %-ного интервала можно определить как

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

Чаще при построении линии регрессии и проверке качества прогноза надо оценивать не только параметры регрессии, но и дисперсию ошибки прогноза. Можно показать , что в этом случае дисперсия ошибки зависит от величины (), где среднее значение независимой переменной. Кроме того, чем больше длина ряда, тем точнее прогноз. Ошибка прогноза уменьшается, если значение X T+1 близко к средней величине независимой переменной, и, наоборот, при удалении от среднего значения прогноз становится менее точным. На рис. 3.6 показаны результаты прогноза с помощью уравнения линейной регрессии на 6 интервалов времени вперед вместе с доверительными интервалами.

Рис. 3.6. Прогноз по уравнению линейной регрессии

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

Рис.3.7. График остатков

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

В табл. 3.2 приведены результаты прогноза (вторая колонка) вместе с доверительными 95 %-ными интервалами (нижним третья и верхним четвертая колонки соответственно).

Таблица 3.2

Результаты прогноза

3.9. Многомерная регрессионная модель

При многомерной регрессии данные для каждого случая включают значения зависимой переменной и каждой независимой переменной. Зависимая переменная y это случайная величина, связанная с независимыми переменными следующим соотношением:

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

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

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

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

Форма разбиения сумм квадратов в случае многомерной регрессии аналогична выражению (3.13), но соотношение для ЧСС будет следующим

Подчеркнем еще раз, что n представляет собой объем наблюдений, а k число переменных в модели. Общая вариация зависимой переменной состоит из двух составляющих: вариации, объясненной независимыми переменными через функцию регрессии, и необъясненной вариации.

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

Таблица 3.3

Таблица ANOVA

Источник	Сумма квадратов		Средний квадрат
Регрессия			SS 2 / (n-k-1)

В качестве примера многомерной регрессии воспользуемся данными из пакета Statistica (файл данных Poverty.Sta) Приведенные данные основаны на сравнении результатов переписи 1960 и 1970 гг. для случайной выборки из 30 стран. Названия стран были введены как названия строк, а названия всех переменных этого файла приведены ниже:

POP_CHNG изменение населения за 1960-1970 гг.;

N_EMPLD количество людей, занятых в сельском хозяйстве;

PT_POOR процент семей, живущих ниже уровня бедности;

TAX_RATE ставка налога;

PT_PHONE процент квартир с телефоном;

PT_RURAL процент сельского населения;

AGE средний возраст.

В качестве зависимой переменной выберем признак Pt_Poor , а в качестве независимых - все остальные. Рассчитанные коэффициенты регрессии между выделенными переменными приведены в табл. 3.4

Таблица 3.4

Регрессионные коэффициенты

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

Включение в правую часть только этих переменных обусловлено тем, что лишь эти признаки имеют значение вероятности р меньше, чем 0,05 (см. четвертый столбец табл. 3.4).

Библиография

Басовский Л. Е. Прогнозирование и планирование в условиях рынка. – М.: Инфра - М, 2003.
Бокс Дж., Дженкинс Г. Анализ временных рядов. Вып.1. Прогноз и управление. – М.: Мир, 1974.
Боровиков В. П., Ивченко Г. И. Прогнозирование в системе Statistica в среде Windows. – М.: Финансы и статистика, 1999.
Дюк В. Обработка данных на ПК в примерах. – СПб.: Питер, 1997.
Ивченко Б. П., Мартыщенко Л. А., Иванцов И. Б. Информационная микроэкономика. Часть 1. Методы анализа и прогнозирования. – СПб.: Нордмед-Издат, 1997.
Кричевский М. Л. Введение в искусственные нейронные сети: Учеб. пособие. – СПб.: СПб. гос. морской техн. ун-т, 1999.
Сошникова Л. А., Тамашевич В. Н., Уебе Г. и др. Многомерный статистический анализ в экономике. – М.: Юнити-Дана, 1999.

Насколько Forecast NOW! лучше модели Экспоненциального сглаживания (ES) вы можете увидеть на графике ниже. По оси X - номер товара, по оси Y - процентное улучшение качества прогноза. Описание модели, детальное исследование, результаты экспериментов читайте ниже.

Описание модели

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

Для сравнения прогнозирование проводилось на неделю вперед в течение 8 недель.

Что такое экспоненциально сглаживание?

Пусть ряд С представляет исходный ряд продаж для прогнозирования

С(1)- продажи в первую неделю, С (2) во второй и так далее.

Рисунок 1. Продажи по неделям, ряд С

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

S (t+1) = S(t) + α *(С(t) - S(t))

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

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

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

S(t+1) = (1 - α)* S(t) + α * С(t) .

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

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

Критерием может выступать точность прогнозирования на предыдущие периоды.

В своем исследовании мы рассмотрели модели экспоненциального сглаживания, в которых α принимает значения {0.2, 0.4, 0.6, 0.8}. Для сравнения с алгоритмом прогнозирования Forecast NOW! для каждого товара строились прогнозы при каждом α, выбирался наиболее точный прогноз. В действительности же, ситуация обстояла бы гораздо более сложная, пользователю не зная наперед точности прогноза нужно определиться с коэффициентом α, от которого очень сильно зависит качество прогноза. Вот такой замкнутый круг.

Наглядно

Рисунок 2. α =0.2 , степень экспоненциального сглаживания высокая, реальные продажи учитываются слабо

Рисунок 3. α =0.4 , степень экспоненциального сглаживания средняя, реальные продажи учитываются в средней степени

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

Рисунок 4. α =0.6 , степень экспоненциального сглаживания низкая, реальные продажи учитываются значительно

Можем видеть, что при α=0.8 ряд почти в точности повторяет исходный, а значит прогноз стремится к правилу «будет продано столько же, сколько и вчера»

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

Рисунок 5. α =0.8 , степень экспоненциального сглаживания крайне низкая, реальные продажи учитываются сильно

Примеры прогнозов

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

Рисунок 6. Прогноз метода экспоненциального сглаживания при α=0.2

Рисунок 7. Прогноз метода экспоненциального сглаживания при α=0.6

Давайте посмотрим, что получается при α = 1.0. Напомним, S - прогнозируемые (сглаженные) продажи, C - реальные продажи.

S(t+1) = (1 - α)* S(t) + α * С(t) .

S(t+1) = С(t) .

Продажи в t+1 день согласно прогнозу равны продажам в предыдущий день. Поэтому к выбору константы надо подходить с умом.

Сравнение с Forecast NOW!

Теперь рассмотрим данный метод прогнозирования в сравнении с Forecast NOW!. Сравнение велось на 256 товарах, которые имеют различные продажи, с сезонностью краткосрочной и долгосрочной, с «плохими» продажами и дефицитом, акциями и прочими выбросами. Для каждого товара был построен прогноз по модели экспоненциального сглаживания, для различных α, выбирался лучший и сравнивался с прогнозом по модели Forecast NOW!

В таблице ниже вы видите значение ошибки прогноза для каждого товара. Ошибка здесь считалась как RMSE. Это корень из среднеквадратичного отклонения прогноза от реальности. Грубо говоря, показывает, на сколько единиц товара мы отклонились в прогнозе. Улучшение показывает, на сколько процентов прогноз Forecast NOW! лучше, если цифра положительная, и хуже, если отрицательная. На рисунке 8 по оси X отложены товары, по оси Y указано насколько прогноз Forecast NOW! лучше, чем прогнозирование методом экспоненциального сглаживания. Как можно видеть из этого графика, точность прогнозирования Forecast NOW! почти всегда в два раза выше и почти никогда не хуже. На деле это означает, что использование Forecast NOW! позволит в два раза сократить запасы или снизить дефицит.

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

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

При прогнозировании данным методом возникает два затруднения:

выбор значения параметра сглаживания α;
определение начального значения Uo.

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

Таким образом, если есть уверенность, что начальные условия, на основании которых разрабатывается прогноз, достоверны, следует использовать небольшую величину параметра сглаживания (α→0). Когда параметр сглаживания мал, то исследуемая функция ведет себя как средняя из большого числа прошлых уровней. Если нет достаточной уверенности в начальных условиях прогнозирования, то следует использовать большую величину α, что приведет к учету при прогнозе в основном влияния последних наблюдений.

Точного метода для выбора оптимальной величины параметра сглаживания α нет. В отдельных случаях автор данного метода профессор Браун предлагал определять величину α, исходя из длины интервала сглаживания. При этом α вычисляется по формуле:

где n – число наблюдений, входящих в интервал сглаживания.

Задача выбора Uo (экспоненциально взвешенного среднего начального) решается следующими способами:

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

Также можно воспользоваться экспертными оценками.

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

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

Задача . Имеются данные, характеризующие уровень безработицы в регионе, %

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

Решение методом экспоненциального сглаживания

1) Определяем значение параметра сглаживания по формуле:

где n – число наблюдений, входящих в интервал сглаживания. α = 2/ (10+1) = 0,2

2) Определяем начальное значение Uo двумя способами:
І способ (средняя арифметическая) Uo = (2,99 + 2,66 + 2,63 + 2,56 + 2,40 + 2,22 + 1,97 + 1,72 + 1,56 + 1,42)/10 = 22,13/10 = 2,21
II способ (принимаем первое значение базы прогноза) Uo = 2,99

3) Рассчитываем экспоненциально взвешенную среднюю для каждого периода, используя формулу

где t – период, предшествующий прогнозному; t+1 – прогнозный период; Ut+1 - прогнозируемый показатель; α - параметр сглаживания; Уt - фактическое значение исследуемого показателя за период, предшествующий прогнозному; Ut - экспоненциально взвешенная средняя для периода, предшествующего прогнозному.

Например:
Uфев = 2,99*0,2 +(1-0,2) * 2,21 = 2,37 (І способ)
Uмарт = 2,66*0,2+(1-0,2) * 2,37 = 2,43 (І способ) и т.д.

Uфев = 2,99*0,2 +(1-0,2) * 2,99 = 2,99 (II способ)
Uмарт = 2,66*0,2+(1-0,2) * 2,99 = 2,92 (II способ)
Uапр = 2,63*0,2+(1-0,2) * 2,92 = 2,86 (II способ) и т.д.

4) По этой же формуле вычисляем прогнозное значение
Uноябрь= 1,42*0,2+(1-0,2) * 2,08 = 1,95 (І способ)
Uноябрь= 1,42*0,2+(1-0,2) * 2,18 = 2,03 (ІІ способ)
Результаты заносим в таблицу.

5) Рассчитываем среднюю относительную ошибку по формуле:

ε = 209,58/10 = 20,96% (І способ)
ε = 255,63/10 = 25,56% (ІІ способ)

В каждом случае точность прогноза является удовлетворительной поскольку средняя относительная ошибка попадает в пределы 20-50%.

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

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

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

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

Исходные данные

Допустим, вы фанат «Властелина Колец», и вот уже три года изготавливаете и торгуете мечами (рис. 1). Отобразим продажи графически (рис. 2). За три года спрос удвоился - может быть, это тренд? Мы вернемся к этой мысли чуть позже. На графике есть несколько пиков и спадов, что может быть признаком сезонности. В частности, пики приходятся на месяцы с номерами 12, 24 и 36, которые оказываются декабрями. Но может быть это лишь случайность? Давайте выясним.

Простое экспоненциальное сглаживание

Методы экспоненциального сглаживания основываются на прогнозировании будущего по данным из прошлого, где более новые наблюдения весят больше, чем старые. Такое взвешивание возможно благодаря константам сглаживания. Первый метод экспоненциального сглаживания, который мы опробуем, называется простым экспоненциальным сглаживанием (ПЭС, simple exponential smoothing, SES). Он использует лишь одну константу сглаживания.

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

Спрос в момент времени t = уровень + случайная погрешность около уровня в момент времени t

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

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

уровень 0 = среднее значение спроса за первый год (месяцы 1-12)

Для спроса на мечи он равен 163. Мы используем уровень 0 (163) как прогноз спроса на месяц 1. Спрос в месяц 1 равен 165, то есть он на 2 меча выше уровня 0 . Стоит обновить приближение исходного уровня. Уравнение простого экспоненциального сглаживания:

уровень 1 = уровень 0 + несколько процентов × (спрос 1 – уровень 0)

уровень 2 = уровень 1 + несколько процентов × (спрос 2 – уровень 1)

И т.д. «Несколько процентов» - называется константой сглаживания, и обозначается альфой. Это может быть любое число от 0 до 100% (от 0 до 1). Выбирать значение альфы вы научитесь позже. В общем случае значение для разных моментов времени:

Уровень текущий период = уровень предыдущий период +
альфа × (спрос текущий период – уровень предыдущий период)

Будущий спрос равен последнему вычисленному уровню (рис. 3). Поскольку вы не знаете, чему равна альфа, установите для начала в ячейке С2 значение 0,5. После того, как модель будет построена, найдите такую альфа, чтобы сумма квадратов ошибки – Е2 (или стандартное отклонение – F2) были минимальны. Для этого запустите опцию Поиск решения . Для этого пройдите по меню ДАННЫЕ –> Поиск решения , и установите в окне Параметры поиска решения требуемые значения (рис. 4). Чтобы отразить результаты прогноза на диаграмме, для начала выберите диапазон А6:В41, и постройте простую линейную диаграмму. Далее кликните на диаграмме правой кнопкой мыши, выберите опцию Выбрать данные. В открывшемся окне создайте второй ряд и вставьте в него предсказания из диапазона А42:В53 (рис. 5).

Возможно, у вас есть тренд

Чтобы проверить это предположение достаточно подогнать линейную регрессию под данные спроса и выполнить тест на соответствие критерию Стьюдента на подъеме этой линии тренда (как в ). Если уклон линии ненулевой и статистически значимый (в проверке по критерию Стьюдента величина р менее 0,05), у данных есть тренд (рис. 6).

Мы воспользовались функцией ЛИНЕЙН, которая возвращает 10 описательных статистик (если вы ранее не пользовались этой функцией, рекомендую ) и функцией ИНДЕКС, которая позволяет «вытащить» только три требуемые статистики, а не весь набор. Получилось, что наклон равен 2,54, и он значим, так как тест Стьюдента показал, 0,000000012 существенно меньше 0,05. Итак, тренд есть, и осталось включить его в прогноз.

Экспоненциальное сглаживание Холта с корректировкой тренда

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

спрос за время t = уровень + t × тренд + случайное отклонение уровня в момент времени t

Экспоненциальное сглаживание Холта с корректировкой тренда имеет два новых уравнения, одно - для уровня по мере его продвижения во времени, а другое - тренд. Уравнение уровня содержит сглаживающий параметр альфа, а уравнение тренда – гамма. Вот как выглядит новое уравнение уровня:

уровень 1 = уровень 0 + тренд 0 + альфа × (спрос 1 – (уровень 0 + тренд 0))

Обратите внимание, что уровень 0 + тренд 0 - это просто одношаговый прогноз от исходных значений к месяцу 1, поэтому спрос 1 – (уровень 0 + тренд 0) - это одношаговое отклонение. Таким образом, основное уравнение приближения уровня будет следующим:

уровень текущий период = уровень предыдущий период + тренд предыдущий период + альфа × (спрос текущий период – (уровень предыдущий период) + тренд предыдущий период))

Уравнение обновления тренда:

тренд текущий период = тренд предыдущий период + гамма × альфа × (спрос текущий период – (уровень предыдущий период) + тренд предыдущий период))

Холтовское сглаживание в Excel аналогично простому сглаживанию (рис. 7), и, как и выше, цель – найти два коэффициента, минимизируя сумму квадратов ошибок (рис. 8). Чтобы получить исходные значения уровня и тренда (в ячейках С5 и D5 на рис. 7), постройте график за первые 18 месяцев продаж и добавьте к нему линию тренда с уравнением. Исходное значение тренда 0,8369 и исходный уровень 155,88 занесите в ячейки С5 и D5. Прогнозные данные можно представить графически (рис. 9).

Рис. 7. Экспоненциальное сглаживание Холта с корректировкой тренда; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Выявление закономерностей в данных

Есть способ испытать прогностическую модель на прочность - сравнить погрешности сами с собой, сдвинутыми на шаг (или несколько шагов). Если отклонения случайны, то улучшить модель нельзя. Однако, возможно, в данных о спросе есть сезонный фактор. Концепция погрешности, коррелирующей с собственной версией за другой период, называется автокорреляцией (подробнее об автокорреляции см. ). Чтобы рассчитать автокорреляцию, начните с данных об ошибке прогноза за каждый период (столбец F на рис. 7 переносим в столбец В на рис. 10). Далее определите среднюю ошибку прогноза (рис. 10, ячейка В39; формула в ячейке: =СРЗНАЧ(B3:B38)). В столбце С рассчитайте отклонение ошибки прогноза от среднего; формула в ячейке С3: =B3-B$39. Далее последовательно сдвигайте столбец С на столбец вправо и строку вниз. Формулы в ячейках D39: =СУММПРОИЗВ($C3:$C38;D3:D38), D41: =D39/$C39, D42: =2/КОРЕНЬ(36), D43: =-2/КОРЕНЬ(36).

Что может значить для одного из столбцов D:O «синхронное движение» со столбцом С. Например, если столбцы С и D синхронны, то число, отрицательное в одном из них, должно быть отрицательным и в другом, положительное в одном, положительное – в другом. Это означает, что сумма произведений двух столбцов будет значительной (отличия накапливаются). Или, что тоже самое, чем ближе значение в диапазоне D41:О41 к нулю, тем ниже корреляция столбца (соответственно от D до О) со столбцом С (рис. 11).

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

Мультипликативное экспоненциальное сглаживание Холта-Винтерса

Метод называется мультипликативным (от multiplicate - умножать), поскольку использует умножение для учета сезонности:

Спрос в момент t = (уровень + t × тренд) × сезонная поправка для момента t × все оставшиеся нерегулярные поправки, которые мы не можем учесть

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

Прогноз на месяц 39 = (уровень 36 + 3 × тренд 36) х сезонность 27

Анализируя данные, необходимо выяснить, что в серии данных является трендом, а что - сезонностью. Чтобы выполнить вычисления по методу Холта-Винтерса, необходимо:

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

Начните с исходных данных (столбцы А и В на рис. 12) и добавьте столбец С со сглаженными значениями на основе скользящего среднего. Так как сезонность имеет 12-месячные циклы, имеет смысл использовать среднее за 12 месяцев. С этим средним есть небольшая проблема. 12 – четное число. Если вы сглаживаете спрос за месяц 7, стоит ли считать его средним спросом с 1-го по 12-й месяц или со 2-го по 13-й? Чтобы справиться с этим затруднением, нужно сгладить спрос с помощью «скользящего среднего 2×12». Т.е., взять половину от двух средних с 1 по 12-й месяц и со 2 по 13. Формула в ячейке С8: =(СРЗНАЧ(B3:B14)+СРЗНАЧ(B2:B13))/2.

Сглаженные данных для месяцев 1–6 и 31–36 получить нельзя, так как не хватает предыдущих и последующих периодов. Для наглядности исходные и сглаженные данные можно отразить на диаграмме (рис. 13).

Теперь в столбце D разделите оригинальную величину на сглаженную и получите приблизительное значение сезонной поправки (столбец D на рис. 12). Формула в ячейке D8: =B8/C8. Обратите внимание на всплески в 20% выше нормального спроса в месяцах 12 и 24 (декабрь), в то время как весной наблюдаются провалы. Эта техника сглаживания дала вам две точечные оценки для каждого месяца (всего 24 месяца). В столбце Е найдено среднее значение этих двух факторов. Формула в ячейке Е1: =СРЗНАЧ(D14;D26). Для наглядности уровень сезонных колебаний можно представить графически (рис. 14).

Теперь можно получить данные, скорректированные на сезонные колебания. Формула в ячейке G1: =B2/E2. Постройте график на основе данных столбца G, дополните его линией тренда, выведите уравнение тренда на диаграмму (рис. 15), и используйте коэффициенты в последующих расчетах.

Сформируйте новый лист, как показано на рис. 16. Значения в диапазон Е5:Е16 подставьте с рис. 12 области Е2:Е13. Значения С16 и D16 возьмите из уравнения линии тренда на рис. 15. Значения констант сглаживания установите для начала на отметке 0,5. Растяните значения в строке 17 на диапазон месяцев с 1 по 36. Запустите Поиск решения для оптимизации коэффициентов сглаживания (рис. 18). Формула в ячейке В53: =(C$52+(A53-A$52)*D$52)*E41.

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

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

Итак, у нас есть вполне рабочий прогноз. Как установить верхние и нижние границы, которые можно использовать для построения реалистичных предположений? В этом вам поможет симуляция Монте-Карло, с которой вы уже встречались в (см. также ). Смысл заключается в том, чтобы сгенерировать будущие сценарии поведения спроса и определить группу, в которую попадают 95% из них.

Удалите с листа Excel прогноз из ячеек В53:В64 (см. рис. 17). Вы запишете туда спрос на основе симуляции. Последнюю можно сгенерировать с помощью функции НОРМОБР. Для будущих месяцев вам достаточно снабдить ее средним (0), стандартным распределением (10,37 из ячейки $Н$2) и случайным числом от 0 до 1. Функция вернет отклонение с вероятностью, соответствующей колоколообразной кривой. Поместите симуляцию одношаговой погрешности в ячейку G53: =НОРМОБР(СЛЧИС();0;H$2). Растянув эту формулу вниз до G64, и вы получите симуляции ошибки прогноза для 12 месяцев одношагового прогноза (рис. 19). Ваши значения симуляций будут отличаться от приведенных на рисунке (на то она и симуляция!).

С погрешностью прогноза у вас есть все, что нужно для обновления уровня, тренда и сезонного коэффициента. Так что выделите ячейки C52:F52 и растяните их до строки 64. В результате у вас имеются симулированная ошибка прогноза и сам прогноз. Идя от обратного, можно спрогнозировать значения спроса. Вставьте в ячейку В53 формулу: =F53+G53 и растяните ее до В64 (рис. 20, диапазон В53:F64). Теперь вы можете нажимать на кнопку F9, каждый раз обновляя прогноз. Разместите результаты 1000 симуляций в ячейках А71:L1070, каждый раз транспонируя значения из диапазона В53:В64 в диапазон А71:L71, A72:L72, … A1070:L1070. Если вас это напрягает напишите код VBA.

Теперь у вас есть по 1000 сценариев на каждый месяц, и вы можете использовать функцию ПЕРСЕНТИЛЬ, чтобы получить верхние и нижние границы в середине 95%-ного доверительно интервала. В ячейке А66 формула: =ПЕРСЕНТИЛЬ(A71:A1070;0,975), а в ячейке А67: =ПЕРСЕНТИЛЬ(A71:A1070;0,025).

Как обычно, для наглядности данные можно представить в графическом виде (рис. 21).

На графике есть два интересных момента:

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

Написано по материалам книги Джона Формана . – М.: Альпина Паблишер, 2016. – С. 329–381