Составление линейных прогнозов с помощью функции регрессии ТЕНДЕНЦИЯ

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

В линейных прогнозах линия прогноза является прямой и описывается уравнением регрессии:

у = а · х + b.                                             (10)

В коэффициентах а и b заложены все фактические данные временного ряда. Линейный прогноз является самым простым способом регрессионного анализа.

 

Задание 3

Составьте прогнозы жалоб клиентов фирмы, поступающих на ее продукцию, по данным за последние 10 дней. Используйте для этого функцию ТЕНДЕНЦИЯ для составления линейного прогноза.

Ход решения задачи

1. Внесите количество зарегистрированных за каждый день жалоб, поступивших в течение 10 дней, в ячейки А1: А10. Номера дней внесите в ячейки В1: В10. Данные возьмите из табл. 4.

Таблица 4 – Данные

Количество звонков	0	1	0	2	3	3	3	0	6	17
Номера дней	1	2	3	4	5	6	7	8	9	10

2. Выделите ячейки С1: С10, внесите знак равенства (=) и затем с помощью Мастера функций внесите из категории Статистические следующую формулу:

=ТЕНДЕНЦИЯ(А1: А10; В1: В10).                         (11)

Диапазоны ячеек вводите, выделяя их в рабочем листе. Поскольку функция ТЕНДЕНЦИЯ выдает целый массив результатов, она вводится как формула массива, т. е. одновременным нажатием клавиш Ctrl, Shift и Enter.

3. Проанализируйте прогнозы в диапазоне С1: С10.

Прогноз в диапазоне С1: С10 получен по формуле

у = а · х + b,                                            (12)

где у – количество прогнозируемых звонков; х – номер дня. Но можно потребовать, чтобы прогноз выдавался по уравнению:

у = а · х.                                           (13)

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

4. Вы составили прогноз в пределах временного ряда. А теперь составьте прогноз на день, следующий за периодом временного ряда, т.е. на тот, для которого еще нет результатов наблюдений. Для этого введите в ячейку В11 число 11, а в ячейку С11 внесите с помощью Мастера функций формулу массива

=ТЕНДЕНЦИЯ(А1: А10; В1: В10; В11)                     (14)

и нажмите комбинацию клавиш Ctrl, Shift, Enter. Excel возвратит в ячейку С11 прогноз на 11-й день, подсчитанный по данным базовой линии А1: А10 и связанных с ней временных значений В1: В10.

5. Получите прогноз на дни с 12-го по 18-й. Для этого введите числа 12, 13,

..., 18 в ячейки В12.–В18, выделите ячейки С12: С18 и введите формулу массива:

=ТЕНДЕНЦИЯ(А1: А10; В1: В10; В12: В18)                   (15)

Excel вернет в ячейки С12: С18 прогноз на временные моменты с 12-го по 18-й. Прогноз будет по-прежнему базироваться на связи Между данными базовой линии в А1: А10 и временными значениями в В1: В10.

6. Проиллюстрируйте результаты на диаграмме, выбрав тип диаграммы График. Активизируйте диаграмму и выберите линию тренда Линейная. Во вкладке Параметры проставьте флажок Показывать уравнение на диаграмме.

Примечание: справку по любой функции можно получить в окне «Мастер функций».

 

6.3. Составление нелинейного прогноза с использованием функции РОСТ

Если зависимость значений базовой линии у от временных моментов х носит ярко выраженный нелинейный характер, то в этом случае строить линейный прогноз даже для грубого приближения нет смысла. При нелинейном характере взаимосвязи показателей используют функцию РОСТ. Она возвращает значения у в соответствие с уравнением регрессии:

у = b · т ^ х,                                              (16)

где параметры b и т подбираются оптимальным образом.

 

Задание 4

Требуется составить прогноз на продажу товара, спрос на который резко возрос за последние 10 дней после удачной рекламы. Поскольку этот спрос носит нелинейный характер, то для прогнозирования используйте функцию РОСТ.

Ход решения задачи

1. Внесите данные по спросу в ячейки А1: А10, а номера дней укажите в В1: В10. Информация представлена в табл. 2.

2. Выделите ячейки С1: 10 и, нажав комбинацию клавиш Ctrl, Shift, Enter,

введите формулу массива:

=РОСТ(А 1: А 10; В1: В10).                              (17)

3. Результаты прогноза сравните с данными базовой линии.

4. Спрогнозируйте спрос на 11-й и 12-й дни. Для этого внесите эти числа в В11: В12, выделите диапазон С1: С12 и введите формулу массива:

=РОСТ(А1: А10; В1: В10; В1: В12).                           (18)

Excel возвратит в ячейки С1: С12 прогнозные значения, подсчитанные по данным диапазона А1: В10.

5. Чтобы сравнить на диаграмме базовую линию с прогнозом линейным и нелинейным, подсчитайте линейный прогноз. Выделите ячейки DLD12 и введите формулу массива:

=ТЕНДЕНЦИЯ(А1: А10; В1: В10: В1: В12).                    (19)

6. Постройте диаграмму График по данным базовой линии А1: А10. На этой же диаграмме постройте линии по данным линейного и нелинейного прогнозов. Для этого:

- выделите ряд данных D1: D12 и выполните команду ПРАВКА > КОПИРОВАТЬ. Активизируйте диаграмму (щелкнув по ней) и выполните ПРАВКА > ВСТАВИТЬ. Вы построили линию линейного прогноза;

- выделите ряд данных С1: С12 и аналогично постройте линию нелинейного прогноза на той же диаграмме. Сравните оба прогноза с фактом.

7. Щелкните дважды кнопкой мыши по диаграмме для ее редактирования. Далее в контекстном меню на базовой линии выберите ЛИНИИ ТРЕНДА. В окне выберите тип Линейная и затем откройте вкладку Параметры. В поле Вперед на введите количество периодов, на протяжении которых линия тренда будет продолжена вперед. Установите флажок опции Показывать уравнение на диаграмме.

Затем на данных базовой линии постройте линии тренда Степенная, Логарифмическая и Полиномиальная. Какая из линий тренда лучше описывает фактические данные?

 

6.4. Прогнозирование с использованием экспоненциального сглаживания

Сглаживание – это способ, обеспечивающий быстрое реагирование прогноза на поведение базовой линии. Если в функциях ТЕНДЕНЦИЯ и РОСТ применяется одна и та же формула y = f(x) ко всем точкам прогноза, то при сглаживании учитывается предыдущий прогноз, что хорошо отражает резкие скачки данных базовой линии. Функция сглаживания имеет следующий вид:

F (t+1) = F (f) + L · E (t),                                       (20)

где t – номер временного периода (месяца, дня, года);

F(f) – прогноз, сделанный в момент времени t;

F(t+1) – следующий прогноз;

L – константа сглаживания;

E(t) –погрешность, равная разности между прогнозом F(t) и фактическими данными в момент t.

В надстройке Excel Пакет анализа имеется инструмент Экспоненциальное сглаживание.

 

Задание 5

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

Ход решения задачи

1. Внесите данные базовой линии в ячейки А1: А10, используя данные табл. 5.

Таблица 5 – Данные базовой линии

Данные базовой линии

10

4

5

5

7

8

6

20

19

20

2. Загрузите надстройку СЕРВИС > НАДСТРОЙКИ и в окне установите флажок опции Пакет анализа.

3. Активизируйте инструмент Excel Экспоненциальное сглаживание. Для этого выполните команду СЕРВИС > АНАЛИЗ ДАННЫХ и в окне «Анализ данных» выберите инструмент Экспоненциальное сглаживание. В его окне введите следующие параметры:

- в поле Входной интервал введите диапазон А1: А10;

- установите флажок Метки;

- в поле Выходной интервал введите ссылку на ячейку В1;

- параметр Фактор затухания Ф введите равным 0, 7;

- проставьте флажок Вывод графика.

Excel возвращает результат в ячейки, начиная с В1, т.е. в диапазон ячеек В1: В10.

Фактор затухания Ф равен 1-L, где L – константа сглаживания. Чтобы составить прогноз на период, следующий за последним показателем базовой линии, введите во Входной интервал на одну (на 2, на 3 и т. д.) ячейку больше.

4. Создайте на диаграмме линию тренда Линейная. Сравните графики.

5. Выполните экспоненциальное сглаживание с Ф = 0, 5. Прогнозные значения внесите в С1: С10. Постройте график на той же диаграмме. Сравните результаты.

Примечание: выбор фактора затухания Ф зависит от наблюдений. Чем меньше фактор затухания, тем точнее прогноз отражает последние данные базовой линии, а чем больше – тем сильнее будет отставание прогноза от этих данных. Обычно выбирают фактор затухания Ф ≥ 0, 7. Дальнейшее исследование его связано с уровнем автокорреляции (см. функцию КОРРЕЛ) и с сезонным сглаживанием.

 

Задание 6

Занесите в базовую линию свои данные и выполните следующие операции:

1. На диаграмме График просмотрите характер базовой линии и подберите соответствующий метод прогнозирования.

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

3. Результат отразите на диаграмме. Выдайте несколько линий тренда.

4. Для сравнения выдайте прогноз еще каким-нибудь методом. Отразите на диаграмме также и его линию тренда.

5. Найдите оценки среднеквадратичного отклонения для каждой из построенных моделей по формуле:

6. Выберите для прогнозирования модель с наименьшей оценкой.

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

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

⇐ Предыдущая123456

Поделиться: