Прогнозирование объема производства в Excel: три интервала уверенности и сезонность

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

ПРОГНОЗИРОВАНИЕ В EXCEL

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

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

Excel содержит функции для прогнозирования по методу экспоненциального сглаживания, которые появились начиная с 2016 версии Excel:

• ПРЕДСКАЗ.ETS — рассчитывает прогнозные значения на основе ретроспективных данных с использованием алгоритма экспоненциального сглаживания;

• ПРЕДСКАЗ.ETS.ДОВИНТЕРВАЛ — возвращает доверительный интервал для прогнозной величины на указанную дату. Доверительный интервал помогает определить точность модели прогнозирования. Чем он меньше, тем выше достоверность прогноза для данной точки.

В Excel встроен удобный инструмент для практического применения указанных функций — Лист прогноза.

Далее рассмотрим, как выполнить прогнозирование объема производства с помощью Листа прогноза, на примере условной производственной компании «Успех».

ПОШАГОВЫЙ АЛГОРИТМ ПРОГНОЗИРОВАНИЯ ОБЪЕМА ПРОИЗВОДСТВА

Перед экономистом компании «Успех» поставлена задача спланировать объем производства на второе полугодие 2026 года. В планово-экономическом отделе накоплена помесячная информация об объемах производства за период с 01.01.2022 по 01.06.2026.

Чтобы спланировать объем производства, воспользуемся Листом прогноза в Excel и пошаговым алгоритмом. Изменяя параметры Листа прогноза, можно в рамках представленного ниже алгоритма создать три варианта прогноза. Сравнение результатов трех прогнозных вариантов поможет выбрать наиболее оптимальный для дальнейшего планирования.

Вариант 1. Прогноз с доверительным интервалом 93 %

Шаг 1. На листе Excel формируем таблицу с исходными фактическими данными (рис. 1):

• столбец временной шкалы (в примере это первый день каждого месяца);

• столбец ретроспективных (фактических) значений объема производства.

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

Если на временной шкале не хватает до 30 % точек данных или есть несколько показателей с одной и той же датой, прогноз все равно будет точным. Однако для повышения точности прогноза рекомендуется перед его созданием обобщить данные.

Шаг 2. Выделяем оба столбца с показателями (см. рис. 1).

Шаг 3. На вкладке Данные в группе Прогноз выбираем «Лист прогноза» (см. рис. 1).

Шаг 4. Создаем Лист прогноза (рис. 2).

В появившемся окне «Создание листа прогноза»:

• выбираем график или гистограмму для визуального представления прогноза. Параметр по умолчанию — график, его и оставим (рис. 2);

• в поле «Завершение прогноза» выбираем дату окончания — 01.12.2026, т.е. декабрь (рис. 2 и 3);

• раскрываем поле «Параметры» (рис. 2 и 3);

• в поле «Доверительный интервал» по умолчанию 95 %, меняем на 93 %. Если флажок «Доверительный интервал» снять, то «Доверительный интервал» будет скрыт. Доверительный интервал 95 % означает, что 95 % будущих точек данных предположительно окажутся в пределах указанного радиуса от результата, спрогнозированного функцией ПРЕДСКАЗ.ETS (с нормальным распределением);

К СВЕДЕНИЮ

Доверительный интервал помогает определить точность прогноза. Чем он меньше, тем выше достоверность прогнозирования для данной точки.

• в поле «Сезонность» параметр по умолчанию «Определять автоматически» оставляем без изменений;

• параметр «Начало прогноза» (01.06.2026) оставляем без изменений. При выборе даты начала, которая наступает раньше, чем заканчиваются статистические данные, для построения прогноза используются только данные, предшествующие ей. Это ретроспективное прогнозирование;

• нажимаем кнопку «Создать».

Рекомендации:

• если в исходных данных прослеживаются сезонные тенденции, то прогнозирование начинают с даты, предшествующей последней точке статистических данных;

• начиная прогноз до последней фактической точки, экономист может почувствовать точность прогнозирования, так как появляется возможность сравнить прогнозируемые ряды с фактическими данными. Если начать прогнозирование со слишком ранней даты, прогноз может отличаться от созданного на основе всех статистических данных. Прогнозирование точнее при использовании всех статистических данных.

Параметры Листа прогноза, которые оставим без изменений:

• «Диапазон временной шкалы» — допускается изменять диапазон, который используется для временной шкалы. Этот диапазон должен соответствовать параметру «Диапазон значений»;

• «Диапазон значений» — допускается изменять диапазон, но он должен совпадать со значением параметра «Диапазон временной шкалы»;

• «Заполнить отсутствующие точки с помощью». Для обработки отсутствующих точек Excel использует интерполяцию. Недостающая точка фактических данных будет завершена средневзвешенным значением соседних точек до тех пор, пока отсутствует менее 30 % точек. Чтобы вместо этого обработать отсутствующие фактические показатели как нули, выберите в списке нули;

• «Объединить дубликаты с помощью». Если фактические данные содержат несколько значений с одной меткой времени, Excel определит их среднее. Чтобы использовать другой метод вычисления (например, Median), выбирают нужное вычисление в списке;

• «Включить статистические данные прогноза». Этот флажок устанавливают, если на новом листе нужно расположить дополнительную статистическую информацию о прогнозе. При этом добавляется таблица статистики, которая включает коэффициенты сглаживания (Alpha, Beta, Gamma) и метрики ошибок (MASE, SMAPE, MAE, RMSE).

Результат первого варианта прогноза объема производства на второе полугодие 2026 г. представлен на рис. 4 и в табл. 1. Таблица и график в Excel формируются автоматически.

Обратите внимание, что для 01.06.2026 объем производства в исходных данных заполнен. Это наш фактический показатель за последний отчетный месяц — 5010 т. В Листе прогноза объем производства 5010 т продублирован в прогнозных значениях указанного временного интервала для подтверждения правильности расчетов.

Так, для 01.11.2026 (ноябрь) прогнозный объем производства составляет 5318 т, интервал — от 3561 до 7075 т. Разброс значительный.