Функция НОРМОБР (вероятность; средн_3нач; станд_откл)

Функция имеет следующие параметры:

вероятность —вероятность нормального распределения; средн_знач —среднее значение; станд_откл —стандартное отклонение.

Она возвращает обратное нормальное распределение для указанного среднего и стандартного отклонения.

Другими словами, она позволяет по заданной вероятности определить величину исследуемой переменной (в нашем примере доходности).

Определим предельную величину доходности для вероятности 84%:

=НОРМОБР (0,84 ;В8 ;В10) (Результат: 11,45%).

Таким образом, для заданной вероятности величина доходности составит не более 11,45%: р(х <0,1145) = 0,84.

Функции ППП EXCEL, определяющие значения параметров распределения М(Е), VAR(E) и о(Е), следует применять только в тех случаях, когда вероятности событий равны. Если же распределение вероятностей задано (например, известно из предыдущего опыта или получено методом экспертных оценок), среднее значение, дисперсия и стандартное отклонение рассчитыва- ются путем непосредственной реализации средствами ППП EXCEL соответствующих соотношений — (4.7), (4.13), (4.16).

Продемонстрируем один из вариантов подобной реализации на решении примера 4.2.

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

В Г

Анализ рисков (акции фирмы "А")

Фіуна "А' Прогноз

Вероятность

Доходность

Взвешенные квадраты отклонений 0,30 0,40 О

70,00', 15,00^п 10( 10 '

Пессимистический

Вероятный

Оптимистический Ожидаемая доходность (R) Стандартное отклонение Коэффициент вариации (CV)

!1

& Интервал ставки Вероятность И; г2ї (r1<=R<=rZ) ГІ г2 13 14

_

I

!нЫ Фирма / ЛнсгЗ / Лисг41 іі|І Рис 4.10. Исходная таблица для решения примера 4.2

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

Таблица 4.4. Математические функции, используемые

при анализе рисков Наименование функции Формат функции Оригинальная версия Локализованная версия SUMPRODUCT СУМПРОИЗВ СУМПРОИЗВ (массив 1 ; массив 2) SQRT КОРЕНЬ КОРЕНЬ(число)

В частности, для выполнения необходимой нам операции удобно использовать функцию СУММПРОИЗВ (). Как следует из табл. 4.4, аргументами функции являются матрицы одинакового размера1. Введем в ячейку и формулу:

=СУММПР0ИЗВ (В5: В7 ; С5: С7) (Результат: 0,15, или 15%)

Для определения величины стандартного отклонения необходимо сперва вычислить дисперсию. Из (4.13) следует, что дисперсия случайной величины представляет собой сумму квадратов отклонений от среднего, взвешенных на соответствующие вероятности. Зададим в ячейке D5 формулу вычисления дисперсии для первого события2:

=В5* (С5-$В$9) А2 (Результат: 0,2165).

Обратите внимание на то, что для задания ячейки, содержащей среднее значение (В9), используется способ абсолютной адресации. Это позволяет безболезненно скопировать данную формулу в ячейки D6.D7 (в противном случае адрес ячейки, содержащей среднее значение, был бы настроен неправильно). Теперь можно вычислить величину стандартного отклонения, которая равна квадратному корню из дисперсии (суммы ячеек D5.D7). Для этого воспользуемся функцией КОРЕНЬ() (см. табл. 4.4). Введите в ячейку В10:

=КОРЕНЬ (СУММ (D5 . D7) ) (Результат: 0,6584, или 65,84%).

Вычисление коэффициента вариации не представляет особых трудностей. Для этого достаточно просто разделить значение ячейки В10 на значение В9. Введите в ячейку В11: =В10/В9 (Результат: 4,39).

' В качестве аргументов могут быть заданы до 30 матриц.

1 Более эффективный способ подобных расчетов состоит в использовании массивов ППП EXCEL. Вычислив основные параметры распределения случайной величины, можно определить вероятность ее попадания в некоторый интервал. В приведенной на рис. 4.11 таблице границы первого интервала задаются в ячейках В16 и С16. Определим вероятность того, что значение доходности попадет в интервал (—70; 0). Введите границы анализируемого интервала в ячейки В16 и С16. Формула вычисления вероятности в ячейке D16 реализована с использованием уже известной нам функцией НОРМРАСП () и имеет следующий вид:

=НОРМРАСП (С16; $В$9; $В$10 ; 1) -НОРМРАСП (В16; $В$9; $В$10 ; 1)

(Результат: 0,31).

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

А

v

I Анализ рисков (акции фирмы УП Фирма "А" | Прогноз Вероятность Доходность Взвешенные квадраты отклонений Пессимистический

Вероятный

Оптинистическии 0 30 0,40

0,30 -70,30 "И 100ДГО 0.21675 С

0,21675 Ожидаемая доходность (R) Стандартное отклонение 1 Коэффициент вариаций (CV) П00° 6 ,84 4 4,3!

Интервал ставки Вероятность М;г2) (г1<—R •70% 0% 0,31 15% 65% 0,28 15% 100% 0,40 -70% 100% 0,80

1 >1»; Фирма А „ Гиамаь - fkj j

Рис.

4.11. Итоговая таблица анализа рисков (фирма "А") Для дальнейшего анализа достаточно указать интересующие интервалы и скопировать формулу в ячейке D16 необходимое число раз. На рис. 4.11 приведена итоговая таблица, содержащая некоторые результаты анализа риска по акциям фирмы "А" (пример 4.2). Аналогичная таблица на рис. 4.12 содержит результаты анализа риска по акциям фирмы "В".

» * Анализ рисков акции фирмы "В") 1 Фирма"В" Прогноз вероятность Доходность Квадраты 3 отклонении 4 "ессимистическии 0.3 104', 0Л00Ї5 ЕерОЯТНЫЙ D.4 15% п У Оптимистический 0,3 2РТ. lS-J

т Ожидаемая доходность '/?J 15004*. J? Стандартное отклонение 3.87, Коэффициент вариации 'CV fl,2g Интервал ставки <П; г2) Вероятность (r1<=R<=r2) г1 [ г2 0% 20% ll^d 15'.. 20% 0,40 10% 20% 0,00 -104 0% 0,00 ь Л

ІЗ

Щ " _

Ж

т Рис. 4.12. Итоговая таблица анализа рисков (фирма "В")

В качестве упражнения попробуйте разработать таблицу анализа рисков для фирмы "В" самостоятельно, используя рис. 4.10 в качестве образца. Сравните полученные результаты.

На рис. 4.13 приведен шаблон для автоматизации проведения анализа рисков, исходя из предположения о нормальном распределении случайной величины (в данном случае доходности). Формулы и собственные имена ячеек, используемые в шаблоне, приведены в табл. 4.5 и 4.6. Читателю предлагается сформировать его самостоятельно. Ниже даются необходимые пояснения. -??—МИ —11 mm і? і in ?? и І* Анализ рисков Прогноз (событие) Вероятное™ Доходность Взвешенные квадраты отклонений Событие 1 Событие N Job

о,ии ОДЮЯ 0Д)0% 0 0 Ожидаемая доходность [R) Стандартное отклонение Коэффициент вариации (CV) 0,00 і ОД) і А' "ЕЛ/0! Интервал ставки (П;г2) Вероятность (r1< R< г2) г1 г2 № № «ЧИСЛО! 1

<1 И **. Г мсті Лисг2/.ЯисгЗ ( ЛиеЫ/Яист5 / Яисг&/71нс*] « | J 1

Рис. 4.13. Таблица-шаблон для анализа рисков Таблица 4.5. Формулы шаблона Адрес ячейки Формула D5 =В5*(СБ-Среднее)А2 D6 =В6*(Сб-Среднее)л2 В8 -СУММПРОИЗВ(В5:Вб;С5:С6) В9 =КОРЕНЬ (СУММ (D5 : D6) ) В10 =Отклонение/Среднее D15 =НОРМРАСП(С15; Среднее; Отклонение; 1) - НОРМРАСП (В15; Среднее; Отклонение; 1)

Таблица 4.6. Имена ячеек шаблона

Адрес ячейки Имя В8 Среднее В9 Отклонение Первая часть шаблона вместе с элементами оформления занимает блок ячеек A1.D6. Она обеспечивает ввод и обработку исходных данных для минимально допустимого числа альтернатив. При этом ячейки блока D5.D6 заполняются автоматически. В случае необходимости базовый шаблон можно легко модифицировать для обработки любого числа событий, вставив необходимое число строк в блок ячеек A5.D6 и скопировав формулу из D5 требуемое количество раз.

Формулы блока В8.В10 вычисляют основные параметры распределения доходности. Блок ячеек B13.D15 предназначен для последующего анализа вероятностей. Интересующие аналитика интервалы доходности вводятся в ячейки В15.С15, В16.С16 и т.д. Формула, заданная в ячейке D15, вычисляет соответствующую вероятность и в случае необходимости может быть скопирована требуемое число раз.

Сохраните полученный шаблон под именем RISK_AN.XLT. Проверим работоспособность шаблона на следующем примере.

Пример 4.4

Прогнозируемые доходности по акциям фирм "К" и "Р" имеют следующие распределения вероятностей (табл. 4.7).

Таблица 4.7. Доходность по акциям (пример 4.4) Вероятность Доходность Акции "К" Акции "Р" 0,15 -15% -25% 0,20 0% 10% 0,40 15% 20% 0,20 20% 30% 0,05 35% 45%

Осуществить анализ рисков операций с акциями фирм "К" и "Р".

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

Установить табличный курсор в ячейку Аб. 2.

Вставить в шаблон три новые строки. 3.

Ввести исходные данные в ячейки В5 . С9. 4.

Скопировать формулу ячейки D5 в блок D6.D8. 5.

Ввести интересующие значения доходности в ячейки В18 . С18 и т.д. 6.

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

фирмы "Р", приведены на рис. 4.14. 'JJ А в С J I E І- 2 Анализ рисков Прогноз (событие) Вероятность Доходность Взвешенные квадраты 3 отклонений 4 5 Событие 1 0,15 ?25,001 0j0234D;»S 6 Событие 2 0.7Г 10,004. 0,00040j і Событие 3 0,40 20 110% 0,00121 1 Гобі ле 1 0,20 30,0" і 0,004805 3 Событие 5 С 45,001 P,0D46^ 125 19 11 Ожидаемая доходность (R) 14.10% Стандартное отклонение "3.57-. 13 Коэффициент вариации (СУ] 1,28 V р Интервал ставки Вероятность ш W 1 И; г2) fr1<=R<=r2) г1 г2 ч .11% 0% 0,1Z 19 Г1ш 451' 0,45 ї- 1 »j Ч Акции -Р" шЪ[ЯисгЗ/ Ч«ст4/Яист5/ГЫстб/ | 1 -- ?

Рис. 4.14. Анализ рисков (акции фирмы "Р")

Руководствуясь п. 1-6, проанализируйте риски для акций фирмы "К" Сравните полученные результаты. Акциям какой фирмы вы бы отдали предпочтение? Почему?

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

<< | >>
Источник: Лукасевич И.Я.. Анализ финансовых операций. Методы, модели, техника вычислений: Учебн. пособие для вузов. — М.: Финансы, ЮНИТИ. - 400 с.. 1998

Еще по теме Функция НОРМОБР (вероятность; средн_3нач; станд_откл):

  1. В этом параграфе мы рассматриваем модели общего равновесия (обмена) с контингентными благами в предположении, что существует конечное множество таких благ, а, следовательно, и состояний мира. Участники обмена при этом имеют собственные (возможно неверные) представления о вероятностях возможных состояний мира. Частным случаем этой ситуации является рынок, где представления всех участников о вероятностях совпадают. Заметим, что часто полученные результаты не зависят от того, являются ли эти пре
  2. 4.2. Закон нормального распределения вероятностей
  3. Определенность, вероятность и возможность
  4. * Нечувствительность к исходным вероятностям
  5. Альтернативы теории вероятностей
  6. Альтернативы теории вероятностей
  7. Насколько вероятно переключение?
  8. Предмет теории вероятности.
  9. АНАЛИЗ ВЕРОЯТНОСТИ БАНКРОТСТВА
  10. Случайность — неопределенность — вероятность
  11. Новое основание теории вероятностей
  12. 10.1. Эмоции и вероятности
  13. 10.1. Эмоции и вероятности
  14. 4.1. Случайные события. Вероятности. Законы распределений
  15. Анализ вероятности банкротства организации
  16. лучаев по законам теории вероятностей; в) пр