Nessa
lição criaremos uma tabela de dados baseada em uma única
variável e utilizaremos o Excel para fazer uma análise de
hipóteses, variando o valor dessa variável.
Exemplo
proposto: Vamos utilizar um exemplo que é clássico para
simulações de uma variável. Esse exemplo é utilizado em
diversos livros de Excel que tratam sobre simulações.
Imagine que você está analisando a hipótese de um empréstimo
para aquisição da casa própria. Nesse caso você fará um
empréstimo de R$ 150.000,00, em 30 anos e quer calcular o
valor da prestação mensal, com base em diferentes taxas de
juros oferecidas pelo mercado.
Exemplo
07: Nesse exemplo vamos fazer uma análise de cenário,
para cálculo do valor da prestação mensal de um empréstimo
com prazo de 30 anos – 360 meses. Usaremos os dados da
planilha C:\ExcelAvancado\Modulo 4 - Exercício 07.xls,
indicada na Figura 4.42:
Figura
4.42 – Dados para a análise de hipóteses.
1. Abra o Excel.
2. Abra a planilha
C:\ExcelAvancado\Modulo 4 – Exercício 07.xls.
3. Na Célula C3 digite
o valor do empréstimo desejado. No nosso exemplo digite
150000.
4. A taxa de juros é a
chamada Variável de Entrada, ou seja, para cada valor
diferente da Taxa de Juros teremos um valor diferente para o
valor da prestação. Para calcular o valor da prestação
vamos utilizar a função PGTO, a qual foi vista no Curso Básico
de Excel em 120 lições.
5. Para fazer a simulação,
devemos colocar a função de cálculo em uma célula qualquer
da planilha. Essa célula servirá como referência para o
Excel, para que ele saiba quais os cálculos devem ser
efetuados com os diferentes valores de entrada. Apenas para
recordar, a função PGTO recebe três parâmetros: A taxa
mensal de juros, o prazo em meses e o valor do empréstimo. Na
nossa planilha, as taxas estão na Coluna B, o prazo é de 360
meses e o valor do empréstimo está na célula C3. Com isso a
função PGTO de referência fica assim:
=PGTO(A6;360;D3)
Digite
essa fórmula na Célula C5. Você deve estar se perguntando:
“O Júlio está louco?”. A6 é uma célula vazia.
Ao invés de A6 não seria B6. Utilizamos uma célula vazia,
acima do primeiro valor de entrada, para que o Excel possa
fazer a simulação. Quando inicia a simulação, o Excel usa
a célula A6 como um local para cálculos temporários, onde
ele coloca a fórmula de referência, calcula o valor para a
primeira taxa de juros e em seguida transporta esse valor para
o local definitivo. Em seguida o Excel usa a próxima taxa de
juros, calcula um novo valor e transporta esse valor para o
destino e assim por diante.
6. Nesse momento o Excel
calcula um valor negativo, pois a célula B5 contém um valor
zero. Dessa maneira a função PGTO irá calcular um valor de
prestação negativo, indicado pelo número em vermelho e
entre parênteses, conforme indicado na Figura 4.43:
Figura
4.43 – Valor de referência.
Agora
estamos aptos a iniciar a nossa simulação.
7. Selecione o intervalo
de células que inclui todos os valores de entrada, mais a célula
de referência. No nosso exemplo selecione o intervalo B5:C13,
conforme indicado na Figura 4.44:
Figura
4.44 – Faixa para a simulação.
8. Selecione o comando Dados
-> Tabela...
9. Será exibida a
janela Tabela, indicada na Figura 4.45:
Figura
4.45 – A janela Tabela.
Nessa
janela temos os seguintes campos:
Célula
de entrada da linha: Insira a referência da célula de
entrada para uma tabela de dados de uma única variável
quando os valores de entrada estiverem em uma linha. Para uma
tabela de dados de duas variáveis, insira referências nessa
caixa e na caixa Célula de entrada da coluna. No nosso
exemplo os valores estão em uma coluna, na coluna B. Deixe
esse campo em branco.
Célula
de entrada da coluna: Insira a referência da célula de
entrada para uma tabela de dados de uma única variável
quando os valores de entrada estiverem em uma coluna. Para uma
tabela de dados de duas variáveis, insira referências nessa
caixa e na caixa Célula de entrada da linha. É o caso do
nosso exemplo, onde os valores de entrada estão na coluna B.
A célula de entrada é o endereço da célula de referência.
Essa é a célula em branco, ao lado dos valores de entrada.
No nosso exemplo é a célula A5. Digite o endereço absoluto
$A$5, conforme indicado na Figura 4.46:
Figura
4.46 – A célula de referência.
10. Clique em OK.
11. Você obterá os resultados
indicados na Figura 4.47:
Figura
4.47 – Cálculos feitos pelo Excel para o valor da prestação.
12. Os valores são negativos pois
representam pagamentos. Altere o valor do empréstimo de
150000 para 100000 e observe que, automaticamente, o Excel
recalcula os valores dos pagamentos, conforme indicado na
Figura 4.48:
Figura
4.48 – Simulação com um novo valor para o empréstimo.
13. Dessa maneira você poderá fazer
diferente simulações, com diferentes valores para o empréstimo.
14. Salve e feche a planilha.
Todos
os direitos reservados: ® Júlio Battisti, 2002