Математическое моделирование. Метод МонтеКарло
 Скачать 0.84 Mb.
|
|
| Показатели | Минимум | Максимум |
| Объем реализации | 150 | 300 |
| Цена единицы товара | 40 | 55 |
| Переменные затраты | 25 | 35 |
Примечание: для упрощения расчетов из налогов учитывать следует только налог на прибыль (20 %).
Задание: провести анализ рисков методом имитационного моделирования:
- создать имитационную модель оценки инвестиционного проекта (не менее 1000 итераций);
- провести статистический анализ полученных результатов по предложенной схеме (см. Приложение)
-построить гистограмму на основе результатов, полученных в ходе имитационного моделирования
- сделать выводы.
Приложение
Метод Монте-Карло.
Моделирование по методу Монте-Карло представляет собой автоматизированную математическую методику, предназначенную для учета риска в процессе количественного анализа и принятия решений. Эта методика применяется профессионалами в разных областях, таких как финансы, управление проектами, энергетика, производство, проектирование, НИОКР, страхование, нефтегазовая отрасль, транспорт и охрана окружающей среды.
Каждый раз в процессе выбора направления дальнейших действий моделирование по методу Монте-Карло позволяет специалисту, принимающему решения, рассматривать целый спектр возможных последствий и оценивать вероятность их наступления. Этот метод демонстрирует возможности, лежащие на противоположных концах спектра (результаты игры ва-банк и принятия наиболее консервативных мер), а также вероятные последствия умеренных решений.
Алгоритм проведения имитационного моделирования.
1 шаг. Создание прогнозной модели в формате табличного редактора Excel.
Функции Excel необходимые для создания такой модели:
Синтаксис'>Функция ЕСЛИ
Функция ЕСЛИ, одна из логических функций, служит для возвращения разных значений в зависимости от того, соблюдается ли условие.
Синтаксис
ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])
ПС (функция ПС)
ПС — одна из финансовых функций, возвращающая приведенную (к текущему моменту) стоимость займа или инвестиции на основе постоянной процентной ставки. Функцию ПС можно применять как для периодических постоянных выплат (например, по ипотеке или другим займам), так и для будущей стоимости, являющейся целью инвестиции.
Синтаксис
ПС(ставка; кпер; плт; [бс]; [тип])
Аргументы функции ПС описаны ниже.
Ставка Обязательный. Процентная ставка за период. Например, если получен кредит на автомобиль под 10 процентов годовых и выплаты производятся ежемесячно, процентная ставка за месяц составит 10%/12 (0,83%). В качестве значения аргумента "ставка" нужно ввести в формулу 10%/12, 0,83% или 0,0083.
Кпер Обязательный. Общее число периодов платежей для ежегодного платежа. Например, если получен кредит на 4 года на покупку автомобиля и платежи производятся ежемесячно, то кредит имеет 4*12 (или 48) периодов. В качестве значения аргумента "кпер" в формулу нужно ввести число 48.
Плт Обязательный. Выплата, производимая в каждый период и не меняющаяся на протяжении всего периода ежегодного платежа. Обычно аргумент "плт" состоит из выплат в счет основной суммы и платежей по процентам, но не включает в себя другие сборы или налоги. Например, ежемесячная выплата по кредиту в размере 10 000 под 12 процентов годовых на 4 года составит 263,33 . В качестве значения аргумента "плт" нужно ввести в формулу число -263,33. Если он опущен, аргумент "бс" является обязательным.
Бс Необязательный. Значение будущей стоимости, т. е. желаемого остатка средств после последнего платежа. Если аргумент "бс" опущен, предполагается, что он равен 0 (например, будущая стоимость для займа равна 0). Предположим, что для определенной цели требуется накопить 50 000 за 18 лет: в этом случае будущая стоимость равна 50 000 . Предположив, что заданная процентная ставка останется без изменений, можно определить, какую сумму необходимо откладывать каждый месяц. Если аргумент "бс" опущен, необходимо использовать аргумент "плт".
Тип Необязательный. Число 0 или 1, обозначающее, когда должна производиться выплата.
| Тип | Когда нужно платить |
| 0 или опущен | В конце периода |
| 1 | В начале периода |
Замечания
Убедитесь, что единицы измерения аргументов "ставка" и "кпер" используются согласованно. При ежемесячных выплатах по четырехгодичному займу из расчета 12 процентов годовых используйте значение 12%/12 в качестве аргумента "ставка" и 4*12 — в качестве аргумента "кпер". При ежегодных платежах по тому же займу используйте значение 12% в качестве аргумента "ставка" и 4 — в качестве аргумента "кпер".
2 шаг. Задание функции распределения каждой переменной, которая оказывает влияние на формирование денежного потока, и введение ее минимального и максимального значений. Для этого присваиваем переменной вероятностное распределение, исходя из оценок или наших ожиданий (например, треугольное, нормальное или равномерное распределение). Для этого используем либо функцию СЛУЧМЕЖДУ, либо надстройку «Генерация случайных чисел».
Функции EXCEL необходимые для задания функции распределения:
СЛУЧМЕЖДУ (функция СЛУЧМЕЖДУ)
Описание:
Возвращает случайное целое число, находящееся в диапазоне между двумя заданными числами. При каждом вычислении листа возвращается новое случайное целое число.
Синтаксис:
СЛУЧМЕЖДУ(нижн_граница;верхн_граница)
Нижн_граница Обязательный. Наименьшее целое число, которое возвращает функция СЛУЧМЕЖДУ.
Верхн_граница Обязательный. Наибольшее целое число, которое возвращает функция СЛУЧМЕЖДУ.
Примечание: При пересчете значений листа после ввода формулы или данных в другую ячейку или при выполнении пересчета вручную (нажатие клавиши F9) создается новое случайное число для любой формулы с использованием функции СЛУЧМЕЖДУ.
Надстройка Генерация случайных чисел.
Использовать нормальное распределение. Количество итераций определить исходя из анализа, который проводится на 4 шаге.
3 шаг. Проведение расчетных итераций, которое является полностью компьютеризированной частью анализа рисков проекта. 200-500 итераций обычно достаточно для хорошей репрезентативной выборки.
В процессе каждой итерации происходит случайный выбор значений ключевых переменных из специфицированного интервала в соответствии с вероятностными распределениями и условиями корреляции. Затем рассчитываются и сохраняются результирующие показатели (например, NPV).
4 шаг. Проведение статистического анализа полученных результатов и построение гистограммы распределения результирующего показателя, по которой можно оценить риск проекта, разброс возможных результатов.
| Номер | Показатели | Формулы |
| 1 | Минимум | МИН(число1;[число2];...) |
| 2 | Максимум | МАКС(число1;[число2];...) |
| 3 | Среднее значение | СРЗНАЧ(число1;[число2];…) |
| 4 | Стандартное отклонение | СТАНДОТКЛОН.Г(число1;[число2];…) |
| 5 | Коэффициент вариации | = стр.4/стр.3 |
| 6 | Число случаев NPV<0 | СЧЁТЕСЛИ(диапазон;критерий) |
| 7 | Число случаев NPV>0 | СЧЁТЕСЛИ(диапазон;критерий) |
| 8 | Сумма убытков | СУММЕСЛИ(диапазон; условие; [диапазон_суммирования]) |
| 9 | Сумма доходов | СУММЕСЛИ(диапазон; условие; [диапазон_суммирования]) |
МИН (функция МИН)
Описание
Возвращает наименьшее значение в списке аргументов.
Синтаксис
МИН(число1;[число2];...)
Аргументы функции МИН описаны ниже.
Число1, число2,... Аргумент "число1" является обязательным, последующие числа необязательные. От 1 до 255 чисел, среди которых требуется найти наименьшее.
Замечания
Аргументы могут быть либо числами, либо содержащими числа именами, массивами или ссылками.
Учитываются логические значения и текстовые представления чисел, которые введены непосредственно в список аргументов.
Если аргумент является массивом или ссылкой, то учитываются только числа. Пустые ячейки, логические значения и текст в массиве или ссылке игнорируются.
Если аргументы не содержат чисел, функция МИН возвращает значение 0.
Аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, приводят к возникновению ошибок.
Если в ссылку в качестве части вычислений необходимо добавить логические значения и текстовые представления, воспользуйтесь функцией МИНА.
МАКС (функция МАКС)
Описание
Возвращает наибольшее значение из набора значений.
Синтаксис
МАКС(число1;[число2];...)
Аргументы функции МАКС описаны ниже.
Число1, число2,... Аргумент "число1" является обязательным, последующие числа необязательные. От 1 до 255 чисел, среди которых требуется найти наибольшее.
Замечания
Аргументы могут быть либо числами, либо содержащими числа именами, массивами или ссылками.
Учитываются логические значения и текстовые представления чисел, которые введены непосредственно в список аргументов.
Если аргумент является массивом или ссылкой, то в нем учитываются только числа или ссылки. Пустые ячейки, логические значения и текст в массиве или ссылке игнорируются.
Если аргументы не содержат чисел, функция МАКС возвращает значение 0 (ноль).
Аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, приводят в возникновению ошибок.
Если в ссылку в качестве части вычислений необходимо добавить логические значения и текстовые представления, воспользуйтесь функцией МАКСА.
СРЗНАЧ (функция СРЗНАЧ)
Описание
Возвращает среднее значение (среднее арифметическое) аргументов. Например, если диапазон A1:A20 содержит числа, формула =СРЗНАЧ(A1:A20) возвращает среднее значение этих чисел.
Синтаксис
СРЗНАЧ(число1;[число2];…)
Аргументы функции СРЗНАЧ описаны ниже.
Число1. Обязательный аргумент. Первое число, ссылка на ячейку или диапазон, для которого требуется вычислить среднее значение.
Число2, ... Необязательный аргумент. Дополнительные числа, ссылки на ячейки или диапазоны, для которых требуется вычислить среднее значение. Аргументов может быть не более 255.
Замечания
Аргументы могут быть числами, именами или ссылками на диапазоны или ячейки, содержащие числа.
Учитываются логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов.
Если аргумент является ссылкой на диапазон или ячейку, содержащую текст или логические значения, или ссылкой на пустую ячейку, то такие значения игнорируются; однако ячейки, которые содержат нулевые значения, учитываются.
Аргументы, являющиеся значениями ошибок или текстом, которые не могут быть преобразованы в числа, вызывают ошибки.
Если логические значения и текстовые представления чисел необходимо учитывать в расчетах, используйте функцию СРЗНАЧА.
Если требуется вычислить среднее значение только для тех значений, которые удовлетворяют определенным критериям, используйте функцию СРЗНАЧЕСЛИ или СРЗНАЧЕСЛИМН.
СТАНДОТКЛОН.Г (функция СТАНДОТКЛОН.Г)
Вычисляет стандартное отклонение по генеральной совокупности, заданной аргументами. При этом логические значения и текст игнорируются.
Стандартное отклонение — это мера того, насколько широко разбросаны точки данных относительно их среднего.
Синтаксис
СТАНДОТКЛОН.Г(число1;[число2];…)
Аргументы функции СТАНДОТКЛОН.Г описаны ниже.
Число1 Обязательный. Первый числовой аргумент, соответствующий генеральной совокупности.
Число2... Необязательный. Числовые аргументы 2—254, соответствующие генеральной совокупности. Вместо аргументов, разделенных точкой с запятой, можно использовать массив или ссылку на массив.
Замечания
Функция СТАНДОТКЛОН.Г предполагает, что аргументы — это вся генеральная совокупность. Если данные являются только выборкой из генеральной совокупности, для вычисления стандартного отклонения следует использовать функцию СТАНДОТКЛОН.
Для больших выборок функции СТАНДОТКЛОН.В и СТАНДОТКЛОН.Г возвращают примерно равные значения.
Стандартное отклонение вычисляется с использованием "n" метода.
Аргументы могут быть либо числами, либо содержащими числа именами, массивами или ссылками.
Учитываются логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов.
Если аргумент является массивом или ссылкой, то учитываются только числа. Пустые ячейки, логические значения, текст и значения ошибок в массиве или ссылке игнорируются.
Аргументы, которые представляют собой значения ошибок или текст, не преобразуемый в числа, вызывают ошибку.
Чтобы включить логические значения и текстовые представления чисел в ссылку для вычисления, используйте функцию СТАНДОТКЛОНПА.
Функция СТАНДОТКЛОН.Г вычисляется по следующей формуле:
где x — выборочное среднее СРЗНАЧ(число1,число2,…), а n — размер выборки.
СЧЁТЕСЛИ (функция СЧЁТЕСЛИ)
С помощью статистической функции СЧЁТЕСЛИ можно подсчитать количество ячеек, отвечающих определенному условию (например, число клиентов в списке из определенного города).
Синтаксис
СЧЁТЕСЛИ(диапазон;критерий)
Ниже приведены примеры.
=СЧЁТЕСЛИ(A2:A5;"яблоки")
=СЧЁТЕСЛИ(A2:A5;A4)
| Имя аргумента | Описание |
| диапазон (обязательный) | Группа ячеек, для которых нужно выполнить подсчет. Диапазон может содержать числа, массивы или ссылки на числа. Пустые и текстовые значения игнорируются. Узнайте, как выбирать диапазоны на листе . |
| критерий (обязательный) | Число, выражение, ссылка на ячейку или текстовая строка, которая определяет, какие ячейки нужно подсчитать. Например, критерий может быть выражен как 32, ">32", В4, "яблоки" или "32". В функции СЧЁТЕСЛИ используется только один критерий. Чтобы провести подсчет по нескольким условиям, воспользуйтесь функцией СЧЁТЕСЛИМН. |
Функция СУММЕСЛИ
В этой статье описаны синтаксис формулы и использование функции СУММЕСЛИ в Microsoft Excel.
Описание
Функция СУММЕСЛИ используется, если необходимо просуммировать значения диапазона, соответствующие указанному критерию
Примечание Если необходимо выполнить суммирование ячеек в соответствии с несколькими условиями, используйте функцию СУММЕСЛИМН.
Синтаксис
СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])
Аргументы функции СУММЕСЛИ описаны ниже.
Диапазон . Обязательный аргумент. Диапазон ячеек, оцениваемых на соответствие условиям. Ячейки в каждом диапазоне должны содержать числа, имена, массивы или ссылки на числа. Пустые ячейки и ячейки, содержащие текстовые значения, пропускаются.
Условие . Обязательный аргумент. Условие в форме числа, выражения, ссылки на ячейку, текста или функции, определяющее, какие ячейки необходимо суммировать.
Важно Все текстовые условия и условия с логическими и математическими знаками необходимо заключать в двойные кавычки ("). Если условием является число, использовать кавычки не требуется.
Диапазон_суммирования . Необязательный аргумент. Ячейки, значения из которых суммируются, если они отличаются от ячеек, указанных в качестве диапазона. Если аргумент диапазон_суммирования опущен, Excel суммирует ячейки, указанные в аргументе диапазон (те же ячейки, к которым применяется условие).
В аргументе условие можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует одному любому символу, а звездочка — любой последовательности символов. Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак "тильда" ().
Построение гистограммы:
Вкладка Данные – Анализ данных – Гистограмма.
В результате должен получиться график следующего вида:
