Инструменты прогнозирования в Microsoft Excel

Преимущества прогнозирования

• Выбирайте оптимальный размер товарных запасов
  

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

• Всегда давайте обозначение столбцам. В первой строчке каждого столбца всегда давайте описание содержащихся в этом столбце данных.
• Разные данные, разные столбцы. Не помещайте в один столбец разнородные данные (например, издержки и объем продаж. Очень вероятно, что вы запутаетесь, и вычисления и работа с данными будут очень усложнены.
• Давайте каждому файлу понятное имя. Это не требует больших усилий, зато значительно ускоряет работу. Правильные имена позволяют быстро найти нужный файл визуально или через программу поиска файлов в Windows.
  

The usefulness of comments

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

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

Начало: простой пример прогнозирования с использованием линии тренда

Viewing your data

Создание линии тренда

Линейный тренд

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

Точки расположены хаотично: нет очевидной зависимости, ненадежные прогнозы

Точки "понятны" и позволяют получить более надежный прогноз

• После оценки диаграммы, вы можете использовать функцию КОРРЕЛ. В нашем примере функция будет иметь вид: КОРРЕЛ(A2:A83,B2:B83). Если результат близок к 0, тогда корреляция слабая, и вывод таков: реального тренда просто не существует. Если значение близко к 1, тогда корреляция сильная. Последнее очень помогает, так как это увеличивает силу объяснения выявленной вами закономерности.
  

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

Идем дальше: пример экспоненциальной зависимости

Идеальная экспоненциальная форма

Использование трендов

Установка Пакета аналитических инструментов

1. Убедитесь, что у вас есть установочный диск Office. Excel может запросить вставить диск для установки файлов Пакета.
2. Откройте таблицу Excel и в меню Инструменты выберите Дополнения. Отметьте первый пункт в окне под названием « Analysis ToolPack (Пакет аналитических инструментов) ».
3. Вставьте диск Office CD, если потребуется.
4. Готово! Обратите внимание, что в меню « Инструменты » теперь больше пунктов, в том числе опция « Анализ данных ». Это именно та, которой мы будем пользоваться больше всего.
   

Использование Пакета аналитических инструментов

... в случае линейной функции

Результат Пакета аналитических инструментов в случае регрессии метода наименьших квадратов

... используя экспоненциальную модель

Как узнать, какую модель выбрать?

Теоретическая справка

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

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

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

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

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

В Excel для построения регрессий имеются две возможности.

1. Добавление выбранных регрессий (линий тренда - trendlines) в диаграмму, построенную на основе таблицы данных для исследуемой характеристики процесса (доступно лишь при наличии построенной диаграммы);
2. Использование встроенных статистических функций рабочего листа Excel, позволяющих получать регрессии (линии тренда) непосредственно на основе таблицы исходных данных.

Добавление линий тренда в диаграмму

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

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

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

y = y(x)

где x - независимая переменная, которая часто принимает значения последовательности натурального ряда чисел (1; 2; 3; …) и производит, например, отсчет времени протекания исследуемого процесса (характеристики).

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

y = mx + b

где m - тангенс угла наклона линейной регрессии к оси абсцисс; b - координата точки пересечения линейной регрессии с осью ординат.

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

В этом случае линия тренда строится в соответствии с уравнением:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

где коэффициенты c0, c1, c2,... c6 - константы, значения которых определяются в ходе построения.

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

Строится в соответствии с уравнением:

y = c ln(x) + b

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

Строится в соответствии с уравнением:

y = c xb

где коэффициенты b, с - константы.

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

Строится в соответствии с уравнением:

y = c ebx

где коэффициенты b, с - константы.

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

Определяется по формуле:

Для добавления линии тренда к ряду данных следует:

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

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

После этого необходимо:

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

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

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

• изменить название линии тренда в поле Название аппроксимирующей (сглаженной) кривой.
• задать количество периодов (вперед или назад) для прогноза в поле Прогноз;
• вывести в область диаграммы уравнение линии тренда, для чего следует включить флажок показать уравнение на диаграмме;
• вывести в область диаграммы значение достоверности аппроксимации R2, для чего следует включить флажок поместить на диаграмму величину достоверности аппроксимации (R^2);
• задать точку пересечения линии тренда с осью Y, для чего следует включить флажок пересечение кривой с осью Y в точке;
• щелкнуть на кнопке OK, чтобы закрыть диалоговое окно.

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

На экране появится диалоговое окно Формат линии тренда (рис. 3), содержащее три вкладки: Вид, Тип, Параметры, причем содержимое последних двух полностью совпадает с аналогичными вкладками диалогового окна Линия тренда (рис.1-2). На вкладке Вид, можно задать тип линии, ее цвет и толщину.

Для удаления уже построенной линии тренда следует выбрать удаляемую линию тренда и нажать клавишу Delete.

Достоинствами рассмотренного инструмента регрессионного анализа являются:

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

К недостаткам можно отнести следующие моменты:

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

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

Использование встроенных функций Excel

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

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

• ТЕНДЕНЦИЯ;
• ЛИНЕЙН;
• НАКЛОН и ОТРЕЗОК.

А также несколько функций для построения экспоненциальной линии тренда, в частности:

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

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

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

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

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

Примеры решения конкретных задач

Рассмотрим решение конкретных задач с помощью перечисленных инструментов пакета Excel.

Задача 1

С таблицей данных о прибыли автотранспортного предприятия за 1995-2002 гг. необходимо выполнить следующие действия.

1. Построить диаграмму.
2. В диаграмму добавить линейную и полиномиальную (квадратичную и кубическую) линии тренда.
3. Используя уравнения линий тренда, получить табличные данные по прибыли предприятия для каждой линии тренда за 1995-2004 г.г.
4. Составить прогноз по прибыли предприятия на 2003 и 2004 гг.

Решение задачи

1. В диапазон ячеек A4:C11 рабочего листа Excel вводим рабочую таблицу, представленную на рис. 4.
2. Выделив диапазон ячеек В4:С11, строим диаграмму.
3. Активизируем построенную диаграмму и по описанной выше методике после выбора типа линии тренда в диалоговом окне Линия тренда (см. рис. 1) поочередно добавляем в диаграмму линейную, квадратичную и кубическую линии тренда. В этом же диалоговом окне открываем вкладку Параметры (см. рис. 2), в поле Название аппроксимирующей (сглаженной) кривой вводим наименование добавляемого тренда, а в поле Прогноз вперед на: периодов задаем значение 2, так как планируется сделать прогноз по прибыли на два года вперед. Для вывода в области диаграммы уравнения регрессии и значения достоверности аппроксимации R2 включаем флажки показывать уравнение на экране и поместить на диаграмму величину достоверности аппроксимации (R^2). Для лучшего визуального восприятия изменяем тип, цвет и толщину построенных линий тренда, для чего воспользуемся вкладкой Вид диалогового окна Формат линии тренда (см. рис. 3). Полученная диаграмма с добавленными линиями тренда представлена на рис. 5.
4. Для получения табличных данных по прибыли предприятия для каждой линии тренда за 1995-2004 гг. воспользуемся уравнениями линий тренда, представленными на рис. 5. Для этого в ячейки диапазона D3:F3 вводим текстовую информацию о типе выбранной линии тренда: Линейный тренд, Квадратичный тренд, Кубический тренд. Далее вводим в ячейку D4 формулу линейной регрессии и, используя маркер заполнения, копируем эту формулу c относительными ссылками в диапазон ячеек D5:D13. Следует отметить, что каждой ячейке с формулой линейной регрессии из диапазона ячеек D4:D13 в качестве аргумента стоит соответствующая ячейка из диапазона A4:A13. Аналогично для квадратичной регрессии заполняется диапазон ячеек E4:E13, а для кубической регрессии - диапазон ячеек F4:F13. Таким образом, составлен прогноз по прибыли предприятия на 2003 и 2004 гг. с помощью трех трендов. Полученная таблица значений представлена на рис. 6.

Задача 2

1. Построить диаграмму.
2. В диаграмму добавить логарифмическую, степенную и экспоненциальную линии тренда.
3. Вывести уравнения полученных линий тренда, а также величины достоверности аппроксимации R2 для каждой из них.
4. Используя уравнения линий тренда, получить табличные данные о прибыли предприятия для каждой линии тренда за 1995-2002 гг.
5. Составить прогноз о прибыли предприятия на 2003 и 2004 гг., используя эти линии тренда.

Решение задачи

Следуя методике, приведенной при решении задачи 1, получаем диаграмму с добавленными в нее логарифмической, степенной и экспоненциальной линиями тренда (рис. 7). Далее, используя полученные уравнения линий тренда, заполняем таблицу значений по прибыли предприятия, включая прогнозируемые значения на 2003 и 2004 гг. (рис. 8).

На рис. 5 и рис. видно, что модели с логарифмическим трендом, соответствует наименьшее значение достоверности аппроксимации

R2 = 0,8659

Наибольшие же значения R2 соответствуют моделям с полиномиальным трендом: квадратичным (R2 = 0,9263) и кубическим (R2 = 0,933).

Задача 3

С таблицей данных о прибыли автотранспортного предприятия за 1995-2002 гг., приведенной в задаче 1, необходимо выполнить следующие действия.

1. Получить ряды данных для линейной и экспоненциальной линии тренда с использованием функций ТЕНДЕНЦИЯ и РОСТ.
2. Используя функции ТЕНДЕНЦИЯ и РОСТ, составить прогноз о прибыли предприятия на 2003 и 2004 гг.
3. Для исходных данных и полученных рядов данных построить диаграмму.

Решение задачи

Воспользуемся рабочей таблицей задачи 1 (см. рис. 4). Начнем с функции ТЕНДЕНЦИЯ:

1. выделяем диапазон ячеек D4:D11, который следует заполнить значениями функции ТЕНДЕНЦИЯ, соответствующими известным данным о прибыли предприятия;
2. вызываем команду Функция из меню Вставка. В появившемся диалоговом окне Мастер функций выделяем функцию ТЕНДЕНЦИЯ из категории Статистические, после чего щелкаем по кнопке ОК. Эту же операцию можно осуществить нажатием кнопки (Вставка функции) стандартной панели инструментов.
3. В появившемся диалоговом окне Аргументы функции вводим в поле Известные_значения_y диапазон ячеек C4:C11; в поле Известные_значения_х - диапазон ячеек B4:B11;
4. чтобы вводимая формула стала формулой массива, используем комбинацию клавиш + + .

Введенная нами формула в строке формул будет иметь вид: ={ТЕНДЕНЦИЯ(C4:C11;B4:B11)}.

В результате диапазон ячеек D4:D11 заполняется соответствующими значениями функции ТЕНДЕНЦИЯ (рис. 9).

Для составления прогноза о прибыли предприятия на 2003 и 2004 гг. необходимо:

1. выделить диапазон ячеек D12:D13, куда будут заноситься значения, прогнозируемые функцией ТЕНДЕНЦИЯ.
2. вызвать функцию ТЕНДЕНЦИЯ и в появившемся диалоговом окне Аргументы функции ввести в поле Известные_значения_y - диапазон ячеек C4:C11; в поле Известные_значения_х - диапазон ячеек B4:B11; а в поле Новые_значения_х - диапазон ячеек B12:B13.
3. превратить эту формулу в формулу массива, используя комбинацию клавиш Ctrl + Shift + Enter.
4. Введенная формула будет иметь вид: ={ТЕНДЕНЦИЯ(C4:C11;B4:B11;B12:B13)}, а диапазон ячеек D12:D13 заполнится прогнозируемыми значениями функции ТЕНДЕНЦИЯ (см. рис. 9).

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

На рис.10 представлена таблица в режиме показа формул.

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

Задача 4

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

1. Получить ряды данных для линейной регрессии: используя функции НАКЛОН и ОТРЕЗОК; используя функцию ЛИНЕЙН.
2. Получить ряд данных для экспоненциальной регрессии с использованием функции ЛГРФПРИБЛ.
3. Используя вышеназванные функции, составить прогноз о поступлении заявок в диспетчерскую службу на период с 12 по 14 число текущего месяца.
4. Для исходных и полученных рядов данных построить диаграмму.

Решение задачи

Отметим, что, в отличие от функций ТЕНДЕНЦИЯ и РОСТ, ни одна из перечисленных выше функций (НАКЛОН, ОТРЕЗОК, ЛИНЕЙН, ЛГРФПРИБ) не является регрессией. Эти функции играют лишь вспомогательную роль, определяя необходимые параметры регрессии.

Для линейной и экспоненциальной регрессий, построенных с помощью функций НАКЛОН, ОТРЕЗОК, ЛИНЕЙН, ЛГРФПРИБ, внешний вид их уравнений всегда известен, в отличие от линейной и экспоненциальной регрессий, соответствующих функциям ТЕНДЕНЦИЯ и РОСТ.

1 . Построим линейную регрессию, имеющую уравнение:

y = mx+b

с помощью функций НАКЛОН и ОТРЕЗОК, причем угловой коэффициент регрессии m определяется функцией НАКЛОН, а свободный член b - функцией ОТРЕЗОК.

Для этого осуществляем следующие действия:

1. заносим исходную таблицу в диапазон ячеек A4:B14;
2. значение параметра m будет определяться в ячейке С19. Выбираем из категории Статистические функцию Наклон; заносим диапазон ячеек B4:B14 в поле известные_значения_y и диапазон ячеек А4:А14 в поле известные_значения_х. В ячейку С19 будет введена формула: =НАКЛОН(B4:B14;A4:A14);
3. по аналогичной методике определяется значение параметра b в ячейке D19. И ее содержимое будет иметь вид: =ОТРЕЗОК(B4:B14;A4:A14). Таким образом, необходимые для построения линейной регрессии значения параметров m и b будут сохраняться соответственно в ячейках C19, D19;
4. далее заносим в ячейку С4 формулу линейной регрессии в виде: =$C*A4+$D. В этой формуле ячейки С19 и D19 записаны с абсолютными ссылками (адрес ячейки не должен меняться при возможном копировании). Знак абсолютной ссылки $ можно набить либо с клавиатуры, либо с помощью клавиши F4, предварительно установив курсор на адресе ячейки. Воспользовавшись маркером заполнения, копируем эту формулу в диапазон ячеек С4:С17. Получаем искомый ряд данных (рис. 12). В связи с тем, что количество заявок - целое число, следует установить на вкладке Число окна Формат ячеек числовой формат с числом десятичных знаков 0.

2 . Теперь построим линейную регрессию, заданную уравнением:

y = mx+b

с помощью функции ЛИНЕЙН.

Для этого:

1. вводим в диапазон ячеек C20:D20 функцию ЛИНЕЙН как формулу массива: ={ЛИНЕЙН(B4:B14;A4:A14)}. В результате получаем в ячейке C20 значение параметра m, а в ячейке D20 - значение параметра b;
2. вводим в ячейку D4 формулу: =$C*A4+$D;
3. копируем эту формулу с помощью маркера заполнения в диапазон ячеек D4:D17 и получаем искомый ряд данных.

3 . Строим экспоненциальную регрессию, имеющую уравнение:

y = bmx

с помощью функции ЛГРФПРИБЛ оно выполняется аналогично:

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

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

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

Рассмотрим, как добавить линию тренда на график в Excel.

Добавление линии тренда на график

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

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

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

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

• лепестковый;
• круговой;
• поверхностный;
• кольцевой;
• объемный;
• с накоплением.

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

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

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

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

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

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

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

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

Получаем результат:

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

y = 4,503x + 6,1333

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

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

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

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

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

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

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

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

y = 7,6403е^-0,084x

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

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

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

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

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

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

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

Например:

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

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

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

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

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

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

• лепестковый;
• круговой;
• поверхностный;
• кольцевой;
• объемный;
• с накоплением.

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

Получаем результат:

y = 4,503x + 6,1333

• 6,1333 – смещения;
• х – номер периода.

y = 7,6403е^-0,084x

Например:

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

Линия тренда в Excel

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

Построение графика

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

1. Строим таблицу, где в одном столбике будут располагаться временные отрезки (в нашем случае даты), а в другом – величина, динамика которой будет отображаться в графике.
2. Выделяем данную таблицу. Переходим во вкладку «Вставка». Там на ленте в блоке инструментов «Диаграммы» кликаем по кнопке «График». Из представленного списка выбираем самый первый вариант.
3. После этого график будет построен, но его нужно ещё доработать. Делаем заголовок графика. Для этого кликаем по нему. В появившейся группе вкладок «Работа с диаграммами» переходим во вкладку «Макет». В ней кликаем по кнопке «Название диаграммы». В открывшемся списке выбираем пункт «Над диаграммой».
4. В появившееся поле над графиком вписываем то название, которое считаем подходящим.
5. Затем подписываем оси. В той же вкладке «Макет» кликаем по кнопке на ленте «Названия осей». Последовательно переходим по пунктам «Название основной горизонтальной оси» и «Название под осью».
6. В появившемся поле вписываем название горизонтальной оси, согласно контексту расположенных на ней данных.
7. Для того, чтобы присвоить наименование вертикальной оси также используем вкладку «Макет». Кликаем по кнопке «Название осей». Последовательно перемещаемся по пунктам всплывающего меню «Название основной вертикальной оси» и «Повернутое название». Именно такой тип расположения наименования оси будет наиболее удобен для нашего вида диаграмм.
8. В появившемся поле наименования вертикальной оси вписываем нужное название.

Урок: Как сделать график в Excel

Создание линии тренда

Теперь нужно непосредственно добавить линию тренда.

1. Находясь во вкладке «Макет» кликаем по кнопке «Линия тренда», которая расположена в блоке инструментов «Анализ». Из открывшегося списка выбираем пункт «Экспоненциальное приближение» или «Линейное приближение».
2. После этого, линия тренда добавляется на график. По умолчанию она имеет черный цвет.

Настройка линии тренда

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

1. Последовательно переходим во вкладке «Макет» по пунктам меню «Анализ», «Линия тренда» и «Дополнительные параметры линии тренда…».
2. Открывается окно параметров, можно произвести различные настройки. Например, можно выполнить изменение типа сглаживания и аппроксимации, выбрав один из шести пунктов:
   • Полиномиальная;
   • Линейная;
   • Степенная;
   • Логарифмическая;
   • Экспоненциальная;
   • Линейная фильтрация.

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

   Если данный показатель равен 1, то модель максимально достоверна. Чем дальше уровень от единицы, тем меньше достоверность.

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

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

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

1. Опять переходим в параметры. В блоке настроек «Прогноз» в соответствующих полях указываем насколько периодов вперед или назад нужно продолжить линию тренда для прогнозирования. Жмем на кнопку «Закрыть».
2. Опять переходим к графику. В нем видно, что линия удлинена. Теперь по ней можно определить, какой приблизительный показатель прогнозируется на определенную дату при сохранении текущей тенденции.

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

Мы рады, что смогли помочь Вам в решении проблемы.

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

Помогла ли вам эта статья?

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

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

Как диаграммы и графики нас обманывают

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

Проиллюстрирую свои слова простейшим примером:

Диаграмма построенная на основе таблицы в MS Excel

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

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

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

Наглядна диаграмма? Вполне. А вот очевидна ли она? Давайте разберемся.

Разбираемся с трендами в MS Excel

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

Что же мы видим на графике? Оранжевые столбики «осени» как минимум ни чем не больше «весенних», а то и меньше. Это свидетельствует не об успехе, а скорее наоборот - посетители прибывают, но читают в среднем меньше и на сайте не задерживаются!

Самое время бить тревогу и… знакомится с такой штукой как линия тренда .

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

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

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

Как построить линию тренда в MS Excel

Добавляем к диаграмме в MS Excel линию тренда

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

На листе диаграммы теперь отображается пунктирная линия тренда. Как видите, она не совпадает на 100% со значениями диаграммы - построенная по средневзвешенным значениям, она лишь в общих чертах повторяет её направление. Однако это не мешает нам видеть устойчивый рост числа посещений сайта - на общем результате не сказывается даже «летняя» просадка.

Линия тренда для столбца «Посетители»

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

Ещё одна линия тренда позволяет прояснить ситуацию

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

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

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

Линия тренда в Excel. Процесс построения

Линия тренда - это один из основных инструментов анализа данных

Чтобы сформировать линию тренда, необхдимо совершить три этапа, а именно:
1. Создать таблицу;
2. Построить диаграмму;
3. Выбрать тип линии тренда.

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

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

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

Различные вариации л инии тренда

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

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

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

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

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

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

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

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

Линия тренда в Excel. Настройка параметро в функциональной линии

Нажав на кнопку «Линия тренда», выбираем необходимое меню под названием «Дополнительные параметры». В появившемся окне следует нажать на «Формат линии тренда», а после поставить и отметку напротив значения «поместить на диаграмму величину достоверности аппроксимации R^2». После этого закрываем меню, нажав на соответственную кнопку. На самой же диаграмме появляется коэффициент R^2= 0,6442.

После этого отменяем вводимые изменения. Выделив график и нажав на вкладку «Макет», следом нажимаем на «Линию тренда» и наживаем на «Нет». Следом, перейдя в функцию «Формат линии тренда», нажимаем на полиноминальную линию и пытаемся добиться значения R^2= 0,8321, меняя степень.

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

(Visited 10 510 times, 27 visits today)

Линия тренда в Excel на разных графиках

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

Рассмотрим, как добавить линию тренда на график в Excel.

Добавление линии тренда на график

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

1. Построим на основе таблицы график. Выделим диапазон – перейдем на вкладку «Вставка». Из предложенных типов диаграмм выберем простой график. По горизонтали – год, по вертикали – цена.
2. Щелкаем правой кнопкой мыши по самому графику. Нажимаем «Добавить линию тренда».
3. Открывается окно для настройки параметров линии. Выберем линейный тип и поместим на график величину достоверности аппроксимации.
4. На графике появляется косая линия.

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

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

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

• лепестковый;
• круговой;
• поверхностный;
• кольцевой;
• объемный;
• с накоплением.

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

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

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

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

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

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

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

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

Получаем результат:

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

y = 4,503x + 6,1333

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

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

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

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

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

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

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

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

y = 7,6403е^-0,084x

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

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

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

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

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

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

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

Например:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Похожие записи:

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

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

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

Часть 10. Подбор формул по графику. Линия тренда

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

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

Процесс подбора эмпирической формулы P(x) для опытной зависимости F(x) называется аппроксимацией (сглаживанием). Для зависимостей с одним неизвестным в Excel используются графики, а для зависимостей со многими неизвестными – пары функций из группы Статистические ЛИНЕЙН и ТЕНДЕНЦИЯ, ЛГРФПРИБЛ и РОСТ.

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

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

Excel предоставляет 5 видов аппроксимирующих функций:

1. Линейная – y=cx+b . Это простейшая функция, отражающая рост и убывание данных с постоянной скоростью.

2. Полиномиальная – y=c0+c1x+c2x2+…+c6x6 . Функция описывает попеременно возрастающие и убывающие данные. Полином 2-ой степени может иметь один экстремум (min или max), 3-ей степени – до 2-х экстремумов, 4-ой степени – до 3-х и т.д.

3. Логарифмическая – y=c lnx+b . Эта функция описывает быстро возрастающие (убывающие) данные, которые затем стабилизируются.

4. Степенная – y=cxb , (х >0и y >0). Функция отражает данные с постоянно увеличивающейся (убывающей) скоростью роста.

5. Экспоненциальная – y=cebx , (e – основание натурального логарифма). Функция описывает быстро растущие (убывающие) данные, которые затем стабилизируются.

Для всех 5-ти видов функций используется аппроксимация данных по методу наименьших квадратов (см. справку по F1 «линия тренда»).

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

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

Приступим к решению . В первую очередь введите эти данные в Excel и постройте график, как на рис. 38. Как видно, график построен на основании диапазона B2:J2. Далее, щелкнув правой кнопкой мыши по графику, добавьте линию тренда, как показано на рис. 38.

Чтобы подписать ось Х соответствующими значениями рекламы (как на рис. 38), следует в ниспадающем меню (рис. 38) выбрать пункт И сходные данные . В открывшемся одноименном окне, в закладке Ряд , в поле П одписи оси Х , укажите диапазон ячеек, где записаны значения Х (здесь $B$1:$K$1):

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

После нажатия ОК Вы получите результат, как на рис. 40. Коэффициент детерминации R2= 0.9846, что является неплохой степенью близости. Для подтверждения правильности выбранной функции (поскольку других теоретических соображений нет) спрогнозируйте развитие продаж на 10 периодов вперед. Для этого щелкните правой кнопкой по линии тренда – измените формат – после этого в поле Прогноз: вперед на: установите 10 (рис.

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

Вычисление по полученной формуле =237,96*LN(6)+5,9606 в Excel дает значение 432 тыс. руб.

В Excel имеется функция ПРЕДСКАЗ(), которая вычисляет будущее значение Y по существующим парам значений X и Y значениям с использованием линейной регрессии. Функция Y по возможности должна быть линейной, т.е. описываться уравнением типа c+bx .

Функция предсказания для нашего примера запишется так: =ПРЕДСКАЗ(K1;B2:J2;B1:J1). Запишите – должно получится значение 643,6 тыс. руб.

Часть11. Контрольные задания

Предыдущая12345678910111213141516Следующая

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

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

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

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

Легкая версия построения

Процесс построения линии тренда состоит из трех этапов: ввод в excel исходных данных, построение графика, выбор линии тренда и ее параметров.

Начнем с ввода данных.

1. Создаем в Excel таблицу с исходными данными.

(Рисунок 1)

2. Выделяем ячейки B3:B17 и перейдя на закладку «Вставка» выбираем «График».

(Рисунок 2)

3. После того как график построен, можно добавить подписи и заголовок.

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

Затем перейдем на закладку "Конструктор" и выберем "Макет 1".

(Рисунок 3)

4. Переходим к построению линии тренда. Для этого снова выделяем график и переходим на закладку «Макет».

(Рисунок 4)

5. Нажимаем на кнопку «Линия тренда» и выбираем «линейное приближение» или «экспоненциальное приближение».

(Рисунок 5)

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

Это наш промежуточный результат.

(Рисунок 6)

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

Профессиональная версия: выбор линии тренда и настройка параметров

6. Нажимаем на кнопку «Линия тренда» и выбираем «Дополнительные параметры и линии тренда».

(Рисунок 7)

7. В окне «Формат линии тренда», мы ставим флажок напротив «поместить на диаграмму величину достоверности аппроксимации R^2 и нажимаем кнопку «закрыть».

Видим на диаграмме коэффициент R^2= 0,6442

(Рисунок 8)

8. Отменяем изменения. Выделяем график, нажимаем на закладку "Макет", кнопку "линия тренда" и выбираем "Нет".

9. Переходим в окно «Формат линии тренда», но уже для того, чтобы выбрать «Полиноминальную» линию тренда, меняем степень, добиваясь показателей коэффициента R^2= 0,8321

(Рисунок 9)

Прогноз

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

(Рисунок 10)

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

Вывод

И в заключение если Вам интересна формула по которой построен тренд, в коне «Формат линии тренда» поставьте флажок напротив «показать уравнение на диаграмме».

Теперь Вы знаете, как выполнить задание и построить линию тренда, даже в такой программе как excel 2010.

Задавайте вопросы, не стесняйтесь.

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

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

Базовые понятия

Думаю, еще со школы все знакомы с линейной функцией, она как раз и лежит в основе тренда:

Y(t) = a0 + a1*t + E

Y — это объем продаж, та переменная, которую мы будем объяснять временем и от которого она зависит, то есть Y(t);

t — номер периода (порядковый номер месяца), который объясняет план продаж Y;

a0 — это нулевой коэффициент регрессии, который показывает значение Y(t), при отсутствии влияния объясняющего фактора (t=0);

a1 — коэффициент регрессии, который показывает, на сколько исследуемый показатель продаж Y зависит от влияющего фактора t;

E — случайные возмущения, которые отражают влияния других неучтенных в модели факторов, кроме времени t.

Построение модели

Итак, мы знаем объем продаж за прошедшие 9 месяцев. Вот, что из себя представляет наша табличка:

Следующее, что мы должны сделать — это определить коэффициенты a0 и a1 для прогнозирования объема продаж за 10-ый месяц.

Определение коэффициентов модели

Строим график. По горизонтали видим отложенные месяцы, по вертикали объем продаж:

В Google Sheets выбираем Редактор диаграмм -> Дополнительные и ставим галочку возле Линии тренда . В настройках выбираем Ярлык — Уравнение и Показать R^2 .

Если вы делаете все в MS Excel, то правой кнопкой мыши кликаем на график и в выпадающем меню выбираем «Добавить линию тренда».

По умолчанию строится линейная функция. Справа выбираем «Показывать уравнение на диаграмме» и «Величину достоверности аппроксимации R^2».

Вот, что получилось:

На графике мы видим уравнение функции:

y = 4856*x + 105104

Она описывает объем продаж в зависимости от номера месяца, на который мы хотим эти продажи спрогнозировать. Рядом видим коэффициент детерминации R^2, который говорит о качестве модели и на сколько хорошо она описывает наши продажи (Y). Чем ближе к 1, тем лучше.

У меня R^2 = 0,75. Это средний показатель, он говорит о том, что в модели не учтены какие-то другие значимые факторы помимо времени t, например, это может быть сезонность.

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

y = 4856*10 + 105104

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

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

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

Инструменты прогнозирования в Microsoft Excel

​Смотрите также​ примера.​ известные_значения_x, не должна​ прогнозов были более​ скачать данный пример:​Рассчитаем прогноз по продажам​Диапазон временной шкалы​Лист прогноза​ имеющихся данных. Функции​ или стабилизацию) продемонстрирует​(вкладка​ серии научных экспериментов,​ линейного приближения, в​ на монитор в​ того, у​ прогноз прибыли на​.​Прогнозирование – это очень​На график, отображающий фактические​ равняться 0 (нулю),​ точными.​Функция ПРЕДСКАЗ в Excel​

Процедура прогнозирования

​.​ ЛИНЕЙН и ЛГРФПРИБЛ​ предполагаемую тенденцию на​Главная​ можно использовать Microsoft​ 2019 году составит​ указанной ранее ячейке.​

Способ 1: линия тренда

​ТЕНДЕНЦИЯ​ 2018 год.​Линия тренда построена и​ важный элемент практически​ объемы реализации продукции,​

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

1. ​ возвращают различные данные​ ближайшие месяцы.​, группа​ Office Excel для​ 4614,9 тыс. рублей.​ Как видим, на​имеется дополнительный аргумент​Выделяем незаполненную ячейку на​​ по ней мы​​ любой сферы деятельности,​ добавим линию тренда​ вернет код ошибки​ с помощью функции​​ степенью точности предсказать​​ продажи за 12​ шкалы. Этот диапазон​Создание листа прогноза​ регрессионного анализа, включая​Эта процедура предполагает, что​Редактирование​ автоматической генерации будущих​Последний инструмент, который мы​ этот раз результат​«Константа»​ листе, куда планируется​ можем определить примерную​ начиная от экономики​

   

2. ​ (правая кнопка по​ #ДЕЛ/0!.​ ПРЕДСКАЗ следующим способом:​ будущие значения на​ месяцев предыдущего года​ должен соответствовать параметру​выберите график или​ наклон и точку​​ диаграмма, основанная на​​, кнопка​

   

3. ​ значений, которые будут​ рассмотрим, будет​ составляет 4682,1 тыс.​, но он не​ выводить результат обработки.​
   • ​ величину прибыли через​​ и заканчивая инженерией.​
   • ​ графику – «Добавить​​Рассматриваемая функция игнорирует ячейки​
   • ​Как видно, в качестве​​ основе существующих числовых​
   • ​ и построим прогноз​​Диапазон значений​
   • ​ гистограмму для визуального​​ пересечения линии с​
   • ​ существующих данных, уже​​Заполнить​

   ​ базироваться на существующих​ЛГРФПРИБЛ​

   ​ рублей. Отличия от​​ является обязательным и​​ Жмем на кнопку​​ три года. Как​​ Существует большое количество​​ линию тренда»).​​ с нечисловыми данными,​ первого аргумента представлен​ значений, и возвращает​ на 3 месяца​.​ представления прогноза.​​ осью.​​ создана. Если это​​).​ данных или для​​. Этот оператор производит​ результатов обработки данных​ используется только при​«Вставить функцию»​ видим, к тому​​ программного обеспечения, специализирующегося​​Настраиваем параметры линии тренда:​

   

4. ​ содержащиеся в диапазонах,​ массив натуральных логарифмов​ соответствующие величины. Например,​ следующего года с​Диапазон значений​В поле​Следующая таблица содержит ссылки​ еще не сделано,​С помощью команды​​ автоматического вычисления экстраполированных​​ расчеты на основе​ оператором​ наличии постоянных факторов.​.​ времени она должна​​ именно на этом​​Выбираем полиномиальный тренд, что​​ которые переданы в​​ последующих номеров дней.​ некоторый объект характеризуется​ помощью линейного тренда.​Здесь можно изменить диапазон,​Завершение прогноза​​ на дополнительные сведения​​ просмотрите раздел Создание​Прогрессия​​ значений, базирующихся на​​ метода экспоненциального приближения.​ТЕНДЕНЦИЯ​

   

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

   

   ​выберите дату окончания,​ об этих функциях.​ диаграмм.​можно вручную управлять​ вычислениях по линейной​ Его синтаксис имеет​незначительны, но они​ используется при наличии​Мастер функций​ тыс. рублей. Коэффициент​ далеко не все​ прогнозной модели.​ третьего аргументов.​ функцию логарифмического тренда,​ изменяется с течением​ для нашего прогноза​ значений. Этот диапазон​ а затем нажмите​Функция​Щелкните диаграмму.​ созданием линейной или​ или экспоненциальной зависимости.​ следующую структуру:​

​ имеются. Это связано​​ линейной зависимости функции.​. В категории​

Способ 2: оператор ПРЕДСКАЗ

​R2​ пользователи знают, что​R2 = 0,9567, что​​Функция ПРЕДСКАЗ была заменена​​ которая записывается как​ времени. Такие изменения​ 1 период (y).​ должен совпадать со​

​Описание​​Выберите ряд данных, к​ экспоненциальной зависимости, а​В Microsoft Excel можно​= ЛГРФПРИБЛ (Известные значения_y;известные​ с тем, что​Посмотрим, как этот инструмент​«Статистические»​, как уже было​

​ обычный табличный процессор​​ означает: данное отношение​ функцией ПРЕДСКАЗ.ЛИНЕЙН в​ y=aln(x)+b.​ могут быть зафиксированы​Уравнение линейного тренда:​ значением параметра​

​Создать​​ПРЕДСКАЗ​ которому нужно добавить​ также вводить значения​ заполнить ячейки рядом​ значения_x; новые_значения_x;[конст];[статистика])​ данные инструменты применяют​ будет работать все​выделяем наименование​ сказано выше, отображает​

​ Excel имеет в​ объясняет 95,67% изменений​ Excel версии 2016,​Результат расчетов:​ опытным путем, в​y = bx​Диапазон временной шкалы​.​Прогнозирование значений​

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

​Как видим, все аргументы​ разные методы расчета:​​ с тем же​​«ПРЕДСКАЗ»​ качество линии тренда.​ своем арсенале инструменты​ объемов продаж с​ но была оставлена​Для сравнения, произведем расчет​

1. ​ результате чего будет​ + a​.​В Excel будет создан​​тенденция​​ скользящее среднее.​

   

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

   

3. ​ с использованием функции​ составлена таблица известных​​y — объемы продаж;​​Заполнить отсутствующие точки с​ новый лист с​Прогнозирование линейной зависимости.​На вкладке​ к начальным значениям​ тренду, с помощью​​ элементы предыдущей функции.​​ и метод экспоненциальной​ сравнить полученные результаты,​ по кнопке​ величина​​ которые по своей​​Уравнение тренда – это​ с Excel 2013​ линейного тренда:​ значений x и​x — номер периода;​ помощью​

   ​ таблицей, содержащей статистические​​РОСТ​​Макет​​ применяется метод наименьших​​ маркер заполнения или​ Алгоритм расчета прогноза​ зависимости.​ точкой прогнозирования определим​«OK»​R2​ эффективности мало чем​

   ​ модель формулы для​​ и более старыми​​И для визуального сравнительного​​ соответствующих им значений​​a — точка пересечения​Для обработки отсутствующих точек​

   ​ и предсказанные значения,​Прогнозирование экспоненциальной зависимости.​в группе​​ квадратов (y=mx+b).​​ команды​

   

4. ​ немного изменится. Функция​Оператор​ 2019 год.​.​составляет​ уступают профессиональным программам.​ расчета прогнозных значений.​ версиями.​ анализа построим простой​ y, где x​ с осью y​ Excel использует интерполяцию.​ и диаграммой, на​

   

5. ​линейн​Анализ​Для получения экспоненциального тренда​Прогрессия​ рассчитает экспоненциальный тренд,​ЛИНЕЙН​Производим обозначение ячейки для​Запускается окно аргументов. В​0,89​ Давайте выясним, что​

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

​ к начальным значениям​​. Для экстраполяции сложных​

Способ 3: оператор ТЕНДЕНЦИЯ

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

​лгрфприбл​​Линия тренда​​ применяется алгоритм расчета​​ и нелинейных данных​​ сколько раз поменяется​ метод линейного приближения.​​ запускаем​​«X»​​ тем выше достоверность​​ и как сделать​​ линейную линию тренда.​​ основании известного значения​Как видно, функцию линейной​​ – количественная характеристика​​b — увеличение последующих​​ как взвешенное среднее​​ слева от листа,​Построение экспоненциального приближения.​и выберите нужный​ экспоненциальной кривой (y=b*m^x).​

​ можно применять функции​ сумма выручки за​ Его не стоит​

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

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

   

2. ​Скачать последнюю версию​​ увидеть прогноз, в​​ ПРЕДСКАЗ используется как​​ в тех случаях,​​ функции ПРЕДСКАЗ можно​Допустим у нас имеются​ отсутствует менее 30 %​​ ввели ряды данных​​ сложный регрессионный анализ —​​ тренда или скользящего​​ учитывается шаг прогрессии.​​ анализ из надстройки​​ есть, за год.​​ линейной зависимости, используемым​​ категории​ отыскать значение функции.​ равной​ Excel​ параметрах необходимо установить​ обычная формула. Если​ когда наблюдается постоянный​​ предположить последующие значения​​ следующие статистические данные​ точек. Чтобы вместо​​ (то есть перед​​ включая вычисление и​

   

3. ​ среднего.​ При создании этих​ "Пакет анализа".​ Нам нужно будет​ инструментом​«Статистические»​ В нашем случаем​1​Целью любого прогнозирования является​ количество периодов.​

Способ 4: оператор РОСТ

​ требуется предсказать сразу​ рост какой-либо величины.​ y для новых​ по продажам за​ этого заполнять отсутствующие​ ним).​ отображение остатков — можно​Для определения параметров и​ прогрессий получаются те​В арифметической прогрессии шаг​ найти разницу в​ТЕНДЕНЦИЯ​находим и выделяем​ это 2018 год.​

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

1. ​ прибыли между последним​. Его синтаксис имеет​ наименование​​ Поэтому вносим запись​​ при коэффициенте свыше​ и определение предполагаемого​​В нашем примере все-таки​​ качестве первого аргумента​ функция логарифмического тренда​​Функция ПРЕДСКАЗ использует метод​​Рассчитаем значение линейного тренда.​

   

2. ​ в списке пункт​ дополнительные параметры прогноза,​ анализа в надстройке​ тренда или скользящего​ вычисляются с помощью​ начальным и следующим​ фактическим периодом и​ такой вид:​​«ТЕНДЕНЦИЯ»​​«2018»​0,85​ результата в отношении​​ экспоненциальная зависимость. Поэтому​​ следует передать массив​

   

3. ​ позволяет получить более​ линейной регрессии, а​ Определим коэффициенты уравнения​Нули​ нажмите кнопку​ "Пакет анализа". Дополнительные​ среднего щелкните линию​ функций ТЕНДЕНЦИЯ и​ значением в ряде​​ первым плановым, умножить​​=ЛИНЕЙН(Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])​. Жмем на кнопку​. Но лучше указать​линия тренда является​ изучаемого объекта на​ при построении линейного​ или ссылку на​ правдоподобные данные (более​

Способ 5: оператор ЛИНЕЙН

​ ее уравнение имеет​​ y = bx​​.​Параметры​ сведения см. в​ тренда правой клавишей​ РОСТ.​ добавляется к каждому​​ её на число​​Последние два аргумента являются​«OK»​

​ достоверной.​ определенный момент времени​ тренда больше ошибок​ диапазон ячеек со​ наглядно при большем​ вид y=ax+b, где:​ + a. В​Объединить дубликаты с помощью​.​ статье Загрузка пакета​ мыши и выберите​Для заполнения значений вручную​ следующему члену прогрессии.​ плановых периодов​ необязательными. С первыми​.​ ячейке на листе,​Если же вас не​ в будущем.​ и неточностей.​ значениями независимой переменной,​ количестве данных).​​Коэффициент a рассчитывается как​​ ячейке D15 Используем​Если данные содержат несколько​

1. ​Вы найдете сведения о​ статистического анализа.​ пункт​ выполните следующие действия.​Начальное значение​​(3)​​ же двумя мы​​Открывается окно аргументов оператора​​ а в поле​ устраивает уровень достоверности,​​Одним из самых популярных​​Для прогнозирования экспоненциальной зависимости​

   

2. ​ а функцию ПРЕДСКАЗ​​Пример 3. В таблице​​ Yср.-bXср. (Yср. и​ функцию ЛИНЕЙН:​​ значений с одной​​ каждом из параметров​​Примечание:​​Формат линии тренда​​Выделите ячейку, в которой​​Продолжение ряда (арифметическая прогрессия)​и прибавить к​ знакомы по предыдущим​​ТЕНДЕНЦИЯ​​«X»​

   

3. ​ то можно вернуться​ видов графического прогнозирования​ в Excel можно​

   

4. ​ использовать в качестве​ Excel указаны значения​ Xср. – среднее​Выделяем ячейку с формулой​​ меткой времени, Excel​​ в приведенной ниже​ Мы стараемся как можно​.​ находится первое значение​1, 2​ результату сумму последнего​ способам. Но вы,​. В поле​​просто дать ссылку​​ в окно формата​ в Экселе является​ использовать также функцию​ формулы массива.​ независимой и зависимой​​ арифметическое чисел из​​ D15 и соседнюю,​ находит их среднее.​ таблице.​ оперативнее обеспечивать вас​Выберите параметры линии тренда,​ создаваемой прогрессии.​3, 4, 5...​ фактического периода.​ наверное, заметили, что​«Известные значения y»​​ на него. Это​​ линии тренда и​ экстраполяция выполненная построением​​ РОСТ.​​Анализ временных рядов позволяет​

​ переменных. Некоторые значения​ выборок известных значений​ правую, ячейку E15​ Чтобы использовать другой​Параметры прогноза​

Способ 6: оператор ЛГРФПРИБЛ

​ актуальными справочными материалами​ тип линий и​​Команда​​1, 3​В списке операторов Мастера​ в этой функции​уже описанным выше​ позволит в будущем​

​Для линейной зависимости –​ изучить показатели во​ зависимой переменной указаны​ y и x​ так чтобы активной​ метод вычисления, например​Описание​ на вашем языке.​ эффекты.​Прогрессия​5, 7, 9​ функций выделяем наименование​ отсутствует аргумент, указывающий​ способом заносим координаты​ автоматизировать вычисления и​ тип аппроксимации. Можно​Попробуем предсказать сумму прибыли​ ТЕНДЕНЦИЯ.​​ времени. Временной ряд​​ в виде отрицательных​ соответственно).​ оставалась D15. Нажимаем​

1. ​Медиана​Начало прогноза​​ Эта страница переведена​​При выборе типа​удаляет из ячеек​​100, 95​​«ЛГРФПРИБЛ»​

   

2. ​ на новые значения.​ колонки​ при надобности легко​ перепробовать все доступные​ предприятия через 3​​При составлении прогнозов нельзя​​ – это числовые​​ чисел. Спрогнозировать несколько​​Коэффициент b определяется по​

   

3. ​ кнопку F2. Затем​, выберите его в​Выбор даты для прогноза​

   

4. ​ автоматически, поэтому ее​​Полиномиальная​​ прежние данные, заменяя​90, 85​. Делаем щелчок по​ Дело в том,​«Прибыль предприятия»​ изменять год.​​ варианты, чтобы найти​​ года на основе​ использовать какой-то один​ значения статистического показателя,​ последующих значений зависимой​ формуле:​ Ctrl + Shift​ списке.​ для начала. При​ текст может содержать​​введите в поле​​ их новыми. Если​Для прогнозирования линейной зависимости​ кнопке​ что данный инструмент​. В поле​В поле​ наиболее точный.​​ данных по этому​​ метод: велика вероятность​

​ расположенные в хронологическом​ переменной, исключив из​Пример 1. В таблице​ + Enter (чтобы​Включить статистические данные прогноза​ выборе даты до​ неточности и грамматические​Степень​ необходимо сохранить прежние​

​ выполните следующие действия.​​«OK»​ определяет только изменение​

​«Известные значения x»​«Известные значения y»​Нужно заметить, что эффективным​ показателю за предыдущие​ больших отклонений и​ порядке.​ расчетов отрицательные числа.​ приведены данные о​ ввести массив функций​Установите этот флажок, если​ конца статистических данных​ ошибки. Для нас​наибольшую степень для​ данные, скопируйте их​Укажите не менее двух​.​ величины выручки за​вводим адрес столбца​указываем координаты столбца​ прогноз с помощью​ 12 лет.​ неточностей.​

​Подобные данные распространены в​

lumpics.ru

Прогнозирование значений в рядах

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

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

Автоматическое заполнение ряда на основе арифметической прогрессии

​. В поле​. Это можно сделать,​ тренда может быть,​ основе табличных данных,​ (хотя бы примерно!)​ человеческой деятельности: ежедневные​

​Если требуется повысить точность​ точно так, как​

1. ​ равен одному году,​«Новые значения x»​ установив курсор в​

   ​ если период прогнозирования​ состоящих из аргументов​ будущее развитие событий​

2. ​ цены акций, курсов​ отрицательных значений (-5,​ прогнозам специалистов, средняя​ коефициентов для (a)​

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

​ а вот общий​​заносим ссылку на​ поле, а затем,​ не превышает 30%​ и значений функции.​ - неотъемлемая и​​ валют, ежеквартальные, годовые​​ -20 и -35)​​ стоимость 1 л​​ и (b).​​ результате добавит таблицу​​Советы:​​ и сообщить, помогла​​Период​

Автоматическое заполнение ряда на основе геометрической прогрессии

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

​число периодов, используемых​Главная​

1. ​Перетащите маркер заполнения в​ЛИНЕЙН​ подсчитать отдельно, прибавив​

   ​ номер года, на​ мыши и выделив​ периодов. То есть,​

2. ​ табличную область, а​ любого современного бизнеса.​ и т.д. Типичный​0;B2:B11;0);ЕСЛИ(B2:B11>0;A2:A11;0))' class='formula'>​ месяце не превысит​ у-значение линейного тренда.​ помощью ПРОГНОЗА. ETS.​Запуск прогноза до последней​​ с помощью кнопок​​ для расчета скользящего​

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

​ затем, находясь во​​ Само-собой, это отдельная​ временной ряд в​C помощью функций ЕСЛИ​ 41,5 рубля. Спрогнозировать​ Для этого в​​ СТАТИСТИКА функциями, а​​ точке статистических дает​​ внизу страницы. Для​​ среднего.​​Правка​​ заполнить ячейки возрастающими​​«OK»​​ значению прибыли результат​

Ручное прогнозирование линейной или экспоненциальной зависимости

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

• ​ стоимость бензина на​ известное уравнение подставим​ также меры, например​ представление точности прогноза​

• ​ удобства также приводим​Примечания:​нажмите кнопку​ или убывающими значениями.​

​.​ вычисления оператора​ случае это 2019​Аналогичным образом в поле​ мы не можем​«Вставка»​ с кучей методов​ объем осадков.​

​ диапазона B2:B11 и​ оставшиеся дни месяца,​

1. ​ рассчитанные коэффициенты (х​ сглаживания коэффициенты (альфа,​ как можно сравнивать​

   ​ ссылку на оригинал​​ ​​Заполнить​Например, если ячейки C1:E1​Результат экспоненциального тренда подсчитан​ЛИНЕЙН​ год. Поле​«Известные значения x»​ составить эффективный прогноз​, кликаем по значку​ и подходов, но​

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

3. ​ содержат начальные значения​ и выведен в​

   • ​, умноженный на количество​«Константа»​вносим адрес столбца​​ более чем на​​ нужного вида диаграммы,​

   • ​ часто для грубой​ процесса через определенные​ Так, получаем прогнозные​​ значение с предсказанным​​Чтобы определить коэффициенты сезонности,​

4. ​ метрик ошибки (MASE,​​ данные. Тем не​​Если у вас есть​Построен на ряде​Прогрессия​

1. ​ SMAPE, обеспечения, RMSE).​​ менее при запуске​​ статистические данные с​​перечислены все ряды​​.​​ 8, то при​​Ставим знак​

2. ​Производим выделение ячейки, в​​ по кнопке​​с данными за​ даже в этом​ блоке​

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

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

​Выполните одно из указанных​ протаскивании вправо значения​«=»​ которой будет производиться​«OK»​ прошедший период.​ случае он будет​«Диаграммы»​ Одна из них​ ряда. Их изменчивость​ с номерами 2,3,5,6,8-10.​Чтобы определить предполагаемую стоимость​ значений тренда («продажи​ создания прогноза возвращаются​ созданный прогноз не​ вы можете создать​ линии тренда. Для​ ниже действий.​

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

1. ​ - это функция​

2. ​ пытаются разделить на​ Для детального анализа​ бензина на оставшиеся​ за год» /​

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

4. ​ за это время​ для конкретной ситуации​ПРЕДСКАЗ (FORECAST)​ закономерную и случайную​ формулы выберите инструмент​ дни используем следующую​ «линейный тренд»).​​ и предсказанными данными​​ вам будет использовать​

5. ​ основе. При этом​ к другим рядам​ ряда часть столбца,​

   • ​Совет:​​ выделяем ячейку, которая​​ наименование​​ выводит результат на​​ на кнопку​ не будет никаких​

   • ​ тип. Лучше всего​​, которая умеет считать​​ составляющие. Закономерные изменения​​ «ФОРМУЛЫ»-«Зависимости формул»-«Вычислить формулу».​​ функцию (как формулу​Рассчитаем средние продажи за​ и диаграмма. Прогноз​

​ статистических данных. Использование​​ в Excel создается​

• ​ выберите нужное имя​​ выберите вариант​​ Чтобы управлять созданием ряда​ содержит значение выручки​«ЛИНЕЙН»​ экран. Как видим,​«OK»​ форс-мажоров или наоборот​ выбрать точечную диаграмму.​ прогноз по линейному​ членов ряда, как​

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

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

​ сумма прогнозируемой прибыли​​.​ чрезвычайно благоприятных обстоятельств,​ Можно выбрать и​ тренду.​ правило, предсказуемы.​ вычислений формулы:​Описание аргументов:​ формулы СРЗНАЧ.​ на основе имеющихся​ дает более точные​ таблицей, содержащей статистические​ затем выберите нужные​.​ ряд значений с​ период. Ставим знак​«Статистические»​

​ на 2019 год,​Оператор производит расчет на​​ которых не было​ другой вид, но​Принцип работы этой функции​​Сделаем анализ временных рядов​​Полученные результаты:​A26:A33 – диапазон ячеек​Определим индекс сезонности для​ данных, зависящих от​ прогноза.​​ и предсказанные значения,​​ параметры.​​Если необходимо заполнить значениями​​ помощью клавиатуры, воспользуйтесь​«*»​и жмем на​​ рассчитанная методом линейной​​ основании введенных данных​​ в предыдущих периодах.​​ тогда, чтобы данные​ несложен: мы предполагаем,​ в Excel. Пример:​Функция имеет следующую синтаксическую​

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

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

Выполнение регрессионного анализа с надстройкой «Пакет анализа»

​ на экран. На​Как построить линию тренда​ выполнить редактирование, в​ можно интерполировать (сгладить)​ данные о продажах​=ПРЕДСКАЗ(x;известные_значения_y;известные_значения_x)​ данные о стоимости​ средней величине). Фактически​ версии AAA.​ то рекомендуется начинать​

support.office.com

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

​ С помощью прогноза​​ скользящее среднее, то​по строкам​(вкладка​ Ставим знак минус​.​ методе расчета, 4637,8​ 2018 год планируется​ в Excel​ частности убрать линию​ некой прямой с​ товаров магазинами, находящимися​Описание аргументов:​ бензина еще не​ нужно каждый объем​Таблицы могут содержать следующие​ прогнозирование с даты,​ вы можете предсказывать​ это скользящее среднее​.​Главная​

​ и снова кликаем​В поле​ тыс. рублей.​ прибыль в районе​Экстраполяцию для табличных данных​ аргумента и выбрать​ классическим линейным уравнением​ в городах с​x – обязательный для​ определены;​ продаж за месяц​ столбцы, три из​ предшествующей последней точке​ такие показатели, как​ базируется на порядке​В поле​, группа​ по элементу, в​«Известные значения y»​

​Ещё одной функцией, с​ 4564,7 тыс. рублей.​ можно произвести через​ другую шкалу горизонтальной​ y=kx+b:​

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

1. ​ населением менее 50​ заполнения аргумент, характеризующий​B3:B25 – диапазон ячеек,​

   • ​ разделить на средний​ которых являются вычисляемыми:​ статистических данных.​

   • ​ будущий объем продаж,​

     ​ расположения значений X​Шаг​Редактирование​

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

2. ​ продлив ее вправо​

   ​ – 2012-2015 гг.​​ новых значений независимой​ стоимости бензина за​ год.​ (ваш ряд данных,​

3. ​Установите или снимите флажок​​ запасах или потребительские​​ получения нужного результата​​ определит значение шага​​Заполнить​​ период. Закрываем скобку​​«Прибыль предприятия»​

   

4. ​ Экселе, является оператор​​ построить график при​​. Этот аргумент относится​ линию тренда. Делаем​ за пределы известного​

   

5. ​ Задача – выявить​​ переменной, для которых​​ последние 23 дня;​В ячейке H2 найдем​ содержащий значения времени);​​доверительный интервал​​ тенденции.​

   ​ перед добавлением скользящего​ прогрессии.​).​ и вбиваем символы​. В поле​ РОСТ. Он тоже​

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

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

​A3:A25 – диапазон ячеек​ общий индекс сезонности​столбец статистических значений (ряд​​, чтобы показать или​​Сведения о том, как​

​ среднего, возможно, потребуется​Тип прогрессии​В экспоненциальных рядах начальное​«*3+»​

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

​ линии тренда подбирается​ нажимаем «Перейти» к​ переменной y. Может​ помощью функции:​Рассчитаем значения тренда для​ ETS. CONFINT). Эти​ распределением). Доверительный интервал​ времени для временной​ значения — это существующие​ и каждый последующий​Продолжение ряда (геометрическая прогрессия)​

​.​ в выбранную ячейку​=РОСТ(Известные значения_y;известные значения_x; новые_значения_x;[конст])​

• ​ в 2019 году​ будет выступать год,​ можно выбрать один​

• ​ так, чтобы сумма​ «Надстройкам Excel» и​ быть указан в​

• ​=СРЗНАЧ(B3:B33)​ будущих периодов: изменим​ столбцы отображаются только​

• ​ помогут вам понять,​ шкалы;​ x- и y-значения;​ результат умножаются на​1, 2​Прогнозируемая сумма прибыли в​ значение линейного тренда.​​Как видим, аргументы у​​ сумма прибыли составит​​ на который следует​​ из шести видов​

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

​ квадратов отклонений исходных​ выбираем «Пакет анализа».​ виде массива чисел​Результат:​ в уравнении линейной​

См. также:

​ в том случае,​

support.office.com

Прогнозирование продаж в Excel и алгоритм анализа временного ряда

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

​4, 8, 16​ 2019 году, которая​Теперь нам предстоит выяснить​ данной функции в​ 4637,8 тыс. рублей.​ произвести прогнозирование.​

​ аппроксимации:​ данных от построенной​Подключение настройки «Анализ данных»​ или ссылки на​

​Можно сделать вывод о​ функции значение х.​ если установлен флажок​ интервал подразумевает более​Эти значения будут предсказаны​ с использованием линейной​В разделе​1, 3​ была рассчитана методом​ величину прогнозируемой прибыли​ точности повторяют аргументы​Но не стоит забывать,​

Пример прогнозирования продаж в Excel

​«Известные значения y»​Линейная​ линии тренда была​ детально описано здесь.​ диапазон ячеек с​ том, что если​ Для этого можно​доверительный интервал​ уверенно предсказанного для​ для дат в​ регрессии. Этой функцией​Тип​

​9, 27, 81​

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

​ просто скопировать формулу​в разделе​ определенный момент. Уровня​ будущем.​

1. ​ можно воспользоваться для​выберите тип прогрессии:​2, 3​ 4639,2 тыс. рублей,​ Устанавливаем знак​ТЕНДЕНЦИЯ​
2. ​ при построении линии​ значений функции. В​Логарифмическая​ тренда наилучшим образом​ ленте.​известные_значения_x – обязательный аргумент,​ на бензин сохранится,​ из D2 в​Параметры​ достоверности 95% по​Примечание:​ прогнозирования будущих продаж,​арифметическая​4.5, 6.75, 10.125​
3. ​ что опять не​«=»​, так что второй​ тренда, отрезок времени​ нашем случае в​;​
4. ​ сглаживала фактические данные.​Из предлагаемого списка инструментов​ который характеризует уже​ предсказания специалистов относительно​ J2, J3, J4.​окна...​
5. ​ умолчанию могут быть​ Для временной шкалы требуются​ потребностей в складских​
6. ​или​Для прогнозирования экспоненциальной зависимости​ сильно отличается от​в любую пустую​ раз на их​ до прогнозируемого периода​ её роли выступает​Экспоненциальная​Excel позволяет легко построить​
7. ​ для статистического анализа​ известные значения независимой​ средней стоимости сбудутся.​
8. ​На основе полученных данных​Щелкните эту ссылку, чтобы​ изменены с помощью​ одинаковые интервалы между​ запасах или тенденций​геометрическая​ выполните следующие действия.​
9. ​ результатов, полученных при​ ячейку на листе.​ описании останавливаться не​ не должен превышать​ величина прибыли за​;​ линию тренда прямо​ выбираем «Экспоненциальное сглаживание».​
10. ​ переменной x, для​​ составляем прогноз по​ загрузить книгу с​ вверх или вниз.​ точками данных. Например,​

​ потребления.​.​

​Укажите не менее двух​

​ вычислении предыдущими способами.​

Алгоритм анализа временного ряда и прогнозирования

​ будем, а сразу​ 30% от всего​ предыдущие периоды.​Степенная​ на диаграмме щелчком​

1. ​ Этот метод выравнивания​ которой определены значения​
2. ​Пример 2. Компания недавно​ продажам на следующие​
3. ​ помощью Excel ПРОГНОЗА.​Сезонность​

​ это могут быть​Использование функций ТЕНДЕНЦИЯ и​В поле​ ячеек, содержащих начальные​Урок:​ в которой содержится​

​ срока, за который​«Известные значения x»​;​ правой по ряду​

exceltable.com

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

​ подходит для нашего​ зависимой переменной y.​ представила новый продукт.​ 3 месяца (следующего​ Примеры использования функции​Сезонности — это число​ месячные интервалы со​ РОСТ​Предельное значение​ значения.​Другие статистические функции в​ фактическая величина прибыли​ этого инструмента на​ накапливалась база данных.​— это аргументы,​Полиномиальная​ - Добавить линию​ динамического ряда, значения​Примечания:​ С момента вывода​ года) с учетом​ ETS​ в течение (количество​ значениями на первое​   . Функции ТЕНДЕНЦИЯ и​введите значение, на​

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

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

1. ​Урок:​ которым соответствуют известные​;​ тренда (Add Trendline),​ которого сильно колеблются.​Второй и третий аргументы​ на рынок ежедневно​
2. ​ сезонности:​Функции прогнозирования​

​ точек) сезонного узора​ число каждого месяца,​ РОСТ позволяют экстраполировать​ котором нужно остановить​ прогноза, укажите дополнительные​Мы выяснили, какими способами​ год (2016 г.).​Выделяем ячейку вывода результата​Экстраполяция в Excel​ значения функции. В​Линейная фильтрация​ но часто для​Заполняем диалоговое окно. Входной​ рассматриваемой функции должны​ ведется учет количества​

​Общая картина составленного прогноза​

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

​ начальные значения.​

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

​y​

​Примечание:​Удерживая правую кнопку мыши,​ в программе Эксель.​«+»​ путем вызываем​

​ ещё одну функцию​

​ нас выступает нумерация​

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

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

​ продаж, с каждой​ временной шкале не​-значения, продолжающие прямую линию​ Если в ячейках уже​ перетащите маркер заполнения​ Графическим путем это​. Далее кликаем по​Мастер функций​ –​ годов, за которые​

​ линейную аппроксимацию.​

​ числовые значения прогноза,​ Фактор затухания –​ или такие диапазоны,​ будет спрос на​График сезонности:​ необходимых финансовых показателей.​ точки, представляющий месяц,​ хватает до 30 %​ или экспоненциальную кривую,​ содержатся первые члены​ в нужном направлении​ можно сделать через​ ячейке, в которой​. В списке статистических​ТЕНДЕНЦИЯ​ была собрана информация​В блоке настроек​ которые ей соответствуют.​ коэффициент экспоненциального сглаживания​ в которых число​ протяжении 5 последующих​

​​В данном примере будем​ сезонности равно 12.​

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

​ о прибыли предыдущих​

​«Прогноз»​ Вот, как раз,​ (по умолчанию –​

​ ячеек совпадает. Иначе​ дней.​Алгоритм анализа временного ряда​

​ использовать линейный тренд​

​ Автоматическое обнаружение можно​ есть несколько чисел​ существующие данные. Эти​ чтобы приложение Microsoft​ возрастающими или убывающими​ а аналитическим –​ линейный тренд. Ставим​«РОСТ»​ к категории статистических​ лет.​в поле​

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

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

​ значениями, отпустите правую​

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

​ функция​ – ссылка на​ код ошибки #Н/Д.​Как видно, в первые​ в Excel можно​ по продажам на​Задание вручную​ той же меткой​y​ автоматически, установите флажок​ кнопку, а затем​ встроенных статистических функций.​«*»​

​ щелкаем по кнопке​

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

​ во многом напоминает​ аргумента не обязательно​

​устанавливаем число​

​ПРЕДСКАЗ (FORECAST)​

• ​ верхнюю левую ячейку​Если одна или несколько​ дни спрос был​ построить в три​ бушующие периоды с​и затем выбрав​ времени, это нормально.​-значения, соответствующие заданным​Автоматическое определение шага​ щелкните​ В результате обработки​
• ​. Так как между​«OK»​ синтаксис инструмента​ должен выступать временной​«3,0»​.​ выходного диапазона. Сюда​ ячеек из диапазона,​ небольшим, затем он​
• ​ шага:​ учетом сезонности.​ числа.​ Прогноз все равно​x​.​

​Экспоненциальное приближение​

1. ​ идентичных данных этими​ последним годом изучаемого​.​ПРЕДСКАЗ​ отрезок. Например, им​, так как нам​Синтаксис функции следующий​ программа поместит сглаженные​ ссылка на который​
2. ​ рос достаточно большими​Выделяем трендовую составляющую, используя​Линейный тренд хорошо подходит​Примечание:​ будет точным. Но​-значениям, на базе линейной​Если имеются существующие данные,​в контекстное меню.​ операторами может получиться​ периода (2016 г.)​Происходит активация окна аргументов​и выглядит следующим​ может являться температура,​
3. ​ нужно составить прогноз​=ПРЕДСКАЗ(X; Известные_значения_Y; Известные_значения_X)​ уровни и размер​ передана в качестве​ темпами, а на​ функцию регрессии.​ для формирования плана​ Если вы хотите задать​ для повышения точности​ или экспоненциальной зависимости.​
4. ​ для которых следует​Например, если ячейки C1:E1​ разный итог. Но​ и годом на​ указанной выше функции.​ образом:​
5. ​ а значением функции​ на три года​где​ определит самостоятельно. Ставим​ аргумента x, содержит​ протяжении последних трех​Определяем сезонную составляющую в​ по продажам для​
6. ​ сезонность вручную, не​ прогноза желательно перед​ Используя существующие​ спрогнозировать тренд, можно​ содержат начальные значения​ это не удивительно,​ который нужно сделать​ Вводим в поля​=ТЕНДЕНЦИЯ(Известные значения_y;известные значения_x; новые_значения_x;[конст])​ может выступать уровень​ вперед. Кроме того,​Х​ галочки «Вывод графика»,​ нечисловые данные или​ дней изменялся незначительно.​ виде коэффициентов.​

exceltable.com

Анализ временных рядов и прогнозирование в Excel на примере

​ развивающегося предприятия.​ используйте значения, которые​ его созданием обобщить​x​ создать на диаграмме​ 3, 5 и​ так как все​

​ прогноз (2019 г.)​ этого окна данные​Как видим, аргументы​ расширения воды при​ можно установить галочки​- точка во​ «Стандартные погрешности».​ текстовую строку, которая​ Это свидетельствует о​Вычисляем прогнозные значения на​

Временные ряды в Excel

​Excel – это лучший​ меньше двух циклов​ данные.​-значения и​ линия тренда. Например,​ 8, то при​ они используют разные​ лежит срок в​ полностью аналогично тому,​«Известные значения y»​

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

​ если имеется созданная​ протаскивании вправо значения​

​ методы расчета. Если​ три года, то​ как мы их​и​При вычислении данным способом​«Показывать уравнение на диаграмме»​ мы делаем прогноз​ ОК. Результаты анализа:​ преобразована в число,​

​ фактором роста продаж​Нужно понимать, что точный​

​ аналитический инструмент, который​ таких значениях этого​

​Совет:​-значения, возвращаемые этими функциями,​ в Excel диаграмма,​ будут возрастать, влево —​ колебание небольшое, то​ устанавливаем в ячейке​ вводили в окне​

​«Известные значения x»​ используется метод линейной​и​Известные_значения_Y​Для расчета стандартных погрешностей​ результатом выполнения функции​ на данный момент​ прогноз возможен только​ позволяет не только​ параметра приложению Excel​ Если выделить ячейку в​ можно построить прямую​ на которой приведены​ убывать.​ все эти варианты,​

​ число​ аргументов оператора​

​полностью соответствуют аналогичным​ регрессии.​«Поместить на диаграмме величину​- известные нам​ Excel использует формулу:​ ПРЕДСКАЗ для данных​

Прогнозирование временного ряда в Excel

​ при индивидуализации модели​ обрабатывать статистические данные,​ не удастся определить​

​ одном из рядов,​ или кривую, описывающую​ данные о продажах​Совет:​ применимые к конкретному​«3»​

​ТЕНДЕНЦИЯ​

​ элементам оператора​Давайте разберем нюансы применения​ достоверности аппроксимации (R^2)»​

​ значения зависимой переменной​ =КОРЕНЬ(СУММКВРАЗН(‘диапазон фактических значений’;​ значений x будет​ базы клиентов, а​ прогнозирования. Ведь разные​

​ но и составлять​ сезонные компоненты. Если​ Excel автоматически выделит​

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

​ оператора​

​. Последний показатель отображает​ (прибыль)​ ‘диапазон прогнозных значений’)/​ код ошибки #ЗНАЧ!.​ развитие продаж с​

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

​Использование функций ЛИНЕЙН и​ месяцев года, можно​

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

exceltable.com

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

​ПРЕДСКАЗ​ качество линии тренда.​Известные_значения_X​ ‘размер окна сглаживания’).​Статистическая дисперсия величин (можно​ постоянными клиентами. В​ разные характеристики.​ точностью. Для того​ недостаточно велики и​На вкладке​ ЛГРФПРИБЛ​ добавить к ней​ ряд значений с​Автор: Максим Тютюшев​Enter​​ на кнопку​​«Новые значения x»​на конкретном примере.​ После того, как​

​- известные нам​ Например, =КОРЕНЬ(СУММКВРАЗН(C3:C5;D3:D5)/3).​ рассчитать с помощью​ таких случаях рекомендуют​бланк прогноза деятельности предприятия​ чтобы оценить некоторые​ алгоритму не удается​

​Данные​   . Функции ЛИНЕЙН и​ линию тренда, которая​ помощью клавиатуры, воспользуйтесь​Когда необходимо оценить затраты​

​.​«OK»​​соответствует аргументу​​ Возьмем всю ту​ настройки произведены, жмем​ значения независимой переменной​​ формул ДИСП.Г, ДИСП.В​ использовать не линейную​Чтобы посмотреть общую картину​ возможности Excel в​ их выявить, прогноз​в группе​ ЛГРФПРИБЛ позволяют вычислить​ представит общие тенденции​

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

​ продаж (рост, снижение​

​Прогрессия​

​ предсказать ожидаемые результаты​

• ​ прибыли, рассчитанная методом​​Результат обработки данных выводится​предыдущего инструмента. Кроме​ нужно будет узнать​
• ​«Закрыть»​​ периодов)​ данные из предыдущего​ в качестве аргумента​
• ​ тренд, чтобы результаты​​ описанного прогноза рекомендуем​ разберем практический пример.​ тренда.​нажмите кнопку​

planetaexcel.ru

​ экспоненциальную кривую для​

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

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

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

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

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

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

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

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

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

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

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

   

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

   

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

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

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

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

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

Сведения о каждом из вариантов можно найти в таблице ниже.

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

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

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

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

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

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

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

Скачивание образца книги

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Статьи по теме

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

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

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

Способ 1: линия тренда

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

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

Способ 2: оператор ПРЕДСКАЗ

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

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

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

«Известные значения y» — база известных значений функции. В нашем случае в её роли выступает величина прибыли за предыдущие периоды.

«Известные значения x» — это аргументы, которым соответствуют известные значения функции. В их роли у нас выступает нумерация годов, за которые была собрана информация о прибыли предыдущих лет.

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

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

Давайте разберем нюансы применения оператора ПРЕДСКАЗ на конкретном примере. Возьмем всю ту же таблицу. Нам нужно будет узнать прогноз прибыли на 2018 год.

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

Способ 3: оператор ТЕНДЕНЦИЯ

Для прогнозирования можно использовать ещё одну функцию – ТЕНДЕНЦИЯ . Она также относится к категории статистических операторов. Её синтаксис во многом напоминает синтаксис инструмента ПРЕДСКАЗ и выглядит следующим образом:

ТЕНДЕНЦИЯ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

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

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

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

Способ 4: оператор РОСТ

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

РОСТ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

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

Способ 5: оператор ЛИНЕЙН

Оператор ЛИНЕЙН при вычислении использует метод линейного приближения. Его не стоит путать с методом линейной зависимости, используемым инструментом ТЕНДЕНЦИЯ . Его синтаксис имеет такой вид:

ЛИНЕЙН(Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

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

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

Способ 6: оператор ЛГРФПРИБЛ

Последний инструмент, который мы рассмотрим, будет ЛГРФПРИБЛ . Этот оператор производит расчеты на основе метода экспоненциального приближения. Его синтаксис имеет следующую структуру:

ЛГРФПРИБЛ (Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

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

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

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

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

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

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

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

1) Ожидания пользователей.

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

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

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

2) Мнение продавцов.

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

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

3) Мнение менеджеров компании.

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

4) Метод Дельфи

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

II)Объективные методы прогнозирования продаж.

Объективные методы прогнозирования продаж базируются в основном на количественных (эмпирических) и аналитических данных.

1) Рыночное тестирование

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

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

2) Анализ временных рядов

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

• - скользящего среднего;
• - экспоненциального сглаживания;
• - декомпозиции.

Метод скользящего среднего

Метод скользящего среднего достаточно прост. Рассмотрим прогноз, который сводится к тому, что объем сбыта в следующем году будет равен объему продаж в году текущем. При значительных колебаниях объемах продаж из года в год такой прогноз чреват серьезными последствиями. Чтобы учесть все нюансы, можно рассчитать среднее значение нескольких показателей объемов продаж за определенные периоды времени, например произвести усреднение объемов продаж за два, три, пять последних лет или за другое количество удобных для расчетов периодов. При таком подходе прогноз продаж оказывается обычным средним значением объемов сбыта. Количество показателей, используемых в вычислении, определяется экспериментальным путем. В конечном итоге число периодов, которое обеспечит наиболее точные прогнозы подающихся проверке данных, будет использоваться для разработки модели прогноза. Термин “скользящее среднее” используется потому, что вычисленное новое среднее значение служит прогнозом на каждом этапе наблюдения при появлении новых данных.

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

При прогнозировании следующего значения метод скользящего среднего придает равный вес каждому из последних значений n, где n -- количество используемых лет. Таким образом, когда n = 4 (т.е. используется четырехгодичное скользящее среднее), при прогнозировании объема сбыта на следующий год одинаковый вес назначается объемам сбыта за каждый год из последних четырех лет.

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

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

Метод декомпозиции

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

• - тренд;
• - циклический фактор;
• - сезонный фактор;
• - случайный фактор.

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

Циклический фактор присутствует не всегда, поскольку отражает подъемы и спады (“волны”) во временном ряде, когда сезонный и случайный компоненты исключены. Циклические подъемы и спады, как правило, проявляются на протяжении достаточно длительного периода времени -- примерно от двух до пяти лет. Для некоторых товаров (например, для консервированной кукурузы) отмечаются незначительные циклические колебания, в то время как продажи других (например, строительство жилья) претерпевают весьма существенные изменения.

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

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

3) Статистический анализ спроса

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

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

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

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

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

Методы прогнозирования объема продаж можно разделить на три основные группы:

• методы экспертных оценок;
• методы анализа и прогнозирования временных рядов;
• казуальные (причинно-следственные) методы.

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

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

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

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

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

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

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

1. точечного прогноза;
2. интервального прогноза;
3. прогноза распределения вероятностей.

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

Интервальный прогноз объема продаж предусматривает установление границ, внутри которых будет находиться прогнозируемое значение показателя с заданным уровнем значимости. Примером является утверждение типа: «В предстоящем году объем продаж составит от 11 до 12,4 млн. руб.».

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

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

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

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

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

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

Вторая группа методов прогнозирования основана на анализе временных рядов.

Таблица 1 представляет временной ряд по показателю потребления безалкогольного напитка «Тархун» в декалитрах (дал) в одном из регионов начиная с 1993 г. Анализ временных рядов может проводиться не только по годовым или месячным данным, но также могут использоваться ежеквартальные, недельные или ежедневные данные об объемах продаж. Для расчетов был использован программный продукт Statistica 5.0 for Windows.

Таблица 1
Ежемесячное потребление безалкогольного напитка «Тархун» в 1993—1999 гг. (тыс. дал)

По данным таблицы 1 построим график потребления напитка «Тархун» в 1993—1999 гг. (рис. 1), где на оси абсцисс представлены даты наблюдения, на оси ординат — объемы потребления напитка.

Рис. 1. Ежемесячное потребление напитка «Тархун» в 1993—1999 гг. (тыс. дал)

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

Тренд — это изменение, определяющее общее направление развития, основную тенденцию временных рядов . Выявление основной тенденции развития (тренда) называется выравниванием временного ряда, а методы выявления основной тенденции — методами выравнивания.

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

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

Рис. 2. Ежегодное потребление напитка «Тархун» в 1993—1999 гг. (тыс. дал)

Порядок расчета скользящих средних по потреблению напитка «Тархун» в 1993 г. приведен в таблице 2. Аналогичный расчет может быть проведен на основе всех данных за 1993—1999 гг.

Таблица 2
Расчет скользящих средних по данным за 1993 г.

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

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

Y t = f(t).

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

1. при равномерном развитии — линейная функция: Y t = b 0 + b 1 t;
2. при росте с ускорением:
   1. парабола второго порядка: Y t = b 0 + b 1 t + b 2 t 2 ;
   2. кубическая парабола: Y t = b 0 + b 1 t + b 2 t 2 + b 3 t 3 ;
3. при постоянных темпах роста — показательная функция: Y t = b 0 b 1 t;
4. при снижении с замедлением — гиперболическая функция: Y t = b 0 + b 1 x1/t.

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

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

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

Расчетные значения параметров уравнения регрессии и графики теоретических и фактических годовых объемов потребления напитка «Тархун» представлены на рисунке 3.

Рис. 3. Теоретические и фактические значения объемов потребления напитка «Тархун» в 1993—1999 гг. (тыс. дал)

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

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

Среднеквадратическая ошибка, рассчитанная по годовым данным потребления напитка «Тархун» для уравнения прямой (рис. 1), составила 1,028 тыс. дал. На основании среднеквадратической ошибки можно рассчитать предельную ошибку прогноза. Для того чтобы гарантировать результат с вероятностью 95%, используется коэффициент, равный 2; а для вероятности 99% этот коэффициент увеличится до 3. Итак, мы можем гарантировать с вероятностью 95%, что объем потребления в 2000 г. составит 134,882 тыс. дал. плюс (минус) 2,056 тыс. дал.

Расчеты по подбору функций, описывающих объем потребления напитка «Тархун» в отдельные месяцы с 1993 г. по 1999 г., показали, что ни одно из перечисленных уравнений не подходит для прогнозирования этого показателя. Во всех случаях объясненная вариация не превысила 28,8%.

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

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

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

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

Сравнение средних квадратических отклонений, вычисленных за разные периоды времени, показывает сдвиги в сезонности (рост свидетельствует об увеличении сезонности потребления напитка «Тархун»).

Другим методом расчета индексов сезонности, часто используемым в различного рода экономических исследованиях, является метод сезонной корректировки, известный в компьютерных программах как метод переписи (Census Method II). Он является своего рода модификацией метода скользящих средних. Специальная компьютерная программа элиминирует трендовую и циклическую компоненты, используя целый комплекс скользящих средних. Кроме того, из средних сезонных индексов удалены и случайные колебания, поскольку под контролем находятся крайние значения признаков.

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

В таблице 3 приведены расчеты индексов и факторов сезонности методами переписи и центрированной скользящей средней.

Таблица 3
Индексы сезонности объема продаж напитка «Тархун», рассчитанные по данным за 1993—1999 гг.

Данные таблицы 3 характеризуют природу сезонности потребления напитка «Тархун»: в летние месяцы объем потребления возрастает, а в зимние — падает. Причем данные обоих методов — переписи и центрированной скользящей средней — дают практически одинаковые результаты. Выбор метода определяется в зависимости от ошибки прогноза, о которой упоминалось выше. Итак, индексы, или факторы, сезонности могут быть учтены при прогнозировании объемов продаж через корректировку трендового значения прогнозируемого показателя. Например, предположим, что был сделан прогноз на июнь 1999 г. методом скользящей средней и он составил 10,480 тыс дал. Индекс сезонности в июне (по методу переписи) равен 115,1. Таким образом, окончательный прогноз для июня 1999 г. составит: (10,480 x 115,1)/100 = 12,062 тыс. дал.

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

где Z — сглаженный (экспоненциальный) объем продаж;
t — период времени;
a — константа сглаживания;
Y — фактический объем продаж.

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

где SO — начальное значение экспоненциальной средней.

При построении прогнозов с помощью метода экспоненциального сглаживания одной из основных проблем является выбор оптимального значения параметра сглаживания a . Ясно, что при разных значениях a результаты прогноза будут различными. Если a близка к единице, то это приводит к учету в прогнозе в основном влияния лишь последних наблюдений; если a близка к нулю, то веса, по которым взвешиваются объемы продаж во временном ряду, убывают медленно, т.е. при прогнозе учитываются все (или почти все) наблюдения. Если нет достаточной уверенности в выборе начальных условий прогнозирования, то можно использовать итеративный способ вычисления a в интервале от 0 до 1. Существуют специальные компьютерные программы для определения этой константы. Результаты расчетов объема продаж напитка «Тархун» методом экспоненциального сглаживания приведены на рисунке 4.

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

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

Рис. 4. График результатов экспоненциального сглаживания

Таблица 4
Прогнозируемый объем продаж напитка «Тархун» в 2000 г.

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

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

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

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

Объемы продаж большинства компаний показывают более значительные колебания, чем те, что представлены в таблице 1. Они растут и падают в зависимости от общей ситуации в бизнесе, уровня спроса на продукты, производимые компаниями, деятельности конкурентов и других факторов. Колебания, отражающие конъюнктурные циклы перехода от более или менее благоприятной рыночной ситуации к кризису, депрессии, оживлению и снова к благоприятной ситуации, называются циклическими колебаниями. Существуют различные классификации циклов, их последовательности и продолжительности. Например, выделяются двадцатилетние циклы, обусловленные сдвигами в воспроизводственной структуре сферы производства; циклы Джанглера (7—10 лет), проявляющиеся как итог взаимодействия денежно-кредитных факторов; циклы Катчина (3—5 лет), обусловленные динамикой оборачиваемости запасов; частные хозяйственные циклы (от 1 до 12 лет), обусловленные колебаниями инвестиционной активности .

Таблица 5
Результаты прогнозирования объема продаж напитка «Тархун» в 1999 г.

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

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

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

• корреляционно-регрессионный анализ;
• метод ведущих индикаторов;
• метод обследования намерений потребителей и др.

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

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

Y (X 1 ; X 2 ; ...; X n) = b 0 + b 1 x X 1 + b 2 x X 2 + ... + b n x X n ,

где Y — прогнозируемый (результативный) показатель; в данном случае — объем продаж;
X 1 ; X 2 ; ...; X n — факторы (независимые переменные); в данном случае — уровень доходов потребителей, цены на продукты конку- рентов и т.д.;
n — количество независимых переменных;
b 0 — свободный член уравнения регрессии;
b 1 ; b 2 ; ...; b n — коэффициенты регрессии, измеряющие отклонение ре- зультативного признака от его средней величины при от- клонении факторного признака на единицу его измере- ния.

Последовательность разработки регрессионной модели для прогнозирования объема продаж включает следующие этапы:

1. предварительный отбор независимых факторов, которые по убеждению исследователя определяют объем продаж. Эти факторы должны быть либо известны (например, при прогнозировании объема продаж цветных телевизоров (результативный показатель) в качестве факторного признака может выступать число цветных телевизоров, находящихся в эксплуатации в настоящее время); либо легко определяемы (например, соотношение цены на исследуемый продукт фирмы с ценами конкурентов);
2. сбор данных по независимым переменным. При этом строится временной ряд по каждому фактору либо собираются данные по некоторой совокупности (например, совокупности предприятий). Другими словами, необходимо, чтобы каждая независимая переменная была представлена 20 и более наблюдениями;
3. определение связи между каждой независимой переменной и результативным признаком. В принципе, связь между признаками должна быть линейной, в противном случае производят линеаризацию уравнения путем замены или преобразования величины факторного признака;
4. проведение регрессионного анализа, т.е. расчет уравнения и коэффициентов регрессии, и проверка их значимости;
5. повтор этапов 1—4 до тех пор, пока не будет получена удовлетворительная модель. В качестве критерия удовлетворительности модели может служить ее способность воспроизводить фактические данные с заданной степенью точности;
6. сравнение роли различных факторов в формировании моделируемого показателя. Для сравнения можно рассчитать частные коэффициенты эластичности, которые показывают, на сколько процентов в среднем изменится объем продаж при изменении фактора X j на один процент при фиксированном положении других факторов. Коэффициент эластичности определяется по формуле

где b j — коэффициент регрессии при j-м факторе.

Регрессионные модели могут использоваться при прогнозировании спроса на потребительские товары и средства производства. В результате проведения корреляционно-регрессионного анализа объема продаж напитка «Тархун» была получена модель

Y t+1 = 2,021 + 0,743A t + 0,856Y t ,

где Y t+1 — прогнозируемый объем продаж в месяце t + 1;
A t — затраты на рекламу в текущем месяце t;
Y t — объем продаж в текущем месяце t.

Возможна следующая интерпретация уравнения многофакторной регрессии: величина объема продаж напитка в среднем увеличивалась на 2,021 тыс. дал, при увеличении затрат на рекламу на 1 руб. объем продаж в среднем увеличивался на 0,743 тыс. дал., при увеличении объема продаж предыдущего месяца на 1 тыс. дал объем продаж в последующем месяце увеличивался на 0,856 тыс. дал.

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

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

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

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

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

1. точность прогноза;
2. наличие необходимых исходных данных;
3. наличие времени для осуществления прогнозирования.

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

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

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

Литература

1. Баззел Р.Д. и др. Информация и риск в маркетинге. — М.: Финстатинформ, 1993.
2. Беляевский И.К. Маркетинговое исследование: информация, анализ, прогноз. — М.: Финансы и статистика, 2001.
3. Березин И.С. Маркетинг и исследования рынков. — М.: Русская деловая литература, 1999.
4. Голубков Е.П. Маркетинговые исследования: теория, методология и практика. — М.: Издательство «Финпресс», 1998.
5. Елисеева И.И., Юзбашев М.М. Общая теория статистики. — М.: Финансы и статистика, 1996.
6. Ефимова М.Р., Рябцев В.М. Общая теория статистики. — М.: Финансы и статистика, 1991.
7. Литвак Б.Г. Экспертные оценки и принятие решений. — М.: Патент, 1996.
8. Лобанова Е. Прогнозирование с учетом экономического роста // Экономические науки. — 1992. — № 1.
9. Рыночная экономика: Учебник. Т. 1. Теория рыночной экономики. Часть 1. Микроэкономика / Под ред. В.Ф. Максимова — М.: Соминтэк, 1992.
10. Статистика рынка товаров и услуг: Учебник / Под ред. И.К. Беляевского. — М.: Финансы и статистика, 1995.
11. Статистический словарь / Под ред. М.А. Королева — М.: Финансы и статистика, 1989.
12. Статистическое моделирование и прогнозирование: Учебное пособие / Под ред. А.Г. Гранберга. — М.: Финансы и статистика, 1990.
13. Юзбашев М.М., Манелля А.И. Статистический анализ тенденций и колеблемости. — М.: Финансы и статистика, 1983.
14. Aaker, David A. and Day George S. Marketing Research. — 4th ed. — NewYork: John Wiley and Sons, 1990. — Chapter 22 «Forecasting».
15. Dalrymple, D.J. Sales forecasting practices // International Journal of Forecasting. — 1987. — Vol. 3.
16. Kress G.J., Shyder J. Forecasting and Market Analysis Techniques: A Practical Approach. — Hardcover, 1994.
17. Schnaars, S.P. The use of multiple scenarios in sales forecasting // The International Journal of Forecasting. — 1987. — Vol. 3.
18. Waddell D., Sohal A. Forecasting: The Key to Managerial Decision Making // Management Decision. — 1994. — Vol 32, Issue 1.
19. Wheelwright, S. and Makridakis, S. Forecasting Methods for Management. — 4th ed. — John Wiley & Sons, Canada, 1985.

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

Зачем директору нужен прогноз продаж

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

«План» и «Прогноз продаж» – это составные части одного процесса.

Лучшая статья месяца

Мы подготовили статью, которая:

✩покажет, как программы слежения помогают защитить компанию от краж;

✩подскажет, чем на самом деле занимаются менеджеры в рабочее время;

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

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

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

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

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

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

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

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

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

1. Ближайшей перспективы (месяц, квартал, год).
2. Среднесрочного планирования (один–три года).
3. Долгосрочного планирования (три–пять лет и более).

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

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

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

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

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

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

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

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

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

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

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

• Управление временем, или Как объявить бойкот пожирателям планов
l&g t;

На каких принципах должно базироваться составление объема продаж

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

1. Руководитель отдела продаж обязан обладать информацией по всем сделкам, планируемым к заключению, в конкретных цифрах. Предоставлять генеральному директору сведения о предполагаемой реализации без уточнения профиля клиента и суммы оборота недопустимо. Информация о величине продаж должна быть предельно конкретной.
2. Важно составлять план на период, в котором предполагается реализация.
3. Менеджеры отдела продаж конкретизируют даты получения выручки. Все сведения собирает коммерческий директор, который предоставляет их для рассмотрения руководителю компании. Задача менеджеров – определить вероятность заключения сделки.
4. Каждой вероятности присваивается конкретный коэффициент. Для внесения в прогноз продаж цена сделки умножается на индекс вероятности. Коммерческий отдел определяет коэффициенты, после чего они утверждаются руководителем компании. Выведенные индексы служат критерием для контроля отчетов, которые составляет служба сбыта.
5. Очень удобно разрабатывать прогноз продаж в Microsoft Excel. В сценарий включаются суммы оборотов по запланированным сделкам, скорректированные на коэффициент вероятности. В таблице Excel создаются страницы для каждого месяца и отдельные разделы для конкретных работников. Формулы помогают автоматически определить вероятность оплат и произвести итоговый расчет.
6. Составление прогноза продаж относится к непосредственной компетенции коммерческого директора. Он отвечает за передачу готового сценария руководителю компании, который, в свою очередь, должен предельно четко обозначить задачу для персонала службы сбыта. Функция менеджеров заключается в своевременном внесении данных в документ Excel. Кроме этого, персонал на уровне автоматизма должен фиксировать все промежуточные показатели при работе с клиентами для последующего учета этой информации в прогнозе.
7. Руководитель организации ведет контроль над деятельностью отдела продаж, используя сведения сформированного сценария. Для этого недостаточно один раз составить таблицу, изменения необходимо вносить регулярно. Если руководитель обнаружит отсутствие корректировок в определенный день, это может свидетельствовать о невыполнении своих функций коммерческим отделом.

Основные методы прогноза продаж на предприятии

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

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

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

• Оценка активов предприятия: памятка для владельца компании

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

№1. Методы целевого прогнозирования продаж

Расчет прогноза продаж при помощи данной группы методов производится в следующем порядке:

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

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

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

1 этап. Определите оптимальный объем продаж. Например, в текущем году сбыт должен составить 150 тыс. единиц товара.

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

1. Количественные показатели продаж за прошлые периоды.
2. Сезонные провалы и повышения спроса на рынке.
3. Размер бюджета, выделенного на рекламные мероприятия, относительно бюджета конкурентов.
4. Наполненность рынка эквивалентной продукцией.

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

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

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

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

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

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

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

№2. Методы пошагового прогнозирования продаж

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

1 этап. Пошаговая разработка сценария начинается с выявления:

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

Для эффективного составления прогноза необходимо ответить на вопросы :

• Какую цену установить для продажи запланированного объема продукции?
• Какие издержки допустимы для того, чтобы реализовать целевой оборот с оптимальной прибыльностью?
• Какая должна быть разница между общей стоимостью проданного товара и понесенными расходами? Удастся ли получить желаемую маржу? Будет размер прибыли удовлетворительным?

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

• Планирование производства - фундамент эффективной деятельности предприятия

3 этап. Экстраполяция.

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

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

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

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

• Какие сделки вы планируете заключить в следующем месяце?
• Какую динамику среди конкурентов вы ожидаете в следующем квартале?

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

1. Изменения банковских ставок.
2. Колебания валютного курса.
3. Предполагаемые изменения в налогообложении.

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

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

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

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

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

1. Повышение розничной цены на товар в возможных пределах.
2. Понижение затратной составляющей в допустимых показателях.
3. Единовременное повышение цены и уменьшение издержек.
4. Снижение размера маржи (это делается в последнюю очередь).

Мнение эксперта

Методы «куда хотим попасть» и «откуда мы идем»

Александр Дорохин ,

Для организации предпочтительно применять два метода прогноза продаж.

Первый из методов можно определить как: «куда мы хотим попасть».

Второй метод – «откуда мы идем». Каждый в своей основе имеет предположение.

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

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

В этой ситуации у руководителя компании есть две основные задачи:

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

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

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

• Проведение планерок: как эффективно донести информацию до коллектива?

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

Поделим расчет прогноза продаж на 3 части :

1. Расчет показателей тенденций.
2. Выявление данных сезонности.
3. Прогнозирование объемов реализации.

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

1. Для расчета значений тренда:

Определим показатели уравнения линейного тренда y=bx+a с помощью функции Excel =Линейн().

Для этого в ячейки Excel вводим функцию =Линейн(объемы продаж за 5 лет; номера периодов; 1;0).

Выделяем 2 ячейки, в левой – формула =Линейн(), нажимаем комбинацию клавиш в следующей последовательности (F2+Ctrl+Shift+Enter). Excel выведет для нас значения коэффициентов a и b.

Рассчитываем значения тренда

Для этого в уравнение y = bx + a подставляем рассчитанные коэффициенты тренда b и а, x – номер периода во временном ряде. Получаем y – значение линейного тренда для каждого периода.

2. Для расчета коэффициентов сезонности:

• Выводим отклонения фактических данных от показателей тренда. Для получения результата реальные показатели делим на значения тренда.
• По всем месяцам выводим средние отклонения за последние 5 лет.
• Определяем общий индекс сезонности – среднее значение коэффициентов, рассчитанных в 3 пункте.
• Выводим коэффициенты сезонности. Каждый коэффициент из пункта 3 делим на коэффициент из пункта 4.

3. Рассчитываем формулу прогноза продаж с учетом роста и сезонности:

• Определяем период, на который необходимо сделать прогноз. Продлеваем номера периодов временного ряда на 2 года и 3 месяца.
• Рассчитываем значения тренда для будущих периодов. В уравнение y = bx + a подставляем полученные коэффициенты тренда b и а, x – номер периода во временном ряде. Определяем y – значение линейного тренда для каждого будущего периода.
• Рассчитываем прогноз. Для этого значения линейного тренда умножаем на коэффициенты сезонности.

Прогноз роста реализации с учетом сезонности готов.

Составить свой пример сценария продаж можно, меняя коэффициенты a и b в линейном тренде y = bx + a.

Дополнительные факторы прогноза продаж

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

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

Как определить оптимальный прогноз продаж

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

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

Мнение эксперта

Точный объем продаж в 100 % случаев оказывается низким

Александр Дорохин ,

руководитель отдела дистрибуции компании «Heinz-Петросоюз», Москва

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

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

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

Управление прогнозом продаж: основные этапы

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

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

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

Этапы продаж:

1. Живая встреча, прямое взаимодействие с потенциальным потребителем. Менеджер демонстрирует продукты.
2. Выявление потребности. Менеджер интервьюирует клиента с целью определить желания и мотивацию к покупке.
3. Выставление предложения. Оно формируется после выявления потребности покупателя.
4. Подготовка договора, согласование с клиентом всех его условий и сроков подписания.
5. Заключение договора. Руководитель подписывает согласованный договор, затем менеджер передает его на подписание клиенту. Документ оформляется должностными лицами со стороны покупателя, после чего передается на исполнение.
6. Оплата сделки. Клиент перечисляет сумму сделки на расчетный счет или проводит оплату наличным способом.
7. Итоговое согласование сделки. Изготовленный макет согласовывается с покупателем.
8. Утвержденный документ заверяется подписями и печатью.

• Подготовьте отчет по продажам

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

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

• Распределите ответственность за предоставление отчета

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

• Вознаграждайте людей за точные прогнозы

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

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

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

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

Это прямая функция начальника отдела сбыта.

1. Руководитель осуществляет непрерывный контроль над тем, как сотрудники выполняют работу по достижению прогнозируемых показателей. Здесь существует правило «не более одной дополнительной попытки». Если оплата в обозначенный день не прошла, проблемы клиента никого не волнуют.
2. Менеджер самостоятельно определяет и называет руководителю отдела продаж крайний срок, за который он доведет эту сделку до результата. Этот период должен быть коротким. Если в обозначенный день результат не достигается, начальник берет на себя вопрос завершения сделки. И бонусы за реализацию получает руководитель.

• Каналы для привлечения новых клиентов на сайт компании

Почему менеджеры занижают прогноз продаж и как с этим бороться

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

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

• Во-вторых, менеджеры иногда занижают процент вероятности положительного закрытия сделки.

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

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

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

Мнение эксперта

Что делать, если менеджеры занижают прогнозы продаж

Николай Кувшинов ,

генеральный директор ООО «КомПрактикс», Москва

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

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

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

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

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