Criando uma simulação de Monte Carlo usando o Excel

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 utiliza 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.

Simulação de Monte Carlo

O método Monte Carlo foi inventado por Nicolas Metropolis em 1947 e procura resolver problemas complexos usando métodos aleatórios e probabilísticos. O termo "Monte Carlo" é originário da área administrativa de Mônaco, popularmente conhecida como um lugar onde as elites europeias jogam. Utilizamos o método de Monte Carlo quando o problema é muito complexo e difícil de resolver por cálculo direto. Um grande número de iterações permite uma simulação da distribuição normal.

O método de simulação de Monte Carlo calcula as probabilidades de integrais e resolve equações diferenciais parciais, introduzindo, assim, uma abordagem estatística do 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 ignorar os fundamentos matemáticos.

Para a simulação de Monte Carlo, isolamos várias variáveis-chave que controlam e descrevem o resultado do experimento, depois atribuímos uma distribuição de probabilidade após a realização de um grande número de amostras aleatórias. Vamos dar um jogo de dados como modelo.

Game of Dice

Aqui está como o jogo de dados rola:

• O jogador joga três dados que têm 6 lados 3 vezes.

• Se o total dos 3 arremessos for 7 ou 11, o jogador vence.

• Se o total dos 3 arremessos for: 3, 4, 5, 16, 17 ou 18, o jogador perde.

• Se o total for outro resultado, o jogador joga novamente e lança os dados novamente.

• Quando o jogador joga os dados novamente, o jogo continua da mesma maneira, exceto que o jogador vence 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, são necessários 5.000 resultados para preparar a simulação de Monte Carlo.

Etapa 1: Eventos de rolagem de dados

Primeiro, desenvolvemos uma série de dados com os resultados de cada um dos 3 dados para 50 jogadas. Para fazer 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 dos 3 rolos.

Etapa 2: Faixa de resultados

Então, precisamos desenvolver uma série de dados para identificar os possíveis resultados para a primeira rodada e as rodadas subsequentes. Há um intervalo de dados de 3 colunas. Na primeira coluna, temos os números de 1 a 18. Esses números representam os possíveis resultados após a rolagem dos dados 3 vezes: o máximo sendo 3 * 6 = 18. Você notará que, para as células 1 e 2, os resultados são N / A, pois é impossível obter 1 ou 2 usando 3 dados. O mínimo é 3.

Na segunda coluna, estão incluídas as possíveis conclusões após a primeira rodada. Conforme declarado na declaração inicial, o jogador ganha (ganha) ou perde (perde) ou repete (rolar novamente), dependendo do resultado (total de 3 jogadas de dados).

Na terceira coluna, são registradas as possíveis conclusões para as rodadas subsequentes. Podemos alcançar esses resultados usando a função "SE". Isso garante que, se o resultado obtido for equivalente ao resultado obtido no primeiro turno, venceremos, caso contrário, seguiremos as regras iniciais da jogada original para determinar se lançamos novamente 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 possíveis resultados da primeira rodada, a conclusão correspondente ao resultado obtido. Por exemplo, ao obter 6, jogamos novamente.

Pode-se obter as conclusões de outros lançamentos de dados, usando uma função "OR" e uma função de índice aninhada em uma função "SE". Essa função informa ao Excel: "Se o resultado anterior for Ganhar ou Perder", pare de rolar os dados porque, uma vez que vencemos ou perdemos, terminamos. Caso contrário, vamos para a coluna das seguintes conclusões possíveis e identificamos a conclusão do resultado.

Etapa 4: Número de Rolamentos de Dados

Agora, determinamos o número de jogadas necessárias antes de perder ou ganhar. Para fazer isso, podemos usar uma função "COUNTIF", que exige que o Excel conte os resultados de "Re-roll" e adicione o número 1 a ele. Acrescenta uma porque temos uma rodada extra e obtemos um resultado final (vitória ou derrota).

Etapa 5: Simulação

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

Em seguida, criaremos uma tabela de análise de sensibilidade usando os dados do recurso ou tabela de dados da tabela (essa sensibilidade será inserida na segunda tabela e na terceira coluna). Nesta análise de sensibilidade, o número de eventos de 1 a 5.000 deve ser inserido na célula A1 do arquivo. De fato, pode-se escolher qualquer célula vazia. A idéia é simplesmente forçar um recálculo a cada vez e, assim, obter novos dados (resultados de novas simulações) sem danificar as fórmulas existentes.

Etapa 6: Probabilidade

Finalmente, podemos calcular as probabilidades de ganhar e perder. Fazemos isso usando a função "COUNTIF". A fórmula conta o número de "vitória" e "perda" e depois divide pelo número total de eventos, 5.000, para obter a respectiva proporção de um e de outro. Finalmente, vemos que a probabilidade de obter um resultado de vitória é de 73, 2% e, portanto, de obter um resultado de perda é de 26, 8%.