Aprenda a criar uma simulação de Monte Carlo usando o Excel

Publicado por Javier Ricardo


Uma simulação de Monte Carlo pode ser desenvolvida usando o Microsoft Excel e um jogo de dados.
A simulação de Monte Carlo é um método numérico matemático que usa sorteios aleatórios para realizar cálculos e problemas complexos. Hoje, é amplamente utilizado e desempenha um papel fundamental em vários campos, como finanças, física, química e economia.


Principais vantagens

  • O método de Monte Carlo busca resolver problemas complexos usando métodos aleatórios e probabilísticos.
  • Uma simulação de Monte Carlo pode ser desenvolvida usando o Microsoft Excel e um jogo de dados.
  • Uma tabela de dados pode ser usada para gerar os resultados – um total de 5.000 resultados são necessários para preparar a simulação de Monte Carlo. 

Simulação de Monte Carlo


O método Monte Carlo foi inventado por John von Neumann e Stanislaw Ulam na década de 1940 e busca resolver problemas complexos usando métodos aleatórios e probabilísticos.
O termo Monte Carlo refere-se à área administrativa de Mônaco popularmente conhecida como um lugar onde as elites europeias jogam.
 


O método de simulação de Monte Carlo calcula as probabilidades para integrais e resolve equações diferenciais parciais, introduzindo assim uma abordagem estatística ao risco em uma decisão probabilística.
Embora existam muitas ferramentas estatísticas avançadas para criar simulações de Monte Carlo, é mais fácil simular a lei normal e a lei uniforme usando o Microsoft Excel e contornar os fundamentos matemáticos.

Quando usar a simulação de Monte Carlo


Usamos o método Monte Carlo quando um problema é muito complexo e difícil de fazer por cálculo direto.
Usar a simulação pode ajudar a fornecer soluções para situações que se revelam incertas. Um grande número de iterações permite uma simulação da distribuição normal. Também pode ser usado para entender como funciona o risco e para compreender a incerteza nos modelos de previsão.


Conforme observado acima, a simulação é frequentemente usada em muitas disciplinas diferentes, incluindo finanças, ciências, engenharia e gerenciamento da cadeia de suprimentos – especialmente nos casos em que há muitas variáveis ​​aleatórias em jogo.
Por exemplo, os analistas podem usar simulações de Monte Carlo para avaliar derivativos, incluindo opções, ou para determinar riscos, incluindo a probabilidade de uma empresa não pagar suas dívidas.

Jogo de dados


Para a simulação de Monte Carlo, isolamos várias variáveis-chave que controlam e descrevem o resultado do experimento e, em seguida, atribuímos uma distribuição de probabilidade depois que um grande número de amostras aleatórias é realizado.
Para demonstrar, vamos usar um jogo de dados como modelo. Veja como funciona o jogo de dados:


• O jogador lança três dados que têm seis lados três vezes.


• Se o total dos três lançamentos for sete ou 11, o jogador ganha.


• Se o total dos três lançamentos for: três, quatro, cinco, 16, 17 ou 18, o jogador perde.


• Se o total for qualquer outro resultado, o jogador joga novamente e joga os dados novamente.


• Quando o jogador volta a lançar os dados, o jogo continua da mesma forma, exceto que o jogador ganha quando o total é igual à soma determinada na primeira rodada.


Também é recomendável usar uma tabela de dados para gerar os resultados.
Além disso, 5.000 resultados são necessários para preparar a simulação de Monte Carlo. 

Para preparar a simulação de Monte Carlo, você precisa de 5.000 resultados.

Etapa 1: Eventos de lançamento de dados


Primeiro, desenvolvemos uma gama de dados com os resultados de cada um dos três dados para 50 lançamentos.
Para isso, propõe-se usar a função “RANDBETWEEN (1,6)”. Assim, cada vez que clicamos em F9, geramos um novo conjunto de resultados de rolagem. A célula “Resultado” é a soma total dos resultados das três jogadas.

Etapa 2: Faixa de resultados


Em seguida, precisamos desenvolver uma série de dados para identificar os resultados possíveis para a primeira rodada e as rodadas subsequentes.
Existe um intervalo de dados de três colunas. Na primeira coluna, temos os números de um a 18. Esses números representam os resultados possíveis após lançar os dados três vezes: O máximo é 3 x 6 = 18. Você notará que para as células um e dois, os resultados são N / A, pois é impossível obter um ou dois usando três dados. O mínimo é três.


Na segunda coluna, são incluídas as conclusões possíveis após a primeira rodada.
Conforme declarado na declaração inicial, ou o jogador ganha (Vitória) ou perde (Perda), ou ele joga novamente (Re-lançamento), dependendo do resultado (o total de três lançamentos de dados).


Na terceira coluna, são registradas as possíveis conclusões das rodadas subsequentes.
Podemos obter esses resultados usando a função “IF”. Isso garante que se o resultado obtido for equivalente ao resultado obtido na primeira rodada, ganhamos, caso contrário, seguimos as regras iniciais do jogo original para determinar se voltamos a lançar os dados.

Etapa 3: Conclusões


Nesta etapa, identificamos o resultado das 50 jogadas de dados.
A primeira conclusão pode ser obtida com uma função de índice. Esta função busca os resultados possíveis do primeiro turno, a conclusão correspondendo ao resultado obtido. Por exemplo, quando tiramos um seis, jogamos novamente.


Pode-se obter os resultados de outros lançamentos de dados, usando uma função “OU” e uma função de índice aninhada em uma função “SE”.
Esta função diz ao Excel: “Se o resultado anterior for Ganhar ou Perder”, pare de lançar os dados porque, uma vez que tenhamos ganho ou perdido, terminamos. Caso contrário, vamos à coluna das possíveis conclusões a seguir e identificamos a conclusão do resultado.

Etapa 4: Número de lançamentos de dados


Agora, determinamos o número de jogadas de dados necessárias antes de perder ou ganhar.
Para fazer isso, podemos usar a função “CONTAR.SE”, que requer que o Excel conte os resultados de “Rolar novamente” e adicione o número um a ele. Acrescenta um porque temos uma rodada extra e obtemos um resultado final (ganhar ou perder).

Etapa 5: Simulação


Desenvolvemos uma gama para rastrear os resultados de diferentes simulações.
Para fazer isso, vamos criar três colunas. Na primeira coluna, um dos números incluídos é 5.000. Na segunda coluna, procuraremos o resultado após 50 lançamentos de dados. Na terceira coluna, o título da coluna, procuraremos o número de lançamentos de dados antes de obter o status final (ganhar ou perder).


Em seguida, criaremos uma tabela de análise de sensibilidade usando os dados de características ou tabela de dados da tabela (esta sensibilidade será inserida na segunda tabela e na terceira colunas).
Nessa análise de sensibilidade, o número de eventos de um a 5.000 deve ser inserido na célula A1 do arquivo. Na verdade, pode-se escolher qualquer célula vazia. A ideia é simplesmente forçar um recálculo a cada vez e assim obter novos lançamentos de dados (resultados de novas simulações) sem danificar as fórmulas no lugar.

Etapa 6: Probabilidade


Podemos finalmente calcular as probabilidades de ganhar e perder.
Fazemos isso usando a função “CONT.SE”. A fórmula conta o número de “ganhos” e “perdas” e divide-se pelo número total de eventos, 5.000, para obter a respectiva proporção de um e do outro. Finalmente vemos que a probabilidade de obter um resultado Vencer é de 73,2% e, portanto, obter um resultado Perder é 26,8%.