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

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

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

у = а · х + b.

(10)

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

Задание 3

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

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

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

Таблица 4

Данные

Количество звонков
Номера дней

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. Проиллюстрируйте результаты на диаграмме, выбрав тип диаграммы График. Активизируйте диаграмму и выберите линию тренда Линейная. Во вкладке Параметры проставьте флажок Показывать уравнение на диаграмме.

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

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

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

у = 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.

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

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

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

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

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

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