Introdução à simulação de Carlo do monte 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 da modelagem de dados e da modelagem de negócios do Microsoft Excel por Wayne. Winston.

  • Quem utiliza a simulação de Carlo do monte?

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

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

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

  • Como é que uma empresa de cartões de saudações determina o número de cartões a produzir?

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

Nota:  O nome monte de simulação de Carlo vem das simulações de computador efetuadas durante o 1930s e 1940s para estimar a probabilidade de que a reação de cadeia necessária para um átomo de bomba para detonate funcione com êxito. O Physicists envolvido neste trabalho foram grandes fãs de jogos de azar, pelo que lhes fornecia as simulações com o codinome Monte Carlo.

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

Muitas empresas utilizam a simulação de Carlo do monte como parte importante do processo de tomada de decisões. Seguem-se alguns exemplos.

  • Os motores gerais, Proctor e jogar, a Pfizer, Bristol-Myers Squibb e Eli Lilly utilizam a simulação para estimar a média de Devoluções e o fator de risco de novos produtos. Na GM, estas informações são utilizadas pelo CEO para determinar quais os produtos que chegam ao mercado.

  • A GM utiliza simulação para atividades como previsões líquidas de receita para a empresa, prever custos estruturais e de compra e determinar o seu susceptibility a diferentes tipos de risco (como as alterações de taxa de juros e as flutuações de taxa de câmbio).

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

  • O Proctor e o jogo utilizam simulação para modelar de forma ideal o risco de Exchange externo.

  • O Sears utiliza a simulação para determinar quantas unidades de cada linha de produto devem ser ordenadas dos fornecedores — por exemplo, o número de pares de anCoragemres trousers que deveriam ser encomendados neste ano.

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

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

Quando escreve a fórmula = Rand () numa célula, obtém um número que é igualmente provável de assumir qualquer valor entre 0 e 1. Portanto, em torno de 25 por cento do tempo, deve obter um número menor ou igual a 0,25; em torno de 10% do tempo, deve obter um número que tenha, pelo menos, 0,90, entre outros. Para demonstrar como a função aleatório funciona, consulte o ficheiro Randdemo. xlsx, apresentado na figura 60-1.

Imagem do Livro

Nota:  Quando abrir o ficheiro Randdemo. xlsx, 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 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 = Rand (). Em seguida, pode atribuir um nome ao intervalo C3: C402 dados. Em seguida, na coluna F, pode controlar a média dos números aleatórios do 400 (célula F2) e utilizar 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. Ao premir a tecla F9, os números aleatórios são recalculados. RePare que a média dos números de 400 é sempre aproximadamente 0,5 e que, em torno de 25% dos resultados, encontram-se 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 por 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), indica-nos nada sobre os valores dos outros números aleatórios gerados.

SuPonhamos que a demanda de um calendário é regida 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 seja reproduzido ou simule esta demanda de calendários muitas vezes? O truque é associar cada valor possível da função aleatório, com uma possível demanda para os calendários. A seguinte atribuição assegura que uma demanda de 10.000 irá ocorrer 10% do tempo, etc.

Demanda

Número aleatório atribuído

10.000

Menor do que 0,10

20.000

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

40.000

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

60 000

Maior ou igual a 0,75

Para demonstrar a simulação de demanda, consulte o ficheiro Discretesim. xlsx, apresentado na figura 60-2 na página seguinte.

Imagem do Livro

A chave para a nossa simulação é utilizar um número aleatório para iniciar uma pesquisa a partir do intervalo de tabelas F2: G5 ( pesquisacom nome). Os números aleatórios maiores ou iguais a 0 e menores do que 0,10 irão produzir uma demanda de 10.000; os números aleatórios maiores ou iguais a 0,10 e menores do que 0,45 irão produzir uma demanda de 20.000; os números aleatórios maiores ou iguais a 0,45 e menores do que 0,75 irão produzir uma demanda de 40.000; e os números aleatórios maiores ou iguais a 0,75 irão resultar numa demanda de 60.000. Pode gerar números aleatórios do 400 ao copiar do C3 para o C4: C402 a fórmula Rand (). Em seguida, pode gerar avaliações de 400 ou iterações de demanda do 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 menor que 0,10 gere uma demanda de 10.000, qualquer número aleatório entre 0,10 e 0,45 gera uma demanda de 20.000, entre outros. No intervalo de células F8: F11, utilize a função CONT.se para determinar a fração das nossas iterações do 400, resultando em cada demanda. Quando premir F9 para recalcular os números aleatórios, as probabilidades simuladas estão próximas às nossas probabilidades de demanda assumidas.

Se escrever em qualquer célula o inv da fórmula (Rand (), MU, Sigma), irá gerar um valor simulado de uma variável aleatória normal com um MU e desvio padrão. Este procedimento está ilustrado no ficheiro Normalsim. xlsx, apresentado na figura 60-3.

Imagem do Livro

Vamos supor que queremos simular avaliações do 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. (Pode escrever estes valores nas células E1 e E2 e atribuir um nome a estas células média e Sigma, respectivamente.) 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 inv (C4; média; Sigma) gera 400 valores de avaliação 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. Ao premir a tecla F9 para recalcular os números aleatórios, a média continua perto do 40.000 e do desvio-padrão próximo do 10.000.

Basicamente, para um número aleatório x, a fórmula inv (p, MU, Sigma) gera o p-percentil de uma variável aleatória normal com um MU e um desvio padrão. 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 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 secção, verá como é que o Monte Carlo Simulation pode ser utilizado como uma ferramenta de tomada de decisão. SuPonhamos que a demanda do cartão de dia dos Namorados é regida 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

O cartão de saudação vende o $4 e o custo variável de produzir cada cartão é de $1.50. Os cartões restantes têm de ser alienados a um custo de $0.20 por cartão. Quantas cartas devem ser impressas?

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 que quantidade de encomendas resulta no lucro máximo em relação às iterações do 1000. Pode encontrar os dados para esta secção no ficheiro namorado. xlsx, apresentada na figura 60-4. Atribua os nomes dos intervalos nas células B1: B11 para as células C1: C11. O intervalo de células G3: H6 é atribuído à pesquisade nome. Os nossos parâmetros de preço e custo de vendas foram introduzidos nas células C4: C6.

Imagem do Livro

Pode introduzir 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 (). Tal como descrito anteriormente, simula a demanda do cartão na célula C3 com a fórmula PROCV (aleatório, pesquisa, 2). (Na fórmula PROCV, Rand é o nome da célula atribuído à célula C3, não à função aleatório.)

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

Se produzirmos mais cartas do que as que estão a ser solicitadas, o número de unidades restantes é igual a produção a menos de demanda; caso contrário, não são deixadas outras unidades. Calculamos o nosso custo de eliminaçã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 o nosso 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 contagem do nosso lucro esperado para cada quantidade. Esta situação é uma das tabelas de dados bidirecionais para o nosso 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 utilizada neste exemplo é apresentada na figura 60-5.

Imagem do Livro

No intervalo de células A16: A1015, introduza os números 1 a 1000 (correspondentes às nossas avaliações de 1000). Uma forma fácil de criar estes valores é começar introduzindo 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 , apresentada na figura 60-6, introduza o valor de incremento 1 e um valor de Stop de 1000. Na área séries em , selecione a opção colunas e, em seguida, clique em OK. Os números 1-1000 serão introduzidos na coluna A começar na célula A16.

Imagem do Livro

Em seguida, inserimos 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 de cada número de avaliação (1 a 1000) e cada quantidade de produção. Vamos consultar a fórmula de lucro (calculada na célula C11) na célula do canto superior esquerdo da nossa tabela de dados (a15) ao introduzir = C11.

Agora estamos prontos para enganar o Excel para simular as iterações do 1000 de demanda para cada quantidade de produção. Selecione o intervalo de tabelas (a15: E1014) e, em seguida, no grupo ferramentas de dados no separador dados, clique em o que é análise e, em seguida, selecione tabela de dados. Para configurar uma tabela de dados bidirecional, selecione a nossa quantidade de produção (célula C1) como célula de entrada de linha e selecione qualquer célula em branco (escolhemos a célula I14) como célula de entrada da coluna. Depois de clicar em OK, o Excel simula os valores de demanda do 1000 para cada quantidade de encomendas.

Para compreender por que motivo 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 da célula de entrada da coluna 1 é colocado numa célula em branco e o número aleatório na célula C2 é recalculado. O lucro correspondente é, em seguida, registado na célula C16. Em seguida, o valor 2 de entrada da célula da coluna é colocado numa célula em branco e o número aleatório em C2 novamente é recalculado. O lucro correspondente é introduzido na célula C17.

Ao copiar da célula B13 para o C13: E13 a fórmula média (B16: B1015), calculamos o lucro simulado de média 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 encomendas. Sempre que premir F9, as iterações de demanda do 1000 são simuladas para cada quantidade de encomendas. Produzir cartões de 40.000 produz sempre o maior lucro esperado. Por isso, parece que produzir cartões de 40.000 é a decisão certa.

O impacto do risco sobre a nossa decisão     Se produzimos 20.000 em vez de cartões de 40.000, o nosso lucro esperado é de cerca de 22%, mas o nosso risco (tal como medido pelo desvio padrão de lucro) desce quase 73%. Por conSeguinte, se for extremamente Averse risco, produzir cartões 20.000 pode ser a decisão certa. Por acaso, produzir cartões de 10.000 tem sempre um desvio padrão de placas de 0 porque, se produzirmos cartões 10.000, iremos sempre vender todos eles sem sobras.

Nota:  Neste livro, a opção de cálculo é definida como automática, exceto para tabelas. (Utilize o comando cálculo no grupo cálculo no separador fórmulas.) Esta definição assegura que a nossa tabela de dados não será recalculada a menos que prima F9, o que é uma boa ideia, uma vez que uma tabela de dados grande irá reduzir o seu trabalho se recalcular 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 sempre que premir F9, é utilizada uma sequência diferente de números aleatórios de 1000 para gerar demandas para cada quantidade de encomendas.

Intervalo de confiança para fins de lucro médio     Uma questão natural a perguntar nesta situação é em que intervalo é de 95% de certeza de que o lucro da média média vai cair? Este intervalo chama-se o intervalo de confiança de percentagem de 95 por lucro médio. Um intervalo de confiança do 95% para a média de qualquer resultado de simulação é calculado pela seguinte fórmula:

Imagem do Livro

Na célula J11, pode calcular o limite inferior para o intervalo de confiança do 95% na média de lucro quando os calendários do 40.000 são produzidos com a fórmula D13-1.96 * D14/sqrt (1000). Na célula J12, pode calcular o limite superior para o nosso intervalo de int $ 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.

Imagem do Livro

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

  1. Um revendedor GMC acredita que a demanda para o 2005 Envoys será normalmente distribuída com uma média de 200 e um desvio padrão de 30. O seu custo de receber um Envoy é $25000 e vende uma Envoy para $40000. A metade de todas as Envoys não vendidas a preço total pode ser vendida para o $30000. Está a pensar em pedir o 200, o 220, o 240, o 260, o 280 ou o 300 Envoys. Quantas devem ser encomendadas?

  2. Um pequeno supermercado está a tentar determinar quantas cópias da revista People devem pedir por semana. As pessoas acreditam que a sua demanda por parte das pessoas é regida pela seguinte variável aleatória discreta:

    Demanda

    Probabilidade

    15

    0,10

    20

    0,20

    17

    0,30

    30

    0,25

    35

    0,15

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

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.

×