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

Nota:  Queremos fornecer-lhe os conteúdos de ajuda mais recentes o mais rapidamente possível e no seu idioma. Esta página foi traduzida automaticamente e pode conter erros gramaticais ou imprecisões. O nosso objetivo é que estes conteúdos lhe sejam úteis. Pode informar-nos se as informações foram úteis no final desta página? Eis o artigo em inglês para referência.

Este artigo foi adaptado de análise de dados do Microsoft Excel e empresas de modelação por Winston L. Winston.

  • Simulação Monte Carlo quem utiliza?

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

  • Como pode simular valores de uma variável aleatória descontínuos?

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

  • Como é que uma empresa de cartão de saudação pode determinar quantas cartões para produzir?

Gostaríamos com exatidão estimar as probabilidades de eventos certo. Por exemplo, o que é a probabilidade de fluxos monetários um novo produto que será um positivo valor atual líquido (VAL)? O que é o fator de risco de portefólio nosso investimento? Simulação Monte Carlo permite-na situações de modelo que apresentam incerteza e reproduzi-los num computador milhares de vezes.

Nota: O nome simulação Monte Carlo provém as simulações computador executadas durante década de 1930 e 1940s para estimar a probabilidade de que a resposta de cadeia necessária para um bomb atom detonar funcionarão com êxito. Os physicists envolvidos deste trabalho estavam ventoinhas grandes de jogo, por isso que atribuiu as simulações o nome de código Monte Carlo.

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

Muitas empresas utilizam simulação Monte Carlo como uma parte do processo de decisão importante. Aqui estão alguns exemplos.

  • Motores geral, Proctor e jogo, Pfizer, Bristol Martins Squibb e Eli Lilly utilizam simulação para estimar o retorno média e fator de risco de novos produtos. Em GM, estas informações são utilizadas pelo CEO para determinar quais os produtos entram no mercado.

  • GM utiliza simulação para atividades como previsão receita líquida para o corporation, as previsões custos de compras e estruturais e para determinar o seu sensibilidade para diferentes tipos de risco (como alterações de taxa de juro e taxa).

  • Lilly utiliza simulação para determinar a capacidade de produção ideal para cada contra.

  • Proctor e jogo utiliza simulação para modelar e optimizada cobrir o risco de moeda estrangeira.

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

  • Empresas Adamastor e contra utilizam simulação ao valor "opções reais", tal como o valor de uma opção para expandir, contrato ou adiar um projeto.

  • Financeiras planificadores utilizam simulação Monte Carlo para determinar estratégias de investimento ideal para a reforma os seus clientes.

Quando escrever a fórmula = rand () numa célula, receberá um número que quatros partem do pressuposto de qualquer valor entre 0 e 1. Assim sendo, cerca de 25% das vezes, deve obter um número menor ou igual a 0,25; 10% das vezes que deve obter um número que existe solução 0.90, pelo menos e assim sucessivamente. Para demonstrar como a função RAND funciona, consulte o artigo o ficheiro Randdemo.xlsx, apresentado na figura 60-1.

Imagem do Livro
Figura 60-1 demonstrar a função RAND

Nota: Quando abre o ficheiro Randdemo.xlsx, não verá os mesmos números aleatórios apresentados na figura 60-1. Função aleatório sempre volta a calcular automaticamente os números gera quando for aberta numa folha de cálculo ou quando novas informações são introduzidas a folha de cálculo.

Em primeiro lugar, copie da célula C3 para C4:C402 a fórmula = rand (). Em seguida, pode o nome do intervalo C3:C402 dados. Em seguida, na coluna F, pode controlar a média dos números aleatórios 400 (célula F2) e utilize a função contar.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 prime a tecla F9, os números aleatórios são recalculados. Aviso de que a média dos 400 números é sempre aproximadamente 0,5 e que cerca 25 por cento os resultados são em intervalos de 0,25. Estes resultados são compatíveis com a definição de um número aleatório. Tenha em atenção 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 grande número (por exemplo, 0,99), -informa-nos nada sobre os valores dos outros números aleatórios gerados.

Suponha que a procura de um calendário é regida pela variável aleatória descontínuos seguinte:

Pedido

Probabilidade

10 000

0,10

20 000

0.35

40.000

0,3

60 000

0,25

Como pode temos Excel reproduzir ou simular, este pedido de calendários muitas vezes? O truque consiste em associar a cada valor da função aleatório possível um pedido possível para calendários. A atribuição seguinte assegura que um pedido de 10.000 irá ocorrer 10% das vezes etc.

Pedido

Número aleatório atribuído

10 000

Menor que 0,10

20 000

Maior ou igual a 0,10 e inferiores a 0.45

40.000

Maior ou igual a 0.45 e inferiores 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.

Imagem do Livro
Figura 60-2, simulação de uma variável aleatória descontínuos

A chave para os nossos simulação está a utilizar um número aleatório para iniciar uma pesquisa a partir do intervalo da tabela F2:G5 (denominada pesquisa). Números aleatórios maiores ou iguais a 0 e menor do que 0,10 vai originar um pedido de 10.000; números aleatórios maiores ou iguais a 0,10 e menor do que 0.45 vai originar um pedido de 20.000; números aleatórios maiores ou iguais a 0.45 e menor do que 0,75 vai originar um pedido de 40.000; e números aleatórios maiores ou iguais a 0,75 vai originar um pedido de 60.000. Gerar números aleatórios 400 ao copiá-do C3 C4:C402 a fórmula rand (). Em seguida, gerar 400 tentativas ou iterações, de pedido de calendário ao copiá-do B3 B4:B402 a fórmula VLOOKUP(C3,lookup,2). Esta fórmula assegura que qualquer número aleatório menor que 0,10 gera um pedido de 10.000, qualquer número aleatório entre 0,10 e 0.45 gera um pedido de 20.000 e assim sucessivamente. Intervalo de células F8:F11, utilize a função contar.se para determinar a fração do nossas 400 iterações que produzam cada pedido. Quando é premida a tecla F9 para recalcular os números aleatórios, as probabilidades simuladas são perto nossas probabilidades assumido pedido.

Se escrever a fórmula NORMINV(rand(),mu,sigma)em qualquer célula, irá gerar um valor de uma variável aleatória normal ter um média mu e o desvio-padrão sigmasimulado. Este procedimento é ilustrado no ficheiro Normalsim.xlsx, apresentado na figura 60-3.

Imagem do Livro
Figura 60-3, simulação de 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 40.000 e um desvio-padrão de 10.000. (Pode escreva estes valores nas células E1 e E2 e atribua um nome estas células vermelha e sigma, 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,mean,sigma) gera 400 diferentes valores de avaliação a partir de uma variável aleatória normal com uma média 40.000 e um desvio-padrão de 10.000. Quando é premir a tecla F9 para recalcular os números aleatórios, a média permanece perto 40.000 e o desvio-padrão perto 10.000.

Basicamente, 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 40.000 e um desvio-padrão de 10.000.

Nesta secção, irá ver como 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 variável aleatória descontínuos seguinte:

Pedido

Probabilidade

10 000

0,10

20 000

0.35

40.000

0,3

60 000

0,25

O cartão de saudação vende para $4.00, e o custo variável de cada cartão é $1.50. Cartões que permaneçam devem ser eliminados um custo de 0,20 $ por cartão. Cartões de quantas devem ser impresso?

Basicamente, podemos simular cada quantidade de produção possíveis (10.000, 20.000, 40.000 ou 60.000) número de vezes (por exemplo, 1000 iterações). Em seguida, podemos determinar que quantidade de encomenda produz o lucro médio máximo sobre as iterações 1000. Pode encontrar os dados para esta secção no ficheiro Valentine.xlsx, apresentado na figura 60-4. Atribuir nomes de intervalo na B1:B11 de células para C1:C11 de células. O intervalo de células G3:H6 é atribuído o nome de pesquisa. Os nossos preço de venda e o custo parâmetros são introduzidos no C4: C6 de células.

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

Pode introduzir 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, simular pedido para o cartão na célula C3 com a fórmula VLOOKUP(rand,lookup,2). (Na fórmula PROCV, aleatório é o nome de célula atribuído a célula C3, não a função RAND.)

O número de unidades vendidas é o valor mais pequeno dos nossos quantidade de produção e o pedido. Na célula C8, calcular os nossos receita com a fórmula MIN (procura produzida,) * unit_price. Na célula C9, pode calcular o custo total de produção com a fórmula produzidos * unit_prod_cost.

Se podemos produzir mais cartões que são no pedido, o número de unidades deixados é igual a produção menos procura; caso contrário não unidades são deixadas. Vamos calcular os nossos custo de eliminação na célula C10 com a fórmula unit_disp_cost * IF (produzidos > pedido, produzidos – pedido, 0). Por fim, na célula C11, podemos calcular os nossos lucro como Receita – total_var_cost-total_disposing_cost.

Gostaríamos de uma forma eficiente de premir F9 muitas vezes (por exemplo, 1000) para cada quantidade de produção e concordância nosso lucro esperado para cada quantidade. Esta situação é um na qual a tabela de dados bidirecional chega ao nosso salvá-los. (Consulte capítulo 15, "Sensibilidade análise com tabelas de dados," para obter detalhes sobre tabelas de dados). A tabela de dados utilizada neste exemplo é apresentada na figura 60-5.

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

Intervalo de células A16:A1015, introduza os números 1 – 1000 (correspondente ao nossas tentativas de 1000). Uma forma fácil de criar estes valores é começa por introduzir 1 na célula A16. Selecione a célula e, em seguida, no separador base no grupo edição, clique em Preenchimento e selecione a série para apresentar a caixa de diálogo da série. Na caixa de diálogo da série, apresentada na figura 60-6, introduza um valor passo 1 e um valor parar de 1000. Na área Série no, selecione a opção de colunas e, em seguida, clique em OK. Os números 1 – 1000 serão introduzidas na coluna um começando na célula A16.

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

Em seguida, podemos introduza nossas quantidades de produção possíveis (10.000, 20.000, 40.000, 60.000) na B15:E15 de células. Queremos calcular a margem para cada número de avaliação (1 a 1000) e cada quantidade de produção. Vamos fazer referência a fórmula para extrair o lucro (calculado na célula C11) na célula do canto superior esquerdo da nossa tabela de dados (R15) ao introduzir = C11.

Vamos está agora prontos para truque Excel para simular 1000 iterações da procura para cada quantidade de produção. Selecione o intervalo de tabela (A15:E1014) e, em seguida, no grupo Ferramentas de dados no separador dados, clique em análise o que se e, em seguida, selecione a tabela de dados. Para configurar uma tabela de dados bidirecional, selecione os nossos quantidade de produção (célula C1) que a célula de entrada da linha e selecione qualquer célula em branco (optamos por célula I14) como célula de entrada da coluna. Depois de clicar em OK, o Excel simula valores de pedido de 1000 para cada quantidade da encomenda.

Para compreender porque é que 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 irá utilizar um valor de 20.000 na célula C1. No C16, o valor de célula de entrada da coluna de 1 é colocado na célula em branco e o número aleatório na célula que C2 volta a calcular. O lucro correspondente, em seguida, é registado na célula C16. Em seguida, o valor de entrada de célula de coluna de 2 é colocado na célula em branco e o número aleatório em C2 volta a calcular novamente. O lucro correspondente é introduzido na célula C17.

Copiando da célula B13 para C13:E13 a fórmula AVERAGE(B16:B1015), podemos calcular lucro médio simulado para cada quantidade de produção. Copiando da célula B14 para C14:E14 a fórmula STDEV(B16:B1015), podemos calcular o desvio padrão da nossa simulados lucros de cada quantidade da encomenda. Sempre que recomendamos prima F9, são simuladas 1000 iterações da procura para cada quantidade da encomenda. Que produza 40.000 cartões sempre rendimentos o maior valor da margem esperado. Por conseguinte, parece que que produza 40.000 cartões é a decisão inicial maiúscula.

O impacto das risco no nosso decisão     Se podemos produzidos 20.000 em vez de 40.000 cartões, nosso lucro esperado desce aproximadamente 22 por cento, mas os nossos risco (medido pelo desvio-padrão de lucro) desce quase 73 por cento. Por conseguinte, caso estamos extremamente antecedem risco, que produza 20.000 cartões poderá estar a decisão à direita. Além disso, que produza 10.000 cartões sempre tem um desvio padrão de 0 cartões porque se podemos produzir 10.000 cartões, será sempre vendemos mesmas sem qualquer sobras.

Nota: Neste livro, a opção de cálculo está definida para Automático exceto para tabelas. (Utilize o comando de cálculo, no grupo cálculos no separador fórmulas). Esta definição assegura que os nossos tabela de dados não serão recalcular a menos que recomendamos prima F9, que é uma boa ideia porque uma tabela de dados grandes desacelerar seu trabalho se volta a calculá-la sempre que escrever algo na sua folha de cálculo. Tenha em atenção que neste exemplo, sempre que prime F9, o lucro médio irá alterar. Isto acontece porque sempre que prime F9, uma sequência diferentes de números aleatórios 1000 é utilizada para gerar pedidos de cada quantidade da encomenda.

Intervalo de confiança para vermelha lucro     É uma pergunta natural a colocar esta situação, para que intervalo estamos 95 por cento-se de que o lucro de médio verdadeiro cair? Este intervalo chama-se o intervalo de confiança de 95 por cento para 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 do Livro

Na célula J11, calcular o limite inferior para o intervalo de confiança de 95 por cento na margem média quando são produzidos 40.000 calendários com a fórmula D13–1.96*D14/SQRT(1000). Na célula J12, calcular o limite superior para os nossos intervalo de confiança de 95 por cento com a fórmula D13+1.96*D14/SQRT(1000). Estes cálculos são apresentados na figura 60-7.

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

Estamos a percentagem de 95 certificar de que o nosso lucro médio quando são ordenados 40.000 calendários está entre $56,687 e $62,589.

  1. Um revendedor GMC acredita que procura para 2005 enviados será distribuída normalmente com uma média de 200 e o desvio-padrão de 30. Dele custo de receber um Envoy for $25.000 e vende uma Envoy para 40.000. Metade de todos os enviados não vendidos pelo preço completo pode ser vendido por 30.000. Ele está a considerar corrigida 200, 220, 240, 260, 280 ou 300 enviados. Quantos deve ele encomendar?

  2. Um pequeno supermercado está a tentar determinar quantas cópias das pessoas revistas deve ordem de cada semana. Estes consideram o que seu pedido para as pessoas é regulado pela variável aleatória descontínuos seguinte:

    Pedido

    Probabilidade

    15

    0,10

    20

    0,20

    25

    0,30

    30

    0,25

    35

    0,15

  3. Supermercado paga $1.00 para cada cópia de pessoas e vende-lo para $1.95. Cada cópia não vendida pode ser devolvida para $0,50. Quantas cópias das pessoas deve o arquivo de ordem?

Precisa de mais ajuda?

Pode sempre perguntar a um especialista na Comunidade Tecnológica do Excel, obter suporte na Comunidade de Respostas ou sugerir uma nova funcionalidade ou melhoria no User Voice do Excel.

Aumente os seus conhecimentos do Office
Explore as formações
Seja o primeiro a obter novas funcionalidades
Adira ao Office Insider

As informações foram úteis?

Obrigado pelos seus comentários!

Obrigado pelo seu feedback! Parece que poderá ser benéfico reencaminhá-lo para um dos nossos agentes de suporte do Office.

×