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

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

• O que acontece quando você digita =RAND() em uma célula?

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

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

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

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

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

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

• General Motors, Proctor e Gamble, Pfizer, Bristol-Myers Squibb e Eli Lilly usam simulação para estimar o retorno médio e o fator de risco de novos produtos. Na GM, essas informações são usadas pelo CEO para determinar quais produtos chegam ao mercado.

• A GM usa simulação para atividades como prever o lucro líquido para a corporação, prever custos estruturais e de compra e determinar sua suscetibilidade a diferentes tipos de risco (como alterações na taxa de juros e flutuações cambiais).

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

• Proctor e Gamble usam simulação para modelar e proteger de forma ideal o risco cambial.

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

• Empresas petrolíferas e farmacêuticas usam simulação para valorizar "opções reais", como o valor de uma opção para expandir, contratar ou adiar um projeto.

• Os planejadores financeiros usam a simulação de Monte Carlo para determinar estratégias de investimento ideais para a aposentadoria de seus clientes.

Ao digitar a fórmula =RAND() em uma célula, você obtém um número igualmente provável de assumir qualquer valor entre 0 e 1. Assim, cerca de 25% do tempo, você deve obter um número menor ou igual a 0,25; cerca de 10% do tempo você deve obter um número que é pelo menos 0,90, e assim por diante. Para demonstrar como a função RAND funciona, dê uma olhada no Randdemo.xlsx de arquivo, mostrado na Figura 60-1.

Primeiro, copie da célula C3 para C4:C402 a fórmula =RAND(). Em seguida, você nomeia o intervalo de dados C3:C402. Em seguida, na coluna F, você pode acompanhar a média dos 400 números aleatórios (célula F2) e usar 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 você pressiona a tecla F9, os números aleatórios são recalculados. Observe que a média dos 400 números é sempre de aproximadamente 0,5, e que cerca de 25% dos resultados estão em intervalos de 0,25. Esses resultados são consistentes com a definição de um número aleatório. Observe também que os valores gerados pelo RAND em células diferentes são independentes. Por exemplo, se o número aleatório gerado na célula C3 for um número grande (por exemplo, 0,99), ele não nos dirá nada sobre os valores dos outros números aleatórios gerados.

Suponha que a demanda por um calendário seja governada pela seguinte variável aleatória discreta:

Demanda	Probabilidade
10.000	0,10
20.000	0.35
40,000	0,3
60.000	0,25

Como podemos fazer o Excel jogar fora ou simular essa demanda por calendários muitas vezes? O truque é associar cada valor possível da função RAND a uma possível demanda por calendários. A atribuição a seguir garante que uma demanda de 10.000 ocorra 10% do tempo e assim por diante.

Demanda	Número aleatório atribuído
10.000	Menos de 0,10
20.000	Maior ou igual a 0,10 e menor que 0,45
40,000	Maior ou igual a 0,45 e menor que 0,75
60.000	Maior ou igual a 0,75

Para demonstrar a simulação da demanda, examine o Discretesim.xlsx de arquivo, mostrado na Figura 60-2 na próxima página.

A chave para nossa simulação é usar um número aleatório para iniciar uma pesquisa do intervalo de tabela F2:G5 ( pesquisa nomeada). Números aleatórios maiores ou iguais a 0 e inferiores a 0,10 renderão uma demanda de 10.000; números aleatórios maiores ou iguais a 0,10 e inferiores a 0,45 produzirão uma demanda de 20.000; números aleatórios maiores ou iguais a 0,45 e inferiores a 0,75 produzirão uma demanda de 40.000; e números aleatórios maiores ou iguais a 0,75 renderão uma demanda de 60.000. Você gera 400 números aleatórios copiando de C3 para C4:C402 a fórmula RAND(). Em seguida, você gera 400 avaliações ou iterações da demanda de calendário copiando da B3 para B4:B402 a fórmula VLOOKUP(C3, pesquisa,2). Essa fórmula garante que qualquer número aleatório menor que 0,10 gere uma demanda de 10.000, qualquer número aleatório entre 0,10 e 0,45 gera uma demanda de 20.000 e assim por diante. No intervalo de células F8:F11, use a função COUNTIF para determinar a fração de nossas 400 iterações que produzem cada demanda. Quando pressionamos f9 para recalcular os números aleatórios, as probabilidades simuladas estão próximas de nossas probabilidades de demanda presumidas.

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

Vamos supor que queremos simular 400 avaliações, 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. (Você pode digitar esses valores nas células E1 e E2 e nomear essas células média e sigma, respectivamente.) Copiar a fórmula =RAND() de C4 para C5:C403 gera 400 números aleatórios diferentes. Copiar de B4 para B5:B403 a fórmula NORMINV(C4,mean,sigma) gera 400 valores de avaliação diferentes de uma variável aleatória normal com 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 próxima a 40.000 e o desvio padrão próximo a 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 sigma de desvio padrão. Por exemplo, o número aleatório 0,77 na célula C4 (consulte Figura 60-3) gera na célula B4 aproximadamente o 77º percentil de uma variável aleatória normal com uma média de 40.000 e um desvio padrão de 10.000.

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

Demanda	Probabilidade
10.000	0,10
20.000	0.35
40,000	0,3
60.000	0,25

O cartão de saudação é vendido por US$ 4,00, e o custo variável de produção de cada cartão é de US$ 1,50. Os cartões restantes devem ser descartados a um custo de US$ 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). Em seguida, determinamos qual quantidade de pedido gera o lucro médio máximo sobre as 1000 iterações. Você pode encontrar os dados desta seção no Valentine.xlsx de arquivo, mostrados na Figura 60-4. Você atribui os nomes de intervalo nas células B1:B11 às células C1:C11. O intervalo de células G3:H6 recebe a pesquisa de nome. Nossos parâmetros de preço de venda e custo são inseridos nas células C4:C6.

Você pode inserir uma quantidade de produção de avaliação (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(). Conforme descrito anteriormente, você simula a demanda pelo cartão na célula C3 com a fórmula VLOOKUP(rand,lookup,2). (Na fórmula VLOOKUP, rand é o nome da célula atribuído à célula C3, não à função RAND.)

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

Se produzirmos mais cartões do que em demanda, o número de unidades restantes será igual à produção menos a demanda; caso contrário, nenhuma unidade será deixada. Calculamos nosso custo de eliminação na célula C10 com a fórmula unit_disp_cost*IF(produzida>demanda, produzida–demanda,0). Por fim, na célula C11, calculamos nosso lucro como receita total_var_cost-total_disposing_cost.

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

No intervalo de células A16:A1015, insira os números de 1 a 1000 (correspondentes às nossas 1000 avaliações). Uma maneira fácil de criar esses valores é começar inserindo 1 na célula A16. Selecione a célula e, em seguida, na guia Página Inicial no grupo Edição , clique em Preencher e selecione Série para exibir a caixa de diálogo Série . Na caixa de diálogo Série , mostrada na Figura 60-6, insira um Valor de Etapa 1 e um Valor de Parada de 1000. Na área Series In , selecione a opção Colunas e clique em OK. Os números 1 a 1000 serão inseridos na coluna A começando na célula A16.

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

Agora estamos prontos para enganar o Excel para simular 1000 iterações de demanda para cada quantidade de produção. Selecione o intervalo de tabelas (A15:E1014) e, em seguida, no grupo Ferramentas de Dados na guia Dados, clique em What If Analysis e selecione Tabela de Dados. Para configurar uma tabela de dados bidirecional, escolha nossa quantidade de produção (célula C1) como a Célula de Entrada de Linha e selecione qualquer célula em branco (escolhemos a célula I14) como a Célula de Entrada da Coluna. Depois de clicar em OK, o Excel simula 1000 valores de demanda para cada quantidade de pedido.

Para entender por que isso funciona, considere os valores colocados pela tabela de dados no intervalo de células C16:C1015. Para cada uma dessas células, o Excel usará um valor de 20.000 na célula C1. Em C16, o valor da célula de entrada da coluna 1 é colocado em uma célula em branco e o número aleatório na célula C2 é recalculado. O lucro correspondente é então registrado na célula C16. Em seguida, o valor de entrada da célula de coluna de 2 é colocado em uma célula em branco e o número aleatório em C2 novamente é recalculado. O lucro correspondente é inserido na célula C17.

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

O impacto do risco em nossa decisão      Se produzimos 20.000 em vez de 40.000 cartões, nosso lucro esperado cai aproximadamente 22%, mas nosso risco (medido pelo desvio padrão de lucro) cai quase 73%. Portanto, se formos extremamente avessos ao risco, produzir 20.000 cartões pode ser a decisão certa. Aliás, produzir 10.000 cartões sempre tem um desvio padrão de 0 cartões porque se produzirmos 10.000 cartões, sempre venderemos todos eles sem sobras.

Intervalo de confiança para lucro médio      Uma pergunta natural a ser feita nesta situação é, em que intervalo temos 95% de certeza de que o lucro médio verdadeiro cairá? Esse 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:

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

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

1. Um revendedor gmc acredita que a demanda por 2005 Envoys será normalmente distribuída com uma média de 200 e desvio padrão de 30. Seu custo de receber um enviado é de $25.000, e ele vende um enviado por $40.000. Metade de todos os Enviados não vendidos a preço total podem ser vendidos por $30.000. Ele está considerando ordenar 200, 220, 240, 260, 280 ou 300 enviados. Quantos ele deve pedir?

2. Um pequeno supermercado está tentando determinar quantas cópias de Pessoas revista eles devem pedir a cada semana. Eles acreditam que sua demanda por Pessoas é governada pela seguinte variável aleatória discreta:

   Demanda	Probabilidade
   15	0,10
   20	0,20
   25	0,30%
   30	0,25
   35	0,15

3. O supermercado paga US$ 1,00 por cada cópia de Pessoas e o vende por US$ 1,95. Cada cópia não vendida pode ser retornada por US$ 0,50. Quantas cópias de Pessoas o pedido do repositório deve ser pedido?