Simulação de Monte Carlo no Excel: Um guia completo

Os métodos de Monte Carlo, cujo nome vem do Cassino de Monte Carlo em Mônaco, são amplamente usados em áreas como finanças, engenharia, cadeia de suprimentos e ciências para modelar fenômenos com incerteza significativa em suas entradas.

Mas o que é a simulação de Monte Carlo? Como isso funciona? E como posso implementar a simulação e analisar os resultados?

Este tutorial apresentará a você a simulação de Monte Carlo e os conceitos estatísticos relevantes por trás da técnica. Também implementaremos a simulação Monte Carlo no Excel, familiarizando você com as funções internas relevantes do Excel.

Por fim, o tutorial deixará você com as práticas recomendadas, técnicas avançadas e recursos adicionais, fazendo deste tutorial o seu guia completo para aprender tudo sobre a simulação Monte Carlo no Microsoft Excel.

O que é a Simulação de Monte Carlo?

A Simulação de Monte Carlo é uma técnica matemática usada para modelar a probabilidade de diferentes resultados em um processo que não pode ser facilmente previsto devido à intervenção de variáveis aleatórias.

É uma ferramenta poderosa para você entender o impacto do risco e da incerteza em vários campos. O método se baseia em amostragem aleatória repetida para simular o comportamento de sistemas e processos complexos.

Primeiro, o problema é modelado por uma distribuição de probabilidade para cada variável que tenha incerteza inerente. Em seguida, um grande número de amostras aleatórias é extraído dessas distribuições de probabilidade, e essas amostras são usadas para calcular os resultados. Esse processo é repetido várias vezes para criar uma distribuição de resultados possíveis, que podem ser analisados estatisticamente para fornecer previsões sobre como um sistema se comportará.

Então, em termos simples. A simulação de Monte Carlo é uma técnica que prevê como os sistemas complexos se comportarão ao simular seus resultados muitas vezes usando valores aleatórios. Ele usa várias etapas:

• Incerteza do modelo: Defina como cada variável pode variar usando distribuições de probabilidade.
• Amostragem aleatória: Selecione aleatoriamente valores para essas variáveis com base em suas distribuições.
• Simule os resultados: Use esses valores para simular o comportamento do sistema.
• Analisar os resultados: Repita o processo várias vezes para obter uma gama de resultados possíveis e, em seguida, analise-os para prever os cenários mais prováveis.

Em seguida, ampliaremos nosso conhecimento básico sobre a simulação Monte Carlo, aprofundando alguns conceitos estatísticos relevantes.

Entendendo as variáveis aleatórias e as distribuições de probabilidade

As variáveis aleatórias e suas distribuições de probabilidade associadas são fundamentais para a simulação Monte Carlo, pois fornecem a estrutura matemática para modelar e simular a aleatoriedade e a variabilidade inerentes a sistemas complexos.

Variáveis aleatórias

Uma variável aleatória é uma variável cujos valores são resultados de um fenômeno aleatório.

As variáveis aleatórias são classificadas em dois tipos:

• Variáveis aleatórias discretas: Essas variáveis assumem um número contável de valores distintos. Nas simulações, as variáveis discretas podem modelar cenários como o número de itens defeituosos em um lote, chegadas de clientes por hora ou outros eventos contáveis.
• Variáveis aleatórias contínuas: Essas variáveis podem assumir qualquer valor em um intervalo contínuo. As variáveis contínuas são usadas para simulações que lidam com medições físicas ou durações de tempo.

As variáveis aleatórias são usadas em simulações porque contêm a incerteza que as técnicas de Monte Carlo foram projetadas para explorar e quantificar.

Distribuições de probabilidade

As distribuições de probabilidade descrevem como as probabilidades são distribuídas entre os valores de uma variável aleatória.

As distribuições de probabilidade são usadas na simulação Monte Carlo para definir como se espera que diferentes entradas ou cenários se comportem, o que é essencial para a modelagem e a tomada de decisões precisas.

A distribuição normal é a distribuição mais usada em estatísticas e simulações porque muitos fenômenos naturais e humanos tendem a seguir essa distribuição devido ao Teorema do Limite Central.

Distribuição normal(Fonte)

A distribuição normal é usada para modelar variáveis que são influenciadas por muitos efeitos pequenos e independentes, como erros de medição ou retornos do mercado de ações.

Algumas outras distribuições de probabilidade são as distribuições uniformes, que são usadas quando qualquer resultado em um intervalo especificado é igualmente provável - uma suposição comum em simulações quando não há dados anteriores disponíveis - e as distribuições binomiais, que são usadas na modelagem de cenários com dois resultados possíveis (sucesso/fracasso) em uma série de experimentos, como testes de aprovação/reprovação ou verificações de controle de qualidade.

Agora que você já entendeu os conceitos e a teoria por trás das simulações de Monte Carlo, vamos passar para o lado da implementação.

Por que usar o Excel para a simulação de Monte Carlo?

Depois de optar por implementar uma simulação Monte Carlo, você tem várias ferramentas, como Excel, Python, R, SAS e MATLAB, para ajudá-lo com as simulações.

O fator mais importante a ser considerado, especialmente ao implementar a simulação Monte Carlo pela primeira vez, é a familiaridade geral que você tem com a ferramenta. O Excel é uma das ferramentas mais usadas no mundo dos negócios, o que significa que muitas pessoas já estão familiarizadas com suas operações básicas. Isso reduz o tempo de treinamento e elimina a necessidade de aprender um novo software do zero.

O Excel também oferece ferramentas fáceis de usar para criar tabelas e gráficos, que podem ser úteis para visualizar os resultados das simulações. Além disso, vários suplementos avançados estão disponíveis para o Excel, aprimorando sua capacidade de realizar simulações complexas de Monte Carlo.

No entanto, também vale a pena observar que, para simulações mais avançadas, especialmente aquelas que exigem a manipulação de grandes conjuntos de dados ou a execução de um número muito alto de simulações, outras ferramentas especializadas além do Excel podem ser mais adequadas.

Visão geral das funções relevantes do Excel

A seguir, exploraremos duas funções essenciais do Excel: RAND e NORM.INV, abrangendo sua sintaxe, parâmetros e casos de uso típicos. Essas funções ajudam a gerar números aleatórios e a definir distribuições de probabilidade, que são aspectos fundamentais de qualquer simulação.

A sintaxe e os parâmetros da função RAND

O RAND gera um número aleatório maior ou igual a 0 e menor que 1. Os números são distribuídos uniformemente, o que significa que qualquer número dentro do intervalo especificado tem a mesma probabilidade de ocorrer.

A sintaxe do RAND é a seguinte:

RAND()

A função RAND não requer nenhum argumento. É usado simplesmente como RAND().

No contexto da Simulação de Monte Carlo, o site RAND() pode ser usado para simular a ocorrência de eventos aleatórios ou para variar as entradas do seu modelo.

A sintaxe e os parâmetros da função NORM.INV

Enquanto o RAND gera números aleatórios uniformes, o NORM.INV é usado para gerar números aleatórios a partir de uma distribuição normal, que é um requisito comum em uma Simulação de Monte Carlo. Essa função retorna o inverso da distribuição cumulativa normal para uma média e um desvio padrão especificados.

A sintaxe da função NORM.INV é a seguinte:

NORM.INV(probability, mean, standard_deviation)

Os parâmetros são:

• probabilidade: Uma probabilidade correspondente à distribuição normal, que deve ser um valor entre 0 e 1. Normalmente, isso é gerado pela função RAND().
• média: A média aritmética da distribuição normal.
• desvio-padrão: O desvio padrão da distribuição normal, uma medida de como os números estão espalhados em torno da média.

O NORM.INV é usado para transformar números aleatórios uniformemente distribuídos da função RAND em números que seguem uma distribuição normal especificada. Isso se torna útil para modelar variáveis que se espera que apresentem variabilidade natural seguindo uma curva normal.

Agora que você já conhece todos os blocos de construção, funções e conceitos por trás de uma simulação Monte Carlo, vamos implementar uma no Microsoft Excel.

Implementação da simulação de Monte Carlo no Microsoft Excel: Um exemplo

Considere um cenário em que você é um analista de dados que trabalha em uma empresa dinâmica de produtos eletrônicos de consumo e recebeu a tarefa de avaliar a viabilidade financeira do lançamento de um novo rastreador de fitness vestível.

O mercado para esses dispositivos é competitivo e a demanda do consumidor pode ser altamente variável, influenciada por tendências sazonais, eficácia de marketing e ações da concorrência. Além disso, os custos associados à fabricação desses dispositivos estão sujeitos a flutuações devido a mudanças nos custos de materiais e incertezas na cadeia de suprimentos.

Você decidiu usar a simulação Monte Carlo no Excel para enfrentar esses desafios. Você acredita que essa abordagem o ajudará a estimar a lucratividade potencial em diferentes cenários, permitindo que a empresa tome decisões bem informadas sobre estratégias de preços, volumes de produção e investimentos em marketing.

Você também analisou dados anteriores de lançamentos de produtos semelhantes e estudos de mercado no setor de eletrônicos de consumo. A partir dessa análise, você concluiu determinadas métricas que servirão de base para a sua simulação:

• Uma demanda média de 10.000 unidades para novos dispositivos no primeiro ano de lançamento, com um desvio padrão de 2.000 unidades, refletindo a incerteza na aceitação do consumidor.
• O preço de venda unitário normalmente varia entre US$ 50 e US$ 70, dependendo do preço competitivo e da saturação do mercado.
• O custo unitário, influenciado pelos preços voláteis dos materiais e pela eficiência da fabricação, é em média de US$ 30 por unidade, com um desvio padrão de US$ 5.

Esses dados históricos formam as suposições subjacentes dos parâmetros de sua simulação, ajudando a criar a simulação para refletir as condições atuais do mercado com mais precisão.

As etapas que você pode seguir para implementar a simulação de Monte Carlo para esse exemplo específico são as seguintes:

Etapa 1: Configure sua planilha do Excel

Primeiro, prepare sua planilha do Excel para incluir colunas para cada variável e uma coluna para o lucro calculado.

Veja como seria a aparência inicial:

Configurando a planilha do Excel.

Etapa 2: Fórmulas de entrada para variáveis

Em cada linha, você inserirá fórmulas para gerar valores aleatórios para demanda, preço de venda e custo com base nas distribuições que você identificou:

• Demanda: Distribuição normal (média = 10.000 unidades, desvio padrão = 2.000 unidades)
• Preço de venda: Distribuição de uniformes (US$ 50 a US$ 70)
• Custo: Distribuição normal (média = US$ 30, desvio padrão = US$ 5)

Para inserir essas fórmulas uma a uma, selecione a célula A2 e digite o seguinte:

=NORM.INV(RAND(), 10000, 2000)

A equação acima cria uma distribuição normal com uma determinada média e desvio padrão, conforme abaixo:

Criar a distribuição para a demanda.

Em seguida, selecione a célula B2 e digite o seguinte:

=50 + (70-50) * RAND()

A equação acima cria uma distribuição uniforme entre US$ 50 e US$ 70 para o preço de venda, conforme abaixo:

Criação da distribuição do preço de venda.

Selecione a célula C2 e digite o seguinte:

=NORM.INV(RAND(), 30, 5)

A equação acima, semelhante à equação de demanda, cria uma distribuição normal com uma determinada média e desvio padrão, conforme abaixo:

Criando a distribuição para o custo.

Etapa 3: Calcule a variável dependente

Agora, calcule o lucro, que é a variável dependente, para cada simulação usando a fórmula na coluna D:

=(B2 - C2) * A2

Cálculo do lucro.

Etapa 4: Preencher para simular vários cenários

O que fizemos até agora foi criar uma única simulação. Vamos estendê-lo a várias, digamos, mil simulações.

Selecione as células A2 a D2 e arraste a alça de preenchimento (um pequeno quadrado na parte inferior direita da seleção) para baixo para preencher as fórmulas em quantas linhas você quiser simular (por exemplo, 1.000 linhas para 1.000 simulações).

Você terá uma aparência semelhante a esta:

Criando as simulações.

Etapa 5: Analisar os resultados

Depois de executar as simulações, você pode analisar os resultados usando funções estatísticas, como mínimo, máximo, média e desvios padrão. Não hesite em consultar rapidamente a folha de dicas do Excel para se atualizar sobre as funções internas do Excel que usaremos a seguir.

Para descobrir o lucro médio esperado a cada mês, digite o seguinte em uma célula, digamos G6:

=AVERAGE(D2:D1001)

Para descobrir o lucro mínimo esperado a cada mês, digite o seguinte em uma célula, digamos G7:

=MIN(D2:D1001)

Para descobrir o lucro máximo esperado a cada mês, digite o seguinte em uma célula, digamos G8:

=MAX(D2:D1001)

Para encontrar o desvio padrão do lucro, digite o seguinte em uma célula, digamos G9:

=STDEV.P(D2:D1001)

Depois de executada, a planilha do Excel deverá ter a seguinte aparência:

Analisando os resultados da simulação.

Você pode interpretar os resultados estimados e as implicações para o lançamento do produto da seguinte forma:

• O valor do lucro médio representa o lucro esperado com o lançamento do novo rastreador de condicionamento físico. Isso sugere que, em média, cada execução de simulação prevê que poderíamos esperar obter cerca de US$ 298.278,67 de lucro. Esse valor é útil como uma estimativa central da lucratividade de acordo com as premissas fornecidas.
• Um lucro mínimo de US$ 67.598,78 é o menor lucro observado em todas as nossas simulações. Ele indica o pior cenário possível de acordo com as premissas do seu modelo, que ainda é lucrativo, mas significativamente menor do que a média. Isso pode ser devido a uma demanda particularmente baixa ou a condições de custo desfavoráveis nessa simulação específica.
• Um lucro máximo de US$ 641.955,42 representa o melhor cenário possível, em que a demanda e o preço provavelmente foram os mais altos e os custos os mais baixos em todas as simulações. Isso mostra o potencial de alta se as condições se mostrarem altamente favoráveis.

Considerando a ampla faixa entre os lucros mínimo e máximo e o desvio padrão substancial, há um risco financeiro considerável associado ao lançamento do novo produto.

Os tomadores de decisão devem considerar se a empresa está confortável com esse nível de incerteza e com o potencial de lucros abaixo da média.

Além disso, embora seja opcional, incentivamos você a criar visualizações, como histogramas, para ter uma compreensão visual dos resultados das simulações.

Práticas recomendadas e técnicas avançadas para aprimorar as simulações

Ao executar novamente a mesma simulação acima, você pode observar uma pequena diferença nos cálculos, conforme mostrado abaixo:

Resultados de simulação variáveis.

Isso ocorre porque os valores da simulação original podem mudar entre as iterações, influenciando as estimativas resultantes. Embora a variação seja pequena, quando o valor estimado muda, os tomadores de decisão ficam preocupados com a precisão e a confiabilidade da simulação.

Vamos explorar algumas técnicas avançadas que poderíamos usar para melhorar a precisão e a confiabilidade das simulações.

Aumentar o número de simulações

A execução de um número maior de simulações ajuda a calcular a média das flutuações aleatórias e fornece uma estimativa mais estável e precisa dos resultados.

Para o exemplo acima, podemos aumentar o número de execuções de simulação (por exemplo, de 1.000 para 10.000 ou mais), especialmente ao lidar com parâmetros altamente variáveis.

A determinação do número "correto" de simulações depende de vários fatores.

Quanto mais complexo for o modelo (ou seja, quanto mais variáveis e quanto mais amplo for o intervalo de suas interações), mais simulações serão necessárias para capturar todos os resultados possíveis e garantir que os resultados não sejam devidos ao acaso.

Se os inputs tiverem alta variabilidade ou forem muito inclinados, serão necessárias mais simulações para estimar com precisão as caudas (valores extremos) das distribuições de resultados.

Para análises mais detalhadas, especialmente em finanças ou gerenciamento de riscos, não é incomum executar de 10.000 a 100.000 simulações. Esse intervalo é normalmente usado para garantir resultados robustos em vários cenários e dados. É claro que, como mencionamos anteriormente, para essas análises em grande escala, o Excel nem sempre é a melhor opção de ferramenta, mas sim o R ou o Python.

Refinando as distribuições de entrada

A precisão das simulações depende muito de quão bem as distribuições de probabilidade de entrada refletem a verdadeira incerteza e o comportamento das variáveis subjacentes. Em nosso exemplo acima, assumimos uma distribuição normal para a demanda e o custo e uma distribuição uniforme para o preço de venda.

Além disso, poderíamos analisar dados históricos mais abrangentes para parametrizar melhor as distribuições. Podemos entender melhor o comportamento do custo, da venda e da demanda em relação a fatores externos com base em informações de especialistas no domínio. Também podemos considerar o uso de distribuições como log-normal, beta ou gama ou a criação de distribuições personalizadas com base em dados empíricos.

Realização de uma análise de sensibilidade

Essa análise é feita para entender quais variáveis de entrada têm o impacto mais significativo sobre o resultado, variando sistematicamente cada entrada e mantendo as outras constantes.

Em nosso exemplo acima, podemos manter duas variáveis constantes e alterar a distribuição de uma delas para entender as alterações nas estimativas. Em seguida, repita o mesmo processo para as duas variáveis restantes, uma a uma. Por fim, essa técnica ajuda a entender em qual variável você deve concentrar esforços para melhorar a precisão.

O emprego das técnicas acima de forma iterativa e a análise dos resultados podem levar a resultados mais precisos e confiáveis.

Conclusão

Este tutorial apresentou a você a Simulação Monte Carlo e os conceitos estatísticos relevantes. Depois de apresentar as funções relevantes do Excel, o tutorial forneceu um guia passo a passo para você implementar a Simulação Monte Carlo no Excel usando um exemplo do mundo real.

Por fim, você aprendeu sobre algumas práticas recomendadas e técnicas avançadas para garantir que seus resultados sejam mais precisos e confiáveis.

Se você estiver particularmente interessado em implementar a Simulação de Monte Carlo acima usando outras ferramentas, como Python ou R, esses dois recursos serão úteis:

• Simulações de Monte Carlo em R
• Simulações de Monte Carlo em Python

Como alternativa, se você quiser se ater ao conhecido Microsoft Excel e quiser dominar suas habilidades usando a ferramenta amplamente adotada, você deve conferir o programa Excel Fundamentals.