Построение линейного тренда. Выбор наиболее подходящей линии тренда для данных

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

Пример 11.3 . Имеются данные о выпуске продукции группой предприятий по месяцам (млн. руб.):

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

I - 64,5;

II - 76,9;

III - 78,8;

IV - 85,9.

В результате укрупнения интервалов общая тенденция роста выпуска продукции данной группой предприятий выступает отчетливо:

64,5

1. 
   Метод скользящей средней.

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

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

Пример 11.4 . Имеются следующие данные о реализации продукции по городу (среднедневная выручка в сопоставимых ценах):

Квартал	Год
	1	2	3	4
I	175	247	420	426
II	263	298	441	449
III	326	366	453	482
IV	2973	341	399	460

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

Увеличение уровней объема реализации во II и III кварталах и относительное их снижение в IV квартале характерны для каждого из представленных годовых периодов. Для выражения общей тенденции развития явления методом сглаживания рядов динамики необходимо прежде всего определить по эмпирическим данным скользящие средние.

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

Особенность сглаживания по четному числу уровней состоит в том, что каждая из исчисленных четырехчленных средних относится к соответствующим промежуткам между смежными кварталами. Так, первая средняя относится к промежутку между II и III кварталом 1-го года, вторая - к промежутку между III и IV кварталом 2-го года и т.д.

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

Период	Исходный уровень	Средняя из суммы четырех уровней ряда	^ Сглаженный средний уровень (с центрированием)
1	175	1061:4 = 265,25	274,25
2	263
3	326
4	297	1033:4 = 283,25	287,60
5	247	1168:4 = 292,00	297,00
6	298	1208:4 = 302,00	307,50
7	366	1252:4 = 313,00	334,60
8	341	1425:4 = 356,35	374,10
9	420	1568:4 = 392,00	402,90
10	441	1655:4 = 413,75	421,00
11	453	1713:4 = 428,25	429,00
12	399	1719:4 = 429,75	430,75
13	426	1727:4=431,75	435,37
14	449	1756:4 = 439,00	446,62

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

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

1. 
   ^ Трендовые модели временных рядов и МНК.

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

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

1) тенденции, характеризующей долговременную основную закономерность развития исследуемого явления;

2) периодичного компонента, связанного с влиянием сезонности развития изучаемого явления;

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

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

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

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

^ Линейная форма тренда:

y e = at + b , (11.1)

где у - уровни, освобожденные от колебаний, выровненные по прямой;

b - начальный уровень тренда в момент или период, принятый за начало отсчета времени;

а - среднегодовой абсолютный прирост (среднее изменение за единицу времени t; константа тренда).

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

^ Параболическая форма тренда:

у = а + bt + ct 2 , (11.2)

где с - квадратический параметр, равный 1/2 ускорения; константа параболического тренда.

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

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

где k - темп изменения в разах;

e - константа тренда.

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

При k
^ Логарифмическая форма тренда:

y = а + b log(t) . (11.4)

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

Для определения параметров уравнения тренда применяют метод наименьших квадратов (МНК) . Применение МНК для определения параметров линейного тренда y e = at + b дает систему двух линейных уравнений, решение которой выбирается таким образом, чтобы Σ t = 0. В рядах с нечетным числом членов это выполняется при условии, что для центрального члена ряда t = 0 и вправо t - +1, +2, +3..., а влево: -1, -2, -3...

Пример 11.4 . Реализация молочной продукции в магазинах группы городов по кварталам 1995-1998 гг. характеризуется следующими данными:

Период	Реализовано продукции, тыс. тонн
1995 г.
I квартал	39,9
II квартал	65,5
III квартал	63,9
IV квартал	38,5
1996 г.
I квартал	38,1
II квартал	82,3
III квартал	83,4
IV квартал	45,1
1997 г.
I квартал	45,9
II квартал	101,5
III квартал	103,8
IV квартал	63,8
1998 г.
I квартал	55,7
II квартал	115,5
III квартал	121,7
IV квартал	65,5

^ Существенной особенностью данного ряда является наличие ярко выраженной тенденции роста. Темп роста (базисный относительно 1995 г.) t = 172,97%.

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

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

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

Примером тенденций, соответствующих логарифмическому тренду, может служить динамика рекордных достижений в спорте: известно, что увеличение на 1 см рекорда прыжка в вы­соту или снижение на 0,1 с времени бега на 200 или 400 м требует все больших и больших затрат времени, каждый рекорд дается все большим и большим трудом. В то же время нет и «вечных» рекордов, все спортивные достижения улучшаются, но медлен­нее и медленнее, т.е. по логарифмическому тренду. Нередко та­кой же характер динамики присущ на отдельных этапах развития динамике урожайности или валового сбора какой-то культуры в данном регионе, пока новое агротехническое достижение не при­даст снова тенденции ускорения, что иллюстрирует рис. 4.5.

Рис. 4.5. Динамика валового сбора чая в Китае

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

за 1978-1983 гг. средний сбор равен 333 тыс. т;

за 1984-1989 гг. средний сбор равен 483 тыс. т, рост на 150 тыс. т;

за 1990-1994 гг. средний сбор равен 566 тыс. т, рост на 83 тыс. т.

На рис. 4.5 для убедительности нанесен и логарифмический тренд. Заметны также 5-6-летние циклические колебания валового сбора чая.

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

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

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

4.6. Логистический тренд и его свойства

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

Примером такого цикла динамики может служить измене­ние доли грамотного населения в стране, например в России, с 1800 г. до наших дней, или изменение доли семей, имеющих те­левизоры, примерно с 1945 до 2000 г. в России, доли жилищ в городах, имеющих горячее водоснабжение или центральное ото­пление (процесс, еще не законченный). В некоторых зарубеж­ных программах для компьютеров логистическая кривая называется S -образной кривой.

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

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

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

При а 0 >0, а 1 <0 с ростом номеров периодов времени t i полу­чаем логистическую тенденцию роста уровней, причем если нужно начать рост почти от нулевой величины, то а 0 должно быть примерно равно 10, тогда при t = 1
= 0,000123 . Чембольше модуль а 1 , тем быстрее будут возрастать уровни. При а 0 <0, а 1 >0 имеем логистический тренд со снижением уровней, причем, если снижение должно начаться почти от единицы, то а 0 должно быть примерно равно -10. Чем больше а 1 , тем быст­рее будут снижаться уровни, например, при а 0 = -10; а 1 = 1, уже при t i = 20 уровни снизятся почти до нуля.

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

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

Процесс подбора эмпирической формулы 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

Если изучаемый процесс приводит к замедлению роста ка­кого-то показателя, но при этом рост не прекращается, не стремится к какому-либо ограниченному пределу, то гипербо­лическая форма тренда уже не подходит. Тем более не подходит парабола с отрицательным ускорением, по которой замедляю­щийся рост перейдет со временем в снижение уровней. В указан­ном случае тенденция изменения лучше всего отображается логарифмической формой тренда: = 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 параметр адаптации сезонных коэффициентов

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

Рассмотрим, как добавить линию тренда на график в 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), что указывает на минимальную ошибку аппроксимации. Спрогнозируем объемы продаж в последующие периоды. Для этого нужно в уравнение вместо х подставлять номер периода.

Например:

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

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

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

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

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

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

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