Início Gestão na Prática Simulação de Monte Carlo
Como fazer a simulação de Monte Carlo?
Simulação de Monte Carlo em uma planilha de Estudo de Viabilidade Econômica
Simulação de Monte Carlo no Excel
Uma ferramenta para você colocar o método em prática agora mesmo
A simulação de Monte Carlo ou Método de Monte Carlo (MMC) é uma metodologia estatística que se baseia em uma grande quantidade de amostragens aleatórias para se chegar em resultados próximos de resultados reais. Traduzindo para o bom português, ele permite que você faça testes com variáveis em um número suficientemente grande de vezes para ter, com mais precisão, a chance de algum resultado acontecer.
Levando o método de Monte Carlo para casos reais, é possível aplicar a simulação em:
Gestão: estudo de viabilidade econômica, análise de riscos, projeções, etc
Finanças: análise de ações, opções futuras, séries macroeconômicas, etc
Outras áreas: computação gráfica, análises variadas, geologia, etc
O nome Monte Carlo surgiu pelo alusão de uma de suas principais características, a aleatoriedade com o que ocorre em cassinos (como o de Monte Carlo) cotidianamente em jogos como a roleta.
Passo 1 – Modelar o problema
Passo 2 – Gerar valores aleatórios para as incertezas do problema
Passo 3 – Substituir as incertezas por valores para calcular o resultado
Passo 4 – Obter uma estimativa para a solução do problema
Por ser um método muito matemático e que demanda de softwares específicos para a grande quantidade de simulações, acredito que seja possível fazer simplificações no método para obter resultados práticos e sem ter um trabalho muito grande.
Monte Carlo em uma Planilha de Estudo de Viabilidade Econômica
Veja um exemplo de aplicação de uma variação da simulação de Monte Carlo na planilha de Estudo de Viabilidade Econômica da LUZ. Nela você pode trabalhar fazendo projeções futuras de receitas, despesas e investimentos, conforme mostrado abaixo:
Depois de preencher todos os dados de previsões sobre o seu negócio, você consegue visualizar o seu cenário projetado, que seria uma possibilidade de resultado real (que pode acontecer de fato no futuro). Veja:
Acontece, que como sabemos, essa é uma projeção e, como o próprio nome já diz, pode não se concretizar. É um caso claro de incerteza e de que por mais que você se esforce e estude o mercado, pode se confundir.
Nesse momento, adotamos um teste de cenários otimistas e pessimistas para trabalhar possibilidades diferentes de resultado final. Aqui entra a aplicação simplificada da simulação de Monte Carlo que eu estava falando. Abaixo eu mostro o mesmo cenário projetado, só que agora com uma variação positiva percentual que você pode modificar:
Veja que ao fazer um teste mudando o campo "Previsão de receita maior em" de 10% para 20%, os resultados de receita bruta da planilha são modificados também:
Essa é uma maneira de praticar um número grande de amostragens e ver todas as possibilidades possíveis para o seu negócio. Observe que fazendo mais modificações (dessa vez eu modifiquei os 3 itens), os resultados vão mudando e o seu lucro livre também.
No final das contas, essa é uma maneira simplificada de aplicação da simulação de monte carlo em uma planilha de estudo de viabilidade econômica em Excel. Se você gostou clique na imagem abaixo e conheça uma versão demonstrativa da planilha:
Apesar do exemplo acima ter sido feito em uma planilha pronta de Excel, ele é uma simplificação da simulação de Monte Carlo. Agora farei uma outra simplificação (com poucos experimentos), mas que pode te dar a noção completa do fator aleatoriedade que o MMC possui.
Vamos fazer o uso do Método de Monte Carlo para calcular a probabilidade de termos prejuízo em uma análise financeira de uma empresa que vende computadores. As principais variáveis que vamos utilizar são:
Essas 2 variáveis podem oscilar ao longo do tempo, nesse caso é preciso estabelecer um valor mínimo e um máximo para cada um deles
Custo dos fornecedores
Esse item vai variar de acordo com o fornecedor utilizado. No nosso caso criei uma tabela com uma distribuição percentual de compra dividido em 4 fornecedores
Deve seguir um padrão histórico. No nosso caso vamos levar em consideração uma demanda de 15.000 por mês, podendo variar (desvio padrão) em até 3.000 para cima ou para baixo.
Esse é um item constante, por isso terá sempre o mesmo valor. Essa é uma simplificação levando em conta que a empresa não terá mudanças estruturais grandes.
Abaixo mostro os valores que utilizei diretamente na planilha:
A partir da definição das variáveis, podemos começar a desenvolver a nossa tabela de experimentos. Como falei no começo desse artigo, a simulação de Monte Carlo envolve a realização de milhares de testes. No nosso caso, farei apenas 10, que estatisticamente é pouco, mas que é suficiente para você entender o uso do MMC. Essa será a nossa tabela:
Agora vamos ver como fazer cada uma das funções e fórmulas para chegar no nosso resultado desejado, que é descobrir a probabilidade desse negócio ter prejuízo. Para isso, temos que calcular a probabilidade de forma aleatória para cada experimento. Dessa forma:
Como falei, o preço de venda varia entre o menor preço possível (R$100) e o maior preço possível (R$200). Para chegar nesse cálculo é necessário somar o menor preço com a diferença do maior com o menor e multiplicar isso por um fator aleatório. Dessa forma:
=$C$3+($D$3-$C$3)*ALEATÓRIO()
Essa variável vai ter a mesma lógica aplicada acima, veja:
=$C$6+($D$6-$C$6)*ALEATÓRIO()
Observe que os resultados sempre vão variar entre R$40 e R$60 para esse caso e toda vez que eu colocar uma nova imagem eles vão ser diferentes. Isso ocorre pela aleatoriedade gerada na planilha.
3. Gasto com Fornecedores
Dessa vez a lógica é um pouco diferente. Como temos uma série de fornecedores (que também incide como um custo direto) temos que calcular a probabilidade de comprarmos de cada um deles. Para isso utilizamos uma tabela com o limite inferior e superior de probabilidade e aplicamos um PROCV para saber os valores dessa maneira:
=PROCV(ALEATÓRIO();$C$9:$E$13;3;VERDADEIRO)
Para o cálculo da demanda temos que utilizar a função inversa da Normal, que pede a probabilidade (estamos utilizando a função ALEATÓRIO para gerar valores aleatórios entre 0 e 1 – 0% e 100%), a demanda média e desvio padrão que já tínhamos:
=INV.NORM(ALEATÓRIO();$C$4;$C$5)
Esse é um dos poucos valores constantes, então nesse caso não aplicaremos nenhuma aleatoriedade e deixaremos ele sempre igual a R$500.000.
6. Lucro ou Prejuízo
Esse é um cálculo bem simples de lucro ou prejuízo. Basta descobrir a sua margem de contribuição (MC), multiplicar pela demanda e subtrair do custo fixo. Como sabemos, MC é o seu preço de venda subtraído dos custos diretos e com fornecedores. A função ficará assim:
=(H4-I4-J4)*K4-L4
7. Resultado dos Experimentos
Por fim, você pode verificar se com os valores aleatórios obtidos vai ter lucro ou prejuízo para cada um dos 10 experimentos.
Toda vez que realizamos uma ação no Excel ele recalcula o efeito de aleatoriedade modificando o nosso resultado:
Veja que temos resultados positivos (lucro) e negativos (prejuízo). Isso quer dizer que, dependendo da previsão que fizermos, esse negócio pode ser um investimento ruim. Agora chega o momento que precisamos identificar o quão ruim ou bom esse investimento pode ser com mais 3 indicadores:
Você consegue chegar no lucro médio utilizando a função MÉDIA do intervalo de lucro ou prejuízo para os 10 experimentos:
=MÉDIA(M4:M13)
O desvio padrão é calculado da mesma forma
=DESVPAD(M4:M13)
Sempre que o desvio padrão for maior, igual ou um valor próximo do lucro médio isso vai significar que você tem um risco maior de ter prejuízo. De toda forma, o indicador mais importante que você tem que olhar é a:
Probabilidade de ter Lucro Menor que Zero
Na nossa planilha o resultado ficou assim. Ou seja, para esse teste específico com 10 experimentos, temos uma probabilidade de 21% de ter prejuízo.
Esse é o indicador que vai te dar o grau de risco de entrar de cabeça nesse negócio ou não. Obviamente que por ter adotado apenas 10 experimentos, as variações da probabilidade acaba sendo muito grande de um teste aleatório para outro e esse não é o ideal.
Para você ter uma ideia, fazendo uma série de testes, tive esse indicador variando de 12% a 32% de probabilidade de lucro menor que zero. Agora, ao expandir a minha amostra para 1000 experimentos, a variação passou a ficar entre 21% e 23%, ou seja, um valor mais assertivo e próximo da realidade.
Que tal usar o Método de Monte Carlo agora?
Viu como o resultado pode ser bacana e te ajudar a tomar decisões de negócios? Se quiser aproveitar a nossa planilha de estudo de viabilidade econômica que possui o MMC simplificado clique na imagem abaixo:
Artigo anteriorComo fazer Projeção Financeira com Fluxo de Caixa
Próximo artigoComo Fazer uma Lista / Menu Dropdown no Excel
Renato Bastos 20/05/2020 at 11:43
Para o cálculo da probabilidade do lucro ser menor que zero basta usar a função: DIST.NORMAL
charles vieira pereira 14/05/2020 at 08:08
Também gostaria de saber como calculo essa probabilidade de ter lucro menor que zero.
Anônimo 13/11/2019 at 12:56
Rafael Ávila 19/09/2019 at 11:10
Anônimo 19/09/2019 at 08:44
Minha simulação é para outra área, mas ajudou muito!
Rafael Ávila 16/02/2018 at 22:46
Oi Eduardo, nunca fiz não, mas se você me passar um briefing do que precisa ser feito, podemos analisar a possibilidade de desenvolver
Eduardo Toledo 10/02/2018 at 11:35
Você já montou alguma planilha para aplicação em ALM, casamento de ativos financeiros (portfólio de investimento) comparado com o passivo atuarial (série de pagamentos previdenciários) para massa de beneficiários?
Se sim, como posso adquiri-la e qual o seu preço?
Você pode me responder pelo email cadastrado abaixo.
Rafael 02/03/2017 at 09:43
Oi Igor, se eu não me engano você precisa fazer o cálculo percentual de quantas vezes o lucro aparece abaixo de zero nos cálculos. Dessa forma, quanto mais testes fizer, mais real a probabilidade será.