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

Предсказ в excel формула

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

​Смотрите также​ детально описано здесь.​ года) с учетом​ + a​50​ по диаграмме и​ в поле окна​«Статистические»​ встроенных статистических функций.​ Нам нужно будет​В поле​ этого окна данные​.​ диаграммы, о которых​ оператора​ тип аппроксимации. Можно​.​Прогнозирование – это очень​Нужная кнопка появится на​ сезонности:​y — объемы продаж;​включительно. Получается, нам​

​ в появившемся списке​ аргументов.​

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

​или​ В результате обработки​ найти разницу в​«Известные значения y»​ полностью аналогично тому,​Открывается окно аргументов оператора​ шла речь выше.​

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

​ПРЕДСКАЗ​ перепробовать все доступные​Открывается окно форматирования линии​ важный элемент практически​ ленте.​

​Общая картина составленного прогноза​x — номер периода;​ нужно будет его​ останавливаемся на значении​После того, как все​«Полный алфавитный перечень»​

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

  • ​ из шести видов​​ и заканчивая инженерией.​
  • ​ выбираем «Экспоненциальное сглаживание».​​График сезонности:​
  • ​ на графике (минимальный​​единиц. На горизонтальной​
  • ​В запустившемся окне выбора​​«OK»​
  • ​«ПРЕДСКАЗ»​​ это не удивительно,​
  • ​ её на число​​. В поле​

​ТЕНДЕНЦИЯ​уже описанным выше​

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

Выбор другого типа апроксимации в Microsoft Excel

​«OK»​ колебание небольшое, то​ результату сумму последнего​. Остальные поля оставляем​«OK»​. В поле​ в 2019 году​ листе, куда планируется​ не превышает 30%​;​ далеко не все​Заполняем диалоговое окно. Входной​ построить в три​ следующие статистические данные​ В поле​ подписи горизонтальной оси.​ ячейку, которая была​в нижней части​ все эти варианты,​ фактического периода.​ пустыми. Затем жмем​.​«Известные значения x»​

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

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

​Экспоненциальная​ пользователи знают, что​ интервал – диапазон​​ шага:​​ по продажам за​«Вперед на»​Открывается окно установки подписи​ выделена в первом​

​ применимые к конкретному​​В списке операторов Мастера​ на кнопку​Результат обработки данных выводится​вводим адрес столбца​ 4637,8 тыс. рублей.​ Жмем на кнопку​ периодов. То есть,​;​

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

​Мы перемещаемся к окну​​ случаю, можно считать​ функций выделяем наименование​«OK»​ на монитор в​«Год»​Но не стоит забывать,​«Вставить функцию»​ при анализе периода​Степенная​

​ Excel имеет в​ Фактор затухания –​ функцию регрессии.​Рассчитаем значение линейного тренда.​«1»​ в поле данного​ перед запуском​ аргументов вышеуказанной функции.​ относительно достоверными.​

​«ЛГРФПРИБЛ»​.​ указанной ранее ячейке.​

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

    ​. Жмем на кнопку​ окна, а затем​Мастера функций​ Она имеет всего​​Автор: Максим Тютюшев​​. Делаем щелчок по​

​ соответствующее количество полей​​ узнать результаты вычисления​​«OK»​​ значение линейного тренда.​​ составляет 4682,1 тыс.​ ячейку, где находится​ до прогнозируемого периода​. В категории​ более чем на​Линейная фильтрация​ эффективности мало чем​

​ – ссылка на​​ определенный период.​​ ячейке D15 Используем​​ углу окна.​​«X»​ аргумента​

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

Изменение аргумента функции ПРЕДСКАЗ в Microsoft Excel

​ выходного диапазона. Сюда​ прогноз возможен только​Выделяем ячейку с формулой​ продлен на указанную​ Затем жмем на​равно​«X»​ актуален данный вопрос​ нем вносим данные​

​ на 2019 год.​​ оператором​

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

​ прогноз. В нашем​ срока, за который​«ПРЕДСКАЗ»​​ случае он будет​​ линейную аппроксимацию.​ это за инструменты,​ программа поместит сглаженные​ при индивидуализации модели​ D15 и соседнюю,​​ длину с помощью​​ кнопку​338​

​следует указать значение​

​ для процедуры прогнозирования.​​ точно так, как​​ Устанавливаем знак​​ТЕНДЕНЦИЯ​​ случае это 2019​ накапливалась база данных.​​, а затем щелкаем​​ относительно достоверным, если​​В блоке настроек​​ и как сделать​​ уровни и размер​​ прогнозирования. Ведь разные​ правую, ячейку E15​​ линии тренда.​​«OK»​​.​​ аргумента, функцию от​ В Экселе есть​ это делали, применяя​«=»​

​незначительны, но они​ год. Поле​Урок:​

​ по кнопке​ за это время​«Прогноз»​ прогноз на практике.​ определит самостоятельно. Ставим​ временные ряды имеют​ так чтобы активной​

    ​Урок:​.​Если все-таки был выбран​​ которого нам следует​​ несколько способов, с​ функцию​​в любую пустую​​ имеются. Это связано​«Константа»​​Экстраполяция в Excel​​«OK»​​ не будет никаких​​в поле​

Результат функции ТЕНДЕНЦИЯ в Microsoft Excel

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

​Чтобы посмотреть общую картину​ Ctrl + Shift​Итак, мы рассмотрели простейшие​ повторяем ту же​ в которой содержится​ нужное число, а​ Давайте рассмотрим их​«OK»​ в которой содержится​ разные методы расчета:​«OK»​ –​ поле​ которых не было​

​ выявление текущей тенденции,​ ОК. Результаты анализа:​ с графиками выше​ + Enter (чтобы​​ примеры экстраполяции для​​ процедуру, то есть,​ искомый аргумент, то​ можно указать координаты​ на конкретных примерах.​.​ фактическая величина прибыли​ метод линейной зависимости​

    ​.​ТЕНДЕНЦИЯ​«X»​​ в предыдущих периодах.​​, так как нам​ и определение предполагаемого​​Для расчета стандартных погрешностей​​ описанного прогноза рекомендуем​ ввести массив функций​​ таблиц и для​​ жмем на кнопку​

Результат функции РОСТ в Microsoft Excel

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

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

​ вперед. Кроме того,​ определенный момент времени​ ‘диапазон прогнозных значений’)/​ изучить показатели во​ сразу 2 значения​ПРЕДСКАЗ​Теперь наш график подготовлен​ для любого другого​ предпочтительнее. Если мы​ задачей которой является​Ставим знак​«+»​ЛИНЕЙН​ сумма прогнозируемой прибыли​ во многом напоминает​ В нашем случаем​Экстраполяцию для табличных данных​ можно установить галочки​ в будущем.​ ‘размер окна сглаживания’).​ времени. Временной ряд​ коефициентов для (a)​​, а во втором​​ и можно, непосредственно,​ числа. Например, искомое​

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

Итоговый расчет функции ЛИНЕЙН в Microsoft Excel

​ Для этого в​ решать и гораздо​ чего на ленте​518​ для другого аргумента​

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

​ пределами известной области.​ содержит значение выручки​​ знак​​ линейной зависимости, используемым​ методе расчета, 4637,8​=ТЕНДЕНЦИЯ(Известные значения_y;известные значения_x; новые_значения_x;[конст])​ этот показатель в​ к категории статистических​

​ достоверности аппроксимации (R^2)»​ линии тренда.​

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

    ​Урок:​ менять формулу, а​​ метод столь востребован​​ период. Ставим знак​. Так как между​​ТЕНДЕНЦИЯ​​Ещё одной функцией, с​

Итоговый расчет функции ЛГРФПРИБЛ в Microsoft Excel

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

​=ЛИНЕЙН(Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])​​ Экселе, является оператор​полностью соответствуют аналогичным​

​ на него. Это​– это аргумент,​ на кнопку​ показателю за предыдущие​ графику – «Добавить​ объемы продаж, производства​ фактических данных от​ при наличии всех​и жмем на​ построения линии тренда.​ чтобы указать координаты​ табличных значений, так​ Ставим знак минус​ который нужно сделать​Последние два аргумента являются​ РОСТ. Он тоже​ элементам оператора​ позволит в будущем​ значение функции для​«Закрыть»​ 12 лет.​ линию тренда»).​

Показатели инвестиционного проекта

Для ответа на вышеприведённые вопросы используют следующие показатели эффективности инвестиционного проекта:

  • срок окупаемости проекта (обычно в месяцах)
  • чистая приведённая стоимость (net present value, NPV)
  • внутренняя норма доходности (IRR).

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

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

Кд = 1 / (1 + Ставка дисконтирования)^Номер периода

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

  • стоимостью привлекаемого капитала инвестора;
  • прогнозной инфляцией;
  • премией за риск проекта.

Коэффициент дисконтирования используется для расчёта показателя Чистая приведённая стоимость (net present value -NPV), который по сути является совокупным дисконтированным денежным потоком. Проект считается экономически выгодным, если его NPV не отрицательна. Нулевое значение NPV говорит о том, что проект принесет прибыль, достаточную для выплаты процентов по привлечённому капиталу с учётом инфляции. Чем выше NPV проекта, тем он привлекательнее (при учете рисков).

Для того чтобы получить более универсальную оценку привлекательности инвестиционного проекта, можно рассчитать третий показатель: внутреннюю норму доходности (IRR) – значение ставки дисконтирования, при которой NPV равен нулю (то есть проект отобъёт вложенные в него средства). Считается, что проект приемлем, если расчётное значение IRR больше ставки дисконтирования. Кроме того, этот показатель удобно использовать при сравнении альтернативных инвестиционных проектов: для каждого рассчитывается показатель IRR и предпочтение отдаётся проекту с наибольшим IRR.

Функция НАИБОЛЬШИЙ

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

Синтаксис: =НАИБОЛЬШИЙ(массив; n), где

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

Массив или диапазон НЕ обязательно должен быть отсортирован.

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

Третье наибольшее значение

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

Различные ранги у равных элементов

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

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

Как в Эксель построить тренд

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

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

Чтобы правильно строить трендовые линии, нужно соблюдать функциональную зависимость y=f(x) . Для получения корректного прогноза в столбец А вносится информация о временном периоде, а в столбец В — цена в указанный промежуток.

«Вставка»-«Диаграммы»-«Точечная»Построение графика выполняется по следующему алгоритму:

  1. Первым действием нужно выделить диапазон данных , например это А1:В9, затем активировать инструмент: «Вставка»-«Диаграммы»-«Точечная»-«Точечная с гладкими кривыми и маркерами».
  2. После открытия графика пользователю станет доступна еще одна панель управления данными , на которой нужно выбрать следующее: «Работа с диаграммами»-«Макет»-«Линия тренда»-«Линейное приближение».
  3. Следующим шагом требуется выполнить двойной клик по образовавшейся линии тенденции в Excel . Когда появиться вспомогательное окно, отметить птичкой опцию «показывать уравнение на диаграмме».

Линейное приближение

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

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

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

Добавление тренда на диаграмму происходит следующим образом:

Линейное приближение excel

  1. Перейти во вкладку «Работа с диаграммами» , затем выбрать раздел «Макет»-«Анализ» и после подпункт «Линия тенденции» . Появится выпадающий список, в котором необходимо активировать строку «Линейное приближение».
  2. Если все выполнено правильно, в области построения диаграмм появится кривая линия черного цвета . По желанию цветовую гамму можно будет изменить на любую другую.

Этот способ поможет создать и построить тренд в Excel 2016 или более ранних версиях.

как добавить линию тренда в ексель

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

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

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

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

Работа с диаграммами

Необходимо запомнить следующее:

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

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

ГистограммаДля этого выполняем последовательность действий:

  1. Вызвать для графика контекстное меню и выбрать «Изменить тип диаграммы» .
  2. Появится новое окно с настройками , в котором требуется найти опцию «Гистограмма» и после выбрать подвид с группировкой.

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

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

Для визуального отображения нужно сделать следующее:

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

  1. Перевести гистограмму в простой точечный график с гладкими кривыми и маркерами . Процесс выполняется через пункт контекстного меню «Изменить тип диаграммы…».
  2. Выполнить двойной клик по прямой образовавшейся тенденции , задать ей параметр прогноза назад на 12,0 и сохранить изменения.

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

Excel для финансистов: финансовые функции

финансовые функции Excel

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

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

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

Наиболее популярные финансовые функции Excel

1. БС

(англ. FV) – возвращает будущую стоимость инвестиций при условиях постоянной процентной ставки, периодических постоянных платежей или единого общего платежа (в виде начальной инвестиции, определяемой аргументом «пс»): = БС(ставка;кпер;плт;[пс];[тип]) , где:

  • «ставка» – процентная ставка за период (можно использовать ставку простого процента в случае с депозитами / вкладами): например, если ставка 6% годовых и выплаты производятся ежемесячно, процентная ставка за месяц составит 6%/12, при ежеквартальных выплатах аргумент «ставка» будет равен 6%/4;
  • «кпер» – общее количество периодов для ежегодного платежа: например, в случае кредита на 5 лет и ежемесячных платежах, аргумент «кпер» будет равен 5*12;
  • «плт» – постоянная выплата за каждый период (выплаты – отрицательные значения, поступления – положительные значения): например, если ежемесячный платеж по кредиту составляет 10 000 руб., то аргумент «плт» будет равен -10 000.
  • «пс» – приведенная стоимость или первоначальная (инвестированная или вложенная) сумма (если аргумент опущен, предполагается значение 0 и необходимо обязательно указать аргумент «плт»),
  • «тип» – срок выплаты в начале (1) или в конце периода (0) (если аргумент «тип» опущен, предполагается значение 0, т.е. в конце периода).

2. БЗРАСПИС

(англ. FVSCHEDULE) – возвращает будущую стоимость инвестиций после начисления ряда сложных процентов (с переменной процентной ставкой, подойдет для вкладов с капитализацией процентов): =БЗРАСПИС(первичное;план) , где:

  • «первичное» – стоимость инвестиции на текущий момент,
  • «план» – массив применяемых процентных ставок.

3. ПС

(англ. PV) – возвращает приведенную (текущую) стоимость инвестиции или займа (на основе постоянной процентной ставки): =ПС(ставка; кпер; плт; [бс]; [тип] ), где:

  • «ставка» – процентная ставка за период (можно использовать ставку простого процента в случае с депозитами / вкладами): например, если ставка 6% годовых и выплаты производятся ежемесячно, процентная ставка за месяц составит 6%/12, при ежеквартальных выплатах аргумент «ставка» будет равен 6%/4;
  • «кпер» – общее количество периодов для ежегодного платежа: например, в случае кредита на 5 лет и ежемесячных платежах, аргумент «кпер» будет равен 5*12;
  • «плт» – постоянная выплата за каждый период (выплаты – отрицательные значения, поступления – положительные значения): например, если ежемесячный платеж по кредиту составляет 10 000 руб., то аргумент «плт» будет равен -10 000.
  • «бс» – будущая стоимость или желаемый остаток средств после последнего платежа (если аргумент опущен, предполагается значение 0 и необходимо обязательно указать аргумент «плт»),
  • «тип» – срок выплаты в начале (1) или в конце периода (0) (если аргумент «тип» опущен, предполагается значение 0, т.е. в конце периода).

4. ЧПС

(англ. NPV) – возвращает чистую приведенную или дисконтированную стоимость инвестиции при условии серии периодических денежных потоков и с использованием ставки дисконтирования: =ЧПС(ставка; значение1; [значение2],…) , где:

  • «ставка» – ставка дисконтирования за один период;
  • «значение1, значение2,…» – предполагаемые выплаты и поступления (должны быть равномерно распределены во времени, при этом выплаты должны осуществляться в конце каждого периода).

5. ЧИСТНЗ

(англ. XNPV) – возвращает чистую приведенную стоимость для денежных потоков, не обязательно являющихся периодическими: =ЧИСТНЗ(ставка;значения;даты), где:

  • «ставка» – ставка дисконтирования за один период;
  • «значение1, значение2,…» – предполагаемые выплаты и поступления (денежные потоки, соответствующие графику платежей, приведенному в аргументе “даты”. Если первое значение является затратами или выплатой, оно должно быть отрицательным. Все последующие выплаты дисконтируются на основе 365-дневного года. Ряд значений должен содержать по крайней мере одно положительное и одно отрицательное значение);
  • «даты» – график дат платежей, который соответствует платежам для денежных потоков.

6. ПЛТ

(англ. PMT) – возвращает сумму периодического платежа с постоянным процентом и постоянной суммой платежа (подходит для расчета платежей по аннуитету): =ПЛТ(ставка; кпер; пс; [бс]; [тип]), где:

  • «ставка» – процентная ставка за период (можно использовать ставку простого процента в случае с депозитами / вкладами): например, если ставка 6% годовых и выплаты производятся ежемесячно, процентная ставка за месяц составит 6%/12, при ежеквартальных выплатах аргумент «ставка» будет равен 6%/4;
  • «кпер» – общее количество периодов для ежегодного платежа: например, в случае кредита на 5 лет и ежемесячных платежах, аргумент «кпер» будет равен 5*12;
  • «пс» – приведенная стоимость или первоначальная (инвестированная или вложенная) сумма (если аргумент опущен, предполагается значение 0),
  • «бс» – будущая стоимость или желаемый остаток средств после последнего платежа (если аргумент опущен, предполагается значение 0),
  • «тип» – срок выплаты в начале (1) или в конце периода (0) (если аргумент «тип» опущен, предполагается значение 0, т.е. в конце периода).

7. ПРПЛТ

(англ. IPMT) – возвращает сумму процентных платежей за указанный период только в том случае, если платежи в каждом периоде осуществляются равными частями: =ПРПЛТ(ставка;период;кпер;пс;[бс];[тип]) , где:

  • «ставка» – процентная ставка за период (можно использовать ставку простого процента в случае с депозитами / вкладами): например, если ставка 6% годовых и выплаты производятся ежемесячно, процентная ставка за месяц составит 6%/12, при ежеквартальных выплатах аргумент «ставка» будет равен 6%/4;
  • «период» – период, для которого требуется найти платежи по процентам (число в интервале от 1 до аргумента “кпер”);
  • «кпер» – общее количество периодов для ежегодного платежа: например, в случае кредита на 5 лет и ежемесячных платежах, аргумент «кпер» будет равен 5*12;
  • «пс» – приведенная стоимость или первоначальная (инвестированная или вложенная) сумма (если аргумент опущен, предполагается значение 0),
  • «бс» – будущая стоимость или желаемый остаток средств после последнего платежа (если аргумент опущен, предполагается значение 0),
  • «тип» – срок выплаты в начале (1) или в конце периода (0) (если аргумент «тип» опущен, предполагается значение 0, т.е. в конце периода).

8. СТАВКА

(англ. RATE) – возвращает ставку процентов по аннуитету за один период: =СТАВКА(кпер; плт; пс; [бс]; [тип]; [прогноз] ), где:

  • «кпер» – общее количество периодов для ежегодного платежа: например, в случае кредита на 5 лет и ежемесячных платежах, аргумент «кпер» будет равен 5*12;
  • «плт» – постоянная выплата за каждый период (выплаты – отрицательные значения, поступления – положительные значения): например, если ежемесячный платеж по кредиту составляет 10 000 руб., то аргумент «плт» будет равен -10 000;
  • «пс» – приведенная стоимость или первоначальная (инвестированная или вложенная) сумма (если аргумент опущен, предполагается значение 0);
  • «бс» – будущая стоимость или желаемый остаток средств после последнего платежа (если аргумент опущен, предполагается значение 0, а аргумент «пс» является обязательным);
  • «тип» – срок выплаты в начале (1) или в конце периода (0) (если аргумент «тип» опущен, предполагается значение 0, т.е. в конце периода);
  • «прогноз» – предполагаемая величина ставки (если аргумент “прогноз” опущен, предполагается значение 10%).
Читайте также:  Сценарии в Excel позволяют прогнозировать результат

9. ЭФФЕКТ

(англ. EFFECT) – возвращает фактическую (или эффективную) годовую процентную ставку, если заданы номинальная годовая процентная ставка и количество периодов в году, за которые начисляются сложные проценты: =ЭФФЕКТ(номинальная_ставка;кол_пер), где:

  • «номинальная_ставка» — номинальная процентная ставка;
  • «кол_пер» – количество периодов в году, за которые начисляются сложные проценты.

10. ДОХОД

(англ. YIELD) – возвращает доходность ценных бумаг (облигаций), по которым производятся периодические выплаты процентов: =ДОХОД(дата_согл; дата_вступл_в_силу; ставка; цена; погашение, частота; [базис] ), где:

  • «дата_согл» — дата расчета за ценные бумаги (дата продажи ценных бумаг покупателю, более поздняя, чем дата выпуска);
  • «дата_вступл_в_силу» — срок погашения ценных бумаг (момент, когда истекает срок действия ценных бумаг);
  • «ставка» — годовая процентная ставка для купонов по ценным бумагам;
  • «цена» — цена ценных бумаг на 100 рублей номинальной стоимости;
  • «погашение» — выкупная стоимость ценных бумаг на 100 рублей номинальной стоимости;
  • «частота» — кол-во выплат по купонам за год (для ежегодных – 1, для полугодовых — 2, для ежеквартальных — 4);
  • «базис» — используемый способ вычисления дня (если 0 или опущен, то используется американский (NASD) 30/360).

11. ВСД

(англ. IRR) – возвращает внутреннюю ставку доходности для потоков денежных средств (для платежей (отрицательные величины) и доходов (положительные величины), которые имеют место в следующие друг за другом и одинаковые по продолжительности периоды): =ВСД(значения; [предположения]) , где:

  • «значения» – массив или ссылка на ячейки, содержащие ряд денежных выплат (отрицательные значения) и поступлений (положительные значения), происходящих в регулярные периоды времени (по крайней мере одна положительная и одна отрицательная величина);
  • «предположение» — величина, предположительно близкая к результату ВСД ( в большинстве случаев нет необходимости задавать аргумент “предположение”. Если он опущен, предполагается значение 10%).

12. МВСД

(англ. MIRR) – возвращает модифицированную внутреннюю ставку доходности, учитывая процент от реинвестирования средств (при котором положительные и отрицательные денежные потоки имеют разные значения ставки): =МВСД(значения;ставка_финанс;ставка_реинвест) , где:

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

Дополнительные финансовые функции Excel

13. ИНОРМА (англ. INTRATE) – возвращает процентную ставку для полностью инвестированных ценных бумаг: =ИНОРМА(дата_согл;дата_вступл_в_силу;инвестиция;погашение;[базис]).

14. ЧИСТВНДОХ (англ. XIRR) – возвращает внутреннюю норму прибыли для графика поступлений денежных средств, не обязательно носящих периодический характер: =ЧИСТВНДОХ(значения;даты[;предположение]).

15. ДОХОДСКИДКА (англ. YIELDDISC) – возвращает годовой доход по ценным бумагам, на которые сделана скидка (например, по казначейским векселям): =ДОХОДСКИДКА(дата_согл;дата_вступл_в_силу;цена;погашение;[базис]).

16. ДОХОДПОГАШ (англ. YIELDMAT) – возвращает годовой доход по ценным бумагам, проценты по которым выплачиваются в срок погашения: =ДОХОДПОГАШ(дата_согл;дата_вступл_в_силу;дата_выпуска;ставка;цена;[базис]).

17. СКИДКА (англ. DISC) – возвращает норму скидки для ценных бумаг: =СКИДКА(дата_согл;дата_вступл_в_силу;цена;погашение;[базис]).

18. ЦЕНА (англ. PRICE) – возвращает цену за 100 рублей номинальной стоимости ценных бумаг, по которым производится периодическая выплата процентов: =ЦЕНА(дата_согл;дата_вступл_в_силу;ставка;доход;погашение,частота;[базис]).

19. АСЧ (англ. SYD) – возвращает величину амортизации актива за данный период, рассчитанную по сумме чисел лет срока полезного использования: =АСЧ(нач_стоимость;ост_стоимость;время_эксплуатации;период).

20. ЦЕНАКЧЕК (англ. TBILLPRICE) – возвращает цену за 100 рублей номинальной стоимости для казначейского векселя: =ЦЕНАКЧЕК(дата_согл;дата_вступл_в_силу;скидка).

Осваиваем прогнозирование временных рядов

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

Время от времени специалистам по SQL Server приходится делать перспективные оценки будущей стоимости, например прогнозы доходов или продаж. Организации иногда применяют технологию интеллектуального анализа данных (data-mining) в построении моделей прогнозирования, чтобы предоставить такие оценки. Разобравшись в основных понятиях и некоторых деталях, вы начнете с успехом использовать возможности прогнозирования в SQL Server Analysis Services (SSAS).

Методы прогнозирования

Существуют различные подходы к прогнозированию. Например, сайт Forecasting Methods (forecastingmethods.org) выделяет различные категории методов прогнозирования, включая казуальные (иначе называемые экономико-математическими), экспертное моделирование (субъективные), временные ряды, искусственный интеллект, рынок прогнозов, вероятностное прогнозирование, моделирование прогнозирования, а также метод прогнозирования на основе референсных классов. Веб-сайт Forecasting Principles (www.forecastingprinciples.com) дает представление о методах в виде методологического дерева, прежде всего разделяя субъективные методы (то есть методы, используемые при недостатке имеющихся данных для количественного анализа) и статические (то есть методы, используемые, когда доступны соответствующие числовые данные). В этой статье я остановлюсь на прогнозировании временных рядов, типе статического подхода, в котором накопленных данных достаточно для прогнозирования показателей.

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

Понимание основ прогнозирования временных рядов

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

Линейный тренд на графике временных интервалов
Экран 1. Линейный тренд на графике временных интервалов

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

  • Базовый уровень (Base level). Базовый уровень, как правило, определяется как среднее значение временного ряда. В некоторых моделях прогнозирования базовый уровень обычно определяется как начальное значение данных ряда.
  • Тренд (Trend). Тренд, как правило, показывает, как временные ряды изменяются от одного периода к другому. На примере, представленном на экране 1, число безработных имеет тенденцию роста с начала 2008 года до января 2010 года, после чего линия тренда направляется вниз. Информацию о совокупности выборочных данных, использованных для построения диаграмм в данной статье, можно найти во врезке «Расчет уровня безработицы».
  • Сезонные колебания. Некоторые значения имеют тенденцию роста или снижения в зависимости от определенных периодов времени, это может быть день недели или месяц в году. Можно рассмотреть пример с продажами в розничных магазинах, пик которых часто приходится на рождественский сезон. В случае с безработицей мы видим сезонный тренд с наивысшими показателями в январе и июле и низкими показателями в мае и октябре, как показано на экране 2.
  • Шум (Noise). Некоторые модели прогнозирования включают четвертую характеристику, шум, или ошибку, которая относится к случайным колебаниям и неравномерным движениям в данных. Шум мы здесь рассматривать не будем.
Сезонный тренд на графике временных интервалов
Экран 2. Сезонный тренд на графике временных интервалов

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

Прогнозируемое значение = Базовый уровень + Тренд + Сезонная составляющая

Определение базового уровня и тренда

Единственный способ определить базовое значение и тренд – это воспользоваться методом регрессии. Под словом «регрессия» здесь понимается рассмотрение взаимосвязи между переменными. В данном случае существует взаимосвязь между независимой переменной времени и зависимой переменной числа безработных. Обратите внимание, что независимая переменная иногда называется прогнозирующим параметром.

Воспользуйтесь таким инструментом, как Microsoft Excel, чтобы применить метод регрессии. Например, вы можете выполнить автоматический подсчет в Excel и добавить линию тренда к графику временных рядов, используя меню Trendline на вкладке Chart Tools Layout или вкладке PivotChart Tools Layout в панели Excel 2010 или Excel 2007. На экране 1 я добавил прямую линию тренда, выбрав режим Linear trendline в меню Trendline. Затем я выбрал More Trendline Options в меню Trendline, а потом – параметры Display Equation on chart («Показывать уравнение на диаграмме») и Display R-squared value on chart («Показывать на диаграмме значение коэффициента детерминации»), см. экран 3.

Параметры тренда в Excel
Экран 3. Параметры тренда в Excel

Этот процесс подгонки линии тренда к накопленным данным называется линейной регрессией (linear regression). Как мы видим на экране 1, линия тренда рассчитывается в соответствии с уравнением, где определяется базовый уровень (8248,8) и тренд (104.67x):

y = 104,67x + 8248,8

Можно представить себе линию тренда как ряд связанных координат осей x-y, куда вы можете включить промежуток времени (то есть ось x) для получения значения (ось y). Excel определяет «лучшую» линию тренда, применяя метод наименьших квадратов (определяемый как R² на экране 1). Линия наименьших квадратов – это линия, которая минимизирует возведенное в квадрат расстояние по вертикали из каждой точки линии тренда к соответствующей точке линии. Среднеквадратические значения позволяют определить, что отклонения выше или ниже актуальной линии не уравновешивают друг друга. На экране 1 мы видим, что R² = 0,5039, то есть линейное соотношение объясняет 50,39 % изменений в статистике безработицы с течением времени.

Определение точной линии тренда в Excel часто включает в себя метод проб и ошибок, наряду с визуальным контролем. На экране 1 прямая линия тренда подходит не самым лучшим образом. Excel предлагает другие варианты линии тренда, которые вы видите на экране 3. На экране 4 я добавил линию скользящей средней за четыре периода, которая строится на основе среднего арифметического показателей текущего и последних установленных периодов временного ряда.

Линия скользящей средней
Экран 4. Линия скользящей средней

Кроме того, я добавил полиномиальную линию тренда, применив алгебраическое уравнение для построения линии. Заметьте, что полиномиальная линия тренда имеет значение R² — 0,9318, определяющее наилучшее соотношение в выражении связи между независимой и зависимой переменными. Однако более высокое значение R² не обязательно означает, что линия тренда обеспечит качество прогнозной оценки. Существуют другие методы расчета точных прогнозов, которые я вкратце опишу ниже. Некоторые варианты линии тренда в Excel (например, линейная, полиномиальная линии тренда) позволяют делать прогнозы вперед, а также в обратном направлении, с учетом количества периодов, с нанесением полученных значений на график. Кому-то может показаться странным выражение «прогноз в обратном направлении». Лучше всего представить это на примере. Предположим, что новый фактор — быстрое увеличение рабочих мест в государственном секторе (например, рабочие места в Homeland Defense в начале 2000-х годов, временные работники Бюро переписи населения США) — послужил причиной быстрого падения уровня безработицы. Вам нужно сделать прогноз темпов роста нового сектора рабочих мест в обратном направлении в течение нескольких месяцев, а затем пересчитать уровень безработицы, чтобы прийти к сглаженному показателю изменения.

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

Полиномиальная линия тренда
Экран 5. Полиномиальная линия тренда

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

Относительно регрессии важно сделать два замечания.

  • Как уже упоминалось выше, линейная регрессия включает одну независимую и одну зависимую переменную. Для понимания того, как дополнительные независимые переменные могут объяснить изменения в зависимой переменной, попробуйте построить модель множественной регрессии. В контексте прогнозирования числа безработных в Соединенных Штатах вы можете увеличить R² (и точность прогноза), учитывая коэффициент роста экономики, населения США, а также рост числа нанятых работников. SSAS может вместить множество переменных (то есть регрессоров) в модель прогнозирования временных рядов.
  • Алгоритмы прогнозирования временных рядов, включая те, что используются в SSAS, позволяют вычислить автокорреляцию, которая является корреляцией между соседними значениями временного ряда. Модель прогнозирования, которая непосредственно включает автокорреляцию, называется авторегрессивной (AR) моделью. Например, модель линейной регрессии выстраивает уравнение тренда на основе периода (например, 104,67 * x), в то время как в AR модели уравнение строится, исходя из предыдущих значений (например, -0,417 * безработных (-1) + 0,549 * занятых (-1)). AR модель потенциально увеличивает точность прогноза, так как учитывает дополнительную информацию сверх тренда и сезонной компоненты.

Учитываем сезонную составляющую

Сезонная компонента в структуре временного ряда обычно проявляется в связи либо с днем недели, либо с днем месяца, или же с месяцем в году. Как отмечалось выше, число безработных в США обычно растет и сокращается в установленный календарный год. Это верно даже при росте экономики, как показано на экране 2. Иными словами, чтобы сделать точный прогноз, вы должны учесть сезонную составляющую. Один общий подход заключается в применении метода сглаживания сезонных колебаний. В работе Practical Time Series Forecasting: A Hands-On Guide, Second Edition (CreateSpace Independent Publishing Platform, 2012) автор Галит Шмуели рекомендует использовать один из трех методов:

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

Базовый уровень и тренд определяются при расчете прогноза с учетом сглаженного временного ряда. Факультативно сезонная составляющая или корректировка могут вновь применяться к прогнозируемым значениям с учетом начальных значений сезонного фактора при работе с методом Хольта-Винтерса. Если вы хотите увидеть, как производятся расчеты с учетом фактора сезонности в Excel, введите в строке поиска в Интернете «метод Винтерса в Excel». Также развернутое объяснение метода Хольта-Винтерса можно найти в руководстве Wayne L. Winston Microsoft Office Excel 2007: Data Analysis and Business Modeling, Second Edition (Microsoft Press, 2007).

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

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

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

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

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

ошибка = прогнозируемое значение – фактическое значение

При таком определении ошибки есть два популярнейших метода для измерения точности: это средняя абсолютная ошибка, то есть mean absolute error (MAE) и средняя абсолютная ошибка в процентах, или mean absolute percentage error (MAPE). В методе MAE абсолютные значения ошибок прогнозирования суммируются, а затем делятся на общее число прогнозов. Методом MAPE рассчитывается среднее абсолютное отклонение от прогноза в процентах. Для просмотра примеров работы с этими и другими методами для измерения качества прогнозных оценок шаблон Excel (с образцом данных прогнозирования и коэффициентами точности) откройте веб-страницу Demand Metrics Diagnostics Template (demandplanning.net/DemandMetricsExcelTemp.htm).

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

Прогнозирование временных рядов в SSAS

Прогнозирование временных рядов впервые появилось в SSAS в 2005 году. Для вычисления прогнозных значений алгоритм временных рядов Microsoft (Microsoft Time Series) использовался единый алгоритм под названием autoregressive tree with cross prediction (ARTXP), или дерево с авторегрессией с перекрестным прогнозированием. ARTXP сочетает метод авторегрессии с интеллектуальным анализом данных decision tree (дерево решений), так что уравнение прогноза может измениться (имеется в виду разделение) на основе определенных критериев. Например, модель прогнозирования обеспечит лучшее соответствие (и большую точность прогноза), если сначала предпринять разделение по дате, а затем на основе значения независимой переменной, как показано на экране 6.

Пример дерева решения ARTXP в SSAS
Экран 6. Пример дерева решения ARTXP в SSAS

В SSAS 2008 в алгоритме Microsoft Time Series в дополнение к ARTXP начал использоваться алгоритм под названием autoregressive integrated moving average (ARIMA), интегрированное скользящее среднее с авторегрессией, для вычисления долгосрочных прогнозов. ARIMA считается отраслевым стандартом и может рассматриваться как сочетание процессов авторегрессии и моделей скользящего среднего. Кроме того, он анализирует исторические ошибки прогнозирования для улучшения модели.

По умолчанию алгоритм Microsoft Time Series сочетает результаты алгоритмов ARIMA и ARTXP для достижения оптимальных прогнозов. По желанию вы можете отменить данную функцию. Обратимся к документации SQL Server Books Online (BOL):

«Алгоритм тренирует две различные модели одних и тех же данных: одна модель использует алгоритм ARTXP, а другая – алгоритм ARIMA. Затем алгоритм объединяет результаты двух моделей, чтобы разработать наилучший прогноз, охватывающий переменное число временных срезов. Поскольку алгоритм ARTXP больше подходит для краткосрочных прогнозов, им желательно воспользоваться в начале ряда прогнозов. Однако если временные срезы, необходимые для прогнозирования, уходят в будущее, алгоритм ARIMA более значим».

При работе с прогнозированием временных рядов в SSAS вы должны постоянно иметь в виду следующее:

  • Хотя в SSAS есть закладка Mining Accuracy Chart, она не работает с интеллектуальным анализом данных для моделей временных рядов. В результате вам следует вручную измерять точность с помощью одного из методов, упомянутых здесь (например, MAE, MAPE), используя для расчетов такой инструмент, как Excel.
  • Редакция SSAS Enterprise Edition позволяет разделить один временной ряд на множество «исторических моделей», так что вам не нужно будет вручную разделять данные на наборы данных для тренировки модели и валидации, проверяя точность прогноза. С точки зрения конечного пользователя, есть только одна модель временных рядов, но вы можете сравнить фактические результаты с прогнозируемыми в рамках модели, как показано на экране 7. Если вы не работаете с редакцией Enterprise Edition или не хотите использовать эту функцию, прежде всего вручную разделите данные.
Сравнение актуальных результатов с результатами прогноза
Экран 7. Сравнение актуальных результатов с результатами прогноза

Следующий шаг

В этой статье я познакомил вас с основами прогнозирования временных рядов. Мы также рассмотрели некоторые детали базовых алгоритмов, чтобы они не стали препятствием в обработке временных рядов. В качестве следующего шага я предлагаю вам освоить инструменты прогнозирования временных рядов с SSAS. Образцом может послужить проект, в котором используются данные по безработице, приведенные в данной статье. Затем вы можете ознакомиться с электронным учебным пособием TechNet «Intermediate Data Mining Tutorial (Analysis Services – Data Mining)» (Промежуточные итоги интеллектуального анализа данных (Analysis Services – интеллектуальный анализ данных)) по адресу technet.microsoft.com/en-us/library/cc879271.aspx.

Читайте также:  Подсветка строки или столбца цветом в Excel для активной ячейки

Почему интеллектуальный анализ данных так непопулярен

В последнее десятилетие начали широко применяться технологии бизнес-аналитики business intelligence (BI), такие, как OLAP. В то же время Microsoft занялась продвижением другой BI–технологии, интеллектуального анализа данных, в таких популярных инструментах, как Microsoft SQL Server и Microsoft Excel. Однако технология интеллектуального анализа данных пока не стала ведущей. Почему? Хотя большинство людей может быстро ухватить суть ключевых понятий интеллектуального анализа данных, основные детали алгоритмов неразрывно связаны с математическими понятиями и формулами. Существует большое «расхождение» между высоким уровнем абстрактного понимания и детальным исполнением. В результате интеллектуальный анализ данных рассматривается ИТ-специалистами и промышленными клиентами как «черный ящик», что не способствует широкому внедрению технологии. Данная статья – моя попытка уменьшить «расхождение» в прогнозировании временных рядов.

Как построить график уравнения регрессии в Excel

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

Подготовительные работы

Перед использованием функции активируйте Пакет анализа. Перейдите:
Выберите раздел:
Далее:
Прокрутите окно вниз, выберите:
Отметьте пункт:
Открыв раздел «Данные», появится кнопка «Анализ».

Как пользоваться

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

  1. Y. Ячейки с данными влияние факторов на которые нужно установить. Это число покупателей. Адрес пропишите вручную или выделите соответствующий столбец;
  2. Х. Данные, влияние на которые нужно установить. В примере, нужно узнать, как температура влияет на количество покупателей. Поэтому выделяем ячейки в столбце «Температура».

Анализ

Нажав кнопку «ОК», отобразится результат.
Основной показатель — R-квадрат. Обозначает качество. Он равен 0,825 (82,5%). Что это означает? Зависимости, где показатель меньше 0,5 считается плохим. Поэтому в примере это хороший показатель. Y-пересечение. Число покупателей, если другие показатели равны нулю. 62,02 высокий показатель.

Прогнозирование в Excel сроков изготовления заказов.

Переходим непосредственно к рассмотрению примера.

Небольшой участок завода производит строительные металлоконструкции. Входным сырьем является листовой и профильный металлопрокат. Мощность участка в рассматриваемом периоде времени неизменна. В наличии есть статистические данные о сроках изготовления 13-и заказов (k=13) и количестве использованного металлопроката. Попробуем найти зависимость срока изготовления заказа от суммарной длины и массы профильного проката и суммарной площади и массы листового проката.

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

Примечательно, что найденная зависимость связывает в одной формуле параметры с различными единицами измерения. Это нормально. Найденные коэффициенты не являются безразмерными. Например, размерность коэффициента b – рабочие дни, а коэффициента m– рабочие дни/м.

1. Запускаем MS Excel и заполняем ячейки B4…F16 таблицы Excel исходными статистическими данными. В столбцы пишем значения переменных xi и фактические значения функции y, располагая данные, относящиеся к одному заказу в одной строке.

2. Так как функции ЛИНЕЙН и ЛГРФПРИБЛ — функции выводящие результаты в виде массива, то их ввод имеет некоторые особенности. Выделяем область размером 5×5 ячеек — ячейки I9…M13. Количество выделенных строк всегда — 5, а количество столбцов должно быть равно количеству переменных xi плюс 1. В нашем случае это: 4+1=5.

3. Нажимаем на клавиатуре клавишу F2 и набираем формулу

в ячейках I9…M13: =ЛИНЕЙН(F4:F16;B4:E16;ИСТИНА;ИСТИНА)

4. После набора формулы необходимо для ее ввода нажать сочетание клавиш Ctrl+Shift+Enter. (Знак «+» нажимать не нужно, в записи он означает, что клавиши нажимаются последовательно при удержании нажатыми всех предыдущих.)

5. Считываем результаты работы функции ЛИНЕЙН в ячейках I9…M13.

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

Общий вид уравнения аппроксимирующей функции y, представлен в объединенных ячейках I2…M2.

Значения коэффициентов b, m1, m2, m3, m4 считываем соответственно

в ячейке M9: b=4,38464164

в ячейке L9: m=0,002493053

в ячейке K9: m=0,000101103

в ячейке J9: m=-0,084844006

в ячейке I9: m=0,002428953

6. Для определения расчетных значений функции y — срока изготовления заказа — вводим формулу

в ячейку G4: =$L$9*B4+$K$9*C4+$J$9*D4+$I$9*E4+$M$9 =5,0

y=b+m1*x1+m2*x2+m3*x3+m4*x4

7. Копируем эту формулу во все ячейки столбца от G5 до G17 «протягиванием» и сверяем расчетные значения с фактическими. Совпадение очень хорошее!

8. Предварительные действия все выполнены. Уравнение аппроксимирующей функции y найдено. Пробуем выполнить прогнозирование в Excel срока изготовления нового заказа. Вписываем исходные данные.

8.1. Длину прокатных профилей по проекту x1 в метрах пишем

8.2. Массу прокатных профилей x в килограммах пишем

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

8.4. Общую массу листового проката x4 в килограммах вписываем

9. Расчетный срок изготовления заказа y в рабочих днях считываем

в ячейке G17: =$L$9*B17+$K$9*C17+$J$9*D17+$I$9*E17+$M$9 =25,4

Прогнозирование в Excel выполнено. На основе статистических данных мы рассчитали предположительный срок выполнения нового заказа — 25,4 рабочих дней. Остается выполнить заказ и сверить фактическое время с прогнозным.

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

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

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

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

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

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

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

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

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

y = 4,503x + 6,1333

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

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

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

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

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

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

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

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

  • где 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 степень.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ПРОГНОЗ Формула в Excel

Ниже приведена ПРОГНОЗНАЯ формула в Excel:

Аргументы функции ПРОГНОЗ в Excel:

  • X-: это числовое значение, где нам нужно прогнозировать новое значение y
  • Known_ Y-: это Known y_values ​​является зависимым массивом или диапазоном данных.
  • Known_ X-: это известные x_values ​​- это независимый массив или диапазон данных.

Особенности прогнозирования:

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

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

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

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

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

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

Перейти к формулам–> Выбрать дополнительную функцию–> Статистика–> Прогноз

Как использовать функцию ПРОГНОЗ в Excel?

ПРОГНОЗ Функция очень проста в использовании. Давайте теперь посмотрим, как использовать функцию FORECAST в Excel с помощью нескольких примеров.

Вы можете скачать этот шаблон функции прогноза Excel здесь — Шаблон функции прогноза Excel

ПРОГНОЗ в Excel — Пример № 1

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

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

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

За 2008 год название продукта «Grommer» заработало 6000000 продаж. Для того, чтобы предсказать следующий год, т. Е. Объем продаж в 2009 году, мы можем использовать ПРОГНОЗ в Excel, что показано с помощью приведенных ниже данных о продажах.

Таким образом, мы должны использовать ПРОГНОЗ в Excel, чтобы получить значение продаж в следующем году, где

D12 — х-числовое значение.

C3: C11 –Know_Y Значение.

D3: D11 — Известное_X значение.

Результат:

Доход от продаж для продукта Groomer за 2009 год составляет 6222222. Таким образом, мы можем легко предсказать доход от продаж для данного продукта.

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

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

  • Выберите ячейку из C3: D12.

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

  • Диаграмма была отображена, и прогнозируемое значение для 2009 года было показано в формате графика ниже.

Результат:

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

ПРОГНОЗ в Excel — Пример № 2

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

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

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

  • Вставить функцию прогноза

  • Выберите значение x как B14

  • Выберите известные у из C2: C13

  • Выберите известные х из B2: B13

= ПРОГНОЗ (В14, С2: С13, В2: В13)

Точно так же мы находим другие значения

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

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

Чтобы вставить график, выполните следующие действия.

  • Выберите месяц и столбец данных о продажах

  • Перейти, чтобы вставить меню. Выберите тип графика

  • Выберите формат графика графика 3D

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

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

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

Преимущества использования ПРОГНОЗА в Excel

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

Рекомендуемые статьи

Это было руководство к функции прогнозирования Excel. Здесь мы обсуждаем формулу прогноза и как использовать функцию прогноза вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи —

5.5. Тесты Чоу на наличие структурной стабильности во временно м ряде.

Диагностика выбросов в остатках является не единственным инструментом для выявления проблем, мешающих повышению точности прогностических моделей. В этом смысле, пожалуй, еще большее значение имеет тест Грегори Чоу на наличие структурной стабильности временного ряда. Поэтому следующим нашим шагом будет оценка на основе этого теста стабильности временного ряда за период с июня 1992 г. по апрель 2010 г. С методикой проведения этого теста можно познакомиться в алгоритме действий № 18.

Алгоритм действий № 18.

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

USDОLLАR = а USDОLLАR(-l) + b USDОLLАR(-2).

Шаг 1. Основные идеи, на которых строится тест Чоу на наличие структурной стабильности.

Тест Чоу на диагностирование структурной стабильности проводится следующим образом. Сначала берется временной ряд (например, данные по ежемесячному курсу доллара за период с июня 1992 г. по апрель 2010 г.), относительно которого выдвигается нулевая гипотеза о его структурной стабильности. Потом этот временной ряд делится на два периода наблюдений, граница между которыми проводится в момент времени t, т. е. в момент предполагаемых структурных изменений. (При необходимости ЕViеws позволяет проводить тест на наличие во временном ряде структурных изменений не только в какой-то один момент времени t, но и сразу для нескольких моментов, деля выборку на несколько соответствующих периодов.).

Читайте также: 

Проверка нулевой гипотезы идет путем сравнения разницы между суммой квадратов остатков, которую мы получаем, построив уравнение регрессии для единого временного ряда, и суммой квадратов остатков, получаемой при построении уравнения регрессии отдельно для каждого периода этого ряда. При этом в соответствии с методикой, предложенной Г. Чоу, определяется фактическое значение F-критерия и LR-статистики (lоg liкеlihооd rаtiо stаtistiс — соотношение статистики логарифмов правдоподобия). Если уровни значимости F-критерия и LR-статистики оказываются меньше 0,05, то тогда нулевая гипотеза о структурной стабильности временного ряда отвергается, а следовательно, влияние структурных изменений признается существенным.

Шаг 2. Проведение в ЕViеws теста Чоу на наличие структурной стабильности.

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

Чтобы в ЕViеws провести тест Чоу на наличие структурной стабильности, в меню оцененного уравнения регрессии необходимо воспользоваться опциями VIЕW/SТАВILIТY ТЕSТS/СНОW ВRЕАКРОINТ ТЕSТ… (смотреть/тесты на стабильность/тест Чоу на структурные изменения). В результате открывается диалоговое мини-окно СНОW ТЕSТS (тесты Чоу), в котором нужно указать конкретное наблюдение, когда произошло предполагаемое структурное изменение во временном ряде. В этом случае в мини-окно введено обозначение — 98m10, т. е. указан октябрь 1998 г. (рис. 5.8). Следовательно, можно посмотреть, произошли ли структурные изменения в октябре 1998 г.

Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Ехсеl и ЕViеws

После того как мы щелкнули кнопку ОК, в мини-окне СНОWТЕSТS появился вывод данных по результатам тестирования, которые приведены в табл. 5.10. Поскольку уровни значимости (Рrоbаbilitу) как F-критерия (F-stаtistiс), так и LR-статистики (Lоg liкеlihооd rаtiо — соотношения логарифмов правдоподобия) у нас оказались равны нулю, т. е. получились меньше критического значения, равного 0,05, следовательно, нулевая гипотеза о наличии структурной стабильности во временном ряде в октябре 1998 г. отвергается.

Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Ехсеl и ЕViеws

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

После того как была выдвинута нулевая гипотеза о структурной стабильности временного ряда, далее нам приходится решать несколько уравнений регрессии USDОLL АR = а USDОLL АR(1) + b USDОLL АR(2) как относительно единого временного ряда, так и относительно каждого выделенного периода наблюдений. Напомним, что в этом случае мы предположили, что структурная нестабильность возникла в октябре 1998 г., а потому временной ряд нами разделен на два периода: с июня 1992 г. по сентябрь 1998 г. и с октября 1998 г. по апрель 2010 г. Таким образом, мы находим сумму квадратов остатков, полученных как по единому уравнению регрессии для всего временного ряда, так и по остальным уравнениям регрессии (назовем их совокупность объединенной кусочно-линейной прогностической моделью) для каждого выделенного периода наблюдений.

Далее складываем суммы квадратов остатков, полученных в объединенной кусочно-линейной прогностической модели, по формуле.

Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Ехсеl и ЕViеws

После чего находим фактическое значение F-критерия по формуле.

Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Ехсеl и ЕViеws

Где SS ед ост — сумма квадратов остатков, полученных по единому уравнению регрессии для всего временного ряда;

п — количество наблюдений во всем временном ряде;

к — количество параметров в уравнении.

Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Ехсеl и ЕViеws

Затем в Ехсеl с помощью функции РРАСП находим значимость фактического F-критерия:

FРАСП(Fфакт); числитель степеней свободы; знаменатель степеней свободы) = FРАСП(42,111; 2; 209) = 0.

Таким образом, поскольку значимость фактического F-критерия равна нулю, это позволяет нам отвергнуть нулевую гипотезу о структурной стабильности временного ряда.

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

Где т — число периодов во временном ряде;

к — количество параметров в уравнении регрессии.

При значимости LR-статистики меньше 0,05 нулевая гипотеза о структурной стабильности отвергается.

Поскольку мы уже научились проводить тест Чоу на структурную стабильность временного ряда, то продолжим наше исследование уровня надежности модели USDОLLАR = а USDОLLАR(-l) + b USDОLLАR(-2), используя при этом рыночные данные за период с июня 1992 г. по апрель 2010 г. Теперь нашей задачей будет последовательное тестирование структурной стабильности временного ряда после резких скачков курса доллара, вошедших в топ-двадцатку самых волатильных месяцев (см. табл. 5.4).

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

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

Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Ехсеl и ЕViеws Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Ехсеl и ЕViеws

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

Судя по табл. 5.11, начиная с октября 1998 г. и по июнь 2000 г. в исследуемом временном ряде наблюдаются структурные изменения. Однако с конца 1998 г. и до середины 2000 г. волатильность на валютном рынке стала понемногу затухать, а к июлю 2000 г. тест Чоу вновь стал уверенно показывать наличие структурной стабильности. Впрочем, за одним-единственным исключением: после резкого роста доллара в январе 2009 г. на 18,7 % — в ходе так называемой плавной девальвации рубля — тест Чоу вновь выявил структурную нестабильность временного ряда, которая, впрочем, восстановилась уже в следующем месяце.

Вызывает большие сомнения, что 26,8 % — ное повышение курса доллара в августе 1998 г. не привело к структурным изменениям во временном ряде. Тем не менее уровни значимости F-критерия и LR-статистики, полученные в ходе тестирования, оказались в августе 1998 г. существенно выше 0,05. Это объясняется тем, что в табл. 5.6 представлены результаты тестирования для статистической модели, построенной на базе данных за период с июня 1992 г. по апрель 2010 г. При такой базе данных прирост курса доллара в августе 1998 г. на 1,67 руб. не выглядит чем-то экстраординарным, хотя сразу после августовского дефолта столь значительный взлет американской валюты буквально шокировал участников рынка.

Посмотрим, что произойдет, если мы возьмем в качестве базы данных период с июня 1992 г. по август 1998 г., т. е. фактически смоделируем ситуацию реального прогнозирования. С этой целью представим, что мы делаем прогноз на сентябрь 1998 г. в августе 1998 г., а потому более поздней информацией по курсу доллара не обладаем. Тем более что мы уже умеем быстро менять нашу базу данных (см. алгоритм действий № 15 «Как в ЕViеws можно быстро изменить выборку данных»).

После того как мы в очередной раз изменили выборку, у нас появилась возможность построить прогноз на сентябрь 1998 г. на основе рыночных данных (по курсу доллара на конец каждого месяца) за период с июня 1992 г. по август 1998 г. С этой целью мы сначала решаем уравнение регрессии (см. алгоритм действий № 6 «Как решить уравнение регрессии в ЕViеws»), а потом делаем прогноз и соответственно сразу же находим остатки (см. алгоритм действий № 8 «Как оценить точность статистической модели в ЕViеws»). Вывод данных по этому уравнению регрессии представлен в табл. 5.12.

Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Ехсеl и ЕViеws

Согласно прогнозу, составленному по этому уравнению регрессии, курс американского доллара к концу сентября 1998 г. должен был вырасти до 9 руб. 3 коп. Однако в действительности стоимость американской валюты к тому времени взлетела до 16 руб. 6 коп., т. е. оказалась выше прогнозируемого значения на 7 руб. 3 коп. Теперь посмотрим, сможем ли мы диагностировать структурные изменения в августе 1998 г., не обращаясь при этом к более поздней рыночной информации.

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

Алгоритм действий № 19.

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

USDОLLАR = а USDОLLАR(-1) + b USDОLLАR(-2).

Шаг 1. Основные идеи, на которых построен тест Чоу на точность прогноза.

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

Для оценки результатов теста ЕViеws сообщает две статистики: величины F-критерия и LR-статистики. Расчет F-критерия основан на сравнении суммы квадратов остатков, полученных для моделей, основанных на всей и неполной выборках (см. формулу (5.6)), а LR-статистики — на сравнении соотношения ограниченного и неограниченного максимума логарифма правдоподобия. При этом в случае если уровень значимости F-критерия и LR-статистики меньше 0,05, нулевая гипотеза отвергается. Некоторые математические подробности по этому тесту приводятся ниже.

Шаг 2. Проведение в ЕViеws теста Чоу на точность прогноза.

Чтобы в ЕViеws получить результаты теста Чоу на точность прогноза, необходимо выбрать следующие опции: VIЕW/SТАВILIТY ТЕSТS/СНОW FОRЕСАSТ ТЕSТ… (посмотреть/тесты на стабильность/тест Чоу на точность прогноза). После чего в появившемся диалоговом мини-окне СНОW ТЕSТS мы указываем прогнозируемое наблюдение — 98m08, т. е. август 1998 г. (рис. 5.9). Таким образом, все остальные наблюдения у нас попадут в неполную выборку охватывающую период с июня 1992 г. по июль 1998 г.

Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Ехсеl и ЕViеws

Если в диалоговом мини-окне СНОWТЕSТS мы щелкнем кнопку ОК, то получим готовый вывод данных с результатами теста Чоу на точность прогноза. Эти данные поместим в табл. 5.13, из которой следует, что уровень значимости как F-критерия, так LR-статистики у нас оказался равен нулю. Следовательно, нулевая гипотеза о структурной стабильности во временном ряде отвергается и делается вывод о значимости структурных изменений во временном ряде, произошедших в августе 1998 г. Таким образом, вывод о наличии структурных изменений зависит не только от этих изменений, но и от объема взятой нами выборки.

Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Ехсеl и ЕViеws Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Ехсеl и ЕViеws

Некоторые математические подробности для теста Чоу на точность прогноза.

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

При этом вычисления делаются по следующей формуле:

Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Ехсеl и ЕViеws

Где SS1 — сумма квадратов остатков, полученных по уравнению регрессии, построенному на всей выборке;

SS2 — сумма квадратов остатков, полученных по уравнению регрессии, построенному на неполной выборке;

Т1 — количество наблюдений в неполной выборке;

Т2 — количество прогнозируемых наблюдений, т. е. наблюдений, не вошедших в неполную выборку;

к — количество параметров в уравнении регрессии.

Таким образом, в нашем случае фактический F-критерий в тесте Чоу на точность прогноза относительно прогнозируемого наблюдения — августа 1998 г. будет иметь следующее значение:

Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Ехсеl и ЕViеws

Далее находим уровень значимости Fфакт с помощью функции в Ехсеl РРАСП(200,28; 1; 70) = 0. Поскольку уровень значимости Fфакт равен нулю, то, следовательно, нулевая гипотеза отвергается.

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

Следует иметь в виду, что при нулевой гипотезе об отсутствии структурных изменений LR-статистика имеет асимптотическое 2 (хи-квадрат) распределение со степенями свободы, равными количеству прогнозируемых наблюдений. В том случае, если уровень значимости LR-статистики оказывается меньше 0,05, нулевая гипотеза о структурной стабильности отвергается.

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

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

Дальнейшие улучшения

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

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

Функция Excel FV

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

Синтаксис и аргументы функции

БС (ставка, кол-во, вып., [Pv], [тип])

(1) Оценить: Необходимые. Процентная ставка за период. Это постоянно на протяжении всей жизни инвестиции.

Допустим, вы получаете годовую процентную ставку 6%, вы можете изменить его следующим образом:

  • Для полугодовых платежей вам необходимо преобразовать его в полугодовую процентную ставку: 3% (= 6% / 2);
  • Для ежеквартальных выплат вам необходимо конвертировать ее в квартальную процентную ставку: 1.5% (= 6% / 4);
  • Для ежемесячных выплат вам необходимо конвертировать ее в ежемесячную процентную ставку: 0.5% (= 6% / 12).

(2) Кпер: Необходимые. Общее количество периодов выплат. Предположим, вам нужно заплатить за 3 лет, вы можете изменить его следующим образом:

  • Для ежеквартальных платежей общее количество периодов платежей составляет 12 (= 3 * 4);
  • Для ежемесячных платежей общее количество периодов платежей составляет 36 (= 3 * 12).

(3) ПМТ: Необходимые. Постоянная оплата в каждом периоде. Это закреплено на всю жизнь инвестирования.

(4) Пв: Необязательный. Текущая стоимость ваших инвестиций или единовременный платеж в настоящее время. Если он не указан, функция FV рассчитает его как 0.

(5) Тип: Необязательный. Значение указывает время платежа. Есть два типа:

  • 0 или не указано: платеж в конце каждого периода;
  • 1: выплата в начале каждого периода.

Возвращаемое значение

Численная величина.
Функция FV вернет будущую стоимость инвестиции на основе периодических постоянных платежей и фиксированной процентной ставки.

Примечания по использованию

(1) Процентная ставка: Убедитесь, что процентная ставка соответствует срокам выплаты.

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

Примеры формул

Пример 1. Рассчитайте будущую стоимость паушальных инвестиций в Excel

Предполагая, что в настоящее время на вашем банковском счете есть 10,000 XNUMX долларов. Теперь вы хотите сохранить деньги в виде срочного депозита в размере 3 лет, а его годовая сложная процентная ставка составляет 5%. По этому депозиту будут начисляться проценты ежегодно. Теперь вы можете применить функцию FV, чтобы легко узнать, сколько денег вы получите от срочного депозита через 3 года.

В этом примере приведенная стоимость, которую вы заплатите, составляет 10,000 5 долларов, процентная ставка — 3%, периоды выплат — 0, а платеж за период — XNUMX. Вы можете рассчитать будущую стоимость этой единовременной инвестиции с помощью одной из формул ниже.:

= FV (C5; C6,0; -C4)

= FV (5%; 3,0; -10000)

Пример 2: Расчет будущей стоимости аннуитета

Предположим, вы планируете купить аннуитетный продукт сейчас. В этом аннуитетном продукте вам нужно заплатить $2,500 в год с фиксированной годовой процентной ставкой 6%, и его жизнь 30 лет. Если вы купите этот аннуитетный продукт, сколько денег вы сможете получить обратно через 30 лет?

В примере приведенная стоимость равна 0, процентная ставка по аннуитету — 6.00%, периоды выплат — 30, а выплаты составляют 2,500 долларов в год. Поэтому вы можете применить одну из формул ниже легко вычислить будущую стоимость вашего аннуитета.

= FV (C4; C5; -C6,0; C7)

= БС (6%; 30; -2500; 0; 0)

Пример 3: Расчет будущей стоимости постоянных ежемесячных платежей

Допустим, вы собираетесь ежемесячно откладывать деньги на продолжение обучения. Вы планируете экономить $500 в месяц, а годовая процентная ставка составляет 4%, и вы будете выполнять этот план за 5 лет. Таким образом, вы также можете применить FV future, чтобы рассчитать, сколько основной суммы и процентов вы получите от этого плана.

В этом примере приведенная стоимость — 0, процентная ставка — 4.00% / 12, периоды выплат — 12 * 5, ежемесячный платеж — 500.00 долларов США, поэтому вы можете использовать формулу = БС (4% / 12, 5 * 12, -500, 1) Чтобы выяснить суть принципа и интереса:

=FV(C5/C7,C6*C7,-C8,C9)

= БС (4% / 12, 5 * 12, -500, 1)

Лучшие инструменты для работы в офисе

Kutools for Excel — поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.

Заключение

Мы познакомились с разными методами и подходами к анализу и прогнозированию временных рядов. К сожалению, или к счастью, серебряной пули для решения такого рода задач пока не появилось. Методы, разработанные в 60-е годы прошлого века, (а некоторые и в начале 19-го), по-прежнему пользуются популярностью наравне с неразобранными в рамках данной статьи LSTM или RNN. Отчасти это связано с тем, что задача прогнозирования, как и любая другая задача, возникающая в процессе работы с данными — во многом творческая и уж точно исследовательская. Несмотря на обилие формальных метрик качества и способов оценки параметров, для каждого временного ряда часто приходится подбирать и пробовать что-то своё. Не последнюю роль играет и баланс между качеством и трудозатратами. Не раз уже упоминавшаяся здесь SARIMA-модель хотя и демонстрирует выдающиеся результаты при должной настройке, может потребовать не одного часа танцев с бубном манипуляций с рядом, в то время как простенькую линейную регрессию можно соорудить за 10 минут, получив при этом более-менее сравнимые результаты.

Ссылка на основную публикацию