Задачи анализа и моделирования временных рядовЗадача 1. На основании информации о деятельности фирмы в течение 9 лет (табл. 1) провести следующие действия с использованием ППП Excel: - определить наличие тренда Yp(t); - построить линейную модель Yp(t) = a0 + a1xt, параметры которой оценить по методу наименьших квадратов (МНК); - построить точечный и интервальный прогнозы трудоемкости производства продукции на два года вперед. Таблица 1. Сведения о деятельности фирмы
Решение. Для решения данной задачи выполним следующий алгоритм: 10. Определим наличие тренда по методу Тинтнера, для чего в столбцы А и В занесем исходные данные (рис. 1). Рис.1. Окно - исходная таблица 20. Проверим гипотезу о равенстве дисперсий с помощью F-теста, который можно найти среди инструментов Анализа данных (рис. 2) Рис. 2. Окно «Анализ данных» 30. Ввести данные для выполнения F-теста, указав интервал для первой и второй переменных (рис. 3). Результат выполнения теста приведен на рис. 4. Анализируя результаты выполнения двухвыборочного F-теста для проверки гипотезы о равенстве дисперсий, делаем вывод, что дисперсии различаются незначимо. Рис. 3. Диалоговое окно Рис. 4. Расчетные результаты анализа метода Тинтнера 40. Выбрать инструмент анализа Двухвыборочный t-тест с одинаковыми дисперсиями (рис. 5) и ввести данные (рис. 6). Результат выполнения t-теста дан на рис.7. Анализируя тест, видно, что тренд существует. 50. Построение линейной модели вида yt = a0 + a1t. Определим параметры модели yt по методу МНК с помощью надстройки Анализ данных. Для регрессионного анализа необходимо: - выбрать команду Сервис → Анализ данных. В окне Анализ данных выбрать инструмент Регрессия, а затем нажать кнопку ОК (рис.8); Рис. 5. Диалоговое окно Рис.6. Диалоговое окно Рис. 7. Результаты теста Рис. 8. Диалоговое окно Рис. 9. Диалоговое окно - в окне Регрессия в поле Входной интервал Y ввести адрес одного диапазона ячеек, который представляет зависимую переменную. В поле Входной интервал X ввести адрес диапазона, который содержит значения независимой переменной t. Если выделены и заголовки столбцов, установить флажок Метки в первой строке; - выбрать параметры вывода (в данном примере – Новый рабочий лист); в поле График подбора поставить флажок; в поле Остатки поставить необходимые флажки и нажать кнопу ОК (рис. 9). Результат регрессионного анализа получим в виде, приведенном на рис. 10. Во втором столбце таблицы рис. 10 находятся коэффициенты уравнения регрессии а0 = 10, 00476; а1.= -0, 469047. Рис. 10. Результат регрессионного анализа Уравнение регрессии имеет следующий вид: Yt = 10 - 0, 47t. 60. Проведем оценку параметров линейной модели вручную (рис. 11). Рис. 11. Промежуточные расчеты данных линейной модели В результате расчетов получаем примерно аналогичные результаты. Иногда для проверки расчетов полезно проверить формулы. Для этого следует выбрать команду Сервис → Параметры и поставить флажок в окне формулы (рис. 12). После этого на листе Excel расчетные значения будут заменены соответствующими формулами и функциями (рис. 13). Рис. 12. Диалоговое окно Рис. 13. Программа на Excel. 70. Построение точечного и интервального прогноза на 2 шага вперед. Для вычисления точечного прогноза в построенную модель подставляем соответствующие значения фактора t = n + k: прогн(n+k) = а0 + а1(n+k). Тогда получим: = 10 - 0, 47*10 = 5, 3; = 10 - 0, 47*11 = 4, 83. Для вычисления интервального прогноза рассчитаем доверительный интервал. Ширину доверительного интервала рассчитаем по формуле: Yn+L ( n+L - сКр; n+L + сКр), где с возьмем из полученного протокола регрессионного анализа. с = = = = 0, 35. Таким образом, интервал равен: сКр = 0, 35*1, 05 = 0, 3675. Далее вычислим верхнюю и нижнюю границы прогноза: y10 (5, 3 – 0, 3675 = 4, 9325; 5, 3 + 0, 3675 = 5, 6675), y11 (4, 83 – 0, 3675 = 4, 4625; 4, 83 + 0, 3675 = 5, 1975). Рис. 14. График фактического временного ряда и его линейной модели
|