Строим простой прогноз продаж в Google Таблицах или Excel

Каким будет спрос и объем продаж в ближайшее время? Стоит ли ожидать роста трафика или его падения? Чтобы ответить на подобные вопросы, не обязательно быть аналитиком или проводить исследования. Читайте, как увидеть общие тенденции и потратить на это минимум времени. Опытом и шаблоном отчета делится Константин Рачин, маркетолог Ringostat.

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

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

Примеры применения

1. Для бизнеса, у которого нет штатного аналитика, а специалисты компании не владеют углубленными знаниями в построении прогнозов. Даже в этом случае важно отслеживать тенденции.
2. Для понимания, какой объем продаж ожидается и составления приблизительного плана. Владея этими данными, вы можете увидеть будущее повышение спроса и вовремя закупить товар. Либо, наоборот, принять меры — если ожидается проседание по доходу.
3. Для прогноза трафика. Чтобы видеть, каких показателей ожидать при текущих темпах роста.

Лично я как маркетолог регулярно использую такой отчет в работе. Например, недавно строил с его помощью план по лидам из органики, которые дойдут до демонстрации нашего сервиса. Ниже я приведу более простой пример — построение прогноза по продажам. В качестве инструмента я описал Google Таблицы, поскольку обычно работаю в них, но в Excel подобный прогноз строится по похожему принципу. Там вы тоже сможете, например, сделать прогнозирование выручки.

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

Шаг 1: сбор данных

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

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

Сначала нужно создать таблицу Google или Excel. Т. к. нас интересуют продажи, берем данные по доходу, допустим, за прошедшие 20 месяцев. Чем больше этот период, тем точнее вы получите прогноз. Откуда можно взять данные о продажах:

• запросить у бухгалтерии;
• из CRM;
• из Google Analytics, если у вас подключен модуль электронной коммерции и туда передаются данные о сделках.

В последнем случае вам может пригодиться наш мануал по настройке автоматической передачи данных в Google Таблицы из Google Analytics.

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

Шаг 2: прописываем формулу FORECAST

Формула FORECAST или ПРЕДСКАЗ в русской локализации — определяет предполагаемое значение параметра Y, исходя из существующего значения X.

Рассмотрим это на нашем примере:

• Х — это точка во времени, для которой мы делаем прогноз;
• известные значения Y — это суммы продаж (revenue) за прошедшие месяцы;
• известные значения X — даты или номера прошедших периодов.

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

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

=ROUND(FORECAST(A22;$B$2:$B$21;$A$2:$A$21))  

Разберем ее составляющие.

1. ROUND — округляет полученное значение. У нас задача не получить сумму с точностью до копейки, а выявить тренд. Поэтому целые величины будут нагляднее.
2. FORECAST — непосредственно прогноз. В шаблоне документа есть дополнительное описание этой функции.
3. A22 — ячейка, для которой нужно сделать прогноз.  
4. $B$2:$B$21 — это диапазон данных Y, известные нам значения зависимой переменной. В данном примере сумма продаж. Значок $ нужен для того, чтобы при протягивании формулы по таблице не менялись поля, которые мы берем для прогноза.
5. $A$2:$A$21 — диапазон дат предыдущих месяцев, за которые нам известны показатели.

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

Шаг 3: создаем график

Цифры для прогноза лучше визуализировать, так данные будут нагляднее. Заходим в раздел Вставка — Диаграмма — Настройки и выбираем тип визуализации График. В качестве диапазона указываем все ячейки с данными:

• ось Х — временной диапазон;
• ось Y — переменная.

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

На примере ниже четко видно, что в целом продажи упадут. Это показывает линия тренда, которая опускается вниз:

Учитывайте, что если у вас сезонный бизнес, то определенное время года тоже сыграет свою роль. В этом случае лучше брать данные за несколько лет. Также для сезонности используют другие инструменты — например, более расширенный метод прогноза, Анализ временных рядов. Но описанный отчет учитывает и пики, поэтому для обобщенного прогноза подойдет.

Вы можете легко изменить вид графика, используя рекомендации из статьи «Наводим красоту в Google Таблицах: лайфхаки по визуализации данных».

Резюме

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

С помощью прогноза вы сможете вовремя заметить, растет или падает трафик, не нужно ли закупить товар. Также это поможет открыть неочевидные инстайты. Бывают случаи, что по графику кажется, что прогноз позитивный, а по линии тренда становится заметно — все не так радужно.