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

• Quem utiliza a simulação monte Carlo?

• O que acontece quando escreve =RAND() numa célula?

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

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

• Como é que uma empresa de cartões 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 os fluxos de caixa de um novo produto terem um valor líquido positivo atual (VAL)? Qual é o fator de risco da nossa carteira de investimento? A simulação monte Carlo permite-nos modelar situações que apresentam incerteza e depois reproduzi-las num computador milhares de vezes.

Nos próximos cinco capítulos, verá exemplos de como pode utilizar o Excel para realizar simulações Monte Carlo.

Muitas empresas utilizam a simulação monte Carlo como uma parte importante do seu processo de tomada de decisão. Eis alguns exemplos.

• General Motors, Proctor e Gamble, Pfizer, Bristol-Myers Squibb, e Eli Lilly usam simulação para estimar tanto o retorno médio como o fator de risco de novos produtos. Na GM, estas informações são utilizadas pelo CEO para determinar que produtos vêm ao mercado.

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

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

• A Proctor e a Gamble utilizam simulação para modelar e proteger idealmente o risco cambial.

• A Sears utiliza a simulação para determinar quantas unidades de cada linha de produto devem ser encomendadas aos fornecedores, por exemplo, o número de pares de calças dockers que devem ser encomendados 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 para expandir, contrair ou adiar um projeto.

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

Quando escreve a fórmula =RAND() numa célula, obtém um número igualmente provável de assumir qualquer valor entre 0 e 1. Assim, cerca de 25% das vezes, deverá obter um número menor ou igual a 0,25; cerca de 10% do tempo deve obter um número que é, pelo menos, 0,90 e assim sucessivamente. Para demonstrar como funciona a função RAND, veja o ficheiro Randdemo.xlsx, apresentado na Figura 60-1.

Primeiro, copie da célula C3 para C4:C402 a fórmula =RAND(). Em seguida, atribua o nome C3:C402 Dados ao intervalo. Em seguida, na coluna F, pode controlar a média dos 400 números aleatórios (célula F2) e utilizar a função CONTAR.SE para determinar as frações entre 0 e 0,25, 0,25 e 0,50, 0,50 e 0,75 e 0,75 e 1. Quando prime a tecla F9, os números aleatórios são recalculados. Repare que a média dos 400 números é sempre aproximadamente 0,5 e que cerca de 25% dos resultados estão em intervalos de 0,25. Estes resultados são consistentes com a definição de um número aleatório. Tenha também em atenção que os valores gerados por 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), não nos diz nada sobre os valores dos outros números aleatórios gerados.

Suponha que a procura de um calendário é 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 fazer com que o Excel seja reproduzido ou simulado, esta procura de 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, etc.

Procura	Número aleatório atribuído
10.000	Menor que 0,10
20.000	Maior ou igual a 0,10 e inferior a 0,45
40,000	Maior ou igual a 0,45 e inferior a 0,75
60 000	Maior ou igual a 0,75

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

A chave da nossa simulação é utilizar um número aleatório para iniciar uma pesquisa a partir do intervalo de tabelas F2:G5 ( pesquisa com nome). Os números aleatórios maiores ou iguais a 0 e inferiores a 0,10 produzirão uma procura de 10 000; números aleatórios maiores ou iguais a 0,10 e inferiores a 0,45 produzirão uma procura de 20 000; números aleatórios maiores ou iguais a 0,45 e inferiores a 0,75 produzirão uma procura de 40 000; e os números aleatórios maiores ou iguais a 0,75 produzirão uma procura de 60 000. Pode gerar 400 números aleatórios ao copiar de C3 para C4:C402 a fórmula RAND(). Em seguida, irá gerar 400 tentativas, ou iterações, da procura de calendário ao copiar de B3 para B4:B402 a fórmula PROCV(C3,pesquisa,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, etc. No intervalo de células F8:F11, utilize a função CONTAR.SE para determinar a fração das nossas 400 iterações que geram cada procura. Quando premimos F9 para recalcular os números aleatórios, as probabilidades simuladas estão próximas das nossas probabilidades de procura assumidas.

Se escrever em qualquer célula a fórmula NORMINV(rand(),mu,sigma), irá 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, apresentado na Figura 60-3.

Vamos supor que queremos simular 400 tentativas 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 escrever estes valores nas células E1 e E2 e atribuir o nome mean e sigma a estas células, respetivamente.) 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,média,sigma) gera 400 valores de avaliação diferentes de uma variável aleatória normal com uma média de 40 000 e um desvio-padrão de 10 000. Quando premimos a tecla F9 para recalcular os números aleatórios, a média permanece próxima de 40 000 e o desvio-padrão é próximo de 10 000.

Essencialmente, para um número aleatório x, a fórmula NORMINV(p,mu,sigma) gera o percentil pde uma variável aleatória normal com um mu médio e um desvio sigma 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, verá como a simulação Monte Carlo pode ser utilizada como uma ferramenta de tomada de decisões. 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 saudação é vendido por $4,00, e o custo variável de produção de cada cartão é de $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). Em seguida, determinamos qual a quantidade de encomendas que gera o lucro médio máximo ao longo das 1000 iterações. Pode encontrar os dados desta secção no ficheiro Valentine.xlsx, apresentados na Figura 60-4. Atribui os nomes de intervalo nas células B1:B11 às células C1:C11. É atribuído ao intervalo de células G3:H6 a pesquisa de nome. Os nossos parâmetros de preço de venda e custo são introduzidos nas células C4:C6.

Pode introduzir 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, simula a procura do cartão na célula C3 com a fórmula PROCV(rand,pesquisa,2). (Na fórmula PROCV, rand é o nome da célula atribuído à célula C3, não a função ALEATÓRIO.)

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

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

Gostaríamos de uma forma eficiente de premir F9 muitas vezes (por exemplo, 1000) por 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 chega ao nosso resgate. (Consulte o Capítulo 15, "Análise de Confidencialidade com Tabelas de Dados", para obter detalhes sobre tabelas de dados.) A tabela de dados utilizada neste exemplo é apresentada na Figura 60-5.

No intervalo de células A16:A1015, introduza os números 1 a 1000 (correspondentes às nossas 1000 tentativas). Uma forma fácil de criar estes valores é começar por introduzir 1 na célula A16. Selecione a célula e, em seguida, no separadorBase no grupo Edição, clique em Preenchimento e selecione Série para apresentar a caixa de diálogo Série. Na caixa de diálogo Série , apresentada na Figura 60-6, introduza um Valor de Passo de 1 e um Valor de Paragem de 1000. Na área Série Em , selecione a opção Colunas e, em seguida, clique em OK. Os números 1 a 1000 serão introduzidos na coluna A, começando na célula A16.

Em seguida, introduzimos as nossas possíveis quantidades de produção (10 000, 20 000, 40 000, 60 000) nas células B15:E15. Queremos calcular o lucro de cada número de avaliação (de 1 a 1000) e de 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 para simular 1000 iterações de procura para cada quantidade de produção. Selecione o intervalo de tabelas (A15:E1014) e, em seguida, no grupo Ferramentas de Dados no separador Dados, clique em Análise de Hipóteses e, em seguida, selecione Tabela de Dados. 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 de Coluna. Depois de clicar em OK, o Excel simula 1000 valores de procura para cada quantidade de encomendas.

Para compreender por que motivo isto funciona, considere os valores colocados pela tabela de dados no intervalo de células C16:C1015. Para cada uma destas células, o Excel utilizará um valor de 20 000 na célula C1. Em C16, o valor da célula de entrada da coluna de 1 é colocado numa célula em branco e o número aleatório na célula C2 é recalculado. Em seguida, o lucro correspondente é registado na célula C16. Em seguida, o valor de entrada da célula de coluna de 2 é colocado numa célula em branco e o número aleatório em C2 volta a ser calculado. O lucro correspondente é introduzido na célula C17.

Ao copiar da célula B13 para C13:E13 a fórmula MÉDIA(B16:B1015), calculamos o lucro simulado médio 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 encomendas. Sempre que premimos F9, são simuladas 1000 iterações de procura para cada quantidade de encomendas. Produzir 40.000 cartões produz sempre o maior lucro esperado. Portanto, parece que produzir 40.000 cartões é a decisão adequada.

O Impacto do Risco na Nossa Decisão      Se produzirmos 20.000 em vez de 40.000 cartões, o nosso lucro esperado cai aproximadamente 22%, mas o nosso risco (medido pelo desvio-padrão do lucro) cai quase 73%. Portanto, 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 eles sem sobras.

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 é denominado intervalo de confiança de 95% para 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, calcula 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 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 apresentados na Figura 60-7.

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

1. Um negociante 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 é de $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 ordenar 200, 220, 240, 260, 280 ou 300 enviados. Quantos deve encomendar?

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

   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 de Pessoas e vende-a por $1,95. Cada cópia não vendida pode ser devolvida por $0,50. Quantas cópias de Pessoas devem ser armazenadas?