Инструменты прогнозирования в Microsoft Excel. Подбор формул по графику

Самым простым типом линии тренда является прямая ли­ния, описываемая линейным (т.е. первой степени) уравнением тренда: где - выровненные, т.е. лишенные колебаний, уровни тренда для лет с номером i;

а - свободный член уравнения, численно равный среднему выровненному уровню для момента или периода времени, принятого за начало отсчета, т.е. для t =0;

b - средняя величина изменения уровней ряда за единицу из­менения времени;

ti - номера моментов или периодов времени, к которым от­носятся уровни временного ряда (год, квартал, месяц, дата).

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

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

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

Равные изменения за равные промежутки времени;

Если средний абсолютный прирост - положительная вели­чина, то относительные приросты или темпы прироста посте­пенно уменьшаются;

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

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

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

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

=a+b*t+c*t 2

Значения (смысл, сущность) параметров параболы II поряд­ка таковы: свободный член а - это средний (выровненный) уро­вень тренда на момент или период, принятый за начало отсчета времени, т.е. t = 0; b - это средний за весь период среднегодовой прирост, который уже не является константой, а изменяется рав­номерно со средним ускорением, равным 2 с, которое и служит константой, главным параметром параболы II порядка.

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

Основные свойства тренда в форме параболы II порядка та­ковы:

1) неравные, но равномерно возрастающие или равномерно убывающие абсолютные изменения за равные промежутки вре­мени;

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

3) так как свободный член уравнения а как значение показа­теля в начальный момент (период) отсчета времени, как правило, величина положительная, то характер тренда определяется знаками параметров b и с:

а) при b >0 и с>0 имеем восходящую ветвь, т.е. тенденцию к ускоренному росту уровней;

б) при b <0 и с<0 имеем нисходящую ветвь - тенденцию к ускоренному сокращению уровней;

в) при b > 0 и с<0 имеем либо восходящую ветвь с замедляю­щимся ростом уровней, либо обе ветви параболы, восходящую и нисходящую, если их по существу можно считать единым про­цессом;

г) при b <0 и с>0 имеем либо нисходящую ветвь с замедляю­щимся сокращением уровней, либо обе ветви - нисходящую и восходящую, если их можно считать единой тенденцией;

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

Экспоненциальным трендом называют тренд, выраженный уравнением: y i =a*k t i . Свобод­ный член экспоненты а равен выровненному уровню, т.е. уров­ню тренда в момент или период, принятый за начало отсчета времени, т.е. при t= 0. Основной параметр экспоненциального тренда k является постоянным темпом изменения уровней (цен­ным). Если k> 1, имеем тренд с возрастающими уровнями, при­чем это возрастание не просто ускоренное, а с возрастающим ускорением и возрастающими производными всех более высо­ких порядков. Если k< 1, то имеем тренд, выражающий тенден­цию постоянного, но замедляющегося сокращения уровней, причем замедление непрерывно усиливается. Экстремума экс­понента не имеет и при стремится либо к при k > 1, либо к 0 при k< 1.

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

Основные свойства экспоненциального тренда:

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

2. Экспонента экстремумов не имеет: при k > 1 тренд стремит­ся к + , при k< 1 тренд стремится к 0.

3. Уровни тренда представляют собой геометр про­грессию: уровень периода с номером t =т есть a*k m .

4. При k > 1 тренд отражает ускоряющийся неравномерно рост уровней, при k < 1 тренд отражает замедляющееся неравномерно уменьшение уровней. Поведение основных показателей дина­мики в этих случаях рассмотрено в табл. 5 и 6.

из различных форм гипербол рассмотрим только наиболее простую:

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

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

Если параметр b<0, то с возрастанием t, т.е. с течением вре­мени, уровни тренда возрастают и стремятся к величине а при .Такой характер динамики присущ, например, показателям КПД двигателей или иных преобразователей энергии.

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

1. Абсолютный прирост или сокращение уровней, ускоре­ние абсолютных изменений, темп изменения - все эти показате­ли не являются постоянными. При b>0 уровни замедленно уменьшаются, отрицательные абсолютные изменения, а также положительные ускорения тоже уменьшаются, цепные темпы из­менения растут и стремятся к 100%.

2. При b<0 уровни замедленно возрастают, положительные абсолютные изменения, а также отрицательные ускорения и цеп­ные темпы роста замедленно уменьшаются, стремясь к 100%.

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

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

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

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

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

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

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

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

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

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

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

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

Мощность

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

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

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

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

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

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

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

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

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

Пример 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%.

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

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

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

Логарифмический тренд 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 для больших массивов данных.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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