ЛАБОРАТОРНАЯ РАБОТА № 7. МОДЕЛИ ПРОГНОЗИРОВАНИЯ

Изучение методов прогнозирования значений экономических показателей с помощью функций и пакета анализа в Excel.

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

− скользящее среднее;

− составление линейных прогнозов в однофакторном регрессионном анализе;

− составление нелинейных прогнозов в однофакторном регрессионном анализе;

− экспоненциальное сглаживание.

Прогнозы с применением метода скользящего среднего

Задание 1

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

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

1. Внесите в столбец А, начиная с ячейки А1, следующие фактические дан-ные по объемам продаж за прошедшие месяцы: 593, 570, 486,854,797,362, 594, 271, 45, 254, 433, 529,994,319, 610, 748. 832,193,720,415, 536,850, 201,833.

Эти данные называются базовой линией.

Примечание: базовая линия–это числовое выражение результатов наблюдений напротяжении длительного периода времени. Каждое ее значение – это данное, снятое в оди-наковые временные интервалы. Пропуск данных не допускается. Если данное отсутствует, то его нужно восполнить его хотя бы как среднее между соседними значениями.

2. Составьте прогноз на каждый месяц заданного временного ряда, исполь-зуя метод скользящего среднего. В этом методе для усреднения используйте интервал в три месяца, предшествующих прогнозу. Составьте сна-чала прогноз по функции СРЗНАЧ, введя в ячейку В4 форму-лу =СРЗНАЧ(А1:АЗ) и затем скопировав ее в ячейки В5:В24.

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

− выберите команду СЕРВИС -> НАДСТРОЙКИ и в окне «Надстройки» установите флажок в строке Пакет анализа. Нажмите на кнопку ОК;

− приступите к работе с методом: выберите СЕРВИС -> АНАЛИЗ ДАН-НЫХ и в окне «Анализ данных» выберите инструмент анализа Скользя-щее среднее. Нажмите на кнопку ОК;

− в окне «Скользящее среднее» в поле Входной интервал введите ряд дан-ных вашей базовой линии А1:А24, выделив его в рабочем листе Excel (диапазон будет занесен в абсолютных ссылках);

− в поле Интервал введите количество месяцев, которые вы хотите вклю-чить в подсчет скользящего среднего. В данном случае это будет число 3;

− в поле Выходной интервал введите адрес ячейки, с которой хотите начать вывод результатов, щелкнув по этой ячейке. Пусть это будет ячейка С1 на

том же рабочем листе. Щелкните ОК.

Примечания:

1. Так как скользящее среднее в данном случае вычисляется по данным трех предше-ствующих месяцев, то в начальном периоде базовой линии будут потери прогнозов (Н/Д).

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

4. Составьте график данных прогноза. Для этого опять вызовите окно «Скользящее среднее» и в нем проставьте флажок Вывод графика. Будет выведена диаграмма с графиком фактических значений и прогнозом ли-нией тренда скользящего среднего.

5. Выделите все значения прогнозов в столбце С и сместите их на одну ячейку вниз, присоединив прогнозы к тем периодам, с которыми они свя-заны. Сравните прогнозы с фактом.

6. Проиллюстрируйте прогноз скользящего среднего на диаграмме другим способом. Для этого:

− выделите данные базовой линии А 1 :А24;

− выберите команду ВСТАВКА -> ДИАГРАММА (можно щелкнуть кнопку Мастера диаграмм на панели инструментов) и укажите место для диа-граммы на новом рабочем листе;

− на первом шаге работы Мастера диаграмм проверьте правильность ссы-лок на ячейки базовой линии и нажмите на кнопку Далее;

− на втором шаге выберите тип диаграммы График, и щелкните Далее;

− на третьем шаге выберите вид графика, включающий линии и маркеры (например, вид 1, 4 или 5). Щелкните кнопку Далее;

− на четвертом шаге установите для опции Ряды данных находятся пере-ключатель В столбцах. Установите параметр 0 в опциях Считать столбцы метками оси Х и Считать стр. метками легенды. Щелкните Далее;

− на последнем шаге работы Мастера определите названия диаграмм и осей, а также отображение легенды. Щелкните по кнопке Готово. Про-смотрите построенный график;

− откройте диаграмму с построенным графиком для редактирования, дваж-ды щелкнув по ней кнопкой мыши. В контекстном меню на линии графи-ка выберите команду ЛИНИИ ТРЕНДА. В окне «Линия тренда» во вкладке Тип выберите линию тренда Скользящее среднее, а затем выбе-рите необходимый период с помощью счетчика Точки (период или ин-тервал – это количество наблюдений, которые включаются в вычисление скользящего среднего. В данном примере это 3). Щелкните кнопку ОК;

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

Задание 2

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