Как в офисе

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

Здравствуйте, уважаемые читатели блога http://nocleep.ru/. Сегодня хочу поделиться информацией, как с помощью программы Excel можно легко и быстро обработать статистические данные и сделать прогноз.

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

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

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

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

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

Прогноз в Эксел

1 шаг. Исходные данные. Где взять?

Нам нужны исходные данные. Где их взять? На сайтах статистики, конечно. Я для своих статей беру данные на сайте Федеральной Службы Государственной Статистики. Для этого необходимо покопаться в разделе «Официальная статистика» или «Базы данных».

Статистика

2 шаг. Исходные данные. Как скачать?

Для расчета прогноза потребуются данные за конкретный период. Чем больше данных, тем точнее будет прогноз. Желательно, чтобы были помесячные данные хотя бы за два года или за несколько лет. Для своего примера я скачаю данные «Число персональных компьютеров в организациях» с 2003 по 2014 годы. И составлю прогноз на 5 лет, т.е. до 2019 года. Для этого нужно:

1) Зайти на сайт Федеральной Службы Государственной Статистики, «Официальная статистика», далее захожу где «Наука, инновации и информационное общество».

Инновации

2) Выбираю «Информационное общество», затем «Информационные и коммуникационные технологии», скачиваю таблицу с данными Excel.

Число компьютеров

3 шаг. Подготовка данных для расчета прогноза на 5 лет

Итак, данные у нас есть. Что с ними необходимо сделать?

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

Статистика в эксел

Во-вторых, данные для прогноза необходимо транспонировать, т.е. выстроить их в вертикальную таблицу. Для этого необходимо: 1) Выделить всю таблицу. 2) Буфер обмена/«Копировать» 3) Выделить новую ячейку, куда будете вставлять транспонированную таблицу. 4) Буфер обмена/«Вставить/Специальная вставка/Транспонировать«

Транспонировать

В итоге получаем вертикальную таблицу:

Транспонировать в эксел

В-третьих, для того, чтобы точки будущего прогноза встали на одну линию, необходимо переименовать годы в цифры: 1, 2, 3, 4, 5 и т.д.

Транспонировать в эксел

Готово! Теперь можно приступать к постройке графика.

4 шаг. Постройка графика

Построим точечную диаграмму с линиями. Для этого необходимо: 1) Выделить вертикальную таблицу (оба столбца с шапкой). 2) Дальше идем: Вставка/Диаграммы/Точечная/Точечная с гладкими отрезками и маркерами. Получаем вот такой график:

График в эксел

3) Для удобства уберем с графика все линии. Для этого, выделяем сначала горизонтальные линии/Delete (на клавиатуре), аналогично выделяем вертикальные линии/ Delete (на клавиатуре). Вот так:

Линии в эксел

4) Добавим легенду. Для этого нужно щелкнуть по таблице, в правом верхнем углу появится крестик. Щелкнув по нему, выбираем — легенда. В результате появится надпись:

Легенда в эксел

5 шаг. Строим прогноз на 5 лет

1) Если мы планируем построить прогноз на 5 лет, то соответственно, нужно продлить столбец с цифрами 1, 2, 3, 4, 5 и т.д. на 5 ячеек. Помните, что они у нас заменят года? Я их для вас выделила желтым цветом. Получаем продленную таблицу следующего вида:

Прогноз в эксел

2) Добавим линию тренда. Для этого необходимо: щелкнуть правой кнопкой мыши по знакомому нам крестику в правом верхнем углу таблицы/выбрать «Линия тренда». На графике появится линейная функция.

Линия тренда

3) Щелкнув по линии тренда мышкой два раза, открывается окно «Формат линии тренда».

Формат линии тренда

Что мы можем с этим делать?

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

Цвет линии тренда

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

Прогноз на пять лет

В-третьих, рекомендую полученное уравнение и коэффициент тесноты связи также покрасить в синий цвет, что и линия тренда. Так Вы никогда не перепутаете, какое уравнение к какой линии тренда относится. Для этого: щелкаем по уравнению, чтобы выделить прямоугольник для закрашивания, справа переходим во вкладку, где ведерко с краской. Выбираем: «Заливка» — «Сплошная заливка» — «Цвет» — синий. Готово!

Цвет прогноза

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

Пять линий тренда в эксел

5) Для постановки точек прогноза выбираем уравнение, где коэффициент тесноты связи R² — наибольшее число. В моем случае это «Полиномиальный тренд» с уравнением и теснотой связи:

Полиниальный тренд в эксел

6) Теперь внимательно. Выбранное уравнение нужно набрать языком Excel в ячейке начала прогноза.

Как должно выглядеть уравнение на языке Excel?

Например, мое уравнение:

y = −0,1837×2 + 2,9289x + 83,664

на языке ексел будет выглядеть так:

=-0,1837*A18^2+2,9289*A18+83,664

Что я сделала? — убрала «y», потому что в Excel все формулы начинаются со знака «=», — ввела знак умножения «*» — в Excel он обозначается звездочкой, — подставила вместо «х» — число года начала прогноза, в моем случае это «А18», — ввела знак, обозначающий степень «^».

В какую ячейку вводить уравнение?

Я ввожу в ячейку «В18», и получаю первую цифру прогноза на 2015 год (вы же помните, что все года мы заменили порядковыми цифрами?).

Прогноз на 2015 год

7) Аналогичным способом вводим уравнение на все 5 лет прогноза, не забывая заменять адрес ячейки года. У меня это выглядит вот так (уравнение справа поставила для вас).

Прогноз на 5 лет

Теперь добавим полученные точки прогноза на график. Для этого нужно добавить ряды — щелкнуть по области графика правой кнопкой мыши/Выбрать данные/Добавить. Дать название ряда «Прогноз на 2015 год». Ввести значения х и y: «х» — обозначение года 2015, в моем случае он заменен цифрой «13» (ячейка «А18»), а «y» — полученное число прогноза на 2015 год в ячейке «В18».

Изменение ряда в эксел

9) Аналогичным образом вводим остальные четыре точки.

Добавление точек прогноза

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

Точки прогноза на 5 лет

Итог

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

Уравнение линии тренда в Excel

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

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

Линейная аппроксимация

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

Рассмотрим условное количество заключенных менеджером контрактов на протяжении 10 месяцев:

На основании данных в таблице Excel построим точечную диаграмму (она поможет проиллюстрировать линейный тип):

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

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

y = 4,503x + 6,1333

  • где 4,503 – показатель наклона;
  • 6,1333 – смещения;
  • y – последовательность значений,
  • х – номер периода.

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

Чтобы спрогнозировать количество заключенных контрактов, например, в 11 периоде, нужно подставить в уравнение число 11 вместо х. В ходе расчетов узнаем, что в 11 периоде этот менеджер заключит 55-56 контрактов.

Экспоненциальная линия тренда

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

Построим экспоненциальную линию тренда в Excel. Возьмем для примера условные значения полезного отпуска электроэнергии в регионе Х:

Строим график. Добавляем экспоненциальную линию.

Уравнение имеет следующий вид:

  • где 7,6403 и -0,084 – константы;
  • е – основание натурального логарифма.

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

Логарифмическая линия тренда в Excel

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

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

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

R2 близок по значению к 1 (0,9633), что указывает на минимальную ошибку аппроксимации. Спрогнозируем объемы продаж в последующие периоды. Для этого нужно в уравнение вместо х подставлять номер периода.

Период 14 15 16 17 18 19 20
Прогноз 1005,4 1024,18 1041,74 1058,24 1073,8 1088,51 1102,47

Для расчета прогнозных цифр использовалась формула вида: =272,14*LN(B18)+287,21. Где В18 – номер периода.

Полиномиальная линия тренда в Excel

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

Полиномиальный тренд в Excel применяется для анализа большого набора данных о нестабильной величине. Посмотрим на примере первого набора значений (цены на нефть).

Чтобы получить такую величину достоверности аппроксимации (0,9256), пришлось поставить 6 степень.

Скачать примеры графиков с линией тренда

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

Приветствую, уважаемые товарищи! Сегодня мы с вами разберем один из субъективных торговых методов – торговля с использованием трендовых линий. Давайте рассмотрим следующие вопросы:

1) Что такое тренд (это важно как отправная точка)
2) Построение трендовых линий
3) Использование в практической торговле
4) Субъективность метода

1) Что такое тренд
_________________
Прежде, чем перейти к построению трендовой линии, надо разобраться непосредственно с самим трендом. Не будем вдаваться в академические споры и для простоты примем следующую формулу:

Тренд (восходящий) – это последовательность растущих максимумов и минимумов, при этом каждый последующий максимум (и минимум) выше предыдущих.

Тренд (нисходящий) – это последовательность падающих (убывающих) максимумов и минимумов, где каждый последующий минимум (и максимум) НИЖЕ предыдущего.

Трендовая линия – это линия, проведенная между двумя максимумами (если тренд нисходящий) или двумя минимумами (если тренд восходящий). То есть, по сути, линия тренда показывает нам, что тренд на графике есть! А ведь его может и не быть (в случае с флетом).

2) Построение трендовых линий
____________________________

Это самый сложный вопрос! Мне доводилось видеть дискуссии на много страниц только о том, КАК ПРАВИЛЬНО строить линию тренда! А ведь нам надо не только строить, но и торговать по ней…

Что бы построить трендовую линию надо иметь, как минимум, два максимума (нисходящий тренд) или два минимума (восходящий тренд). Мы должны соединить эти экстремумы линией.

Важно соблюдать следующие правила при построении линий:

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

Читайте также:  Примеры функции ABS в Excel для пересчета значения по модулю

Данные правила помогут вам правильно строить трендовые линии!

3) Торговля по трендовым линиям
____________________________

Мы имеем две принципиально разные возможности:
А) Использовать линию как уровень поддержки (сопротивления), что бы войти по ней по направлению тренда
Б) Использовать трендовую линию Форекс для того, что бы сыграть на пробой (разворот) тренда.

Оба способа хороши, если уметь «правильно их готовить».

Итак, мы построили линию по двум точкам. Как только цена коснется линии, мы должны войти в рынок по направлению существующей тенденции. Для входа используем ордера типа «бай лимит или sell лимит».

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

Если мы хотим сыграть на слом линии тренда, то надо действовать немного иначе:
1) Ждем касание линии
2) Ждем отскока
3) На образовавшуюся галочку ставим ордер бай-стоп (или sell стоп)
Обратите внимание на рисунок.


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

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

4) Субъективность метода
_________________________

Кажется все просто? На деле, используя данный метод, мы столкнемся со следующими трудностями:
А) Угол наклона линии (всегда можно построить линии тренда имеющие разный наклон.
Б) Что считать пробоем трендовой линии (насколько пунктов или процентов цена должна «переломить» линию, что бы считать это прорывом)?
В) Когда линию считать «устаревшей» и строить новую?

Обратите внимание на рисунок.


Красной линией обозначен один из вариантов начертания. Неопытный трейдер мог так провести линию (и поплатиться за это).

В данном деле важен практический опыт. То есть не удается все свести к нескольким простым правилам построения. Именно поэтому индикатора трендовых линий не существует. Точнее, может и существует, но строит их «криво» и неправильно. Эта техника изначально «заточена» под опыт и мастерство трейдера.

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

Нужен ли данный инструмент в вашей торговле – решать только вам!

Успехов и удачных торгов. Артур.
blog-forex.org

Концепция трендовой торговли (видео)

Трендовые модели (фигуры)

Видеоролик к данной теме:

Отображение ряда на диаграмме с помощью линии тренда

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

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

На вкладке Конструктор нажмите кнопку Добавить элемент диаграммы и выберите пункт Линия тренда.

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

Выберите нужный тип линии тренда или скользящего среднего.

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

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

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

В меню Диаграмма выберите команду Добавить линию тренда, а затем — пункт Тип.

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

Выберите нужный тип линии тренда или скользящего среднего.

Выполните одно из указанных ниже действий.

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

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

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

Отформатируйте ваши линии тренда

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

Откройте панель «Форматировать линию тренда», дважды щелкнув линию тренда, которую вы хотите отформатировать, или щелкнув правой кнопкой мыши и выбрав «Форматировать линию тренда».

Выбрать

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

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

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

Стоит ли добиваться максимального значения R²

Улучшить достоверность апроксимации можно меняя вид кривой. Это можно сделать в открывающемся справа окошке Формат линии тренда.

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

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

Возможности инструмента

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

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

Параметры линии тренда можно условно поделить на четыре блока:

  1. Тип приближения.
  2. Название полученной кривой, которое формируется автоматически или может быть задано пользователем.
  3. Блок прогнозирования, который позволяет продлить линию тренда на заданное количество периодов вперед или назад, на основании имеющихся данных. Что позволяет оценить дальнейшее изменение исследуемой величины.
  4. Дополнительные опции, которые отражают математическую составляющую кривой. Самой интересной и полезной строчкой здесь является величина достоверности. Если значение коэффициента близко к единице, то ошибка минимальна и дальнейший прогноз будет достаточно точным.

Выведем на исходный график уравнение линии и коэффициент достоверности.

Как видите, значение близко к 0,5, это говорит о низкой достоверности полученной линии тренда, и дальнейший прогноз будет ошибочным.

Прогнозирование, шаг 6.1: методы прогнозирования

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

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

Классификация методов прогнозирования.

Методы прогнозирования делятся на две группы или класса: интуитивные (субъективные или качественные) и формализованные (объективные или количественные). Интуитивные методы прогнозирования — это такие методы, основой которых НЕ являются сухие расчеты, математика и статистика. Они, в первую очередь, основаны на оценках группы экспертов и предназначены для прогнозирования объемов новой позиции, у которой нет истории продаж. Либо для прогнозирования объемов позиции, история продаж которой настолько нестабильна, что невозможно подобрать под нее адекватную математическую модель. В пример можно привести такие методы, как «Метод Дельфи», «Мозговой штурм», «Опрос/анкетирование» и так далее, но в данной статье данные методы прогнозирования рассмотрены не будут.

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

  1. Линейная регрессия
  2. Полиномиальный прогноз
  3. Экспоненциальное сглаживание
  4. Модель Хольта
  5. Модель Хольта-Винтерса
  6. Модель Тейла-Вейджа

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

Методы прогнозирования: метод линейной регрессии.

Построение прогноза с помощью метода линейной регрессии — один из наиболее простых, часто-встречающихся и распространенных (если рассматривать Excel) методов прогнозирования. Часто встречается он как раз из-за того, что в Excel его очень легко применить — достаточно воспользоваться функцией ЛИНЕЙН, ПРЕДСКАЗ или ТЕНДЕНЦИЯ, где исходными данными будут являться номера периодов и соответствующие им объемы продаж.

Для нахождения прогноза на период x, мы воспользуемся уравнением y=k*x+b, где k — угловой коэффициент, который находится с помощью метода наименьших квадратов (на основании предыдущих периодов x и соответствующих значений y), а b — это точка, в которой наш график пересекается с осью y. Данное уравнение описывает линию, которая называется линия тренда, которая показывает динамику продаж и прогнозы на последующие периоды.

На гистограмме ниже изображены столбцы с объемами продаж для соответствующего периода (номера недель по оси X), пунктирная линия, которая как раз является линией тренда и столбец c предсказанным значением (соответствует «продолжению» линии тренда):

Методы прогнозирования: линейная регрессия

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

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

Хочу отметить, что в моем опыте работы данный метод прогнозирования в совокупности с корректным сглаживанием числового ряда, а также с корректными значениями планируемых промо-объемов, которые мне предоставлял отдел трейд-маркетинга, достигал достаточно высокой точности прогнозирования (выше 80%).

Методы прогнозирования: полиномиальный прогноз.

Построение прогноза с помощью полинома немного похоже на построение прогноза с помощью предыдущего метода. Здесь, для нахождения прогноза на последующие периоды вместо линейного уравнение, мы используем полином третьей степени вида y = a*x³+b*x²+c*x+d или полином второй степени вида y = a*x²+b*x+c, где коэффициенты a,b,c,d постоянны и находятся с помощью различных методов решения систем линейных уравнений, на основании предыдущих периодов x и соответствующим им объемов продаж y.

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

Методы прогнозирования: полиномиальный прогноз

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

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

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

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

Для нахождения прогноза на период t+1, используется следующая формула:

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

где yt — значение факта на период t, t — сглаженное значение на период t, а α — коэффициент или параметр сглаживания, который принимает значение 0<α<1.

Читайте также:  Функция ФИШЕР в Excel и примеры ее работы

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

Единого метода определения α не существует, но для его нахождения можно выбрать следующие варианты:

  1. В некоторых источниках рекомендуют использовать значение 0,2<α<0,3, поэтому просто предлагается оставить параметр сглаживания примерно в этих рамках.
  2. Использовать формулу α = 2/(n-1), где n — количество периодов для экспоненциального сглаживания. Например, грубо говоря, если n=8, то модель будет учитывать значения последних восьми периодов в числовом ряду, и α будет равен 0,25.

Также, возникает проблема нахождения 1: для его расчета необходимо предыдущее значение факта y на период t = 0, а у нас такого, естественно, нет. Поэтому, за 1 можно принять значение y1.

Гистограмма с линией экспоненциального сглаживания по которой строится прогноз выглядит так (α=0,4):

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

Данный метод неплохой, но он не учитывает тренд продукции и сезонные колебания (поэтому первоначально числовой ряд нужно очистить от сезонности, как и в линейной регрессии), то есть лучше всего подходит для стабильных числовых рядов. Плюс ко всему, как я уже говорил, подходит для построения прогноза только на 1 период вперед. Для числового ряда с учетом тренда можно использовать улучшенную модель экспоненциального сглаживания (метод Хольта), а если еще необходимо учесть сезонность, то можно использовать Метод Хольта-Винтерса или Метод Тейла-Вейджа, о которых я напишу далее.

Методы прогнозирования: модель на основе экспоненциального сглаживания с учетом тренда (Метод Хольта).

Данная модель — усовершенствованная версия модели экспоненциального сглаживания с учетом тренда продаж.

Формула нахождения прогноза на период t+d следующая:

Методы прогнозирования: Метод Хольта

где d — порядковый номер периода, на который мы делаем прогноз (то есть если числовой ряд состоит из 10 периодов, а прогноз мы делаем на 11, то d =1), а At и Bt — адаптивные переменные: A — экспоненциально-сглаженное фактическое значение , а B — значение тренда. Находятся данные переменные по следующим формулам:

Методы прогнозирования: переменные Хольта

И как в случае с обычным экспоненциальным сглаживанием, здесь тоже есть параметры, но их уже два: α1 и α2. И оба они принимают значения ∈(0,1). Подбирать эти параметры нужно так, чтобы прогнозы, построенные на уже имеющиеся периоды с фактическими значениями, с помощью полученной модели, давали наименьшую ошибку прогноза (во многих источниках рекомендуют использовать RMSE или MAPE). Напомню, для нахождения RMSE используется следующая формула:

Методы прогнозирования: RMSE

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

Методы прогнозирования: модель на основе экспоненциального сглаживания с учетом тренда и мультипликативной сезонности (Метод Хольта-Винтерса).

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

Формула расчета прогноза на период t+d следующая:

Методы прогнозирования: Метод Хольта-Винтерса

где d — порядковый номер прогнозируемого периода (как и в предыдущем примере), L — это количество периодов в одном цикле (для месячного прогнозирования — 12, для квартального — 4, для ежедневного — 7). At и Bt — адаптивные переменные: A — экспоненциально-сглаженное фактическое значение, а B — значение тренда. А C с большим индексом «t+(d mod L) — L» — значение сезонности в процентах. Индекс переменной C может немного смутить, но на самом деле все проще, чем кажется: просто используем C из прошлого цикла соответствующего периода (для прогноза на март используем коэффициент сезонности марта прошлого цикла).

Сами переменные A,B и C рассчитываются по следующим формулам:

Методы прогнозирования: Переменные Хольта-Винтерса

При использовании Метода Хольта-Винтерса рассчитывать коэффициенты сезонности перед построением не нужно, их расчет включен в формулу. Однако очищать числовой ряд от неадекватных значений все так же необходимо. Также, стоит отметить, что для корректной «работы» данной модели, история продаж должна содержать как минимум несколько циклов для корректного расчета сезонности (для месячного прогнозирования, необходима история продаж как минимум за 2-3 последних года).

Особенности расчета начальных переменных:

  • Для A1 можно использовать y1
  • Для B1 можно использовать 0
  • В первом цикле при расчете A мы должны ссылаться на C прошлых циклов, но так как их нет, вместо C можно использовать 1 (то есть, пренебречь сезонностью)
  • А для расчета сезонностей самого первого цикла(!) для каждого периода можно использовать формулу Ct=yt/At

Параметры α1,α2,α3 ∈(0,1), как и в предыдущей модели, подбираются путем минимизации ошибки прогноза (RMSE или MAPE).

Методы прогнозирования: модель на основе экспоненциального сглаживания с учетом тренда и аддитивной сезонности (Метод Тейла-Вейджа).

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

Формула расчета прогноза на период t+d следующая:

Методы прогнозирования: Метод Тейла-Вейджа

где d — порядковый номер прогнозируемого периода (как и в предыдущем примере), L — это количество периодов в одном цикле (для месячного прогнозирования — 12, для квартального — 4, для ежедневного — 7). At и Bt — адаптивные переменные: A — сглаженное фактическое значение (с помощью экспоненты), а B — значение тренда. А C с большим индексом «t+(d mod L) — L» — значение сезонности в процентах.

Сами переменные A,B и C рассчитываются по следующим формулам:

Методы прогнозирования: Переменные Тейла-Вейджа

Особенности расчета начальных переменных:

  • Для A1 можно использовать y1
  • Для B1 можно использовать 0
  • В первом цикле при расчете A мы должны ссылаться на C прошлых циклов, но так как их нет, вместо C можно использовать 0 (то есть пренебречь сезонностью)
  • А для расчета сезонностей самого первого цикла(!) для каждого периода можно использовать формулу Ct=yt-At

Параметры α1,α2,α3 ∈(0,1), как и в предыдущей модели, подбираются путем минимизации ошибки прогноза (RMSE или MAPE).

Методы прогнозирования: построение прогноза основанное на свойствах.

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

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

  • Диагональ экрана
  • Фирма-изготовитель
  • Разрешение экрана
  • Наличие Smart-TV
  • Возможность подключения USB
  • Частота экрана
  • Габариты
  • Возможность крепления на стену
  • Цвет
  • Частота обновления экрана
  • И так далее..

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

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

Методы прогнозирования: итоги и полезные ссылки.

Здесь были рассмотрены самые распространенные и наиболее простые способы/методы прогнозирования. Помимо них существует еще и другие, в том числе и более сложные, например:

  • Модели на основе авторегрессии и среднего скользящего (ARIMA или модель Бокса-Дженкинса, ARIMAX и SARIMA)
  • Модели на основе нейронных сетей (построение прогноза с помощью искусственного интеллекта)
  • Прогнозирование с помощью «бутстреппинга»
  • И другие модели/способы/методы прогнозирования

Возможно, спустя какое-то время, по некоторым из них будут написаны отдельные статьи (либо будет расширена текущая).

Файл с примерами расчетов в Excel, можно скачать нажав на кнопку ниже:

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

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

Функция ТЕНДЕНЦИЯ в Excel

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

Что такое метод наименьших квадратов?

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

Синтаксис

Ниже представлена ​​формула ТЕНДЕНЦИИ в Excel.

Формула ТЕНДЕНЦИИ в Excel

Аргументы

Для данного линейного уравнения y = m * x + c

Known_y’s: Это обязательный аргумент, который представляет набор значений y, которые у нас уже есть как существующие данные в наборе данных, который следует соотношению y = mx + c.

Известные_x: Это необязательный аргумент, представляющий набор значений x, длина которых должна быть равной длине набора known_y. Если этот аргумент опущен, набор known_x принимает значение (1, 2, 3… и так далее).

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

Const: Это необязательный аргумент, который указывает, равняется ли постоянное значение c 0. Если const имеет значение ИСТИНА или опущено, c вычисляется нормально. Если false, c принимается равным 0 (нулю), а значения m корректируются так, чтобы y = mx.

Как использовать функцию ТЕНДЕНЦИЯ в Excel?

Функция ТЕНДЕНЦИЯ в Excel очень проста и удобна в использовании. Давайте разберемся с работой функции ТЕНДЕНЦИЯ на некоторых примерах.

Пример # 1

В этом примере предположим, что у нас есть данные для результатов тестов с их средним баллом, теперь, используя эти данные, нам нужно сделать прогноз для среднего балла. У нас есть существующие данные в столбцах A и B, существующие значения GPA, соответствующие баллам, – это известные значения Y, а существующие значения баллов – это известные значения X. Мы дали некоторые значения для значений X как оценка, и нам нужно предсказать значения Y, которые являются средним баллом на основе существующих значений.

Существующие значения:

Функция тренда в Excel - пример 1

Заданные значения и значения Y, которые необходимо предсказать:

Функция тренда в Excel - пример 1-1

Чтобы предсказать значения GPA для заданных результатов тестов в ячейках D2, D3 и D4, мы будем использовать функцию ТЕНДЕНЦИЯ в Excel.

Формула ТЕНДЕНЦИЯ в excel будет принимать существующие значения известных X и Y, а мы передадим новые значения X для вычисления значений Y в ячейках E2, E3 и E4.

Формула ТЕНДЕНЦИИ в Excel будет следующей:

= ТЕНДЕНЦИЯ ($ A $ 2: $ A $ 16, $ B $ 2: $ B $ 16, D2)

Мы зафиксировали диапазон для известных значений X и Y, и передаем новое значение X в качестве опорного значения. Применяя ту же формулу ТЕНДЕНЦИИ в Excel к другим ячейкам, мы имеем

ТЕНДЕНЦИЯ Пример 1-2

Вывод:

ТЕНДЕНЦИЯ Пример 1-3

Итак, используя функцию ТЕНДЕНЦИЯ в Excel выше, мы предсказали три значения Y для заданных новых результатов тестов.

Пример # 2 – Прогнозирование роста продаж

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

Существующие данные содержат даты в столбце A и выручку от продаж в столбце B. Нам нужно рассчитать ориентировочную стоимость продаж на следующие 5 месяцев. Исторические данные приведены ниже:

Пример 2

Чтобы спрогнозировать продажи для данных предстоящих месяцев в следующем году, мы будем использовать функцию ТЕНДЕНЦИЯ в Excel, поскольку стоимость продаж увеличивается линейно, заданные известные значения Y – это доход от продаж, а известные значения X – даты окончания месяца, новые значения X – это даты следующих 3 месяцев, то есть 31.01.2018, 28.02.2018 и 31.03.2018, и нам нужно вычислить оценочные значения продаж на основе исторические данные приведены в диапазоне A1: B13.

Формула ТЕНДЕНЦИЯ в excel будет принимать существующие значения известных X и Y, а мы передадим новые значения X для вычисления значений Y в ячейках E2, E3 и E4.

Читайте также:  Динамическая карта в Excel из фигур и макросов VBA скачать

Формула ТЕНДЕНЦИИ в Excel будет следующей:

= ТЕНДЕНЦИЯ ($ B $ 2: $ B $ 13, $ A $ 2: $ A $ 13, D2)

Мы зафиксировали диапазон для известных значений X и Y, и передаем новое значение X в качестве опорного значения. Применяя ту же формулу ТЕНДЕНЦИИ в Excel к другим ячейкам, которые у нас есть,

Пример 2-1

Вывод:

Пример 2-2

Итак, используя приведенную выше функцию ТЕНДЕНЦИЯ, мы спрогнозировали оценочные значения продаж для данных предстоящих месяцев в ячейках D2, D3 и D4.

Пример функции ПРЕДСКАЗ в Excel

Сначала возьмем для примера условные цифры – значения x и y.

В свободную ячейку введем формулу: =ПРЕДСКАЗ(31;A2:A6;B2:B6). Функция находит значение y для заданного значения x = 31. Результат – 20,9063.

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

Сначала построим график по имеющимся данным.

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

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

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

Подставим значение x (11 месяц) в уравнение. Получим значение y (продажи для искомого месяца). Скопируем формулу до конца второго столбца.

Теперь для расчета будущих продаж воспользуемся функцией ПРЕДСКАЗ. Точка x, для которой необходимо рассчитать значение y, соответствует номеру месяца для прогнозирования (в нашем примере – ссылка на ячейку со значением 11 – А12). Формула: =ПРЕДСКАЗ(A12;$B$2:$B$11;$A$2:$A$11).

Абсолютные ссылки на диапазоны со значениями y и x делают их статичными (не позволяют изменяться, когда мы протягиваем формулу вниз).

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

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

Шаг 5

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

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

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

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

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

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

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

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

Прогнозируем

y = 4856*10 + 105104

Получаем 153664 продажи в следующем месяце. Если добавим новую точку на график, то сразу видим, что R^2 улучшился.

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

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

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

Прогнозирование по обычному графику невозможно, так как его коэффициент детерминированности (R^2) будет близок к нулю.

Именно поэтому применяются специальные функции.

Сейчас мы их построим, настроим и проанализируем.

Зачем нужна линия тренда

Линия тренда «по-простому», это непрерывная линия составленная на основе усредненных на основе специальных алгоритмов значений из которых строится наша диаграмма. Иными словами, если наши данные «прыгают» за три отчетных точки с «-5» на «0», а следом на «+5», в итоге мы получим почти ровную линию: «плюсы» ситуации очевидно уравновешивают «минусы».

Исходя из направления линии тренда гораздо проще увидеть реальное положение дел и видеть те самые тенденции, а следовательно — строить прогнозы на будущее. Ну а теперь, за дело!

Прогнозируем

y = 4856*10 + 105104

Получаем 153664 продажи в следующем месяце. Если добавим новую точку на график, то сразу видим, что R^2 улучшился.

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

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

Наиболее часто тренд представляется линейной зависимостью исследуемой величины вида

где y – исследуемая переменная (например, производительность) или зависимая переменная;
x – число, определяющее позицию (второй, третий и т.д.) года в периоде прогнозирования или независимая переменная.

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

где n – объем исследуемой совокупности (число единиц наблюдений).

Рис. 5.3. Построение тренда методом наименьших квадратов

Значения констант b и a или коэффициента при переменной Х и свободного члена уравнения определяются по формуле:

В табл. 5.1 приведен пример вычисления линейного тренда по данным .

Таблица 5.1. Вычисление линейного тренда

Функция прогнозирования 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. Вы также можете просмотреть наши другие предлагаемые статьи —

Ссылка на основную публикацию