Главная | О нас | Обратная связь Автоматизация Автостроение Антропология Археология Архитектура Астрономия Предпринимательство Биология Биотехнология Ботаника Бухгалтерский учет Генетика География Геология Государство Демография Деревообработка Журналистика и СМИ Зоология Изобретательство Иностранные языки Информатика Информационные системы Искусство История Кинематография Кораблестроение Кулинария Культура Лексикология Литература Логика Маркетинг Математика Математический анализ Материаловедение Машиностроение Медицина Менеджмент Металлургия Метрология Механика ОБЖ Охрана Труда Педагогика Политология Правоотношение Программирование Производство Промышленность Психология Радио Разное Социология Спорт Статистика Строительство Теология Технологии Туризм Усадьба Физика Физиология Философия Финансы Химия Черчение Экология Экономика Электротехника Имитационное моделирование с применением инструмента «Генерация случайных чисел» 2019-11-13 245 Обсуждений (0) Имитационное моделирование с применением инструмента «Генерация случайных чисел» 0.00 из 5.00 0 оценок Скачать документ РЕАЛИЗАЦИЯ МЕТОДА МОНТЕ-КАРЛО В EXCEL Фирма рассматривает инвестиционный проект по производству продукта «А». В процессе анализа экспертами были выявлены три ключевых параметра проекта и определены возможные границы их изменений (табл. 1). Прочие параметры проекта считаются постоянными величинами в течение срока реализации проекта (табл. 2) Таблица1. Ключевые параметры проекта по производству продукта «А» Показатели	Сценарий
Худший	Лучший	Вероятный
Объем выпуска Q	150	300	200
Цена за 1 шт.- P	40	55	50
Переменные затраты V	35	25	30

Таблица 2 Неизменные параметры проекта

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

Проведение имитационных экспериментов по методу Монте-Карло в Excel можно осуществить двумя способами – с помощью встроенных функций и путем использования инструмента «Анализ данных» - «Генератор случайных чисел».

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

Таблица 3. Встроенные функции Имитационного моделирования

Чтобы значения случайных чисел не изменялись при каждом пересчете листа, нужно отключить режим автоматических вычислений, выбрав на вкладке «Формулы» - «Параметры вычислений» пункт «Вручную». Тогда пересчет листа будет осуществляться после нажатия клавиши F9.

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

На листе «Формулы» строим генеральную совокупность, задаем исходные значения и вводим формулы. Пример оформления листа приведен на рис.1. Для ряда ячеек задаем имена, чтобы было их удобнее использовать при расчете: вкладка «Формулы», кнопка «Присвоить имя».

В ячейках $A$11:$A$510, $B$11:$B$510 и $C$11:$C$510 генерируют значения для соответствующих переменных с учетом заданных в ячейках С3:С5 диапазонов их изменений с помощью функции СЛУЧМЕЖДУ(нижн_граница; верхн_граница).

Поступления NFC =( Q *( P - V )- F - A )*(1- T )+ A

Чистый приведенный доход NPV =ПС( r ; n ;- NFC )- I

Функция ПС (ставка;кпер;плт;бс;тип) возвращает приведенную (к текущему моменту) стоимость инвестиции.

В правой части листа кроме значений постоянных переменных  содержатся также функции, вычисляющие параметры распределения изменяемых (Q, V, P) и результатных (NCF, NPV) переменных и вероятности различных событий. Соответствующие формулы приведены в табл 4.

Рис 1. Лист «Формулы»

Таблица 4. Формулы листа «Формулы»

В рассматриваемом примере сделано предположение о независимости и равномерном распределении ключевых переменных Q, V, P. Однако какое распределение при этом будет показатель NPV заранее определить нельзя, но можно попытаться аппроксимировать неизвестное распределение каким-либо известным (нормальным распределением). В прикладном анализе для целей аппроксимации широко применяется частный случай нормального распределения – стандартное нормальное распределение. Математическое ожидание стандартно распределенной случайной величины Е равно 0: M(E) = 0. График этого распределения симметричен относительно оси ординат, и характеризуется всего одним параметром – стандартным отклонением s , равным 1. Приведение случайной переменной E к стандартно распределенной осуществляется с помощью нормализации:

Для вычисления вероятностей по значению нормализованной величины Z используются специальные статистические таблицы. В Excel подобные вычисления осуществляются с помощью статистических функций НОРМАЛИЗАЦИЯ (x;среднее;стандартное_откл) и НОРМСТРАСП(z) (данная функция используется вместо таблицы площадей стандартной нормальной кривой). Эти функции заданы в ячейках K21 и L21. В ячейке L21 отображается вероятность того, что чистый приведенный доход будет меньше некоторого значения X. Сумма всех отрицательных значений NPV в полученной генеральной совокупности (ячейка L17) может быть интерпретирована как чистая стоимость неопределенности для инвестора в случае принятия проекта. Аналогично сумма всех положительных значений NPV (ячейка L18) может трактоваться как чистая стоимость неопределенности для инвестора в случае отклонения проекта.

Имитационное моделирование с применением инструмента «Генерация случайных чисел»

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

Рассмотрим наш пример. Также предположим, что распределение ключевых переменных является нормальным. Создайте копию листа «Формулы», переименуйте его в «Генерация», очистите диапазоны $A$11:$A$510, $B$11:$B$510 и $C$11:$C$510, а также внесите соответствующие изменения согласно рис. 2 и описанию ниже.

Рис. 2 Лист «Генерация»

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

Таблица 5. Формулы для расчета вероятностей листа «Генерация»

Таблица 6. Формулы для расчета параметров распределения

Проведение имитационного эксперимента заключается в следующем: в меню «Анализ данных» выберите пункт «Генерация случайных чисел». В появившемся диалоговом окне выберите тип распределения «Нормальное», число переменных 1, число случайных чисел 500. При заполнении полей окна «Генератор случайных чисел» следует обратить внимание, что параметры «Среднее» и «Стандартное отклонение» можно задать только в виде констант. Использовать адреса ячеек и собственные имена не допустимо! В результате, переменные затраты будут заполнены сгенерированными случайными значениями. Аналогично заполните количества и цены.

Указание аргумента «Случайное рассеивание» равным 1, позволяет при повторных запусках генератора получать те же значения случайных величин, что и при первом.

В качестве вывода: оценивается величина ожидаемого истого приведенного дохода (NPV) и стандартное отклонение. Коэффициент вариации должен быть меньше 1, таким образом, риск данного проекта в целом ниже среднего риска инвестиционного портфеля фирмы. Результаты вероятностного анализа покажут, каков шанс получить отрицательную величину NPV. Также оцениваем общее число отрицательных значений NPV. Можно рассчитать, с какой вероятностью чистый приведенный доход проекта будет больше 0. Также оценивается вероятность попадания NPV в интервал М(NPV)+s и вероятность попадания значения NPV в интервал [М(NPV)-s; М(NPV)].

2019-11-13

245

Обсуждений (0)

Имитационное моделирование с применением инструмента «Генерация случайных чисел» 0.00 из 5.00 0 оценок

Скачать документ