Пример выполнения задания № 2 в Excel и Calc

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

Таблица 16

Год	Квартал	Прибыль компании, млн у.е., Y_t
	I
II
III
IV
	I
II
III
IV
	I
II
III
IV
	I
II
III
IV

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

1. Построим график временного ряда с помощью Мастера диаграмм, предварительно выделив два столбца переменных t = 1, 2, …, 16, и отвечающие этим моментам уровни y_t:

тип диаграммы – «График» (MS Excel) или «Линия» (OO Calc), вид – «График с маркерами» (MS Excel) или «Линии и точки» (OO Calc);

диапазон данных – в MS Excel выделяем значения переменных y_t, переходим на вкладку «Ряд», курсор устанавливаем в строке «Подписи оси Х» и выделяем в таблице значения кварталов, в OO Calc на этом шаге ставим флажок у команды «Первый столбец как надпись», шаг «Ряд данных» пропускаем, нажимая «Далее»;

элементы диаграммы – оформляем заголовки, оси, сетку, нажимаем «Готово» (рис. 16).

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

2. Автокорреляционную функцию временного ряда можно построить с использованием в Excel встроенной функции КОРРЕЛ, а в Calc – CORREL (рис. 17). Аргументы названных функций – это уровни изучаемого ряда, сдвинутые между собой на заданный промежуток времени L (смотри пример из разд. 2.2).

Рис. 16. График временного ряда

Полученные значения коэффициентов автокорреляции для L = 1, 2, …, 4 следует проверить на существенность. При этом наблюдаемое значение t-статистики находим по формуле:

Полученную для каждого лага t-статистику сравнивают с критическим значением распределения Стьюдента t (a/2,n) для заданного уровня значимости a и числа степеней свободы n = n – L– 2. Если наблюдаемое значение t-статистики превосходит критическое t (a/2, n), то коэффициент автокорреляции r(L) значим.

Критическое значение t (a/2, n) в Excel находят с помощью встроенной функции СТЬДРАСПОБР(a, n), а в Calc – TINV(a, n).

В нашем примере (рис. 17) при заданном уровне значимости a = 0,05 статистически значимыми будут r(1) и r(4).

Рис. 17. Автокорреляционная функция изучаемого ряда

По графику временного ряда (рис. 16) и автокорреляционной функции для данного временного ряда (рис. 17) делаем вывод о наличии в уровнях ряда периодических колебаний (период колебаний равен 4 кварталам) и общей убывающей тенденции.

3. Построение мультипликативной модели.

Шаг первый. Лаг максимального значения коэффициента автокорреляции равен четырем, поэтому сначала моделируем квартальную сезонную компоненту S.

Проведем выравнивание исходных уровней ряда с использованием процедуры скользящей средней. Применяя для нашего примера ту же методику, что и для аддитивной модели (табл. 7, разд. 2.4), получим значения центрированных скользящих средних, которые приведены на рис. 18 (ячейки F7:F18).

Рис. 18. Процедура оценивания сезонной компоненты ряда

Найдем оценки сезонной компоненты как частное от деления фактических уровней ряда на центрированные скользящие средние (ячейки G7:G18 на рис. 18). Используем эти данные для определения индексов сезонности S_t (разд. 2.5).

Для удобства расчета S_t составим вспомогательную таблицу (рис. 19). Блок D4:G7 содержит разнесённые по кварталам оценки сезонной компоненты. Соответствующие средние значения приведены в ячейках D9:G9.

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

Определим корректирующий коэффициент (ячейка L9): .

Рис. 19. Расчет индексов сезонности

Скорректируем значения индексов сезонности, умножив средние оценки на корректирующий коэффициент k (ячейки D10:G10 на рис.19).

В результате выполнения указанных действий в нашем примере получим следующие значения индексов сезонности: для 1-го квартала ; для 2-го квартала ; для 3-го квартала ; для 4-го квартала .

Экономический смысл этих показателей состоит в следующем. Прибыль компании в 1-м квартале ниже среднегодовой прибыли в 0,914 раза, во 2-м и 3-м кварталах – выше в 1,202 и 1,082 раза, соответственно. В 4-м квартале прибыль компании ниже среднегодового значения в 0,802 раза.

Шаг второй. Определимся с формой тренда в нашем примере. Для этого устраним влияние сезонной компоненты, разделив каждый уровень исходного ряда на соответствующее значение индекса сезонности. Полученные в результате десезонализированные данные (ячейки D4:D19 на рис. 20) помогают визуально определить форму тренда (рис. 21).

Рис. 20. Ряд десезонализированных данных

Рис. 21. График десезонализированного ряда

Шаг третий. По результатам визуального анализа (рис. 21) для описания тренда можно предложить линейную функцию . Выборочные оценки b₀и b_l для неизвестных коэффициентов β₀ и β₁ найдём по формулам (25) и (26), при этом вместо значений y_t следует брать десезонализированные, т. е. очищенные от влияния сезонной компоненты данные. В результате получим b₀ = 90,565и b_l = –2,773.

Таким образом, оценочное уравнение тренда:

Тот же результат можно получить, используя стандартные средства в электронных таблицах Excel или Calc. Для этого помечаем одну из точек десезонализированных данных (рис. 21) и из контекстного меню выбираем команду «Добавить линию тренда» (Excel) или «Вставить линию тренда» (Calc).

В диалоговом окне «Линия тренда» в Excel присутствуют две закладки, первая из них содержит образцы форм линии регрессии (тренда). В нашем примере заказываем линейную форму.

Переходим во вторую закладку – «Параметры». Устанавливаем флажки на опциях «Показать уравнение на диаграмме» и «Поместить на диаграмму величину достоверности аппроксимации».

Аналогично в Calc в диалоговом окне «Линия тренда» на закладке «Тип» выбираем тип регрессии – «Линейный», устанавливаем флажки на опциях «Показать уравнение» и «Показать коэффициент корреляции R²».

Результаты выполнения указанных действий в виде оценочного уравнения тренда, его графика и значения коэффициента достоверности аппроксимации представлены на рис. 22.

Рис. 22. Построение линейного тренда в Excel

Таким образом, для изучаемого ряда выполнено моделирование сезонной S_t и трендовой Т_t составляющих, других систематических компонент автокорреляционная функция не показывает (рис. 17). Продемонстрируем смысл полученных результатов.

По уравнению тренда оценим его уровни в каждый момент времени t = 1, 2, …, 16 (ячейки Е4:Е19 на рис. 23).

Соответствующая этим данным прямая на рис. 24 даёт графическое представление трендовой модели изучаемого ряда.

return false">ссылка скрыта

Модель ряда с поправкой на сезонность получается при умножении уровней T_t на значения индексов сезонности S_t (ячейки F4:F19 на рис. 23). Значения (Т×S) изображены на рис. 24 в виде уровней, помеченных маркерами-кружками.

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

Рис. 23. Расчёт компонентного состава изучаемого ряда

Рис. 24. Уровни исходного ряда (ромбики), его трендовая модель (квадратики) и трендовая модель с поправкой на сезонность (кружки)

4. Оценим качество модели временного ряда посредством коэффициента детерминации R² по формуле (31).

Предварительно рассчитаем абсолютную ошибку E_t, которая в мультипликативной модели определяются по формуле (33), результаты расчётов E_t приведены в ячейках D4:D19 на рис. 25.

Тогда сумма квадратов абсолютных ошибок равна 207,7 (ячейка E20 на рис. 25). Общая сумма квадратов отклонений фактических уровней ряда от его среднего уровня равна 5023 (ячейка F20 на рис. 25). Отсюда найдём долю объясненной дисперсии уровней ряда по формуле (31): .

Таким образом, коэффициент детерминации R² равен 0,959, то есть построенная модель объясняет 95,9 % общей вариации уровней временного ряда, представляющего прибыль компании за 16 кварталов.

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

Рис. 25. Оценка качества построенной модели

5. Прогнозирование по мультипликативной модели.

Трендовый прогноз для второго квартала следующего, то есть пятого, года (t = 18) предсказывает величину прибыли:

(млн у.е.).

Уточним трендовый прогноз, поправив его на сезонность. Индекс сезонности для второго квартала . Тогда скорректированный на сезонность прогноз прибыли компании на второй квартал 5-го года:

(млн у.е.).

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

Варианты к заданию № 2

Вариант 1. Имеются данные о перевозках грузов железнодорожным транспортом (млн т) за четыре года.

Год	Квартал	Перевозка грузов, млн т	Год	Квартал	Перевозка грузов, млн т

Сделать прогноз ожидаемого объема перевозок на третий квартал 2000-го года.

Вариант 2. Имеются данные об объеме экспорта из Российской Федерации (млрд долл.) за 1995–1998 гг.

Год	Квартал	Экспорт	Год	Квартал	Экспорт

		50,4			51,6
					52,4

		53,2
		50,8			52,6

					56,8

Сделать прогноз объема экспорта из России за второй квартал 1999-го года.

Вариант 3. Имеются данные о перевозках грузов железнодорожным транспортом (млн т) за четыре года.

Год	Квартал	Объем перевозок, млн т	Год	Квартал	Объем перевозок, млн т
		13,7			15,7
		12,1			13,3
		12,7			14,1
		16,7			18,7
		14,9			16,7
		12,5			14,3
		13,7			14,7
		17,7			18,5

Сделать прогноз ожидаемого объема перевозок на второе полугодие 2000-го года.

Вариант 4. Имеются данные об импорте товаров некоторой страны (млн долл.) за 1996–1999 гг.

Год	Квартал	Импорт товаров, млн долл.	Год	Квартал	Импорт товаров, млн долл.
		17,2			19,2
		15,6			16,8
		16,2			17,6
		20,2			22,2
		18,4			20,2
					17,8
		17,2			18,2
		21,2

Сделать прогноз ожидаемого объема импорта за первый квартал 2000-го года.

Вариант 5. Имеются поквартальные данные по розничному товарообороту России (% к предыдущему периоду) в 1997–2000 гг.

Год	Квартал	Товаро- оборот, %	Год	Квартал	Товаро- оборот, %
		37,3			39,3
		35,7			36,9
		36,3			37,7
		40,3			42,3
		38,5			40,3
		36,1			37,9
		37,3			38,3
		41,3			42,1

Сделать прогноз ожидаемого товарооборота России на третий квартал 2001-го года.

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

Год	Квартал	Прибыль, тыс. долл.	Год	Квартал	Прибыль, тыс. долл.

Сделать прогноз ожидаемой прибыли компании за первое полугодие 2000-го года.

Вариант 7. Имеются данные об объеме экспорта из Российской Федерации (млрд долл.) за 1995–1998 гг.

Год	Квартал	Экспорт, млрд долл.	Год	Квартал	Экспорт, млрд долл.
		80,6			82,6
					80,2
		79,6
		83,6			85,6
		81,8			83,6
		79,4			81,2
		80,6			81,6
		84,6			85,4

Сделать прогноз объема экспорта из России за третий квартал 1999-го года.

Вариант 8. Имеются поквартальные данные об объемах выпуска продукции (тыс. шт.) некоторой фирмой за 1996–1999 гг.:

Год	Квартал	Объем выпуска, тыс. шт.	Год	Квартал	Объем выпуска, тыс. шт.

Сделать прогноз ожидаемого объема выпуска продукции за первое полугодие 2000-го года.

Вариант 9. Имеются данные о перевозках грузов транспортом (млн тонн) за четыре года.

Год	Квартал	Объем перевозок, млн тонн	Год	Квартал	Объем перевозок, млн тонн
		40,5			42,5
		38,9			40,1
		39,5			40,9
		43,5			45,5
		41,7			43,5
		39,3			41,1
		40,5			41,5
		44,5			45,3

Сделать прогноз ожидаемого объема перевозок на первое полугодие 2000-го года.

Вариант 10. Имеются данные об импорте товаров некоторой страны (млн долл.) за 1999–2002 годы:

Год	Квартал	Импорт товаров, млн долл.	Год	Квартал	Импорт товаров, млн долл.
		45,8			47,8
		44,2			45,4
		44,8			46,2
		48,8			50,8
					48,8
		44,6			46,4
		45,8			46,8
		49,8			50,6

Сделать прогноз ожидаемого объема импорта за первый квартал 2003-го года.