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

1) В ячейку А1 занесите название – Квартал, в ячейку В1 – название Выпуск. В ячейки А2, А3, …, А21 введите данные первого столбца таблицы 6.15, в ячейки В2, В3, …, В21 – данные выбранного варианта задания.

Введите новое название листа «Исходные данные».

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; 9; 9) для вычисления критического значения F-статистики (при уровне значимости и числе степеней свободы ).

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

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

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

В ячейку А18 введите формулу для вычисления критического значения статистики Стьюдента =СТЬЮДРАСПОБР(0,05; 20-2).

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

3) Вернитесь на лист «Исходные данные». В ячейку C1 введите название «Лаги». В ячейки С2:С6 введите значения 1, 2, 3, 4, 5.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Далее>;

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

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

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

6) Выберите наилучшую форму тренда по максимальному значению коэффициента детерминации построенных моделей.

7) Рассчитайте для выбранной трендовой модели точечный прогноз выпуска продукции. Для этого в ячейке В23 листа «Исходные данные» по уравнению лучшего тренда вычислите прогнозное значение при . При необходимости рассчитайте значения сезонной компоненты и учтите их при определении окончательного значения точечного прогноза.

Приложение: Отчет о результатах вычислений и анализа

1. Спецификация, параметризация и верификация модели

Анализ исходных данных позволяет сделать вывод о том, что они

представляют собой временной ряд. Поэтому возникает задача анализа структуры этого временного ряда.

Как известно, при исследовании экономического временного ряда выделяются несколько составляющих: тренд, сезонная компонента, циклическая компонента, случайная компонента.

В данном случае может идти речь только о тренде, сезонной и случайной компонентах.

Для проверки гипотезы о существовании тренда используется метод сравнения средних уровней, который опирается на предположение об однородности исходного временного ряда. Это предположение и проверяется перед применением метода сравнения средних уровней.

Для этого исходный ряд разбивается на две равные части объема 10, для каждой из них вычисляются выборочные дисперсии, по которым находится значение ........... и сравнивается со значением =............. . Так как (больше или меньше) , то гипотеза о равенстве дисперсий выделенных частей временного ряда (принимается или отклоняется).

Тренд по временному ряду присутствует, если

 

.

 

Так как (больше или меньше) , то тренд во временном ряду (присутствует или отсутствует).

Анализ структуры временного ряда осуществляются на основании автокорреляционных коэффициентов, которые приведены в таблице 6.14.

 

Таблица 6.14. Коэффициенты автокорреляции

 

Лаги Коэффициенты автокорреляции
 
 
 
 
 

 

Наиболее высоким оказался коэффициент автокорреляции порядка (1, 2, 3, 4, 5). Анализ коррелограммы показывает, что исследуемый ряд содержит (тренд, сезонную компоненту). Кроме того, визуальный анализ точечного графика временного ряда подтверждает, что исследуемый ряд содержит (тренд, сезонную компоненту).

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

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