Създаване на симулация на Монте Карло с помощта на Excel

Симулация в Монте Карло може да бъде разработена с помощта на Microsoft Excel и игра на зарове. Симулацията в Монте Карло е математически числен метод, който използва произволни чертежи за извършване на изчисления и сложни задачи. Днес той се използва широко и играе ключова роля в различни области като финанси, физика, химия и икономика.

Симулация в Монте Карло

Методът Монте Карло е изобретен от Николя Метрополис през 1947 г. и се стреми да реши сложни проблеми, използвайки случайни и вероятностни методи. Терминът "Монте Карло" произхожда от административната зона на Монако, известна като място, където европейските елити играят. Използваме метода на Монте Карло, когато проблемът е твърде сложен и труден за директно изчисление. Голям брой повторения позволява симулация на нормалното разпределение.

Методът за симулация на Монте Карло изчислява вероятностите за интеграли и решава частични диференциални уравнения, като по този начин въвежда статистически подход за риск при вероятностно решение. Въпреки че съществуват много съвременни статистически инструменти за създаване на симулации в Монте Карло, по-лесно е да се симулира нормалния закон и единния закон с помощта на Microsoft Excel и да се заобиколи математическите основи.

За симулацията в Монте Карло изолираме няколко ключови променливи, които контролират и описват резултата от експеримента, след което присвояваме вероятностно разпределение след извършване на голям брой случайни проби. Нека вземем игра на зарове като модел.

Игра на зарове

Ето как се търкаля играта с зарчета:

• Играчът хвърля три зарчета, които имат 6 страни 3 пъти.

• Ако общо 3-те хвърляния са 7 или 11, играчът печели.

• Ако общо 3-те хвърляния са: 3, 4, 5, 16, 17 или 18, играчът губи.

• Ако общият резултат е друг резултат, играчът играе отново и преобръща заровете.

• Когато играчът хвърли заровете отново, играта продължава по същия начин, с изключение на това, че играчът печели, когато общият размер е равен на сумата, определена в първия рунд.

Също така се препоръчва да използвате таблица с данни за генериране на резултатите. Освен това са необходими 5000 резултати, за да се подготви симулацията в Монте Карло.

Стъпка 1: Събития за подвижни зара

Първо, ние разработваме набор от данни с резултатите от всяка от 3-те зарчета за 50 рула. За целта се предлага да се използва функцията "RANDBETWEEN (1, 6)". По този начин, всеки път, когато кликнете върху F9, генерираме нов набор от резултати от ролки. Клетката „Резултат“ е сумата от резултатите от трите ролки.

Стъпка 2: Обхват на резултатите

След това трябва да разработим набор от данни, за да идентифицираме възможните резултати за първия и следващите кръгове. Има 3-колонен диапазон от данни. В първата колона имаме числата от 1 до 18. Тези цифри представляват възможните резултати след завъртането на заровете 3 пъти: максималният е 3 * 6 = 18. Ще отбележите, че за клетки 1 и 2, откритията са N / A, тъй като е невъзможно да се получи 1 или 2 с помощта на 3 зарове. Минимумът е 3.

Във втората колона са включени възможните заключения след първия кръг. Както е посочено в първоначалното изявление, или играчът печели (Win), или губи (Lose), или те преиграват (Re-roll), в зависимост от резултата (общо 3 ролки на зарове).

В третата колона се регистрират възможните заключения за следващите кръгове. Ние можем да постигнем тези резултати с помощта на функцията "IF". Това гарантира, че ако полученият резултат е еквивалентен на резултата, получен в първия рунд, ние печелим, в противен случай следваме първоначалните правила на оригиналната игра, за да определим дали ще преобърнем заровете.

Стъпка 3: Заключения

В тази стъпка ние идентифицираме резултата от 50-те ролки с зарове. Първият извод може да се получи с функция на индекс. Тази функция търси възможните резултати от първия кръг, като заключението съответства на получения резултат. Например при получаване на 6 играем отново.

Човек може да получи констатациите на други ролки с зарове, използвайки функция "ИЛИ" и индексна функция, вложен във функция "ИФ". Тази функция казва на Excel: „Ако предишният резултат е Win or Lose“, спрете да търкаляте заровете, защото след като сме спечелили или загубили, сме готови. В противен случай отиваме в колоната на следните възможни заключения и идентифицираме заключението на резултата.

Стъпка 4: Брой ролки с зарове

Сега ние определяме броя на ролките с зарове, необходими преди загуба или победа. За целта можем да използваме функция „COUNTIF“, която изисква Excel да преброява резултатите от „Re-roll“ и да добави към него числото 1. Той добавя един, защото имаме един допълнителен кръг и получаваме краен резултат (победа или загуба).

Стъпка 5: Симулация

Ние разработваме диапазон за проследяване на резултатите от различни симулации. За целта ще създадем три колони. В първата колона една от включените цифри е 5000. Във втората колона ще търсим резултата след 50 ролки с зарове. В третата колона, заглавието на колоната, ще потърсим броя на ролките с зарове, преди да получим крайния статус (спечелете или загубите).

След това ще създадем таблица за анализ на чувствителността, като използваме характеристиките или таблицата с данни на таблицата (тази чувствителност ще бъде вмъкната във втората таблица и третата колона). При този анализ на чувствителността, броят на събитията от 1 - 5 000 трябва да се вмъкне в клетка А1 на файла. Всъщност човек може да избере всяка празна клетка. Идеята е просто да се налага преизчисляване всеки път и по този начин да получите нови ролки с зарчета (резултати от нови симулации), без да повредите формулите на място.

Стъпка 6: Вероятност

Най-накрая можем да изчислим вероятностите за победа и загуба. Правим това с помощта на функцията "COUNTIF". Формулата отчита броя на "спечелете" и "загубете", след което се разделя на общия брой събития, 5000, за да се получи съответното съотношение на едното и другото. Най-накрая виждаме, че вероятността да постигнем резултат от печалбата е 73, 2% и резултатът от загуба е 26, 8%.