APOSTILA EXCEL AVANCADO

Prévia do material em texto

Curso de 
FINANÇAS CORPORATIVAS E 
CONTROLADORIA
Disciplina: 
Eletrônicas em Finanças 
Professor: André Luiz Pires de Miranda (andre@fumec.br)
 
 
 
 
 
 
 
Curso de MBA (Pós-Graduação) em
FINANÇAS CORPORATIVAS E 
CONTROLADORIA 
 
 
Disciplina: Aplicação de Planilhas 
Eletrônicas em Finanças – Excel 
Avançado 
 
 
 
 
Junho-Julho / 2011 
 
 
 
 
 
 
 
 
 
 
 
André Luiz Pires de Miranda (andre@fumec.br)
Graduação) em 
FINANÇAS CORPORATIVAS E 
Aplicação de Planilhas 
Excel 
André Luiz Pires de Miranda (andre@fumec.br) 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof. André Pires Pág. I-1 
O PROFESSOR 
 
 
Graduado em Tecnologia em Processamento de Dados pela Universidade FUMEC (1990) 
e em Administração, também pela FUMEC (1997), Mestre em Administração - Faculdades 
Integradas de Pedro Leopoldo-MG (2005) e MBA Finanças pelo IBMEC (2008). 
Atualmente é professor e gerente de projetos de informática da Universidade FUMEC. 
Tem experiência na área de Tecnologia da Informação, Educação e Finanças, atuando 
principalmente nos seguintes temas: gestão estratégica e projeto de sistemas, evolução 
da TI, sistemas de apuração de concursos, finanças corporativas, mercado de capitais, 
previdência privada e revisor especializado na área de finanças. Atuou, ainda, como 
professor da disciplina Derivativos, em curso de pós-graduação da Pucminas e Jogos de 
Negócios em cursos de Gestão e Finanças na Fumec. 
 
 
 
 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof. André Pires Pág. I-2 
 
SUMÁRIO 
 
 
Conteúdo 
1. PRIMEIRA PARTE ............................................................................................................................................ 1 
1.1. OPÇÕES do EXCEL .................................................................................................................................... 1 
1.2. FORMATAÇÃO ............................................................................................................................................. 2 
1.3. CONGELAR PAINÉIS .................................................................................................................................. 4 
1.4. FÓRMULAS ABSOLUTAS E RELATIVAS ............................................................................................... 4 
1.5. PROTEGER PLANILHA .............................................................................................................................. 5 
1.6. FUNÇÕES ...................................................................................................................................................... 7 
1.7. FUNÇÕES FINANCEIRAS ........................................................................................................................ 10 
1.8. PLANEJANDO A APOSENTADORIA - I ................................................................................................. 16 
1.9. PLANEJANDO A APOSENTADORIA - II ................................................................................................ 17 
2. SEGUNDA PARTE .......................................................................................................................................... 19 
2.1. BANCO DE DADOS ................................................................................................................................... 19 
2.2. VÍNCULOS COM OUTRAS PLANILHAS OU ARQUIVOS ................................................................... 19 
2.3. PRECISÃO CONFORME EXIBIDO ......................................................................................................... 19 
2.4. ARREDONDAR e TRUNCAR ................................................................................................................... 20 
2.5. NOMES de CÉLULAS ................................................................................................................................ 21 
2.6. FUNÇÃO PROCV ....................................................................................................................................... 22 
2.7. FUNÇÃO PROCV e SE ............................................................................................................................. 22 
2.8. FUNÇÃO PROCV com CONCATENAR .................................................................................................. 23 
2.9. FORMATAÇÃO CONDICIONAL .............................................................................................................. 24 
2.10. SOMASE ...................................................................................................................................................... 24 
2.11. SEERRO ...................................................................................................................................................... 27 
2.12. COMENTÁRIOS EM CÉLULAS ............................................................................................................... 27 
2.13. ATINGIR METAS ........................................................................................................................................ 28 
2.14. SOLVER ....................................................................................................................................................... 29 
2.15. TABELA DINÂMICA ................................................................................................................................... 30 
3. TERCEIRA PARTE ......................................................................................................................................... 33 
3.1. FILTRAR E FILTRO AVANÇADO ............................................................................................................ 33 
3.2. SUBTOTAL .................................................................................................................................................. 36 
3.3. CONSOLIDAR INFORMAÇÕES .............................................................................................................. 37 
3.4. VALIDAÇÃO ................................................................................................................................................ 38 
3.5. ÍNDICES FINANCEIROS ........................................................................................................................... 41 
3.6. PONTO DE EQUILÍBRIO .......................................................................................................................... 42 
3.7. ALAVANCAGEM OPERACIONAL E FINANCEIRA .............................................................................. 44 
3.8. TÉCNICAS DE ANÁLISE DE INVESTIMENTOS .................................................................................. 45 
3.8.1. Período de Pay-Back ............................................................................................................................. 45 
3.8.2. Índice de Lucratividade ......................................................................................................................... 47 
3.8.3. Taxa Interna de Retorno e Taxa Interna de Retorno Modificada ................................................... 48 
3.8.4. Valor Presente Líquido (VPL) .............................................................................................................. 49 
3.9. FORMAÇÃO DE PREÇO .......................................................................................................................... 50 
3.9.1. Exemplo de Cálculo do Mark-UP .......................................................................................................51 
3.9.2. Formação de Preço com Indicadores ............................................................................................... 52 
4. REFERÊNCIAS................................................................................................................................................ 53 
 
 
 
 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof. André Pires Pág. I-3 
 
 
LISTAS DE PLANILHAS 
 
 
PLANILHA TRATAMENTO_HORA_DATA.XLS ................................................................ 3 
FORMATAÇÃO PERSONALIZADA.XLS ........................................................................... 4 
PROTEGER_PLANILHA.XLS ............................................................................................ 5 
FUNÇÃO_SE.XLS .............................................................................................................. 7 
FUNÇÃO_SE_OU_E.XLS .................................................................................................. 9 
FUNÇÕES_FINANCEIRAS.XLS ...................................................................................... 11 
INDICES.XLS .................................................................................................................... 19 
PRECISÃO_CASAS_DECIMAIS.XLS. ............................................................................ 19 
CALCULO_INSS_IRRF.XLS ............................................................................................ 21 
PROCV_E_CONCATENAR.XLS ..................................................................................... 23 
FUNÇÃO_SOMASE.XLS ................................................................................................. 24 
FUNÇÃO_SEERRO.XLS.................................................................................................. 27 
ATINGIR_META_SOLVER.XLS ...................................................................................... 28 
TABELA_DINÂMICA.XLS ................................................................................................ 30 
FILTRAR_SUBTOTAL.XLS ............................................................................................... 33 
CONSOLIDAR.XLS. .......................................................................................................... 37 
VALIDAÇÃO.XLS .............................................................................................................. 38 
FORMAÇÃO_DE_PREÇO.XLS ......................................................................................... 40 
PONTO_DE_EQUILÍBRIO.XLS ......................................................................................... 43 
ALAVANCAGEM_OPER_FIN.XLS .................................................................................... 44 
ANÁLISE_DE_INVESTIMENTOS.XLS .............................................................................. 45 
FORMAÇÃO_DE_PREÇO.XLS ......................................................................................... 50 
 
 
 
 
 
 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 1/57 
1. PRIMEIRA PARTE 
 
� Opções do Excel 
� Formatação 
� Exibição (Congelar Painéis) 
� Fórmulas Absolutas e Relativas 
� Proteger Planilha 
� Funções 
� Funções Financeiras 
� Planejando a Aposentadoria 
 
 
 
1.1. OPÇÕES do EXCEL 
 
 
 
 
 
 
 
 
 
Clicar aqui para 
ir às opções do 
Excel (1º passo) 
Clicar aqui para 
ir às opções do 
Excel (2º passo) 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 2/57 
 
 
 
 
 
 
 
 
 
 
1.2. FORMATAÇÃO 
 
Ao criar uma planilha, formate as células após digitar as informações. Para isso, marque a 
célula ou conjunto de células que deseja formatar e clique no botão direito do mouse. Irá 
aparecer a seguinte janela de opções: 
 
Opções de configuração e 
instalação de suplementos 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 3/57 
 
 
 
Formatação de Data e Hora: 
PLANILHA TRATAMENTO_HORA_DATA.XLS 
 
Dias Ent-1ºHor Saida-1ºHor Tot-1 Ent-2ºHor Saida-2ºHor Tot-2 Total 
Seg 08:00 12:00 04:00 13:00 18:00 05:00 09:00 
Terça 08:00 12:00 04:00 14:00 18:00 04:00 08:00 
Quarta 08:00 12:00 04:00 14:00 18:00 04:00 08:00 
Quinta 08:00 12:00 04:00 14:00 18:00 04:00 08:00 
Sexta 08:00 12:00 04:00 14:00 18:00 04:00 08:00 
 20:00 21:00 
 
 
 
 
 
DIAS Dias/ano Anos 
 1/7/2011 1/9/2011 62 
 
 
 4/5/1980 4/5/2011 11322 365 31,02 
 
 
 
 
 
 
 24/3/2011 15:00 25/3/2011 16:00 25:00:00 Resultado em horas 
 
 
 
 24/3/2011 15:00 25/3/2011 16:00 1,0417 Resultado em dias 
01 hora mais 1/24 avos de 
hora 
Formato de 
hora: [h]:mm:ss 
 
Formato numérico 
 
Formato de 
hora: [h]:mm:ss 
 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 4/57 
Formatação Personalizada 
FORMATAÇÃO PERSONALIZADA.XLS 
 
Utilizando a planilha (Formatação Personalizada.xls), iguale a coluna ANÁLISE à coluna 
do PREÇO UNITÁRIO, por exemplo: em C2, coloque =B2; em C3, coloque =B3; e assim 
por diante. Vamos agora formatar as células da coluna ANÁLISE, considerando que se o 
valor for maior que R$ 200, a célula será mostrada na cor vermelha e com a expressão 
“Caro”. Abaixo deste valor, a célula será mostrada na cor azul e com a expressão 
“Barato”. 
Formato: [vermelho][>200]”Caro”;[azul][<=200]”Barato” 
 
 
1.3. CONGELAR PAINÉIS 
 
Procedimento para fixar linha de cabeçalho e/ou coluna de referência à esquerda, 
enquanto as linhas e colunas de dados são giradas. 
 
Utilizando como exemplo a mesma planilha Tratamento_hora_data.xls, posicione na 
célula B3, vá em barra de opções, escolha EXIBIÇÃO e CONGELAR PAINÉIS. 
 
 
 
 
Escolha umas das opções disponíveis: congelar painéis, congelar linha superior, congelar 
primeira coluna. 
 
 
 
1.4. FÓRMULAS ABSOLUTAS E RELATIVAS 
 
Quando copiamos uma fórmula =A1*B1 para as linhas abaixo, em uma planilha, o Excel 
entende que as fórmulas devem ser relativas ou seja, mudam de acordo com as 
linhas. No exemplo usado =A1*B1 sendo relativas, mudam para =A2*B2, =A3*B3 e assim 
por diante. 
 
Mas quando um determinado endereço deve permanecer absoluto, antes de copiar deve-
se usar o símbolo $ na fórmula que queremos copiar. 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 5/57 
Ex.: Na fórmula =A1*B1 queremos que quando copiar para as linhas abaixo o 
endereço B1 permaneça absoluto, ou fixo: 
 =A1*$B$1 
 Note que o símbolo $ está antes do número 1 que identifica a linha. Para fixar a célula, 
posicione antes dela e aperte a tecla F4. 
 
Depois de copiado, as fórmulas ficam assim: 
 
=A2*$B$1 
=A3*$B$1 
=A4*$B$1 
=A5*$B$1 
 
Quando copiamos uma fórmula para outras colunas, a regra é a mesma, ou seja, deve-se 
colocar o símbolo $ na fórmula à frente da letra que identifica a coluna que deve 
permanecer fixa. 
 
Usandoo exemplo =A1*A2, quando copiamos para as outras colunas, ficam 
assim: 
 
=B1*B2 
=C1*C2 
=D1*D2 
 
Colocando o símbolo $ antes do A1, =$A$1*A2, depois de copiado, fica assim: 
 
=$A$1*B2 
=$A$1*C2 
=$A$1*D2 
 
Para fixar a coluna e a linha usa-se =$A$1*A2, pois assim, quando esta fórmula for 
copiada para baixo ou para o lado na planilha, o endereço $A$1 sempre ficará fixo. 
 
 
1.5. PROTEGER PLANILHA 
 
Para proteger uma planilha, vá em REVISÃO, PROTEGER PLANILHA. 
 
Para ocultar colunas: marque a coluna ou conjunto de colunas que deseja ocultar, clique 
com o botão direito do mouse, escolha a opção OCULTAR. Se desejar desfazer a 
operação, marque as colunas anteriores e posteriores à(s) coluna(s) ocultada(s), clique 
com o botão direito do mouse, escolha a opção REEXIBIR. 
 
Para ocultar o conteúdo de uma célula, utilize a seguinte opção em FORMATAR 
CÉLULAS: formato personalizado na seguinte forma “;;;” 
 
Exemplo de proteção de uma planilha ocultando a fórmula de uma célula: para esta 
demonstração, utilize a planilha: 
PROTEGER_PLANILHA.XLS 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 6/57 
 
 
 
Objetivo na proteção: proteger a planilha, mantendo oculta a fórmula de cálculo do Montante (VF), 
mas deixando livre a manipulação das demais variáveis: 
 
 
1º Passo: 
 
Configurar o conjunto de células B1:B5, deixando-as livres para manipulação. Marque este conjunto de 
células, clique no botão direito do mouse, marque a opção Formatar Células e escolha a opção Proteção. 
Deixe desmarcado as opções Bloqueadas e Ocultas, e dê OK. 
 
 
 
 
 
2º Passo: 
 
Configurar a célula B6, de modo que a fórmula da célula não fique visível e não permita sua modificação. 
Seguindo o mesmo passo anterior, deixe marcado as opções Bloqueadas e Ocultas e dê OK. 
 
 
 
3º Passo: 
 
Para que as configurações anteriores tenham sentido, será necessário proteger a planilha. Para isto, vá em 
Revisão\Proteger Planilha e introduza uma senha. Com isto, as variáveis do bloco B1:B5 poderão ser 
manipuladas, sem que o usuário visualize e altere a fórmula de cálculo do montante em B6. 
 
 
 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 7/57 
1.6. FUNÇÕES 
 
 
 
 
Função SE (fórmulas condicionais): 
FUNÇÃO_SE.XLS 
Sintaxe padrão: =SE(teste; ação para verdadeiro; ação para falso) 
 
Ex.: a comissão paga aos vendedores, de acordo com a tabela a seguir, que consta na 
planilha função_SE.xls, está condicionada ao volume de vendas atingido, de acordo com 
a seguinte escala: 
Menor ou igual a R$ 15.000 � 10% 
Maior que R$ 15.000 e menor ou igual a R$ 25.000 � 15% 
Maior que R$ 25.000 � 20% 
 
Vendedor Volume de Vendas Comissão (%) 
Pedro R$ 30.000,00 20% 
Ana Paula R$ 21.000,00 15% 
José Augusto R$ 15.000,00 10% 
 
Sintaxe do comando: =SE(B3<=15000;10%;SE(B3<=25000;15%;20%)) 
Abrir janela 
de funções 
B3 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 8/57 
Usando OU e E junto com o SE: 
 
Exemplo para OU junto com o SE: 
 
Se a região for SUL ou NORTE ou LESTE a comissão será de 10%, caso 
contrário será de 5%. 
 
=SE( OU (C9="Sul"; C9="Norte"; C9="Leste");B9*10%;B9*5%) 
 
Exemplo para o E junto com o SE: 
 
Se o total das vendas for entre R$10.000,00 e R$20.000,00, a comissão será de 10%, caso 
contrário será de 5%. 
 
=SE( E (B15>=10000; B15<=20000);B15*10%;B15*5%) 
 
Para a condição ser verdadeira, as opções destacadas em vermelho devem necessariamente 
serem verdadeiras, ou seja o valor deve ser entre R$10.000,00 e R$20.000,00. Diferente do OU 
onde qualquer opção poderia ser verdadeira. 
 
Usando condição encadeada 
 
Usa-se quando houver mais que uma condição para testar. 
No cálculo do INSS deverá ser respeitada uma tabela divulgada pelo governo. 
 
Salário até R$ 1.106,90 o desconto será de 8% ($C$21). 
Salário de R$ 1.106,91 até R$ 1.844,83 o desconto será de 9% ($C$22). 
Salário de R$ 1.844,83 até R$ 3.689,66 o desconto será de 11% ($C$23). 
Salário acima de R$ 3.689,66 o desconto será o valor de R$ 405,86 ($C$24). 
 
Nas células D21:D24 estão os valores de salário para teste: 
 
=SE(D21<=$B$21;D21*$C$21;SE(D21<=$B$22;D21*$C$22;SE(D21<=$B$23;D21*$C$23;$C$24))) 
 
Pode-se colocar até 64 condições encadeadas na versão 2007 do Excel. 
 
Os operadores lógicos são: 
 
> maior 
< menor 
>= maior ou igual 
<= menor ou igual 
= igual 
<> diferente 
 
 
Na planilha a seguir (Função_SE_OU_E.xls), há restrições para definir os produtos em 
promoção e, também, para estabelecer o preço de venda, conforme a seguir: 
 
Quanto à promoção: 
 
Será oferecida para os modelos com Valor de Par superior a R$ 30,00 E se a quantidade for superior a 10 
pares. 
 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 9/57 
FUNÇÃO_SE_OU_E.XLS 
Sintaxe: =SE(E(F5>30;G5>10);"Promoção";"Preço Normal") 
 
 
 
M
o
de
lo
 
Ta
m
an
ho
 
Có
di
go
 
M
ar
ca
 
Co
r 
Va
lo
r 
Pa
r 
Qt
de
 
Va
lo
r 
To
ta
l 
Nº
 
Pe
di
do
 
M
ês
 
Pr
o
m
o
çã
o
 
Pr
eç
o
 
de
 
 
Ve
n
da
 
5 33 533 Azaléia Branca R$ 29,90 8 R$ 239,20 2538 Janeiro Preço Normal R$ 35,88 
5 35 535 Azaléia Azul R$ 29,90 15 R$ 448,50 2538 Janeiro Preço Normal R$ 35,88 
5 38 538 Azaléia Preta R$ 32,90 10 R$ 329,00 2538 Janeiro Preço Normal R$ 39,48 
8 34 834 Bibi Rosa R$ 48,70 15 R$ 730,50 2540 Fevereiro Promoção R$ 56,01 
8 36 836 Bibi Branca R$ 48,70 20 R$ 974,00 2540 Fevereiro Promoção R$ 56,01 
8 33 833 Bibi Branca R$ 23,50 10 R$ 235,00 2540 Fevereiro Preço Normal R$ 28,20 
14 37 1437 Dakota Preta R$ 24,50 8 R$ 196,00 2789 Março Preço Normal R$ 29,40 
14 39 1439 Dakota Marrom R$ 24,50 5 R$ 122,50 2863 Abril Preço Normal R$ 26,95 
14 41 1441 Dakota Beje R$ 25,00 10 R$ 250,00 2863 Abril Preço Normal R$ 27,50 
20 34 2034 Via Marte Vemelha R$ 32,99 14 R$ 461,86 2910 Maio Promoção R$ 36,29 
20 38 2038 Via Marte Verde R$ 32,99 12 R$ 395,88 2910 Maio Promoção R$ 36,29 
23 35 2335 Beira Rio Azul R$ 45,79 25 R$ 1.144,75 2910 Maio Promoção R$ 50,37 
23 38 2338 Beira Rio Branca R$ 46,59 19 R$ 885,21 2538 Janeiro Promoção R$ 53,58 
23 40 2340 Beira Rio Amarela R$ 46,59 10 R$ 465,90 2538 Janeiro Preço Normal R$ 55,91 
38 35 3835 Azaléia Preta R$ 62,30 25 R$ 1.557,50 2540 Fevereiro Promoção R$ 71,65 
38 33 3833 Azaléia Branca R$ 21,29 15 R$ 319,35 2863 Abril Preço Normal R$ 23,42 
38 39 3839 Azaléia Amarela R$ 23,29 16 R$ 372,64 3014 Julho Preço Normal R$ 25,62 
45 34 4534 Picadilly Azul R$ 32,48 13 R$ 422,24 2987 Junho Promoção R$ 35,73 
45 36 4536 Picadilly Preta R$ 32,48 20 R$ 649,60 2987 Junho Promoção R$ 35,73 
45 42 4542 Picadilly Marrom R$ 34,00 8 R$ 272,00 2987 Junho Preço Normal R$ 37,40 
 
 
Quanto ao Preço de Venda Final: 
 
Se os meses forem Janeiro OU Fevereiro OU Março: 
 
 Se não estiver na promoção, preço de venda = valor do par + 20%Se estiver na promoção, preço de venda = valor do par + 15% 
 
 
Para os demais meses, independente se houver promoção ou não, preço de venda = 
valor do par + 10%. 
 
Sintaxe: 
 
=SE(OU(J5="Janeiro";J5="Fevereiro";J5="Março"); 
 SE(K5="Preço Normal";F5+F5*20%; F5+F5*15%);F5+F5*10%) 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 10/57 
1.7. FUNÇÕES FINANCEIRAS 
 
As funções financeiras do Excel são as mesmas utilizadas na calculadora HP 12-C. Ao 
selecionar o botão Fx , encontram-se diversas funções disponíveis: 
 
 
 
Função PAGTO (Valor da Prestação – Na HP, PMT): 
 
Em uma loja compramos um televisor no valor de R$ 1.800,00, nas seguintes condições: 
 
R$ 400,00 à vista e Saldo Devedor de R$ 1.400,00 (VP) 
Restante em 12 parcelas mensais (Nper) 
Taxa de juros da loja: 1,5% ao mês (Taxa) 
 
Calcular o valor da Prestação 
 
 
 
 
Outras Funções Financeiras: TAXA, VP, NPER, VF. 
Tipo(0) – sem entrada 
Tipo(1) – com entrada 
 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 11/57 
Acompanhe os exemplos através da planilha 
FUNÇÕES_FINANCEIRAS.XLS 
 
TAXA DE JUROS EQUIVALENTES: exemplo de conversão de taxa mensal para anual e 
vice-versa: 
 
Tx Efetiva Mensal 0,50% am 
 
 Tx Efetiva Anual Equivalente 6,17% aa 
 
 Tx Efetiva Anual 12,00% aa 
 
 Tx Efetiva Mensal 
Equivalente 0,95% am 
 
Sintaxe de Taxa mensal para Taxa anual =((1+B3)^12)-1 
 
Sintaxe de Taxa anual para Taxa mensal =((1+B7)^(1/12))-1 
 
 
PRAZO OU NÚMERO DE PERÍODOS (NPER): exemplo de cálculo do número de 
períodos em uma aplicação, de acordo com taxa definida e retorno esperado: 
 
Aplicação (10.000,00) R$ PV 
 Resgate 15.000,00 R$ FV 
 Taxa de Juro 1,00% am i 
 Função NPER 40,75 meses n 
 
Sintaxe: =NPER(B6;0;B4;B5) 
 
VALOR DA PRESTAÇÃO (PMT): exemplo de cálculo da prestação sobre financiamento 
(saldo devedor) e, em outro exemplo, considerando o valor futuro (VF). 
 
PRESTAÇÕES - SÉRIES UNIFORMES POSTECIPADAS 
 
 Valor à Vista 
 
 500.000,00 
 Entrada 
 
 400.000,00 
 Valor Financiado 
 
 100.000,00 PV 
 Taxa de Juro a.m 0,60% i 
 No. Prestações mensais 
 
 24 n 
 Função PGTO (Prestação) (4.486,33) PMT 
B3 
B7 
B4 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 12/57 
 PRESTAÇÕES - SÉRIES UNIFORMES POSTECIPADAS 
 
 
 Valor Futuro 
 
 100.000,00 FV 
 Taxa de Juro am 
 
0,60% i 
 No. Prestações mensais 
 
 24 n 
 Função PGTO (Prestação) (3.886,33) PMT 
 
 
 
 
 
VALOR PRESENTE (VP): exemplos de cálculo do valor presente de um montante 
aplicado e, também, considerando uma série de pagamentos. 
 
 
 
VALOR PRESENTE (de um Pagamento Único - Montante) 
 
 Montante 1.000,00 R$ FV 
 Prazo 24 meses n 
 Taxa 1,20% am i 
 
 
 
 Função VP (751,05) 
 
 PV 
 
 
 
 
 VALOR PRESENTE DE UMA SÉRIE UNIFORME 
POSTECIPADA 
 
 
 
 Prestações 80,00 R$ PMT 
 Prazo 24 meses n 
 Taxa 1,20% am i 
 
 
 
 Função VP (1.659,68) PV 
 
 
 
 
 
 
 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 13/57 
VALOR FUTURO (VF): exemplo de cálculo de montante após um período de acumulação 
a taxa determinada e depósito periódico: 
 
VALOR FUTURO (Montante acumulado) 
 
 
 Depósito de abertura (1.000,00) R$ VP 
 Depósito mensal (200) PMT 
 Prazo 60 meses n 
 Taxa 0,80% am i 
 Função VF (Montante) R$ 16.937,76 
 
 VF 
 
Sintaxe: =VF(B6;B5;B4;B3;0) 
 
 
TAXA DE JUROS (TAXA%): exemplo de cálculo da taxa de juros sobre um montante 
aplicado e financiamento de saldo devedor: 
 
TAXA DE JUROS COMPOSTOS Montante - Capital 
 
 
 
 
 Aplicação (20.000,00) R$ PV 
 Resgate 28.000,00 R$ FV 
 Prazo 18 meses n 
 Função TAXA 1,89% am 
 
Sintaxe: =TAXA(B5;;B3;B4) 
 
 
 TAXA DE JUROS Prestação - Capital 
 
 Aquisição de: Veículo 
 Valor à Vista 40.000,00 R$ 
 Entrada 31.000,00 R$ 
 
 Valor Financiado 9.000,00 R$ PV 
 No. Prestações 24 meses n 
 Prestação (432,00) R$ PMT 
 Função TAXA 1,16% am i 
Sintaxe: =TAXA(B15;B16;B14;;0) 
B3 
B3 
B14 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 14/57 
Financiamento PRICE e Financiamento SAC 
 
A tabela Price é caracterizada pela utilização de juros compostos e também pelo valor 
fixo da parcela. É uma das mais conhecidas e utilizadas, embora seja a mais onerosa 
para o consumidor. 
 
A tabela SAC (Sistema de Amortização Constante) é caracterizada pela amortização 
constante do saldo devedor e também pelo valor decrescente da parcela. Embora seja 
uma alternativa melhor que a tabela Price, ela é menos utilizada devido ao fato de suas 
prestações serem maiores no começo. 
 
Verifique as diferenças nos exemplos a seguir e acompanhe as fórmulas de cálculo 
através da planilha Funções_Financeiras.xls. 
 
 
 
FINANCIAMENTO PRICE 
 
Taxa de juros 
 
1,0% am 
Prestações 
 
10 meses 
Valor do 
empréstimo 25.000,00 
Meses Saldo Devedor Juros 
 
Amortização 
 
Prestação 
0 25.000,00 - 
 
- 
 
- 
1 22.610,45 250,00 2.389,55 2.639,55 
2 20.197,00 226,10 2.413,45 2.639,55 
3 17.759,42 201,97 2.437,58 2.639,55 
4 15.297,46 177,59 2.461,96 2.639,55 
5 12.810,88 152,97 2.486,58 2.639,55 
6 10.299,44 128,11 2.511,44 2.639,55 
7 7.762,88 102,99 2.536,56 2.639,55 
8 5.200,96 77,63 2.561,92 2.639,55 
9 2.613,42 52,01 2.587,54 2.639,55 
10 0,00 26,13 2.613,42 2.639,55 
 Total 1.395,52 25.000,00 26.395,52 
 
 
 
 
 
 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 15/57 
FINANCIAMENTO SAC 
 
Taxa de juros 
 
1,0% am 
Prestações 
 
10 meses 
Valor do 
empréstimo 25.000,00 
Meses Saldo Devedor Juros 
 
Amortização 
 
Prestação 
0 25.000,00 
 
- 
 
- 
 
- 
1 22.500,00 250,00 2.500,00 2.750,00 
2 20.000,00 225,00 2.500,00 2.725,00 
3 17.500,00 200,00 2.500,00 2.700,00 
4 15.000,00 175,00 2.500,00 2.675,00 
5 12.500,00 150,00 2.500,00 2.650,00 
6 10.000,00 125,00 2.500,00 2.625,00 
7 7.500,00 100,00 2.500,00 2.600,00 
8 5.000,00 75,00 2.500,00 2.575,00 
9 2.500,00 50,00 2.500,00 2.550,00 
10 0,00 25,00 2.500,00 2.525,00 
 Total 1.375,00 25.000,00 26.375,00 
 
 
 
 
 
Cálculo de Taxa Líquida ou Taxa Real: exemplo a seguir considerandoimpostos e o 
indexador IGPM: 
 
TAXA REAL LÍQUIDA 
 
 Taxa Aparente "Nominal" Taxa de Inflação Taxa 
Real 
Líquida Taxa 
Bruta 
 
Imposto 
 Taxa Líquida 
(efetiva) IGPM 
14,00% 1,00% 13,86% 5,00% 8,44% 
 =A5*(1-B5) =(1+C5)/(1+D5)-1 
 
 Taxa real �
	
��
� �������
	
��
� �� �����çã�
� 1 
A5 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 16/57 
1.8. PLANEJANDO A APOSENTADORIA - I 
 
Como é de conhecimento de todos, se quisermos manter um padrão de vida na 
aposentadoria, similar àquele que viermos a alcançar no período de vida produtivo, torna-
se necessário planejar uma reserva financeira para complementação da aposentadoria do 
INSS ou outras fontes de renda, se for o caso. 
 
Veja, a seguir, utilizando a mesma planilha exemplo anterior, como calcular o tempo e o 
valor da contribuição, para que se garanta um benefício mensal a partir de uma 
determinada idade: 
 
Detalhes para preenchimento desta planilha: a) as células de fundo laranja são variáveis 
que você pode alterar para simular as diversas situações desejadas; b) as células de 
fundo branco são fórmulas resultantes das variáveis indicadas nas células de fundo 
laranja. 
 
 
PLANO DE PREVIDÊNCIA PRIVADA - I 
(complementação de aposentadoria) 
 Idade de ingresso no Plano 20 
 Ida de saída do Plano 60 
 Expectativa de Vida 85 
 Meses de Contribuição 480 
 Benefício desejado R$ 2.000,00 
 Meses com direito ao benefício após a idade de saída 300 
 Rentabilidade esperada (%am) 0,50% 
 Reserva necessária para garantir o benefício (310.413,73) 
 Contribuição mensal para garantir a reserva R$ 155,87 
 
No exemplo anterior, foi simulada a situação de uma pessoa de 20 anos, que pretende 
acumular uma reserva financeira e, a partir dos 60 anos, projetando uma expectativa de 
mais 25 anos de vida, iniciar o recebimento de um benefício no valor de R$ 2.000,00, 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 17/57 
considerando uma taxa de juros conservadora de 0,5%am. Aqui não foram considerados 
outros descontos, por exemplo, taxa de administração, taxa de carregamento, etc. Esta é 
uma modalidade típica de acúmulo de reserva financeira através da tradicional poupança. 
Dentro destas condições, para atingir o benefício desejado, esta pessoa deverá contribuir, 
mensalmente, com o valor de R$ 155,87. 
 
Faça uma simulação, mantendo-se as mesmas condições e considerando a taxa de juros 
em 0,6%. Observe bem a ação dos juros compostos ao longo do tempo. Simule, também, 
outras situações mais próximas da sua realidade. 
 
 
1.9. PLANEJANDO A APOSENTADORIA - II 
 
Em se tratando de planejamento de aposentadoria que, via de regra, envolve um longo 
tempo, há várias opções de investimento. Além da tradicional poupança, pode-se optar 
por alternativas que possibilitem taxas de retornos mais interessantes, não obstante o 
risco e eventuais taxas de administração, carregamento, etc, por exemplo: CDB, fundos 
de pensão, títulos públicos, fundos multimercados, fundos imobiliários, fundos de ações, 
clubes de investimento, etc. 
 
A seguir, a demonstração de uma planejamento de aposentadoria através de um fundo de 
pensão que, evidentemente, retém percentual relativo à taxa de administração e 
carregamento: 
 
PLANO DE PREVIDÊNCIA PRIVADA - II (complementação de 
aposentadoria) 
 Rentabilidade anual (%aa) 6,50% 
 Taxa de administração contratual (%aa) 2,00% 
 Rentabilidade líquida (%aa) 4,50% 
 Taxa de carregamento (contratual) 1,00% 
 Idade de ingresso no Plano 25 
 Ida de saída do Plano 55 
 Expectativa de Vida 80 
 Meses de Contribuição 360 
 Benefício desejado R$ 5.000,00 
 Meses com direito ao benefício após a idade de saída 300 
 Rentabilidade mensal esperada (%am) 0,37% 
 Reserva necessária para garantir o benefício (R$ 907.896,62) 
 Aporte inicial R$ 50.000,00 
 Aporte inicial projetado (R$ 187.265,91) 
 Reserva líquida para garantir o benefício (R$ 720.630,71) 
 Contribuição mensal para garantir a reserva sem carregamento R$ 964,62 
 Contribuição mensal para garantir a reserva com carregamento R$ 974,36 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 18/57 
Os detalhes de preenchimento desta planilha são idênticos ao modelo de aposentadoria-I. 
 
Aspectos a serem observados: 
 
� Rentabilidade anual: taxa de rentabilidade anual esperada para o investimento. 
� Taxa de administração: cobrada anualmente pela administradora do plano 
� Rentabilidade líquida = (rentabilidade anual – taxa de administração) 
� Taxa de carregamento: taxa de desconto sobre cada parcela de contribuição depositada 
para constituição da reserva financeira. Normalmente inicia em um patamar mais alto (+ou- 
4/5%) e vai sendo reduzida à medida que a reserva financeira aumenta, tendendo a zero. 
� Rentabilidade mensal esperada: é a rentabilidade líquida anual convertida para mensal, 
da seguinte forma: (1+RLanual)^^(1/12)-1 
� Reserva necessária para garantir o benefício: total da reserva financeira, a valor 
presente, para garantir o pagamento do benefício desejado no tempo estimado. 
� Aporte inicial: se a situação permitir, pode-se iniciar o planejamento com um depósito 
inicial, que será subtraído da reserva total, antes do cálculo do valor de contribuição. 
� Aporte inicial projetado: valor do aporte inicial projetado de acordo com a rentabilidade 
mensal esperada e os meses de contribuição. 
� Reserva líquida para garantir o benefício = reserva necessária – aporte inicial projetado. 
� Contribuição mensal para garantir a reserva sem carregamento: cálculo do valor da 
contribuição mensal, para constituição da reserva financeira, sem considerar a taxa de 
carregamento. 
� Contribuição mensal para garantir a reserva com carregamento: cálculo do valor da 
contribuição mensal, para constituição da reserva financeira, considerando a taxa de 
carregamento. 
 
Simule outras situações de acordo com a sua realidade. Observe que, quanto mais cedo 
a pessoa iniciar o seu planejamento, menor será o valor mensal da contribuição. 
 
 
 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 19/57 
2. SEGUNDA PARTE 
 
� Banco de dados 
� Vínculos com outras planilhas 
� Precisão numérica (precisão conforme exibido) 
� Nomes de células 
� PROCV 
� PROCV e SE 
� PROCV com CONCATENAR 
� Formatação Condicional 
� SOMASE 
� SEERRO 
� Comentários em Células 
� Atingir Meta 
� Solver 
� Tabela Dinâmica 
 
 
2.1. BANCO DE DADOS 
 
O banco de dados do Excel não é nenhuma função específica e sim trata-se das 
informações constantes organizadas por colunas, sendo a primeira linha o nome dos 
campos do banco de dados. 
 
Código Nome Endereço Cidade Cep Estado Telefone 
 
 
 
 
 
Com as informações digitadas no banco de dados, podemos usá-las para vários fins, 
como uma mala direta, tabela dinâmica, pesquisa, etc. 
 
 
 
2.2. VÍNCULOS COM OUTRAS PLANILHAS OU ARQUIVOS 
 
Podemos vincular uma célula a uma outra célulalocalizada em outra planilha ou arquivo. 
Ver exemplo na planilha 
INDICES.XLS 
 
 
2.3. PRECISÃO CONFORME EXIBIDO 
 
No Excel os cálculos são feitos com todas as casas depois da vírgula. Em uma soma 
de células, onde os valores estão formatados como 9.999,99, são consideradas todas 
as casas. Observe o exemplo, através da planilha: 
PRECISÃO_CASAS_DECIMAIS.XLS. 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 20/57 
valor1 valor2 mult valor interno 
3,6659980000 8,5587800000 31,38 31,3764703624 
2,2255000000 5,2554500000 11,70 11,6960039750 
2,5587700000 2,3655600000 6,05 6,0529239612 
3,3656560000 33,2233300000 111,82 111,8182999545 
45,2545400000 5,5554540000 251,41 251,4095152612 
 412,35 412,35 
 
Se marcarmos a opção “Definir Precisão Conforme Exibido”, o resultado da soma será 
412,36, considerando duas casas decimais, conforme exibido a seguir: 
 
valor1 valor2 mult valor interno 
3,6659980000 8,5587800000 31,38 31,3800000000 
2,2255000000 5,2554500000 11,70 11,7000000000 
2,5587700000 2,3655600000 6,05 6,0500000000 
3,3656560000 33,2233300000 111,82 111,8200000000 
45,2545400000 5,5554540000 251,41 251,4100000000 
 412,36 412,36 
 
Para configurar esta opção: vá em Opções do Excel, guia Avançado: 
 
 
 
 
2.4. ARREDONDAR e TRUNCAR 
 
A função ARRED arredonda o resultado de uma operação para quantas casas for 
necessário. Para isto, basta incluir na fórmula esta função com a seguinte sintaxe: 
=ARRED(B1*B2;2). 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 21/57 
Na função TRUNCAR, ignora-se os dígitos restantes após os 2 dígitos depois da vírgula: 
=TRUNCAR(B1*B2;2). Veja a seguir a diferença: 
 
Valor-1 199,99990000 
 
Valor2 5,87900000 
 
Multiplicação sem arredondamento 1.175,79941210 
 
Multiplicação com arredondamento de 2 casas 1.175,80000000 (*) 
Multiplicação com truncamento de 2 casas 1.175,79000000 (**) 
 
(*) =ARRED(B1*B2;2) 
(**) =TRUNCAR(B1*B2;2) 
 
 
2.5. NOMES de CÉLULAS 
CALCULO_INSS_IRRF.XLS 
 
Em uma planilha podemos selecionar uma ou um conjunto de células e lhe dar um nome. Este 
procedimento facilita o uso de outras funções, como o PROCV, que será visto a seguir. Será 
utilizado, neste exemplo, a planilha Calculo_INSS_IRRF.xls. Nesta tabela, será dado nome ao 
conjunto equivalente à tabela do INSS, que compreende o intervalo A10:D12. 
 
Para este procedimento, marque o conjunto de células (A9:D12), vá na guia Fórmulas e, em 
seguida, clique em Definir Nomes. No campo Nome, coloque a identificação “inss”. Repita o 
procedimento e dê o nome de “irrf” à tabela do irrf. 
 
 
 
sb inss irrf 
 
R$ 900,00 R$ 72,00 R$ 0,00 
R$ 1.600,00 R$ 144,00 R$ 65,40 
R$ 2.500,00 R$ 275,00 R$ 222,15 
R$ 4.000,00 R$ 405,86 R$ 634,65 
R$ 25.000,00 R$ 405,86 R$ 6.409,65 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 22/57 
tabela do inss 
 
lim_inf lim_sup perc Limite 
R$ 0,00 R$ 1.106,90 8,0% R$ 0,00 
R$ 1.106,91 R$ 1.844,83 9,0% R$ 0,00 
R$ 1.844,84 R$ 3.689,66 11,0% R$ 405,86 
 
tabela do irrf 
 
lim_inf lim_sup perc Dedução 
R$ 0,00 R$ 1.164,00 0,0% R$ 0,00 
R$ 1.164,01 R$ 2.326,00 15,0% R$ 174,60 
R$ 2.326,00 R$ 999.999.999,00 27,5% R$ 465,35 
 
 
2.6. FUNÇÃO PROCV 
 
A função PROCV é usada para pesquisar no banco de dados uma informação baseada 
em uma chave de pesquisa. Por exemplo, utilizando-se a planilha anterior, Calculo_ 
INSS_IRRF.xls, pesquise o percentual de incidência do INSS para o salário bruto (sb) no 
valor de R$ 1.600,00: 
 
 
Sintaxe: =PROCV(A3;inss;3) 
 
 
 
 
 
Cálculo do IRRF utilizando PROCV na planilha anterior, considerando: de acordo com o 
valor do salário bruto (sb), aplicar o percentual de incidência e a dedução, se houver. Veja 
o exemplo: 
 
=(A2*PROCV(A2;irrf;3))-PROCV(A2;irrf;4), onde: 
 
A2*PROCV(A2;irrf;3) � salário bruto * percentual da tabela de IRRF 
 
PROCV(A2;irrf;4) � valor da dedução equivalente ao salário bruto pesquisado na 
 tabela de IRRF 
 
 
2.7. FUNÇÃO PROCV e SE 
 
Cálculo do INSS utilizando as funções PROCV e SE, considerando: de acordo com o 
valor do salário bruto (sb), calcular o valor do INSS conforme o percentual da tabela, até o 
limite máximo de R$ 405,86. 
 
=SE((PROCV(A2;inss;3)*A2)<$D$12;PROCV(A2;inss;3)*A2;$D$12) 
 
 
Valor procurado ou CHAVE 
Matriz ou Tabela utilizada 
 
Coluna onde está o índice procurado na tabela inss 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 23/57 
2.8. FUNÇÃO PROCV com CONCATENAR 
PROCV_E_CONCATENAR.XLS 
Quando a chave para pesquisa for mais que uma, por exemplo, um pedido de 
calçados, que para cada tamanho, de um mesmo modelo, existe um preço 
diferente, é necessário usar a função CONCATENAR . 
 
Para este exemplo, utilizar a planilha PROCV_e_CONCATENAR.xls, demonstrada a 
seguir: 
 
 Tabela_aux 
chave modelo tamanho cor valor 
 
1033 10 33 Branco 37,50 
1034 10 34 Preto 41,20 
2033 20 33 Branco 38,41 
2034 20 34 Preto 45,74 
3033 30 33 Branco 50,25 
3034 30 34 Preto 55,00 
 
Note que para um mesmo modelo, mas com tamanhos diferentes, existem chaves 
diferentes. Para o modelo 10 com tamanho 33, a chave é 1033; e para o modelo 
10 com tamanho 34, a chave é 1034. Isto porque, através da função CONCATENAR, 
duas células formaram uma. 
 
Ex.: =CONCATENAR(B2;C2) ou =B2&C2 
 
 
Na planilha (Tabela_Pedidos) a chave de pesquisa é composta pelos elementos modelo e 
tamanho, concatenados na Tabela_aux com o nome chave. Na célula A11 consta o 
modelo e na célula B11 o tamanho. Sendo assim, baseado na chave A11 e B11, 
pesquisar na Tabela_aux a coluna 5 (coluna de valor), considerando que a informação 
deva ser exata: 
=PROCV(A11&B11;tab_aux;5;0) 
 
Tabela_Pedidos 
modelo tamanho cor valor pedido qtd valortotal 
10 33 Branco 37,50 2545 5 187,50 
10 34 Preto 41,20 2546 15 618,00 
20 33 Branco 38,41 2547 4 153,64 
20 34 Preto 45,74 2548 9 411,66 
30 33 Branco 50,25 2549 6 301,50 
30 34 Preto 55,00 2550 7 385,00 
A11 
B11 
A2:E7 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 24/57 
2.9. FORMATAÇÃO CONDICIONAL 
 
Através da guia Início/Formatação Condicional, na guia de opções, pode-se definir uma 
formatação para uma ou mais células. Utilizando-se da mesma planilha anterior, realçar 
com cores fortes todos os valores, da coluna valortotal, menores que 300,00. 
 
 
 
 
 
 
 
 
2.10. SOMASE 
FUNÇÃO_SOMASE.XLS 
Para exemplificar esta função, usar a planilha Função_SOMASE.xls. De um cadastro de 
pedidos, gerar um resumo com o total de pares e valor dos clientes. 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 25/57 
Estrutura da função: 
 
 
Cadastro de Pedidos: 
Cliente Ref Preço Unitário Pares Total (R$) 
Azaléia 27 R$ 3,90 3251 R$ 12.678,90 
Azaléia 10 R$ 5,90 1300 R$ 7.670,00 
Azaléia 10 R$ 8,60 1300 R$ 11.180,00 
Azaléia 17 R$ 4,804200 R$ 20.160,00 
Bibi 26 R$ 8,90 3220 R$ 28.658,00 
Bibi 19 R$ 8,70 2355 R$ 20.488,50 
Bibi 20 R$ 7,90 3450 R$ 27.255,00 
Bibi 28 R$ 2,45 2500 R$ 6.125,00 
Bibi 29 R$ 8,70 154 R$ 1.339,80 
Havaianas 24 R$ 25,00 3620 R$ 90.500,00 
Havaianas 10 R$ 3,60 1350 R$ 4.860,00 
Havaianas 16 R$ 2,50 1255 R$ 3.137,50 
Itapuã 21 R$ 2,70 12541 R$ 33.860,70 
Itapuã 10 R$ 3,60 5400 R$ 19.440,00 
Itapuã 22 R$ 5,50 3700 R$ 20.350,00 
Itapuã 15 R$ 10,50 5200 R$ 54.600,00 
Karita 18 R$ 6,90 1600 R$ 11.040,00 
Musa 14 R$ 3,80 6500 R$ 24.700,00 
Musa 23 R$ 2,30 2400 R$ 5.520,00 
Musa 25 R$ 6,50 352 R$ 2.288,00 
 
Exemplo: totalizar os pedidos do Cliente Itapuã: 
 
=SOMASE(A2:A21;A15;E2:E21), onde: 
 
(A2:A21) � intervalo dos clientes onde serão selecionados todos iguais a Itapuã. 
 
A15 � critério de seleção dentre todos os clientes. Neste caso, considerar somente se for 
igual a Itapuã. 
 
(E2:E21) � intervalo que será considerado para somar os valores somente para cliente 
Itapuã. 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 26/57 
Resumo 
Cliente Pares Valor 
Azaléia 10051 R$ 51.688,90 
Bibi 11679 R$ 83.866,30 
Havaianas 6225 R$ 98.497,50 
Itapuã 26841 R$ 128.250,70 
Karita 1600 R$ 11.040,00 
Musa 9252 R$ 32.508,00 
 
R$ 405.851,40 
 
 
 
SOMASE com texto na fórmula: exemplo a seguir da planilha Indices.xls, guia EBTIDA:: 
 
 
DRE 
 Valores 
Receita Líquida $1.847.543,00 
(-) Custo das mercadorias vendidas 
-
$1.108.525,80 
(=) Lucro Bruto $739.017,20 
(-) Despesas operacionais -$369.508,60 
(-) Depreciação -$53.845,00 
Lucro Operacional $315.663,60 
(-) Despesas Financeiras -$39.485,00 
(=) Lucro Antes I.R. $276.178,60 
(-) Imposto de Renda e cont. social -$91.138,94 
(=) Lucro Líquido $185.039,66 
 
EBITDA Valores 
Vendas $1.847.543,00 
CMV 
-
$1.108.525,80 
Lucro bruto $739.017,20 
Despesas operacionais -$369.508,60 
EBITDA $369.508,60 
Depreciação -$53.845,00 
Despesas financeiras -$39.485,00 
Lucro antes do IR e CS $276.178,60 
IR+CS -$91.138,94 
Lucro líquido $185.039,66 
 
Conclusões: 
 
Lucro Operacional positivo 
 
Lucro líquido positivo 
EBTIDA positivo 
 
="Lucro Operacional "&SE(C9>0;"positivo";"negativo") 
="Lucro líquido "&SE(C13>0;"positivo";"negativo") 
="EBTIDA "&SE(C20>0;"positivo";"negativo") 
 
 
C9 
C13
C20 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 27/57 
2.11. SEERRO 
FUNÇÃO_SEERRO.XLS 
 
Com a função SEERRO elimina-se o problema que ocorre, por exemplo, numa divisão por 
zero, onde o Excel retorna o conjunto de caracteres #N/D. Quando isto ocorre em grande 
escala, em uma mesma planilha, cria-se uma poluição visual indesejada. Elimina-se isso com a 
função SEERRO(). 
Ao aplicar a função EERRO em uma fórmula, o Excel retorna como VERDADEIRO quando 
aparece o #N/D. Aproveitando-se a função SE, ou seja, o cálculo somente será feito se a 
expressão for verdadeira. Siga o exemplo através da planilha Função_SEERRO.xls. 
 
Modelo Vendas_2009 Vendas_2010 Var(%) 
Palio 3200 2900 -9,38% 
Celta 0 2650 *** 
Ka 2300 2200 -4,35% 
Clio 2500 2600 4,00% 
Gol 3180 3250 2,20% 
 
Sintaxe: =SEERRO((C2/B2-1);"***") 
 
Ao calcular a variação percentual comparando-se as vendas de 2010 com as de 2009, 
primeiro testa-se a divisão. Se o resultado obtido não for válido, como no exemplo do 
modelo Celta, 3 asteriscos serão impressos. Desta forma evita-se a impressão do 
conjunto de caracteres #N/D. 
 
 
2.12. COMENTÁRIOS EM CÉLULAS 
 
Recurso utilizado para comentar o conteúdo de uma determinada célula explicando, por 
exemplo, sua fórmula de cálculo. O comentário pode ser visível ou não, sendo esta 
configuração através das Opções do Excel. 
 
 
Caminho para configuração: Opções do Excel/Avançado... 
 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 28/57 
2.13. ATINGIR METAS 
ATINGIR_META_SOLVER.XLS 
 
Esta função é muito útil quando se deseja obter um resultado alterando uma variável. No 
exemplo a seguir, através da planilha Atingir_Meta_Solver.xls, que demonstra um plano 
de investimento, verifique quanto deveria ser o taxa mensal, mantendo-se as demais 
variáveis, para que o montante aos 60 meses chegue a R$ 75.000,00 
 
Investimento Inicial R$ 10.000,00 
 
Aporte mensal R$ 300,00 
 
Taxa (mensal) 1,50% 
 
Taxa (anual) equivalente 19,56% 
 
 
Meses Valor Futuro Meses Valor Futuro Meses Valor Futuro Meses Valor Futuro Meses Valor Futuro 
1 R$ 10.450,00 13 R$ 16.406,57 25 R$ 23.528,36 37 R$ 32.043,30 49 R$ 42.223,91 
2 R$ 10.906,75 14 R$ 16.952,67 26 R$ 24.181,29 38 R$ 32.823,95 50 R$ 43.157,27 
3 R$ 11.370,35 15 R$ 17.506,96 27 R$ 24.844,01 39 R$ 33.616,31 51 R$ 44.104,63 
4 R$ 11.840,91 16 R$ 18.069,57 28 R$ 25.516,67 40 R$ 34.420,55 52 R$ 45.066,20 
5 R$ 12.318,52 17 R$ 18.640,61 29 R$ 26.199,42 41 R$ 35.236,86 53 R$ 46.042,19 
6 R$ 12.803,30 18 R$ 19.220,22 30 R$ 26.892,41 42 R$ 36.065,41 54 R$ 47.032,83 
7 R$ 13.295,35 19 R$ 19.808,52 31 R$ 27.595,79 43 R$ 36.906,39 55 R$ 48.038,32 
8 R$ 13.794,78 20 R$ 20.405,65 32 R$ 28.309,73 44 R$ 37.759,99 56 R$ 49.058,89 
9 R$ 14.301,70 21 R$ 21.011,73 33 R$ 29.034,38 45 R$ 38.626,39 57 R$ 50.094,78 
10 R$ 14.816,22 22 R$ 21.626,91 34 R$ 29.769,89 46 R$ 39.505,79 58 R$ 51.146,20 
11 R$ 15.338,47 23 R$ 22.251,31 35 R$ 30.516,44 47 R$ 40.398,37 59 R$ 52.213,39 
12 R$ 15.868,55 24 R$ 22.885,08 36 R$ 31.274,19 48 R$ 41.304,35 60 R$ 53.296,59 
 
Procedimento da função Atingir Meta: 
 
Na barra de ferramentas, escolha a opção DADOS, vá em TESTE DE HIPÓTESES e 
clique na opção ATINGIR METAS. Abrirá a seguinte janela: 
 
 
 
Se a opção Teste de Hipóteses não estiver aparecendo, vá em Opções do 
Excel/Suplementos e solicite a sua instalação. 
 
Em “Definir Célula”, coloque a referência daquela célula que se deseja variar, por 
exemplo, a que representa o montante aos 60 meses: J18. 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 29/57 
Em “Para Valor”, preencha com o novo valor desejado, por exemplo: 75.000. 
Em “Alternando Célula”, indique qual variável deverá ser ajustada para que o novo valor 
seja atingido, por exemplo, a taxa mensal. Feito isto, clique em OK e veja o resultado: 
para atingir o montante de R$ 75.000,00, mantendo-se o aporte inicial de R$ 10.000,00 e 
os aportes mensais de R$ 300,00. o investidor terá que conseguir uma aplicação que lhe 
dê um retorno mensal de 2,24% ao mês, acima da atual de 1,50%. 
 
 
2.14. SOLVER 
 
A função SOLVER é uma sofisticação da função ATINGIR METAS, pois permite 
manipular uma ou mais variáveis para se atingir um novo resultado. Para testar esta 
função, utilize a mesma planilha anterior. 
 
Na barra de ferramentas, escolha a opção DADOS, vá em ANÁLISE DE DADOS e clique 
na opção SOLVER. Se a opção Análise de Dados não estiver aparecendo, vá em Opções 
do Excel/Suplementos e solicite a sua instalação. Ao clicar na função Solver, abrirá a 
seguinte janela de parâmetros: 
 
 
 
 
Objetivo: atingir, aos 60 meses, o montante de R$ 75.000, mantendo-se as seguintes 
restrições: a) aporte inicial <= R$ 12.000; b) aporte mensal >= zero; b) taxa mensal<= 
0,85%. 
Em “Definir Célula de destino”, coloque a referência daquela que se deseja variar, por 
exemplo, a que representa o montante aos 60 meses: J18. 
 
Em “Igual a”, marque a opção “Valor de” e preencha o box com 75000 (montante 
desejado). 
No Box referente a “Células Variáveis”, preencha com o conjunto de células que serão 
utilizadas para se atingir o montante desejado, neste exemplo $C$1:$C$3, que 
compreende: investimento inicial, aporte mensal e taxa mensal. 
 
 
Em “Submeter às Restrições”, adicione as seguintes: 
 
$C$1 <= 12.000 � aporte inicial limitado a R$ 12.000 
$C$2 >= 0 � aporte mensal maior que zero 
$C$3 <= 0,85% � limitar a taxa mensal até o máximo de 0,85% 
 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 30/57 
Definidas as restrições, clique em Resolver. 
Resultado: para se atingir o montante de R$ 75.000,00, o aporte inicial deverá ser de R$ 
10.009,60 e a taxa mensal de 0,85%, ampliando o aporte mensal para R$ 749,76. Estes 
resultados podem ser visualizados através dos Relatórios: Resposta e Sensibilidade. 
 
 
Microsoft Excel 12.0 Relatório de resposta 
Planilha: [Atingir_Meta_Solver.xlsx]Mensal 
Relatório criado: 14/5/2011 18:26:21 
Célula de destino (Valor de) 
Célula Nome Valor original Valor final 
$J$18 Valor Futuro R$ 42.667,87 R$ 75.000,00 
Células ajustáveis 
Célula Nome Valor original Valor final 
$C$1 Investimento Inicial R$ 10.000,00 R$ 10.009,60 
$C$2 Aporte mensal R$ 300,00 R$ 749,76 
$C$3 Taxa (mensal) 1,00% 0,85% 
Restrições 
Célula Nome Valor da célula Fórmula Status Transigência 
 
$C$1 Investimento Inicial R$ 10.009,60 $C$1<=$F$1 
Sem 
agrupar 1990,399647 
$C$3 Taxa (mensal) 0,85% $C$3<=$F$3 Agrupar 0 
$C$2 Aporte mensal R$ 749,76 $C$2>=$F$2 
Sem 
agrupar R$ 749,76 
 
 
 
2.15. TABELA DINÂMICA 
TABELA_DINÂMICA.XLS 
Através da Tabela Dinâmica, é possível analisar dados de uma tabela ou banco de dados, 
de uma forma diferenciada. Para exemplificar, use a planilha Tabela_dinâmica.xls, que 
contêm informações de valores pagos, por um cliente, aos seus diversos fornecedores de 
alimentos em diversas datas. Através da Tabela Dinâmica, é possível consolidar estas 
informações, em uma única tabela, por data e por fornecedor. Veja o exemplo: 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 31/57 
Fornecedor Data Pagto Valor 
 
Verde Mar 25/8 R$ 100,00 
Bretas 26/8 R$ 150,00 
MartPlus 25/8 R$ 600,00 
Verde Mar 27/8 R$ 3.000,00 
Carrefour 27/8 R$ 2.000,00 
Martplus 27/8 R$ 400,00 
Bretas 28/8 R$ 256,00 
Bretas 25/8 R$ 300,00 
Martplus 27/8 R$ 200,00 
 
Estrutura gerada através da Tabela Dinâmica: 
 
Soma de Valor Data Pagto 
Fornecedor 25/8 26/8 27/8 28/8 Total geral 
Bretas 300 150 256 706 
Carrefour 2000 2000 
MartPlus 600 600 1200 
Verde Mar 100 3000 3100 
Total geral 1000 150 5600 256 7006 
 
Através da 1ª tabela, com o recurso da Tabela Dinâmica, foi gerada uma nova tabela 
consolidando os valores pagos por fornecedores e por datas. A seguir, o passo a passo 
da Tabela Dinâmica: 
 
Passo 1: Inserir\Tabela Dinâmica\Tabela Dinâmica 
 
 
 
Preenchimento: 
 
Em Tabela/Intervalo, preencher com o intervalo da tabela original. Neste exemplo: A1:C10 
 
Onde será colocado o relatório de tabela dinâmica: pode ser em uma nova planilha ou a 
partir de uma célula na própria planilha. Neste exemplo, foi sugerido a posição A12. 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 32/57 
Na sequência da tela anterior, abrirá a janela, a seguir, de formatação da Tabela 
Dinâmica. No formulário de Tabela Dinâmica já está indicado o que deve conter cada 
bloco. Neste exemplo, faça da seguinte forma: 
 
a) Arraste o campo Fornecedor para a área reservada às linhas. 
b) Arraste o campo Data Pagto para a área reservada às colunas. 
c) Arraste o campo Valor para a área de dados. 
 
 
 
 
 
Após arrastar todos os campos, será gerada a seguinte tabela: 
 
Soma de Valor Data Pagto 
Fornecedor 25/8 26/8 27/8 28/8 Total geral 
Bretas 300 150 256 706 
Carrefour 2000 2000 
MartPlus 600 600 1200 
Verde Mar 100 3000 3100 
Total geral 1000 150 5600 256 7006 
 
 
 
Teste também a opção de gráfico dinâmico disponível em Inserir\Tabela Dinâmica\Gráfico 
Dinâmico. Os procedimentos são os mesmos. 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 33/57 
3. TERCEIRA PARTE 
 
� Filtrar e Filtro Avançado 
� Subtotal 
� Consolidar Informações 
� Validação 
� Índices Financeiros 
� Ponto de Equilíbrio 
� Alavancagem Operacional e Financeira 
� Técnicas de Análise de Investimentos 
� Formação de Preço 
� Macros 
 
 
3.1. FILTRAR E FILTRO AVANÇADO 
Filtrar_Subtotal.xls 
O uso do AutoFiltro para filtrar dados é um modo rápido e fácil de localizar e trabalhar 
com um subconjunto de dados em um intervalo de células ou coluna de tabela. Para 
exemplificar este comando, utilize a planilha Filtrar_Subtotal.xls: 
 
pedido cliente ref pu pares total 
2554 Bibi 20 R$ 1,20 1.254 R$ 1.504,80 
2549 Beira Rio 15 R$ 1,20 5.200 R$ 6.240,00 
2556 Beira Rio 22 R$ 5,20 1.200 R$ 6.240,00 
2553 Bibi 19 R$ 4,20 2.355 R$ 9.891,00 
2560 Bibi 26 R$ 3,60 3.220 R$ 11.592,00 
2548 Musa 14 R$ 3,50 6.500 R$ 22.750,00 
2561 Azaléia 27 R$ 7,80 3.251 R$ 25.357,80 
2555 Beira Rio 21 R$ 2,50 12.541 R$ 31.352,50 
2547 Beira Rio 10 R$ 9,60 5.400 R$ 51.840,00 
 
 
Procedimento para inserir os marcadores de filtro: Marque a planilha e vá na guia 
Dados\Filtro. Observe na planilha os marcadores nos cabeçalhos: 
 
 
 
Para retirar os marcadores, clique novamente no comando Filtro. 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 34/57 
Exemplo-1: classificar a tabela por cliente: clique na seta ao lado do cabeçalho “Cliente”. 
Em seguida, irá aparecer a janela com as opções de classificação. Se clicar em 
“Classificar de A a Z, a tabela será ordenada por nome de Cliente, na ordem crescente. 
Teste as demais opções. 
 
 
 
Exemplo-2: selecionar todos os pedidos do cliente “Beira Rio”. Utilize a mesma tela 
anterior. Desmarque a opção “Selecionar Tudo” e marque somente o cliente “Beira Rio” e 
dê OK. Veja o resultado a seguir: 
 
 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 35/57 
Exemplo-3: utilizar o Filtro Avançado e gerar outra planilha somente com os pedidos do 
cliente “Bibi”. Veja os procedimentos: 
 
a) Utilize esta tabela para o filtro avançado. Coloque na linha 12 uma cópia da linha de cabeçalho e, 
na linha seguinte, o nome do Cliente “Bibi”, que será o principal filtro ou intervalo de critério. Entre 
em Dados=>Filtrar=>Filtro Avançado. 
 
 
 
 
b) Em Intervalo da Lista, preencha com a referência completa da tabela principal: $A$1:$F$10 
 
c) Em Intervalo de critério, preencha com a referência completa do critério de seleção: $A$12:$F$13 
 
d) Em Ação, marque a opção “Copiar para outro local”. Um pouco abaixo, em “Copiar para:”, insira a 
posição onde o filtro serágerado: $A$15A15. Dê OK e veja o resultado: 
 
pedido cliente ref pu pares total 
2554 Bibi 20 R$ 1,20 1.254 R$ 1.504,80 
2549 Beira Rio 15 R$ 1,20 5.200 R$ 6.240,00 
2556 Beira Rio 22 R$ 5,20 1.200 R$ 6.240,00 
2553 Bibi 19 R$ 4,20 2.355 R$ 9.891,00 
2560 Bibi 26 R$ 3,60 3.220 R$ 11.592,00 
2548 Musa 14 R$ 3,50 6.500 R$ 22.750,00 
2561 Azaleia 27 R$ 7,80 3.251 R$ 25.357,80 
2555 Beira Rio 21 R$ 2,50 12.541 R$ 31.352,50 
2547 Beira Rio 10 R$ 9,60 5.400 R$ 51.840,00 
 
 
pedido cliente ref pu pares total 
 Bibi 
 
pedido cliente ref pu pares total 
2554 Bibi 20 R$ 1,20 1.254 R$ 1.504,80 
2553 Bibi 19 R$ 4,20 2.355 R$ 9.891,00 
2560 Bibi 26 R$ 3,60 3.220 R$ 11.592,00 
 
OBS.: Em "Intervalo da lista", "Intervalo de critérios" e "Filtrar lista no local" deve-se informar uma seleção 
com as mesmas colunas, nem mais e nem menos. 
Critério se 
seleção 
Filtro 
gerado 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 36/57 
3.2. SUBTOTAL 
 
Utilizando a mesma planilha Filtrar_Subtotal.xls, será demonstrado o uso do comando 
Subtotal. O objetivo nesta tabela será totalizar os valores a cada mudança de cliente. 
Sendo assim, é necessário ordenar antes a tabela por cliente. 
 
Procedimento: com a planilha selecionada, vá na guia Dados\Subtotal. Coloque as 
seguintes condições: a cada mudança em “Cliente”, adicione subtotal a “Total”. Dê OK e 
veja o resultado. Se posteriormente quiser remover os subtotais, vá em Dados\Subtotal e 
clique no botão “Remover Todos”. 
 
 
 
Planilha com Subtotais: 
 
Observe a sintaxe do comando Subtotal, por exemplo, do cliente Beira Rio: =SUBTOTAL(9;F4:F7) 
 
SUBTOTAL(núm_função, ref1, ref2, ...) 
 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 37/57 
Núm_função é o número de 1 a 11 que especifica qual função usar no cálculo de subtotais dentro de uma 
lista. 
 
Ref1, ref2 são intervalos de 1 a 254, ou referências, cujo subtotal é solicitado. 
 
NÚM_FUNÇÃO 
 
FUNÇÃO 
1 MÉDIA 
2 CONTA 
3 CONT.VALORES 
4 MÁX 
5 MÍN 
6 MULT 
7 DESVPAD 
8 DESVPADP 
9 SOMA 
10 VAR 
11 VARP 
 
 
 
3.3. CONSOLIDAR INFORMAÇÕES 
 
Criar uma planilha totalizadora que contenha todas as categorias de planilhas 
semelhantes. Para exemplificar, a partir de várias planilhas de controle de vendas e 
gastos por vendedores, criar uma única planilha consolidada. Para este exemplo, usar a 
planilha: 
Consolidar.xls. 
 
Objetivo: consolidar na planilha consolidado os valores de vendas e gastos dos 
vendedores Pedro e Erika, oriundos das suas respectivas planilhas individuais. 
 
Planilha do Pedro: 
Vend Mês Volume de vendas Gastos 
Pedro janeiro R$ 20.000,00 R$ 3.200,00 
Pedro fevereiro R$ 25.000,00 R$ 3.100,00 
Pedro março R$ 22.500,00 R$ 2.900,00 
 
Planilha da Erika: 
Vend Mês Volume de vendas Gastos 
Erika janeiro R$ 30.000,00 R$ 3.500,00 
Erika fevereiro R$ 32.000,00 R$ 4.200,00 
Erika março R$ 28.000,00 R$ 2.500,00 
 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 38/57 
Para consolidar os totais de vendas e gastos, siga os passos: 
 
Passo-1: Crie a planilha “Consolidado”, posicione na célula A1 e vá na guia 
Dados\Consolidar. 
 
 
Passo-2: Preencha as seguintes informações: 
a) Função: escolha a função Soma 
b) Referência: vá na planilha Pedro, marque-a totalmente e clique em Adicionar. 
Repita o processo para a planilha da vendedora Erika. 
c) Marque as opções de Rótulo para linha e coluna e dê OK. 
 
Planilha consolidada dos vendedores: 
 
Teste também o resultado marcando a opção “Criar vínculos com dados de origem”. 
 
3.4. VALIDAÇÃO 
Validação.xls 
O serviço de validação permite validar a entrada de dados restringindo valores que não 
sejam permitidos. Na tabela Validação.xls, utilizada para este exemplo, restrinja o preço 
do Etanol de modo que seja maior ou igual a R$ 2,30. Siga os passos: 
 
Passo-1: planilha de preço dos combustíveis 
 
Produto Preço de venda 
Etanol R$ 2,30 
Gasolina Comum R$ 2,70 
Gasolina Aditivada R$ 3,10 
Gasolina Podium R$ 3,50 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 39/57 
Passo-2: 
 
Restringir o preço do Etanol de modo que seja >= R$ 2,30. Marque a célula de preço do Etanol, e vá na 
guia Dados e Validação de Dados. Abrirá a seguinte janela de configurações: 
 
 
 
 
Passo-3: 
 
a) Guia Configurações: dentre as várias opções, escolha personalizado e, no campo fórmula, restrinja 
o preço conforme está na figura. 
b) Guia Mensagem de Entrada: preencha uma mensagem de alerta. 
 
 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 40/57 
c) Guia Alerta de Erro: preencha o estilo e uma mensagem de erro se necessário. 
 
 
 
 
Validação com LISTA: suponha que em uma célula deseja-se restringir as opções de escolha no momento 
do preenchimento. Por exemplo: em uma tabela de tributos, permitir a escolha do tipo de cálculo do imposto 
de renda: normal ou simples. Para este exemplo, usar a planilha Formação_de_Preço.xls, guia Preços_1. 
Formação_de_Preço.xls 
 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 41/57 
3.5. ÍNDICES FINANCEIROS 
 
A análise das demonstrações financeiras é comumente feita através de índices, de forma 
a avaliar os desempenhos passado e presente, bem como projetar o futuro da empresa. 
Os índices são relações entre contas ou grupos de contas das demonstrações 
financeiras. 
 
Existem basicamente duas formas de se utilizarem os índices. A primeira é a análise 
temporal da empresa, na qual o seu desempenho é medido ao longo do tempo 
possibilitando, assim, o traçado de tendências para o futuro. Uma ferramenta muito 
importante nesse estudo é a análise horizontal e vertical. 
 
A outra forma de utilização dos índices é a comparação com os índices de empresas 
concorrentes, no mesmo período de tempo. As datas devem ser iguais devido a possíveis 
cenários econômicos diferentes em épocas distintas. Além disso, a confrontação de 
índices pode ser feita também com a média do setor de atividade. É importante frisar que, 
como as empresas podem usar alguns critérios diferentes na elaboração de suas 
demonstrações, faz-se necessário padronizá-las. 
 
A análise de um índice isolado não oferece uma informação válida. A apreciação de um 
único índice não informa o suficiente sobre o desempenho global da empresa, podendo 
levar a conclusões incompletas. O uso de índices em conjunto propicia melhores dados 
para estudo. Mesmo com o conjunto de índices complementares à mão, ainda se fazem 
necessárias comparações temporais e setoriais. 
 
Os índices foram agrupados em quatro grupos: liquidez, estrutura (endividamento), 
atividade e rentabilidade (retorno). 
 
Para exemplificar esse assunto, utilize a planilha: Indices.xls. 
 
Nesta planilha, consta ainda um modelo de Análise de Insolvência de Kanitz, através de 
indicadores combinados, que tem como objetivo prever possibilidade de falêncianas 
empresas por meio de tratamento financeiro e estatístico de índices financeiros. 
 
 
 
 
Índices Financeiros 
 
 
 
Índice Fórmula Resultado 
ÍNDICES DE LIQUIDEZ 
 
Índice de liquidez geral ILG = (AC + RLP) / (PC + ELP) 15,35 
Índice de liquidez corrente ILC = AC / PC 16,94 
Índice de liquidez seca ILS = (AC - EST) / PC 13,36 
 
 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 42/57 
ÍNDICES DE ESTRUTURA 
 
Participação de terceiros CT/CP = (PC + ELP) / PL 6,21% 
Passivo Circulante / Patrimônio Líquido PC / PL 5,57% 
Exigível de longo prazo / Patrimônio líquido ELP / PL 0,64% 
Capital de Terceiros / Passivo Total (PC+ELP) / (PC+ELP+PL) 5,85% 
Passivo Circulante / Capital de Terceiros PC / (PC + ELP) 89,70% 
Imobilização de recursos permanentes AP / (ELP+PL) 10,78% 
Imobilização do patrimônio liquido AP / PL 10,85% 
 
ÍNDICES DE ATIVIDADE 
 
Giro de duplicatas a pagar 
GDP = Compras / Média Duplicatas a 
pagar 14 
Prazo médio de pagamento PMP = 360 / GDP 25 
Giro do estoque GE = CMV/Estoque Médio 6 
Prazo médio do estoque PME = 360 / GE 60 
Giro do recebimento GR = Vendas / Média Duplicatas a receber 15 
Prazo médio de recebimento PMR = 360 / GR 23 
 
 
ÍNDICES DE RENTABILIDADE 
 
Retorno sobre o investimento ROI = LL / AT 28,45% 
Retorno sobre o patrimônio líquido ROE = LL / PL 30,22% 
Margem de lucro operacional (lucratividade) ML = LO / RB 21,83% 
Margem de lucro líquido (lucratividade) ML = LL / RB 19,32% 
Giro do ativo GIRO = RB / AT 1,47 
 
 
3.6. PONTO DE EQUILÍBRIO 
 
Administradores necessitam saber o nível mínimo de faturamento que suas empresas 
devem atingir de forma a não operar no prejuízo. Esse nível é conhecido como ponto de 
equilíbrio. Ao operar abaixo desse nível, a empresa apresenta prejuízo. Ao operar acima 
dele, apresenta lucro. 
 
O ponto de equilíbrio ou break even point é, portanto, um importante indicador para os 
administradores financeiros. Sua determinação pode orientar a formulação das metas de 
faturamento da empresa, revelando o faturamento mínimo que ela deve buscar. 
 
Fórmula básica do ponto de equilíbrio: 
 
RT = DT 
Q x PVu = CFt + (Q x CVu) 
(Q x PVu) – (Q x CVu) = CFt 
Q (PVu – CVu) = CFt 
Q = CFt / (PVu – CVu), 
 
onde: 
RT = receita total 
DT = despesa total 
Q = quantidade no ponto de equilíbrio 
PVu = preço de venda unitário 
CFt = custo fixo total 
CVu = custo de venda unitário 
FACE – FUMEC 
Prof.: André Pires 
Margem de Contribuição (MC) = (PVu 
produto menos o seu custo variável. O resto desta subtração é a parcela do produto que 
deve ser suficiente para contribuir para 
para a formação do lucro. 
 
Para exemplificar esse assunto, utilize a planilha:
Ponto_de_Equilíbrio.xls 
Exercício 4 
 
Demanda máxima 2.500
Preço venda 
Custo variável 
Custo fixo $8.000
 
Quantidade de 
equilíbrio 1.000 
Receita de equilíbrio $20.000
 
Demandas Receita total
0 
250 $5.000
500 $10.000
750 $15.000
1.000 $20.000
1.250 $25.000
1.500 $30.000
1.750 $35.000
2.000 $40.000
2.250 $45.000
2.500 $50.000
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 Disc.: Aplicação de planilhas eletrônicas em finanças 
 
Margem de Contribuição (MC) = (PVu – CVu). A MC é dada pelo preço de venda do 
produto menos o seu custo variável. O resto desta subtração é a parcela do produto que 
deve ser suficiente para contribuir para o pagamento das despesas fixas da empresa e 
Para exemplificar esse assunto, utilize a planilha: 
 
2.500 
$20 
$12 
$8.000 
 
1.000 QE = CF/(PV-CV) 
$20.000 RE = QE*Preço venda 
 
Receita total Custo fixo Custo variável Custo total Resultado 
$0 $8.000 $0 $8.000 -$8.000 
$5.000 $8.000 $3.000 $11.000 -$6.000 
$10.000 $8.000 $6.000 $14.000 -$4.000 
$15.000 $8.000 $9.000 $17.000 -$2.000 
$20.000 $8.000 $12.000 $20.000 $0 
$25.000 $8.000 $15.000 $23.000 $2.000 
$30.000 $8.000 $18.000 $26.000 $4.000 
$35.000 $8.000 $21.000 $29.000 $6.000 
$40.000 $8.000 $24.000 $32.000 $8.000 
$45.000 $8.000 $27.000 $35.000 $10.000 
$50.000 $8.000 $30.000 $38.000 $12.000 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Pág. 43/57 
CVu). A MC é dada pelo preço de venda do 
produto menos o seu custo variável. O resto desta subtração é a parcela do produto que 
o pagamento das despesas fixas da empresa e 
FACE – FUMEC Disc.: Aplicação de planilhas eletrônicas em finanças -Excel Avançado 
Prof.: André Pires Pág. 44/57 
3.7. ALAVANCAGEM OPERACIONAL E FINANCEIRA 
 
Alavancagem operacional estuda a proporção entre os custos fixos e os custos variáveis dentro de uma 
estrutura operacional das empresas, ou seja, CF x CV. Na realidade, o GAO estuda como os custos 
influenciam os lucros das empresas em função da demanda. Portanto, quanto maior o investimento em CF, 
maior será o risco da empresa, pois ela deve faturar mais. Assim, quanto mais for favorável as vendas, mais 
favorável a utilização de CF. O resultado é a variação percentual no lucro operacional ocorrida em função 
da variação percentual no nível de atividade da empresa (vendas). 
 
 ��� �
∆�� !�%
∆#����%
 
 
A alavancagem financeira está relacionada ao planejamento e financiamento das necessidades, bem 
como à aplicação adequada de recursos. Portanto, estuda a proporção de capital próprio x capital de 
terceiros na estrutura financeira da empresa, isto é, a relação exigível/total de ativos e o seu grau é avaliado 
pela variação do LPA (lucro para os acionistas) em relação do LAJIR (lucro antes dos juros e do imposto de 
renda. 
 ��$ �
∆�%&%
∆�&'()%
 
 
Alavancagem Total: se uma empresa tiver despesas operacionais fixas e despesas financeiras fixas, o 
efeito dessas despesas poderá ser chamado alavancagem total. Altos graus de alavancagem operacional e 
financeira farão com que a alavancagem total seja elevada e vice-versa. 
GAT � GAO x GAF ��. �
∆�&'()%
∆#����%
 / 
∆�%&%
∆�&'()%
 ��. �
∆�%&%
∆#����%
 
Para exemplificar esse assunto, utilize a planilha: 
Alavancagem_Oper_Fin.xls 
Alavancagem 
 
Incremento das vendas 1000 
 
Preço de Venda $10,00 
 
% Custos Variáveis 50,00% 
 
Custos fixos $1.500,00 
 
Despesa de juros $1.000,00 
 
Alíquota IR 50,00% 
 
Dividendos preferenciais $500,00 
 
 
Unidades vendidas 1.000 2.000 3.000 4.000 5.000 
Venda $10.000,00 $20.000,00 $30.000,00 $40.000,00 $50.000,00 
Custos variáveis $5.000,00 $10.000,00 $15.000,00 $20.000,00 $25.000,00 
Custos fixos $1.500,00 $1.500,00 $1.500,00 $1.500,00 $1.500,00 
LAJIR $3.500,00 $8.500,00 $13.500,00 $18.500,00 $23.500,00 
Despesa de juros $1.000,00 $1.000,00 $1.000,00 $1.000,00 $1.000,00 
LAIR $2.500,00 $7.500,00 $12.500,00 $17.500,00