Introdução à simulação Davi 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ê. No final da página, deixe sua opinião se estas informações foram úteis para você. Aqui está o artigo em inglês para facilitar a referência.

Este artigo foi adaptado da modelagem de dados e análise de negócios do Microsoft Excel por Wayne L. Winston.

  • Quem usa a simulação do Carlo Davi?

  • O que acontece quando você digita = aleatório () 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 quantos cartões deseja produzir?

Gostaríamos de estimar precisamente as probabilidades de eventos incertos. Por exemplo, qual é a probabilidade de o fluxo de caixa de um novo produto ter um valor presente líquido (NPV) positivo? Qual é o fator de risco de nosso portfólio de investimentos? A simulação do Carlo Davi nos permite modelar situações que apresentam incerteza e depois reproduzi-las em um computador com milhares de vezes.

Observação:  O nome Davi Carlo simulação vem das simulações de computador executadas durante a 1930s e 1940s para estimar a probabilidade de que a reação da cadeia necessária para um bomba Atom para detonate funcione com sucesso. Os Physicists envolvidos nesse trabalho foram grandes fãs de jogos de azar, portanto, forneciam que os simuladores Davio codinome Carlo.

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

Muitas empresas usam a simulação do Carlo Davi como parte importante do processo de tomada de decisões. Aqui estão alguns exemplos.

  • Motores gerais, Proctor e jogar, Pfizer, Bristol-Myers Squibb e Eli Lilly usam simulação para estimar a média de retorno e o fator de risco de novos produtos. Na GM, essas informações são usadas pelo CEO para determinar quais produtos vêm ao mercado.

  • A GM usa simulação de atividades, como Previsão de receita líquida da empresa, prever custos estruturais e de compra e determinar sua susceptibility para tipos diferentes de risco (como alterações de taxa de juros e flutuações na taxa de câmbio).

  • O Lilly usa simulação para determinar a capacidade de planta ideal para cada medicamento.

  • Proctor e jogar usa a simulação para modelar e, de forma otimizada, o risco do Exchange estrangeiro.

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

  • As empresas de petróleo e farmácia usam a simulação de "opções reais", como o valor de uma opção para expandir, contratar ou adiar um projeto.

  • Os planejadores financeiros usam a simulação do Carlo Davi para determinar estratégias de investimento ideais para a aposentadoria dos seus clientes.

Quando você digita a fórmula = Rand () em uma célula, obtém um número que é igualmente presumindo qualquer valor entre 0 e 1. Portanto, cerca de 25% do tempo, você deve obter um número menor ou igual a 0,25; cerca de 10% do tempo você deve obter um número de pelo menos 0,90 e assim por diante. Para demonstrar como a função aleatório funciona, dê uma olhada no arquivo Randdemo. xlsx, mostrado na figura 60-1.

Imagem de Livro

Observação:  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 sempre recalcula automaticamente os números gerados quando uma planilha é aberta ou quando novas informações são inseridas na planilha.

Primeiro, copie da célula C3 para C4: C402 a fórmula = Rand (). Em seguida, nomeie o intervalo C3: C402 Data. Em seguida, na coluna F, você pode acompanhar a média dos números aleatórios do 400 (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 números do 400 é 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 diferentes células 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 informa nada sobre os valores dos outros números aleatórios gerados.

Suponha que a demanda de um calendário seja regida pela seguinte variável aleatória discreta:

Pedido

Probabilidade

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

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

Pedido

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 de demanda, examine o arquivo Discretesim. xlsx, mostrado na figura 60-2 na próxima página.

Imagem de Livro

A chave para a nossa simulação é usar um número aleatório para iniciar uma pesquisa a partir do intervalo de tabelas F2: G5 ( pesquisanomeada). Números aleatórios maiores que ou iguais a 0 e menores que 0,10 resultarão em uma demanda de 10.000; números aleatórios maiores que ou iguais a 0,10 e menores do que 0,45 resultarão em uma demanda de 20.000; números aleatórios maiores que ou iguais a 0,45 e menores do que 0,75 resultarão em uma demanda de 40.000; e números aleatórios maiores do que ou iguais a 0,75 resultarão em uma demanda de 60.000. Você gera números aleatórios do 400 copiando de C3 para C4: C402 a fórmula Rand (). Em seguida, você poderá gerar avaliações do 400 ou iterações de demanda do calendário copiando de B3 para B4: B402 a fórmula PROCV (C3, Lookup, 2). Essa fórmula garante que qualquer número aleatório menor que 0,10 gerará 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 iterações do 400, gerando cada demanda. Quando pressionamos F9 para recalcular os números aleatórios, as probabilidades simuladas estão próximas às nossas probabilidades de demanda presumidas.

Se você digitar em qualquer célula a fórmula inv (aleatório (), MU, Sigma), irá gerar um valor simulado de uma variável aleatória normal com um MU da média e o Sigmapadrão. Este procedimento é ilustrado no arquivo Normalsim. xlsx, mostrado na figura 60-3.

Imagem de Livro

Vamos supor que queremos simular avaliações de 400, 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 nomear essas células média e Sigma, respectivamente.) Copiar a fórmula = Rand () de C4 para C5: C403 gera 400 números aleatórios diferentes. Copiando de B4 para B5: B403 a fórmula inv (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 pressionarmos a tecla F9 para recalcular os números aleatórios, a média permanecerá próxima a 40.000 e o desvio padrão será mais próximo de 10.000.

Basicamente, para um número aleatório x, a fórmula inv (p, MU, Sigma) gera o pésimo percentil de uma variável aleatória normal com um MU -Mean e um Sigmapadrão. Por exemplo, o número aleatório 0,77 na célula C4 (veja a figura 60-3) gera na célula B4 aproximadamente o percentil 77th 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 a simulação de Carlo Davi pode ser usada como uma ferramenta de tomada de decisões. Suponha que a demanda do cartão de dia dos namorados seja regida pela seguinte variável aleatória discreta:

Pedido

Probabilidade

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

O cartão de mensagem vende $4, e o custo variável da produção de cada cartão é $1.50. Cartões sobrantes devem ser descartados a um custo de $0.20 por cartão. Quantos cartões devem ser impressos?

Basicamente, simulamos cada quantidade possível de produção (10.000, 20.000, 40.000 ou 60.000) muitas vezes (por exemplo, impossibilidades de impossibilidade de interação do 1000). Em seguida, determinamos qual quantidade do pedido produz o lucro médio máximo sobre as iterações do 1000. Você pode encontrar os dados para esta seção no arquivo namorado. xlsx, mostrados na figura 60-4. Você atribui os nomes de intervalo nas células B1: B11 às células C1: C11. O intervalo de células G3: H6 é atribuído à pesquisade nome. Nossos parâmetros de preço e custo de venda são inseridos nas células C4: C6.

Imagem de Livro

Você pode inserir 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, você simula a demanda do cartão na célula C3 com a fórmula PROCV (Rand, Lookup, 2). (Na fórmula PROCV, aleatório é o nome da célula atribuído à célula C3, não a função aleatório).

O número de unidades vendidas é menor do que a quantidade de produção e a demanda. Na célula C8, você calcula a receita com a fórmula mín (produzida, demanda) * unit_price. Na célula C9, você calcula o custo total de produção com a fórmula produzida * unit_prod_cost.

Se produzirmos mais cartões do que há por demanda, o número de unidades restantes é igual à produção de menos demanda; caso contrário, nenhuma unidade será deixada. Calculamos o custo da disposição na célula C10 com a fórmula unit_disp_cost * se (produced>demand, produzido – demanda, 0). Por fim, na célula C11, calculamos nosso lucro como receita – total_var_cost-total_disposing_cost.

Gostaríamos de ter uma maneira eficiente de pressionar F9 muitas vezes (por exemplo, 1000) para cada quantidade de produção e contar o lucro esperado para cada quantidade. Essa situação é aquela na qual uma tabela de dados bidirecional chega à nossa resgate. (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.

Imagem de Livro

No intervalo de células A16: A1015, insira os números 1 – 1000 (correspondentes a nossas avaliações de 1000). Uma maneira fácil de criar esses valores é começar inserindo 1 no A16 da célula. Selecione a célula e, na guia página inicial , no grupo edição , clique em preenchimentoe selecione série para exibir a caixa de diálogo série . Na caixa de diálogo série , mostrada na figura 60-6, insira um valor de etapa 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 1 – 1000 serão inseridos na coluna A começar no A16 da célula.

Imagem de Livro

Em seguida, entramos em 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 de avaliação (1 a 1000) e cada quantidade de produção. Nos referimos à fórmula de lucro (calculada na célula C11) na célula superior esquerda da nossa tabela de dados (a15) inserindo = C11.

Agora estamos prontos para induzir o Excel a simular 1000 iterações de demanda para cada quantidade de produção. Selecione o intervalo de tabelas (a15: E1014) e, em seguida, no grupo ferramentas de dados na guia dados, clique em o que é análise de dados e, em seguida, selecione tabela de dados. Para configurar uma tabela de dados bidirecional, escolha a quantidade de produção (célula C1) como a célula de entrada da linha e selecione qualquer 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 os valores da demanda do 1000 para cada pedido.

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

Ao copiar da célula B13 para o C13: E13 a média da fórmula (B16: B1015), calculamos o lucro simulado médio para cada quantidade de produção. Ao copiar da célula B14 para o C14: E14 a fórmula DESVPAD (B16: B1015), calculamos o desvio padrão dos nossos lucros simulados para cada quantidade de pedidos. Cada vez que pressionamos F9, 1000 iterações de demanda são simuladas para cada quantidade de pedido. A produção de cartões de 40.000 sempre produz o maior lucro esperado. Portanto, parece que produzir cartões 40.000 é a decisão adequada.

O impacto do risco na nossa decisão     Se produzimos 20.000 em vez de cartões 40.000, o lucro esperado cai aproximadamente 22%, mas o risco (conforme medido pelo desvio padrão do lucro) cai em quase 73%. Portanto, se for extremamente Averse para arriscar, produzir cartões 20.000 pode ser a decisão certa. Incidentalmente, a produção de cartões de 10.000 sempre tem um desvio padrão de placas 0 porque, se produzirmos cartões 10.000, sempre venderemos todos eles sem sobras.

Observação:  Nesta pasta de trabalho, a opção cálculo é definida como automática, exceto para tabelas. (Use o comando cálculo no grupo cálculo na guia fórmulas). Essa configuração garante que a tabela de dados não será recalculada, a menos que você pressione F9, o que é uma boa ideia porque uma tabela de dados grande diminuirá o seu trabalho se ele recalcular toda vez que você digitar algo em sua planilha. Observe que, neste exemplo, sempre que você pressionar F9, o lucro médio será alterado. Isso acontece porque cada vez que você pressiona F9, uma sequência diferente de números aleatórios do 1000 é usada para gerar demandas para cada quantidade de pedido.

Intervalo de confiança para o lucro médio     Uma pergunta natural a ser pedida nesta situação é, em que intervalo são 95% de certeza de que o lucro da média verdadeira vai cair? Esse intervalo é chamado de intervalo de confiança de porcentagem de 95 por lucro médio. Um intervalo de confiança de% 95 para a média de qualquer saída de simulação é calculada pela seguinte fórmula:

Imagem de Livro

Na célula J11, você calcula o limite inferior para o intervalo de confiança de porcentagem de 95 no lucro médio quando os calendários do 40.000 são produzidos com a fórmula D13 – 1.96 * D14/sqrt (1000). Na célula J12, você calcula o limite superior para o 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

Temos 95% de certeza de que o nosso lucro médio quando os calendários do 40.000 são pedidos é entre $56687 e $62589.

  1. Um carteador GMC acredita que a demanda por 2005 Envoys será normalmente distribuída com uma média de 200 e o desvio padrão de 30. O custo de receber um Envoy é $25000, e ele vende um Envoy para $40000. A metade de todas as Envoys não vendidas pelo preço total pode ser vendida para $30000. Ele está pensando em solicitar o 200, o 220, o 240, o 260, o 280 ou o 300 Envoys. Quantos devem ser solicitados?

  2. Um pequeno supermercado está tentando determinar quantas cópias da revista de pessoas devem pedir a cada semana. Eles acreditam que a demanda por pessoas é regida pela seguinte variável aleatória discreta:

    Pedido

    Probabilidade

    15

    0,10

    20

    0,20

    25

    0,30

    30

    0,25

    35

    0,15

  3. O supermercado paga $1 para cada cópia de pessoas e a vende para o $1.95. Cada cópia não vendida pode ser retornada para $0.50. Quantas cópias de pessoas devem ter na ordem da loja?

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.

×