Формула расчета коэффициентов линейного тренда

Содержание
  1. Построение линейного тренда
  2. Методы сглаживания колебаний.
  3. Метод «скользящих средних» (МСС).
  4. Метод экспоненциального сглаживания.
  5. 5 способов расчета значений линейного тренда в MS Excel
  6. 1-й способ расчета значений линейного тренда в Excel с помощью графика
  7. 2-й способ расчета значений линейного тренда в Excel — функция ЛИНЕЙН
  8. 3-й способ расчета значений линейного тренда в Excel — функция ТЕНДЕНЦИЯ
  9. 4-й способ расчета значений линейного тренда в Excel — функция ПРЕДСКАЗ
  10. 5-й способ расчета значений линейного тренда в Excel — Forecast4AC PRO
  11. Присоединяйтесь к нам!
  12. О линейном тренде
  13. Как мы можем скорректировать прогнозные значения тренда?
  14. Точных вам прогнозов!
  15. Присоединяйтесь к нам!
  16. Как спрогнозировать бизнес-показатели по методу линейного тренда с учетом сезонности: пошаговый мануал
  17. Что собой представляет метод линейного тренда?
  18. Способ 1. Расчет линейного тренда с помощью функции ЛИНЕЙН
  19. Шаг 1. Представим исходные данные в виде простой таблицы
  20. Шаг 2. Рассчитываем значение линейного тренда — y(x)=a+bx
  21. Шаг 3. Для каждого периода рассчитываем значение линейного тренда
  22. Шаг 4. Определяем индекс сезонности для каждого месяца (отношение продаж месяца к средней величине)
  23. Шаг 5. Создаем таблицу для прогнозных значений на следующий год и рассчитываем значения тренда для них
  24. Шаг 6. На основе полученных данных, составляем прогноз по продажам на следующий год с учетом сезонности
  25. Способ 2. Быстрый расчет прогноза, с помощью функций ТЕНДЕНЦИЯ и ПРЕДСКАЗ
  26. Шаг 1. Рассчитываем значения линейного тренда с помощью стандартной функции ТЕНДЕНЦИЯ
  27. Шаг 2. Рассчитываем значения линейного тренда с помощью стандартной функции ПРЕДСКАЗ
  28. Как можно исправить прогнозные значения тренда?
  29. Построение функции тренда в Excel. Быстрый прогноз без учета сезонности
  30. Базовые понятия
  31. Построение модели
  32. Определение коэффициентов модели
  33. Прогнозируем

Построение линейного тренда

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

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

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

nailuchshee sootvetstvie

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

postroenie trenda metodom naimenshih kvadratov

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

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

znacheniya konstant opredel po formule

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

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

vichislenie lineynogo trenda

Методы сглаживания колебаний.

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

Метод «скользящих средних» (МСС).

МСС позволяет сгладить ряд значений с тем, чтобы выделить тренд. При использовании этого метода берется среднее (обычно среднеарифметическое) фиксированного числа значений. Например, трехточечное скользящее среднее. Берется первая тройка значений, составленная из данных за январь, февраль и март (10 + 12 + 13), и определяется среднее, равное 35 : 3 = 11,67.

Полученное значение 11,67 ставится в центре диапазона, т.е. по строке февраля. Затем «скользим на один месяц» и берется вторая тройка чисел, начиная с февраля по апрель (12 + 13 + 16), и рассчитывается среднее, равное 41 : 3 = 13,67, и таким приемом обрабатываем данные по всему ряду. Полученные средние представляют новый ряд данных для построения тренда и его аппроксимации. Чем больше берется точек для вычисления скользящей средней, тем сильнее происходит сглаживание колебаний. Пример из МВА построения тренда дан в табл. 5.2 и на рис. 5.4.

Таблица 5.2 Расчет тренда методом трехточечного скользящего среднего

raschet trenda metodom trehtochechnogo skolzyashego srednego

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

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

kharakter izmeneniya obema prodag

Рис. 5.4. Характер изменения объема продаж по месяцам года:
исходные данные (ряд 3); скользящие средние (ряд 4); экспоненциальное сглаживание (ряд 2); тренд, построенный методом регрессии (ряд 1)

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

Альтернативный подход к сокращению разброса значений ряда состоит в использовании метода экспоненциального сглаживания. Метод получил название «экспоненциальное сглаживание» в связи с тем, что каждое значение периодов, уходящих в прошлое, уменьшается на множитель (1 – α).

Каждое сглаженное значение рассчитывается по формуле вида:

где St – текущее сглаженное значение;
Yt – текущее значение временного ряда; St – 1 – предыдущее сглаженное значение; α – сглаживающая константа, 0 ≤ α ≤ 1.

Источник

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

Автор: Алексей Батурин.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

О линейном тренде

Автор: Алексей Батурин.

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

Линейный тренд разложим на «запчасти»;

Как скорректировать значения линейного тренда и для чего;

Линейный тренд – это функция y=ax+b, где

Значение x – это номер периода во временном ряду (например, номер месяца, квартала, дня; См. статью о временных рядах.)

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

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

Причем, если a>0, то динамика роста положительная,

по 28-й — y=53934*28+1784066 = 3294218

Получили прогнозные значения тренда с 15 по 28 месяца. Отношение прогноза к фактическим данным 1,34, т.е. прогнозируется рост на 34%.

Как мы можем скорректировать прогнозные значения тренда?

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

Скорректируем значение рассчитанного нами выше тренда y=53934x+1784066 – ряд 1 на графике:

linetrend3
Если изменяем значение «a» линейного тренда y=ax+b, то увеличиваем наклон тренда (ряд 3 на графике);

Если изменяем значение «b» линейного тренда (Ряд 2), то тренд мы поднимаем параллельно ряду 1.

Т.е. мы можем изменять наклон тренда, изменять уровень тренда, и одновременно и уровень и наклон — ряд 4 (пример во вложении).

Теперь рассчитаем коэффициенты сезонности с помощью Forecast4AC PRO (лист «ForLin»). Умножим значения тренда на сезонность. Прогноз продаж готов! Также стоит учесть дополнительные факторы, кроме сезонности, которые влияют на объем продаж.

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

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

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

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

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

Источник

Как спрогнозировать бизнес-показатели по методу линейного тренда с учетом сезонности: пошаговый мануал

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

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

Что собой представляет метод линейного тренда?

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

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

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

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

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

Способ 1. Расчет линейного тренда с помощью функции ЛИНЕЙН

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

Шаг 1. Представим исходные данные в виде простой таблицы

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

Шаг 2. Рассчитываем значение линейного тренда — y(x)=a+bx

Найдем значения параметров а и b с помощью функции ЛИНЕЙН, где первый параметр функции — диапазон значений y, второй — диапазон значений x. В третий и четвертый параметры вносим 1 и 0 соответственно:

Примечание для тех, кто работает в Exсel: чтобы рассчитать сразу 2 коэффициента линейного тренда (a) и (b), необходимо установить курсор в ячейку с формулой, выделить соседнюю справа и нажать клавишу F2, а затем одновременно — клавиши CTRL + SHIFT + ВВОД. Google Таблицы справляются сами по умолчанию.

Шаг 3. Для каждого периода рассчитываем значение линейного тренда

В известное уравнение y(x)=a+bx подставляем рассчитанные коэффициенты:

Шаг 4. Определяем индекс сезонности для каждого месяца (отношение продаж месяца к средней величине)

Фактически, нужно каждый объем продаж за месяц разделить на средний объем продаж за год:

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

Шаг 5. Создаем таблицу для прогнозных значений на следующий год и рассчитываем значения тренда для них

Последовательность нумерации периодов продолжаем:

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

Способ 2. Быстрый расчет прогноза, с помощью функций ТЕНДЕНЦИЯ и ПРЕДСКАЗ

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

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

Шаг 1. Рассчитываем значения линейного тренда с помощью стандартной функции ТЕНДЕНЦИЯ

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

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

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

Как можно исправить прогнозные значения тренда?

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

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

Мы можем влиять на параметры a и b линейного уравнения y = a + bx, тем самым изменяя значения:

Таким образом, мы можем изменять наклон и уровень тренда, как в отдельности, так и одновременно (подробнее в примере):

Источник

Построение функции тренда в Excel. Быстрый прогноз без учета сезонности

predictive analytics 383b23df 423a 468f bdc6 bfdc66a2fbb4

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

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

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

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

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

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

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

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

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

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

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

img 5b162de33719f

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

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

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

img 5b162e45cf604

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

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

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

img 5b162f6f6ce02

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

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

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

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

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

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

img 5b1630074d3c0

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

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

Источник

Комфорт
Adblock
detector