Embora a longa lista de funções do Excel seja um dos recursos mais atraentes do aplicativo de planilhas da Microsoft, existem algumas preciosidades subutilizadas que aprimoram essas funções. Uma ferramenta frequentemente negligenciada é a Análise de variações hipotéticas.

A ferramenta Análise^{(What-If Analysis)} de variações hipotéticas do Excel é dividida em três componentes principais. A parte discutida aqui é o poderoso recurso Goal Seek que permite que você trabalhe de trás para frente a partir de uma função e determine as entradas necessárias para obter a saída desejada de uma fórmula em uma célula. Continue lendo para saber como usar a ferramenta de busca de metas de análise de variações hipotéticas do Excel.^{(What-If Analysis Goal Seek)}

Exemplo de ferramenta de busca de metas do Excel

Suponha que você queira fazer um empréstimo hipotecário para comprar uma casa e está preocupado com a forma como a taxa de juros do empréstimo afetará os pagamentos anuais. O valor da hipoteca é de $ 100.000 e você pagará o empréstimo ao longo de 30 anos.

Usando a função PMT do Excel , você pode facilmente descobrir quais seriam os pagamentos anuais se a taxa de juros fosse 0%. A planilha provavelmente ficaria assim:

A célula em A2 representa a taxa de juros anual, a célula em B2 é a duração do empréstimo em anos e a célula em C2 é o valor do empréstimo hipotecário. A fórmula em D2 é:

=PMT(A2,B2,C2)

e representa os pagamentos anuais de uma hipoteca de $ 100.000 por 30 anos com 0% de juros. Observe^(Notice) que o valor em D2 é negativo, pois o Excel assume que os pagamentos são um fluxo de caixa negativo de sua posição financeira.

Infelizmente, nenhum credor hipotecário vai emprestar $ 100.000 a 0% de juros. Suponha que^(Suppose) você faça alguns cálculos e descubra que pode pagar $ 6.000 por ano em pagamentos de hipoteca. Agora você está se perguntando qual é a taxa de juros mais alta que você pode assumir para o empréstimo para garantir que não acabe pagando mais de US $ 6.000 por ano.

Muitas pessoas nessa situação simplesmente começariam a digitar números na célula A2 até que o valor em D2 chegasse a aproximadamente US$ 6.000. No entanto, você pode fazer com que o Excel faça o trabalho para você usando a ferramenta Busca de meta de análise de variações hipotéticas. ^{(Analysis Goal Seek)}Essencialmente, você fará o Excel trabalhar para trás a partir do resultado em D4 até chegar a uma taxa de juros que satisfaça seu pagamento máximo de $ 6.000.

Comece clicando na guia Dados na faixa de ^(Data)opções^(Ribbon) e localizando o botão Análise de variações hipotéticas na seção ^{(What-If Analysis)}Ferramentas de dados^{(Data Tools)} . Clique no botão Análise de^{(What-If Analysis)} variações hipotéticas e escolha Meta Buscar^{(Goal Seek)} no menu.

O Excel abre uma pequena janela e solicita que você insira apenas três variáveis. A variável Set Cell deve ser uma célula que contém uma fórmula. Em nosso exemplo aqui, é D2 . A variável Para Valor^{(To Value)} é o valor que você deseja que a célula em D2 esteja no final da análise.

Para nós, é -6.000 . Lembre-se de que o Excel vê os pagamentos como um fluxo de caixa negativo. A variável Alterando a célula^{(By Changing Cell)} é a taxa de juros que você deseja que o Excel encontre para você, de modo que a hipoteca de $ 100.000 custe apenas $ 6.000 por ano. Então, use a célula A2 .

Clique no botão OK e você pode notar que o Excel pisca vários números nas respectivas células até que as iterações finalmente convergem em um número final. No nosso caso, a célula em A2 deve agora ler cerca de 4,31%.

Essa análise nos diz que, para não gastar mais de US$ 6.000 por ano em uma hipoteca de US$ 100.000 por 30 anos, você precisa garantir o empréstimo em não mais que 4,31%. Se você quiser continuar fazendo análises hipotéticas, você pode tentar diferentes combinações de números e variáveis ​​para explorar as opções que você tem ao tentar garantir uma boa taxa de juros em uma hipoteca.

A ferramenta de busca de metas de análise de variações hipotéticas^{(What-If Analysis Goal Seek)} do Excel é um complemento poderoso para as várias funções e fórmulas encontradas em uma planilha típica. Trabalhando para trás a partir dos resultados de uma fórmula em uma célula, você pode explorar as diferentes variáveis ​​em seus cálculos com mais clareza.

Using Excel’s What-If Analysis Goal Seek Tool

Although Excel’s long list of functions is onе of the most enticing features of Microsoft’s spreadsheet application, thеre a fеw underutilized gems that enhance these functions. One often-overlooked tоol is the What-Іf Αnalysis.

Excel’s What-If Analysis tool is broken down into three main components. The part discussed here is the powerful Goal Seek feature that lets you work backwards from a function and determine the inputs necessary to get the desired output from a formula in a cell. Read on to learn how to use Excel’s What-If Analysis Goal Seek tool.

Excel’s Goal Seek Tool Example

Suppose that you want to take out a mortgage loan to buy a house and you are concerned about how the interest rate on the loan will affect the yearly payments. The amount of the mortgage is $100,000 and you will pay back the loan over the course of 30 years.

Using Excel’s PMT function, you can easily figure out what the yearly payments would be if the interest rate were 0%. The spreadsheet would likely look something like this:

The cell at A2 represents the yearly interest rate, the cell at B2 is the length of the loan in years, and the cell at C2 is the amount of the mortgage loan. The formula in D2 is:

=PMT(A2,B2,C2)

and represents the yearly payments of a 30-year, $100,000 mortgage at 0% interest. Notice that the figure in D2 is negative since Excel assumes that the payments are a negative cash flow from your financial position.

Unfortunately, no mortgage lender is going to lend you $100,000 at 0% interest. Suppose you do some figuring and find out that you can afford to pay back $6,000 per year in mortgage payments. You are now wondering what is the highest interest rate you can take on for the loan to make sure you don’t end up paying more than $6,000 per year.

Many people in this situation would simply start typing numbers in cell A2 until the figure in D2 reached approximately $6,000. However, you can make Excel do the work for you by using the What-If Analysis Goal Seek tool. Essentially, you will make Excel work backwards from the result in D4 until it arrives at an interest rate that satisfies your maximum payout of $6,000.

Begin by clicking on the Data tab on the Ribbon and locating the What-If Analysis button in the Data Tools section. Click on the What-If Analysis button and choose Goal Seek from the menu.

Excel opens up a small window and asks you to input only three variables. The Set Cell variable must be a cell that contains a formula. In our example here, it is D2. The To Value variable is the amount you want the cell at D2 to be at the end of the analysis.

For us, it is -6,000. Remember that Excel sees payments as a negative cash flow. The By Changing Cell variable is the interest rate you want Excel to find for you so that the $100,000 mortgage will cost you only $6,000 per year. So, use cell A2.

Click the OK button and you may notice that Excel flashes a bunch of numbers in the respective cells until the iterations finally converge on a final number. In our case, the cell at A2 should now read about 4.31%.

This analysis tells us that in order not to spend more than $6,000 per year on a 30-year, $100,000 mortgage, you need to secure the loan at no more than 4.31%. If you want to continue doing what-if analyses, you can try different combinations of numbers and variables to explore the options you have when trying to secure a good interest rate on a mortgage.

Excel’s What-If Analysis Goal Seek tool is a powerful complement to the various functions and formulas found in the typical spreadsheet. By working backwards from the results of a formula in a cell, you can explore the different variables in your calculations more clearly.

Related posts

• Como usar a análise de variações hipotéticas do Excel

• Como usar o recurso Speak Cells do Excel

• 2 maneiras de usar a função de transposição do Excel

• Como entender a análise de variações hipotéticas no Microsoft Excel

• Como excluir linhas em branco no Excel

• 3 maneiras de dividir uma célula no Excel

• 4 maneiras de usar uma marca de seleção no Excel

• Agrupar linhas e colunas em uma planilha do Excel

• Classificação básica de dados de uma coluna e várias colunas em planilhas do Excel

• Conectando o Excel ao MySQL

• Como escrever uma fórmula/instrução SE no Excel

• Como remover linhas de grade no Excel

• Como inserir CSV ou TSV em uma planilha do Excel

• Soletrar rapidamente números no Word e Excel

• Como pesquisar no Excel

• Como fazer um gráfico de pizza no Excel

• Como mesclar dados em vários arquivos do Excel

• Como remover linhas duplicadas no Excel

• Como usar o preenchimento do Flash no Excel

• Como inserir uma planilha do Excel em um documento do Word