Кривелевич М.Е. Долгосрочная финансовая политика
Подождите немного. Документ загружается.
Рис. 19. Исходная таблица для решения примера
Прежде всего необходимо определить среднюю величину доходности -М(Е).
Наиболее простой способ - последовательно перемножить каждую ячейку блока В5. В7 на
соответствующую ей ячейку блока С5. С7 и суммировать полученные значения. Нетрудно
заметить, что данная последовательность действий представляет собой операцию
нахождения суммы произведений элементов двух матриц. Поскольку матричные операции
достаточно часто встречаются в прикладном анализе, для автоматизации их выполнения в
EXCEL реализована специальная группа математических функций. Форматы некоторых
функций этой группы, которые будут использованы в данной главе, приведены в таблице 14.
Таблица 14. Математические функции, используемые при анализе рисков
Наименование функции
Оригинальная
версия
Локализованная
версия
Синтаксис
SUMPRODUCT СУМПРОИЗВ СУМПРОИЗВ (массив 1 ; массив 2)
SQRT КОРЕНЬ КОРЕНЬ (число)
В частности, для выполнения необходимой нам операции удобно использовать
функцию СУММПРОИЗВ (). Как следует из табл. 4.4, аргументами функции являются
матрицы одинакового размера. Введем в ячейку и формулу:
=СУММПРОИЗВ (В5 : В7; С5: С7) (Результат: 0,15, или 15% )
Для определения величины стандартного отклонения необходимо сперва вычислить
дисперсию. Из (20) следует, что дисперсия случайной величины представляет собой сумму
квадратов отклонений от среднего, взвешенных на соответствующие вероятности. Зададим в
ячейке D5 формулу вычисления дисперсии для первого события:
=В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).
Вычислив основные параметры распределения случайной величины, можно
определить вероятность ее попадания в некоторый интервал. В приведенной на рис. 4.11
таблице границы первого интервала задаются в ячейках В16 и С16. Определим вероятность
того, что значение доходности попадет в интервал (- 70; 0). Введите границы анализируемого
интервала в ячейки В16 и С16. Формула вычисления вероятности в ячейке D16
реализована с использованием уже известной нам функцией НОРМРАСП и имеет
следующий вид:
=НОРМРАСП(С16; $В$9; $В$10; 1) - НОРМРАСП(В16; $В$9; $В$10; 1)
(Результат: 0,31).
Снова обращаем внимание на использование абсолютной адресации при задании в
формулах ячеек, содержащих среднее значение и стандартное отклонение.
Рис. 20. Итоговая таблица анализа рисков (фирма "А")
Рассмотрение моделей и методов учета рисков при принятии инвестиционных
решений будет продолжено более подробно в следующем разделе.
Глава 7. Принятие инвестиционных решений в условиях риска
Прогноз не может быть абсолютно точным, иначе это не прогноз, а констатация
факта. Тем не менее чрезвычайно важно представлять себе каких результатов мы ожидаем,
насколько они вероятны и как отразится на результате то или иное изменение исходных
данных.
Анализ чувствительности показателей широко используется в практике финансового
менеджмента. В общем случае он сводится к исследованию зависимости некоторого
результирующего показателя от вариации значений показателей, участвующих в его
определении. Другими словами, этот метод позволяет получить ответы на вопросы вида: что
будет с результирующей величиной, если изменится значение некоторой исходной
величины? Отсюда его второе название - анализ "что будет, если" ("what if analysis").
Как правило, проведение подобного анализа предполагает выполнение следующих
шагов.
1. Задается взаимосвязь между исходными и результирующим показателями в виде
математического уравнения или неравенства.
2. Определяются наиболее вероятные значения для исходных показателей и возможные
диапазоны их изменений.
3. Путем изменения значений исходных показателей исследуется их влияние на конечный
результат.
Проект с меньшей чувствительностью NPV считается менее рисковым.
Обычная процедура анализа чувствительности предполагает изменение одного
исходного показателя, в то время как значения остальных считаются постоянными
величинами. Рассмотрим применение данного метода на примере.
Фирма рассматривает инвестиционный проект, связанный с выпуском продукта "А".
Полученные в результате опроса экспертов данные по проекту приведены в таблице 15.
Провести анализ чувствительности NPV к изменениям ключевых исходных показателей.
Таблица 15. Исходные данные по проекту производства продукта "А"
Показатели Диапазон изменений Наиболее вероятное значение
Объем выпуска Q 150 – 300 200
Цена за штуку Р 35 – 55 50
Переменные затраты V 25 – 40 30
Постоянные затраты F 500 500
Амортизация А 100 100
Налог на прибыль Т 60% . 60%
Норма дисконта r 8% - 15% 10%
Срок проекта n 5 – 7 5
Остаточная стоимость S
n
200 200
Начальные инвестиции I
0
2000 2000
Первый этап анализа согласно сформулированному выше алгоритму состоит в
определении зависимости результирующего показателя от исходных. В данном случае с
учетом приведенных в таблице 15 обозначений подобная зависимость может быть задана
соотношением:
(25)
Диапазоны возможных изменений исходных показателей определены ранее (т.е.
выполнен второй этап - см. таблицу 15), поэтому можно приступать к анализу. Однако
прежде облегчим этот процесс, разработав шаблон, автоматизирующий его проведение.
Для реализации типовой процедуры анализа чувствительности в рассматриваемом
примере будем использовать «Таблицу подстановок» - надстройку Excel, добавляемую в
меню «Данные».
Прежде всего спроектируем шаблон для ввода исходных данных. Предлагаемый
вариант такого шаблона приведен на рисунке 21. При этом были определены следующие
имена и формулы (табл. 16 и табл. 17).
Рис. 21. Шаблон для анализа чувствительности NPV
Таблица 16. Формулы шаблона
Ячейка Формула
В9 = (Количество* (Цена - Перем_расх) - Пост_расх -Аморт) * (1 - Налог) +Аморт
D10 =ПС (Норма ; Срок ; - Платежи) + ПЗ (Норма ; Срок ; 0 ; - Ост_стоим) -
Нач_инвест
Таблица 17. Имена ячеек шаблона
Адрес
ячейки
Имя Комментарии
В2 Количество Объем выпуска продукции (штук)
В3 Цена Цена за единицу
В4 Пepeм_pacx Переменные затраты на единицу продукции
В5 Норма Норма дисконта
В6 Срок Продолжительность проекта (лет)
В9 Платежи Величина чистых поступлений
D2 Нач_инвест Начальные инвестиции
D3 Пост_расх Норма дисконта
D4 Аморт Амортизационные отчисления
D5 Ост_стоим Стоимость актива на конец операции
D6 Налог Ставка налога
Как следует из табл. 15, для решения задачи используются всего две формулы. Первая
задана в ячейке В9. Она служит для вычисления чистого платежа NCF
t
и реализует
соотношение:
NCF = [Q(P - V) - F - A](1 - T) + A (26)
Нетрудно заметить, что выражение (26) является числителем первого слагаемого из
соотношения (25). Поскольку в данном случае поток платежей представляет собой аннуитет
(согласно методике проведения анализа исходные показатели считаются одинаковыми для
всех периодов), формула для вычисления критерия NPV задана в ячейке D10 с
использованием функции ПС. Напомним, что эта функция вычисляет современную
величину аннуитета - PV.
Заполните шаблон наиболее вероятными значениями исходных показателей (т.е.
данными, приведенными в последней графе табл. 15). После ввода данных ячейки В9 и D10
будут содержать расчетные значения периодического платежа и ожидаемой NPV (pиc. 21).
Рис. 21. Шаблон после ввода исходных данных
Теперь необходимо выбрать параметр, влияние которого будет подвергнуто анализу.
Предположим, что таким параметром является цена. Диапазон ее изменений составляет
интервал 35 - 55 (см. табл. 15). Заполним ячейки колонки
С варьируемыми значениями цены
(например, от 45 до 25 с шагом 5), начиная с ячейки С11, после чего необходимо выполнить
следующую последовательность действий.
1. Выделить блок ячеек С10 . D15.
2. Выбрать - из темы Данные главного меню пункт
Таблица подстановки. На экране
появится окно диалога (рис. 22).
3. Установить курсор в поле
Ячейка ввода столбца и ввести имя ячейки, содержащей
входной параметр (ячейка ВЗ).
4. Закрыть окно диалога, нажав кнопку [ОК].
Полученная в результате выполнения указанных действий таблица приведена
на рис. 23.
Рис. 22. Диалоговое окно Таблица подстановки.
Рис. 23. Анализ чувствительности NPV к изменению цены
Приведем необходимые пояснения к п.п. 1 - 4. Результирующая таблица (рис. 23)
построена таким образом, что введенные в блок
С11. С15 значения цены автоматически
подставляются в ячейку В3, которая служит входным параметром. Вычисляемые по
формуле ячейки D10 значения NPV заполняют следующие за ней пустые ячейки колонки D
(в нашем примере - блок
D12. D15) в соответствии с данными блока значений входного
параметров (блок
С11. С15).
Обратите внимание на следующее:
• ячейки, содержащие варьируемые значения и результаты вычислений, должны
занимать соседние колонки или строки;
• в первой ячейке колонки или строки, содержащей результаты вычислений,
обязательно должна быть задана связывающая формула!
В общем случае в таблице подстановок с одним входом можно использовать
произвольное количество формул и входных значений. Однако каждая формула должна
прямо или косвенно ссылаться на одну и ту же входную ячейку. В данном примере - любую
ячейку из блока В2.В6 или D2.D6.
Для проведения анализа по другому показателю необходимо ввести диапазон
требуемых значений в ячейки колонки С (начиная с С11), выделить соответствующий блок
ячеек колонок С и D (начиная с С10) и указать в окне диалога адрес или имя ячейки -
входного параметра. На рис. 24 приведены результаты количественного анализа
чувствительности NPV к изменениям объемов выпуска. В качестве входного параметра в
окне диалога была указана ячейка В2.
Рис. 24. Анализ чувствительности NPV к изменению объемов выпуска
Построенная по результатам анализа чувствительности диаграмма (блок ячеек
C11.D17) позволяет даже визуально определить примерный объем выпуска продукта (около
80 штук), обеспечивающий при прочих равных условиях безубыточность проекта.
Завершая рассмотрение данного метода, отметим его преимущества и недостатки.
Метод анализа чувствительности является хорошей иллюстрацией влияния отдельных
исходных показателей на результат. Он также показывает направления дальнейших
исследований. Если установлена сильная чувствительность результирующего показателя к
изменениям некоторого исходного, последнему следует уделить особое внимание.
Вместе с тем данный метод обладает и рядом недостатков, наиболее существенные из
них:
• жесткая детерминированность используемых моделей для связи ключевых
переменных;
• не позволяет получить вероятностные оценки возможных отклонений исходных и
результирующих показателей;
• предполагает изменение одного исходного показателя, в то время как остальные
считаются постоянными величинами. Однако на практике между показателями
существуют взаимосвязи и изменение одного из них часто автоматически
приводит к изменениям остальных.
Другим инструментом формирования прогноза является – «Метод сценариев».
В отличие от предыдущих метод сценариев позволяет совместить исследование
чувствительности результирующего показателя с анализом вероятностных оценок его
отклонений. В общем случае процедура использования данного метода в процессе анализа
инвестиционных рисков включает выполнение следующих шагов.
1. Определяют несколько вариантов изменений ключевых исходных показателей
(например, пессимистический, наиболее вероятный и оптимистический).
2. Каждому варианту изменений приписывают его вероятностную оценку.
3. Для каждого варианта рассчитывают вероятное значение критерия NPV (либо IRR, РI,
а также оценки его отклонений от среднего значения.
4. Проводится анализ вероятностных распределений полученных результатов.
Проект с наименьшими стандартным отклонением (σ) и коэффициентом вариации
(СV) считается менее рисковым.
В процессе демонстрации техники применения метода сценариев используется
следующий пример.
Предположим, что по результатам анализа проекта из предыдущего примера были
составлены следующие сценарии его развития и определены возможные вероятности их
осуществления (табл. 18). Провести анализ собственного риска проекта.
Таблица 18. Сценарии реализации проекта по производству продукта "А"
Сценарий Показатели
наихудший Р = 0,25 наилучший Р = 0,25 вероятный P = 0,5
Объем выпуска Q 150 300 200
Цена за штуку Р 40 55 50
Переменные затраты V 35 25 30
Норма дисконта r 15% 8% 10%
Срок проекта n 7 5 5
Для автоматизации решения подобных задач удобно использовать специальный
инструмент EXCEL – «Диспетчер сценариев».
Сценарий в EXCEL - это множество изменяемых ячеек, которое сохраняется под
указанным пользователем именем. Каждому такому набору соответствует своя модель
предположений. Это позволяет проследить, как значения изменяемых ячеек влияют на
модель в целом. Для каждого сценария можно определить до 32 изменяемых ячеек. Как
правило, в качестве изменяемых используются те ячейки, от значений которых зависят
ключевые формулы.
Таким образом, процесс создания сценариев в EXCEL сводится к определению
наборов входных значений. Рассмотрим технику использования сценариев на решении
примера 5.3. При этом в качестве базы для определения сценариев можно использовать
шаблон, сформированный для анализа чувствительности при решении примера 5.2.
Осуществите загрузку шаблона для анализа чувствительности и заполните его
данными для наиболее вероятного сценария развития событий (последняя графа табл. 18).
Приступаем к формированию первого сценария
1. Выделите блок ячеек, которые будут использоваться в качестве изменяемых (в данном
примере блок В2 .В6).
2. Выберите в главном меню тему Сервис пункт Сценарии. В появившемся диалоговом
окне
Диспетчер сценариев задайте операцию Добавить. Результатом выполнения
указанных действий будет появление диалогового окна
Добавление сценария.
3. Введите имя сценария, например Вероятный (рис. 25). При этом в поле Изменяемые
ячейки автоматически будет подставлен выделенный на первом шаге блок. В противном
случае в это поле необходимо ввести координаты входного блока - $В$2.$В$6. Поле
Примечание заполняется по усмотрению пользователя.
4. Нажмите кнопку [ОК]. На экране появится диалоговое окно Значения ячеек
сценария
(рис. 26), содержащее данные выделенного ранее блока В2. В6. Поскольку
они соответствуют наиболее вероятному развитию событий, оставим их без изменений.
Нажмите кнопку [ОК].
Чтобы сформировать следующий сценарий (например, "наихудший" или "наилучший"
), нажмите кнопку [Добавить] и повторите шаги 2 - 4. Отличия будут лишь в задании
имени сценария (шаг 3) и значений входных ячеек (шаг 4), в качестве которых следует
указать данные из соответствующих граф табл. 18.
Завершив формирование сценариев, нажмите кнопку Отчет (Итоги), в появившемся
диалоговом окне укажите операцию Структура (Итоги сценария) и нажмите кнопку
[ОК]. EXCEL автоматически сформирует отчет на отдельном листе рабочей книги и
присвоит ему имя Структура сценария (Итоги сценария, рис. 27).
Рис. 25. Диалоговое окно Добавление сценария "Вероятный"
Рис. 26. Диалоговое окно Значение ячеек сценария "Вероятный"
Рис. 27. Отчет по сценариям
Обратите внимание на то, что в полученном отчете ячейки колонок Е, F, G затенены.
Этим указывается, что их значения используются в сценариях в качестве входных
(изменяемых). Ячейки колонки D показывают текущие в данный момент значения
изменяемых переменных и приведены в отчете просто для справки. Последняя строка отчета
содержит значения результата (критерия NPV) для заданных сценариев развития событий.
Как следует из полученного отчета, чистая приведенная стоимость проекта при
наиболее неблагоприятном развитии событий будет отрицательной (-1259,15). При
нормальном (ожидаемом) или наиболее благоприятном развитии событий проект
обеспечивает получение положительной NPV (3658,73 и 11950,89 соответственно).
Полученные результаты могут быть использованы для. проведения дальнейшего
анализа - оценки вероятностного распределения значений критерия NPV.
Прежде всего, выполним ряд несложных преобразований над отчетом в целях
удаления ненужной информации и проведения дальнейших вычислений. Для этого удалим
два раза колонку
А, затем колонку В и строку 1. Присвоим листу Структура сценария
(Итоги сценария)
какое-нибудь другое имя (например, Анализ рисков). Введем в блок
ячеек
B4.D4
соответствующие значения вероятностей (см. табл. 5.5) и в ячейку
А4 комментарий -
"Вероятности". Изменим комментарий в ячейке А11 на "NPV" .
Приступим к проведению вероятностного анализа.
Прежде всего определим среднее ожидаемое значение
NPV. Для этого можно
использовать соотношение:
(27)
Введем в ячейку А14 комментарий "Средняя ожидаемая NPV", а в ячейку С14
формулу:
=СУММПРОИЗВ (В4 : D4; B11:D11) (Результат: 4502,30).
Отметим, что среднее ожидаемое значение NPV больше величины, которую мы
надеялись получить в наиболее вероятном случае.
Следуя правилам хорошего тона, сразу же определим для ячейки В14 имя –
«Среднее».