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

Задача 1. На основании информации о деятельности фирмы в течение 9 лет (табл. 1) провести следующие действия с использованием ППП Excel:

- определить наличие тренда Y_p(t);

- построить линейную модель Y_p(t) = a₀ + a₁xt, параметры которой оценить по методу наименьших квадратов (МНК);

- построить точечный и интервальный прогнозы трудоемкости производства продукции на два года вперед.

Таблица 1. Сведения о деятельности фирмы

Текущий номер года, t
Трудоемкость продукции, yt	8, 8	9, 2	8, 4	7, 8	8, 1	7, 4	6, 7	5, 8

 

Решение. Для решения данной задачи выполним следующий алгоритм:

1⁰. Определим наличие тренда по методу Тинтнера, для чего в столбцы А и В занесем исходные данные (рис. 1).

Рис.1. Окно - исходная таблица

2⁰. Проверим гипотезу о равенстве дисперсий с помощью F-теста, который можно найти среди инструментов Анализа данных (рис. 2)

Рис. 2. Окно «Анализ данных»

3⁰. Ввести данные для выполнения F-теста, указав интервал для первой и второй переменных (рис. 3). Результат выполнения теста приведен на рис. 4. Анализируя результаты выполнения двухвыборочного F-теста для проверки гипотезы о равенстве дисперсий, делаем вывод, что дисперсии различаются незначимо.

Рис. 3. Диалоговое окно

Рис. 4. Расчетные результаты анализа метода Тинтнера

4⁰. Выбрать инструмент анализа Двухвыборочный t-тест с одинаковыми дисперсиями (рис. 5) и ввести данные (рис. 6). Результат выполнения t-теста дан на рис.7. Анализируя тест, видно, что тренд существует.

5⁰. Построение линейной модели вида y_t = a₀ + a₁t.

Определим параметры модели y_t по методу МНК с помощью надстройки Анализ данных. Для регрессионного анализа необходимо:

- выбрать команду Сервис → Анализ данных. В окне Анализ данных выбрать инструмент Регрессия, а затем нажать кнопку ОК (рис.8);

Рис. 5. Диалоговое окно

Рис.6. Диалоговое окно

Рис. 7. Результаты теста

Рис. 8. Диалоговое окно

Рис. 9. Диалоговое окно

- в окне Регрессия в поле Входной интервал Y ввести адрес одного диапазона ячеек, который представляет зависимую переменную. В поле Входной интервал X ввести адрес диапазона, который содержит значения независимой переменной t. Если выделены и заголовки столбцов, установить флажок Метки в первой строке;

- выбрать параметры вывода (в данном примере – Новый рабочий лист); в поле График подбора поставить флажок; в поле Остатки поставить необходимые флажки и нажать кнопу ОК (рис. 9).

Результат регрессионного анализа получим в виде, приведенном на рис. 10. Во втором столбце таблицы рис. 10 находятся коэффициенты уравнения регрессии а₀ = 10, 00476; а₁.= -0, 469047.

Рис. 10. Результат регрессионного анализа

Уравнение регрессии имеет следующий вид: Y_t = 10 - 0, 47t.

6⁰. Проведем оценку параметров линейной модели вручную (рис. 11).

Рис. 11. Промежуточные расчеты данных линейной модели

В результате расчетов получаем примерно аналогичные результаты.

Иногда для проверки расчетов полезно проверить формулы. Для этого следует выбрать команду Сервис → Параметры и поставить флажок в окне формулы (рис. 12). После этого на листе Excel расчетные значения будут заменены соответствующими формулами и функциями (рис. 13).

Рис. 12. Диалоговое окно

Рис. 13. Программа на Excel.

7⁰. Построение точечного и интервального прогноза на 2 шага вперед.

Для вычисления точечного прогноза в построенную модель подставляем соответствующие значения фактора t = n + k: _{прогн(n+k)} = а₀ + а₁(n+k). Тогда получим: = 10 - 0, 47*10 = 5, 3; = 10 - 0, 47*11 = 4, 83.

Для вычисления интервального прогноза рассчитаем доверительный интервал. Ширину доверительного интервала рассчитаем по формуле:

Y_n+L ( _n+L - _сК_р; _n+L + _сК_р),

где _с возьмем из полученного протокола регрессионного анализа.

_с = = = = 0, 35.

Таким образом, интервал равен: _сК_р = 0, 35*1, 05 = 0, 3675.

Далее вычислим верхнюю и нижнюю границы прогноза:

y₁₀ (5, 3 – 0, 3675 = 4, 9325; 5, 3 + 0, 3675 = 5, 6675),

y₁₁ (4, 83 – 0, 3675 = 4, 4625; 4, 83 + 0, 3675 = 5, 1975).

Рис. 14. График фактического временного ряда и его линейной модели