o Que é a Simulação de Monte Carlo?A simulação de Monte Carlo é um processo de usar curvas de probabilidade para determinar a probabilidade de um resultado. Você pode coçar a cabeça aqui e dizer… “Hey Rick, uma curva de distribuição tem uma matriz de valores. Então, como exatamente eu determino a probabilidade de um resultado?”E melhor ainda, como faço isso no Microsoft Excel sem nenhum suplemento especial que você nunca perguntaria.Isso é feito executando a simulação milhares de vezes e analisando a distribuição da saída. Isso é particularmente importante quando você está analisando a saída de várias curvas de distribuição que se alimentam umas das outras.Exemplo:

• # de Unidades Vendidas pode ter uma curva de distribuição
• multiplicado pelo preço de Mercado, o que pode ter outra curva de distribuição
• menos salários variáveis que têm outra curva
• etc., etc.

uma vez que todas essas distribuições estão misturadas, a saída pode ser bastante complexa. A execução de milhares de iterações (ou simulações) dessas curvas pode fornecer alguns insights. Isso é particularmente útil na análise de risco potencial para uma decisão.Ao descrever a simulação de Monte Carlo, muitas vezes me refiro aos jogos de guerra do filme de 1980, onde um jovem Mathew Broderick (Antes de Ferris Bueller) é um hacker que usa seu modem dial-up para invadir os computadores do Pentágono e iniciar a 3ª Guerra Mundial. Meio. Ele então fez com que os computadores do Pentágono fizessem muitas simulações dos jogos Tic Tac Toe para ensinar ao computador que ninguém vai fazer uma guerra nuclear – e salvar o mundo no processo.Obrigado Ferris. És um herói.Aqui está um vislumbre do filme para mostrar-lhe grande tempo Monte Carlo em ação. Estou assumindo que você vai ignorar a política, o homem estranho abraçando e, claro, Dabney Coleman.

curvas de distribuição

existem várias curvas de distribuição que você pode usar para configurar sua simulação de Monte Carlo. E essas curvas podem ser trocadas com base na variável. A Microsoft não tem uma fórmula chamada “simulação do Monte Carlo” na barra de menus 🙂

distribuição uniforme

em uma distribuição uniforme, há probabilidade igual entre o mínimo e o máximo. Uma distribuição uniforme parece um retângulo.

distribuição normal (Gaussiana)

esta é também a sua curva em forma de sino padrão. Esta fórmula de simulação de Monte Carlo é caracterizada por ser distribuída uniformemente em cada lado (mediana e média é a mesma – e sem distorção). As caudas da curva vão para o infinito. Portanto, esta pode não ser a curva ideal para os preços das casas, onde algumas casas de topo aumentam a média (média) bem acima da mediana, ou nos casos em que há um mínimo ou máximo rígido. Um exemplo disso pode ser o salário mínimo em sua localidade. Observe que o nome da função varia dependendo da sua versão.

distribuição Lognormal

uma distribuição onde o logaritmo é normalmente distribuído com a média e o desvio padrão. Portanto, a configuração é semelhante à distribuição normal, mas observe que as variáveis mean e standard_dev devem representar o logaritmo.

distribuição de Poisson

esta é provavelmente a distribuição mais subutilizada. Por padrão, muitas pessoas usam uma curva de distribuição normal quando Poisson é mais adequado para seus modelos. Poisson é melhor descrito quando há uma grande distribuição perto do início que rapidamente se dissipa para uma cauda longa de um lado. Um exemplo disso seria um call center, onde nenhuma chamada é atendida antes do segundo ZERO. Seguido pela maioria das chamadas atendidas nos 2 primeiros intervalos (digamos, 30 e 60 segundos) com uma rápida queda no volume e uma cauda longa, com muito poucas chamadas atendidas em 20 minutos (alegadamente).

o objetivo aqui não é mostrar todas as distribuições possíveis no Excel, pois isso está fora do escopo deste artigo. Em vez disso, para garantir que você saiba que existem muitas opções disponíveis para sua simulação de Monte Carlo. Não caia na armadilha de assumir que uma curva de distribuição normal é o ajuste certo para toda a sua modelagem de dados. Para encontrar mais curvas, para ir as funções estatísticas dentro de sua pasta de trabalho do Excel e investigar. Se você tiver dúvidas, coloque-as na seção de comentários abaixo.

construindo o modelo

para esta configuração, assumiremos uma distribuição normal e 1.000 iterações.

variáveis de entrada

a configuração assume uma distribuição normal. Uma distribuição normal requer três variáveis: probabilidade, média e desvio padrão. Abordaremos a média e o desvio padrão em nosso primeiro passo. Eu assumo um problema de previsão financeira que consiste em receitas, despesas variáveis e fixas. Onde a receita menos despesas variáveis menos despesas fixas é igual ao lucro. As despesas fixas são custo afundado na planta e no equipamento, assim que nenhuma curva da distribuição é assumida. As curvas de distribuição são assumidas para receita e despesas variáveis.

primeira simulação

o exemplo abaixo indica as configurações de receita. A fórmula pode ser copiada e colada na célula D6 para despesas variáveis. Para receitas e despesas que você a norma de função.INV() onde os parâmetros são:

• probabilidade = a função RAND () para obter um número aleatório com base nos outros critérios dentro da distribuição.
• média = a média utilizada no Passo 1. Para a receita é C3.
• desvio padrão = o desvio padrão usado na Etapa 1. Para receita é C4

como RAND () é usado como probabilidade, uma probabilidade aleatória é gerada na atualização. Vamos usar isso a nosso favor na próxima etapa.

1.000 simulações

existem várias maneiras de fazer 1.000 ou mais variações. A opção mais simples é pegar a fórmula do Passo #2 e torná-la absoluta. Em seguida, copie e cole 1.000 vezes. Isso é simples, mas não muito chique. E se Ferris Bueller puder salvar o mundo mostrando um novo jogo Tic Tac Toe para um computador, então podemos apimentar essa análise também. Vamos nos aventurar no mundo das mesas.

• primeiro, queremos criar um esboço para uma tabela. Fazemos isso listando os números de 1 a 1.000 em linhas. Na imagem de exemplo abaixo, a lista de números começa em B12.
• na próxima coluna, na célula C12, referiremos a primeira iteração.

• em seguida, destaque a área onde queremos abrigar as 1.000 iterações
• selecione Dados > tabelas de dados
• para célula de entrada de coluna: selecione uma célula em branco. No arquivo de download, célula D11 é selecionado
• Selecione OK

• uma Vez OK é selecionado a partir do passo anterior, uma tabela é inserida que autopopulates 1.000 simulações

Estatísticas de Resumo

uma Vez que as simulações são executadas, é hora de reunir as estatísticas de resumo. Isso pode ser feito de várias maneiras. Neste exemplo, usei a função COUNTIF () para determinar a porcentagem de simulações que não são lucrativas e a probabilidade de um lucro maior que US $1 milhão. Como esperado, a probabilidade de mais de US $1 milhão gira em torno de 50%. Isso ocorre porque usamos curvas de distribuição normais que são distribuídas uniformemente em torno da média, que era de US $1 milhão. a probabilidade de perder dinheiro é de 4,8%. Isso foi coletado usando a função COUNTIF () para contar as simulações que eram menores que zero e dividindo pelo total de 1.000 iterações.

obtenha o Download

no vídeo acima, Oz pergunta sobre os vários usos para simulação de Monte Carlo. Para que o usaste? Existem exemplos específicos que você pode compartilhar com o grupo? Em caso afirmativo, deixe uma nota abaixo na seção de comentários. Além disso, sinta-se à vontade para se inscrever em nosso boletim informativo, para que você possa se manter atualizado como novo Excel.TV shows são anunciados. Deixe-me uma mensagem abaixo para ficar em contato.