Моделирование методом Монте-Карло в Crystal Ball для Excel

Рубрика: 7. Полезняшки Excel

Ранее я рассмотрел пример использования метода Монте-Карло для расчета риска с применением стандартных средств Excel. К сожалению, Excel не очень подходит для решения такого рода задач, так как является детерминистской программой. В каждой отдельной ячейке может располагаться лишь конкретное значение, и для моделирования методом Монте-Карло требуется создать множество строк (сценариев), используя генератор случайных чисел (например, функцию СЛЧИС).

Скачать заметку в формате Word, примеры в формате Excel

Существует немало программ для моделирования методом Монте-Карло. С их обзором можно ознакомиться, например, в книге Дугласа Хаббарда Как измерить всё, что угодно. Оценка стоимости нематериального в бизнесе:

Инструмент Кем разработан Описание
@Risk Palisade Corporation, Итака, штат Нью-Йорк Достаточно совершенный инструмент для работы на основе Excel; описывает большое число распределений; широкая база пользователей, предоставляется техническая поддержка
AIE Hubbard Decision Research, Глен-Эллин, штат Иллинойс Набор макросов на основе Excel; также позволяет рассчитывать стоимость информации и оптимальный портфель; подчеркивает приоритетность методологии над инструментарием; предоставляются консалтинговые услуги по практическим вопросам внедрения
Crystal Ball Decisioneering, Inc, Денвер, штат Колорадо Еще один инструмент на базе Excel. Продукт, успешно конкурирующий с @Risk. Много пользователей, предоставляется техническая поддержка
Risk Solver Engine Frontline Systems, Инклин-Вилладж, штат Невада Уникальная платформа разработки на базе Excel, позволяющая выполнять моделирование методом Монте-Карло с беспрецедентной скоростью. Поддерживает форматы SIP и SLURPs, необходимые для управления вероятностями
SAS SAS Corporation, Роли, штат Северная Каролина Пакет программ высшей степени сложности, используемый многими профессиональными статистиками и далеко выходящий за рамки метода Монте-Карло
SPSS SPSS Inc., Чикаго, штат Иллинойс Также выходит за пределы метода Монте-Карло; весьма популярен среди ученых
XLSim Профессор Стэнфордского университета Сэм Сэвидж, AnalyCorp Недорогой пакет программ, предназначенный для легкого изучения, удобен в применении. Сэвидж проводит в организациях семинары по методу Монте-Карло

Книга написана американским автором и вышла в США в 2007 г. Программа Crystal Ball, упомянутая в таблице сейчас принадлежит уже Oracle. Демо-версия программы доступна для скачивания с сайта компании. Описание базовых функциональных возможностей Crystal Ball я нашел на сайте Финансовое моделирование, бюджетирование, планирование.

Скачайте и установите Crystal Ball на ПК. Прежде чем запустить программу закройте все окна Excel. Запустите Crystal Ball. Сначала откроется Excel, а затем в нем появится закладка Crystal Ball (рис. 1).

Рис. 1. Запуск Crystal Ball сначала открывает Excel, а затем появляется закладка Crystal Ball

Воспользуемся примером Хаббарда, рассмотренным ранее, и на его основе изучим основы работы в программе Crystal Ball.

Предположим, что вы хотите арендовать новый станок. Стоимость годовой аренды станка 400 000 дол., и договор нужно подписать на несколько лет. Поэтому, даже не достигнув точки безубыточности, вы всё равно не сможете сразу вернуть станок. Вы собираетесь подписать договор, думая, что современное оборудование позволит сэкономить на трудозатратах и стоимости сырья и материалов, а также считаете, что материально-техническое обслуживание нового станка обойдется дешевле.

Ваши калиброванные специалисты [1] по оценке дали следующие интервалы значений ожидаемой экономии и годового объема производства (в таблице приведены 90%-ные доверительные интервалы):

экономия на материально-техническом обслуживании от 10 до 20 дол. на единицу продукции
экономия на трудозатратах от «–2» до 8 дол. на единицу продукции
экономия на сырье и материалах от 3 до 9 дол. на единицу продукции
объем производства от 15 000 до 35 000 единиц продукции в год
годовая экономия (MS + LS + RMS) х PL

Шаг. 1. Формирование модели. Разместим исходные данные на листе Excel. Они будут включать названия параметров и их средние значения, а также формулу для расчета годовой экономии (рис. 2)

Рис. 2. Исходные данные

Таким образом, суть нашей модели – расчет годовой экономии от использования нового станка. Годовая экономия (зависимая переменная) есть функция трех видов экономии и объема производства (итого, четырех влияющих переменных).

Шаг. 2. Задание параметров распределения влияющих переменных. Встаньте в ячейку В2 и на вкладке Crystal Ball щелкните Define Assumption. В открывшемся окне выберите Normal и нажмите Ok

Рис. 3. Выбор нормального распределения для первого параметра «Экономия на материально-техническом обслуживании»

Задайте среднее значение – Mean и стандартное отклонение – Std. Dev. (рис. 4). Поскольку исходные данные сформулированы в терминах 90%-ного доверительного интервала (CI), формулы для расчета следующие:

Среднее (Mean) = (Верхняя граница 90%-ного CI + Нижняя граница 90%-ного СI)/2;

Стандартное отклонение (Std. Dev.) = (Верхняя граница 90%-ного CI – Нижняя граница 90%-ного СI)/3,29

а наша таблица, приспособленная для работы в Crystal Ball примет вид:

Параметр Границы 90%-ного доверительного интервала Среднее Стандартное отклонение
экономия на материально-техническом обслуживании от 10 до 20 дол. на единицу продукции 15 3,04
экономия на трудозатратах от «–2» до 8 дол. на единицу продукции 3 3,04
экономия на сырье и материалах от 3 до 9 дол. на единицу продукции 6 1,82
объем производства от 15 000 до 35 000 единиц продукции в год 25 000 6 079
годовая экономия (MS + LS + RMS) х PL

Рис. 4. Выбор параметров нормального распределения

Последовательно вставая курсором в ячейки В3:В5 выберите вид и параметры распределения для всех четырех влияющих переменных. После задания параметров ячейки окрашиваются в зеленый цвет.

Шаг 3. Выбор зависимой переменной. Встаньте в ячейку В6, содержащую формулу расчета годовой экономии, и щелкните Define Forecast. В открывшемся окне в поле «Units» укажите ссылку на ячейку (рис. 5).

Рис. 5. Выбор зависимой переменной

Шаг. 4. Выбор условий моделирования. Этот шаг не является обязательным, так как система предложит параметры моделирования по умолчанию. Учитывая, что наша модель довольно простая, можно увеличить число итераций (по умолчанию оно равно 1000). Щелкните Run Preferences, и выберите 10 000 (рис. 6). Чем больше итераций, тем надежней результаты моделирования!

Рис. 6. Выбор числа итераций

Шаг. 5. Запуск моделирования. Щелкните Start, и наслаждайте результатом вашего первого моделирования в Crystal Ball 🙂 После 10 000 итераций программа выведет результаты в графическом виде (рис. 7).

Рис. 7. Результаты моделирования – распределение годовой экономии

В будущем вы всегда можете увидеть результаты моделирования, если щелкните View Charts  (рис. 8)

Рис. 8. Вывод диаграммы с результатами моделирования на экран монитора

Вы также можете создать отчет о моделировании (в отдельном файле Excel), если щелкните на Create Report (рис. 9).

Рис. 9. Фрагмент отчета.

Обратите внимание на величину стандартного отклонения прогнозного значения «Годовая экономия». Вспомним, что среднее значение и стандартное отклонение однозначно задают верхнюю и нижнюю границы 90%-ного доверительного интервала, и вычислим эти границы:

Нижняя граница = среднее – стандартное отклонение * 3,29 / 2 = 600 127 – 189 495 * 3,29 /2 = 288 408

Верхняя граница = среднее + стандартное отклонение * 3,29 / 2 = 600 127 + 189 495 * 3,29 /2 = 911 846

Видно, что не весь 90%-ный доверительный интервал «Годовой экономии» превышает точку безубыточности – 400 000 долл. То есть, существует вероятность того, что точка безубыточности достигнута не будет…

Заметим, что моделирование в Crystal Ball дало те же результаты, что и моделирование в Excel с помощью функции СЛЧИС (рис. 10).

Рис. 10. Результаты моделирования в Excel с помощью функции СЛЧИС


[1] См. главу 5 упоминавшейся книги Дугласа Хаббарда Как измерить всё, что угодно. Оценка стоимости нематериального в бизнесе

Комментарии: 14 комментариев

Сергей Викторович, вечер добрый!
Уточните, пожалуйста, как вы рассчитали стандартное отклонение (откуда взялся знаменатель 3,29)?

Иван, подробнее о 3,29 почитайте в заметке Использование метода Монте-Карло для расчета риска. Там есть поясняющий рисунок. Обратите также внимание на комментарий к заметке, в котором происхождение коэффициента 3,29 объясняется еще раз.

а возможно ли вашу программу использовать для расчета полей нейтронов в однородной среде?
поскольку существующие на сегодняшний день спец. программы такие как MCNP невозможно найти в свободном доступе.

А возможно ли использовать Crystal Ball для результативного фактора, который факически не связан с выделенными параметрами?

Вероника, если я Вас правильно понял, у Вас есть результирующий фактор, для которого Вы не можете записать формулу зависимости от выделенных параметров? Раз так, то и моделировать нечего… Суть моделирование — это оценка того, как будет вести себя результирующий фактор при изменении выделенных параметров. Попробуйте начать с модели: что от чего зависит? Например, мы придаем выделенным параметрам какие-то функции распределения. Фактически Вы должны поступить так с результирующим фактором. Раз он ни от чего не зависит, сделайте допущение, какой функции распределения он подчиняется.
Если я Вас неверно понял, пришлите в личку пример с подробным описанием проблемы.

Благодарю за ответ! да, именно так, есть функции распределения зависимых переменных, но точной формулы зависимости «как в примере с экономией» я записать не могу.

Здравствуйте!
Меня интересует, а если у нас есть формула с точными значениями, как найти(рассчитать) пределы min и max Пример:
x*y*z*n=m(но суть не формуле, а в определение значений +-от исходных)
есть какие-то формулы, либо это определяется, в зависимости от параметра?
Заранее спасибо

Роман, на мой взгляд, это типичная задача для моделирования методом Монте-Карло. Задайте для x, y, z, n средние значения и функцию распределения. «Кидайте кость» и подсчитывайте в каждом эксперименте m. Получите распределение для m. Как вариант, для исходных переменных можно задать минимум и максимум и сказать, что распределение равномерное.

Отличная статья. Толково и понятно. Хочу показать разрабатываемый нами инструмент (надеемся, что в будущем аналог представленных в первой таблице программ). У нас на сайте http://www.eds-plus.ru/eva.html представлена бесплатная демо-версия такой надстройки для Excel. В ней реализованы:
— Анализ чувствительности;
— Сценарный подход (c вычислением VaR — value at risk);
— Метод Монте-Карло;
— Подбор распределения.

Буду благодарен за любые комментарии по данному проекту на почту info@eds-plus.ru

А как определить значения Р10, Р50 и Р90?

Добрый день, Сергей Викторович! Как всегда огромное спасибо за статью, познакомился с данный надстройкой.
у меня небольшое замечание (не существенно, но если не трудно исправить, то будет отлично)) В формуле вычисления границ из полученных данных, у Вас расчёты выполнены по 189495, а формуле 199495

Роман, спасибо, поправил.

И ещё вопрос, у меня есть keygen к данной программе, который активирует даже последнюю версию crystal ball. Можно ли в комментариях выложить ссылку на данный файл, может кому-то понадобиться?

Пожалуй, нет…


Прокомментировать