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

Из данной статьи вы узнаете:

Примеры применения логарифмического тренда в бизнесе;

Логарифмический тренд y(x)=a*ln(x)+b разложим на запчасти;

5 способов расчета значений логарифмического тренда в Excel ;

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

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

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

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

Логарифмический тренд – это функция y(x)=a*ln(x)+b, где

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

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

b – точка пересечения с осью y на графике;

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

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

Если а<0, то динамика тренда отрицательная.

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

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

Временной ряд - продажи по месяцам по новому товару

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

1. Время - месяцы- x;

2. Объёмы продаж по месяцам - y;

Уравнение логарифмического тренда y(x)=a*ln(x)+b, где y - это объёмы продаж, x - месяцы.

5 способов расчета логарифмического тренда в Excel.

1-й способ - с помощью графика.

Строим график в Excel и видим по оси x - наш временной рад (1, 2, 3... - ноябрь, декабрь, январь...), по оси y объёмы продаж + добавляем на график линию тренда и уравнение тренда .

Получаем уравнение тренда y=2 673 493 ln(x) + 2 913 282

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

1. Время - значение по оси Х;
2. Значение "a" и "b" уравнения логарифмического тренда y(x)=a*ln(x)+b;

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

Например , для 14 месяца значение тренда рассчитывается по следующей схеме: в уравнение подставляем x=14 и получаем y=2 673 493 ln(14) + 2 913 282=9 968 782

20-го y=2 673 493 ln(20) + 2 913 282=10 922 350

2-й способ - с помощью функции Excel =Линейн().

Для расчета коэффициентов логарифмического тренда воспользуемся функцией Excel =ЛИНЕЙН() .

Для этого в функцию =ЛИНЕЙН() введем:

1. известные значения y – объем продаж;

2. известные значения x – номера периодов, причём введенные, как LN(номера периодов);

3. константа – вводим 1 для расчёта коэффициента b уравнения y(x)=a*ln(x)+b;

4. Статистика - 1 или 0;

Формула будит выглядеть вот так =ЛИНЕЙН(C2:O2;LN(C1:O1); ИСТИНА;ИСТИНА)

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

Коэффициенты «а» и «b» логарифмического тренда y(x)=a*ln(x)+b рассчитаны;

Получаем уравнение тренда y=2673492*ln(x)+2913281

Для прогнозирования нам необходимо продлить линию тренда и определить её значения. При её продлении нам будет известен только один параметр - это время, т.е. значения по оси X.

Рассчитываем значения тренда с 1-го месяца (ноябрь) до 20 (июнь)- y=2673492*ln(14)+2913281=9968782

17-го - y=2673492*ln(17)+2913281=10487857

3-й способ - с помощью функции Excel =ТЕНДЕНЦИЯ().

Расчет значений логарифмического тренда с помощью функции Excel =ТЕНДЕНЦИЯ().

Для этого в функцию =ТЕНДЕНЦИЯ() вводим:

1. Известные значения y - объёмы продаж за анализируемый период;

2. Известные значений x - порядковые номера периодов (месяцев), причем введенные как LN(Известные значений x);

3. Новые значения x- порядковые номера периодов, для которых хотим рассчитать значения трендов, причем введенные как LN(Новые значения x);

4. Константа - ставим «1», если хотим рассчитать значения тренда y(x)=a*ln(x)+b с коэффициентом b.

Формула будет выглядеть вот так =ТЕНДЕНЦИЯ(C4:O4;LN(C2:O2);LN(Q2:W2); 1)

Затем, вводим формулу =ТЕНДЕНЦИЯ(), как формулу массива . Для этого

1. Выделяем диапазон ячеек с 1-го по 20-й период, в первой ячейке введена формула =ТЕНДЕНЦИЯ();

2. Нажимаем F2, а затем одновременно - клавиши CTRL + SHIFT + ВВОД.

Значения логарифмического тренда с помощью формулы Excel =тенденция() рассчитаны .

4-й способ - функция Excel =предсказ().

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

Для этого вводим в функцию =предсказ(

1. X - номер периода, для которого рассчитываем прогноз, причем вводим как LN(x);

2. Известные значения y - объёмы продаж по месяцам, фиксируем диапазон , выделяем его и нажимаем F4. Получаем ссылку, как на картинке:

3. Известные значения x - порядковые номера периодов , для которых хотим рассчитать значения логарифмического тренда, причем вводим как LN(Известные значения x) + фиксируем выделенный диапазон, выделяем его и нажимаем F4;

Получаем формулу =ПРЕДСКАЗ(LN(Q2); $C$4:$O$4;LN($C$2:$O$2))

Протягиваем формулу, значения логарифмического тренда рассчитаны .

5-й способ - Forecast4AC PRO

Расчет значений логарифмического тренда - с помощью программы Forecast4AC PRO .

1. Устанавливаем курсор в начало временного ряда, выбираем в настройках программы:

Что рассчитываем - значения тренда;

Тренд – Логарифмический тренд;

Временной ряд - месячный;

и сохраняем;

2. Заходим в меню программы и нажимаем «Start_Forecast» - готово, значения логарифмического тренда рассчитаны!

Коэффициенты сезонности рассчитаем с помощью программы Forecast4AC PRO (лист " Лист2FYMLn ") или по аналоги, только для рассчета коэффициентов сезонности вместо линейного тренда используем логарифмический.

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

Отношение прогноза к предыдущему периоду получилось 116%, т.е. прогнозируется рост на 16%.

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

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

Скорректируем значение "a" и "b" рассчитанного нами выше тренда y=2673492*ln(x)+2913281

При изменении значений «a» и «b» логарифмического тренда y(x)=a*ln(x)+b, получаем увеличение значений тренда, причем увеличение коэффициента "а" на 10% даёт больший рост, чем увеличение коэффициента «b» на 20%.

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

С помощью программы Forecast4AC PRO вы сможете в Excel одним нажатием клавиши рассчитать значения логарифмического тренда, коэффициенты сезонности и прогноз для более чем 5000 строк одновременно.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

St =aYt +(1−α)St−1,

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

Чем меньше значение константы α , тем менее оно чувствительно к изменениям тренда в данном временном ряду.

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

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

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

1. 
   Линейная – y = cx + b . Это простейшая функция, отражающая рост и убывание данных с постоянной скоростью.
2. 
   Полиномиальная – y = c 0 + c 1 x + c 2 x 2 +…+ c 6 x 6 . Функция описывает попеременно возрастающие и убывающие данные. Полином 2-ой степени может иметь один экстремум (min или max), 3-ей степени – до 2-х экстремумов, 4-ой степени – до 3-х и т.д.
3. 
   Логарифмическая – y = c lnx + b . Эта функция описывает быстро возрастающие (убывающие) данные, которые затем стабилизируются.
4. 
   Степенная – y = cx b , (х >0 и y >0). Функция отражает данные с постоянно увеличивающейся (убывающей) скоростью роста.
5. 
   Экспоненциальная – y = ce bx , (e – основание натурального логарифма). Функция описывает быстро растущие (убывающие) данные, которые затем стабилизируются.

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

Реклама (тыс. руб)	1,5	2	2,5	3	3,5	4	4,5	5	5,5	6
Продажи (тыс. шт)	3	13	25	35	40	45	48	50	51

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

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

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

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

Рис. 2.48

Рис. 2.49

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

Рис. 2.51
Теперь вернитесь к состоянию рис. 2.50, нажав кнопку Отменить на Панели инструментов. Попробуйте изменить формат линии тренда – установите полиномиальную линию тренда полиномом 2-ой степени – получите рис. 2.52.

Рис. 2.52
Как видно, полученная формула аппроксимирует исходную зависимость (на отрезке B2:J2) с большей степенью близости, т.к. R 2 = 0.9973. В то же время, если сделать прогноз на 10 периодов вперед, то он будет не совсем верно отражать реальность: продажи не могут уменьшаться с увеличением рекламных вложений. Убедитесь в этом: сделайте прогноз на 10 периодов наблюдения вперед и получите график.

Опять вернитесь к состоянию рис. 2.50, нажав кнопку Отменить . Для вычисления продаж при рекламе в 6 тыс. руб. запишите в ячейку К2 формулу =23,796*LN(K1)+0,5961: должно получиться 43,2 тыс. штук.

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

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

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

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

Цена (руб)	1	1,5	2	2,5	3	3,5	4	4,5	5
Спрос (шт)	1300	700	500	200	100	70	50	40

1. 
   Концентрация ядовитого вещества в водоеме изменялась во времени согласно таблице:

Определите вид зависимости концентрации от времени и расчетную концентрацию в момент выброса.

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

В настоящем разделе рассматривается аппроксимация экспериментальных данных с помощью функций ЛИНЕЙН, ТЕНДЕНЦИЯ, ЛГРФПРИБЛ и РОСТ. Функции ЛИНЕЙН и ТЕНДЕНЦИЯ применяют для восстановления линейных зависимостей вида y=b+a 1 x 1 +a 2 x 2 +…+a n x n , а функции ЛГРФПРИБЛ и РОСТ - для нелинейных (показательных) зависимостей вида y=ba 1 X 1 a 2 X 2 …a n Xn .

Функции ЛИНЕЙН и ЛГРФПРИБЛ возвращают массив с т.н. регрессионной статистикой, в котором содержатся вычисленные значения параметров (b,a 1 ,a 2 ,…a n), коэффициент детерминации R 2 и другие данные, характеризующие аппроксимирующую функцию. Формат функций ЛИНЕЙН, ЛГРФПРИБЛ и их применение поясним на примере.

Расчет стоимости недвижимости
Агентство недвижимости оценивает однокомнатные квартиры по трем переменным: х1 – общая площадь, х2 – площадь кухни, х3 – этаж квартиры , предполагая, что между каждой переменной х1, х2, х3 и зависимой переменной y (стоимость) существует линейная зависимость. Подобрать формулу для вычисления стоимости однокомнатных квартир и вычислить стоимость квартиры с данными: х1=42кв.м, х2=11кв.м, х3=5эт. Собранные рекламные данные занесены в приведенную ниже таблицу.

Последовательность действий для решения задачи следующая:

1. 
   Заведите приведенную таблицу в Excel, в ячейки A1:D14.
2. 
   Выделите диапазон ячеек B17:E21 (рис. 2.54) для сохранения результатов вычислений функции ЛИНЕЙН – массива регрессионной статистики.
3. 
   Вызовите мастер функций, выберите статистическую функцию ЛИНЕЙН и заполните параметры функции как на рис. 2.53. Параметр Изв_знач_ y содержит диапазон D2:D14, т.е. известные значения y. Параметр Изв_знач_х содержит диапазон A2:C14, т.е. известные значения х. Параметр Стат=1 , поскольку мы хотим получить дополнительную статистику.

Рис. 2.53

1. 
   После нажатия ОК встаньте на строку формул и нажмите Ctrl+Shift+Enter. В результате должен получиться массив значений, показанный на рис. 2.54. Интересующие нас коэффициенты выделены на рисунке (подробнее см. справку F1). Коэффициент детерминации R 2 =0.9725 вполне удовлетворителен. Таким образом, искомая формула имеет вид:

Y = 1,36*х1 + 0,1*х2 – 0,21*х3 – 19,27

Рис. 2.54

1. 
   После подбора формулы осталось вычислить стоимость при х1=42, х2=11, х3=5. В любую ячейку запишите выражение =1,36*42+0,1*11–0,21*5–19,27. В результате получится y=37.9 тыс. $.

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

Рис. 2.55
Новые значения Х, для которых надо рассчитать стоимость , следует ввести в ячейки F2:H14. Диапазон I2:I14 используйте для записи рассчитанных значений y, Вызовите мастер функций и функцию ТЕНДЕНЦИЯ. Параметры функции заполните как на рис. 2.56. Как видно параметр Нов_знач_х содержит диапазон F2:H14, т.е. новые значения х. После нажатия ОК встаньте на строку формул и нажмите Ctrl+Shift+Enter – результат, заполненный диапазон I2:I14 на рис. 2.55.

Рис. 2.56

Оценка эффективности рекламы
Следующий пример. Подобрать формулу для вычисления процента увеличения оборота при различных затратах на рекламу. Экспериментально известны проценты увеличения оборота при затратах в 5, 10, 15, 20 тыс.$ в 3-х масс-медиа - на телевидении, радио и в прессе:

	5 тыс. $	10 тыс. $	15 тыс. $	20 тыс. $
1. TV	28%	43%	61%	95%
2. Радио	15%	24%	34%	50%
3. Пресса	6%	9%	13%	20%

Кроме этого, надо вычислить процент увеличения оборота в прессе при затратах 2 тыс.$ и на телевидении при затратах в 22 тыс.$. Дополнительно вычислите проценты для всех масс-медиа при затратах 2, 17 и 25 тыс.$.

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

Рис. 2.57
Затем вычислите массив с регрессионной статистикой функцией ЛИНЕЙН: выделите диапазон ячеек F2:H6 и проделайте известные из предыдущего примера действия. В итоге должен получиться массив:

Как видно, коэффициент детерминации R 2 =0.8757 не удовлетворителен. Поэтому выполните подбор формулы с помощью функции для нелинейных зависимостей ЛГРФПРИБЛ: выделите диапазон ячеек F2:H6 и проделайте известные из предыдущего примера действия. В итоге должен получиться массив:

В этом случае коэффициент детерминации R 2 =0.989 вполне удовлетворителен и можно записать искомую аппроксимирующую формулу показательного типа (т.к. использована функция ЛГРФПРИБЛ):

Y = 0,44 * 0,46 х1 * 1,08 х2

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

Пресса, 2 тыс.$	5,0%	=0,44*0,46^3*1,08^2
TV, 22 тыс.$	110,0%	=0,44*0,46^1*1,08^22

В заключении , вычислите проценты для всех масс-медиа при затратах 2, 17 и 25 тыс.$. Подготовьте данные, колонки J и K, как на рис. 2.58.

Для вычисления значений Y используем функцию РОСТ, поскольку уже известно, что зависимость нелинейная, показательная. Выделите диапазон ячеек L2:L10 и введите функцию РОСТ ; заполнение параметров функции показано на рис. 2.59.

Рис. 2.58

Рис. 2.59
После нажатия ОК и Ctrl+Shift+Enter на строке формул, колонка L будет заполнена как на рис. 2.58. Сравните результаты с результатами вычисления по подобранной формуле.
Задачи для самостоятельного выполнения:

1. 
   Источник радиоактивного излучения помещен в жидкость. Датчик расположен на расстоянии (х1) 20, 50 и 100 см от источника. Измерения интенсивности излучения (y, мРн) проводились через 1, 5 и 10 суток (х2) после установки источника. Необходимо подобрать аппроксимирующее уравнение. Результаты измерений приведены в таблице:

х1 / х2	1	5	10
20	61.2	43.6	28.3
50	33.6	24.0	15.6
100	12.3	8.8	5.7

1. 
   В бассейне проводится ежедневная частичная смена воды. Необходимо подобрать формулу для вычисления уровня воды в бассейне, которая зависит от двух переменных: х1 – длительность впуска воды, х2 – длительность выпуска воды. Кроме этого, необходимо вычислить значения уровня воды для х1 с шагом 10 и х2 с шагом 5. Исходные данные - результаты наблюдений за неделю приведены в таблице:

х1	х2	y
120	20	3.2
100	25	2.8
130	20	3.3
100	15	3.3
110	23	3.0
105	26	2.8
112	13	3.3

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

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

Арифметическая

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

В следующем примере прямая линия описывает стабильный рост продаж холодильников на протяжении 13 лет. Обратите внимание, что значение R-квадрат = 0,9036, то есть близко к единице, что свидетельствует о хорошем совпадении расчетной линии с данными.

Логарифмическая

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

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

Полиномиальная

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

В следующем примере полином второй степени (один максимум) описывает зависимость расхода бензина от скорости автомобиля. Близкое к единице значение R-квадрат = 0,9474 свидетельствует о хорошем совпадении кривой с данными.

Мощность

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

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

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

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

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

Скользящее среднее

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

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

Если изучаемый процесс приводит к замедлению роста ка­кого-то показателя, но при этом рост не прекращается, не стремится к какому-либо ограниченному пределу, то гипербо­лическая форма тренда уже не подходит. Тем более не подходит парабола с отрицательным ускорением, по которой замедляю­щийся рост перейдет со временем в снижение уровней. В указан­ном случае тенденция изменения лучше всего отображается логарифмической формой тренда: = a + b ln .

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

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

Основные свойства логарифмического тренда:

1. Если b>0, то уровни возрастают, но с замедлением, а если b<0, то уровни тренда уменьшаются, тоже с замедлением.

2. Абсолютные изменения уровней по модулю всегда умень­шаются со временем.

3. Ускорения абсолютных изменений имеют знак, противо­положный самим абсолютным изменениям, а по модулю посте­пенно уменьшаются.

4. Темпы изменения (цепные) постепенно приближаются к 100% при .

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

Адаптивные методы

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

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

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

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

37 . Модель Брауна.

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

х с волной(?)=а0т+ а1т* т (1).где - прогноз, выполненный на? шагов вперед на t-м шаге адаптации, - адаптируемые параметры модели, ? – период упреждения. Параметры рассчитываются по формулам:

система:а0т=2St (1) - St (2), f1t=1 -бетта/бетта *(St(1)- St(2)), (2) где - экспоненциальные средние соответственно 1-го и 2-го порядков; ? – параметр сглаживания (адаптации). Иногда параметр сглаживания обозначают как?=1-? Экспоненциальная средняя 1- го порядка представляет собой сумму взвешенных значений переменной за весь предшествующий период адаптации и определяется формулой:St(1)=(1-бетта)*хе + бетта*St-1(1),: (3)

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

Экспоненциальную среднюю можно выразить через предшествующую экспоненциальную среднюю: St-1(1)=(1-бетта)*хе-1 + бетта*St-2(1), (4) подставив (4) в (2) получим:St(1)=(1-бетта)*хе+бетта*(1-бетта)*хt-1+бетта в кв* St-2(1),(5)Аналогично можно выразить через предшествующую экспоненциальную среднюю и подставить в уравнение (3) и, и, и т.д. Отсюда имеем St-2 (1)= (1-бетта)*сумма от 0 до 1 БЕТТА (j)*хt-j+бетта (t)*S0(1).(6) Таким образом, применив такую процедуру экспоненциального сглаживания к исходному ряду, получим сглаженный ряд первого порядка. Повторное применение процедуры экспоненциального сглаживания уже к сглаженному ряду первого порядка, называется процедурой экспоненциального сглаживания второго порядка (к применяем формулу (3)):St(2)=(1- бетта)*St(1)+ бетта*St-1 (2),(7) Начальные значения экспоненциальных средних будут определяться по формулам:система:S0(1)=a00- (бетта/1- бетта) *а10; S0 (2)=a00-(2*бетта/(1-бетта))*а10,(8),Система (8) получена решением системы (2) относительно при t=0.

Начальные значения параметров (необходимы для решения системы (8)) рассчитываются как коэффициенты регрессии хт=а00+а10*т.Отметим, что значение параметра адаптации?=1-? лежит в интервале (1; 0). Выбор значения? зависит от того, каким значениям исходного ряда (начальным или конечным) придается больший вес. Если требуется придать вес более поздним значения ряда (увеличить степень реагирования модели на последние изменения), то берут значения? больше 0,5. Если же хотят получить более сглаженную картину тенденции развития ряда, то есть стремятся избежать краткосрочных изменений и повысить степень устойчивости модели, то значения? берут меньше 0,5, и таким образом придают вес ранним наблюдениям ряда.

Будем рассматривать два способа определения параметра адаптации?:

1) метод Брауна алфа=2/m+1, где m –число наблюдений в ряду.

2) метод выбора?, исходя из минимума средней квадратической ошибки между расчетным и фактическим значениями.

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

Модель Хольта.

Модель Хольта, содержащая два параметра адаптации, выглядит следующим образом: х с волной е(р)=ае+бе*р (3.1.),где - прогноз, выполненный на? шагов вперед после t шагов адаптации, - корректируемые параметры модели на каждом шаге t, ? – период упреждения прогноза. Адаптация параметров модели происходят по следующим формулам:система:ае=альфа1*х1+ (1- альфа)*(ае-1+бе-1); бт=альфа2*(фе-фе-1)+(1-фльфа2)*бт-1,(3.2) где? – параметры адаптации.

1. Модель Хольта-Уинтерса.

Модель Хольта-Уинтерса имеет другое название адаптивной сезонной модели с линейным трендом имеет три параметра адаптации. Различают аддитивную и мультипликативную модель Хольта-Уинтерса в зависимости от того, как включена сезонная составляющая (умножение или сложение). Рекуррентные формулы для обновления мультипликативной модели:система:а0е=альфа*(хт/фт-1)+(1-альфа1)*(ф0е-1+ф1т-1); а1т=альфа2*(а0т-а0т-1)+(1-альфа2)*ат-1;фе=альфа3*(хт/а0т)+(1-альфа3)*фе-ий

(4.1) где - адаптируемые параметры линейного тренда на t-м шаге адаптации, - параметры адаптации, - адаптируемый параметр сезонных коэффициентов на t- м шаге адаптации, l – период сезонности. Прогнозирование в мультипликативной модели на? шагов вперед осуществляется по формуле:х с фолной т+р=а0т+ф1т*р+фе-1+р, 4.5)Определение начальных параметров а00,а10,фий-1 (i=0, 1, …,l) для параметров адаптации альфа 1 2 3 исходит из следующих требований: параметры а00,а10 определяются как коэффициенты регрессии хт=а00+а10*т, адаптируемые же коэффициенты сезонности определяются как среднее арифметическое значение индексов фс волной т=хт-у расч.т (для мультипликативной модели) и ф с волной=хт-у расч.т.

(для аддитивной модели), причем рассчитываются они для каждой одноименной фазы периода (-расчетные значения линейного тренда).

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