Как в excel сделать прогноз

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

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

Создание прогноза

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

ряд значений даты или времени для временной шкалы;

ряд соответствующих значений показателя.

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

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

Выделите оба ряда данных.

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

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

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

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

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

Этот лист будет находиться слева от листа, на котором вы ввели ряды данных (то есть перед ним).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Объединение дубликатов с помощью

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

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

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

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

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

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

столбец статистических значений времени (ваш ряд данных, содержащий значения времени);

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

столбец прогнозируемых значений (вычисленных с помощью функции ПРЕДСКАЗ.ЕTS);

два столбца, представляющие доверительный интервал (вычисленные с помощью функции ПРЕДСКАЗ.ЕTS.ДОВИНТЕРВАЛ). Эти столбцы отображаются только в том случае, если в разделе ” Параметры ” установлен флажок ” доверительный интервал “.

Скачайте пример книги.

Дополнительные сведения

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

См. также:

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

Источник: support.office.com

Функция ПРЕДСКАЗ для прогнозирования будущих значений в Excel

Функция ПРЕДСКАЗ в Excel позволяет с некоторой степенью точности предсказать будущие значения на основе существующих числовых значений, и возвращает соответствующие величины. Например, некоторый объект характеризуется свойством, значение которого изменяется с течением времени. Такие изменения могут быть зафиксированы опытным путем, в результате чего будет составлена таблица известных значений x и соответствующих им значений y, где x – единица измерения времени, а y – количественная характеристика свойства. С помощью функции ПРЕДСКАЗ можно предположить последующие значения y для новых значений x.

Примеры использования функции ПРЕДСКАЗ в Excel

Функция ПРЕДСКАЗ использует метод линейной регрессии, а ее уравнение имеет вид y=ax+b, где:

  1. Коэффициент a рассчитывается как Yср.-bXср. (Yср. и Xср. – среднее арифметическое чисел из выборок известных значений y и x соответственно).
  2. Коэффициент b определяется по формуле:

Пример 1. В таблице приведены данные о ценах на бензин за 23 дня текущего месяца. Согласно прогнозам специалистов, средняя стоимость 1 л бензина в текущем месяце не превысит 41,5 рубля. Спрогнозировать стоимость бензина на оставшиеся дни месяца, сравнить рассчитанное среднее значение с предсказанным специалистами.

Вид исходной таблицы данных:

Пример 1.” src=”https://exceltable.com/funkcii-excel/images/funkcii-excel145-2.png” >

Чтобы определить предполагаемую стоимость бензина на оставшиеся дни используем следующую функцию (как формулу массива):

  • A26:A33 – диапазон ячеек с номерами дней месяца, для которых данные о стоимости бензина еще не определены;
  • B3:B25 – диапазон ячеек, содержащих данные о стоимости бензина за последние 23 дня;
  • A3:A25 – диапазон ячеек с номерами дней, для которых уже известна стоимость бензина.

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

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

Анализ прогноза спроса продукции в Excel по функции ПРЕДСКАЗ

Пример 2. Компания недавно представила новый продукт. С момента вывода на рынок ежедневно ведется учет количества клиентов, купивших этот продукт. Предположить, каким будет спрос на протяжении 5 последующих дней.

Вид исходной таблицы данных:

Пример 2.” src=”https://exceltable.com/funkcii-excel/images/funkcii-excel145-6.png” >

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

Читайте также:  Как посчитать сумму в эксель

Рассчитаем значения логарифмического тренда с помощью функции ПРЕДСКАЗ следующим способом:

Как видно, в качестве первого аргумента представлен массив натуральных логарифмов последующих номеров дней. Таким образом получаем функцию логарифмического тренда, которая записывается как y=aln(x)+b.

Для сравнения, произведем расчет с использованием функции линейного тренда:

И для визуального сравнительного анализа построим простой график.

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

Прогнозирование будущих значений в Excel по условию

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

Вид таблицы данных:

Для расчета будущих значений Y без учета отрицательных значений (-5, -20 и -35) используем формулу:

C помощью функций ЕСЛИ выполняется перебор элементов диапазона B2:B11 и отброс отрицательных чисел. Так, получаем прогнозные данные на основании значений в строках с номерами 2,3,5,6,8-10. Для детального анализа формулы выберите инструмент «ФОРМУЛЫ»-«Зависимости формул»-«Вычислить формулу». Один из этапов вычислений формулы:

Особенности использования функции ПРЕДСКАЗ в Excel

Функция имеет следующую синтаксическую запись:

  • x – обязательный для заполнения аргумент, характеризующий одно или несколько новых значений независимой переменной, для которых требуется предсказать значения y (зависимой переменной). Может принимать числовое значение, массив чисел, ссылку на одну ячейку или диапазон;
  • известные_значения_y – обязательный аргумент, характеризующий уже известные числовые значения зависимой переменной y. Может быть указан в виде массива чисел или ссылки на диапазон ячеек с числами;
  • известные_значения_x – обязательный аргумент, который характеризует уже известные значения независимой переменной x, для которой определены значения зависимой переменной y.
  1. Второй и третий аргументы рассматриваемой функции должны принимать ссылки на непустые диапазоны ячеек или такие диапазоны, в которых число ячеек совпадает. Иначе функция ПРЕДСКАЗ вернет код ошибки #Н/Д.
  2. Если одна или несколько ячеек из диапазона, ссылка на который передана в качестве аргумента x, содержит нечисловые данные или текстовую строку, которая не может быть преобразована в число, результатом выполнения функции ПРЕДСКАЗ для данных значений x будет код ошибки #ЗНАЧ!.
  3. Статистическая дисперсия величин (можно рассчитать с помощью формул ДИСП.Г, ДИСП.В и др.), передаваемых в качестве аргумента известные_значения_x, не должна равняться 0 (нулю), иначе функция ПРЕДСКАЗ вернет код ошибки #ДЕЛ/0!.
  4. Рассматриваемая функция игнорирует ячейки с нечисловыми данными, содержащиеся в диапазонах, которые переданы в качестве второго и третьего аргументов.
  5. Функция ПРЕДСКАЗ была заменена функцией ПРЕДСКАЗ.ЛИНЕЙН в Excel версии 2016, но была оставлена для обеспечения совместимости с Excel 2013 и более старыми версиями.
  6. Для предсказания только одного будущего значения на основании известного значения независимой переменной функция ПРЕДСКАЗ используется как обычная формула. Если требуется предсказать сразу несколько значений, в качестве первого аргумента следует передать массив или ссылку на диапазон ячеек со значениями независимой переменной, а функцию ПРЕДСКАЗ использовать в качестве формулы массива.

Источник: exceltable.com

5 способов расчета значений линейного тренда в MS Excel

Это первая статья из серии “Как самостоятельно рассчитать прогноз продаж с учетом роста и сезонности”, из которой вы узнаете о 5 способах расчета значений линейного тренда в Excel.

Для того, чтобы легче было научиться прогнозировать продажи с учетом роста и сезонности, я разбил 1 большую статью о расчете прогноза на 3 части:

    1. Расчет значений тренда (рассмотрим на примере Линейного тренда в этой статье);
    2. Расчет сезонности;
    3. Расчет прогноза;

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

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

Рассмотрим линейный тренд на примере расчета прогноза продаж в Excel по месяцам.

Временной ряд продажи по месяцам (см. вложенный файл).

В этом временном ряду у нас есть 2 переменных:

Уравнение линейного тренда y(x)=a+bx, где

y — это объёмы продаж

x — номер периода (порядковый номер месяца)

a – точка пересечения с осью y на графике (минимальный уровень);

b – это значение, на которое увеличивается следующее значение временного ряда;

1-й способ расчета значений линейного тренда в Excel с помощью графика

Выделяем анализируемый объём продаж и строим график, где по оси Х — наш временной ряд (1, 2, 3… — январь, февраль, март …), по оси У – объёмы продаж. Добавляем линию тренда и уравнение тренда на график. Получаем уравнение тренда y=135134x+4594044

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

При расчете значений линейного тренде нам будут известны:

  1. Время – значение по оси Х;
  2. Значение “a” и “b” уравнения линейного тренда y(x)=a+bx;

Рассчитываем значения тренда для каждого периода времени от 1 до 25, а также для будущих периодов с 26 месяца до 36.

Например, для 26 месяца значение тренда рассчитывается по следующей схеме: в уравнение подставляем x=26 и получаем y=135134*26+4594044=8107551

27-го y=135134*27+4594044=8242686

2-й способ расчета значений линейного тренда в Excel — функция ЛИНЕЙН

1. Рассчитаем коэффициенты линейного тренда с помощью стандартной функции Excel:

=ЛИНЕЙН(известные значения y, известные значения x, константа, статистика)

Для расчета коэффициентов в формулу вводим

известные значения y (объёмы продаж за периоды),

известные значения x (номера периодов),

вместо константы ставим 1,

вместо статистики 0,

Получаем 135135 – значение (b) линейного тренда y=a+bx;

Для того чтобы Excel рассчитал сразу 2 коэффициента (a) и (b) линейного тренда y=a+bx, необходимо

    1. установить курсор в ячейку с формулой и выделить соседнюю справа, как на рисунке;
    2. нажимаем клавишу F2, а затем одновременно — клавиши CTRL + SHIFT + ВВОД.

Получаем 135135, 4594044 – значение (b) и (a) линейного тренда y=a+bx;

2. Рассчитаем значения линейного тренда с помощью полученных коэффициентов . Подставляем в уравнение y=135134*x+4594044 номера периодов – x, для которых хотим рассчитать значения линейного тренда.

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

3-й способ расчета значений линейного тренда в Excel — функция ТЕНДЕНЦИЯ

Рассчитаем значения линейного тренда с помощью стандартной функции Excel:

=ТЕНДЕНЦИЯ(известные значения y; известные значения x; новые значения x; конста)

Подставляем в формулу

  1. известные значения y – это объёмы продаж за анализируемый период (фиксируем диапазон в формуле, выделяем ссылку и нажимаем F4);
  2. известные значения x – это номера периодов x для известных значений объёмов продаж y;
  3. новые значения x – это номера периодов, для которых мы хотим рассчитать значения линейного тренда;
  4. константа – ставим 1, необходимо для того, чтобы значения тренда рассчитывались с учетом коэффицента (a) для линейного тренда y=a+bx;

Для того чтобы рассчитать значения тренда для всего временного диапазона, в “новые значения x” вводим диапазон значений X, выделяем диапазон ячеек равный диапазону со значениями X с формулой в первой ячейке и нажимаем клавишу F2, а затем — клавиши CTRL + SHIFT + ВВОД.

4-й способ расчета значений линейного тренда в Excel — функция ПРЕДСКАЗ

Рассчитаем значения линейного тренда с помощью стандартной функции Excel:

=ПРЕДСКАЗ(x; известные значения y; известные значения x)

Вместо X поставляем номер периода, для которого рассчитываем значение тренда.

Вместо “известные значения y” – объёмы продаж за анализируемый период (фиксируем диапазон в формуле, выделяем ссылку и нажимаем F4);

“известные значения x” – это номера периодов для каждого выделенного объёма продаж.

3-й и 4-й способ расчета значений линейного тренда быстрее, чем 1 и 2-й, однако с его помощью невозможно управлять коэффициентами тренда, как описано в статье “О линейном тренде”.

5-й способ расчета значений линейного тренда в Excel — Forecast4AC PRO

2. Заходим в меню программы и нажимаем “Start_Forecast”. Значения линейного тренда рассчитаны.

Читайте также:  Excel если больше или меньше в excel

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

В следующих статье “Как самостоятельно сделать прогноз продаж с учетом роста и сезонности” мы:

О том, что еще важно знать о линейном тренде, вы можете узнать в статье “Что важно знать о линейном тренде”.

Точных вам прогнозов!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

  • Novo Forecast Lite – автоматический расчет прогноза в Excel .
  • 4analytics – ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition – BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO – прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Источник: 4analytics.ru

Прогнозирование с помощью Excel (Эксель модели примеры методы)

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

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

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

Рассмотрим несколько способов, которые могут дать прогнозированный результат:

Линия тренда – графическое отображение прогнозирования за счет экстраполяции. Звучит заумно? На практике все проще.
Давайте попробуем спрогнозировать сумму доходов компании через 36 месяцев на основе показателей за прошлые 12 лет.

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

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

Произведем небольшие настройки формата линии: «Прогноз» установим на три года, вписываем «3.0», и укажем, чтобы показывалась величина достоверности и само уравнение на диаграмме.

По построенной линии тренда можем спрогнозировать доход через три года – он будет более 4500 тыс. руб. Достоверность прогнозирования принято считать верным при «0.85» ед. Эффективность прогнозирования не будет успешным, если период будет превышать 30% от периода базы.

2. Использование оператора ПРЕДСКАЗ

Также в наборе функций программы есть ряд стандартных фунций создания прогноза. Одним из таких является оператор «ПРЕДСКАЗ», синтаксис которого таковой: «=ПРЕДСКАЗ(X;известные_значения_y;известные значения_x)».

Аргумент «Х», исходя из нашей таблицы, это искомый год для прогнозирования. «Значения у» – прибыль за прошлое время. «Значения х» – года, в течение которых были собраны данные.

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

В появившемся диалоговом окне укажем все исходные данные, согласно описанию выше.

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

Еще одним статическим оператором, который можно использовать для прогнозирования, является оператор «ТЕНДЕНЦИЯ» со следующим синтаксисом: «=ТЕНДЕНЦИЯ(Известные значения_y;известные значения_x; новые_значения_x;[конст])». Аргументы оператора идентичны аргументам оператора «ПРЕДСКАЗ».

Попробуем провести прогнозирование на следующий год, используя оператор «ТЕНДЕНЦИЯ». В новую ячейку вставим функцию из мастера функций.

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

4. Использование оператора РОСТ

Аналогичным методом для прогноза данных является функция «РОСТ», за исключением того, что он использует при расчете прогноза экспоненциальную зависимость, в отличие от предыдущих методов, которые использовали линейную. Его аргументы идентичны аргументам оператора «ТЕНДЕНЦИЯ».

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

5. Использование оператора ЛИНЕЙН

Другой оператор, который может спрогнозировать результат на определенный период времени, оператор «ЛИНЕЙН», который основан на линейном приближении. Его синтаксис схож с прошлыми операторами: «=ЛИНЕЙН(Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])».

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

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

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

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

Источник: user-life.com

Прогнозирование продаж в Excel с учетом сезонности

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

Из чего состоит временной ряд

Уровни временного ряда (Yt) представляют из себя сумму двух компонент:

  1. Регулярную составляющую
  2. Случайную составляющую

В свою очередь регулярная составляющая состоит из:

  1. Тренда
  2. Сезонности
  3. Циклической составляющей

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

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

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

  1. Тренд (Tt)
  2. Сезонность (St)
  3. Цикличность (Ct)
  4. Случайные возмущения (Et)

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

Виды моделей временного ряда

Обычно, выделяют две модели временного ряда и третью — смешанную.

    Аддитивная модель

Мультипликативная модель

Смешанная модель

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

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

Построение этих моделей сводится к расчету тренда (Tt), сезонности (St) и случайных возмущений (Et) для каждого уровня ряда (Yt).

Алгоритм построения модели

  1. Выравниваем ряд с помощью скользящей средней, то есть сглаживаем ряд и отфильтровываем высокочастотные колебания.
  2. Рассчитываем значение сезонной компоненты St.
  3. Рассчитываем значения Tt с использованием полученного уравнения тренда.
  4. Используя полученные значения St и Tt, находим прогнозные значения уровней временного ряда.
  5. Оцениваем качество модели.

Реализация на практике

Итак, мы имеем на руках данные о продажах за 2016 и 2017 год и хотим спрогнозировать продажи на 2018 год.

Шаг 1

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

Удобнее брать период сглаживания в виде нечетного числа, тогда формула для расчета уровней сглаженного ряда:

yi — фактическое значение i-го уровня ряда,

yt — значение скользящей средней в момент времени t,

2p+1 — длина интервала сглаживания.

Но так как мы решили использовать месячную динамику в виде четного числа 12, то данная формула нам не подойдет и мы воспользуемся этой:

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

Читайте также:  Excel количество чисел в диапазоне

Сглаживаем наши уровни ряда и растягиваем формулу вниз:

Сразу можем построить график из известных значений уровня продаж и их сглаженной. Выведем ее уравнение и значение коэффициента детерминации R^2:

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

Шаг 2

Так как мы рассматриваем аддитивную модель вида:

Найдем оценки сезонной компоненты как разность между фактическими уровнями ряда и значениями скользящей средней St+Et = Yt-Tt, так как Yt и Tt мы уже знаем.

Используем оценки сезонной компоненты (St+Et) для расчета значений сезонной компоненты St. Для этого найдем средние за каждый интервал (по всем годам) оценки сезонной компоненты St.

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

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

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

Шаг 3

Теперь рассчитываем значения уровня тренда T(t) по тому уравнению, которое мы получили при построении сглаженного тренда на первом шаге.

T(t) = – 23294 + 34114 * t – 1593 *t^2 + 26,3 *t^3

Вместо t используем значения из столбца Период из соответствующей строки.

Шаг 4

Имея рассчитанные значения S(t) и T(t) мы можем рассчитать прогнозные значения уровней ряда Y(t). Для этого накладываем уровни сезонности на тренд.

Теперь построим график известных значений Y(t) и спрогнозированных за 2018 год.

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

Шаг 5

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

yi — спрогнозированные уровни ряда,

yi* — фактические уровни ряда,

n — количество складываемых элементов.

Модель может считаться адекватной, если:

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

Сложив весь столбец с ошибками аппроксимации и поделив на 12, получаем среднюю ошибку аппроксимации 4,13%. Это значение меньше 15% и можем сделать вывод об адекватности модели.

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

Источник: thisisdata.ru

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

«Точные расчеты делать сложно, особенно когда дело касается прогнозов на будущее», — говорил датский физик Нильс Бор. Что ж, прогнозирование в один клик в Excel значительно упрощает эту задачу.

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

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

Экспоненциальное сглаживание для прогнозов

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

В новой функции Excel 2016 используется другой алгоритм: экспоненциальное сглаживание (ETS). Это один из самых популярных способов прогнозирования, который уже стал отраслевым стандартом.

Одно из главных преимуществ метода ETS — возможность обнаруживать сезонные закономерности и доверительные интервалы.

Сезонные закономерности

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

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

Продолжительность сезонности известна не всегда. Этот метод прогноза выявляет сезонные закономерности в хронологических данных, определяя ту, которая наиболее точно соответствует данным. Для наиболее точного прогноза хронологические данные должны содержать как можно больше повторяющихся циклов. Желательно, чтобы полных сезонных циклов было хотя бы 2–3. Автоматически обнаруживаемое значение в разделе сезонности можно найти в диалоговом окне Создание прогноза в разделе Параметры. Если сезонные данные обнаружить не удалось или сезонность известна заранее, то это значение можно переопределить, выбрав параметр Задать вручную.

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

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

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

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

Как создать прогноз

Чтобы создать лист прогноза, сначала подготовьте набор данных временного ряда (с временным рядом и рядом значений). Затем на вкладке «Данные» нажмите на кнопку Лист прогноза. Запустится диалоговое окно создания прогноза с пошаговыми инструкциями.

Как узнать, точен ли прогноз? Можно ли ему доверять?

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

Посмотрите на ширину доверительного интервала (см. выше).

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

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

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

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

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

Источник: www.administrator-pro.ru