Отобразить на графике фактические данные, результаты расчетов й прогнозирования.

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

• Выберем тип диаграммы - точечная, на которой значения соединены отрезками.

• В формате области построения укажем тип заливки - обычная; рамка - невидимая. Результат действия виден на рис. 3.13.

• Далее на графике изобразить результаты прогнозирования.

Для этого следует «кликнуть» правой кнопкой мышки и в появившемся меню выбрать Исходные данные. Затем последовательно нажать кнопки Ряд, Добавить и указать диапазоны размещения данных (рис. 3.14).

• В диалоговом окне Исходные данные в поле значения Yвве­дите адрес диапазона ячеек, который представляет прогноз зависимой переменной. В поле значения Xвведите адрес диапазона, который содержит значения независимой пере­менной t.

• Аналогично вводятся данные для верхних и нижних границ прогноза (рис. 3.15, 3.16).

3.3. АНАЛИЗ ВРЕМЕННЫХ РЯДОВ С ПОМОЩЬЮ ИНСТРУМЕНТА МАСТЕР ДИАГРАММ

При анализе временных рядов широко применяются графические методы. Это объясняется тем, что табличное пред­ставление временного ряда и описательные характеристики не по­зволяют понять характер процесса, а по графику временного ряда можно сделать определенные выводы, которые потом могут быть проверены с помощью расчетов:

· наличие тренда и его характер;

· наличие сезонных и циклических компонент;

· степень плавности или прерывистости изменений последова­тельных значений ряда после устранения тренда.

Так графический анализ ряда обычно задает направление его дальнейшего анализа. В Excel для этого можно использовать сред­ство Мастер диаграмм.

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

Работа Мастера диаграмм состоит из четырех основных шагов, выполнение которых рассмотрим на следующем примере.

Пример 3.3. Построить график временного ряда Индекс потре­бительских расходов, выделить тренд этого временного ряда и сделать прогноз на два шага вперед. Исходные данные по этому временному ряду за 16 месяцев приведены в табл. 3.10.

Таблица 3.10

Индекс потребительских расходов

Шаг 1. Выбор типа и вида диаграммы. Во вкладке Стандартные можно увидеть основные типы диаграмм. На рис. 3.18 на вкладке Стандартные выделен тип График. Выбрав вид График с маркерами, необходимо нажать на кнопку Далее.

Шаг 2. Выбор и уточнение ориентации диапазона данных и ряда. На экране появилось диалоговое окно, показанное на рис 3.19. Вкладка Диапазон данных позволяет выполнить следующие операции:

• выбрать (или изменить) диапазон данных листа. Если перед началом работы с Мастером диаграмм данные не были вы­делены, то, используя это поле, можно выбрать их сейчас;

• уточнить ориентацию диапазона данных диаграммы с по­мощью переключателя Ряды в строках и столбцах. При выборе переключателя В строках строки рабочего листа будут рассматриваться как ряды диаграммы, а при выборе переключателя В столбцах - рядами диаграммы будут стол­бцы данных.

Вкладка Ряд позволяет управлять параметрами каждого ряда диаграммы. С ее помощью можно выполнить следу­ющие операции:

• добавлять и удалять ряды;

• присваивать рядам имена;

• выделять (или переопределять) данные, используемые для построения рядов;

• изменять подписи категорий.

Шаг 3. Настройка диаграммы. Это наиболее сложный этап работы Мастера диаграмм. В появившемся диалоговом окне пред­лагается большое количество самых различных параметров диаграммы (рис.3.20). Если параметры не изменяются, то используется установленное по умолчанию значение.

Шаг 4. Выбор месторасположения диаграммы. На этом шаге определяется месторасположение созданной диаграммы (рис. 3.21).

На рис. 3.22 приведен результат работы Мастера диаграмм.

Excel предоставляет дополнительные возможности по работе с диаграммами. Наиболее полезной с точки зрения анализа времен­ных рядов представляется возможность создания линий тренда.

Построение линий тренда

Для описания закономерностей в исследуемом временном ряду строятся линии тренда. В табл. 3.11 приведены типы линий трен­да, используемые в Excel.

Таблица 3.11

Типы линий тренда и их математические уравнения

Для добавления линии тренда в диаграмму выполните следую­щие действия:

1) щелкните правой кнопкой мыши на одном из рядов диаграммы;

2) выберите команду Добавить линию тренда из контекстного меню. На экране появится диалоговое окно Линия тренда (рис. 3.23);

3) выберите тип регрессии. Если это Полиномиальная регрессия введите значение степени в поле Степень. Если же вы выбрали тип Скользящее среднее (который не является регрессией), то в поле Точки введите число точек, необходимых для вычисления средней величины;

4) убедитесь в том, что ряд, для которого необходимо построить линию тренда, выделен в списке Построение линии тренда на ряде. Если нет, то выделите его, а затем переключитесь на вкладку Параметры (рис. 3.24);

5) в разделе Название аппроксимирующей (сглаженной) кривой уста­новите переключатель Автоматическое или Другое, после чего введите название кривой. Оно появится в легенде диаграммы;

6) если линия тренда создается с помощью регрессии (т.е. на вкладке выбран любой тип, кроме скользящего среднего), то в соответствующих полях можно ввести прогнозируемое коли­чество периодов, которые будут добавлены к линии тренда;

7)в случае необходимости можете установить и остальные пара­метры (они могут быть доступны или недоступны в зависи­мости от выбранного типа регрессии).

Так, можно установить пересечение с осью У, отображение на диаграмме уравнения или величины достоверности аппроксимации (R2)[9];

8) щелкните на кнопке ОК для завершения процесса создания линии тренда.

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

Пример 3.4. Для временного ряда Затраты на рекламу выбрать наилучший вид тренда и построить графический прогноз на два шага вперед. Данные за 16 месяцев приведены в табл. 3.12.

Таблица 3.12

Затраты на рекламу по месяцам

Решение

Для решения поставленной задачи необходимо выполнить следующую последовательность действий:

• выделить ячейки А1:А17, содержащие наименование времен­ного ряда и исходные данные;

• вызвать Мастер диаграмм:

• Шаг 1. Выбрать тип диаграммы — График; вид — Первый;

• Шаг 2. Щелкнуть кнопку Далее;

• Шаг 3. В появившемся окне снова выбрать кнопку Далее;

• Шаг 4. Щелкнуть кнопку Готово. На экране появится пост­роенный график;

• щелкнуть правой кнопкой на линии графика. График выделен метками (рис. 3.26);

у = -0,0006х⁵ + 0,0292х⁴ - 0,5515х³ + 4,319х² - 11,616х + 12,831

R² = 0,7008

• в диалоговом окне Линия тренда выбрать тип Линейная (потом Полиномиальная второй степени, потом Полиномиальная пятой сте­пени);

• на вкладке Параметры назначаем: Показывать уравнение на диаграмме, Поместить на диаграмму R2, Прогноз на два шага;

• для построения прогноза выбрать модель с наибольшим коэф­фициентом детерминации R2;

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

Вопросы и задачи для самостоятельного решения

1. В чем суть прогнозирования экономических процессов на ос­нове метода экстраполяции?

2. На какие компоненты в общем случае можно разложить уровни временного ряда?

3. Перечислите методы выявления наличия тренда.

4. Укажите правильный вид линейной модели временного ряда:

а) Y(t) = a₀ + a₁t + a₂t²;

б) Y(t) = a₀ + a₁k;

в) Y(t) = a₀ + a₁t;

г) Y(t) = a₀ + а_хе^t.

5. Чем определяется качество математической модели временного ряда:

а) случайным характером остаточной компоненты;

б) альтернативностью и системностью подхода к моделированию;

в) адекватностью и точностью модели;

г) интервальным прогнозом.

6. Каким образом выполняется оценка адекватности трендовых моделей?

7. Назовите статистические критерии оценки точности моделей прогнозирования.

8. От каких факторов зависит ширина доверительного интерва­ла прогноза?

Задача 3.1. Имеются данные за 9 месяцев об уровне безработи­цы у_t (в % к общему числу трудоспособного населения области):

Проверьте наличие тренда, гарантируя результат с вероятнос­тью Р=0,9 ( = 1,89; F_кp = 5,34). Отобразите на графике факти­ческие данные.

Задача 3.2. Дан временной ряд котировок евро за январь 2000 г. (по дням):

Определить прогнозные значения данного показателя на сле­дующие 2 дня с использованием модели У = а₀ + a₁t. Табличное значение критерия Стьюдента:

t _табл ( = 0,1;k=n-2 = 5) = 2,01.

Задача 3.3. Оценить адекватность модели f(t) = 22,89 - 1,25t, описывающей временной ряд Y(t) = (25; 17; 18; 16; 20; 15; 14), на основе исследования:

• случайности остаточной компоненты по критерию пиков;

• независимости уровней ряда остатков по d-критерию (в каче­стве критических используйте уровни d₁ = 1,08 и d₂ = 1,36) или по первому коэффициенту корреляции, критический уровень которого r(1) = 0,36;

• нормальности распределения остаточной компоненты по RS-кри­терию с критическими уровнями 2,7—3,7.