Как построить простой прогноз в Excel

В Excel можно построить простой прогноз продаж или другого показателя – с учетом сезонности или без.

Самый простой способ – добавить линию тренда на график с показателем.

Допустим, у вас есть график с динамикой продаж. Щелкните правой кнопкой мыши на ряд данных и нажмите «Добавить линию тренда …»:

В окне «Формат линии тренда» выберите тип тренда (в данном случае может подойти линейный), а также на сколько периодов (в данном случае месяцев) вперед построить прогноз. Можно вывести на график коэффициент детерминации (R2) чем он ближе к единице, тем точнее тренд описывает реальные данные, – соответственно, если вы будете сравнивать несколько типов линий тренда, то с помощью этого коэффициента сможете выбрать лучшую.

Результат:

Для прогнозирования не на графике, а в диапазоне ячеек можно воспользоваться функцией ПРЕДСКАЗ (FORECAST). У нее следующий синтаксис:

=ПРЕДСКАЗ(x; известные_значения_y; известные_значения_x).

• Известные_значения_y – старые значения показателя, значения которого вы будете прогнозировать;

• известные_значения_x – значения фактора, влияющего на зависимый показатель y (в качестве x могут выступать и периоды – как в нашем примере);

• x – новое значение фактора.

Если прогнозируемый показатель сильно растет и вы ожидаете продолжения этого роста, воспользуйтесь функцией РОСТ (GROWTH). Синтаксис и принцип работы у нее аналогичный – с той лишь разницей, что прогноз строится по экспоненциальному тренду.

Как быть, если у вашего показателя сильные сезонные колебания? Рассмотрим самый простой и доступный способ.

Разделите продажи за каждый месяц (или другой период) на средние продажи за год или на показатели тренда (значения линейного тренда можно рассчитать с помощью функции ТЕНДЕНЦИЯ (TREND) ее единственным аргументом будут известные значения прогнозируемого показателя).

Это пример с делением на средние продажи:

А это – с делением на тренд:

Если есть данные за несколько лет, рассчитайте среднее за несколько лет для каждого месяца – так вы сможете сгладить статистические выбросы, которые могли иметь место лишь в одном году.

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

Например, для декабря показатель может составить 1,29, то есть в этот месяц высокого сезона показатель (допустим, выручка) на 129 % выше «обычного». Для января, наоборот, коэффициент может быть равен 0,86, к примеру.

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

Если вы хотите построить многопараметрическую регрессию для прогнозирования показателя, зависящего от нескольких факторов, вам понадобится функция ЛИНЕЙН (LINEST). Ее описание, к сожалению, выходит за рамки этой книги – см. список литературы в конце раздела. Кроме того, вы можете обратиться к сайту planetaexcel.ru, другим порталам или справке Excel.