Функции прогнозирования в табличном процессоре.

Общий алгоритм выполнения прогнозирования:

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

1. Выделение трендовой составляющей как функции регрессии от нескольких факторов – обязательный этап прогнозирования;

2. Выделение сезонной составляющей в виде сезонных коэффициентов;

3. Трендовая и сезонная составляющая комбинируются (с помощью операции сложения или умножения) в единую функцию прогнозирования – виртуальный (не вычислительный) этап;

4. Вычисляются прогнозные значения как значения функции «прогнозирования» при будущих значениях факторов.

Качество прогноза напрямую зависит от качества построенной модели данных

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

1. Подготовка данных. Этап предварительного анализа имеющихся данных: анализ резко выделяющихся наблюдений, восстановление пропущенных данных, исключение факторов, явно не влияющих на прогнозируемую переменную У;

2. Выделение трендовой составляющей – подбор функций, аппроксимирующих трендовую компоненту, и вычисление параметров этих функций, отбор значимых факторов;

3. Выделение сезонной составляющей – вычисление сезонных коэффициентов по разностям между фактическими значениями переменной У и вычисленным значениям тренда;

4. Анализ остатков и отбор моделей. После построения функции прогнозирования (модели данных) проводится статистический анализ остатков – разностей между значениями переменной У и вычисленным значением функции прогнозирования. На основе анализа остатков отбираются одна или несколько моделей данных, наиболее адекватно представляющих исходные данные;

5. Вычисление прогнозного значения. На основе отобранных функций прогнозирования вычисляются прогнозные значения.

Суть всех реализованных методов сводится к построению функции регрессии («тренда») с дальнейшей ее экстраполяцией на основе данных за последние 6 месяцев.

Прогноз производится в несколько этапов:

1 Этап. Расчёт исходных данных

2 Этап. Построение функций регрессии различными методами

3 Этап. Выбор функции регрессии по условию минимальной ошибки аппроксимации

4 Этап. Расчёт доверительных интервалов и окончательный прогноз

Как выполнить вычисления с использованием скользящего среднего EXCEL

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

Однако надстройка для вычисления скользящего среднего (линия тренда) связывает прогноз с конечными результатами наблюдений в конкретном среднем значении.

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

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

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

=РЯД (Прогноз;;'Рис 9.4'!SBS2:$В$25;2)

на

=РЯД (Прогноз;;'Рис 9.4'!$В$1:$В$25;2).