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

Olga Feoktistova
23.04.20193 513 2
1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5,00 out of 5)
Loading ... Loading ...
A A A

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

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

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

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

 

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

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

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

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

 

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

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

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

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

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

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

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

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

 

Шаг 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 — ячейка, для которой нужно сделать прогноз.  Как построить простой прогноз в Google Analytics и Excel
  4. $B$2:$B$21 — это диапазон данных Y, известные нам значения зависимой переменной. В данном примере сумма продаж. Значок $ нужен для того, чтобы при протягивании формулы по таблице не менялись поля, которые мы берем для прогноза.
  5. $A$2:$A$21 — диапазон дат предыдущих месяцев, за которые нам известны показатели.

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

 

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

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

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

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

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

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

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

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

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

 

Резюме

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

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

Категория

Если вы нашли ошибку - выделите её и нажмите Ctrl + Enter или .

  • Ilya Kukushkin

    Что-то не пойму. Forecast — это вроде как линейный регрессионный анализ зависимости функции от одной переменной. Но в вашем примере вы ведете зависимость суммы продаж в зависимости от порядкового номера месяца. Т.е. функция будет строить зависимость суммы продаж от порядкового числа. Т.е. предсказательный смысл тут только в определения тренда, который и так понятен, если взглянуть на предыдущие цифры. А сами цифры никак не будут согласовываться с полученным прогнозом, т.е. 21 месяц может быть лучше 20-го, а 22-й хуже 21-го и наоборот. Т.е. предсказывать показатель конкретного месяца по этому прогнозу нельзя.

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

    • Константин Рачин

      Ilya,

      «смысл тут только в определения тренда, который и так понятен, если взглянуть на предыдущие цифры.»

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

      «Было бы интереснее посмотреть уровень продаж в зависимости от каких-то параметров рекламных кампаний, количества обращений и других параметров. Ставить гипотезы, смотреть зависимость и получать прогноз в выигрыше в продажах в зависимости от усиления того или иного параметра.»

      Да, можно искать зависимости от переменных, но тут лучше считать регрессию, через «Анализ данных» в Excel.