Este artigo foi adaptado do Microsoft Excel Data Analysis and Business Modeling por Wayne L. Winston.
Descrição geral
- Quem usa a simulação de Monte Carlo?
- O que acontece quando escreve =ALEATÓRIO() numa célula?
- Como simular valores de uma variável aleatória discreta?
- Como simular valores de uma variável aleatória normal?
- Como uma empresa de cartões de felicitações 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 atual líquido (VAL) positivo? Qual é o fator de risco da nossa carteira de investimentos? A simulação de Monte Carlo permite-nos modelar situações que apresentam incerteza e depois jogá-las num computador milhares de vezes.
Nota
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 funcionaria com sucesso. Os físicos envolvidos neste trabalho eram grandes fãs de jogos de azar, então eles 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 executar 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 do seu processo de tomada de decisão. Eis alguns exemplos.
- General Motors, Proctor and Gamble, Pfizer, Bristol-Myers Squibb e Eli Lilly usam a 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 prever o lucro líquido da empresa, prever custos estruturais e de compra e determinar sua suscetibilidade a diferentes tipos de risco (como mudanças na taxa de juros e flutuações da taxa de câmbio).
- Lilly usa simulação para determinar a capacidade ideal da planta para cada droga.
- A Proctor and Gamble utiliza a simulação para modelar e proteger de forma otimizada o risco cambial.
- A Sears usa a 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 encomendados 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, 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.
O que acontece quando escreve =ALEATÓRIO() numa célula?
Ao escrever a fórmula =ALEATÓRIO() numa célula, obtém um número que tem 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; Cerca de 10 por cento das vezes 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 do arquivo, mostrado na Figura 60-1.
Nota
Ao abrir o arquivo Randdemo.xlsx, você não verá os mesmos números aleatórios mostrados na Figura 60-1. A função ALEATÓRIO recalcula sempre automaticamente os números gerados quando uma folha de cálculo é aberta ou quando são introduzidas novas informações na folha de cálculo.
Em primeiro lugar, copie da célula C3 para C4:C402 a fórmula =ALEATÓRIO(). Em seguida, atribua o nome ao intervalo C3:C402 Dados. Em seguida, na coluna F, pode controlar a média de 400 números aleatórios (célula F2) e utilizar a função CONTAR.SE para determinar as frações que se situam entre 0 e 0,25, 0,25 e 0,50, 0,50 e 0,75 e 0,75 e 0,75 e 1. Quando prime 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. 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 pela função 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.
Como simular valores de uma variável aleatória discreta?
Suponhamos 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 reproduza, ou simule, essa demanda 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 atribuição a seguir garante que uma demanda 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 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, observe a Discretesim.xlsx do arquivo, mostrada na Figura 60-2 na página seguinte.
A chave para a nossa simulação é utilizar um número aleatório para iniciar uma pesquisa a partir do intervalo da tabela F2:G5 (pesquisa denominada). 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 produzirão uma demanda de 60.000. Para gerar 400 números aleatórios, copie de C3 para C4:C402 a fórmula RAND(). Em seguida, gera 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 assegura 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 sucessivamente. No intervalo de células F8:F11, utilize a função CONTAR.SE para determinar a fração das 400 iterações que resultam em cada procura. Quando pressionamos F9 para recalcular os números aleatórios, as probabilidades simuladas estão próximas das nossas probabilidades de demanda assumidas.
Como simular valores de uma variável aleatória normal?
Se escrever em qualquer célula a fórmula INV.NORM(rand(),mu,sigma), irá 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 na Normalsim.xlsx do arquivo, mostrada 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 os nomes média e sigma a estas células, respetivamente.) 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 experimentais 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 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 pésimo 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 (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.
Como uma empresa de cartões de felicitações 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. Suponhamos que a procura de um cartão 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 é vendido por US $ 4,00, e o custo variável de produzir cada cartão é de US $ 1,50. Os cartões que sobrarem 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 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 cartão 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.
Nota
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.