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

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

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

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

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

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

Текущий номер года, t
Трудоемкость продукции, y_t	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.