Реализация с помощью ппп Excel

Анализ структуры временного ряда, подбор линии тренда и прогнозирование удобно осуществлять с помощью «Пакета анализа» табличного процессора Excel. Отчасти это уже делалось в изложенных выше примерах.

Методику применения ППП Excel проиллюстрируем также на примере следующей задачи (при этом будем опираться на статистические данные, находящиеся в таблице 6.15, и исходить из того, что число наблюдений равно 20).

Задача. Для осуществления прогноза исследовать совокупность квартальных данных о динамике выпуска продукции некоторого предприятия за пять лет.

Требуется:

1) ввести данные;

2) проверить гипотезу о существовании тренда с помощью метода сравнения средних уровней;

3) рассчитать значения коэффициентов автокорреляции для характеристики структуры временного ряда;

4) построить коррелограмму и с помощью автокорреляционной функции провести анализ структуры временного ряда;

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

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

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

Результаты вычислений и анализа оформить в виде отчета (форма отчета прилагается ниже).

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

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 листа «Исходные данные» по уравнению лучшего тренда вычислите прогнозное значение при . При необходимости рассчитайте значения сезонной компоненты и учтите их при определении окончательного значения точечного прогноза.

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