Рубрика развивается при поддержке
Сервисы
Ringostat
1957

Два способа быстро построить прогноз в «Google Таблицах»

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

В закладки

Мы в Ringostat — настоящие фанаты аналитики, статистики и электронных таблиц. Каждый отдел мониторит свою работу с помощью автоматизированных дашбордов, созданных в Google Spreadsheets.

Там же мы строим простые прогнозы для некоторых KPI, чтобы понимать, придерживаться ли тех же темпов и стратегии или нужно искать новые подходы, ведь с нынешними в ближайшем будущем нас ждёт спад. Способом построения такого простого прогноза в Google Spreadsheets мы и поделимся в этой статье.

Такие прогнозы пригодятся, чтобы:

  • показать промежуточные цифры клиентам и начальству;
  • следить за трендами;
  • заранее реагировать на проблемы.

Мы, например, используем подобные формулы, чтобы прогнозировать количество лидов на разных этапах воронки продаж. Но сегодня рассмотрим пример полегче: предскажем доходы вымышленного магазина, чтобы понять, пора ли ему что-то менять в работе или достаточно просто держать темп.

Оформляем данные

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

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

Данные придётся или вписывать в Google Spreadsheets вручную, или подтянуть из нужной системы (отчётов, бухгалтерии, CRM). Для примера: придумаем доход воображаемого магазина за восемь месяцев и рассчитаем, сколько он заработает до конца года, если будет продолжать работать как раньше.

Способ первый: делаем прогноз с формулой forecast («предсказ»)

Когда мы внесли данные, в свободных ячейках под ними добавляем несколько временных ячеек, а рядом пишем формулу forecast, или «предсказ» в русской локализации. Выглядит она так:

=FORECAST(X; Известные_значения_Y; Известные_значения_X)

  • Х — точка во времени, для которой мы делаем прогноз;
  • известные_значения_Y — известные нам значения зависимой переменной (на основе которых делаем прогноз);
  • известные_значения_X — известные нам значения независимой переменной (даты или номера периодов).

В примере с доходом магазина это будет выглядеть следующим образом:

=FORECAST(A10;B2:B9;A2:A9)

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

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

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

Способ второй: делаем прогноз с формулой trend («тенденция»)

Эта формула выдаёт те же числа, что и прошлая, хотя математически приходит к ним немного другим путем. С точки зрения пользователя, принципиален тот факт, что её не нужно «растягивать» на соседние ячейки, достаточно в одной формуле прописать диапазон результатов.

Благодаря этому таблица будет меньше тупить над большими массивами информации (если речь идёт о сотнях показателей), да и знаки доллара прописывать не придется.

Выглядит формула trend или «тенденция» следующим образом:

=TREND(исходные_данные_y; [известные_даные_x]; [новые_данные_x])

  • Х — точка во времени, для которой мы делаем прогноз;
  • исходные_данные_y — известные нам значения зависимой переменной (revenue);
  • известные_данные_x — известные нам значения независимой переменной (даты или номера периодов);
  • новые_данные_x — значения независимой переменной, для которых нужно сделать прогноз.

Возвращаясь к нашему примеру, формула будет выглядеть так:

=TREND(B2:B9;A2:A9;A10:A13)

Строим график

Теперь у нас есть исходные данные и прогноз на их основе, но всё ещё не хватает общей картины. Чтобы её получить построим график с имеющейся информацией. Для этого в меню выбираем «Вставка» → «Диаграмма» и строим линейный график. По оси X размещаем даты, а числовые показатели помечаем как данные оси Y. Получаем график изменений Revenue с прогнозом на сентябрь, октябрь, ноябрь и декабрь.

Чтобы увидеть тренд, намеченный таблицей, заходим в настройки диаграммы, «Дополнительные» → «Серии», проматываем этот раздел в самый низ и отмечаем чекбокс «Линия тренда». Наблюдаем, как на графике появляется линия, совпадающая с данными от сентября и позже.

Теперь понятно, что у нашего вымышленного магазина дела идут не слишком хорошо и пора что-то менять в работе. График не объясняет, почему тренд стал падать, но владелец магазина должен уже подумывать о решении проблемы с первых спадов в июне, а теперь и вовсе срочно решаться на изменения в работе.

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

Материал опубликован пользователем.
Нажмите кнопку «Написать», чтобы поделиться мнением или рассказать о своём проекте.

Написать
{ "author_name": "Ringostat", "author_type": "self", "tags": ["\u043f\u0440\u043e\u0433\u043d\u043e\u0437\u044b","\u0438\u043d\u0441\u0442\u0440\u0443\u043c\u0435\u043d\u0442\u044b"], "comments": 4, "likes": 19, "favorites": 223, "is_advertisement": false, "subsite_label": "services", "id": 75090, "is_wide": false, "is_ugc": true, "date": "Fri, 12 Jul 2019 15:12:58 +0300", "is_special": false }
Облачная платформа
Основа для цифровизации бизнеса
0
{ "id": 75090, "author_id": 157882, "diff_limit": 1000, "urls": {"diff":"\/comments\/75090\/get","add":"\/comments\/75090\/add","edit":"\/comments\/edit","remove":"\/admin\/comments\/remove","pin":"\/admin\/comments\/pin","get4edit":"\/comments\/get4edit","complain":"\/comments\/complain","load_more":"\/comments\/loading\/75090"}, "attach_limit": 2, "max_comment_text_length": 5000, "subsite_id": 200396, "last_count_and_date": null }
4 комментария
Популярные
По порядку
2

Спасибо! Вполне доступно и понятно, у меня вечно проблемы с таблицами и расчетом данных. Здесь, вроде бы понятно, хотя на практике могут и возникнуть ошибки.

Ответить
1

По какой методике рассчитываются данные? Были применены две разные формулы и они обе показали одинаковый результат. В чем их отличие и в каком случае применяется одна и должна применяться другая?

Ответить
1

не знаю как гугле, но в экселе "предсказ" работает со значениями, а "тенденция" с массивами.

Ответить
1

Отлично, спасибо за информацию!

Ответить
{ "page_type": "article" }

Прямой эфир

[ { "id": 1, "label": "100%×150_Branding_desktop", "provider": "adfox", "adaptive": [ "desktop" ], "adfox_method": "createAdaptive", "auto_reload": true, "adfox": { "ownerId": 228129, "params": { "pp": "g", "ps": "bugf", "p2": "ezfl" } } }, { "id": 2, "label": "1200х400", "provider": "adfox", "adaptive": [ "phone" ], "auto_reload": true, "adfox": { "ownerId": 228129, "params": { "pp": "g", "ps": "bugf", "p2": "ezfn" } } }, { "id": 3, "label": "240х200 _ТГБ_desktop", "provider": "adfox", "adaptive": [ "desktop" ], "adfox": { "ownerId": 228129, "params": { "pp": "g", "ps": "bugf", "p2": "fizc" } } }, { "id": 4, "label": "Article Branding", "provider": "adfox", "adaptive": [ "desktop" ], "adfox": { "ownerId": 228129, "params": { "p1": "cfovx", "p2": "glug" } } }, { "id": 5, "label": "300x500_desktop", "provider": "adfox", "adaptive": [ "desktop" ], "adfox": { "ownerId": 228129, "params": { "pp": "g", "ps": "bugf", "p2": "ezfk" } } }, { "id": 6, "label": "1180х250_Interpool_баннер над комментариями_Desktop", "provider": "adfox", "adaptive": [ "desktop" ], "adfox": { "ownerId": 228129, "params": { "pp": "h", "ps": "bugf", "p2": "ffyh" } } }, { "id": 7, "label": "Article Footer 100%_desktop_mobile", "provider": "adfox", "adaptive": [ "desktop", "tablet", "phone" ], "adfox": { "ownerId": 228129, "params": { "pp": "g", "ps": "bugf", "p2": "fjxb" } } }, { "id": 8, "label": "Fullscreen Desktop", "provider": "adfox", "adaptive": [ "desktop", "tablet" ], "auto_reload": true, "adfox": { "ownerId": 228129, "params": { "pp": "g", "ps": "bugf", "p2": "fjoh" } } }, { "id": 9, "label": "Fullscreen Mobile", "provider": "adfox", "adaptive": [ "phone" ], "auto_reload": true, "adfox": { "ownerId": 228129, "params": { "pp": "g", "ps": "bugf", "p2": "fjog" } } }, { "id": 10, "disable": true, "label": "Native Partner Desktop", "provider": "adfox", "adaptive": [ "desktop", "tablet" ], "adfox": { "ownerId": 228129, "params": { "pp": "g", "ps": "clmf", "p2": "fmyb" } } }, { "id": 11, "disable": true, "label": "Native Partner Mobile", "provider": "adfox", "adaptive": [ "phone" ], "adfox": { "ownerId": 228129, "params": { "pp": "g", "ps": "clmf", "p2": "fmyc" } } }, { "id": 12, "label": "Кнопка в шапке", "provider": "adfox", "adaptive": [ "desktop" ], "adfox": { "ownerId": 228129, "params": { "p1": "bscsh", "p2": "fdhx" } } }, { "id": 13, "label": "DM InPage Video PartnerCode", "provider": "adfox", "adaptive": [ "desktop", "tablet", "phone" ], "adfox_method": "createAdaptive", "adfox": { "ownerId": 228129, "params": { "pp": "h", "ps": "bugf", "p2": "flvn" } } }, { "id": 14, "label": "Yandex context video banner", "provider": "yandex", "yandex": { "block_id": "VI-223676-0", "render_to": "inpage_VI-223676-0-1104503429", "adfox_url": "//ads.adfox.ru/228129/getCode?pp=h&ps=bugf&p2=fpjw&puid1=&puid2=&puid3=&puid4=&puid8=&puid9=&puid10=&puid21=&puid22=&puid31=&puid32=&puid33=&fmt=1&dl={REFERER}&pr=" } }, { "id": 15, "label": "Баннер в ленте на главной", "provider": "adfox", "adaptive": [ "desktop", "tablet", "phone" ], "adfox": { "ownerId": 228129, "params": { "p1": "byudx", "p2": "ftjf" } } }, { "id": 16, "label": "Кнопка в шапке мобайл", "provider": "adfox", "adaptive": [ "tablet", "phone" ], "adfox": { "ownerId": 228129, "params": { "p1": "byzqf", "p2": "ftwx" } } }, { "id": 17, "label": "Stratum Desktop", "provider": "adfox", "adaptive": [ "desktop" ], "auto_reload": true, "adfox": { "ownerId": 228129, "params": { "pp": "g", "ps": "bugf", "p2": "fzvb" } } }, { "id": 18, "label": "Stratum Mobile", "provider": "adfox", "adaptive": [ "tablet", "phone" ], "auto_reload": true, "adfox": { "ownerId": 228129, "params": { "pp": "g", "ps": "bugf", "p2": "fzvc" } } }, { "id": 19, "disable": true, "label": "Тизер на главной", "provider": "adfox", "adaptive": [ "desktop", "tablet", "phone" ], "auto_reload": true, "adfox": { "ownerId": 228129, "params": { "p1": "cbltd", "p2": "gazs" } } }, { "id": 20, "label": "Кнопка в сайдбаре", "provider": "adfox", "adaptive": [ "desktop" ], "adfox": { "ownerId": 228129, "params": { "p1": "cgxmr", "p2": "gnwc" } } } ] { "page_type": "default" }