Análise de variações hipotéticas com o Scenario Manager

O Scenario Manager é útil nos casos em que você tem mais de duas variáveis ​​na análise de sensibilidade. O Scenario Manager cria cenários para cada conjunto de valores de entrada para as variáveis ​​em consideração. Os cenários ajudam você a explorar um conjunto de resultados possíveis, apoiando o seguinte -

  • Variando até 32 conjuntos de entrada.
  • Mesclar os cenários de várias planilhas ou pastas de trabalho diferentes.

Se você deseja analisar mais de 32 conjuntos de entrada e os valores representam apenas uma ou duas variáveis, você pode usar tabelas de dados. Embora seja limitada a apenas uma ou duas variáveis, uma Tabela de dados pode incluir quantos valores de entrada diferentes você desejar. Consulte Análise de variações hipotéticas com tabelas de dados neste tutorial.

Cenários

Um cenário é um conjunto de valores que o Excel salva e pode substituir automaticamente em sua planilha. Você pode criar e salvar diferentes grupos de valores como cenários em uma planilha e, em seguida, alternar entre esses cenários para visualizar os diferentes resultados.

Por exemplo, você pode ter vários cenários de orçamento diferentes que comparam vários níveis de receita e despesas possíveis. Você também pode ter cenários de empréstimo diferentes de fontes diferentes que comparam várias taxas de juros possíveis e prazos de empréstimo.

Se as informações que deseja usar nos cenários forem de fontes diferentes, você poderá coletar as informações em pastas de trabalho separadas e, em seguida, mesclar os cenários das diferentes pastas de trabalho em uma.

Depois de ter todos os cenários de que precisa, você pode criar um relatório de resumo do cenário -

  • Isso incorpora informações de todos os cenários.
  • Isso permite comparar os cenários lado a lado.

Gerente de Cenário

O Scenario Manager é uma das ferramentas de análise de variações hipotéticas do Excel.

Para criar um relatório de análise com o Scenario Manager, você deve seguir estas etapas -

Step 1 - Defina o conjunto de valores iniciais e identifique as células de entrada que você deseja variar, chamadas de células em mudança.

Step 2 - Crie cada cenário, nomeie o cenário e insira o valor para cada célula de entrada em mudança para esse cenário.

Step 3- Selecione as células de saída, chamadas de células de resultado que você deseja rastrear. Essas células contêm fórmulas no conjunto inicial de valores. As fórmulas usam as células de entrada variáveis.

O Scenario Manager cria um relatório contendo os valores de entrada e saída para cada cenário.

Valores iniciais para cenários

Antes de criar vários cenários diferentes, você precisa definir um conjunto de valores iniciais nos quais os cenários serão baseados.

As etapas para configurar os valores iniciais para os cenários são:

  • Defina as células que contêm os valores de entrada.
  • Nomeie as células de entrada apropriadamente.
  • Identifique as células de entrada com valores constantes.
  • Especifique os valores para as entradas constantes.
  • Identifique as células de entrada com valores variáveis.
  • Especifique os valores iniciais para as entradas de mudança.
  • Defina as células que contêm os resultados. As células de resultado contêm fórmulas.
  • Nomeie as células de resultado apropriadamente.
  • Coloque as fórmulas nas células de resultado.

Considere o exemplo anterior de empréstimo. Agora, proceda da seguinte forma -

  • Defina uma célula para o valor do empréstimo.

    • Este valor de entrada é constante para todos os cenários.

    • Nomeie a célula Loan_Amount.

    • Especifique o valor como 5.000.000.

  • Defina as células para Taxa de juros, Nº de pagamentos e Tipo (Pagamento no início ou no final do mês).

    • Esses valores de entrada mudarão nos cenários.

    • Nomeie as células Interest_Rate, NPER e Type.

    • Especifique os valores iniciais para a análise nessas células como 12%, 360 e 0, respectivamente.

  • Defina a célula para o EMI.

    • Este é o valor do resultado.

    • Nomeie a célula EMI.

    • Coloque a fórmula nesta célula como -

      =PMT (Interest_Rate/12, NPER, Loan_Amount, 0, Type)

Sua planilha se parece com a mostrada abaixo -

Como você pode ver, as células de entrada e as células de resultado estão na coluna C com os nomes fornecidos na coluna D.

Criação de cenários

Depois de configurar os valores iniciais para os cenários, você pode criar os cenários usando o Gerenciador de cenários da seguinte maneira:

  • Clique na guia DADOS na faixa de opções.
  • Clique em Análise de variações hipotéticas no grupo Ferramentas de dados.
  • Selecione Gerenciador de cenários na lista suspensa.

A caixa de diálogo Gerenciador de cenários é exibida. Você pode observar que ele contém uma mensagem -

“No Scenarios defined. Choose Add to.”

Você precisa criar cenários para cada conjunto de valores variáveis ​​no Scenario Manager. É bom ter o primeiro cenário definido com valores iniciais, pois permite que você volte aos valores iniciais sempre que quiser, enquanto exibe diferentes cenários.

Crie o primeiro cenário com os valores iniciais da seguinte forma -

  • Clique no Add botão na caixa de diálogo Gerenciador de cenários.

o Add Scenario a caixa de diálogo aparece.

  • Em Nome do cenário, digite Cenário 1.
  • Em Changing Cells, insira as referências para as células, ou seja, C3, C4 e C5 com a tecla Ctrl pressionada.

O nome da caixa de diálogo muda para Editar cenário.

  • Edite o texto no Comment as – Initial Values caixa.

  • Selecione a opção Impedir alterações em Proteção e clique em OK.

o Scenario Valuesa caixa de diálogo aparece. Os valores iniciais que você definiu aparecem em cada uma das caixas de células em mudança.

Scenario 1 com os valores iniciais é criado.

Crie mais três cenários com valores variáveis ​​nas células em mudança, como segue -

  • Clique no Add botão na caixa de diálogo Valores do cenário.

A caixa de diálogo Adicionar cenário é exibida. Observe que C3, C4, C5 aparecem na caixa Changing cells.

  • Na caixa Nome do cenário, digite Cenário 2.

  • Edite o texto no Comment as - Taxa de juros diferente.

  • Selecione Evitar alterações em Proteção e clique em OK.

o Scenario Valuesa caixa de diálogo aparece. Os valores iniciais aparecem nas células em mudança. Altere o valor deInterest_Rate para 0.13 e clique Add.

o Add Scenarioa caixa de diálogo aparece. Observe que C3, C4, C5 aparecem na caixa sob a alteração de células.

  • Na caixa Nome do cenário, digite Cenário 3.

  • Edite o texto no Commentcaixa como - Diferente não. de Pagamentos.

  • Selecione Evitar alterações em Proteção e clique em OK.

A caixa de diálogo Valores do cenário é exibida. Os valores iniciais aparecem nas células em mudança. Altere o valor de NPER para 300 e clique emAdd.

o Add Scenarioa caixa de diálogo aparece. Observe que C3, C4, C5 aparecem na caixa Changing cells.

  • Na caixa Nome do cenário, digite Cenário 4.

  • Edite o texto no Comment caixa como - Diferentes tipos de pagamento.

  • Selecione Evitar alterações em Proteção e clique em OK.

o Scenario Valuesa caixa de diálogo aparece. Os valores iniciais aparecem nas células em mudança. Altere o valor de Tipo para 1. Clique em OK ao adicionar todos os cenários que deseja adicionar.

o Scenario Managera caixa de diálogo aparece. Na caixa sob Cenários, você encontrará os nomes de todos os cenários que criou.

  • Clique em Cenário 1. Como você sabe, o Cenário 1 contém os valores iniciais.

    • Agora clique Summary. A caixa de diálogo Resumo do cenário é exibida.

Relatórios de resumo de cenário

O Excel fornece dois tipos de relatórios de resumo do cenário -

  • Resumo do cenário.
  • Relatório de tabela dinâmica de cenário.

Na caixa de diálogo Resumo do cenário, você pode encontrar esses dois tipos de relatório.

Selecione Resumo do cenário em Tipo de relatório.

Resumo do Cenário

No Result cells caixa, selecione a célula C6 (Aqui, colocamos o PMTfunção). Clique OK.

O relatório de resumo do cenário aparece em uma nova planilha. A planilha é denominada Resumo do Cenário.

Você pode observar o seguinte no relatório de resumo do cenário -

  • Changing Cells- Lista todas as células usadas como células em mudança. Como você nomeou as células, Interest_Rate, NPER e Type, elas parecem tornar o relatório significativo. Caso contrário, apenas referências de células serão listadas.

  • Result Cells - Exibe a célula de resultado especificada, ou seja, EMI.

  • Current Values - É a primeira coluna e relaciona os valores daquele cenário que é selecionado na caixa de diálogo Gerenciador de cenários antes de criar o relatório de resumo.

  • Para todos os cenários que você criou, as células em mudança serão destacadas em cinza.

  • Na linha EMI, os valores dos resultados para cada cenário serão exibidos.

Você pode tornar o relatório mais significativo exibindo os comentários que adicionou ao criar os cenários.

  • Clique no botão + à esquerda da linha que contém os nomes dos cenários. Os comentários para os cenários aparecem na linha sob os nomes dos cenários.

Cenários de diferentes fontes

Suponha que você obtenha os cenários de três fontes diferentes e precise preparar o relatório de resumo do cenário em uma pasta de trabalho mestre. Você pode fazer isso mesclando os cenários de pastas de trabalho diferentes na pasta de trabalho Mestre. Siga as etapas abaixo -

  • Suponha que os cenários estejam nas pastas de trabalho, Bank1_Scenarios, Bank2_Scenarios e Bank3_Scenarios. Abra as três pastas de trabalho.

  • Abra a pasta de trabalho Mestre, na qual você tem os valores iniciais.

  • Clique em DADOS> Análise de variações hipotéticas> Gerenciador de cenários na pasta de trabalho mestre.

o Scenario Manager A caixa de diálogo é exibida.

Como você pode observar, não há cenários, pois você ainda não adicionou nenhum. CliqueMerge.

A caixa de diálogo Mesclar cenários é exibida.

Como você pode ver, em Mesclar cenários de, você tem duas caixas -

  • Book
  • Sheet

Você pode selecionar uma planilha específica de uma pasta de trabalho específica que contém os cenários que deseja adicionar aos resultados. Clique na seta suspensa deBook para ver as pastas de trabalho.

Note - As pastas de trabalho correspondentes devem ser abertas para aparecer nesta lista.

Selecione o livro - Bank1_Scenarios.

A folha do banco1 é exibida. Na parte inferior da caixa de diálogo, o número de cenários encontrados na planilha de origem é exibido. Clique OK.

A caixa de diálogo Gerenciador de cenários é exibida. Os dois cenários que foram mesclados na pasta de trabalho mestre serão listados em Cenários.

Clique no Mergebotão. oMerge Scenariosa caixa de diálogo aparece. Agora, selecioneBank2_Scenarios na lista suspensa na caixa Livro.

A folha do banco2 é exibida. Na parte inferior da caixa de diálogo, o número de cenários encontrados na planilha de origem é exibido. Clique OK.

o Scenario ManagerA caixa de diálogo é exibida. Os quatro cenários que foram mesclados na pasta de trabalho Mestre estão listados em Cenários.

Clique no Mergebotão. oMerge Scenariosa caixa de diálogo aparece. Agora, selecioneBank3_Scenarios na lista suspensa na caixa Livro.

A folha do banco 3 é exibida. Na parte inferior da caixa de diálogo, o número de cenários encontrados na planilha de origem será exibido. Clique OK.

A caixa de diálogo Gerenciador de cenários é exibida. Os cinco cenários que foram mesclados na pasta de trabalho Mestre serão listados em Cenários.

Agora, você tem todos os cenários necessários para produzir o relatório de resumo do cenário.

Clique no botão Resumo. oScenario Summary a caixa de diálogo aparece.

  • Selecione Resumo do cenário.
  • Na caixa Células de resultado, digite C6 e clique em OK.

O relatório de resumo do cenário aparece em uma nova planilha na pasta de trabalho Mestre.

Exibindo cenários

Suponha que você esteja apresentando seus cenários e gostaria de alternar dinamicamente de um cenário para outro e exibir o conjunto de valores de entrada e valores de resultado do cenário correspondente.

  • Clique em DADOS> Análise de variações hipotéticas> Gerenciador de cenários no grupo Ferramentas de dados. A caixa de diálogo Gerenciador de cenários é exibida. A lista de cenários é exibida.

  • Selecione o cenário que deseja exibir. CliqueShow.

Os valores da planilha são atualizados de acordo com o cenário selecionado. Os valores do resultado são recalculados.

Relatório de tabela dinâmica de cenário

Você também pode ver o relatório de cenário na forma de uma tabela dinâmica.

  • Clique no botão Resumo no Scenario ManagerCaixa de diálogo. A caixa de diálogo Resumo do cenário é exibida.

  • Selecione os Scenario PivotTable report em Tipo de relatório.

  • Digite C6 no Result cells caixa.

O relatório de tabela dinâmica do cenário aparece em uma nova planilha.