Задание 10. Пример выполнения интервального экспертного прогнозирования

Составьте прогноз продаж мороженого “Пломбир" ЗАО “Белоснежка” на следующий год по месяцам, при условии, что известны объёмы его реализации за два предшествующих года.

Исходная информация представлена в табл. 4.1. Из таблицы видно, что значения объёма продаж имеют выраженный сезонный характер с возрастающим трендом.

Таблица 4. 1.

Фактические объёмы реализации продукции за два года (в тыс. руб.)

№п.п.	Месяц	Объем продаж	№п.п.	Месяц	Объем продаж
Первый анализируемый год			Второй анализируемый год
1	июль	8174,4	1	июль	8991,84
2	август	5078,33	2	август	5586,1626
3	сентябрь	4507,206	3	сентябрь	4957,9267
4	октябрь	2257,199	4	октябрь	2482,9191
5	ноябрь	3400,697	5	ноябрь	3740,7671
6	декабрь	2968,718	6	декабрь	3265,5896
7	январь	2147,143	7	январь	2361,8568
8	февраль	1325,567	8	февраль	1458,1241
9	март	2290,956	9	март	2520,0517
10	апрель	2953,341	10	апрель	3248,6752
11	май	4216,285	11	май	4637,9132
12	июнь	8227,569	12	июнь	9050,3264

Откройте лист MS Excel, и создайте в нем таблицу, фрагмент которой представлен на рисунке ниже.

Заполните в нем ячейки столбцов А, В и С. Присвойте листу имя 4.1. ТРЕНД.

Постройте две линии тренда.

• Сначала выберите тип Линейный. Определите тренд, наилучшим образом аппроксимирующий фактические данные.

• Для этого рекомендуется использовать полиномиальную линию тренда шестой степени, что позволяет сократить ошибку прогнозной модели (рис. 4.3).

Рис. 4. 3. Опция “Линии тренда”

На рис. 4.4 показано, что полиномиальный тренд аппроксимирует фактические данные гораздо лучше, чем предлагаемый обычно в литературе линейный. Коэффициент детерминации полиномиального тренда (0,7435) гораздо выше, чем линейного (4E-05).

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

Применение других типов тренда (логарифмического, степенного, экспоненциального, скользящего среднего) также не даёт такого эффективного результата. Они неудовлетворительно аппроксимируют фактические значения, коэффициенты их детерминации ничтожно малы:

• Логарифмический R² = 0,0166;

• Степенной R² =0,0197;

• Экспоненциальный R² =8Е-05.

Вычитая из столбца фактических значений объёмов продаж столбец значений тренда, определите величины сезонной компоненты (рис. 4.5).

Рис. 4. 5. Расчет Сезонной компоненты

Скорректируйте значения сезонной компоненты таким образом, чтобы их сумма была равна нулю (рис.4.6).

Для этого:

• Переименуйте ярлык второго листа на 4.2. СРЕДНИЕ ЗНАЧЕНИЯ СЕЗОННОЙ КОМПОНЕНТЫ¹⁵.

• Скопируйте в него ячейки А2:А26 столбца "№ п.п." и ячейки Е2:Е26 столбца "Сезонная компонента" таблицы из листа 4.1.ТРЕНД.

• Превратите ее в таблицу, внешний вид которой представлен ниже на рис. 4.6, учитывая, что в столбцы "1-й сезон" и "2-й сезон" заносятся значения столбца "Сезонная компонента" исходной таблицы.

Вычисления в таблице:

• Столбец "Итого" вычисляется суммированием значений столбцов "1-й сезон" и "2-й сезон".

• Столбец "Среднее" находится как среднее значение столбцов "1-й сезон" и "2-й сезон".

• Столбец "Сезонная компонента" корректируют таким образом, чтобы сумма значений стала равной нулю.

• Для этого значение ячейки Е15 - "Сумма средних значений сезонных компонент" разделите на количество расчетных месяцев – 12 и результат поместите в ячейку Е16..

• Значение столбца "Сезонная компонента" определяется как разность между значениями столбца "Среднее" и ячейкой Е16. Так, формула для третьей строки таблицы будет иметь следующий вид:

(4.8)

 Внимание. Вспомните, что абсолютный адрес ячейки можете установить с помощью нажатия клавиши F4.

Рассчитайте ошибки модели как разность между фактическими значениями и значениями модели, как показано на рис 4.6:

Для этого:

• Переименуйте ярлык третьего листа на 4.3. РАСЧЕТ ОШИБОК.

Рис. 4. 6. Расчёт средних значений сезонной компоненты

• Скопируйте в него ячейки А2:А26 столбца "№ п.п." и ячейки С2:C26 и D2:D26 столбцов "Объем продаж" и "Значения тренда" таблицы из листа 4.1. ТРЕНД. Если для ячеек С2:C26 выполняется обычная процедура копирования-вставки, то для ячеек D2:D26 столбца "Значения тренда" - процедура Специальной вставки.

• Выполните копирование ячеек D2:D26 листа 4.1. ТРЕНД.

• Щелкните правой кнопкой мыши в ячейке С2 листа РАСЧЕТ ОШИБОК и выберите команду Специальная вставка.

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

Рис. 4. 7. Диалоговое окно Специальная вставка

• Щелкните по кнопке ОК.

В ячейки С2:С26 листа РАСЧЕТ ОШИБОК будут вставлены только значения, а не формулы

• Выделите ячейки F2:F14 "Сезонная компонента (усредненная)" листа 4.2. СРЕДНИЕ ЗНАЧЕНИЯ СЕЗОННОЙ КОМПОНЕНТЫ.

• Вставьте выделенные значения в четвертый столбец новой таблицы (рис.4.8), начиная с ячейки С2 с помощью процедуры Специальной вставки, как это было описано выше. В ячейки С2:С14 листа 4.3. РАСЧЕТ ОШИБОК будут вставлены только значения, а не формулы.

• Выделите ячейки С3:С14 листа 4.3. РАСЧЕТ ОШИБОК, выполните их копирование и вставку, начиная с ячейки С15. В результате этого, в ячейках С3:С26 будут записаны усредненные значения Сезонной компоненты.

• Выполните форматирование ячейки С2, как указано на рис. 4.8.

• В ячейки E2 и F2 введите заголовки столбцов "Значения модели" и "Отклонения" соответственно.

Вычисления в таблице:

• Столбец "Значения модели" вычисляется суммированием значений столбцов "Значения тренда" и "Сезонная компонента (усредненная)".

• Столбец "Отклонение" находится вычитанием столбца "Значение модели" из столбца "Объем продаж".

• Выполните необходимое форматирование таблицы, как показано на рис. 4.8.

Рис. 4. 8.. Внешний вид таблицы 4.3. Расчет ошибок (фрагмент)

Находим среднеквадратическую ошибку модели (Е) по формуле

Е= О² /  (T+S)² (4.8)

где:

Т- трендовое значение объёма продаж;

S – сезонная компонента;

О- отклонения модели от фактических значений

Е= 0,006955 или 0,696%

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

• В ячейку G5 введите заголовок Квадраты значения модели, в ячейку H5 - Квадраты отклонений, в ячейку А27 - Итого, в ячейку А29 - Среднеквадратическая ошибка модели.

• Выделите ячейки G3:G26.

• Напишите формулу =E3^2

Для вычисления среднеквадратической ошибки модели в рабочем листе :

• Нажмите комбинацию клавиш Ctrl + Enter для ввода формул в ячейки.

• Не снимая выделения, щелкните мышью по кнопке Автосумма панели инструментов Стандартная для подсчета значений суммы по столбцу в ячейке G27.

• В ячейки H3:H26 аналогичным образом введите формулу =F3^2 и подсчитайте значение суммы по столбцу в ячейке H27.

• В ячейке G29 подсчитайте среднеквадратическую ошибку модели по формуле

(4.9)

Перейдите к построению модели прогнозирования:

F = T + S ± E (4.10)

Построенную модель представьте графически:

• Выделите ячейки B2:B26 столбца Объем продаж и Е2:Е26 столбца Значения модели листа 4.3. РАСЧЕТ ОШИБОК и постройте график, представленный на рис 4.9.

Рис. 4. 9. Модель прогноза объёма продаж

На основе модели постройте окончательный прогноз объёма продаж.

Для смягчения влияния прошлых тенденций на достоверность прогнозной модели, предлагается сочетать трендовый анализ с экспоненциальным сглаживанием. Это позволит нивелировать недостаток адаптивных моделей, т.е. учесть наметившиеся новые экономические тенденции:

Fпр t = a Fф t-1 + (1-а) Fм t (4.11)

где:

Fпр t - прогнозное значение объёма продаж;

Fф t-1 – фактическое значение объёма продаж в предыдущем году;

Fм t - значение модели;

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

Константу сглаживания рекомендуется определять методом экспертных оценок, как вероятность сохранения существующих экономических тенденций на основе динамики, прогнозных значений макроэкономических показателей (уровень инфляции, доходы населения, валютный курс, ВНП, объёмы и структура потребления и т.д.) и темпов изменений этих показателей. То есть, если макроэкономические показатели изменяются (колеблются с той же скоростью) амплитудой что и прежде, значит предпосылок к изменению экономических тенденций нет, и, следовательно, а 1, если наоборот, то а  0.

Вы предполагаете, макроэкономические показатели вашего региона достаточно стабильны и выбираете значение коэффициента сглаживания равным 0.8. Однако дополнительно выбираем еще два варианта значений коэффициента сглаживания: 0.7, 0.9. Используя все три коэффициента значения, выполним прогнозирование для третьего сезона.

Дополните на листе 4.3. РАСЧЕТ ОШИБОК имеющуюся таблицу следующим:

• В ячейку I2 введите текст Прогноз при а = 0,8

• В ячейку J2 введите текст Прогноз при а = 0,7

• В ячейку R2 введите текст Прогноз при а = 0,9

• В ячейки I3, J3, K3 введите значения коэффициентов - 0.8, 0.7 и 0.9 соответственно.

• В ячейке I15 подсчитайте прогнозное значение объёма продаж для июля третьего сезона по формуле:

(4.12)

Результат будет иметь следующий вид:

Fм t = 7193,472 + 1668,26763 =8861,739631± 61,68 (руб.)

Число 61.68 показывает величину возможной среднеквадратической ошибки расчетов, равной 0,696%

• С помощью маркера заполнения выполните расчеты в ячейках I16:I26.

• Аналогичным образом выполните прогнозирование для значений коэффициента сглаживания: 0.7и 0.9 в ячейках J15:J26 и K15:K26 соответственно. Получившийся фрагмент таблицы приведен на рис. 4.10.

Рис. 4. 10. Результаты выполнения прогноза на третий сезон при значениях коэффициента сглаживания 0.8, 0.7 и 0.9

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

Для этого можно воспользоваться следующей последовательностью операций (можете придумать собственный способ):

• Используя ячейки листа 4.3. РАСЧЕТ ОШИБОК с подсчитанными ранее результатами подготовьте исходные данные для графического описания процесса (рис. 4.11).

Рис. 4. 11. Таблица исходных данных для графического описания процесса

• Это получается при копировании ячеек основной таблицы с помощью механизма Специальной вставки. Для примера, опишем заполнение ячеек N3:N14.

• Выделите ячейки B15:B26.

• Щелкните в выделенном интервале правой кнопкой мыши / Выберите команду Копировать

• Щелкните правой кнопкой мыши в ячейке N3 / Выделите команду Правка / Специальная вставка.

• В появившемся диалоговом окне Специальная вставка установите переключатель в положение значения / Нажмите ОК.

• Выделите ячейки M2:R14 и постройте график, аналогичный рис. 4.12.

Рис. 4. 12. Сравнение фактических данных за второй сезон, полученной модели и прогнозов для разных значений коэффициентов сглаживания

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

Для учёта новых экономических тенденций рекомендуется регулярно уточнять модель на основе мониторинга фактически полученных объёмов продаж, добавляя их или заменяя ими данные статистической базы, на основе которой строится модель.

Кроме того, для повышения надёжности прогноза рекомендуется строить все возможные варианты прогнозов (от оптимистичного до пессимистичного) и определять доверительный интервал прогноза.