Составление прогнозов скользящего среднего с помощью диаграмм

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

Если эти недостатки вам не слишком мешают, то можете вычислить скользящее среднее, выполнив следующие шаги.

1. Выделите данные своей базовой линии (данные с рис.3.1).

2.Щелкните на кнопке Мастер диаграмм, расположенной на стандартной панели инструментов

3. На первом шаге работы средства Мастер диаграмм проверьте правильность ссылок на ячейки базовой линии и щелкните на кнопке Далее.

4. На втором шаге выберите тип диаграммы График.

5. На третьем шаге выберите вид графика, включающий как линии, так и маркеры.

6. На последнем шаге работы мастера определите названия диаграммы и осей, а также необходимость отображения легенды в соответствующих полях диалогового окна. Щелкните на кнопке Готово.

7. Выделите ряд данных диаграммы, а затем, нажав правую кнопку мыши выберите команду «Добавить линию тренда».

8. В появившемся диалоговом окне Линия тренда щелкните на корешке вкладки Тип. Выберите линию тренда Скользящее среднее (Линейная фильтрация), а затем — необходимые периоды с помощью счетчика Точки. Период — это количество наблюдений, которое включается в любое вычисление скользящего среднего.

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

10. Результаты представлены на рисунке 3.5.

 

 

Рисунок 2.5 - Поскольку скользящее среднее базируется на данных предыдущих наблюдений, они имеют тенденцию запаздывать по сравнению с изменениями в базовых линиях, лежащих в их основе

 

Закончив выполнение вышеперечисленных действий, вы увидите на графике линию тренда скользящего среднего (вместе с фактическими данными наблюдений), как это показано на рисунке 3.5. Первые несколько показателей скользящего среднего отсутствуют по той же причине, по которой средство Скользящее среднее возвращает вместо этих показателей #Н/Д. Дело в том, что скользящее среднее, включающее данные трех предшествующих наблюдений, не может быть вычислено до тех пор, пока не будет закончено наблюдение за третьим периодом.

 

Алгоритм выбора оптимального значения m для скользящего среднего.

 

Рисунок 3.6 - Исходная таблица со значениями на листе

«Выбор оптимального m»

 

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

1. Создайте новый лист с названием «Выбор оптимального m».

2. На основе данных, приведенных в таблице на рисунке 3.6, на рабочем листе MS Excel создается таблица, заполняемая данными исходного временного ряда.

3. Формируются и заносятся в таблицу данные сглаженных временных рядов для 2-х, 3-х и 4-х месячного скользящего среднего.

4. Строятся графики исходного временного ряда и сглаженных временных рядов.

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

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

 

Переходим к реализации решения задачи.

1. Заполняем диапазон ячеек A5:B15 рабочего листа «Выбор оптимального m» данными временного ряда из таблицы рис. 3.6. В результате получаем таблицу со значениями, приведенную на рис. 3.6.

2. По данным временного ряда из диапазона ячеек A5:B15 строим на основе метода скользящего среднего три модели исследуемой зависимости по данным за 2, 3 и 4 предыдущих месяца соответственно. Значения полученных сглаженных временных рядов располагаем соответственно в диапазонах ячеек C7:С16; D8:D16; E9:E16.

Сначала строим ряд значений скользящего среднего по двум месяцам:

1. в ячейку C7 заносим формулу =СРЗНАЧ(B5:B6) и, используя маркер заполнения, копируем ее на диапазон ячеек C8:C16, в результате чего диапазон ячеек C7:C16 заполняется вычисленными показателями 2-х месячного скользящего среднего.

2. Аналогично строятся ряды значений 3-х и 4-х месячного скользящего среднего:

- в ячейку D8 вводим формулу =СРЗНАЧ(B5:B7) и, используя маркер заполнения, копируем ее на диапазон ячеек D9:D16, в результате чего диапазон ячеек D8:D16 заполняется показателями 3-х месячного скользящего среднего;

- вводим в ячейку E9 формулу =СРЗНАЧ(B5:B8) и маркером заполнения копируем ее на диапазон ячеек E10:E16, в результате чего диапазон ячеек E9:E16 заполняется показателями 4-х месячного скользящего среднего.

3. На рисунке 3.7 приведены таблицы с результатами для 2-х, 3-х и 4-х месячного скользящего среднего.

 

Рисунок 3.7 - Таблица значений для 2-х, 3-х, 4-х месячного скользящего среднего

 

Рисунок 3.8 - Графики исходного временного ряда и сглаженных временных рядов (линии тренда скользящего среднего (за 2, 3 и 4 месяца))

 

На рисунке 3.8 приведен график исходного временного ряда и построенные относительно него прогнозные линии тренда скользящего среднего (за 2, 3 и 4 месяца). Отметим, что эти графики строились по стандартной методике построения диаграмм в MS Excel.

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

Рассчитаем абсолютное отклонение:

1. В ячейку B20 (абсолютное отклонение по 2 месяцам) запишем формулу =ABS(B9-C9) и маркером заполнения копируем ее на диапазон ячеек B21:B26.

2. В ячейку С20 (абсолютное отклонение по 3 месяцам) запишем формулу =ABS(B9-D9) и маркером заполнения копируем ее на диапазон ячеек C21:C26.

3. В ячейку D20 (абсолютное отклонение по 4 месяцам) запишем формулу =ABS(B9-E9) и маркером заполнения копируем ее на диапазон ячеек D21:D26.

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

- Для скользящего среднего по 2 месяцам по формуле =СРЗНАЧ(B20:B26).

- Для скользящего среднего по 3 месяцам по формуле =СРЗНАЧ(C20:C26).

- Для скользящего среднего по 4 месяцам по формуле =СРЗНАЧ(D20:D26).

5. Рассчитанные значения представлены на рисунке 3.9.

 

 

Рисунок 3.9 - Таблица абсолютных отклонений

 

Рассчитаем относительное отклонение:

1. В ячейку B31 (относительное отклонение по 2 месяцам) запишем формулу =ABS((B9-C9)/B9)*100) и маркером заполнения копируем ее на диапазон ячеек B31:B37.

2. В ячейку С31 (относительное отклонение по 3 месяцам) запишем формулу =ABS((B9-D9)/B9)*100 и маркером заполнения копируем ее на диапазон ячеек C31:C37.

3. В ячейку D31 (относительное отклонение по 4 месяцам) запишем формулу =ABS((B9-E9)/B9)*100 и маркером заполнения копируем ее на диапазон ячеек D31:D37.

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

- Для скользящего среднего по 2 месяцам по формуле =СРЗНАЧ(B31:B37).

- Для скользящего среднего по 3 месяцам по формуле =СРЗНАЧ(C31:C37).

- Для скользящего среднего по 4 месяцам по формуле

=СРЗНАЧ(D31:D37).

5. Рассчитанные значения представлены на рисунке 3.10.

 

 

Рисунок 3.10 - Таблица относительных отклонений

 

Рассчитаем среднее квадратичное отклонение:

1. В ячейку B42 (абсолютное отклонение по 2 месяцам) запишем формулу =КОРЕНЬ(СУММКВРАЗН(B9:B15;C9:C15)/СЧЁТ(B9:B15)).

2. В ячейку С42 (абсолютное отклонение по 3 месяцам) запишем формулу =КОРЕНЬ(СУММКВРАЗН(B9:B15;D9:D15)/СЧЁТ(B9:B15)).

3. В ячейку D42 (абсолютное отклонение по 4 месяцам) запишем формулу =КОРЕНЬ(СУММКВРАЗН(B9:B15;E9:E15)/СЧЁТ(B9:B15)).

4. Рассчитанные значения представлены на рисунке 2.11.

 

 

Рисунок 3.11 - Таблица среднего квадратичного отклонений

 

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

Вывод: Из приведенных таблиц следует, что для сглаживания исходного временного ряда и составления прогноза о тенденции изменения прибыли фирмы предпочтительнее модель 2-х месячного скользящего среднего, поскольку она более точно реагирует на колебания исходного временного ряда и имеет меньшие ошибки прогнозирования (среднее абсолютное, среднее относительное, среднее квадратичное). Прогнозное значение прибыли фирмы на 12 месяц – 8325 тыс. руб.