Análise de variações hipotéticas com tabelas de dados

Com uma tabela de dados no Excel, você pode facilmente variar uma ou duas entradas e realizar análises de variações hipotéticas. Uma Tabela de dados é um intervalo de células em que você pode alterar os valores em algumas das células e chegar a diferentes respostas para um problema.

Existem dois tipos de tabelas de dados -

  • Tabelas de dados de uma variável
  • Tabelas de dados de duas variáveis

Se você tiver mais de duas variáveis ​​em seu problema de análise, você precisa usar a ferramenta Scenario Manager do Excel. Para obter detalhes, consulte o capítulo - Análise de variações hipotéticas com o Scenario Manager neste tutorial.

Tabelas de dados de uma variável

Uma tabela de dados de uma variável pode ser usada se você quiser ver como diferentes valores de uma variável em uma ou mais fórmulas irão alterar os resultados dessas fórmulas. Em outras palavras, com uma tabela de dados de uma variável, você pode determinar como alterar uma entrada altera qualquer número de saídas. Você entenderá isso com a ajuda de um exemplo.

Example

Há um empréstimo de 5.000.000 por um mandato de 30 anos. Você deseja saber os pagamentos mensais (EMI) para taxas de juros variadas. Você também pode estar interessado em saber o valor dos juros e do principal que é pago no segundo ano.

Análise com tabela de dados de uma variável

A análise com uma tabela de dados de uma variável deve ser feita em três etapas -

Step 1 - Defina o fundo necessário.

Step 2 - Crie a tabela de dados.

Step 3 - Realize a análise.

Vamos entender essas etapas em detalhes -

Etapa 1: definir o plano de fundo necessário

  • Suponha que a taxa de juros seja de 12%.

  • Liste todos os valores necessários.

  • Nomeie as células que contêm os valores, para que as fórmulas tenham nomes em vez de referências de células.

  • Defina os cálculos para EMI, Juros cumulativos e Principal cumulativo com as funções do Excel - PMT, CUMIPMT e CUMPRINC respectivamente.

Sua planilha deve ter a seguinte aparência -

Você pode ver que as células na coluna C são nomeadas conforme as células correspondentes na coluna D.

Etapa 2: criar a tabela de dados

  • Digite a lista de valores, ou seja, taxas de juros que você deseja substituir na célula de entrada da coluna E da seguinte forma -

    Como você pode observar, há uma linha vazia acima dos valores da taxa de juros. Esta linha é para as fórmulas que você deseja usar.

  • Digite a primeira função (PMT) na célula, uma linha acima e uma célula à direita da coluna de valores. Digite as outras funções (CUMIPMT and CUMPRINC) nas células à direita da primeira função.

    Agora, as duas linhas acima dos valores da taxa de juros são as seguintes -

    A Tabela de Dados se parece com a seguinte -

Etapa 3: faça a análise com a ferramenta Tabela de dados de análise de variações hipotéticas

  • Selecione o intervalo de células que contém as fórmulas e os valores que deseja substituir, ou seja, selecione o intervalo - E2: H13.

  • Clique na guia DADOS na faixa de opções.

  • Clique em Análise de variações hipotéticas no grupo Ferramentas de dados.

  • Selecione Tabela de dados na lista suspensa.

Data Table a caixa de diálogo aparece.

  • Clique no ícone na caixa da célula de entrada da coluna.
  • Clique na célula Interest_Rate, que é C2.

Você pode ver que a célula de entrada Coluna é considerada $ C $ 2. Clique OK.

A tabela de dados é preenchida com os resultados calculados para cada um dos valores de entrada, conforme mostrado abaixo -

Se você pode pagar um EMI de 54.000, pode observar que a taxa de juros de 12,6% é adequada para você.

Tabelas de dados de duas variáveis

Uma tabela de dados de duas variáveis ​​pode ser usada se você quiser ver como valores diferentes de duas variáveis ​​em uma fórmula irão alterar os resultados dessa fórmula. Em outras palavras, com uma tabela de dados de duas variáveis, você pode determinar como alterar duas entradas altera uma única saída. Você entenderá isso com a ajuda de um exemplo.

Example

Existe um empréstimo de 50.000.000. Você deseja saber como as diferentes combinações de taxas de juros e prazos de empréstimos afetarão o pagamento mensal (EMI).

Análise com tabela de dados de duas variáveis

A análise com a tabela de dados de duas variáveis ​​deve ser feita em três etapas -

Step 1 - Defina o fundo necessário.

Step 2 - Crie a tabela de dados.

Step 3 - Realize a análise.

Etapa 1: definir o plano de fundo necessário

  • Suponha que a taxa de juros seja de 12%.

  • Liste todos os valores necessários.

  • Nomeie as células que contêm os valores, para que a fórmula tenha nomes em vez de referências de células.

  • Defina o cálculo para EMI com a função Excel - PMT.

Sua planilha deve ter a seguinte aparência -

Você pode ver que as células na coluna C são nomeadas conforme as células correspondentes na coluna D.

Etapa 2: criar a tabela de dados

  • Tipo =EMI na célula F2.

  • Digite a primeira lista de valores de entrada, ou seja, taxas de juros na coluna F, começando com a célula abaixo da fórmula, ou seja, F3.

  • Digite a segunda lista de valores de entrada, ou seja, número de pagamentos na linha 2, começando com a célula à direita da fórmula, ou seja, G2.

    A tabela de dados tem a seguinte aparência -

Faça a análise com a Tabela de dados da ferramenta de análise de variações hipotéticas

  • Selecione o intervalo de células que contém a fórmula e os dois conjuntos de valores que deseja substituir, ou seja, selecione o intervalo - F2: L13.

  • Clique na guia DADOS na faixa de opções.

  • Clique em Análise de variações hipotéticas no grupo Ferramentas de dados.

  • Selecione Tabela de dados na lista suspensa.

A caixa de diálogo Tabela de dados é exibida.

  • Clique no ícone na caixa da célula de entrada de linha.
  • Clique na célula NPER, que é C3.
  • Novamente, clique no ícone na caixa da célula de entrada de linha.
  • Em seguida, clique no ícone na caixa da célula de entrada da coluna.
  • Clique na célula Interest_Rate, que é C2.
  • Novamente, clique no ícone na caixa da célula de entrada da coluna.

Você verá que a célula de entrada Linha é considerada $ C $ 3 e a célula de entrada Coluna é considerada $ C $ 2. Clique OK.

A tabela de dados é preenchida com os resultados calculados para cada combinação dos dois valores de entrada -

Se você pode pagar um EMI de 54.000, a taxa de juros de 12,2% e 288 EMIs são adequados para você. Isso significa que o prazo do empréstimo seria de 24 anos.

Cálculos da tabela de dados

As tabelas de dados são recalculadas sempre que a planilha que as contém é recalculada, mesmo que não tenham sido alteradas. Para acelerar os cálculos em uma planilha que contém uma Tabela de Dados, você precisa alterar as opções de cálculo paraAutomatically Recalculate a planilha, mas não as Tabelas de dados, conforme fornecido na próxima seção.

Acelerando os cálculos em uma planilha

Você pode acelerar os cálculos em uma planilha contendo Tabelas de Dados de duas maneiras -

  • Nas opções do Excel.
  • Da faixa de opções.

Das opções do Excel

  • Clique na guia ARQUIVO na faixa de opções.
  • Selecione Opções na lista do painel esquerdo.

A caixa de diálogo Opções do Excel é exibida.

  • No painel esquerdo, selecione Formulas.

  • Selecione a opção Automatic except for data tables debaixo Workbook Calculationna seção Opções de cálculo. Clique OK.

Da fita

  • Clique na guia FÓRMULAS na Faixa de Opções.

  • Clique no Calculation Options no grupo Cálculos.

  • Selecione Automatic Except for Data Tables na lista suspensa.