Как посчитать максимальную просадку в excel

Обновлено: 25.11.2022

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

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

Доходность является одним из важнейших показателей эффективности управления портфелем, свидетельствующим об эффективности управления. Но нельзя, используя только доходность, судить о качестве управленческой стратегии. Помимо доходности есть обратная сторона – риск, не учет его в оценке эффективности может исказить реальное положение вещей. Слишком позитивная доходность могла быть получена портфельным управляющим, за счет нескольких сверх рискованных сделок или вследствие того, что весь фондовый рынок находился в растущем тренде. Примером этого может послужить российский фондовый рынок, где до кризиса 2008 года почти все ПИФ (паевые инвестиционные фонды) показывали сверхдоходность в 100-200% годовых, это объяснялось тем, что рынок находился в повышательном тренде последние несколько лет, но когда рынок рухнул, за ним, и обесценились многие паевые фонды. Это свидетельствует о низком качестве управления и недооценке редких, но возможных событий, таки как кризисы.

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

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

Формула коэффициента Шарпа

Расчет коэффициента Шарпа в Excel
Коэффициент Шарпа (Sharpe ratio) является одним из самых распространенных коэффициентов оценки эффективности управления инвестиционными портфелем, другими словами оценивает качество стратегии за отчетный период. Другое название этого показателя – «доходность – разброс» (reward to variability ratio) и представляет собой отношение превышения доходности инвестиционного портфеля (или доходности фонда) надо доходностью безрискового актива к риску этого портфеля, выраженным в виде стандартного отклонения. Формула расчета этого показателя следующая:

Где: arp – средняя доходность инвестиционного портфеля за выбранный временной интервал;
аrf – средняя доходность безрискового актива;
σp – риск инвестиционного портфеля, выраженный как стандартное отклонение доходностей портфеля.

котировки

Далее рассчитаем значения среднедневной доходности по безрисковому активу. За такой актив, как правило, берут государственные ценные бумаги или банковские депозиты. Годовой процент по ним составляет 7%, тогда как дневная доходность такого актива будет равна 0,02%.

В Excel формулы расчета среднедневной доходности рынка безрискового актива, инвестиционного портфеля и изменчивости доходности инвестиционного портфеля (σр) следующие:
=СРЗНАЧ(C:C)
=0.07/360
=СРЗНАЧ(D:D)
=СТАНДОТКЛОН(D:D)
Формула коэффициента Шарпа выглядит так:
=(F3-F2)/F4

Расчет показателя Шарпа в Excel

Коэффициент Трейнора в Excel

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

Расчет коэффициента Трейнора в Excel
Коэффициент Трейнора (Treynor 1965) еще называют коэффициентом «доходности – изменчивости» (reward to volatility ratio) и представляет собой отношение избыточной доходности к рыночному риску. Этот коэффициент строится на основе модели CAPM.

Где: arp – среднедневная доходность инвестиционного портфеля;
аrf – среднедневная доходность безрискового актива;
βр – коэффициент бета инвестиционного портфеля (систематический риск портфеля).
Для более подробного изучения расчета риска рекомендую к прочтению:
Построение модели CAPM для российского фондового рынка

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

Где: σpm – ковариация между доходностью инвестиционного портфеля и доходностью рынка;
σm – дисперсия доходности рынка.

Коэффициент бета показывает, как изменяется доходность инвестиционного портфеля от изменения доходности рынка в целом. Показатель бета меньше 1 свидетельствует о том, что инвестиционный портфель с такой стратегией имеет больше риска, нежели сам рынок. Это легко проверить, если рассчитать стандартные отклонения доходностей индекса РТС и доходностей инвестиционного портфеля для рынка (RTSI) стандартное отклонение составляет 1,78% , а для портфеля 2,20%, что выше, а значит и более рискованно.

И так мы разобрали все основные параметры для расчета коэффициента Трейнора.
Коэффициент бета =КОВАР(C:C;D:D)/ДИСП(C:C)
Коэффициент Трейнора =(F3-F2)/F6

Расчет показателя Трейнора в Excel

Формула альфы Дженсена

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

Расчет альфы Дженсена в Excel
Коэффициент альфа Дженсена был предложен Jensen в 1968 году. Это абсолютный показатель, который показывает, насколько более эффективно активное управление инвестиционным портфелем, нежели пассивное. Формула расчета следующая:

Где: αp – средняя доходность инвестиционного портфеля;
αrf – средняя доходность безрискового актива;
αrm – средняя доходность рыночного индекса (рынка);
βp –коэффициент бета (систематический риск портфеля).

Мы имеем все параметры для расчета этого коэффициента.
Альфа Дженсена =F3-(F2+(F1-F2)*F6)

Расчет альфы Дженсена в Excel

Чем выше значения этого коэффициента, тем более эффективно управление инвестиционным портфелем. Отрицательное значение говорит о том, что следование за рынком (использование пассивной стратегии) более эффективно, нежели использование активной стратегии управления портфелем. Мы получили значение альфы 0,21%, то есть на 0,21% среднедневная доходность портфеля выше среднерыночной. Часто встречается критика данного показателя из-за того что его расчет строится на основе коэффициента «бета», который не стационарен и меняется со временем, поэтому эффективное управление в прошлом может не повторится.

Формула коэффициента Джека Швагера

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

Где: AAR – среднеарифметическое месячных прибылей;
AAMR – среднее значение максимальных просадок капитала за месяц (стоимости активов).

Формула коэффициента Сортино

Этот показатель берется, как правило, для годовых данных, но мы будем использовать его для месячной доходности и просадки. Сначала рассчитаем месячные доходности портфеля, потом максимальные месячные просадки (убытки) с помощью функции МИН(). Далее рассчитывается среднемесячная прибыль (функция CРЗАНЧ()), среднемесячные убытки и непосредственно сам показатель Швагера. У нас получилось -0.379, что означает, что среднемесячные прибыли портфеля были меньше, чем его просадки.

Расчет коэффициента Сортино в Excel
Для оценки эффективности управления инвестиционным портфелем часто используют коэффициент Сортино. Этот коэффициент очень схож с коэффициентом Шарпа за исключением того, что при оценке риска берется только те наблюдения, где значения доходности ниже определенного уровня, который обычно берется за значения доходности безрискового актива или относительно точки безубыточности. Другими словами, данный коэффициент учитывает только волатильность в периоды спада (нисходящий риск). Формула расчета показателя Сортино следующая:

Где: arp – среднедневная доходность инвестиционного портфеля;
armar – среднедневная минимально допустимая доходность инвестиционного портфеля (minimum acceptable rturn);
nmar – количество наблюдений, в которых доходность была ниже допустимой доходности armar.

Для расчета данного коэффициента необходимо определить уровень допустимой доходности (MAR), возьмем его равным дневной доходности безрискового актива (0,02%). Так же выделим доходности, которые были получены при нисходящей волатильности. В колонке ari по формуле отделим только отрицательные доходности:
=ЕСЛИ(A3

MAR

После этого посчитаем разницу и квадрат между ari и MAR. Далее рассчитаем среднее значение по сумме квадратов и возьмем корень из него – это и будет знаменатель формулы.
D=(C3-B3)^2
F3 =СРЗНАЧ(D:D)
G3=КОРЕНЬ(F3)
Коэффициент Сортино =(H1-H2)/G3

Расчет показателя Сортино в Excel

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

Формула коэффициента информационное отношение

Расчет коэффициента «Информационное отношение» в Excel
Данный коэффициент показывает эффективность управления инвестиционным фондом, рассчитанную как избыточная доходность портфеля по сравнению с рыночной доходность, отнесенная к стандартному отклонению избыточной доходности. Этот коэффициент рассчитывает по формуле:

Где: arp – средняя доходность инвестиционного портфеля за выбранный временной интервал;
αrm – средняя доходность рыночного индекса (рынка);
nm – количество наблюдений, в которых доходность была выше допустимой доходности среднерыночной доходности (arm).

Расчет этого коэффициента очень походит на расчет коэффициента Сортино, только вместо волатильности вниз необходимо найти волатильность вверх. Можно заметить, что при пассивном управлении этот коэффициент будет равен нулю.
Для этого расчета необходимо найти доходности инвестиционного портфеля, которые были больше среднерыночной (0,07%). Для этого в колонке ari запишем:
ari =ЕСЛИ(A2>B2;A2;0)
(ari- arm)^2=(C2-B2)^2
Информационное отношение =(H1-H2)/G3

Расчет коэффициента информационное отношение в Excel

Формула коэффициента Калмара

Коэффициент информационного отношения (IR) не так популярен в российской практике оценки эффективности управления инвестиционным портфелем, зато он очень распространен в США. Он позволяет определить эффективность по отношению к рынку.

Расчет калмар коэффициента в Excel
Калмар коэффициент (Calmar ratio) представляет собой коэффициент доходность-риск, где доходность берется как среднегеометрическая доходность, а риск рассчитывается как максимальная просадка за все время управления. Можно заметить, что калмар – это не фамилия ученого разработавшего его, а аббревиатура бюллетени, которую выпускала компания Terry Young -«California Managed Account Reports». Формула расчета коэффициента следующая:

Где: CAGR (Compound Annual Return) – среднегеометрический месячный темп прироста доходности;
MaxDradawn- максимальная просадка все время управления портфелем.

Для расчета среднегеометрического темпа необходимо исключить убыточные месяцы торговли (колонка В).
Среднегеометрическая доходность ИП =СРГЕОМ(B:B)
Максимальная просадка (MaxDradawn) =МИН(A:A)
Коэффициент Калмара =C1/C2

Расчет в Excel коэффицента Калмара

Расчет коэффициента M2 в Excel
Коэффициент М2 (Индекс Модильяни), разработанный Модильяни в 1997 году, рассчитывается как умножение коэффициента Шарпа на стандартное отклонение рыночной доходности и прибавление безрисковой ставки доходности. Формула расчет коэффициента приведена ниже:

Формула М2 индеск Модильяни


Где: arp – средняя доходность инвестиционного портфеля за выбранный временной интервал;
аrf – средняя доходность безрискового актива;
σp – риск инвестиционного портфеля, выраженный как стандартное отклонение доходностей портфеля;
σm – стандартное отклонение рыночной доходности.
Отношение стандартных отклонений называют фактором рычага.

Расчет индекса Модильяни в Excel

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

Подсчет максимального и минимального значения выполняется известными функциями МАКС и МИН. Бывает, что вычисления нужно произвести по группам или в зависимости от условия, как в СУММЕСЛИ.

Долгое время в Excel не было аналога СУММЕСЛИ или СРЗНАЧЕСЛИ для расчета максимального и минимального значения, поэтому использовали формулу массивов.

Пусть имеются данные

Нужно подсчитать максимальное значение в указанной группе. Название группы (критерий) введем в отдельную ячейку (D2). Пусть для начала это будет группа Б. Рядом введем следующую формулу:

Это формула массивов, поэтому ввести ее нужно комбинацией Ctrl + Shift + Enter.

Максимальное значение по условию

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

Как это работает? Очень просто. Первым делом нужно указать диапазон, который будет использоваться в качестве аргумента функции МАКС, то есть только те ячейки, которые соответствуют указанной группе. Так как мы заранее позаботились об удобстве использования функции, то название группы указали не внутри формулы, а в отдельной ячейке (гораздо легче менять группу). Тогда формула для нужного диапазона выглядит так.

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

Создание массива для функции МАКС

На следующем этапе укажем функцию МАКС, аргументом которой выступает полученный выше массив. Excel воспринимает примерно так.

Массив внутри функции МАКС

Видно, что максимальное значение внутри массива равно 31. Его и мы и увидим в ячейке с формулой. Нужно только не забыть итоговую функцию ввести комбинацией клавиш Ctrl + Shift + Enter, иначе ничего не получится. В строке формул формула массива отображается внутри фигурных скобок. Добавляются сами, специально дорисовывать не нужно.

Если функцию МАКС заменить на МИН, то по указанному условию (названию группы) будет выдаваться минимальное значение.

Функции Excel 2016 МАКСЕСЛИ (MAXIFS) и МИНЕСЛИ (MINIFS)

В MS Excel добавили новые статистические функции — МАКСЕСЛИ и МИНЕСЛИ. Обе функции имеют возможность учитывать несколько условий и некоторое время в их названиях в конце были буквы -МН. Потом убрали, хотя в скриншотах ниже используется вариант названий с -МН.

Есть ряд значений, каждое из которых входит в некоторую группу. Нужно рассчитать максимальное значение по группе А. Используем формулу МАКСЕСЛИ.

Функция МАКСЕСЛИМН

Все очень просто. Как и у СУММЕСЛИМН вначале указываем диапазон, где находится искомое максимальное значение (колонка В), затем диапазон с критериями (колонка А) и далее сам критерий (в ячейке D2). Можно указать сразу несколько условий. Таким же способом легко рассчитать минимальное значение по условию. Найдем, к примеру, минимум внутри группы Б.

Функция МИНЕСЛИМН

Ниже показан ролик, как рассчитать максимальное и минимальное значение по условию.

Всем привет!
Люди добрые, подскажите, как в excel посчитать просадку эквити в процентах. Весь день мучаюсь, но ничего толкового не выходит.
Заранее благодарен.


Мои записи

Вопрос по ДУ

Тейк-Профит


Краткосрочный прогноз по нефти, золоту и EURUSD на 11.05.2022


Краткосрочный прогноз по BTCUSD, XRPUSD и ETHUSD на 11.05.2022


У AbbVie неплохой апсайд

Находящаяся в нашем аналитическом покрытии американская биофармацевтическая компания AbbVie отчиталась за 1 квартал. Квартальная выручка AbbVie увеличилась на 4,1% г/г и составила $13,54 млрд, не д.

​​«Энергетическая война» продолжается

🛢 На глобальном энергетическом рынке ситуация продолжает оставаться накаленной. Несколько дней назад в Сенате Конгресса США был одобрен антикартельный законопроект NOPEC. По нему суды США наделяютс.


Акции перед обвалом. Обзор до начала торгов.

🔥💵 Поздравляю всех с открытием новой торговой недели. Надеюсь вы хорошо отдохнули :) Обсуждаем факторы которые будут влиять на рынок перед началом торгов. Главное событие дня для фондовых рынков. .


План торговли на 11 Мая


Blockchain Life — самое интересное

О каких трендах рассказали спикеры Сергей Хитров, Основатель Listing. Help, главный организатор форума Blockchain Life, о будущем криптоигр и эйрдроп.


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


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


Потенциально шикарный график .По евродол 1,0230 быстрее чем 1,0680 (на момент прогноза 1,0528)


Курс доллара на сегодня. Что ожидать?


На подходе "Меркурианци"


Sell In May And Go Away — Ложь!

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


Общая доходность SPX за 12 месяцев стала отрицательной. Это только начало.

Это всё-таки произошло. SPX опустился ниже 4000 пунктов и приблизился к значениям весны 2021 года, но то ли ещё будет. Мягкотелая политика ФРС привела к тому, что сейчас пузырь на американском рын.

Аппроксимация в Microsoft Excel

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

Выполнение аппроксимации

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

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

Но она может быть построена с применением одного из пяти видов аппроксимации:

  • Линейной;
  • Экспоненциальной;
  • Логарифмической;
  • Полиномиальной;
  • Степенной.

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

Способ 1: линейное сглаживание

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

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

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

Построение диаграммы в Microsoft Excel

График построен в Microsoft Excel

Добавление линии тренда через контекстное меню в Microsoft Excel

Добавление линии тренда через блок инструментов на ленте в Microsoft Excel

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

Также в нашем случае для сравнения различных вариантов аппроксимации важно установить галочку около пункта «Поместить на диаграмму величину достоверной аппроксимации (R^2)». Данный показатель может варьироваться от 0 до 1. Чем он выше, тем аппроксимация качественнее (достовернее). Считается, что при величине данного показателя 0,85 и выше сглаживание можно считать достоверным, а если показатель ниже, то – нет.

Включение линейной аппроксимации в Microsoft Excel

Линия тренда построена с помощью линейной аппроксимации в Microsoft Excel

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

В конкретно нашем случае формула принимает такой вид:

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

Способ 2: экспоненциальная аппроксимация

Теперь давайте рассмотрим экспоненциальный тип аппроксимации в Эксель.

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

Переход в формат лини тренда в Microsoft Excel

Построение экспоненциальной линии тренда в Microsoft Excel


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

Общий вид функции сглаживания при этом такой:

где e – это основание натурального логарифма.

В конкретно нашем случае формула приняла следующую форму:

Способ 3: логарифмическое сглаживание

Теперь настала очередь рассмотреть метод логарифмической аппроксимации.

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

Включение логарифмической аппроксимации в Microsoft Excel

Логарифмическая линия тренда построена в Microsoft Excel

В общем виде формула сглаживания выглядит так:

где ln – это величина натурального логарифма. Отсюда и наименование метода.

В нашем случае формула принимает следующий вид:

Способ 4: полиномиальное сглаживание

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

    Переходим в окно формата линии тренда, как уже делали не раз. В блоке «Построение линии тренда» устанавливаем переключатель в позицию «Полиномиальная». Справа от данного пункта расположено поле «Степень». При выборе значения «Полиномиальная» оно становится активным. Здесь можно указать любое степенное значение от 2 (установлено по умолчанию) до 6. Данный показатель определяет число максимумов и минимумов функции. При установке полинома второй степени описывается только один максимум, а при установке полинома шестой степени может быть описано до пяти максимумов. Для начала оставим настройки по умолчанию, то есть, укажем вторую степень. Остальные настройки оставляем такими же, какими мы выставляли их в предыдущих способах. Жмем на кнопку «Закрыть».

Включение полиномиальной аппроксимации в Microsoft Excel

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

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

В нашем случае формула приняла такой вид:

Включение полиномиальной аппроксимации в шестой степени в Microsoft Excel

Полиномиальная линия тренда в шестой степени в Microsoft Excel

Формула, которая описывает данный тип сглаживания, приняла следующий вид:

Способ 5: степенное сглаживание

В завершении рассмотрим метод степенной аппроксимации в Excel.

    Перемещаемся в окно «Формат линии тренда». Устанавливаем переключатель вида сглаживания в позицию «Степенная». Показ уравнения и уровня достоверности, как всегда, оставляем включенными. Жмем на кнопку «Закрыть».

Полиномиальная линия тренда в шестой степени в Microsoft Excel

Степенная линия тренда построена в Microsoft Excel

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

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

В конкретно нашем случае она выглядит так:

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

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

Закрыть

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Закрыть

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

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

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

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

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

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

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

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

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

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

Добавление линии тренда в Microsoft Excel

  • Линейная;
  • Логарифмическая;
  • Экспоненциальная;
  • Степенная;
  • Полиномиальная;
  • Линейная фильтрация.

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

Параметры линии тренда в Microsoft Excel

Линия тренда построена в Microsoft Excel

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

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

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

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

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

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

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

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

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

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

Переход в Мастер функций в Microsoft Excel

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

В поле «Известные значения y» указываем координаты столбца «Прибыль предприятия». Это можно сделать, установив курсор в поле, а затем, зажав левую кнопку мыши и выделив соответствующий столбец на листе.

Аналогичным образом в поле «Известные значения x» вносим адрес столбца «Год» с данными за прошедший период.

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

Результат функции ПРЕДСКАЗ в Microsoft Excel

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

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

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

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

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

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


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

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

    Производим обозначение ячейки для вывода результата и запускаем Мастер функций обычным способом. В категории «Статистические» находим и выделяем наименование «ТЕНДЕНЦИЯ». Жмем на кнопку «OK».

Переход к аргументам функции ТЕНДЕНЦИЯ в Microsoft Excel

Аргументы функции ТЕНДЕНЦИЯ в Microsoft Excel

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

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

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

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

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

    Выделяем ячейку вывода результата и уже привычным путем вызываем Мастер функций. В списке статистических операторов ищем пункт «РОСТ», выделяем его и щелкаем по кнопке «OK».

Переход к аргументам функции РОСТ в Microsoft Excel

Аргументы функции РОСТ в Microsoft Excel

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

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

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

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

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

    Производим выделение ячейки, в которой будет производиться вычисление и запускаем Мастер функций. Выделяем наименование «ЛИНЕЙН» в категории «Статистические» и жмем на кнопку «OK».

Переход к аргументам функции ЛИНЕЙН в Microsoft Excel

Аргументы функции ЛИНЕЙН в Microsoft Excel

Результат функции ЛИНЕЙН в Microsoft Excel

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

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

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

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

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

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

    В списке операторов Мастера функций выделяем наименование «ЛГРФПРИБЛ». Делаем щелчок по кнопке «OK».

Переход к аргументам функции ЛГРФПРИБЛ в Microsoft Excel

Аргументы функции ЛГРФПРИБЛ в Microsoft Excel

Результат функции ЛГРФПРИБЛ в Microsoft Excel

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

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

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

Закрыть

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Закрыть

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

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