Порядок выполнения работы. 1. Введите данные таблицы

1. Введите данные таблицы. В ячейку А1 занесите название первого столбца — «Год», в ячейку В1 — название второго столбца — «Выпуск продукции». В ячейки А2, А3, …, А21 введите данные первого столбца исходной таблицы, в ячейки В2, В3, …, В21 — данные второго столбца в соответствии с вариантом.

Введите новое название листа «Исходные данные», щелкнув правой кнопкой мыши на названии листа «Лист 1» и выбрав опцию переименование или двойным щелчком левой кнопкой мыши в поле «Лист 1». Сохраните рабочую книгу под названием «Временные ряды - Фамилия».

2. Проверьте гипотезу о существовании тренда по тесту Чоу. Скопируйте ячейки А2:В11 в ячейку А1 нового листа, который переименуйте в «Тест Чоу»; ячейки А12:В21 в ячейку D1 полученного листа. Введите формулы:

В ячейку А13 =СРЗНАЧ(В1:В10)

В ячейку А14 =ДИСП(В1:В10)

В ячейку D13 =СРЗНАЧ(Е1:Е10)

В ячейку D14 =ДИСП(Е1:Е10)

В ячейку А15 введите формулу для вычисления наблюдаемого значения критерия Фишера =А14/D14, если значение в ячейке А14 больше чем в D14, если же меньше, то =D14/А14. В ячейку А16 введите формулу

=FРАСПОБР(0,05,10-1,20-10-1)

для вычисления критического значения F-статистики , где , .

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

В ячейку А17 введите формулу для вычисления наблюдаемого значения t- статистики

=3*КОРЕНЬ(10)*ABS(A13-D13)/КОРЕНЬ(А14*(10-1)+В14*(10-1)).

В ячейку А18 введите формулу для вычисления критического значения t- статистики

=СТЬЮДРАСПОБР(0,05, 20-2).

Сравните и . Если больше , то сделайте вывод о присутствии тренда во временном ряду.

3. Рассчитайте значение коэффициентов автокорреляции уровней. Вернитесь на лист «Исходные данные». В ячейку C1 введите название «Периоды». В ячейки С2:С11 введите значения 1,2, …, 10 (число периодов) .

В ячейку D1 введите «Коэф. автокор.».

В ячейку D2 введите формулу коэффициента корреляции

=КОРРЕЛ(B 2:B20;B3:B21).

В ячейку D3 введите формулу коэффициента корреляции

=КОРРЕЛ(B2:B19;B4:B21).

В ячейку D4 введите формулу коэффициента корреляции

=КОРРЕЛ(B2:B18;B5:B21).

В ячейку D5 введите формулу коэффициента корреляции

=КОРРЕЛ(B2:B17;B6:B21).

В ячейку D5 введите формулу коэффициента корреляции

=КОРРЕЛ(B2:B16;B7:B21).

В ячейку D6 введите формулу коэффициента корреляции

=КОРРЕЛ(B2:B15;B8:B21).

В ячейку D7 введите формулу коэффициента корреляции

=КОРРЕЛ(B2:B14;B9:B21).

В ячейку D8 введите формулу коэффициента корреляции

=КОРРЕЛ(B2:B13;B10:B21).

В ячейку D9 введите формулу коэффициента корреляции

=КОРРЕЛ(B2:B12;B11:B21).

В ячейку D10 введите формулу коэффициента корреляции

=КОРРЕЛ(B2:B11;B12:B21).

Проведите анализ тенденции. В ячейку D11 введите формулу =МАКС(D1:D10).Сделайте выводы:

Если , то ряд содержит трендовую компоненту.

Если , где , то ряд содержит циклические колебания с периодичностью .

Постройте коррелограмму с помощью диаграммы. Для построения коррелограммы выполняются следующие действия:

– на панели инструментов активизируется кнопка Мастер диаграмм (шаг 1 из 4), в одноименном диалоговом окне среди стандартных типов выбирается График и верхний левый вид диаграммы и нажимается кнопка Далее>;

– открывается диалоговое окно Мастер диаграмм (шаг 2 из 4), в котором во вкладке Диапазон данных в поле Диапазон вводится ссылка на диапазон ячеек D2:D11; во вкладке Ряд в поле Подписи оси Х вводится ссылка на ячейки С2:С11 значений , в поле Имя – название «Коэффициенты автокорреляции уровней»; нажимается кнопка Далее>;

– открывается диалоговое окно Мастер диаграмм (шаг 3 из 4), в котором во вкладке Заголовки в поле Ось Х(категорий) вводится название «Периоды», в поле Ось Y(значений) – название «Коэффициенты автокорреляции уровней»; во вкладке Легенда снимается флажок Добавить легенду и нажимается кнопка Далее>;

– открывается диалоговое окно Мастер диаграмм (шаг 4 из 4) в поле имеющемся устанавливается флажок и нажимается кнопка Готово.

4. Проведите аналитическое выравнивание временного ряда с использованием линейной, степенной, экспоненциальной и полиномиальной функций.На листе «Исходные данные» выполняются следующие действия:

– на панели инструментов активизируется кнопка Мастер диаграмм (шаг 1 из 4), в одноименном диалоговом окне среди стандартных типов выбирается График и верхний левый вид диаграммы и нажимается кнопка Далее>;

– открывается диалоговое окно Мастер диаграмм (шаг 2 из 4), в котором во вкладке Диапазон данных в поле Диапазон вводится ссылка на диапазон ячеек В2:В21; во вкладке Ряд в поле Подписи оси Х вводится ссылка на ячейки А2:А21, в поле Имя – название «Выпуск продукции»; нажимается кнопка Далее>;

– открывается диалоговое окно Мастер диаграмм (шаг 3 из 4), в котором во вкладке Заголовки в поле Ось Х(категорий) вводится название «Годы», в поле Ось Y(значений) – название «Выпуск продукции»; во вкладке Легенда снимается флажок Добавить легенду и нажимается кнопка Далее>;

– открывается диалоговое окно Мастер диаграмм (шаг 4 из 4) в поле имеющемся устанавливается флажок и нажимается кнопка Готово.

Далее график с линией тренда четырежды копируется на новый лист «Тренды». На каждом из графиков курсор установить на кривой, нажать левую клавишу мышки, затем нажать правую кнопку мыши, выбрать Добавить линию тренда. В диалоговом окне выделить один из типов трендов, во вкладке Параметры установить флажок в поле показать уравнение на диаграмме, поместить на диаграмму R^2. Повторить действие Добавить линию тренда для 4-х типов трендовых моделей.

5. Выберите наилучшую форму тренда по максимальному значению коэффициента детерминации построенных моделей и графического анализа остатков. Сравните величины коэффициентов достоверности аппроксимации R^2. Сделайте вывод по виду диаграммы о лучшей форме тренда.

6. Рассчитайте для выбранной формы прогнозируемое значение выпуска продукции на 2010 год. В ячейке В23 по уравнению лучшего тренда вычислите прогнозное значение тренда для x =24 (x =2010-1987+1).

Приложение:

ОТЧЕТ