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

Сглаживание – это способ, обеспечивающий быстрое реагирование про-гноза на поведение базовой линии. Если в функциях ТЕНДЕНЦИЯ и РОСТ применяется одна и та же формула 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

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

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

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. Найдите оценки средне-квадратичного отклонения для каждой из постро-енных моделей по формуле:

(21)

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

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

(22)

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

БИБЛИОГРАФИЧЕСКИЙ СПИСОК

1. Бондаренко, С.Excel 2003.Популярный самоучитель/С.Бондаренко,М. : Бондаренко. – СПб. : Питер 2005. – 320 с.

2. Гельман, В.Я.Решение математических задач средствамиExcel /В.Я. Гельман. – СПб. : Питер, 2003. – 240 с.

3. Долженков, В.Microsoft Excel 2003 /В.Долженков. –СПб. :Питер,2004. – 432 c.

4. Златопольский, Д.М.1700заданий поMicrosoft Excel.Основы информатики / Д.М. Златопольский. – СПб. : «БХВ–Петербург», 2003. – 544 с.

5. Курбатова, Е.А. Microsoft Excel 2003. Краткое руководство /Е.А. Курбатова. – М. : Диалектика, 2003. – 288 с.