Короткий ответ: для прогноза спроса в Excel используются методы скользящего среднего (=СРЗНАЧ) и экспоненциального сглаживания (надстройка «Пакет анализа» или ручная формула). Источник данных — ряды Росстата из ЕМИСС (промпроизводство, цены, доходы населения). Точность прогноза проверяется через MAPE — средняя ошибка для простых методов составляет 5–15 % при горизонте 3–6 месяцев.
Прогноз спроса — основа любого бизнес-плана. Без него невозможно обосновать выручку, потребность в мощностях, график инвестиций. При этом сложные эконометрические модели (ARIMA, Prophet) для большинства проектов избыточны. В нашей практике для 80 % промышленных проектов достаточно двух простых методов временных рядов, реализуемых в стандартном Excel.
Скользящее среднее: первый шаг к прогнозу
Скользящее среднее (Moving Average) сглаживает случайные колебания и выявляет тренд. Идея: прогноз на следующий период равен среднему за последние n периодов. Чем больше n, тем сильнее сглаживание, но ниже чувствительность к изменениям тренда.
В Excel реализация: столбец A — дата, B — фактические продажи. В ячейке C6 пишется =СРЗНАЧ(B3:B5) для 3-месячного окна и копируется вниз. Для 6-месячного окна — =СРЗНАЧ(B2:B7) и так далее. Выбор окна определяется волатильностью ряда: чем выше волатильность, тем шире окно. Для сезонных продуктов (стройматериалы, удобрения) оптимальное окно — 12 месяцев, чтобы захватить полный цикл.
Практическое правило: окно скользящего среднего должно перекрывать минимум один полный цикл сезонности. Для ежемесячных данных — 12 месяцев, для квартальных — 4 квартала. Если сезонности нет, начинайте с n = 3 и увеличивайте, пока MAPE не перестанет снижаться.
Экспоненциальное сглаживание: простой вес для свежих данных
Экспоненциальное сглаживание (Exponential Smoothing) решает главный недостаток скользящего среднего — все наблюдения в окне имеют одинаковый вес. В реальности свежие данные важнее данных трёхмесячной давности. Метод присваивает веса, убывающие по экспоненте: коэффициент α (0 < α < 1) определяет скорость убывания.
Формула в Excel: F(t+1) = α × Y(t) + (1 − α) × F(t), где Y(t) — фактическое значение, F(t) — прогноз на текущий период. Для начального F(1) обычно берётся Y(1). α подбирается минимизацией MSE: типичные значения — 0,1–0,3 для стабильных рынков, 0,4–0,6 для быстро меняющихся.
В Excel можно настроить надстройку «Пакет анализа» — инструмент «Экспоненциальное сглаживание», где задаётся α и строится прогноз с доверительными интервалами. Либо использовать ручной расчёт с таблицей и подбором α через =СУММКВРАЗН(факт; прогноз) и «Поиск решения».
Использование рядов Росстата для прогноза
Росстат — основной источник официальных временных рядов для прогнозирования спроса в B2B-проектах. Наиболее полезные для бизнес-планов:
- Производство по ОКПД2 — помесячные данные о выпуске продукции в натуральном и стоимостном выражении. Позволяют оценить динамику отрасли и ёмкость рынка.
- Индекс цен производителей (ИЦП) — обязателен для дефлирования выручки и прогноза затрат.
- Инвестиции в основной капитал — косвенный индикатор спроса на промышленное оборудование.
- Реальные располагаемые доходы населения — для потребительских рынков коррелируют со спросом с лагом 3–6 месяцев.
Данные загружаются через ЕМИСС (emiss.ru) в формате Excel или CSV. Типичный ряд — помесячно с 2015 года, 120–130 точек. Этого достаточно для скользящего среднего и экспоненциального сглаживания. Важный нюанс: ряды Росстата публикуются с задержкой 1–2 месяца, поэтому последние 1–2 точки часто требуют дооценки на основе оперативной статистики (ФНС, отраслевых ассоциаций).
Пример: для прогноза спроса на металлорежущий инструмент мы использовали ряд Росстата «Производство станков» (ОКПД2 28.41) за 2018–2025 гг. Скользящее среднее с окном 12 мес. дало MAPE 8,3 %. Экспоненциальное сглаживание с α = 0,25 — 7,9 %. Прогноз на 2026 год: рост 4–6 % к уровню 2025-го.
Контроль точности: MAPE и проверка на тестовой выборке
Любой прогноз нужно проверять. Стандартный приём: разделить ряд на обучающую выборку (80 % данных) и тестовую (20 %). Построить модель на обучающей, рассчитать прогноз на тестовый период и сравнить с фактом.
Метрика — MAPE (Mean Absolute Percent Error): среднее по модулю отклонение прогноза от факта в процентах. Для простых методов временных рядов на промышленных данных MAPE в 5–10 % на горизонте 3–6 месяцев считается хорошим результатом. Выше 15 % — модель непригодна для бизнес-плана, требуется пересмотр метода или учёт внешних факторов (регрессоров).
Какую формулу использовать в Excel для скользящего среднего?
Для скользящего среднего в Excel проще всего использовать =СРЗНАЧ(диапазон) с относительной ссылкой. Например, для 3-месячного скользящего среднего: =СРЗНАЧ(B2:B4) с копированием вниз. Также есть встроенная функция =СРЗНАЧА и надстройка «Пакет анализа» с инструментом «Скользящее среднее», который строит прогноз и стандартные ошибки.
Где взять официальные данные Росстата для прогноза спроса?
Основные источники Росстата: раздел «Официальная статистика» — «Предпринимательство» (производство в натуральном выражении по ОКПД2), «Цены» (ИЦП, ИПЦ), «Население» (доходы, численность). Данные выгружаются в Excel через ЕМИСС (emiss.ru) — единую межведомственную информационно-статистическую систему, где доступны ряды с 2000 года по большинству показателей.