Introdução à simulação de Monte Carlo no Excel

Este artigo foi adaptado da Microsoft Excel Data Analysis and Business Modeling por Wayne L. Winston.

  • Quem usa a simulação de Monte Carlo?

  • O que acontece quando escreve =RAND() numa cela?

  • Como se pode simular valores de uma variável aleatória discreta?

  • Como se pode simular valores de uma variável aleatória normal?

  • Como pode uma empresa de cartões de saudação determinar quantos cartões produzir?

Gostaríamos de estimar com precisão as probabilidades de eventos incertos. Por exemplo, qual é a probabilidade de os fluxos de caixa de um novo produto terem um valor presente líquido positivo (NPV)? Qual é o fator de risco da nossa carteira de investimento? A simulação de Monte Carlo permite-nos modelar situações que apresentam incertezas e depois jogá-las num computador milhares de vezes.

Nota:  O nome de simulação de Monte Carlo provém das simulações de computador realizadas durante as décadas de 1930 e 1940 para estimar a probabilidade de a reação em cadeia necessária para uma bomba atómica detonar com sucesso. Os físicos envolvidos neste trabalho eram grandes fãs de jogo, por isso deram às simulações o nome de código Monte Carlo.

Nos próximos cinco capítulos, você verá exemplos de como você pode usar Excel para executar simulações de Monte Carlo.

Muitas empresas usam a simulação de Monte Carlo como parte importante do seu processo de tomada de decisão. Aqui estão alguns exemplos.

  • A General Motors, a Proctor e a Gamble, a Pfizer, a Bristol-Myers Squibb e eli Lilly usam a simulação para estimar tanto o retorno médio como o fator de risco de novos produtos. Na GM, esta informação é utilizada pelo CEO para determinar quais os produtos que chegam ao mercado.

  • A GM utiliza simulação para atividades como a previsão do rendimento líquido da empresa, a previsão de custos estruturais e de compra e a determinação da sua suscetibilidade a diferentes tipos de risco (tais como alterações das taxas de juro e flutuações cambiais).

  • Lilly usa simulação para determinar a capacidade ideal da planta para cada fármaco.

  • Proctor e Gamble usam simulação para modelar e, da melhor forma, sebe de risco cambial.

  • A Sears usa simulação para determinar quantas unidades de cada linha de produtos devem ser encomendadas a fornecedores — por exemplo, o número de pares de calças Dockers que devem ser encomendadas este ano.

  • As empresas petrolíferas e farmacêuticas usam a simulação para valorizar "opções reais", como o valor de uma opção de expansão, contratação ou adiamento de um projeto.

  • Os planeadores financeiros usam a simulação de Monte Carlo para determinar as estratégias de investimento ideais para a reforma dos seus clientes.

Quando digita a fórmula =RAND() numa célula, obtém-se um número que é igualmente suscetível de assumir qualquer valor entre 0 e 1. Assim, cerca de 25% das vezes, deve obter um número inferior ou igual a 0,25; cerca de 10% do tempo deve obter um número que é pelo menos 0,90, e assim por diante. Para demonstrar como funciona a função RAND, dê uma olhada no ficheiro Randdemo.xlsx, mostrado na Figura 60-1.

Imagem do Livro

Nota:  Ao abrir o ficheiro Randdemo.xlsx, não verá os mesmos números aleatórios mostrados na Figura 60-1. A função RAND recalcula automaticamente os números que gera quando uma folha de cálculo é aberta ou quando novas informações são introduzidas na folha de cálculo.

Em primeiro lugar, copiar da célula C3 para C4:C402 a fórmula =RAND(). Em seguida, nomeia o intervalo C3:C402 Data. Em seguida, na coluna F, pode rastrear a média dos 400 números aleatórios (célula F2) e utilizar a função COUNTIF para determinar as frações que estão entre 0 e 0,25, 0,25 e 0,50, 0,50 e 0,75, e 0,75 e 1. Quando pressiona a tecla F9, os números aleatórios são recalculados. Note-se que a média dos 400 números é sempre de aproximadamente 0,5, e que cerca de 25 por cento dos resultados são em intervalos de 0,25. Estes resultados são consistentes com a definição de um número aleatório. Note também que os valores gerados pela RAND em diferentes células são independentes. Por exemplo, se o número aleatório gerado na célula C3 é um grande número (por exemplo, 0,99), não nos diz nada sobre os valores dos outros números aleatórios gerados.

Suponha que a procura de um calendário seja regida pela seguinte variável aleatória discreta:

Procura

Probabilidade

10.000

0,10

20.000

0.35

40,000

0,3

60 000

0,25

Como podemos ter o Excel a jogar, ou simular, esta procura por calendários muitas vezes? O truque é associar cada valor possível da função RAND a uma possível procura de calendários. A seguinte atribuição garante que uma procura de 10.000 ocorrerá 10% do tempo, e assim por diante.

Procura

Número aleatório atribuído

10.000

Menos de 0,10

20.000

Maior ou igual a 0,10, e menos de 0,45

40,000

Maior ou igual a 0,45, e menos de 0,75

60 000

Maior ou igual a 0,75

Para demonstrar a simulação da procura, veja o ficheiro Discretesim.xlsx, mostrado na Figura 60-2 na página seguinte.

Imagem do Livro

A chave para a nossa simulação é usar um número aleatório para iniciar uma pesquisa a partir da gama de mesa F2:G5 (chamada de pesquisa). Números aleatórios superiores ou iguais a 0 e inferiores a 0,10 produzirão uma procura de 10.000; Números aleatórios superiores ou iguais a 0,10 e inferiores a 0,45 produzirão uma procura de 20.000; Números aleatórios superiores ou iguais a 0,45 e inferiores a 0,75 produzirão uma procura de 40.000; e números aleatórios superiores ou iguais a 0,75 produzirão uma procura de 60.000. Gera 400 números aleatórios copiando de C3 a C4:C402 a fórmula RAND(). Em seguida, gera 400 ensaios, ou iterações, de procura de calendário copiando de B3 para B4:B402 a fórmula VLOOKUP (C3,lookup,2). Esta fórmula garante que qualquer número aleatório inferior a 0,10 gera uma procura de 10.000, qualquer número aleatório entre 0,10 e 0,45 gera uma procura de 20.000, e assim por diante. Na gama celular F8:F11, utilize a função COUNTIF para determinar a fração das nossas 400 iterações que produzem cada exigência. Quando pressionamos F9 para recalcular os números aleatórios, as probabilidades simuladas estão próximas das nossas probabilidades de procura assumidas.

Se digitar em qualquer célula a fórmula NORMINV(rand(),mu,sigma), gerará um valor simulado de uma variável aleatória normal com um mu médio e um desvio padrão sigma. Este procedimento é ilustrado no ficheiro Normalsim.xlsx, mostrado na Figura 60-3.

Imagem do Livro

Vamos supor que queremos simular 400 ensaios, ou iterações, para uma variável aleatória normal com uma média de 40.000 e um desvio padrão de 10.000. (Pode digitar estes valores nas células E1 e E2, e nomear estas células médias e sigma,respectivamente.) Copiar a fórmula =RAND() de C4 a C5:C403 gera 400 números aleatórios diferentes. A cópia de B4 a B5:B403 a fórmula NORMINV (C4,média, sigma) gera 400 valores experimentais diferentes de uma variável aleatória normal com uma média de 40.000 e um desvio padrão de 10.000. Quando pressionamos a tecla F9 para recalcular os números aleatórios, a média permanece perto de 40.000 e o desvio padrão perto de 10.000.

Essencialmente, para um número aleatório x, a fórmula NORMINV (p,mu,sigma) gera o percentil pth de uma variável aleatória normal com um mu médio e um sigmade desvio padrão . Por exemplo, o número aleatório 0,77 na célula C4 (ver Figura 60-3) gera na célula B4 aproximadamente o percentil 77 de uma variável aleatória normal com uma média de 40.000 e um desvio padrão de 10.000.

Nesta secção, você verá como a simulação de Monte Carlo pode ser usada como uma ferramenta de tomada de decisão. Suponha que a procura de um cartão do Dia dos Namorados é regida pela seguinte variável aleatória discreta:

Procura

Probabilidade

10.000

0,10

20.000

0.35

40,000

0,3

60 000

0,25

O cartão de felicitações vende-se por $4,00, e o custo variável de produzir cada cartão é $1,50. Os cartões restantes devem ser eliminados com um custo de $0,20 por cartão. Quantos cartões devem ser impressos?

Basicamente, simulamos cada quantidade de produção possível (10.000, 20.000, 40.000 ou 60.000) muitas vezes (por exemplo, 1000 iterações). Então determinamos qual a quantidade de encomenda que rende o lucro médio máximo sobre as 1000 iterações. Pode encontrar os dados desta secção no ficheiro Valentine.xlsx, mostrados na Figura 60-4. Atribui os nomes de gama nas células B1:B11 às células C1:C11. O intervalo de células G3:H6 é atribuído ao nome de procuração. O nosso preço de venda e os parâmetros de custo estão inscritos nas células C4:C6.

Imagem do Livro

Pode introduzir uma quantidade de produção experimental (40.000 neste exemplo) na célula C1. Em seguida, crie um número aleatório na célula C2 com a fórmula =RAND(). Como descrito anteriormente, simula a procura do cartão na célula C3 com a fórmula VLOOKUP (rand,lookup,2). (Na fórmula VLOOKUP, rand é o nome celular atribuído à célula C3, não a função RAND.)

O número de unidades vendidas é menor da nossa quantidade de produção e procura. Na célula C8, calcula-se a nossa receita com a fórmula MIN (produzida,demand)*unit_price. Na célula C9, calcula-se o custo total de produção com a fórmula produzida*unit_prod_cost.

Se produzirmos mais cartões do que a procura, o número de unidades que sobram é igual à produção menos procura; caso contrário, não sobram unidades. Calculamos o nosso custo de eliminação na célula C10 com a fórmula unit_disp_cost*IF (produzido>procura, produzido-demand,0). Finalmente, na célula C11, calculamos o nosso lucro como receita: total_var_cost-total_disposing_cost.

Gostaríamos de uma forma eficiente de pressionar F9 muitas vezes (por exemplo, 1000) para cada quantidade de produção e contabilizar o nosso lucro esperado para cada quantidade. Esta situação é aquela em que uma tabela de dados bidirecional vem resgatar-nos. (Ver Capítulo 15, "Análise de Sensibilidade com Tabelas de Dados", para obter detalhes sobre tabelas de dados.) A tabela de dados utilizada neste exemplo é mostrada na Figura 60-5.

Imagem do Livro

Na gama celular A16:A1015, insira os números 1-1000 (correspondentes aos nossos 1000 ensaios). Uma maneira fácil de criar estes valores é começar por introduzir 1 na célula A16. Selecione a célula e, em seguida, no separador Home no grupo Editar, clique em Preenchere selecione Série para exibir a caixa de diálogo série. Na caixa de diálogo série, mostrada na Figura 60-6, introduza um Valor de Passo de 1 e um Valor de Paragem de 1000. Na área Série In, selecione a opção Colunas e, em seguida, clique em OK. Os números 1-1000 serão introduzidos na coluna A a partir da célula A16.

Imagem do Livro

Em seguida, entramos nas nossas possíveis quantidades de produção (10.000, 20.000, 40.000, 60.000) nas células B15:E15. Queremos calcular os lucros de cada número experimental (1 a 1000) e cada quantidade de produção. Referimo-nos à fórmula de lucro (calculada na célula C11) na célula superior esquerda da nossa tabela de dados (A15) ao introduzir =C11.

Estamos agora prontos para enganar o Excel a simular 1000 iterações de procura por cada quantidade de produção. Selecione a gama de tabelas (A15:E1014) e, em seguida, no grupo Data Tools no separador Dados, clique no What If Analysis e, em seguida, selecione Data Table. Para configurar uma tabela de dados bidirecional, escolha a nossa quantidade de produção (célula C1) como célula de entrada de linha e selecione qualquer célula em branco (escolhemos a célula I14) como célula de entrada da coluna. Depois de clicar OK, o Excel simula 1000 valores de procura para cada quantidade de encomenda.

Para entender por que razão isto funciona, considere os valores colocados pela tabela de dados na gama celular C16:C1015. Para cada uma destas células, o Excel usará um valor de 20.000 na célula C1. Em C16, o valor celular de entrada da coluna de 1 é colocado numa célula em branco e o número aleatório na célula C2 recalcula. O lucro correspondente é então registado na célula C16. Em seguida, o valor de entrada da célula da coluna de 2 é colocado numa célula em branco, e o número aleatório em C2 recalcula novamente. O lucro correspondente é introduzido na célula C17.

Ao copiar da célula B13 para C13:E13 a fórmula AVERAGE (B16:B1015)calculamos o lucro médio simulado para cada quantidade de produção. Ao copiar da célula B14 para C14:E14 a fórmula STDEV (B16:B1015), calculamos o desvio padrão dos nossos lucros simulados para cada quantidade de encomenda. Cada vez que pressionamos F9, 1000 iterações de procura são simuladas para cada quantidade de encomenda. Produzir 40.000 cartões rende sempre o maior lucro esperado. Por conseguinte, parece que produzir 40.000 cartões é a decisão adequada.

O Impacto do Risco na Nossa Decisão     Se produzimos 20.000 em vez de 40.000 cartões, o nosso lucro esperado cai cerca de 22%, mas o nosso risco (medido pelo desvio padrão dos lucros) cai quase 73%. Por conseguinte, se formos extremamente avessos ao risco, produzir 20.000 cartões pode ser a decisão certa. A propósito, produzir 10.000 cartões tem sempre um desvio padrão de 0 cartões porque se produzirmos 10.000 cartões, venderemos sempre todos sem restos.

Nota:  Neste livro, a opção Cálculo é definida como Automática Exceto para Tabelas. (Utilize o comando de cálculo no grupo de cálculo no separador Fórmulas.) Esta definição garante que a nossa tabela de dados não será recalculada a menos que pressionemos F9, o que é uma boa ideia porque uma grande tabela de dados irá abrandar o seu trabalho se recalcular cada vez que escreve algo na sua folha de cálculo. Note que, neste exemplo, sempre que pressionar F9, o lucro médio mudará. Isto acontece porque cada vez que pressiona F9, uma sequência diferente de 1000 números aleatórios é usada para gerar exigências para cada quantidade de encomenda.

Intervalo de confiança para lucro médio     Uma pergunta natural a fazer nesta situação é, em que intervalo estamos 95% certos de que o verdadeiro lucro médio vai cair? Este intervalo é chamado de intervalo de confiança de 95% para o lucro médio. Um intervalo de confiança de 95% para a média de qualquer saída de simulação é calculado pela seguinte fórmula:

Imagem do Livro

Na célula J11, calcula-se o limite inferior para o intervalo de confiança de 95% no lucro médio quando são produzidos 40.000 calendários com a fórmula D13-1.96*D14/SQRT (1000). Na célula J12, calcula-se o limite superior para o nosso intervalo de confiança de 95% com a fórmula D13+1.96*D14/SQRT (1000). Estes cálculos são mostrados na Figura 60-7.

Imagem do Livro

Temos 95% de certeza que o nosso lucro médio quando são encomendados 40.000 calendários é entre $56.687 e $62.589.

  1. Um concessionário da GMC acredita que a procura de enviados de 2005 será normalmente distribuída com uma média de 200 e um desvio padrão de 30. O custo de receber um enviado é $25.000, e vende um enviado por $40.000. Metade de todos os enviados não vendidos a preço total podem ser vendidos por $30.000. Está a considerar encomendar 200, 220, 240, 260, 280 ou 300 enviados. Quantos deve pedir?

  2. Um pequeno supermercado está a tentar determinar quantas cópias da revista People devem encomendar todas as semanas. Acreditam que a sua procura por pessoas é regida pela seguinte variável aleatória:

    Procura

    Probabilidade

    15

    0,10

    20

    0.20

    25

    0.30

    30

    0,25

    35

    0,15

  3. O supermercado paga $1,00 por cada cópia do People e vende-o por $1,95. Cada cópia não vendida pode ser devolvida por $0,50. Quantas cópias das pessoas devem encomendar?

Precisa de mais ajuda?

Pode sempre perguntar a um especialista na Comunidade Tecnológica do Excel, obter suporte na Comunidade de Respostas ou sugerir uma nova funcionalidade ou melhoria no User Voice do Excel.

Nota:  Esta página foi traduzida automaticamente e pode conter erros gramaticais ou imprecisões. O nosso objetivo é que estes conteúdos lhe sejam úteis. Pode indicar-nos se estas informações foram úteis? Eis o artigo em inglês para sua referência.​

Aumente os seus conhecimentos do Office
Explore as formações
Seja o primeiro a obter novas funcionalidades
Adira ao Office Insider

As informações foram úteis?

Obrigado pelos seus comentários!

Obrigado pelo seu feedback! Parece que poderá ser benéfico reencaminhá-lo para um dos nossos agentes de suporte do Office.

×