Прогнозирование в экселе: Создание прогноза в Excel для Windows

Содержание

Создание прогноза в Excel для Windows

Параметры прогноза

Описание

Начало прогноза

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

Советы: 

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

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

Доверительный интервал

Установите или снимите флажок Доверительный интервал, чтобы показать или скрыть его. Доверительный интервал — это диапазон вокруг каждого предсказанного значения, в который в соответствии с прогнозом (при нормальном распределении) предположительно должны попасть 95 % точек, относящихся к будущему.

Доверительный интервал помогает определить точность прогноза. Чем он меньше, тем выше достоверность прогноза для данной точки. Доверительный интервал по умолчанию определяется для 95 % точек, но это значение можно изменить с помощью стрелок вверх или вниз.

Сезонность

Сезонность — это число для длины (количества точек) сезонного шаблона и оно определяется автоматически. Например, в ежегодном цикле продаж, где каждая точка представляет месяц, сезонность составляет 12. Автоматическое обнаружение можно переопрепредить, выбрав «Установить вручную» и выбрав число.

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

Диапазон временной шкалы

Здесь можно изменить диапазон, используемый для временной шкалы. Этот диапазон должен соответствовать параметру Диапазон значений.

Диапазон значений

Здесь можно изменить диапазон, используемый для рядов значений. Этот диапазон должен совпадать со значением параметра

Диапазон временной шкалы.

Заполнить отсутствующие точки с помощью

Для обработки отсутствующих точек Excel использует интерполяцию, то есть отсутствующие точки завершаются как взвешнее среднее соседних точек, если отсутствует менее 30 % точек. Чтобы вместо отсутствующих точек рассматривать отсутствующие нули, выберите в списке нуль.

Использование агрегатных дубликатов

Если данные содержат несколько значений с одной меткой времени, Excel находит их среднее. Чтобы использовать другой метод вычисления, например «Медиана» или «Количество», выберите нужное вычисление из списка.

Включить статистические данные прогноза

Установите этот флажок, если хотите поместить на новом листе дополнительную статистическую информацию о прогнозе. При этом добавляется таблица статистики, созданная с помощью прогноза. ETS. Функция СТАТ и показатели, такие как коэффициенты сглаживания (альфа, бета, гамма) и метрики ошибок (MASE, SMAPE, MAE, RMSE).

Быстрый прогноз функцией ПРЕДСКАЗ (FORECAST)

Умение строить прогнозы, предсказывая (хотя бы примерно!) будущее развитие событий — неотъемлемая и очень важная часть любого современного бизнеса. Само-собой, это отдельная весьма сложная наука с кучей методов и подходов, но часто для грубой повседневной оценки ситуации достаточно простых техник. Одна из них — это функция ПРЕДСКАЗ (FORECAST), которая умеет считать прогноз по линейному тренду.

Принцип работы этой функции несложен: мы предполагаем, что исходные данные можно интерполировать (сгладить) некой прямой с классическим линейным уравнением y=kx+b:


Построив эту прямую и продлив ее вправо за пределы известного временного диапазона — получим искомый прогноз. 

Для построения этой прямой Excel использует известный метод наименьших квадратов

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

Excel позволяет легко построить линию тренда прямо на диаграмме щелчком правой по ряду — Добавить линию тренда (Add Trendline), но часто для расчетов нам нужна не линия, а числовые значения прогноза, которые ей соответствуют. Вот, как раз, их и вычисляет функция ПРЕДСКАЗ (FORECAST).

Синтаксис функции следующий

=ПРЕДСКАЗ(X; Известные_значения_Y; Известные_значения_X)

где

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

 

Ссылки по теме

 

Прогнозирование в Excel показателей бизнеса в будущем

 

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

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

Рассмотрим работу функции «ПРЕДСКАЗ» на живом примере из жизненной практики.

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


По данной таблице создан график продаж.

Необходимо спрогнозировать объемы продаж на будущие 2 года.

Для этого можно воспользоваться функцией «ПРЕДСКАЗ»:
• Устанавливаем курсор в нужную ячейку;
• Запускаем «Мастер функций»;
• В мастере функций следует выбрать функцию «ПРЕДСКАЗ» и нажать «ОК»;

В появившемся окне следует заполнить поля:

• Поле «X» — указывается значение Х в искомой ячейке;
• Поле «Известные_значения_Y» — вносятся уже известные статистические значения «Y»;
• Поле «Известные_значения_X» — вносятся уже известные статистические значения «X»;


После нажатия кнопки «ОК» функция посчитает прогнозные значения на основании статистики.
Естественно, что такие расчеты очень грубые и не учитывают все факторы влияющие на продажи, поэтому можно корректировать расчет функции «ПРЕДСКАЗ» поправочными коэффициентами. Например, можно применить коэффициент снижения продаж из-за кризиса в стране 0.9-0.7 или иные условия.

 

Используйте функцию прогноза Excels, чтобы превратить исторические данные в прогнозы

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

, Крошечный взгляд в будущее, если хотите.

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

Если вы действительно хотите знать, как использовать функции, вы можете ознакомиться с руководством Microsoft по функциям прогнозирования Excel. Но мы рекомендуем придерживаться кнопки «Лист прогноза».

Как Excel прогнозирует данные?

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

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

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

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

Форматирование ваших данных для прогнозирования

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

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

,

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

Второй столбец — ваши данные. Это то, что вы собираетесь прогнозировать в будущем.

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

Простое прогнозирование

Чтобы приступить к прогнозу Excel, выделите данные и перейдите к Данные> Прогнозный лист.

После того, как вы нажмете кнопку «Лист прогноза», вы увидите красиво отформатированный график, который содержит ваши данные синим цветом, а прогнозные данные — оранжевым.

Удар Создайте в новом окне, и вы получите более постоянную версию этого графика вместе с таблицей прогнозируемых значений. Эта таблица также включает 95% доверительные интервалы (которые также представлены на графике).

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

Настройка прогноза Excel

в Создать прогнозный лист окно, по умолчанию вы видите только одно выпадающее меню: Конец прогноза.

Дата окончания прогноза позволяет вам выбрать, как далеко в будущем прогнозировать. Имейте в виду, что чем дальше, тем больше ошибок вы увидите.

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

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

Мы рассмотрим каждый вариант в отдельности.

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

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

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

Это означает, что Excel на 95 процентов уверен, что прогнозируемое значение будет находиться между этими двумя строками.

Сезонность определяет повторяющийся характер вашей временной шкалы. В большинстве случаев Excel будет рассчитывать это автоматически. В других случаях вы захотите изменить это самостоятельно.

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

И вот как это выглядит после того, как я изменил сезонность на 4:

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

Просто убедитесь, что ваш сезонный номер является точным. Если вы используете месячные данные, сезонность равна 12. Еженедельные данные в течение года будут 52. Если еженедельно в течение полугода, используйте 26.

Если вы проверите Включить статистику прогноза, Excel даст вам дополнительную базовую статистику в вашем окончательном листе. Вот статистика, которую вы получите:

Если вы не знаете, что означают эти статистические данные, не беспокойтесь о них.

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

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

Стандартный метод заполнения недостающих точек — это интерполяция, в которой используются средневзвешенные значения соседних значений отсутствующей точки. Это хороший вариант, особенно если учесть, что он ваш единственный.

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

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

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

Прогнозирование будущего с помощью функции прогноза в Excel

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

Прогнозы в Excel не идеальны, но это отличный инструмент для использования в вашем арсенале Excel

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

Используете ли вы прогноз Excel? Какие еще у вас есть советы для новичков в этой функции? Поделитесь своими мыслями в комментариях ниже!

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

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

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

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

 

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

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

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

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

Ringostat тоже содержит отчеты, полезные для бизнеса. Но не только:

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

 

Шаг 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 Таблицах: лайфхаки по визуализации данных».

 

Резюме

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

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


Подпишитесь на обновления

Раз в неделю мы отправляем дайджест самых интересных новостей о digital 

Email*

Подписаться

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

Прогноз в Excel (формула, примеры)

Функция прогноза в Excel (Содержание)

  • Прогноз в Excel
  • Формула прогноза в Excel
  • Как использовать функцию прогноза в Excel?

Прогноз в Excel

Функция ПРОГНОЗ встроена в функцию Excel, которая входит в Статистическую функцию, которая используется для возврата прогноза будущего значения на основе существующих заданных значений. В финансовом учете эта функция ПРОГНОЗ будет полезна для расчета прогнозных отчетов. Например, если нам известны значения продаж за последние два месяца, мы можем легко предсказать продажи в следующем месяце, используя эту функцию FORECAST. Синтаксис для функции FORECAST приведен ниже.

ПРОГНОЗ Формула в Excel

Ниже приведена ПРОГНОЗНАЯ формула в Excel:

Аргументы функции ПРОГНОЗ в Excel:

  • X-: это числовое значение, где нам нужно прогнозировать новое значение y
  • Known_ Y-: это Known y_values ​​является зависимым массивом или диапазоном данных.
  • Known_ X-: это известные x_values ​​- это независимый массив или диапазон данных.

Особенности прогнозирования:

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

Прогнозирование:

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

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

  • Перейдите в меню формул и нажмите «Вставить функцию». Появится диалоговое окно. Выберите категорию статистически. Как только вы выберете статистику, вы найдете список функций. Выберите функцию прогноза

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

Перейти к формулам–> Выбрать дополнительную функцию–> Статистика–> Прогноз

Как использовать функцию ПРОГНОЗ в Excel?

ПРОГНОЗ Функция очень проста в использовании. Давайте теперь посмотрим, как использовать функцию FORECAST в Excel с помощью нескольких примеров.

Вы можете скачать этот шаблон функции прогноза Excel здесь — Шаблон функции прогноза Excel

ПРОГНОЗ в Excel — Пример № 1

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

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

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

За 2008 год название продукта «Grommer» заработало 6000000 продаж. Для того, чтобы предсказать следующий год, т. Е. Объем продаж в 2009 году, мы можем использовать ПРОГНОЗ в Excel, что показано с помощью приведенных ниже данных о продажах.

Таким образом, мы должны использовать ПРОГНОЗ в Excel, чтобы получить значение продаж в следующем году, где

D12 — х-числовое значение.

C3: C11 –Know_Y Значение.

D3: D11 — Известное_X значение.

Результат:

Доход от продаж для продукта Groomer за 2009 год составляет 6222222. Таким образом, мы можем легко предсказать доход от продаж для данного продукта.

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

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

  • Выберите ячейку из C3: D12.

  • Выберите Вставить вкладку, затем выберите опцию Линейный график. В линейном графике выберите первый вариант.

  • Диаграмма была отображена, и прогнозируемое значение для 2009 года было показано в формате графика ниже.

Результат:

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

ПРОГНОЗ в Excel — Пример № 2

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

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

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

  • Вставить функцию прогноза

  • Выберите значение x как B14

  • Выберите известные у из C2: C13

  • Выберите известные х из B2: B13

= ПРОГНОЗ (В14, С2: С13, В2: В13)

Результатом является:

Точно так же мы находим другие значения

Теперь мы можем видеть, что прогноз в Excel предсказал некоторые значения продаж на 2019 год, что показывает увеличение продаж за соответствующий месяц.

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

Чтобы вставить график, выполните следующие действия.

  • Выберите месяц и столбец данных о продажах

  • Перейти, чтобы вставить меню. Выберите тип графика

  • Выберите формат графика графика 3D

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

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

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

Преимущества использования ПРОГНОЗА в Excel

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

Рекомендуемые статьи

Это было руководство к функции прогнозирования Excel. Здесь мы обсуждаем формулу прогноза и как использовать функцию прогноза вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи —

  1. Функция Excel COMBIN
  2. Функция INT Excel
  3. ПОДСКАЗКА в Excel
  4. Функция LN Excel

РЕАЛИЗАЦИЯ В EXCEL И ПРОГРАММЕ R

8

Использование простых скользящих средних в ряде случаев показывает

ложные дна в кривой сглаженного ряда (спад может быть заменен подъемом

или наоборот).

Помимо простой скользящей средней, также рассчитывают взвешенные

скользящие средние (более совершенный метод). Считается, что все уровни

временного ряда равноценны и при исчислении каждому уровню

присваивается вес, который зависит от расстояния данного уровня до

середины активного участка сглаживания (Елисеева, 2003).

Таблица 1.1

Расчет скользящих средних на основе динамического ряда объема прямых

иностранных инвестиций в экономику Европейского Союза

Годы

Объем

прямых

иностранных

инвестиций в

ЕС (млн. $), yt

3-членная

простая

скользящая

средняя

5-членная

простая

скользящая

средняя

7-членная

простая

скользящая

средняя

3-членная

взвешенная

скользящая

средняя

5-членная

взвешенная

скользящая

средняя

7-членная

взвешенная

скользящая

средняя

1990 90402,4 − − − − − −

1991 71848,4 77022,4 − − 75728,9 − −

1992 68816,3 71625,7 76228,4 − 70923,4 72712,9 −

1993 74212,5 72963,7 82278,9 88000,7 73275,9 76030,8 78344,7

1994 75862,4 90243,3 90750,9 94487,2 86648,1 88604,3 89782,2

1995 120655,0 103575,2 104149,1 121356,1 107845,1 105889,5 107078,5

1996 114208,2 123556,9 141292,8 166743,4 121219,7 127930,8 134478,2

1997 135807,5 169982,3 203425,8 257445,8 161438,6 176161,5 189942,6

1998 259931,1 260755,3 321120,6 294344,4 260549,3 279516,4 287944,9

1999 386527,4 451862,4 365109,6 331785,8 435528,6 416585,3 399736,9

2000 709128,7 476603,0 414497,0 362481,8 534734,5 486260,6 458026,9

2001 334153,1 475342,2 428326,9 399648,6 440044,9 443001,2 437667,4

2002 382744,8 348659,4 430216,4 497726,5 357180,7 378406,6 399484,7

2003 329080,4 369266,7 477685,9 599025,0 359220,2 398124,4 433608,2

2004 395975,1 557177,2 629978,6 735393,9 516876,7 559777,4 592513,8

2005 946476,1 812689,3 886172,0 841530,7 846136,0 852376,0 855000,1

2006 1095616,7 1235268,1 1035778,0 850474,0 1200355,2 1155471,0 1104345,2

2007 1663711,5 1278812,9 1045652,5 885963,9 1375037,6 1254062,6 1174836,9

2008 1077110,5 1062056,5 971859,2 950982,3 1065820,0 1035751,6 1015850,5

2009 445347,4 699989,2 922956,6 920518,1 636328,8 737836,3 791312,3

2010 577509,7 624653,7 736859,8 850557,5 612867,7 653825,1 689509,3

2011 851104,1 720613,6 642616,1 668812,5 753236,2 712550,7 694674,6

2012 733227,1 730074,4 631845,9 594244,7 730862,6 699772,1 677093,8

2013 605892,0 576872,0 627371,1 − 584127,0 596280,4 −

2014 391496,8 517508,1 − − 486005,3 − −

2015 555135,5 − − − − − −

Создайте прогноз в Excel для Windows

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

Информацию о том, как рассчитывается прогноз, и о вариантах, которые вы можете изменить, можно найти внизу этой статьи.

Создать прогноз

  1. На листе введите два ряда данных, которые соответствуют друг другу:

    • Серия с записями даты или времени для временной шкалы

    • Серия с соответствующими значениями

      Эти значения будут предсказаны для будущих дат.

    Примечание: Временная шкала требует согласованных интервалов между точками данных. Например, месячные интервалы со значениями на 1-е число каждого месяца, годовые интервалы или числовые интервалы. Ничего страшного, если в вашей временной шкале отсутствует до 30% точек данных или есть несколько чисел с одной и той же меткой времени. Прогноз все равно будет точным. Однако суммирование данных перед созданием прогноза даст более точные результаты прогноза.

  2. Выберите обе серии данных.

    Совет: Если вы выберете ячейку в одной из своих серий, Excel автоматически выберет остальные данные.

  3. На вкладке Данные в группе Прогноз щелкните Лист прогноза .

  4. В поле Create Forecast Worksheet выберите линейную или столбчатую диаграмму для визуального представления прогноза.

  5. В поле Конец прогноза выберите дату окончания и нажмите Создать .

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

    Вы найдете новый рабочий лист слева («перед») от листа, на котором вы ввели ряд данных.

Настройте свой прогноз

Если вы хотите изменить какие-либо дополнительные параметры прогноза, щелкните Параметры .

Вы найдете информацию о каждом из вариантов в следующей таблице.

Варианты прогноза

Описание

Начало прогноза

Выберите дату начала прогноза. Когда вы выбираете дату до окончания исторических данных, в прогнозе используются только данные, предшествующие дате начала (это иногда называют «ретроспективным прогнозом»).

Советы:

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

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

Доверительный интервал

Установите или снимите флажок Доверительный интервал , чтобы показать или скрыть его.Доверительный интервал — это диапазон, окружающий каждое прогнозируемое значение, в который ожидается попадание 95% будущих точек на основе прогноза (с нормальным распределением). Доверительный интервал может помочь вам выяснить точность прогноза. Меньший интервал означает большую уверенность в прогнозе для конкретной точки. Уровень достоверности 95% по умолчанию можно изменить с помощью стрелок вверх или вниз.

Сезонность

Сезонность — это число, обозначающее продолжительность (количество точек) сезонного шаблона, которое определяется автоматически. Например, в годовом цикле продаж, где каждая точка представляет месяц, сезонность равна 12. Вы можете отменить автоматическое определение, выбрав Установить вручную , а затем выбрав число.

Примечание: При установке сезонности вручную избегайте значения менее 2 циклов исторических данных. При менее чем двух циклах Excel не может определить сезонные составляющие. А когда сезонность недостаточно значительна, чтобы алгоритм мог ее обнаружить, прогноз вернется к линейному тренду.

Диапазон временной шкалы

Измените здесь диапазон, используемый для вашей временной шкалы. Этот диапазон должен соответствовать диапазону значений .

Диапазон значений

Измените здесь диапазон, используемый для вашего ряда значений.Этот диапазон должен быть идентичен диапазону Timeline Range .

Заполните недостающие точки с помощью

Для обработки отсутствующих точек Excel использует интерполяцию, что означает, что отсутствующая точка будет восполнена как средневзвешенное значение соседних точек, если отсутствует менее 30% точек.Чтобы вместо этого рассматривать отсутствующие точки как нули, щелкните Нули в списке.

Совокупные дубликаты с использованием

Если ваши данные содержат несколько значений с одной и той же меткой времени, Excel будет усреднять значения. Чтобы использовать другой метод расчета, например Median или Count , выберите нужный расчет из списка.

Включить статистику прогноза

Установите этот флажок, если вы хотите, чтобы дополнительная статистическая информация по прогнозу была включена в новый рабочий лист. При этом добавляется таблица статистики, созданная с помощью функции FORECAST.ETS.STAT, и включающая такие меры, как коэффициенты сглаживания (альфа, бета, гамма) и метрики ошибок (MASE, SMAPE, MAE, RMSE).

Формулы, используемые в данных прогнозирования

Когда вы используете формулу для создания прогноза, она возвращает таблицу с историческими и прогнозируемыми данными, а также диаграмму. Прогноз предсказывает будущие значения с использованием существующих временных данных и версии AAA алгоритма экспоненциального сглаживания (ETS).

Таблица может содержать следующие столбцы, три из которых являются расчетными столбцами:

  • Исторический столбец времени (ваш временной ряд данных)

  • Столбец исторических значений (соответствующие серии данных значений)

  • Столбец прогнозируемых значений (рассчитано с использованием FORECAST.ETS)

  • Два столбца, представляющие доверительный интервал (рассчитанный с использованием FORECAST. ETS.CONFINT). Эти столбцы появляются только в том случае, если в поле « Параметры » установлен флажок «Доверительный интервал ».

Скачать образец рабочей тетради

Щелкните эту ссылку, чтобы загрузить книгу с примерами функций Excel FORECAST.ETS

Нужна дополнительная помощь?

Вы всегда можете спросить эксперта в техническом сообществе Excel, получить поддержку в сообществе Answers или предложить новую функцию или улучшение в Excel User Voice.

Связанные темы

Функции прогнозирования

Функция ПРОГНОЗ

в Excel — Easy Excel Tutorial

FORECAST.LINEAR | FORECAST.ETS | Лист прогноза

Функция FORECAST (или FORECAST.LINEAR) в Excel предсказывает будущее значение по линейному тренду. Функция FORECAST.ETS в Excel предсказывает будущее значение с использованием экспоненциального тройного сглаживания, которое учитывает сезонность.

Примечание: функция ПРОГНОЗ — старая функция. Microsoft Excel рекомендует использовать новую функцию ПРОГНОЗ.ЛИНЕЙНЫЙ, которая дает точно такой же результат.

ПРОГНОЗ.ЛИНЕЙНЫЙ

1. Функция FORECAST.LINEAR ниже предсказывает будущее значение в соответствии с линейным трендом.

Объяснение: когда мы перетаскиваем функцию FORECAST.LINEAR вниз, абсолютные ссылки ($ B $ 2: $ B $ 11 и $ A $ 2: $ A $ 11) остаются неизменными, в то время как относительная ссылка (A12) изменяется на A13 и A14.

2. Введите значение 89 в ячейку C11, выберите диапазон A1: C14 и вставьте диаграмму рассеяния с прямыми линиями и маркерами.

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

FORECAST.ETS

Функция FORECAST.ETS в Excel 2016 или более поздней версии — отличная функция, которая может обнаруживать сезонные закономерности.

1. Функция FORECAST.ETS ниже предсказывает будущее значение с использованием экспоненциального тройного сглаживания.

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

2. Введите значение 49 в ячейку C13, выберите диапазон A1: C17 и вставьте диаграмму рассеяния с прямыми линиями и маркерами.

3. Вы можете использовать функцию FORECAST.ETS.SEASONALITY, чтобы найти длину сезонного шаблона. Увидев диаграмму, вы, вероятно, уже знаете ответ.

Заключение: в этом примере при использовании функции FORECAST.ETS также можно использовать значение 4 в качестве четвертого аргумента.

Лист прогнозов

Используйте инструмент Таблица прогнозов в Excel 2016 или более поздней версии для автоматического создания таблицы визуальных прогнозов.

1. Выберите диапазон A1: B13, показанный выше.

2. На вкладке «Данные» в группе «Прогноз» щелкните «Лист прогноза».

Excel открывает диалоговое окно, показанное ниже.

3. Укажите, когда закончится прогноз, установите доверительный интервал (95% по умолчанию), определите сезонность автоматически или вручную установите продолжительность сезонной модели и т. Д.

4. Щелкните «Создать».

Этот инструмент использует функцию FORECAST.ETS и вычисляет такие же будущие значения. Нижняя и верхняя границы достоверности — приятный бонус.

Пояснение: в период 13 мы можем быть уверены на 95%, что количество посетителей будет от 86 до 94.

моделей прогнозирования с линейным и экспоненциальным сглаживанием

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

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

Прогноз в Excel

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

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

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

Как прогнозировать в Excel с помощью экспоненциального сглаживания

Прогнозирование

с экспоненциальным сглаживанием в Excel основано на версии AAA (аддитивная ошибка, аддитивная тенденция и аддитивная сезонность) алгоритма Exponential Triple Smoothing (ETS), который сглаживает незначительные отклонения в прошлых тенденциях данных путем обнаружения сезонных моделей и доверительных интервалов. .

Этот метод прогнозирования лучше всего подходит для нелинейных моделей данных с сезонными или другими повторяющимися моделями . Он доступен в Excel 2016, Excel 2019 и Excel для Office 365.

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

Автоматическое создание листа экспоненциального прогноза

Таблица прогнозов , представленная в Excel 2016, упрощает прогнозирование временных рядов.По сути, вам нужно только правильно организовать исходные данные, а все остальное сделает Excel.

Упорядочивание данных

На листе Excel введите две серии данных в соседние столбцы:

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

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

Теоретически ваши временные ряды могут пропускать до 30% точек данных или иметь несколько записей с одной и той же датой или временем, и Excel по-прежнему будет делать прогноз правильно. Однако рекомендуется суммировать ваши данные, чтобы получить более точные прогнозы.

В этом примере мы попытаемся спрогнозировать продажи на следующие несколько лет на основе следующих исторических данных.Обратите внимание, что столбец A содержит даты (1-е число каждого месяца) в настраиваемом формате, в котором отображаются только месяц и год. Однако это полнофункциональные даты, а не текстовые значения.

Создание листа прогноза

Имея два ряда данных, выполните следующие шаги для построения модели прогнозирования:

  1. Выберите обе серии данных. В большинстве случаев достаточно выбрать только одну ячейку в любом из ваших рядов, и Excel автоматически подберет остальные данные.
  2. Перейдите на вкладку Data > Forecast и нажмите кнопку Forecast Sheet .
  3. Окно Create Forecast Worksheet показывает предварительный просмотр прогноза и предлагает вам выбрать:
    • Тип графика: линейная (по умолчанию) или столбчатая диаграмма
    • Дата окончания прогноза
  4. Когда закончите, нажмите кнопку Create .

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

Вот как выглядит наш прогноз продаж в Excel:

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

Настройка прогноза Excel

Если вы хотите изменить какие-либо параметры прогноза по умолчанию, нажмите Параметры в нижней левой части окна Создать рабочий лист прогноза и настройте следующие параметры так, как вам нужно:

Начало прогноза — дата начала прогнозирования.Вы можете выбрать дату в средстве выбора даты или ввести ее прямо в поле.

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

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

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

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

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

Excel определяет сезонный цикл автоматически, но также позволяет вам установить его вручную. Когда Excel не может определить сезонность (обычно менее 2 циклов исторических данных), прогнозы возвращаются к линейному тренду.

Включить статистику прогноза — дополнительная статистическая информация о прогнозе.Установите этот флажок, если вы хотите, чтобы Excel создавал таблицу с дополнительной статистикой, такой как константы сглаживания (альфа, бета, гамма) и показатели ошибок (MASE, SMAPE, MAE, RMSE). Все эти значения рассчитываются с помощью функции FORECASE.ETS.STAT.

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

Диапазон значений — диапазон, используемый для вашего ряда значений. Он должен соответствовать диапазону Timeline Range .

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

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

Формулы прогноза экспоненциального сглаживания

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

Естественно, ничто не мешает вам самостоятельно построить аналогичную модель прогнозирования, используя следующие формулы.

Прогнозируемые значения (функция FORECAST.ETS)

Будущие значения рассчитываются с помощью ПРОГНОЗА.Функция ETS, имеющая следующий синтаксис:

FORECAST.ETS (целевая_дата, значения, временная шкала, [сезонность], [завершение_данных], [агрегирование])

Подробное объяснение каждого аргумента см. В разделе Использование функции ПРОГНОЗ.ETS в Excel.

Для нашего образца таблицы прогнозов Excel создал следующую формулу:

= ПРОГНОЗ.ETS (A31, $ B $ 2: $ B $ 30, $ A $ 2: $ A $ 30, 1, 1)

Где:

  • A31 — плановая дата
  • $ B $ 2: $ B $ 30 — диапазон значений данных
  • $ A $ 2: $ A $ 30 — это диапазон временного ряда
  • 1 в аргументе 4 th (сезонность) — указывает Excel на автоматическое определение сезонности.
  • 1 в аргументе 5 th (завершение данных) — указывает Excel заполнить недостающие точки как среднее значение соседних точек.
  • Аргумент 6 (агрегирование) опущен, что означает, что несколько значений с одной и той же меткой времени должны агрегироваться с использованием СРЕДНЕГО.

Доверительный интервал (функция FORECAST.ETS.CONFINT)

Чтобы вернуть доверительный интервал для значения прогноза на указанную дату, FORECAST.Используется функция ETS.CONFINT.

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

= FORECAST.ETS.CONFINT (A31, $ B $ 2: $ B $ 30, $ A $ 2: $ A $ 30, 0,95, 1, 1)

Где:

  • A31 — плановая дата
  • $ B $ 2: $ B $ 30 — диапазон значений данных
  • $ A $ 2: $ A $ 30 — это диапазон временного ряда
  • 0,95 — доверительная вероятность 95%.
  • 1 в аргументе 5 th (сезонность) — автоматическое определение сезонности.
  • 1 в аргументе 6 (завершение данных) — недостающие точки заполняются на основе среднего значения соседних точек.
  • 7 -й аргумент пропущен (агрегирование) — агрегирование нескольких значений данных с одной и той же меткой времени с помощью функции СРЕДНЕЕ.

В автоматически созданном листе прогнозов Excel не выводит значение доверительного интервала. Вместо этого он использует функцию FORECAST.ETS.CONFINT в сочетании со значением прогноза для расчета границ уверенности, при условии, что в разделе опций установлен флажок Доверительный интервал .

Чтобы получить нижнюю границу , вы вычтите доверительный интервал из прогнозируемого значения:

= C31 - FORECAST.ETS.CONFINT (A31, $ B $ 2: $ B $ 30, $ A $ 2: $ A $ 30, 0,95, 1, 1)

Чтобы получить верхнюю границу , вы добавляете доверительный интервал к прогнозируемому значению:

= C31 + FORECAST.ETS.CONFINT (A31, $ B $ 2: $ B $ 30, $ A $ 2: $ A $ 30, 0,95, 1, 1)

Где C31 — это прогнозируемое значение, возвращаемое FORECAST.ETS.

Как прогнозировать с помощью линейной регрессии в Excel

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

Microsoft Excel не предоставляет встроенной функции для автоматического линейного прогнозирования, но у него есть специальная функция для этого, точнее, две функции: FORECAST и FORECAST.LINEAR.

Обе функции имеют одинаковое назначение, синтаксис и возвращают одинаковые результаты. Разница только в версиях Excel:

  • В Excel 2016 и Excel 2019 доступны обе функции, но рекомендуется использовать более новую версию ПРОГНОЗ.ЛИНЕЙНЫЙ.
  • В Excel 2013, 2010 и 2007 доступна только функция ПРОГНОЗ.

Подробное объяснение синтаксиса функций можно найти в этом руководстве: Как использовать функцию ПРОГНОЗ в Excel. А пока остановимся на примере линейного прогноза.

Формулы линейного прогноза

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

В Excel 2016 и 2019:

= ПРОГНОЗ.ЛИНЕЙНЫЙ (A14, $ B $ 2: $ B $ 13, $ A $ 2: $ A $ 13)

В Excel 2013 и более ранних версиях:

= ПРОГНОЗ (A14, $ B $ 2: $ B $ 13, $ A $ 2: $ A $ 13)

Где:

  • A14 — это плановая дата
  • $ B $ 2: $ B $ 13 — диапазон значений данных
  • $ A $ 2: $ A $ 13 — это диапазон временного ряда

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

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

График прогнозирования линейной регрессии

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

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

  1. Скопируйте последнее значение исторических данных в прогноз В этом примере мы копируем значение из B13 в C13.Это поможет нам добиться эффекта сплошной непрерывной линии.
  2. Выберите 3 столбца данных: временные ряды, значения исторических данных и прогнозные значения.
  3. На вкладке Insert в группе Charts щелкните значок Insert Line или Area Chart и выберите первый тип диаграммы (2-D Line).

Готово!

Вот как делать прогнозы в Excel. Чтобы поближе познакомиться с примерами, обсуждаемыми в этом руководстве, вы можете загрузить нашу книгу примеров прогнозов Excel.

Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Вас также может заинтересовать

Excel ПРОГНОЗ и связанные функции с примерами формул

В руководстве объясняется, как использовать Excel FORECAST и другие связанные функции с примерами формул.

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

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

Функции прогнозирования Excel

В последних версиях Excel существует шесть различных функций прогнозирования.

Две функции делают линейных прогнозов:

  • FORECAST — прогнозирует будущие значения с помощью линейной регрессии; устаревшая функция для обратной совместимости с Excel 2013 и более ранними версиями.
  • ЛИНЕЙНЫЙ — идентично функции ПРОГНОЗ; часть нового набора функций прогнозирования в Excel 2016 и Excel 2019.

Четыре функции ETS предназначены для экспоненциального сглаживания прогнозов. Эти функции доступны только в Excel для Office 365, Excel 2019 и Excel 2016.

  • ETS — прогнозирует будущие значения на основе алгоритма экспоненциального сглаживания.
  • ETS.CONFINT — вычисляет доверительный интервал.
  • ETS.СЕЗОННОСТЬ — вычисляет продолжительность сезонного или другого повторяющегося шаблона.
  • ETS.STAT — возвращает статистические значения для прогнозирования временных рядов.

Excel ПРОГНОЗ (функция

)

Функция ПРОГНОЗ в Excel используется для прогнозирования будущего значения с помощью линейной регрессии . Другими словами, ПРОГНОЗ прогнозирует будущую стоимость по линии наилучшего соответствия на основе исторических данных.

Синтаксис функции ПРОГНОЗ следующий:

ПРОГНОЗ (x, известные_y, известные_x)

Где:

  • X (обязательно) — числовое значение x, для которого вы хотите спрогнозировать новое значение y.
  • Known_y’s (обязательно) — массив известных зависимых значений y.
  • Known_x’s (обязательно) — массив известных независимых x-значений.

Функция ПРОГНОЗ работает во всех версиях Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP и Excel 2000.

Примечание. В Excel 2016 и 2019 эта функция была заменена на FORECAST.LINEAR, но по-прежнему доступна для обратной совместимости.

Excel ПРОГНОЗ.ЛИНЕЙНАЯ функция

Функция FORECAST.LINEAR является современным аналогом функции FORECAST. Он имеет то же назначение и синтаксис:

FORECAST.LINEAR (x, известные_y, известные_x)

Эта функция доступна в Excel для Office 365, Excel 2019 и Excel 2016.

Как FORECAST и FORECAST.LINEAR рассчитывают будущие значения

Обе функции вычисляют будущее значение y с помощью уравнения линейной регрессии:

y = a + bx

Где константа a (точка пересечения):

А коэффициент b (наклон прямой) равен:

Значения x ȳ и ȳ являются выборочными средними (средними) известными значениями x и y.

Excel ПРОГНОЗ Функция не работает:

Если формула ПРОГНОЗ возвращает ошибку, это, скорее всего, по следующим причинам:

  1. Если диапазоны known_x и known_y имеют разную длину или пусты, # N / A! возникает ошибка.
  2. Если значение x нечисловое, формула возвращает #VALUE! ошибка.
  3. Если дисперсия известных_x равна нулю, # DIV / 0! возникает ошибка.

Как использовать функцию ПРОГНОЗ в Excel — пример формулы

Как уже было сказано, Excel ПРОГНОЗ и ПРОГНОЗ.ЛИНЕЙНЫЕ функции предназначены для линейного прогнозирования тренда. Они лучше всего подходят для линейных наборов данных и в ситуациях, когда вы хотите спрогнозировать общую тенденцию, игнорируя незначительные колебания данных.

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

С известными значениями y (количество посетителей) в B2: B22 и известными значениями x (датами) в A2: A22 формула прогноза выглядит следующим образом.

Excel 2019 — Excel 2000 :

= ПРОГНОЗ (A23, $ B $ 2: $ B $ 22, $ A $ 2: $ A $ 22)

Excel 2016 и Excel 2019 :

= ПРОГНОЗ.ЛИНЕЙНЫЙ (A23; $ B $ 2: $ B $ 22, $ A $ 2: $ A $ 22)

Где A23 — новое значение x, для которого вы хотите спрогнозировать будущее значение y.

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

Обратите внимание, что мы блокируем диапазоны с помощью абсолютных ссылок на ячейки (например, $ A $ 2: $ A $ 2), чтобы предотвратить их изменение при копировании формулы в другие ячейки.

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

Подробные шаги по построению такого графика описаны в диаграмме прогнозирования линейной регрессии.

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

Функция Excel FORECAST.ETS

Функция FORECAST.ETS используется для выполнения экспоненциального сглаживания прогнозов на основе ряда существующих значений.

Точнее, он предсказывает будущее значение на основе версии AAA алгоритма Exponential Triple Smoothing (ETS), отсюда и название функции.Этот алгоритм сглаживает незначительные отклонения в тенденциях данных, обнаруживая закономерности сезонности и доверительные интервалы. «AAA» означает аддитивную ошибку, аддитивный тренд и аддитивную сезонность.

Функция FORECAST.ETS доступна в Excel для Office 365, Excel 2019 и Excel 2016.

Синтаксис Excel FORECAST.ETS следующий:

FORECAST.ETS (целевая_дата, значения, временная шкала, [сезонность], [завершение_данных], [агрегирование])

Где:

  • Target_date (обязательно) — точка данных, для которой нужно прогнозировать значение.Он может быть представлен датой / временем или числом.
  • Значения (обязательно) — диапазон или массив исторических данных, для которых вы хотите спрогнозировать будущие значения.
  • Timeline (обязательно) — массив дат / времени или независимых числовых данных с постоянным шагом между ними.
  • Сезонность (необязательно) — число, обозначающее продолжительность сезонной закономерности:
    • 1 или опущено (по умолчанию) — Excel автоматически определяет сезонность по положительным целым числам.
    • 0 — отсутствие сезонности, т.е. линейный прогноз.

    Максимально допустимая сезонность — 8 760 часов, т.е. количество часов в году. Более высокое значение сезонности приведет к # ЧИСЛО! ошибка.

  • Завершение данных (необязательно) — учитывает недостающие точки.
    • 1 или опущено (по умолчанию) — заполнить пропущенные точки как среднее значение соседних точек (линейная интерполяция).
    • 0 — считать недостающие точки нулями.
  • Агрегирование (необязательно) — указывает, как агрегировать несколько значений данных с одной и той же меткой времени.
    • 1 или опущено (по умолчанию) — функция СРЕДНИЙ используется для агрегирования.
    • Другие варианты: 2 — COUNT, 3 — COUNTA, 4 — MAX, 5 — MEDIAN, 6 — MIN и 7 — SUM.

5 вещей, которые вы должны знать о FORECAST.ETS

  1. Для корректной работы функции FORECAST.ETS шкала времени должна иметь равный интервал — ежечасно, ежедневно, ежемесячно, ежеквартально, ежегодно и т. Д.
  2. Функция лучше всего подходит для нелинейных наборов данных с сезонным или другим повторяющимся шаблоном .
  3. Когда Excel не может обнаружить шаблон , функция возвращается к линейному прогнозу.
  4. Функция может работать с неполными наборами данных , где отсутствует до 30% точек данных. Пропущенные точки обрабатываются в соответствии со значением аргумента завершения данных .
  5. Хотя требуется временная шкала с последовательным шагом, в ряду даты / времени может быть дубликатов . Значения с одинаковой меткой времени агрегируются, как определено аргументом агрегация .

Функция FORECAST.ETS не работает:

Если ваша формула выдает ошибку, это, вероятно, будет одним из следующих:

  1. # Н / Д возникает, если значения , массивы и шкалы времени имеют разную длину.
  2. # ЗНАЧЕНИЕ! ошибка возвращается, если сезонность , завершение данных или агрегация аргумент нечисловой.
  3. # ЧИСЛО! ошибка может быть выдана по следующим причинам:
    • Не удается обнаружить согласованный размер шага на шкале времени .
    • Значение сезонности выходит за пределы поддерживаемого диапазона (0 — 8,7600).
    • Завершение данных Значение отличается от 0 или 1.
    • Значение агрегации вне допустимого диапазона (1–7).

Как использовать функцию ПРОГНОЗ.ETS в Excel — пример формулы

Чтобы увидеть, чем будущие значения, рассчитанные с помощью экспоненциального сглаживания, отличаются от прогноза линейной регрессии, давайте сделаем ПРОГНОЗ.Формула ETS для того же набора данных, который мы использовали в предыдущем примере:

= ПРОГНОЗ.ETS (A23, $ B $ 2: $ B $ 22, $ A $ 2: $ A $ 22)

Где:

  • A23 — плановая дата
  • $ B $ 2: $ B $ 22 — исторические данные ( значения )
  • $ A $ 2: $ A $ 22 — даты (шкала времени )

Опуская последние три аргумента ( сезонность , завершение данных или агрегация ), мы полагаемся на значения по умолчанию Excel.И Excel отлично прогнозирует тенденцию:

Функция Excel FORECAST.ETS.CONFINT

Функция FORECAST.ETS.CONFINT используется для вычисления доверительного интервала для прогнозируемого значения.

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

FORECAST.ETS.CONFINT доступен в Excel для Office 365, Excel 2019 и Excel 2016.

Функция имеет следующие аргументы:

ПРОГНОЗ.ETS.CONFINT (целевая_дата, значения, временная шкала, [уровень достоверности], [сезонность], [завершение данных], [агрегирование])

Как видите, синтаксис FORECAST.ETS.CONFINT очень похож на синтаксис функции FORECAST.ETS, за исключением этого дополнительного аргумента:

Confidence_level (необязательно) — число от 0 до 1, указывающее уровень достоверности для рассчитанного интервала. Обычно это десятичное число, хотя допустимы и проценты. Например, чтобы установить уровень достоверности 90%, вы вводите либо 0.9 или 90%.

  • Если опущено, используется значение по умолчанию 95%, что означает, что 95% времени ожидается, что прогнозируемая точка данных будет находиться в пределах этого радиуса от значения, возвращаемого FORECAST.ETS.
  • Если уровень достоверности выходит за пределы поддерживаемого диапазона (0–1), формула возвращает # ЧИСЛО! ошибка.

FORECAST.ETS.CONFINT Пример формулы

Чтобы увидеть, как это работает на практике, давайте вычислим доверительный интервал для нашего набора данных выборки:

= ПРОГНОЗ.ETS.CONFINT (A23, $ B $ 2: $ B $ 22, $ A $ 2: $ A $ 22)

Где:

  • A23 — плановая дата
  • $ B $ 2: $ B $ 22 — исторические данные
  • $ 2 $: 22 $ — даты

Последние 4 аргумента опущены, что указывает Excel использовать параметры по умолчанию:

  • Установите уровень достоверности 95%.
  • Автоматическое определение сезонности.
  • Заполните недостающие точки как среднее значение соседних точек.
  • Объедините несколько значений данных с одной и той же меткой времени с помощью функции СРЕДНИЙ.

Чтобы понять, что на самом деле означают возвращаемые значения, взгляните на снимок экрана ниже (некоторые строки с историческими данными скрыты для экономии места).

Формула в D23 дает результат 6441,22 (с округлением до 2 десятичных знаков). Это означает, что в 95% случаев прогноз на 11 марта будет находиться в пределах 6441,22 от прогнозируемого значения 61 075 (C3). Это 61 075 ± 6441,22.

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

Чтобы получить нижнюю границу , вычтите доверительный интервал из прогнозируемого значения:

= C23-D23

Чтобы получить верхнюю границу , добавьте доверительный интервал к прогнозируемому значению:

= C23 + D23

Где C23 — это прогнозируемое значение, возвращаемое FORECAST.ETS, а D23 — доверительный интервал, возвращаемый FORECAST.ETS.CONFINT.

Скопируйте приведенные выше формулы, нанесите результаты на диаграмму, и вы получите четкое визуальное представление прогнозируемых значений и доверительного интервала:

Excel ПРОГНОЗ.ЕТС.РЕЗОНАЛЬНОСТЬ (функция

)

Функция FORECAST.ETS.SEASONALITY используется для вычисления длины повторяющегося шаблона на указанной временной шкале. Он тесно связан с FORECAST.ETS, потому что обе функции используют один и тот же алгоритм для определения сезонности.

Эта функция доступна в Excel для Office 365, Excel 2019 и Excel 2016.

Синтаксис FORECAST.ETS.SEASONALITY следующий:

FORECAST.ETS.SEASONALITY (значения, временная шкала, [data_completion], [агрегация])

Для нашего набора данных формула принимает следующий вид:

= ПРОГНОЗ.ETS.SEASONALITY (B2: B22, A2: A22)

И возвращает сезонность 7, что полностью согласуется с недельным паттерном наших исторических данных:

Функция Excel FORECAST.ETS.STAT

Функция FORECAST.ETS.STAT в возвращает указанное статистическое значение, относящееся к прогнозированию экспоненциального сглаживания временных рядов.

Как и другие функции ETS, он доступен в Excel для Office 365, Excel 2019 и Excel 2016.

Функция имеет следующий синтаксис:

ПРОГНОЗ.ETS.STAT (значения, временная шкала, тип_статистики, [сезонность], [завершение_данных], [агрегирование])

Аргумент statistic_type указывает, какое статистическое значение нужно вернуть:

  1. Alpha (базовое значение) — значение сглаживания от 0 до 1, которое управляет взвешиванием точек данных. Чем выше значение, тем больший вес имеют последние данные.
  2. Beta (значение тренда) — значение от 0 до 1, определяющее расчет тренда.Чем выше значение, тем больше внимания уделяется последним тенденциям.
  3. Гамма (значение сезонности) — значение от 0 до 1, которое контролирует сезонность прогноза ETS. Чем выше значение, тем больший вес придается недавнему сезонному периоду.
  4. MASE (средняя абсолютная масштабированная ошибка) — мера точности прогноза.
  5. SMAPE (симметричная средняя абсолютная процентная ошибка) — показатель точности, основанный на процентных или относительных ошибках.
  6. MAE (средняя абсолютная ошибка) — измеряет среднюю величину ошибок прогноза, независимо от их направления.
  7. RMSE (среднеквадратичная ошибка) — мера разницы между предсказанными и наблюдаемыми значениями.
  8. Размер шага обнаружен — размер шага обнаружен на временной шкале.

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

= ПРОГНОЗ.ETS.STAT (B2: B22, A2: A22, 1)

На скриншоте ниже показаны формулы для других статистических значений:

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

Вас также может заинтересовать

Как прогнозировать доход в Excel на 2021 год

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

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

Важность прогнозирования доходов

Точное прогнозирование доходов может помочь вам:

  • Предскажите темпы роста вашей компании
  • Освободите оборотный капитал
  • Управляйте производственными графиками Управляйте денежными потоками

Точные данные прогнозирования учитывают такие аспекты, как сезонность, исторические показатели продаж и тенденции продаж.Сочетание правильных данных с правильными методами прогнозирования доходов в Microsoft Excel может помочь вам понять, что вы можете ожидать в течение определенного периода времени, например следующего месяца или следующего квартала продаж.

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

Методы прогнозирования доходов в Excel

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

  • Скользящие средние
  • Экспоненциальное сглаживание
  • Линейная регрессия

Прогнозирование доходов на основе скользящих средних в Excel

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

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

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

Плюсов:

  • Полезно для краткосрочного прогнозирования тенденций и доходов от продаж
  • Прогноз меняется с течением времени
  • Учет микро отклонений для получения более четкого и точного прогноза
  • Чисто объективный на основе исторических данных

Минусы:

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

Как использовать Excel для прогнозирования скользящих средних

Чтобы использовать Excel для создания прогноза со скользящими средними, выполните следующие действия.

  • Создайте новый лист и введите исторические данные за заданный период времени. — Вы можете использовать модель скользящих средних для таких временных рамок, как неделя или столько, сколько хотите.
  • Рассчитайте средний доход от продаж за выбранный период времени. — Используйте функцию «Среднее». ПРИМЕР: = СРЕДНЕЕ (G2: I2) за трехмесячный период на изображении ниже.
  • Кроме того, вы можете сгенерировать диаграмму Excel для облегчения анализа данных с первого взгляда.- Создайте диаграмму, используя исторические данные за заданный период времени. Щелкните значок плюса и выделите «Линия тренда». Щелкните правой кнопкой мыши, чтобы выбрать «дополнительные параметры». Затем выберите «Скользящее среднее» и выберите желаемый период времени. Вы создадите линейную диаграмму, которая поможет вам определять тенденции продаж и делать прогнозы.

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

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

Экспоненциальное сглаживание выручки в Excel

Экспоненциальное сглаживание, такое как ETS (ошибка, тренд, сезонность), учитывает сезонные закономерности выручки от продаж. Как и метод скользящего среднего, экспоненциальное сглаживание компенсирует случайные величины. По сути, это «сглаживает» ваши данные.

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

Методы экспоненциального сглаживания, такие как ETS, также по-разному присваивают значение данных в зависимости от того, сколько им лет. Когда вы вводите данные временного ряда, новые данные получают более высокое значение, чем старые данные, чтобы в идеале создать более точные прогнозируемые значения.

Бизнес-профессионалы часто используют один из трех типов методов экспоненциального сглаживания:

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

Плюсы:

  • Полезно для долгосрочного прогнозирования продаж
  • Учет сезонности продаж и тенденций выручки
  • Взвешенные переменные могут дать более точные будущие значения
  • Простота использования

Минусы:

  • Ошибки ввода данных могут создавать ложные прогнозы
  • Не удается учесть рыночные тенденции или реальные изменения в реальном времени
  • Прогнозы могут отставать от фактических рыночных тенденций

Как прогнозировать с помощью экспоненциального сглаживания в Excel

  • Создайте новый рабочий лист и используйте функцию Excel «Таблица прогнозов» для создания формул и диаграмм.- Вы можете выбрать столбчатую или линейную диаграмму.
  • Выберите дату окончания для прогноза. — Имейте в виду, что простое экспоненциальное сглаживание лучше всего подходит для краткосрочных прогнозов, но вы можете использовать тройное экспоненциальное сглаживание для сезонных прогнозов.
  • Excel создает новый рабочий лист. — На новом рабочем листе отображаются три столбца, показывающие прогнозные и граничные значения для доверительного интервала или диапазона, в который должны упасть будущие значения.Функция FORECAST.ETS вычисляет эти значения прогноза, создавая алгоритм ETS на основе исторических данных и временного интервала.
  • Настройте свои данные. — Значения прогноза могут измениться при использовании функции FORECAST.ETS в зависимости от ряда переменных. Эти переменные включают доверительный интервал, сезонность, временной диапазон и диапазон значений.
  • При желании отобразить статистику прогноза. — FORECAST.ETS.STATS можно использовать для отображения коэффициентов сглаживания.

Прогнозирование доходов с помощью линейной регрессии в Excel

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

После того, как вы нанесли на карту взаимосвязь между двумя переменными в Excel, вы можете использовать функцию прогноза для прогнозирования дохода.

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

Плюсов:

  • Простота использования и понимания
  • Полезно для просмотра взаимосвязи между переменными

Минусы:

  • Корреляция может не равняться причинно-следственной связи, что может привести к неточным прогнозам
  • Предполагает линейную связь между x_values ​​и y_values ​​без учета других реальных факторов
  • Зависит от точного ввода данных
  • Невозможно использовать для прогнозирования сезонных тенденций
  • Исторические данные могут не предсказывать будущие тенденции

Как делать прогнозирование линейной регрессии в Excel
  • Начните новый рабочий лист и добавьте точки временной шкалы — Это определяет продолжительность вашего прогноза.
  • Введите значения x_values ​​и y_values.
  • Используйте функцию «Прогноз», чтобы создать прогноз дохода с линейной регрессией.

Плюсы и минусы использования Excel

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

Плюсы
  • Предлагает несколько средств финансового прогнозирования
  • Простота использования
  • Легкость обмена среди людей в вашей организации
  • Анализ и прогнозы на основе объективных исторических данных

Минусы
  • Ручной процесс может стать жертвой опечаток и ошибок при вводе данных
  • Прогнозирование настолько же точно, как и введенные данные
  • Данные в реальном времени недоступны
  • Может плохо предсказать тенденции
  • Создает большие файлы, которыми трудно поделиться те, кто находится за пределами вашей организации
  • Требуется экспорт данных из SalesForce или других CRM-систем

revVana Automates Forecasting Revenue Облачная платформа

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

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

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

Вам не нужно обучать свой отдел продаж или финансовый отдел новым методам или изучать новую программу, так как revVana Plan автоматически создает прогнозы доходов от продаж в фоновом режиме.Интегрируйте план revVana с SalesForce и мгновенно прогнозируйте доход в любое время.

Получите бесплатную демоверсию прямо сейчас.


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

ПОДРОБНЕЕ

Прогнозирование в Excel 2016

Прогнозирование важно во многих случаях для эффективного и действенного планирования.

Прогноз будущих потребностей в электроэнергии жизненно важен для планирования строительства новых электростанций; планирование работы сотрудников в колл-центре на следующей неделе требует прогнозов количества звонков; просто представляю, сколько прогнозов должны были сделать организаторы Олимпийских игр 2012 года в Лондоне.Прогнозы могут быть необходимы на месяцы или годы вперед или всего за несколько минут. Какими бы ни были ситуации или временные рамки, точные прогнозы являются жизненно важной частью подготовки и планирования. Подробнее об учебной таблице Excel.

Ключевой шаг в прогнозировании — это знать, можно ли что-то спрогнозировать точно, или лучше просто подбросить монетку. Хорошие прогнозы фиксируют подлинные закономерности и взаимосвязи, существующие в исторических данных, но не воспроизводят события из прошлого, которые больше не повторится.

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

Анализ WhatIf

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

Некоторые из полезных инструментов прогнозирования в Excel:

  • Для анализа тенденций вы можете использовать функцию Trend , а для визуализации и расчета тенденций диаграммы Excel могут отображать тенденции и уравнения для тенденции.Вы можете рассчитать точность тренда или Excel может предоставить вам эту информацию в виде диаграммы. Функция Trend вернет точный результат только в том случае, если вы работаете с линейными данными.

  • Для прогнозирования линейных данных можно использовать функцию Прогноз . Функция Forecast может прогнозировать любое количество периодов в будущем.
  • Если вы работаете с экспоненциальными данными, вы можете использовать функцию Growth для прогнозирования, а экспоненциальные тенденции также можно визуализировать на диаграмме Excel.

  • Инструмент Solver в Excel может оказаться огромным подспорьем, если вы хотите прогнозировать сезонные данные.
  • Пакет Analysis Toolpak предоставляет нам Exponential Smoothing, Moving Average и Regression , которые являются всеми инструментами, которые нам нужны для создания правильной модели прогноза.
  • Инструменты Scenario Manager и Goal Seek могут оказать большую помощь в получении точного прогноза.
  • Excel также имеет все параметры и функции для измерения точности нашего прогноза, что позволяет постоянно развивать нашу модель прогноза для получения очень точного прогноза.

График прогнозов

В Excel 2016 Microsoft значительно упростила прогнозирование. Microsoft добавила в Excel инструмент Forecast Sheet . Этот инструмент может дать вам прогноз за несколько секунд.

В Excel 2016 выберите два соответствующих набора данных и перейдите в «Данные> Прогноз> Таблица прогнозов». В поле «Создать лист прогноза» выберите линейную или столбчатую диаграмму, выберите дату окончания и нажмите «Создать».

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

В списке «Заполнить отсутствующие точки с помощью» вы можете указать Excel, как обрабатывать отсутствующие данные. «Интерполяция» вычислит недостающие данные.

В списке «Сводные данные с использованием дубликатов» выберите, как вы хотите, чтобы Excel вычислял повторяющиеся записи.

Best STL предлагает курс по прогнозированию и анализу данных, в котором исследуются все различные методы и модели.Это выборка из наших учебных курсов по Office 365 в Лондоне.

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

Прогнозирование в Excel и экспоненциальное сглаживание — Эдвард Бодмер — Проектные и корпоративные финансы

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

Один из моих студентов спросил меня, как работает прогноз Excel. В то время я понятия не имел, что есть даже прогноз в Excel. Затем я увидел прогноз в Excel, который выглядит довольно круто. Когда вы заходите в Интернет и делаете прогнозы Google в Excel, вы получаете целую кучу обсуждений того, как можно нажать пару кнопок и получить некоторые прогнозы. Многие объяснения также говорят вам, как вы можете использовать ПРОГНОЗ.ETS функционирует и извлекает статистику, такую ​​как альфа и бета, а также среднеквадратичную статистику. Эти пояснения также расскажут вам, как получить доверительные интервалы с помощью функции FORECAST.ETS.CONFINT. На одном веб-сайте, который называется реальной статистикой, вы узнаете, как составить собственный прогноз (и я использовал его). Изначально я работал над прогнозированием в целом из-за вопроса человека из одного из моих классов, который хотел знать, как рассчитываются доверительные интервалы.

Но вот проблема, ETS.ПРОГНОЗ — это черный ящик. Ни на одном из веб-сайтов не рассказывается подробно, как детально воспроизвести прогноз и доверительные интервалы. Это большая проблема, потому что прогноз делается на основе экспоненциального сглаживания, что является очень простым процессом. Когда я попытался создать свою собственную модель в файле рабочего листа ниже, мне не удалось воспроизвести параметры прогноза в Excel.

Файл Excel, который работает с помощью прогноза Excel и экспоненциального сглаживания в подробном порядке с помощью параметрического теста

Может быть, это потому, что я просто тупой (наверное).Прогнозы для различных темпов роста ВВП на душу населения выглядят хорошо, но я думаю, что есть что-то очень странное с параметрами, указанными в прогнозах Excel. Во-первых, прогнозы часто имеют четкую тенденцию. Но параметр тенденции — бета — сообщаемый в ETS.FORECAST.STATISTICS, равен 0,001. Я действительно могу что-то здесь упустить, поэтому я должен быть осторожен с моими заявлениями. Возможно, они неправильно моделируют бета-параметр, но я так не думаю. Что действительно плохо в этом, так это то, что параметр бета, безусловно, является наиболее важным фактором при составлении прогноза.Без параметра бета прогноз экспоненциального сглаживания представляет собой плоскую линию.

Файл, который вы можете загрузить, щелкнув указанную выше кнопку, начинается с демонстрации того, как инструмент прогноза Excel работает, как и все другие веб-сайты (это действительно не имеет большого значения). За секунды вы можете сделать прогноз временного ряда в Excel. Вы просто выбираете серию данных, смежную с серией дат, и получаете серию прогнозов с доверительным интервалом. Вы можете экспортировать прогноз на отдельный лист.Вопрос только в том, какие методы использует Excel для этого. Во-первых, просто получите некоторые данные. Вы можете получить файл для экономических переменных и делать прогнозы для таких вещей, как ВВП на душу населения, население, ожидаемая продолжительность жизни и т. Д. Единственное, что вам нужно сделать, это выбрать дату, затем перейти на ленту ДАННЫХ и затем щелкнуть вкладку прогноза как показано ниже. При этом используется функция FORECAST.ETS.

Как только вы это сделаете, вы можете выбрать опцию для вывода статистики. Это вычисляется с помощью ПРОГНОЗА.Функция ETS.SEASONALITY. Пример этого вывода показан ниже.

Это все довольно скучно. Реальный вопрос в том, как делаются прогнозы. Чтобы проиллюстрировать, как составляется прогноз с использованием экспоненциального сглаживания, начнем с простого случая без тренда. Этот прогноз будет одинаковым для каждого года прогноза. Прогноз представляет собой средневзвешенное значение текущего значения данных и предыдущего прогноза. Текущим данным присвоен вес альфа, а предыдущему прогнозу — вес (1-альфа).Это означает, что альфа умножается на текущее значение, а предыдущий прогноз умножается на 1-альфа, как показано ниже. Иллюстрация демонстрирует, что прогноз остается неизменным. Абсолютная ошибка и среднеквадратическая ошибка могут использоваться для поиска наилучшего значения альфа с помощью таблицы данных или макроса. В столбце Q альфа умножена на текущее значение данных. Ошибка — это разница между прогнозируемым значением и фактическими данными.

Более интересно включить в анализ тенденцию, чем взвешивать только предыдущий прогноз и текущее значение.Чтобы сделать этот анализ тенденций, вы можете включить бета-фактор. Этот коэффициент бета или тренда применяется к тренду, а не к прогнозу. Тренд вычисляется как разница между предыдущим значением без тенденции и текущим значением без тенденции. Они вычисляются аналогично простому экспоненциальному сглаживанию. Для меня главное — иметь правильное количество столбцов. Один столбец предназначен для экспоненциального сглаживания без тренда. Следующее вычисляет тренд. Затем, наконец, можно рассчитать прогноз тенденции.

Единственное сглаживание — это последнее прогнозируемое значение без тренда плюс текущее значение, умноженное на альфа, как показано ниже.

Интереснее понять тренд в анализе. Для этого вы можете включить бета-фактор. Этот коэффициент применяется к тренду, а не к прогнозу. Тренд вычисляется как разница между предыдущим значением без тенденции и текущим значением без тенденции.Они вычисляются аналогично простому экспоненциальному сглаживанию.

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

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *