Компьютерную имитацию значений ключевых параметров модели можно провести используя
В общем случае можно выделить две составляющие собственного риска инвестиционного проекта:
- 1) чувствительность его чистой приведенной стоимости (NPV) к изменениям значений ключевых показателей;
- 2) величину диапазона возможных изменений ключевых показателей, определяющую их вероятностные распределения.
Поэтому все перечисленные методы количественного анализа инвестиционных рисков базируются на концепции временной стоимости денег и вероятностных подходах.
5.6.1. Метод корректировки ставки дисконтирования
Метод корректировки ставки дисконтирования с учетом риска (risk adjusted discount rate approach - RAD) - наиболее простой и вследствие этого наиболее применяемый на практике. Основная идея метода заключается в корректировке некоторой базовой нормы дисконта, которая считается безрисковой или минимально приемлемой (например, ставка доходности по государственным ценным бумагам, предельная или средняя стоимость капитала для фирмы). Корректировка осуществляется путем прибавления величины требуемой премии за риск, после чего производится расчет критериев эффективности инвестиционного проекта - NPV, IRR, PI по вновь полученной таким образом норме. Решение принимается согласно правилу выбранного критерия.
В общем случае, чем больше риск, ассоциируемый с проектом, тем выше должна быть величина премии, которая может определяться по внутрифирменным процедурам, экспертным путем или по формальным методикам. В частности, в качестве ориентира для установления величины премии за риск может использоваться коэффициент вариации - CV. Чем больше коэффициент, тем большей должна быть премия за риск.
Например, фирма может установить премию за риск в 10% при расширении уже успешно действующего проекта, 15% - в случае, если реализуется новый проект, связанный с основной деятельностью фирмы и 20%, если проект связан с выпуском продукции, производство и реализация которой требуют освоения новых видов деятельности и рынков. Пусть предельная стоимость капитала для фирмы равна 8%. Тогда для перечисленных типов проектов норма дисконта будет соответственно равна - 18%, 23% и 28%. Рассмотрим пример.
Компания «X» рассматривает инвестиционный проект, первоначальные затраты по которому составят 100 000. Ожидаемые поступления от реализации проекта равны 50 000, 60 000 и 40 000. Произвести оценку проекта, если его реализация связана с освоением новых видов деятельности для корпорации.
Поскольку проект связан с освоением новых видов деятельности и рынков, целесообразно установить максимальную надбавку за риск - 20%. С учетом предельной цены капитала для корпорации (8%) скорректированная норма дисконта будет равна 8%+20%=28%. Далее производится расчет NPV по скорректированной норме дисконта. NPV =- 5242,92, поскольку результат отрицательный, согласно правилу NPV, проект следует отклонить.
Как уже отмечалось выше, главные достоинства этого метода - в простоте расчетов, которые могут быть выполнены с использованием даже обыкновенного калькулятора, а также в понятности и доступности. Вместе с тем метод имеет существенные недостатки:
- - Метод корректировки нормы дисконта осуществляет приведение будущих потоков платежей к настоящему моменту времени (т.е. обыкновенное дисконтирование по более высокой норме), но не дает никакой информации о степени риска (возможных отклонениях результатов). При этом полученные результаты существенно зависят только от величины надбавки за риск.
- - Он также предполагает увеличение риска во времени с постоянным коэффициентом, что вряд ли может считаться корректным, так как для многих проектов характерно наличие рисков в начальные периоды с постепенным снижением их к концу реализации. Таким образом, прибыльные проекты, не предполагающие со временем существенного увеличения риска, могут быть оценены неверно и отклонены.
- - Данный метод не несет никакой информации о вероятностных распределениях будущих потоков платежей и не позволяет получить их оценку.
- - Наконец, обратная сторона простоты метода состоит в существенных ограничениях возможностей моделирования различных вариантов, которое сводится к анализу зависимости критериев NPV (IRR, Pi и др.) от изменения только одного показателя - нормы дисконта.
Несмотря на отмеченные недостатки, метод корректировки нормы дисконта широко применяется на практике.
5.6.2. Метод достоверных эквивалентов (коэффициентов определенности) (Certainty coefficients)
В отличие от предыдущего метода, в этом случае осуществляется корректировка не нормы дисконта, а ожидаемых значений потока платежей CFt путем введения специальных понижающих коэффициентов а, для каждого периода реализации проекта. Теоретически значения коэффициентов at могут быть определены из соотношения:
где CCFt - величина чистых поступлений от безрисковой операции в периоде t (например, периодический платеж по долгосрочной государственной облигации, ежегодная сумма процентов по банковскому депозиту и др.);
RFCt- ожидаемая (запланированная) величина чистых поступлений от реализации проекта в периоде t; t — номер периода.
Тогда достоверный эквивалент ожидаемого платежа может быть определен как:
Таким образом осуществляется приведение ожидаемых (запланированных) поступлений к величинам платежей, получение которых практически не вызывает сомнений и значения которых могут быть определены абсолютно точно (достоверно).
Однако в реальной практике для определения значений коэффициентов чаще всего прибегают к методу экспертных оценок. В этом случае коэффициенты отражают степень уверенности специалистов-экспертов в том, что поступление ожидаемого платежа осуществится, или, другими словами - степень уверенности в достоверности его величины.
После того как значения коэффициентов тем или иным путем определены, рассчитывают критерий NPV (IRR, PI) для откорректированного потока платежей по формуле:
Предпочтение отдается проекту скорректированный поток платежей которого обеспечивает получение большей величины NPV. Используемые при этом множители at получили названия коэффициентов достоверности или определенности (certainty coefficients).
Пусть в предыдущем примере в результате опроса экспертов получены следующие значения коэффициентов достоверности: 0,9; 0,85 и 0,6 соответственно.
Расчет скорректированного потока платежей для данного случая приведен в табл. 5.9.
Далее производится расчет NPV для скорректированного потока платежей. NPV = 4442,92.
NPV, рассчитанный для первоначального потока (по ставке 8%), равен 29489,92.
Цель лекции. Научить строить модель задачи, в которой ключевые данные не могут быть точно определены. Показать, как выделить стохастические переменные, выбрать и построить для них закон распределения. Показать применение функций Mathcad для реализации метода Монте-Карло. Представить, как провести анализ результата.
5.1 Постановка задач имитационного моделирования
В первой главе описаны основные этапы экономико-математического моделирования. После того, как математическая модель построена, выбирается программное обеспечение для ее реализации на компьютере, производится разработка и расчет компьютерной модели. Использование компьютеров дает возможность представить сложный экономический процесс посредством искусственных процессов-аналогов, которые можно реализовать на ЭВМ, и выполнить оптимизацию исследуемых параметров в режиме "имитации". Имитация - это компьютерный эксперимент, который проводится с моделью системы, а не с самой системой. Для этого выбираются специальные имитирующие компьютерные программы и технологии программирования. В процессе машинного эксперимента меняют те или иные показатели, т.е. изменяют состояние объекта и регистрируют его поведение в новых условиях. Математическую динамическую модель называют имитационной моделью. Имитационной моделью называют также специальный программный комплекс, который позволяет имитировать деятельность какого-либо сложного объекта [23, 24].
В этой главе рассмотрим задачи, в которых свойства изучаемого объекта точно не определены. Переменные модели - стохастические, их значения не могут быть точно установлены или предсказаны. Неопределенность стохастических переменных связана с недостаточностью информации о них. Неопределенность вероятностных переменных связана с вероятностным характером описываемых ими процессов. Не зная точных исходных данных, используя компьютерные технологии, мы имеем возможность непрерывно и случайным образом генерировать значения исходных величин и затем рассчитывать значения выходной переменной. Для оценки характеристик исследуемых совокупностей используется аппарат теории вероятности, теории случайных функций, понятие случайной величины [25].
Если входные переменные являются стохастическими, то и выходные зависимые переменные также будут стохастическими. При моделировании систем, содержащих вероятностные элементы, используются методы Монте-Карло [24, 26]. Это общее название группы методов решения задач, в которых реализуется построение вероятностных распределений возможных значений выходной стохастической переменной при изменении входных переменных, которые генерируются в виде случайных последовательностей. Стохастические переменные моделируются как случайные величины с определенным законом распределения вероятностей. При численном решении задач, включающих стохастические переменные, используется метод статистических испытаний , который заключается в моделировании случайных величин. На практике эти величины получают путем некоторых операций, результатами которых являются псевдослучайные числа с теми же свойствами, что и числа, получаемые путем случайной выборки. Экономические показатели (цены, прибыль , затраты и т.д) моделируются как случайные числа с каким-либо распределением. Повторяющиеся испытания реализуются путем генерации случайных совокупностей этих параметров. Статистические результаты получаются в результате обработки этих совокупностей. Вероятность того, что результаты отличаются от истинных не более чем на заданную величину, есть функция количества испытаний или размера сгенерированных выборок. Имеется большое число вычислительных алгоритмов, которые позволяют получить длинные последовательности псевдослучайных чисел. Для исследователя представляют интерес выводы, носящие характер статистических показателей, представленных в виде графиков или таблиц, в которых каждому варианту исследуемых параметров поставлены в соответствие определенные средние значения с набором характеристик их распределения.
5.2 Основные этапы имитационного моделирования
В общем случае проведение имитационного моделирования происходит по общепринятой схеме, но поскольку переменные модели являются стохастическими, надо проводить анализ этих переменных. Для моделирования стохастических переменных, используются компьютерные технологии, с помощью которых случайным образом генерируются значения исходных величин и затем рассчитываются значения выходной переменной. Для оценки характеристик исследуемых совокупностей и используется аппарат теории вероятности и теории случайных функций. Приведем основные этапы имитационного моделирования.
Прежде всего, надо четко определить цель моделирования . Когда цель определена, выделяются следующие этапы:
- Выбрать основные объекты и величины, описывающие исследуемый процесс. Определить входные показатели ..
- Выбрать и рассчитать выходные показатели, описывающие модель системы. Для этого выбрать закон изменения переменных, описывающий исследуемый процесс, - функциональную зависимость . Тогда математическая модель системы или процесса имеет вид:
 | ( 5.1) |
Результаты имитационного эксперимента могут использоваться для построения прогнозных моделей и сценариев. Рассмотрим реализацию метода Монте –Карло в программе Mathcad.
5.3 Имитационное моделирование прибыли фирмы методом Монте-Карло
Рассмотрим технику имитационного моделирования методом Монте-Карло на примере исследования прибыли фирмы. Рассмотрим, например, рекламную деятельность фирмы.
Фирма предполагает инвестировать сумму 2 млн. руб., используя денежные средства от рекламной деятельности. Предоставляются рекламные услуги по заказам клиентов. Количество и стоимость заказов - величины постоянно меняющиеся. Затраты составляют в среднем 20% от стоимости заказов. Необходимо оценить, какую прибыль может получить фирма за год в таких условиях, насколько она отличается от 2 млн. руб.
Постановка задачи
Цель задачи – рассчитать прибыль за год и оценить риск быть меньше 2 млн. руб. Разделим переменные на стохастические и постоянные и определим возможные границы их изменений. Стохастические переменные моделируем в виде случайных совокупностей с определенным законом распределения вероятностей. Выходной показатель – прибыль представляем в виде статистических показателей, гистограммы распределения, графиков.
Имитационное моделирование (simulation) является одним из мощнейших методов анализа экономических систем. В общем случае под имитацией понимают процесс проведения экспериментов с математическими моделями сложных систем реального мира.
Цели проведения подобных экспериментов могут быть самыми различными - от выявления свойств и закономерностей исследуемой системы до решения конкретных практических задач. С развитием средств вычислительной техники и программного обеспечения спектр применения имитации в сфере экономики существенно расширился. В настоящее время ее используют как для решения задач внутрифирменного управления, так и для моделирования управления на макроэкономическом уровне. Рассмотрим основные преимущества применения имитационного моделирования в процессе решения задач финансового анализа.
Как следует из определения, имитация - это компьютерный эксперимент. Единственное отличие подобного эксперимента от реального состоит в том, что он проводится с моделью системы, а не с самой системой. Проведение реальных экспериментов с экономическими системами по крайней мере неразумно, требует значительных затрат и вряд ли осуществимо на практике. Таким образом, имитация - единственный способ исследования систем без осуществления реальных экспериментов.
Часто практически невыполним или требует значительных затрат сбор необходимой информации для принятия решений. Например, при оценке риска инвестиционных проектов, как правило, используют прогнозные данные об объемах продаж, затратах, ценах и т.д.
Однако чтобы адекватно оценить риск, необходимо иметь достаточное количество информации для формулировки правдоподобных гипотез о вероятностных распределениях ключевых параметров проекта. В подобных случаях отсутствующие фактические данные заменяются величинами, полученными в процессе имитационного эксперимента (т.е. сгенерированными компьютером).
При решении многих задач финансового анализа используются модели, содержащие случайные величины, поведение которых не поддается управлению со стороны лиц, принимающих решения. Такие модели называют стохастическими. Применение имитации позволяет сделать выводы о возможных результатах, основанные на вероятностных распределениях случайных факторов (величин). Стохастическую имитацию часто называют методом Монте-Карло.
Имитационное моделирование представляет собой серию численных экспериментов, призванных получить эмпирические оценки степени влияния различных факторов (исходных величин) на некоторые зависящие от них результаты (показатели).
В общем случае проведение имитационного эксперимента можно разбить на следующие этапы.
Установить взаимосвязи между исходными и выходными показателями в виде математического уравнения или неравенства.
Задать законы распределения вероятностей для ключевых параметров модели.
Провести компьютерную имитацию значений ключевых параметров модели.
Рассчитать основные характеристики распределений исходных и выходных показателей.
Провести анализ полученных результатов и принять решение.
Результаты имитационного эксперимента могут быть дополнены статистическим анализом, а также использоваться для построения прогнозных моделей и сценариев.
Осуществим имитационное моделирование анализа рисков инвестиционного проекта на основании данных примера, используемого ранее для демонстрации метода сценариев. Для удобства приведем его условия еще раз.
Фирма рассматривает инвестиционный проект по производству продукта "А". В процессе предварительного анализа экспертами выявлены три ключевых параметра проекта
и определены возможные границы их изменений (табл. 22). Прочие параметры проекта считаются постоянными величинами (табл. 23).
Неизменяемые параметры проекта по производству продукта "А"| Показатели | Наиболее вероятное значение |
| Постоянные затраты F | 500 |
| Амортизация A | 100 |
| Налог на прибыль Т | 60% |
| Норма дисконта r | 10% |
| Срок проекта n | 5 |
| Начальные инвестиции I0 | 2000 |
Первый этап анализа согласно сформулированному выше алгоритму состоит в определении зависимости результирующего показателя от исходных. При этом в качестве результирующего показателя обычно выступает один из критериев эффективности: NPV,
Предположим, что используемым критерием является чистая современная стоимость проекта NPV. В целях упрощения будем полагать, что генерируемый проектом поток платежей имеет вид аннуитета. Тогда величина потока платежей NCF для любого периода t одинакова и может быть определена из соотношения:
NCF = [Q(P - V) - F - A](1 - T) + A (35)
Следующий этап проведения анализа состоит в выборе законов распределения вероятностей ключевых переменных.
По условиям примера ключевыми варьируемыми параметрами являются переменные расходы V, объем выпуска Q и цена Р. Диапазоны возможных изменений варьируемых показателей приведены в табл. 22. При этом будем исходить из предположения, что все ключевые переменные имеют равномерное распределение вероятностей.
Проведение имитационных экспериментов в среде EXCEL можно осуществить двумя способами - с помощью встроенных функций и путем использования инструмента Генератор случайных чисел дополнения Анализ данных (Analysis ToolPack). Для сравнения ниже рассматриваются оба способа. При этом основное внимание уделено технологии проведения имитационных экспериментов и последующего анализа результатов с использованием инструмента Генератор случайных чисел.
- Имитационное моделирование с применением функций EXCEL
Применение встроенных функций целесообразно лишь в том случае, когда вероятности реализации всех значений случайной величины считаются одинаковыми. Тогда для имитации значений требуемой переменной можно воспользоваться математическими функциями СЛЧИС или СЛУЧМЕЖДУ. Форматы функций приведены в табл. 24.
Функция СЛЧИС - возвращает равномерно распределенное случайное число Е, большее либо равное 0 и меньшее 1, т.е.: 0 lt; Е lt; 1. Вместе с тем путем несложных преобразований с ее помощью можно получить любое случайное вещественное число. Например, чтобы получить случайное число между а и b, достаточно задать в любой ячейке ЭТ следующую формулу:
Эта функция не имеет аргументов. Если в Excel установлен режим автоматических вычислений, принятый по умолчанию, то возвращаемый функцией результат будет изменяться всякий раз, когда происходит ввод или корректировка данных. В режиме ручных вычислений пересчет всей ЭТ осуществляется только после нажатия клавиши [F9].
Настройка режима управления вычислениями производится установкой
соответствующего флажка в подпункте Вычисления пункта Параметры темы Сервис главного меню.
В целом применение данной функции при решении задач финансового анализа ограничено рядом специфических приложений. Однако ее удобно использовать в некоторых случаях для генерации значений вероятности событий, а также вещественных чисел.
Функция СЛУЧМЕЖДУ (нижн граница; верхн граница), как следует из названия, позволяет получить случайное число из заданного интервала. При этом тип возвращаемого числа (т.е. вещественное или целое) зависит от типа заданных аргументов.
В качестве примера сгенерируем случайное значение для переменной Q (объем выпуска продукта). Согласно данным табл. 6.1, эта переменная принимает значения из диапазона 150 - 300.
Введите в любую ячейку формулу:
=СЛУЧМЕЖДУ(150; 300) (Результат: 210).
Если задать аналогичные формулы для переменных Р и V, а также формулу для вычисления NPV и скопировать их требуемое число раз, можно получить генеральную совокупность, содержащую различные значения исходных показателей и полученных результатов. После этого, используя рассмотренные в предыдущих главах статистические функции, нетрудно рассчитать соответствующие параметры распределения и провести вероятностный анализ.
Продемонстрируем изложенный подход на решении примера Перед тем как приступить к разработке шаблона, целесообразно установить в режим ручных вычислений. Для этого необходимо выполнить следующие действия.
Выбрать в главном меню тему Сервис.
Выбрать пункт Параметры, подпункт Вычисления.
Установить флажок Вручную и нажать кнопку [ОК].
Приступаем к разработке шаблона. С целью упрощения и повышения наглядности анализа выделим для его проведения в рабочей книге EXCEL два листа.
Первый лист - Имитация, предназначен для построения генеральной совокупности (рис. 32). Определенные в данном листе формулы и собственные имена ячеек приведены в табл. 25 и 26.
Первая часть листа (блок ячеек А1. Е7) предназначена для ввода диапазонов изменений ключевых переменных, значения которых будут генерироваться в процессе проведения эксперимента. В ячейке В7 задается общее число имитаций (экспериментов).
Формула, заданная в ячейке Е7, вычисляет номер последней строки выходного блока, в который будут помещены полученные значения. Смысл этой формулы далее раскрыт. Вторая часть листа (блок ячеек А9.Е11) предназначена для проведения имитации. Формулы в ячейках А10.С11 генерируют значения для соответствующих переменных с учетом заданных в ячейках В3. С5 диапазонов их изменений.
1. Установить взаимосвязи между исходными и выходными показателями в виде математического уравнения или неравенства.
2. Задать законы распределения вероятностей для ключевых параметров модели.
3. Провести компьютерную имитацию значений ключевых параметров модели.
4. Рассчитать основные характеристики распределений исходных и выходных показателей.
5. Провести анализ полученных результатов и принять решение.
Краткие теоретические сведения
Имитационное моделирование (simulation) является одним из мощнейших методов анализа экономических систем.
В общем случае, под имитацией понимают процесс проведения на ЭВМ экспериментов с математическими моделями сложных систем реального мира.
Цели проведения подобных экспериментов могут быть самыми различными – от выявления свойств и закономерностей исследуемой системы, до решения конкретных практических задач.
При решении многих задач финансового анализа используются модели, содержащие случайные величины, поведение которых не поддается управлению со стороны лиц, принимающих решения. Такие модели называют стохастическими. Применение имитации позволяет сделать выводы о возможных результатах, основанные на вероятностных распределениях случайных факторов (величин). Стохастическую имитацию часто называют методом Монте-Карло.
Имитационное моделирование представляет собой серию численных экспериментов призванных получить эмпирические оценки степени влияния различных факторов (исходных величин) на некоторые зависящие от них результаты (показатели).
Результаты имитационного эксперимента могут быть дополнены статистическим анализом, а также использоваться для построения прогнозных моделей и сценариев.
Осуществим имитационное моделирование анализа рисков инвестиционного проекта на основании данных примера.
Фирма рассматривает инвестиционный проект по производству продукта "А". В процессе предварительного анализа экспертами были выявлены три ключевых параметра проекта и определены возможные границы их изменений (табл. .1). Прочие параметры проекта считаются постоянными величинами (табл. 2).
Таблица 1 – Ключевые параметры проекта по производству продукта "А"
| Сценарий | Показатели | |
| Наихудший | Наилучший | Вероятный |
| Объем выпуска – Q | ||
| Цена за штуку – P | ||
| Переменные затраты – V |
Таблица 2 – Неизменяемые параметры проекта по производству продукта "А"
| Показатели | Наиболее вероятное значение |
| Постоянные затраты – F | |
| Амортизация – A | |
| Налог на прибыль – T | 60% |
| Норма дисконта – r | 10% |
| Срок проекта – n | |
| Начальные инвестиции – I0 |
Первым этапом анализа является определение зависимости результирующего показателя от исходных. При этом в качестве результирующего показателя выступает чистая современная стоимость проекта NPV:
(1)
где NCFt – величина чистого потока платежей в периоде t.
По условиям примера, значения нормы дисконта r и первоначального объема инвестиций I0 известны и считаются постоянными в течении срока реализации проекта (табл. 2).
По условиям примера ключевыми варьируемыми параметрами являются: переменные расходы V, объем выпуска Q и цена P. Диапазоны возможных изменений варьируемых показателей приведены в табл. 1. При этом будем исходить из предположения, что все ключевые переменные имеют равномерное распределение вероятностей.
Реализация третьего этапа может быть осуществлена только с применением ЭВМ, оснащенной специальными программными средствами. Поэтому прежде чем приступить к третьему этапу – имитационному эксперименту, познакомимся с соответствующими средствами Excel, автоматизирующими его проведение.
Первый лист – "Имитация", предназначен для построения генеральной совокупности (рис.1). Определенные в данном листе формулы и собственные имена ячеек приведены в таб.4 и 5.
Рис. 1. – Лист "Имитация"
Таблица 4 – Формулы листа "Имитация"
| Ячейка | Формула |
| Е7 | =B7+10-2 |
| A10 | =СЛУЧМЕЖДУ($B$3;$C$3) |
| A11 | =СЛУЧМЕЖДУ($B$3;$C$3) |
| B10 | =СЛУЧМЕЖДУ($B$4;$C$4) |
| B11 | =СЛУЧМЕЖДУ($B$4;$C$4) |
| C10 | =СЛУЧМЕЖДУ($B$5;$C$5) |
| C11 | =СЛУЧМЕЖДУ($B$5;$C$5) |
| D10 | =(B10*(C10-A10)-Пост_расх-Аморт)*(1-Налог)+Аморт |
| D11 | =(B11*(C11-A11)-Пост_расх-Аморт)*(1-Налог)+Аморт |
| E10 | =ПЗ(Норма;Срок;-D10)-Нач_инвест |
| E11 | =ПЗ(Норма;Срок;-D11)-Нач_инвест |
Таблица 5 – Имена ячеек листа "Имитация"
| Адрес ячейки | Имя | Комментарии |
| Блок A10:A11 | Перем_расх | Переменные расходы |
| Блок B10:B11 | Количество | Объем выпуска |
| Блок C10:C11 | Цена | Цена изделия |
| Блок D10:D11 | Поступления | Поступления от проекта NCFt |
| Блок E10:E11 | ЧСС | Чистая современная стоимость NPV |
Первая часть листа (блок ячеек А1:Е7) предназначена для ввода диапазонов изменений ключевых переменных, значения которых будут генерироваться в процессе проведения эксперимента. В ячейке В7 задается общее число имитаций (экспериментов). Формула, заданная в ячейке Е7, вычисляет номер последней строки выходного блока, в который будут помещены полученные значения. Смысл этой формулы будет раскрыт позже.
Вторая часть листа (блок ячеек А9:Е11) предназначена для проведения имитации. Формулы в ячейках А10:С11 генерируют значения для соответствующих переменных с учетом заданных в ячейках В3:С5 диапазонов их изменений. Обратите внимание на то, что при указании нижней и верхней границы изменений используется абсолютная адресация ячеек.
Формулы в ячейках D10:E11вычисляют величину потока платежей и его чистую современную стоимость соответственно. При этом значения постоянных переменных берутся из следующего листа шаблона – "Результаты анализа".
Лист "Результаты анализа" кроме значений постоянных переменных содержит также функции, вычисляющие параметры распределения изменяемых (Q, V, P) и результатных (NCF, NPV) переменных и вероятности различных событий. Определенные для данного листа формулы и собственные имена ячеек приведены в табл. 6 и 7. Общий вид листа показан на рис. 2.
Таблица 6 – Формулы листа "Результаты анализа".
| Ячейка | Формула |
| B8 | =СРЗНАЧ(Перем_расх) |
| B9 | =СТАНДОТКЛОНП(Перем_расх) |
| B10 | =B9/B8 |
| B11 | =МИН(Перем_расх) |
| B12 | =МАКС(Перем_расх) |
| C8 | =СРЗНАЧ(Количество) |
| C9 | =СТАНДОТКЛОНП(Количество) |
| C10 | =C9/C8 |
| C11 | =МИН(Количество) |
| C12 | =МАКС(Количество) |
| D8 | =СРЗНАЧ(Цена) |
| D9 | =СТАНДОТКЛОНП(Цена) |
| D10 | =D9/D8 |
| D11 | =МИН(Цена) |
| D12 | =МАКС(Цена) |
| E8 | =СРЗНАЧ(Поступления) |
| E9 | =СТАНДОТКЛОНП(Поступления) |
| E10 | =E9/E8 |
| E11 | =МИН(Поступления) |
| E12 | =МАКС(Поступления) |
| F8 | =СРЗНАЧ(ЧСС) |
| F9 | =СТАНДОТКЛОНП(ЧСС) |
| F10 | =F9/F8 |
| F11 | =МИН(ЧСС) |
| F12 | =МАКС(ЧСС) |
| F13 | =СЧЁТЕСЛИ(ЧСС;"<0") |
| F14 | =СУММЕСЛИ(ЧСС;"<0") |
| F15 | =СУММЕСЛИ(ЧСС;">0") |
| Е18 | =НОРМАЛИЗАЦИЯ(D18;$F$8;$F$9) |
| F18 | =НОРМСТРАСП(E18) |
Таблица 7 – Имена ячеек листа "Результаты анализа"
| Адрес ячейки | Имя | Комментарии |
| B2 | Нач_инвест | Начальные инвестиции |
| B3 | Пост_расх | Постоянные расходы |
| B4 | Аморт | Амортизация |
| D2 | Норма | Норма дисконта |
| D3 | Налог | Ставка налога на прибыль |
| D4 | Срок | Срок реализации прока |
Рис. 2. Лист "Результаты анализа"
В данном случае, заданная в ячейке F13, эта функция осуществляет подсчет количества отрицательных значений NPV, содержащихся в блоке ячеек ЧСС (см. табл. 7).
Механизм действия функции СУММЕСЛИ() аналогичен функции СЧЕТЕСЛИ().Отличие заключается лишь в том,что эта функция суммирует значения ячеек в указанном блоке, если они удовлетворяют заданному условию. Функция имеет следующий формат:
В данном случае, заданные в ячейках F14.F15, функции осуществляет подсчет суммы отрицательных (ячейка F14) и положительных (ячейка F14) значений NPV,содержащихся в блоке ЧСС. Смысл этих расчетов будет объяснен позже.
Две последние формулы (ячейки Е18и F18) предназначены для проведения вероятностного анализа распределения NPV и требуют небольшого теоретического отступления.
В рассматриваемом примере мы исходим из предположения о независимости и равномерном распределении ключевых переменных Q, V, P. Однако какое распределение при этом будет иметь результатная величина – показатель NPV, заранее определить нельзя.
Одно из возможных решений этой проблемы – попытаться аппроксимировать неизвестное распределение каким-либо известным. При этом в качестве приближения удобнее всего использовать нормальное распределение. Это связано с тем, что в соответствии с центральной предельной теоремой теории вероятностей при выполнении определенных условий сумма большого числа случайных величин имеет распределение, приблизительно соответствующее нормальному.
Приведение случайной переменной E к стандартно распределенной величине Z осуществляется с помощью т.н. нормализации – вычитания средней и последующего деления на стандартное отклонение:
(3).
Как следует из (3), величина Z выражается в количестве стандартных отклонений. Для вычисления вероятностей по значению нормализованной величины Z используются специальные статистические таблицы.
В Excel подобные вычисления осуществляются с помощью статистических функций НОРМАЛИЗАЦИЯ() и НОРМСТРАСП().
Функция НОРМАЛИЗАЦИЯ(x; среднее; станд_откл)
х – нормализуемое значение;
среднее – математическое ожидание случайной величины Е;
станд_откл – стандартное отклонение.
Полученное значение Z является аргументом для следующей функции – НОРМСТРАСП().
Функция НОРМСТРАСП(Z)
Эта функция возвращает стандартное нормальное распределение, т.е. вероятность того, что случайная нормализованная величина Е будет меньше или равна х. Она имеет всего один аргумент – Z, вычисляемый функцией НОРМАЛИЗАЦИЯ().
Нетрудно заметить,что эти функции следует использовать в тандеме. При этом наиболее эффективным и компактным способом их задания является указаниефункции НОРМАЛИЗАЦИЯ() в качестве аргумента функции – НОРМСТРАСП(), т.е.:
=НОРМСТРАСП(НОРМАЛИЗАЦИЯ(x; среднее; станд_откл)).
С целью повышения наглядности, в проектируемом шаблоне функции заданы раздельно (ячейки Е18 и F18).
Сформируйте данный шаблон и сохраните его на магнитном диске под именем SIMUL_1.XLT. Приступаем к имитационному эксперименту. Для его проведения необходимо выполнить следующие шаги.
1. Ввести значения постоянных переменных (табл. 2) в ячейки В2:В4 и D2:D4 листа "Результаты анализа".
2. Ввести значения диапазонов изменений ключевых переменных (табл. 1) в ячейки В3:С5 листа "Имитация".
3. Задать в ячейке В7 требуемое число экспериментов.
4. Установить курсор в ячейку А11 и вставить необходимое число строк в шаблон (номер последней строки будет вычислен в Е7).
5. Скопировать формулы блока А10:Е10 требуемое количество раз.
6. Перейти к листу "Результаты анализа" и проанализировать полученные результаты.
Рассмотрим реализацию выделенных шагов более подробно. Выполнение первых трех пунктов не должно вызвать особых затруднений. Введите значения постоянных переменных в ячейки В2.В4 листа "Результаты анализа". Введите значения диапазонов изменений ключевых переменных в ячейки В3.С5 листа "Имитация". Укажите в ячейке В7 число проводимых экспериментов, например – 500. Установите табличный курсор в ячейку А11.
На следующем шаге необходимо вставить в шаблон нужное количество строк (498) . Однако выделение такого количества строк при помощи указателя мыши – достаточно трудоемкая операция. К счастью Excel предоставляет более эффективные процедуры для выполнения подобных операций. В частности, в данном случае можно воспользоваться операцией перехода, которую также удобно применять и для выделения больших диапазонов ячеек.
Нажмите функциональную клавишу [F5]. На экране появится окно диалога "Переход" (рис.3).
Рис. 3. Окно диалога "Переход"
Для перехода к нужному участку электронной таблицы достаточно указать в поле "Ссылка" адрес или имя соответствующей ячейки (блока). В данном случае, таким адресом будет любая ячейка последней вставляемой строки, номер которой вычислен в ячейке Е7 (508). Например, в качестве адреса перехода может быть указана ячейка А508.
Введите в поле "Ссылка" адрес: А508 и нажмите комбинацию клавиш [SHIFT] + [ENTER]. Результатом выполнения этих действий будет выделение блока А11:А508. После чего осуществите вставку строк любым из известных вам способов.
Теперь необходимо заполнить вставленные строки формулами блока ячеек А10:Е10. Для этого выполните следующие действия.
1. Выделите и скопируйте в буфер блок ячеек А10:Е10.
3. Нажмите клавишу [ENTER].
4. Нажмите клавишу [F9].
Результатом выполнения этих действий будет заполнение блока А10:Е509случайными значениями ключевых переменных V, Q, P и результатами вычислений величин NCF и NPV. Фрагмент результатов имитации, полученных автором, приведен на рис. 4 . Соответствующие проведенному эксперименту результаты анализа приведены на рис. 5.
Рис. 4. Результаты имитации
Рис. 5. Результаты анализа
На практике одним из важнейших этапов анализа результатов имитационного эксперимента является исследование зависимостей между ключевыми параметрами. Как было показано в предыдущей главе, количественная оценка вариации напрямую зависит от степени корреляции между случайными величинами. Методы оценки степени зависимости, а также технология ее автоматизации путем применения специальных инструментов Excel, будут продемонстрированы ниже. Здесь же мы ограничимся визуальным (графическим) исследованием. На рис. 6 приведен график распределения значений ключевых параметров V, P и Q, построенный на основании 65 имитаций.
Нетрудно заметить, что в целом, вариация значений всех трех параметров носит случайный характер, что подтверждает принятую ранее гипотезу об их независимости. Для сравнения ниже приведен график распределений потока платежей NCF и величины NPV (рис. 7).
Рис. 6.6. Распределение значений параметров V, P и Q
Рис. 6.7. Зависимость между NCF и NPV
Как и следовало ожидать, направления колебаний здесь в точности совпадают и между этими величинами существует сильная корреляционная связь, близкая к функциональной. Дальнейшие расчеты показали, что величина коэффициента корреляции между полученными распределениями NCF и NPV оказалась равной 1.
Подводя итоги отметим, что в целом применение рассмотренной технологии проведения имитационных экспериментов в среде Excel – достаточно трудоемкий процесс, который к тому же ограничивается случаем равномерного распределения исследуемых переменных.
Читайте также: