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

Observação:  Desejamos fornecer o conteúdo da Ajuda mais recente no seu idioma da forma mais rápida possível. Esta página foi traduzida de forma automatizada e pode conter imprecisões ou erros gramaticais. Nosso objetivo é que este conteúdo seja útil para você. As informações foram úteis? Dê sua opinião no final desta página. Aqui está o artigo em inglês para facilitar a referência.

Este artigo foi adaptado de Microsoft Excel Data Analysis and Business Modeling por Winston.

  • Quem utiliza 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 mensagem pode determinar quantas cartas para produzir?

Queremos precisamente estimar as probabilidades de eventos indefinidos. Por exemplo, o que é a probabilidade que fluxos de caixa de um novo produto terá um valor de presente líquido positivo (NPV)? O que é o fator de risco do nosso portfólio de investimento? Simulação de Monte Carlo nos permite às situações de modelo que apresentam incerteza e reproduzi-los check-out em um computador milhares de vezes.

Observação: O nome simulação de Monte Carlo é proveniente de simulações computador realizadas durante 1930 e 1940s para estimar a probabilidade de que a reação de corrente exigia uma bomba atom para detonate funcionaria com êxito. Os physicists envolvidas nesse trabalho foram ventiladores grandes de jogos, para que eles deu simulações o nome de código Monte Carlo.

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

Muitas empresas usam 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 e aposta, Pfizer, Bristol Martins Squibb e Eli Lilly usam simulação para estimar o retorno médio e o fator de risco de novos produtos. Em GM, essas informações são usadas pelo CEO para determinar quais produtos vêm no mercado.

  • GM usa simulação para atividades como receita líquida de previsão para a empresa, prever custos estruturais e compras e determinar seu susceptibilidade para diferentes tipos de risco (como alterações de taxa de juros e flutuações da taxa do exchange).

  • Lilly usa simulação para determinar a capacidade de fábrica ideal para cada medicamento.

  • Proctor e aposta usa simulação modelar e ideal restringir o risco de intercâmbio.

  • Sears usa simulação para determinar quantas unidades de cada linha de produto devem ser ordenadas de fornecedores — por exemplo, o número de pares de trousers Dockers que devem ser ordenadas este ano.

  • Empresas de petróleo e medicamentos usam simulação ao valor "opções reais", como o valor de uma opção para expandir, contrato ou adiar um projeto.

  • Planejadores financeiros usam simulação de Monte Carlo para determinar estratégias de investimento ideal para aposentadoria de seus clientes.

Quando você digita a fórmula = rand () em uma célula, você obtém um número que é igualmente provável presumem qualquer valor entre 0 e 1. Portanto, em torno de 25% do tempo, você deve obter um número menor ou igual ao 0,25; em torno 10 por cento das vezes, que 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 arquivo Randdemo.xlsx, mostrado na Figura 60-1.

Imagem de Livro
Figura demonstrando função aleatório de 60-1

Observação: Quando você abre o arquivo Randdemo.xlsx, você não verá os mesmos números aleatórios mostrados na Figura 60-1. A função RAND recalcula sempre automaticamente os números que ele gera quando uma planilha for aberta ou quando novas informações são inseridas na planilha.

Primeiro, copie da célula C3 para C4:C402 a fórmula = rand (). Você nomeie o intervalo de C3:C402 dados. Em seguida, na coluna F, você pode controlar a média dos números aleatórios 400 (célula F2) e use 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 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, que em torno de 25% dos resultados sã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 aleatório em células diferentes são independentes. Por exemplo, se o número aleatório gerado na célula C3 é um número grande (por exemplo, 0,99), ele informa nada sobre os valores dos outros números aleatórios gerados.

Suponha que a demanda por um calendário é regulamentada pelos seguinte variável aleatória distinta:

Demanda

Probabilidade

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

Como podemos ter Excel reproduzir check-out ou simular, essa demanda para calendários muitas vezes? O truque é associar cada valor possível da função aleatório uma possível demanda para calendários. Atribuição a seguir garante que uma demanda de 10.000 ocorrerá 10 por cento do tempo e assim por diante.

Demanda

Número aleatório atribuído

10.000

Menor que 0,10

20.000

Maior que ou igual a 0,10 e menor que 0,45

40.000

Maior que ou igual a 0,45 e menor que 0,75

60.000

Maior que ou igual a 0,75

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

Imagem de Livro
Figura simular uma variável aleatória discreta de 60-2

A chave para nossa simulação é usar um número aleatório para iniciar uma pesquisa do intervalo tabela F2:G5 (chamado de pesquisa). Números aleatórios maior que ou iguais a 0 e menor que 0,10 produzirá uma demanda de 10.000; números aleatórios maior que ou iguais a 0,10 e menor que 0,45 produzirá uma demanda de 20.000; números aleatórios maior que ou iguais a 0,45 e menor que 0,75 produzirá uma demanda de 40.000; e números aleatórios maiores que ou iguais a 0,75 produzirá uma demanda de 60.000. Você pode gerar números aleatórios 400 copiando de C3 para C4:C402 a fórmula aleatório (). Você gera 400 tentativas ou iterações, de demanda de calendário copiando de B3 para B4:B402 a fórmula VLOOKUP(C3,lookup,2). Essa fórmula garante que qualquer número aleatório menor que 0,10 gera 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 CONT.se para determinar a fração de nossas 400 iterações gerando cada demanda. Quando podemos pressionar F9 para recalcular os números aleatórios, as probabilidades simuladas são próximo nossas probabilidades demanda assumida.

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

Imagem de Livro
Figura 60-3 simular uma variável aleatória normal

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 nomeie essas células significam e sigma, respectivamente.) Copiar a fórmula = rand () de C4 para C5:C403 gera 400 diferentes números aleatórios. Copiando de B4 para B5:B403 a fórmula NORMINV(C4,mean,sigma) gera 400 valores diferentes de avaliação de uma variável aleatória normal com uma média de 40.000 e um desvio padrão de 10.000. Quando podemos pressionar a tecla F9 para recalcular os números aleatórios, a média permanece próximo 40.000 e o desvio padrão da próxima 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 Figura 60-3) gera na célula B4 aproximadamente o 77th 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 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 é regulamentada pelos seguinte variável aleatória distinta:

Demanda

Probabilidade

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

Cartão vende para US $4,00, e o custo de variável de produção cada cartão é 1,50. Cartões de abandonados devem ser descartados da um custo de US $0,20 por cartão. Quantos cartões devem ser impressas?

Basicamente, podemos simular cada quantidade de produção possíveis (10.000, 20.000, 40.000 ou 60.000) muitas vezes (por exemplo, iterações de 1000). Podemos determinar que quantidade de ordem produz o lucro médio máximo sobre as iterações de 1000. Você pode encontrar os dados para esta seção no arquivo Valentine.xlsx, mostrado na Figura 60-4. Atribua nomes de intervalo em células B1:B11 ao C1:C11 de células. O intervalo de células G3:H6 é atribuído a pesquisade nomes. Nossos parâmetros de custo e preço de venda são inseridos em células C4: C6.

Imagem de Livro
Simulação de cartão de dia dos Namorados Figura 60-4

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

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

Se podemos produzir mais cartas que estão em demanda, o número de unidades esquerda ao longo de produção for igual ao menos demanda; Caso contrário, nenhum unidades são mantidas. Podemos calcular nosso custo de descarte na célula C10 com a fórmula unit_disp_cost * IF (produzidas > demand, produzidas – demanda, 0). Por fim, na célula C11, podemos calcular nosso lucro como receita – total_var_cost-total_disposing_cost.

Podemos gostaria de uma maneira eficiente de pressionar F9 muitas vezes (por exemplo, 1000) para cada quantidade de produção e tabular nosso lucro esperado para cada quantidade. Essa situação é um em que uma tabela de dados bidirecional vem nosso salvação. (Consulte Capítulo 15, "Sensibilidade análise com tabelas de dados," para obter detalhes sobre tabelas de dados.) A tabela de dados usada neste exemplo é mostrada na Figura 60-5.

Imagem de Livro
Tabela de dados bidirecional de figura 60-5 para simulação de cartão de mensagem

No intervalo de células A16:A1015, insira os números 1 – 1000 (correspondente à nossas tentativas de 1000). Uma maneira fácil de criar esses valores é começar digitando 1 na célula A16. Selecione a célula e, em seguida, na guia início no grupo edição, clique em preenchimento e selecione a série para exibir a caixa de diálogo de série. Na caixa de diálogo série, mostrada na Figura 60-6, insira um valor de etapa de 1 e um valor parar de 1000. Na área de Série em, selecione a opção de colunas e clique em Okey. Os números 1 – 1000 serão inseridos na coluna um começando na célula A16.

Imagem de Livro
Figura 60-6 usando a caixa de diálogo de série para preencher os números de avaliação 1 a 1000

Avançar podemos inserir nossas quantidades de produção possíveis (10.000, 20.000, 40.000, 60.000) em B15:E15 de células. Queremos calcular o lucro para cada número de avaliação (1 a 1000) e cada quantidade de produção. Nós consulte a fórmula de lucro (calculado na célula C11) na célula superior esquerda da nossa tabela de dados (A15) por meio de = C11.

Podemos agora está prontos para truque Excel para simular iterações de 1000 de demanda para cada quantidade de produção. Selecione o intervalo de tabela (A15:E1014) e, no grupo Ferramentas de dados na guia dados, clique em quais hipóteses e, em seguida, selecione a 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. Após clicar em Okey, o Excel simula valores de demanda de 1000 para a quantidade de cada pedido.

Para compreender por que isso funciona, considere os valores colocados por 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. C16, o valor de célula de entrada da coluna 1 é colocado em uma célula em branco e o número aleatório na célula que C2 recalcula. O lucro correspondente é registrado, em seguida, na célula C16. Em seguida, o valor de entrada de célula de coluna de 2 é colocado em uma célula em branco e o número aleatório em C2 recalcula novamente. O lucro correspondente é inserido na célula C17.

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

O impacto do risco em nossa decisão     Se nós produzidas 20.000 em vez de 40.000 cartões, nosso lucro esperado cai aproximadamente 22%, mas nosso risco (como medido pelo desvio padrão de lucro) descarta quase 73%. Portanto, se houver muito avessas a riscos, produzir 20.000 cartões pode ser a decisão à direita. Aliás, produzir 10.000 cartões sempre tem um desvio padrão de 0 placas porque se podemos produzir 10.000 cartões, será sempre vendemos todos eles sem qualquer leftovers.

Observação: Nesta pasta de trabalho, a opção de cálculo está definida como Automático, exceto para tabelas. (Use o comando de cálculo no grupo cálculo, na guia fórmulas.) Esta configuração garante que nossa tabela de dados não são recalculadas, a menos que estamos pressionar F9, que é uma boa ideia porque uma tabela de dados grande lento seu trabalho se ele é recalculada sempre que você digita algo na sua planilha. Observe que neste exemplo, sempre que você pressionar F9, o lucro médio será alterado. Isso acontece porque cada vez que você pressionar F9, uma sequência diferente de números aleatórios de 1000 é usada para gerar demanda para cada quantidade de pedido.

Intervalo de confiança para significam lucro     Uma pergunta natural pedir nessa situação é, em qual intervalo estamos 95 por cento se o lucro de média true irá se situar? Esse intervalo é chamado de intervalo de confiança de 95% de lucro médio. Um intervalo de confiança de 95 por cento para a média de qualquer saída simulação é calculado pela seguinte fórmula:

Imagem de Livro

Na célula J11, você calcular 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ê calcular 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.

Imagem de Livro
Intervalo de confiança de 95% de figura 60-7 para médio lucro quando 40.000 calendários são ordenados

Estamos 95 por cento se nosso lucro médio quando 40.000 calendários são ordenados está entre r $56,687 e $62,589.

  1. Um revendedor GMC acredita que demanda por 2005 Envoys serão normalmente distribuída com uma média de 200 e o desvio padrão de 30. Seu custo de receber um Envoy é r $25.000, e ele vende um Envoy para US $40.000. Metade de todas as Envoys não vendidos pelo preço total pode ser vendida por $30.000. Ele está considerando a ordenação 200, 220, 240, 260, 280 ou 300 Envoys. Quantos deve ele pedido?

  2. Um pequeno supermercado está tentando determinar quantas cópias das pessoas revista deve solicitar cada semana. Eles acreditar que sua demanda para as pessoas é regulamentada pelos seguinte variável aleatória distinta:

    Demanda

    Probabilidade

    15

    0,10

    20

    0,20

    25

    0,30

    30

    0,25

    35

    0,15

  3. O supermercado paga US $1,00 para cada cópia de pessoas e vende-lo para $1.95. Cada cópia não vendida pode ser retornada por $0,50. Quantas cópias de pessoas deve loja do pedido?

Precisa de mais ajuda?

Você pode sempre consultar um especialista na Excel Tech Community, obter suporte na Comunidade de respostas ou sugerir um novo recurso ou melhoria no User Voice do Excel.

Expanda suas habilidades no Office
Explore o treinamento
Obtenha novos recursos primeiro
Ingressar no Office Insider

Essas informações foram úteis?

Obrigado por seus comentários!

Agradecemos pelos seus comentários! Parece que pode ser útil conectar você a um de nossos agentes de suporte do Office.

×