Como usar as ferramentas de análise de variações hipotéticas no Microsoft Excel

Ao analisar seus dados no Microsoft Excel, você pode querer fazer algumas comparações, como “E se eu escolher a Opção A em vez da Opção B?” Usando as ferramentas de análise de variações hipotéticas integradas no Excel, você pode comparar números e valores com mais facilidade – por exemplo, para avaliar salários de empregos, opções de empréstimo ou cenários de receitas e despesas.

As ferramentas de Análise de variações hipotéticas no Excel incluem o Gerenciador de cenário, Atingir meta e Tabela de dados. Para explicar melhor a finalidade dessas ferramentas, vejamos exemplos de cada uma.

Gerenciador de cenários

Usando o Scenario Manager, insira valores que você pode alterar para ver resultados variados. Como bônus, crie um Relatório de Resumo do Cenário para comparar os valores ou números lado a lado.

Por exemplo, digamos que você esteja planejando um evento e decidindo entre alguns temas com custos diferentes. Configure os preços de cada tema para ver quanto custarão para compará-los.

Crie vários cenários para diferentes situações para ajudar na sua tomada de decisão.

Como usar o gerenciador de cenários

Se você estiver pronto para comparar situações diferentes, como no exemplo acima, siga estas etapas para usar o Gerenciador de cenários no Excel.

• Insira os dados para seu primeiro cenário em sua planilha. Usando o exemplo anterior, estamos comparando os custos do tema do nosso evento e inserindo as despesas do nosso Tema Praia nas células A2 a A6 e seus custos nas células B2 a B6. Estamos somando os preços na célula B7 para ver o custo total.

• Para adicionar esses detalhes ao Scenario Manager, vá para a guia “Data” e a seção “Forecast” da faixa de opções. Abra o menu suspenso “What-If Analysis” e escolha “Scenario Manager”.

• Clique em “Adicionar”.

• Dê um nome ao seu cenário (estamos usando “Beach Theme”) e insira as células que você ajustará no campo “Changing cells”. Como alternativa, arraste o cursor pelas células da planilha para preencher esse campo. Opcionalmente, insira um comentário diferente do padrão e clique em “OK”.

• Os valores no campo “Alterar células” devem corresponder aos da sua planilha, mas você pode ajustá-los aqui. Clique em “OK” para continuar.

• Agora que adicionou o primeiro cenário, você o verá listado no Scenario Manager. Selecione “Adicionar” para configurar seu próximo cenário.

• Insira os detalhes para o segundo cenário como você fez para o primeiro. Inclua o nome, alterando as células e o comentário opcional e clique em “OK”. Em nosso exemplo, estamos inserindo “Vegas Theme” e o mesmo intervalo de células, B2 a B6, para ver facilmente uma comparação no local.

• Insira os valores para seu segundo cenário na janela Valores de Cenário. Se estiver usando as mesmas células da primeira, você as verá preenchidas. Digite os que você deseja usar e clique em “OK”.

• Escolha o cenário que deseja visualizar na lista da janela Gerenciador de cenários e clique em “Mostrar”.

• Os valores em sua planilha serão atualizados para exibir o cenário selecionado.

• Continue a adicionar e mostrar cenários adicionais para visualizar os valores atualizados em sua planilha. Depois de encontrar aquele que deseja manter em sua planilha, selecione “Fechar” para sair do Gerenciador de Cenários.

Veja o resumo do cenário

Visualize o Resumo do cenário para ver todos os seus cenários de uma só vez para realizar uma comparação lado a lado.

• Volte para “Dados -> Análise de variações hipotéticas -> Gerenciador de cenários” e clique em “Resumo”.

• Escolha o tipo de relatório que deseja visualizar: “Resumo do cenário” ou “ Relatório de tabela dinâmica do cenário ”. Opcionalmente, se você deseja exibir seu resultado, insira a célula que o contém e clique em “OK”.

Em nosso exemplo, estamos selecionando “Resumo do cenário”, que coloca o relatório em uma nova guia de planilha. Você também notará que o relatório pode opcionalmente incluir agrupamento de células para ocultar certas partes do relatório.

Observe que se você ajustar os detalhes no Scenario Manager, o relatório não será atualizado automaticamente, então você deve gerar um novo relatório.

Atingir meta

A ferramenta Goal Seek funciona de forma um tanto oposta ao Scenario Manager. Com esta ferramenta, você tem um resultado conhecido e insere diferentes variáveis ​​para ver como chegar a esse resultado.

Por exemplo, talvez você venda produtos e tenha uma meta de lucro anual. Você quer saber quantas unidades precisa vender ou a que preço para atingir sua meta. O Goal Seek é a ferramenta ideal para encontrar respostas.

Com o Goal Seek, apenas uma variável ou valor de entrada pode ser usado. Use isso para os cenários em que você tem os valores restantes antecipadamente.

Como usar a busca de meta

Em um exemplo da ferramenta Goal Seek, temos 1.500 produtos para vender e queremos lucrar $ 52.000. Estamos usando o Atingir meta para determinar a que preço devemos vender nosso produto para atingir essa meta.

• Comece inserindo os valores e fórmulas em sua planilha, de acordo com seu cenário. Usando nosso exemplo, estamos inserindo a quantidade atual na célula B2, o preço estimado na célula B3 e uma fórmula para o lucro na célula B4, que é =B2*B3.

• Vá para a guia “Dados”, abra o menu suspenso “Análise de variações hipotéticas” e escolha “Atingir meta”.

• Insira os seguintes valores e clique em “OK”:
  • Definir célula : a referência da célula (contendo a fórmula) para o valor que você deseja alterar para alcançar o resultado desejado. No nosso exemplo, esta é a célula B4.
  • To value : o valor do resultado desejado. Para nós, isso é 52000.
  • Ao alterar a célula : a referência da célula que você deseja alterar para chegar ao resultado. Estamos usando a célula B3, pois queremos alterar o preço.

• Clique em “OK” para ver a atualização da caixa “Goal Seek Status” para exibir uma solução e sua planilha mudar para conter os valores ajustados. Em nosso exemplo, devemos vender nosso produto por US$ 35 para atingir nossa meta de US$ 52.000. Selecione “OK” para manter os novos valores em sua planilha.

Você sabe : há muitas coisas que você pode fazer no Microsoft Excel, incluindo inserir minigráficos e minigráficos .

Tabela de dados

Use uma tabela de dados no Excel para visualizar uma variedade de situações numéricas possíveis.

Para um exemplo ideal, você pode revisar as opções de empréstimo. Ao inserir diferentes taxas de juros, você pode ver qual seria o seu pagamento mensal com cada uma delas. Isso ajuda você a determinar qual taxa comprar ou discutir com seu credor.

Com uma tabela de dados, você só pode usar até duas variáveis. Se precisar de mais, use o Scenario Manager.

Como usar uma tabela de dados

Siga as etapas abaixo para usar uma tabela de dados, a terceira ferramenta de análise de variações hipotéticas. Observe a configuração de dados.

Por exemplo, estamos usando uma tabela de dados para ver quanto seriam nossos pagamentos de empréstimos com diferentes taxas de juros por meio dos seguintes dados:

• Taxa de juros, número de pagamentos e valor do empréstimo nas células B3 a B5.
• Uma coluna Taxa com as taxas de juros a serem exploradas nas células C3 a C5.
• Uma coluna Pagamento com a fórmula para o pagamento atual na célula D2.
• As células de resultado abaixo da fórmula na coluna Pagamento, inseridas automaticamente usando a ferramenta Tabela de dados. Isso nos mostra os valores de pagamento por taxa de juros.

Ao inserir seus dados e fórmula em sua planilha, lembre-se do seguinte:

• Use um layout orientado por linha ou coluna. Ele determinará o posicionamento de sua fórmula.
• Para um layout orientado por linha, coloque sua fórmula na célula uma coluna à esquerda do valor inicial e uma célula abaixo da linha que contém os valores.
• Para um layout orientado a colunas, coloque sua fórmula na célula uma linha acima e uma célula à direita da coluna que contém os valores.

Em nosso exemplo, estamos usando uma única variável (a taxa de juros) em um layout orientado a colunas. Observe o posicionamento de nossa fórmula na célula D2 (uma linha acima e uma célula à direita de nossos valores).

• Insira seus próprios dados e selecione as células que contêm a fórmula, os valores e as células de resultado. Em nosso exemplo, estamos selecionando as células C2 a D5.

• Vá para a guia “Dados”, abra o menu suspenso “Análise de variações hipotéticas” e escolha “Tabela de dados”.

• Insira a célula que contém a variável variável para seus dados na caixa Tabela de dados. Para um layout orientado a linha, use a “Célula de entrada de linha” e para um layout orientado a coluna, use a “Célula de entrada de coluna”. No nosso exemplo, estamos usando o último e inserindo “B3”, que é a célula que contém a taxa de juros.

• Depois de clicar em “OK” na caixa Tabela de dados, você deverá ver as células de resultado preenchidas com os dados esperados. Nosso exemplo inclui o valor de nosso pagamento para cada taxa de juros diferente.

Observe que você pode usar duas variáveis ​​em sua tabela de dados em vez de uma, experimentar o layout orientado por linha ou visualizar mais detalhes e limitações dessa ferramenta de análise de variações hipotéticas na página de suporte da Microsoft para o recurso .

perguntas frequentes

Como faço para editar um cenário existente no Excel?

Você pode alterar o nome e os valores de um cenário usando o Scenario Manager. Abra a ferramenta selecionando “Data -> What-If Analysis -> Scenario Manager”. Escolha o cenário na lista e clique em “Editar” à direita. Faça suas alterações e escolha “OK” para salvá-las.

Se você criou inicialmente um relatório Resumo do cenário, precisará gerar novamente o relatório para ver os detalhes atualizados.

Posso impedir que o Excel recalcule uma tabela de dados?

Se sua pasta de trabalho contiver uma tabela de dados, o Excel recalculará automaticamente essa tabela de dados, mesmo que não haja alterações. No entanto, você pode desativar essa opção se desejar.

Vá para a guia “Fórmula”, abra o menu suspenso “Opções de cálculo” no grupo Cálculo e selecione “Automático, exceto para tabelas de dados”.

Para recalcular sua Tabela de Dados manualmente, selecione a(s) fórmula(s) e pressione F9.

Que outras ferramentas de análise o Excel oferece?

O Excel fornece muitos tipos diferentes de ferramentas de análise de dados, dependendo do que você precisa. Para citar alguns, você pode usar formatação condicional para destacar dados específicos, Análise Rápida para formatação, gráficos e tabelas e Power Query para análise de dados robusta.

Você também pode usar recursos básicos do Excel, como filtros , para restringir seus dados, segmentações para filtrar tabelas e gráficos e a ferramenta Analisar dados para obter respostas a perguntas sobre seus dados.

Para obter assistência adicional sobre esses recursos e muito mais, acesse a guia “Ajuda” no Excel no Windows ou use a opção de menu “Diga-me” no Excel no Mac.

Crédito da imagem: Pixabay . Todas as capturas de tela por Sandy Writtenhouse.