O que é uma análise de variações hipotéticas no Excel?
A análise de variações hipotéticas no Excel é uma ferramenta que nos ajuda a criar diferentes modelos, cenários e tabelas de dados. Neste artigo, examinaremos as maneiras de usar a análise de variações hipotéticas.
Temos 3 partes de análise de variações hipotéticas no Excel. Eles são os seguintes:
- Gerente de Cenário
- Atingir Meta no Excel
- Tabela de dados em Excel
Gerente de cenário nº 1 em análise de variações hipotéticas
Como chefe de negócios, é importante conhecer os diferentes cenários de seu projeto futuro. Com base nos cenários, o chefe de negócios tomará decisões. Por exemplo, você vai realizar um dos projetos importantes. Você fez sua lição de casa e listou todas as despesas possíveis do seu final e abaixo está a lista de todas as suas despesas.
O fluxo de caixa esperado desse projeto é de 75 milhões, que está na célula C2. As despesas totais abrangem todas as despesas fixas e variáveis e o custo total é de 57,45 milhões na célula C12. O lucro total é de 17,55 milhões na célula C14 e o% de lucro é de 23,40% de sua entrada de caixa.
Este é o cenário básico do seu projeto. Agora você precisa saber o cenário de lucro se alguma de suas despesas aumentar ou diminuir.
Cenário 1
- Em um cenário de caso geral, você estimou o custo da Licença do Projeto em 10 milhões, mas com certeza está prevendo que será em 15 milhões
- Custo da matéria-prima será aumentado em 2,5 milhões
- Outras despesas serão reduzidas em 50 mil.
Cenário 2
- O custo do projeto será de 20 milhões.
- Salários diários de trabalho em 5 milhões
- Custo operacional de 3,5 milhões.
Agora você listou todos os cenários no formulário. Com base nesses cenários, você precisa criar uma tabela de cenários de como isso afetará seu lucro e% de lucro. Para criar cenários de análise what-if, siga as etapas abaixo.
- Etapa 1: Vá para DADOS> Análise de variações hipotéticas> Gerenciador de cenários.
- Etapa 2: Depois de clicar em Gerenciador de cenários, ele será mostrado abaixo da caixa de diálogo.
- Etapa 3: Clique em ADICIONAR. Dê um nome ao cenário.
- Etapa 4: Ao mudar as células, selecione as primeiras mudanças de cenário que você listou. As mudanças são Custo do projeto (célula C10) em 15 milhões, Custo da matéria-prima (célula C7) em 11 milhões e outras despesas (célula C11) em 4,5 milhões. Mencione essas 3 células aqui.
- Etapa 5: Clique em OK. Ele solicitará que você mencione os novos valores, mencione os novos valores listados no cenário 1.
- Etapa 6: Não clique em OK, mas clique em OK Adicionar. Isso salvará este cenário para você.
- Etapa 7: Agora, ele solicitará que você crie mais um cenário. Conforme listamos no cenário 2, faça as alterações. Desta vez, precisamos mudar o custo do projeto (C10), Custo de mão de obra (C8) e Custo operacional (C9)
- Etapa 8: Agora adicione novos valores aqui.
- Passo 9: Agora clique em Ok. Ele mostrará todos os cenários que criamos.
- Passo 10: Clique em RESUMO, ele perguntará quais células de resultado você deseja alterar. Aqui, precisamos alterar a célula de despesa total (C12), a célula de lucro total (C14) e a célula de% de lucro (C16).
- Etapa 11: Clique em OK. Ele criará um relatório resumido para você na nova planilha.
O Excel total criou 3 cenários, embora tenhamos fornecido apenas 2 mudanças de cenário. Porque o Excel mostrará os relatórios existentes como um cenário.
A partir desta tabela, podemos ver facilmente o impacto das alterações na porcentagem de lucro de vazamento.
Nº 2 Atingir meta na análise de variações hipotéticas
Agora sabemos a vantagem do gerenciador de cenários. Análise de variações hipotéticas A busca de metas pode dizer o que você deve fazer para atingir a meta definida.
Andrew é o décimo aluno da classe. Sua meta é atingir a nota média de 85 no exame final e já fez 5 exames e saiu com apenas 1 exame. Nos 5 exames concluídos, as pontuações previstas são as seguintes.
Para calcular a média atual, aplique a fórmula da Média na célula B7.
A média atual é de 82,2.
A META de Andrew é 85 e sua média atual é 82,2 e baixa em 3,8 com um exame para fazer.
Agora a questão é quanto ele precisa pontuar no exame final para obter uma média geral de 85 no final. Isso pode ser descoberto pela ferramenta de análise de hipóteses GOAL SEEK.
- Etapa 1: vá para DADOS> Análise de variações hipotéticas> Atingir meta
- Etapa 2: aparecerá abaixo da caixa de diálogo.
- Etapa 3: aqui precisamos definir a célula primeiro. Set Cell nada mais é do que de qual célula precisamos do resultado final, ou seja, nossa célula média geral (B7). Em seguida vem o valor Para, isso nada mais é do que o valor de que precisamos para definir a célula, ou seja, a meta média geral de Andrew (85).
A próxima e última parte é mudar qual célula você deseja ver o impacto. Portanto, precisamos alterar a célula B6, que é a célula da pontuação final do sujeito.
- Etapa 4: Clique em OK. O Excel levará alguns segundos para concluir o processo, mas, eventualmente, mostra o resultado final como o mostrado abaixo.
Agora temos nossos resultados aqui. Para obter uma média geral de 85, Andrew precisa ter 99 notas no exame final.
# 3 Tabela de dados na análise de variações hipotéticas
Já vimos duas técnicas maravilhosas na análise de variações hipotéticas no Excel. A tabela de dados pode criar diferentes tabelas de cenários com base na alteração da variável. Temos dois tipos de tabela de dados aqui “Uma tabela de dados variáveis” e “Tabela de dados de duas variáveis”. Neste artigo, vou mostrar uma tabela de dados variáveis do Excel.
Suponha que você esteja vendendo um produto de 1.000 a 15 rúpias, sua despesa total prevista é de 12.500 e o lucro é de 2.500.
Você não está satisfeito com o lucro que está obtendo. Seu lucro previsto é de 7.500. Você decidiu aumentar seu preço por unidade para aumentar seu lucro, mas não sabe quanto precisa aumentar.
As tabelas de dados podem ajudá-lo. Crie uma tabela abaixo
Agora, na célula F1, forneça um link para a célula de lucro total, ou seja, B6
- Etapa 1: Selecione a tabela recém-criada.
- Etapa 2: Vá para DADOS> Análise de variações hipotéticas> Tabela de dados.
- Etapa 3: Agora você verá a caixa de diálogo abaixo.
- Etapa 4: Como estamos mostrando o resultado verticalmente, deixe a célula de entrada Row. Na célula de entrada da coluna, selecione a célula B2, que é o preço de venda original.
- Etapa 5: Clique em Ok para obter os resultados. Ele listará os números de lucro na nova tabela.
Portanto, temos nossa tabela de dados pronta. Se você quer ter um lucro de 7.500, você precisa vender a 20 por unidade.
Coisas para lembrar
- A tabela de dados de análise de hipóteses pode ser executada com duas alterações de variáveis. Consulte nosso artigo sobre a Tabela de dados de duas variáveis da análise what-if.
- A busca do objetivo da análise de variações hipotéticas leva alguns segundos para realizar os cálculos.
- O gerenciador de cenários de análise de variações hipotéticas pode fornecer um resumo com números de entrada e valores atuais juntos.
