Анализ и прогнозирование временных рядов

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

В качестве аппроксимирующей функции проанализировать линейную, полиномиальную, логарифмическую, экспоненциальную и степенную зависимости. Для построения прогноза выбрать модель с наибольшим коэффициентом детерминации R² .

Таблица 2

Неделя
Количество машин

Таблица Excel с исходными данными имеет следующий вид (рис. 2.33).

Рис. 2.33. Таблица Excel с исходными данными

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

Выделим диапазон ячеек А2:К2 и, используя команду График вкладки Вставка (рис. 2.34), построим график (рис. 2.35).

Рис. 2.34. Вкладка Вставка. Команда График

Рис. 2.35. График – Динамика продаж автомашин

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

Рис. 2.36. Команда Добавить линию тренда

контекстного меню

В диалоговом окне Формат линии тренда (рис. 2.37) выбираются предлагаемые типы линии тренда и активизируются опции показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации.

Рис. 2.37. Выбраны параметры линии тренда

В результате получим графики следующего вида (рис. 2.38 -2.).

Рис. 2.38. Тип линии тренда - Линейная

Рис. 2.39. Тип линии тренда - Логарифмическая

Рис. 2.40. Тип линии тренда – Полиномиальная

Рис. 2.41. Тип линии тренда - Степенная

Рис. 2.42. Тип линии тренда – Экспоненциальная

В качестве аппроксимирующей функции выбран полином второй степени – парабола, так как имеет наибольшее значение R²=0,9905, по этому типу тренда и построен прогноз на два шага вперед (рис. 2.43). В нашем примере прогнозируется число проданных автомашин на 11 и 12 неделях (рис. 2.44).

Рис. 2.43. Задан прогноз на два периода вперед

Рис. 2.44. Прогноз на два периода вперед

Так же для построения прогноза можно использовать встроенную статистическую функцию ТЕНДЕНЦИЯ. Заполним диапазон ячеек L1:M1 соответственно числами 11 и 12. Так как функция ТЕНДЕНЦИЯ дает массив ответов, то перед ее вызовом необходимо выделить диапазон ответов, в нашем случае L2:M2. Используя кнопку Мастера функций , вызовем диалоговое окно функции и заполним поля аргументов, как показано на рис. 2.45.

Рис. 2.45. Статистическая функция ТЕНДЕНЦИЯ

По окончании ввода формулы: =ТЕНДЕНЦИЯ(B2:K2;B1:K1;L1:M1) нажмите комбинацию клавиш Ctrl+Shift+Enter.

Результат вычислений показан на следующем рис. 2.46.

Рис. 2.46.

Получили следующий прогноз, если предприятие сохранит динамику продаж автомобилей, то на 11 неделе оно продаст 78 автомашин, а на 12 неделе – 84.

Линейная регрессия

В таблице заданы два временных ряда: первый из них представляет нарастающую по кварталам прибыль коммерческого банка (У), второй ряд – процентную ставку этого банка по кредитованию юридических лиц (Х) за тот же период (табл. 3).

Требуется:

1. Построить однофакторную модель регрессии;

2. Оценить прибыль банка при заданной (принимается пользователем самостоятельно) процентной ставке;

3. Отобразить на графике исходные данные, результаты моделирования.

Таблица 3

Номер наблюдения
Прибыль (У)
% ставка (Х)

Таблица с исходными данными в Excel имеет следующий вид (рис. 2.47).

Рис. 2.47. Таблица с исходными данными

Для вычисления параметров модели составим расчетную таблицу следующего вида (рис. 2.48).

Рис. 2.48. Расчетная таблица

Эта же таблица в режиме индикации формул выглядит так, как показано на следующем рис. 2.49.

Рис. 2.49. Расчетная таблица в режиме

индикации формул

В ячейки С19 и С20 введены формулы для вычисления параметров а₁ и а₀ (рис. 2.50):

Рис. 2.50. Формулы для вычисления параметров а₁ и а₀

Значения самих параметров приведены на рис. 2.51.

Рис. 2.51. Значения параметров а₁ и а₀

Построенная модель зависимости прибыли от величины процентной ставки имеет вид:

.

Для того чтобы определить прибыль при величине процентной ставке равной 30%, необходимо подставить значение х в полученную модель.

В ячейку С22 введена следующая формула (рис 2.52):

Рис. 2.52. Формула для вычисления прогнозной величины прибыли

Прогнозное значение прибыли составит 13 тыс. руб. (рис. 2.53).

Рис. 2.53. Прогнозное значение прибыли

Рассчитаем таблицу остатков (рис. 2.54).

Рис. 2.54. Таблица остатков

Таблица остатков в режиме индикации формул имеет следующий вид (рис. 2.55).

Рис. 2.55. Таблица остатков в режиме индикации формул

Величина отклонения от линии регрессии вычисляется по следующей формуле:

В ячейку С38 введена формула для вычисления величины отклонения с использованием встроенной математической функции КОРЕНЬ (рис. 2.56).

Рис. 2.56. Встроенная математическая функция КОРЕНЬ

Величина отклонения от линии регрессии составляет 3,4401 (рис. 2.57).

Рис. 2.57. Величина отклонения от линии регрессии

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

,

которая введена в ячейку С40.

Коэффициент t_a является табличным значением t – статистики Стьюдента при заданном уровне значимости a и числе наблюдений. Если задать вероятность попадания прогнозируемой величины внутрь доверительного интервала, равную 90% (a = 0,01), число степеней свободы df = 10-1-1, то t_a=1,8595.

Значение U=6,804 (рис. 2.58).

Рис. 2.58. Величина доверительного интервала

Для расчета верхней и нижней границ прогноза соответственно вводим в ячейки С42 и С43 формулы, как показано на следующем рис. 2.59.

Рис. 2.59. Формулы для расчета границ прогноза

Верхняя граница прогноза равна 19,81 тыс. руб., нижняя – 6,20 тыс. руб. (рис. 2.60).

Рис. 2.60. Значения границ прогноза

График исходных данных и результаты моделирования приведены на рис. 2.61.

Рис. 2.61. График модели парной регрессии

Для вычисления параметров модели можно было также использовать встроенные статистические функции, такие как НАКЛОН, ОТРЕЗОК, ЛИНЕЙН, СТОШУХ и др.

Функция НАКЛОН вычисляет наклон линии регрессии, в нашем примере это параметр а₁.

Функция ОТРЕЗОК вычисляет параметр а₀.

Функция ЛИНЕЙН одновременно вычисляет оба эти параметра. Перед вводом функции необходимо выделить диапазон ответов (две ячейки), а после заполнения аргументов функции нажать комбинацию клавиш Ctrl+Shift+Enter.

Функция СТОШУХ вычисляет стандартную ошибку, в нашем примере это величина S_y.

Диалоговое окно встроенной статистической функции НАКЛОН с введенными аргументами показано на рис. 2.62.

Рис. 2.62. Встроенная статистическая функция НАКЛОН

Диалоговое окно встроенной статистической функции ОТРЕЗОК с введенными аргументами показано на рис. 2.63.

Рис. 2.63. Встроенная статистическая функции ОТРЕЗОК

Диалоговое окно встроенной статистической функции ЛИНЕЙН с введенными аргументами показано на рис. 2.64.

Рис. 2.64. Встроенная статистическая функция ЛИНЕЙН

Диалоговое окно встроенной статистической функции СТОШУХ с введенными аргументами показано на рис. 2.65.

Рис. 2.65. Встроенная статистическая функция СТОШУХ

Результат вычислений по встроенным статистическим функциям показан на рис. 2.65.

Рис. 2.66. Результат вычислений по встроенным статистическим функциям

Так же для построения модели можно использовать встроенный инструмент Пакета анализа Регрессия. Для этого на вкладке Данные выберите команду Анализ данных (рис. 2.67).

Рис. 2.67. Вкладка Данные. Команда Анализ данных

В появившемся диалоговом окне Анализ данных выберите инструмент Регрессия (рис. 2.68).

Рис. 2.68. Диалоговое окно Анализ данных.

Инструмент Регрессия

Заполните аргументы диалогового окна Регрессия, как показано на рис 2.69.

Рис. 2.69. Параметры инструмента Регрессия

Excel сгенерирует лист отчета, содержащий следующие таблицы:

· регрессионная статистика (рис. 2.70);

· дисперсионный анализ (рис. 2.71 – 2.72);

· таблица остатков (рис. 2.73),

а также построит график остатков (рис. 2.74).

Рис. 2.70. Регрессионная статистика

Рис. 2.71. Дисперсионный анализ

Рис. 2.72. Дисперсионный анализ. Значения коэффициентов

Рис. 2.73. Вывод остатка

График остатков имеет следующий вид (рис. 2.74).

Рис. 2.74. График остатков

При выполнении этого задания, значения коэффициентов уравнения парной регрессии а₁ и а₀ определялись тремя способами: методом наименьших квадратов, при помощи встроенных статистических функций и используя инструмент Регрессия. В каждом случае был получен один и тот же результат, что говорит о правильности расчета этих параметров.