Este artigo foi adaptado de Microsoft Excel Data Analysis and Business Modeling de Wayne L. Winston.
Visão Geral
- 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 card de mensagens 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 (VPL) positivo? 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, reproduzi-las em um computador milhares de vezes.
Observação
O nome simulação de Monte Carlo vem das simulações de computador realizadas durante as décadas de 1930 e 1940 para estimar a probabilidade de que a reação em cadeia necessária para uma bomba atômica detonar funcionasse com sucesso. Os físicos envolvidos neste trabalho eram grandes fãs de jogos de azar, então deram às simulações o codinome Monte Carlo.
Nos próximos cinco capítulos, você verá exemplos de como você pode usar o Excel para realizar simulações de Monte Carlo.
Quem usa a simulação de Monte Carlo?
Muitas empresas usam a simulação de Monte Carlo como uma parte importante de seu processo de tomada de decisão. Aqui estão alguns exemplos.
- General Motors, Proctor and 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 a simulação para atividades como previsão de lucro líquido para a corporação, previsão de custos estruturais e de compra e determinação de sua suscetibilidade a diferentes tipos de risco (como mudanças na taxa de juros e flutuações da taxa de câmbio).
- A Lilly usa simulação para determinar a capacidade ideal da planta para cada medicamento.
- A Proctor and Gamble usa simulação para modelar e proteger o risco cambial de maneira ideal.
- A Sears usa simulação para determinar quantas unidades de cada linha de produtos devem ser encomendadas aos 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 avaliar "opções reais", como o valor de uma opção para expandir, contrair ou adiar um projeto.
- Os planejadores financeiros usam a simulação de Monte Carlo para determinar as estratégias de investimento ideais para a aposentadoria de seus clientes.
O que acontece quando você digita =RAND() em uma célula?
Ao digitar a fórmula =ALEATÓRIO() em uma célula, você obtém um número com a mesma probabilidade de assumir qualquer valor entre 0 e 1. Assim, em torno de 25% das vezes, você deve obter um número menor ou igual a 0,25; Em cerca de 10% das vezes, você deve obter um número que seja pelo menos 0,90 e assim por diante. Para demonstrar como a função ALEATÓRIO funciona, dê uma olhada no Randdemo.xlsx arquivo, mostrado na Figura 60-1.
Observação
Ao abrir a Randdemo.xlsx de arquivos, você não verá os mesmos números aleatórios mostrados na Figura 60-1. A função ALEATÓRIO sempre recalcula automaticamente os números que gera quando uma planilha é aberta ou quando novas informações são inseridas nela.
Primeiro, copie da célula C3 para C4:C402 a fórmula =RAND(). Em seguida, você nomeia o intervalo como 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 CONT.SE 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 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 por ALEATÓRIO 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.
Como você pode simular valores de uma variável aleatória discreta?
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 com que o Excel reproduza ou simule essa demanda por calendários muitas vezes? O truque é associar cada valor possível da função ALEATÓRIO a uma possível demanda por calendários. A atribuição a seguir garante que uma demanda de 10.000 ocorrerá 10% do tempo e assim por diante.
| demanda | Número aleatório atribuído |
|---|---|
| 10.000 | Menor que 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 de demanda, veja a Discretesim.xlsx do arquivo, mostrada 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 a partir do intervalo de tabelas F2:G5 ( pesquisa nomeada). Números aleatórios maiores ou iguais a 0 e menores que 0,10 produzirão uma demanda de 10.000; números aleatórios maiores ou iguais a 0,10 e menores que 0,45 produzirão uma demanda de 20.000; números aleatórios maiores ou iguais a 0,45 e menores que 0,75 produzirão uma demanda de 40.000; e números aleatórios maiores ou iguais a 0,75 resultarão em 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 do calendário copiando de B3 para B4:B402 a fórmula PROCV(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 gerará uma demanda de 20.000 e assim por diante. No intervalo de células F8:F11, use a função CONT.SE para determinar a fração de nossas 400 iterações que produz cada demanda. Quando pressionamos F9 para recalcular os números aleatórios, as probabilidades simuladas estão próximas de nossas probabilidades de demanda assumidas.
Como você pode simular valores de uma variável aleatória normal?
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 uma média mu e desvio padrão sigma. Este procedimento é ilustrado no Normalsim.xlsx do arquivo, mostrado 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. (Você pode digitar esses valores nas células E1 e E2 e nomeá-las como média e sigma, respectivamente.) Copiar a fórmula =ALEATÓRIO() de C4 para C5:C403 gera 400 números aleatórios diferentes. Copiando de B4 para B5:B403, a fórmula INV.NORM(C4,média,sigma) gera 400 valores de tentativa diferentes a partir 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 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 pésimo percentil de uma variável aleatória normal com uma média mu e um desvio padrão sigma. Por exemplo, o número aleatório 0,77 na célula C4 (consulte a 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.
Como uma empresa de card de mensagens pode determinar quantos cartões produzir?
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 card de Dia dos Namorados 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 |
A card de saudação é vendida por R$ 4,00 e o custo variável de produção de cada card é de R$ 1,50. Os cartões que sobrarem devem ser eliminados a um custo de $0,20 por card. 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 ordem gera o lucro médio máximo ao longo das 1000 iterações. Você pode encontrar os dados para esta seção no Valentine.xlsx do arquivo, mostrado na Figura 60-4. Pode atribuir os nomes dos intervalos nas células B1:B11 às células C1:C11. Ao intervalo de células G3:H6 é atribuído o nome de pesquisa. Os nossos parâmetros preço de venda e custo são introduzidos nas células C4:C6.
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 =ALEATÓRIO(). Tal como descrito anteriormente, simula a procura do card na célula C3 com a fórmula PROCV(aleatório,proc,2). (Na fórmula PROCV, rand é o nome da célula atribuído à célula C3 e não a função ALEATÓRIO.)
O número de unidades vendidas é o menor da nossa quantidade de produção e demanda. Na célula C8, calcula a nossa receita utilizando a fórmula MÍN(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 procurados, o número de unidades que sobram é igual à produção menos a 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*SE(procura produzida>;produção–procura;0). Finalmente, na célula C11, calculamos o 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. Esta é uma situação em que uma tabela de dados bidirecional vem em nosso socorro. (Consulte o Capítulo 15, "Análise de sensibilidade 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, introduza os números de 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 separador Base no grupo Edição , clique em Preenchimentoe selecione Série para apresentar a caixa de diálogo Série . Na caixa de diálogo Série , mostrada na Figura 60-6, insira um valor de etapa de 1 e um valor de parada de 1000. Na área Série em, selecione a opção Colunas e clique em OK. Os números de 1 a 1000 serão introduzidos na coluna A a partir da célula A16.
Em seguida, introduzimos as nossas quantidades de produção possíveis (10.000, 20.000, 40.000, 60.000) nas células B15:E15. Queremos calcular o lucro para cada número experimental (1 a 1000) e cada quantidade de produção. Referimo-nos à fórmula para lucro (calculada na célula C11) na célula superior esquerda da nossa tabela de dados (A15) ao introduzir =C11.
Agora estamos prontos para enganar o Excel para simular 1000 iterações de demanda para cada quantidade de produção. Selecione o intervalo da tabela (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 a Célula de Entrada de Linha e selecione uma 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 ordem.
Para compreender porque 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 vai 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. 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 é novamente calculado. O lucro correspondente é introduzido na célula C17.
Copiando da célula B13 para C13:E13 a fórmula MÉDIA(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 DESVPAD(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 demanda são simuladas para cada quantidade de ordem. Produzir 40.000 cartões sempre rende 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, 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.
Observação
Neste livro, a opção Cálculo está definida como Automático, exceto para tabelas. (Utilize o comando Cálculo no grupo Cálculo no separador Fórmulas.) Esta definição garante que a nossa tabela de dados não será recalculada a menos que primamos F9, o que é uma boa ideia porque uma tabela de dados grande irá tornar o seu trabalho mais lento se voltar a calcular sempre que escrever algo na sua folha de cálculo. Tenha em atenção que, neste exemplo, sempre que premir F9, o lucro médio será alterado. Isto acontece porque cada vez que prime F9, é utilizada uma sequência diferente de 1000 números aleatórios para gerar pedidos para cada quantidade de encomenda.
Intervalo de confiança para o lucro médio Uma pergunta natural a fazer nesta situação é: em que intervalo temos 95% de certeza de que o lucro médio real cairá? Este intervalo chama-se intervalo de confiança de 95 por cento para o lucro médio. Um intervalo de confiança de 95 por cento para a média de qualquer saída de simulação é calculado pela seguinte fórmula:
Na célula J11, calcula o limite inferior do intervalo de confiança de 95 por cento sobre o lucro médio quando são produzidos 40 000 calendários com a fórmula D13–1,96*D14/RAIZQ(1000). Na célula J12, calcula o limite superior do 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 nosso lucro médio quando 40.000 calendários são encomendados é entre US$ 56.687 e US$ 62.589.
Problemas
Um distribuidor da GMC acredita que a procura de enviados para 2005 será normalmente distribuída com uma média de 200 e desvio-padrão de 30. Seu custo para receber um enviado é de US $ 25.000, e ele vende um enviado por US $ 40.000. Metade de todos os Envoys não vendidos a preço integral pode ser vendida por US $ 30.000. Ele está pensando em encomendar 200, 220, 240, 260, 280 ou 300 enviados. Quantos ele deve encomendar?
Um pequeno supermercado está a tentar determinar quantos exemplares de People revista devem encomendar por semana. Eles acreditam que sua demanda por People é 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 O supermercado paga US $ 1,00 por cada cópia de People e vende-o por US $ 1,95. Cada cópia não vendida pode ser devolvida por $0,50. Quantas cópias de People a loja deve encomendar?
Precisa de mais ajuda?
Pode sempre colocar uma pergunta a um especialista da Comunidade Tecnológica do Excel ou obter suporte nas Comunidades.