Прогноз продаж в Excel: 3 способа с формулами

Прогноз продаж нужен селлеру не ради красивого графика, а чтобы ответить на два вопроса: сколько заказать и когда. Для этого хватает Excel и нескольких формул. Разберём три способа — от простого к точному: скользящее среднее, линейный тренд и прогноз с учётом сезонности, — с готовыми формулами и тем, где каждый из них ошибается.

Подготовьте данные

Нужна таблица продаж по периодам: в столбце A — даты (неделя или месяц), в столбце B — продажи в штуках. Для одного товара — одна таблица, для нескольких — по столбцу на товар.

Три правила, без которых прогноз будет врать:

• Берите штуки, а не рубли. Цена меняется из-за акций, а заказывать вы будете штуки.
• Отметьте периоды без остатка. Если товара не было на складе неделю, продажи в эту неделю — ноль не потому, что нет спроса. Такие периоды замените средним по соседним или исключите, иначе прогноз занизится. Как оценить потери от дефицита — в статье про out-of-stock и потерянную выручку.
• Не меньше 8–12 периодов для тренда и хотя бы год истории для сезонности.

Способ 1. Скользящее среднее

Самый простой прогноз: следующий период будет как среднее последних N периодов.

Для прогноза по последним 4 неделям, если продажи в B2:B13, в B14:

=СРЗНАЧ(B10:B13)

Как выбрать N: чем меньше, тем быстрее прогноз реагирует на изменения, но тем сильнее скачет от случайных всплесков. Для товаров со стабильным спросом берите 4–8 недель, для быстро меняющихся — 2–4.

Где ошибается: не видит тренда. Если продажи растут, скользящее среднее всегда отстаёт — и вы заказываете меньше, чем нужно.

Способ 2. Линейный тренд

Если продажи устойчиво растут или падают, продлите линию тренда. Пусть номера периодов 1…12 в A2:A13, продажи — в B2:B13. Прогноз на 13-й период:

=ПРЕДСКАЗ.ЛИНЕЙН(13;B2:B13;A2:A13)

В старых версиях Excel та же функция называется ПРЕДСКАЗ. Похожий результат даёт ТЕНДЕНЦИЯ, которая умеет считать сразу несколько будущих периодов. В Google Таблицах — FORECAST или FORECAST.LINEAR.

Проверьте тренд глазами: постройте график продаж и добавьте линию тренда — «Добавить элемент диаграммы» → «Линия тренда» → «Линейная».

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

Способ 3. Прогноз с сезонностью

Для сезонных товаров сначала посчитайте коэффициенты сезонности — во сколько раз каждый месяц отличается от среднего.

Если продажи за 12 месяцев прошлого года в B2:B13, коэффициент января в C2:

=B2/СРЗНАЧ($B$2:$B$13)

Протяните до C13. Коэффициент 1,5 значит, что в этом месяце продаётся в полтора раза больше среднего, 0,6 — на 40% меньше.

Пример: в среднем товар продаётся по 300 штук в месяц, коэффициент ноября — 1,8. Прогноз на ноябрь — 300 × 1,8 = 540 штук.

Если истории два года и больше, берите коэффициенты как среднее по годам — один год может оказаться нетипичным.

Встроенный прогноз Excel. В Excel 2016 и новее есть функция ПРЕДСКАЗ.ETS и кнопка «Данные» → «Лист прогноза». Она сама находит тренд и сезонность и строит прогноз с доверительным интервалом. Нужны даты с равным шагом — например, по неделям или по месяцам — и достаточно длинная история. В Google Таблицах ETS нет.

Подробнее о сезонности и подготовке к пикам — в статье про сезонность спроса и про прогноз под распродажи.

Как проверить точность прогноза

Прогноз без проверки — гадание. Самая понятная метрика — средняя ошибка в процентах (MAPE). Спрячьте последние 4 периода, сделайте прогноз на них по оставшимся данным и сравните с фактом.

MAPE — среднее этих ошибок. Ошибка 10–20% для отдельного товара на маркетплейсе — хороший результат, 50% — повод сменить метод или признать, что спрос у товара случайный. Для таких товаров спасает не точный прогноз, а страховой запас — см. статью про минимальный остаток и safety stock.

От прогноза к заказу

Прогноз сам по себе ничего не решает — его надо перевести в заказ:

Если поставка идёт 30 дней, а заказываете вы на 60 дней вперёд, берите прогноз на 90 дней. Когда именно заказывать, подскажет точка дозаказа, а сколько за раз — формула Уилсона.

Частые ошибки

• Прогноз по дням без остатка. Занижает спрос именно у тех товаров, которые чаще всего кончаются.
• Один метод на всё. Стабильным товарам хватает скользящего среднего, сезонным — нужны коэффициенты.
• Забытые акции. Всплеск продаж в распродажу — не рост спроса. Отметьте такие периоды и не давайте им тянуть прогноз вверх.
• Прогноз «раз и навсегда». Пересчитывайте хотя бы раз в месяц.

Автоматизация

Вести прогноз в Excel по десяти товарам — нормально, по тысяче — нет. Veloseller каждый день считает скорость продаж по каждому SKU с поправкой на дни без остатка и показывает, на сколько дней хватит товара и что пора заказать. Сравнить общие методы прогноза можно в статье прогноз спроса на маркетплейсе.

Итог

Для прогноза продаж в Excel хватает трёх приёмов: скользящее среднее — «СРЗНАЧ» за последние недели, линейный тренд — «ПРЕДСКАЗ.ЛИНЕЙН», и сезонность — коэффициенты месяцев или «Лист прогноза». Считайте в штуках, чистите периоды без остатка, проверяйте точность на отложенных неделях и переводите прогноз в заказ с учётом срока поставки, остатка и страхового запаса.