EXCEL PARA GESTÃO 65 EXERCÍCIOS
Adelaide Carvalho
FCA – Editora de Informática, Lda.
www.fca.pt
EDIÇÃO FCA – Editora de Informática, Lda. www.fca.pt Copyright © 2016, FCA – Editora de Informática, Lda. ISBN eBook: 978-972-722-853-9 1.ª edição eBook: setembro 2016 Capa: José Manuel Ferrão – Look-Ahead Todos os nossos livros passam por um rigoroso controlo de qualidade, no entanto aconselhamos a consulta periódica do nosso site (www.fca.pt) para fazer o download de eventuais correções. Não nos responsabilizamos por desatualizações das hiperligações presentes nesta obra, que foram verificadas à data de publicação da mesma. Os nomes comerciais referenciados neste livro têm patente registada. Reservados todos os direitos. Esta publicação não pode ser reproduzida, nem transmitida, no todo ou em parte, por qualquer processo eletrónico, mecânico, fotocópia, digitalização, gravação, sistema de armazenamento e disponibilização de informação, sítio Web, blogue ou outros, sem prévia autorização escrita da Editora.
ÍNDICE O Autor Prólogo 1 – Matemática Financeira ? Exercício 1.1: Taxas proporcionais ? Exercício 1.2: Taxas equivalentes ? Exercício 1.3: Taxas anuais efetivas (I) ? Exercício 1.4: Taxas anuais efetivas (II) ? Exercício 1.5: Gráfico para capital acumulado ? Exercício 1.6: Variação da taxa de juro ? Exercício 1.7: Capital acumulado ? Exercício 1.8: Várias taxas para o capital acumulado ? Exercício 1.9: Taxa anual nominal ? Exercício 1.10: Capital comum ? Exercício 1.11: Vencimento comum ? Exercício 1.12: Valor atual de renda ? Exercício 1.13: Valor acumulado de renda ? Exercício 1.14: Valor acumulado de fundo ? Exercício 1.15: Valor atual de renda mensal perpétua ? Exercício 1.16: Valor atual de renda anual perpétua ? Exercício 1.17: Pagamento único para substituir renda
? Exercício 1.18: Várias taxas para o pagamento único ? Exercício 1.19: Substituição de prestações em dívida ? Exercício 1.20: Vários prazos de pagamento e várias taxas de juro ? Exercício 1.21: Termo de renda ? Exercício 1.22: Números de períodos de renda ? Exercício 1.23: Taxa de juro de renda ? Exercício 1.24: Valor acumulado de duas rendas ? Exercício 1.25: Locação financeira ? Exercício 1.26: Sistema de amortização constante ? Exercício 1.27: Sistema de amortização progressiva ? Exercício 1.28: Cenários para sistema de amortização progressiva ? Exercício 1.29: Plano de fundo de amortização ? Exercício 1.30: Amortização progressiva com taxa de juro variável 2 – Aplicações à Gestão ? Exercício 2.1: Vencimentos ? Exercício 2.2: Efeito sobre a massa salarial ? Exercício 2.3: Câmbios ? Exercício 2.4: Preçário e valor das vendas ? Exercício 2.5: Índices de preços ? Exercício 2.6: Lucro estimado ? Exercício 2.7: Resultados operacionais esperados ? Exercício 2.8: Plano anual de custos ? Exercício 2.9: Previsão de autofinanciamento
? Exercício 2.10: Orçamento de projeto ? Exercício 2.11: Orçamento de tesouraria ? Exercício 2.12: Conta de exploração previsional ? Exercício 2.13: Taxas de crescimento a preços nominais ? Exercício 2.14: Proveitos reais ? Exercício 2.15: Necessidades de fundo de maneio ? Exercício 2.16: Prémios anuais de seguros ? Exercício 2.17: Reintegrações e amortizações ? Exercício 2.18: Repartição primária de custos ? Exercício 2.19: Repartição secundária de custos ? Exercício 2.20: TIR e VAL ? Exercício 2.21: Regresso de capital, TIR e VAL ? Exercício 2.22: TIR e MTIR ? Exercício 2.23: Objetivo para MTIR ? Exercício 2.24: Ponto crítico de vendas ? Exercício 2.25: Gráfico para o ponto crítico de vendas ? Exercício 2.26: Unidades para obter certos lucros ? Exercício 2.27: Preço e quantidade críticos ? Exercício 2.28: Preços para diversos lucros ? Exercício 2.29: Análise ABC ? Exercício 2.30: Quantidade económica a encomendar (I) ? Exercício 2.31: Quantidade económica a encomendar (II) ? Exercício 2.32: Menor custo anual de stock ? Exercício 2.33: Qee para descontos de quantidade
? Exercício 2.34: Quantidades a encomendar constantes ? Exercício 2.35: Quantidades a encomendar dinâmicas Bibliografia Índice Remissivo Complementos na Web
O AUTOR Adelaide Carvalho Doctor of Philosophy in Management Science (Lancaster University, Reino Unido), Doutora em Economia e Gestão (Universidade do Porto, Portugal), Master of Science in Management Science (University of Kent at Canterbury, Reino Unido), Master of Science in Computing Science (University of London, Reino Unido), Eq. Mestre em Ciência dos Computadores (Universidade do Porto, Portugal), Licenciada em Economia (Universidade do Porto, Portugal). Docente, desde 1983, em diversos estabelecimentos de ensino superior. Organizou e ministrou, de 1991 até ao presente, vários cursos de aplicação da Informática à Economia e à Gestão em Portugal, Grécia, Finlândia, Moçambique, Macau e Timor-Leste. Autora de vários livros de Informática aplicada à Economia e à Gestão editados pela FCA.
PRÓLOGO A publicação de Excel para Gestão – 65 Exercícios visa incentivar os utilizadores de Excel a explorarem as áreas da Gestão onde as funcionalidades do Excel possam contribuir para soluções mais claras, mais simples e, sobretudo mais fáceis de conceber, de parametrizar e de reutilizar. Os exercícios que selecionámos apresentam as folhas de cálculo como ferramenta imprescindível à tomada de decisões na área da Gestão, já que efetuam cálculos morosos e complexos e libertam tempo que poderá ser dedicado à experimentação de novas soluções através da construção de cenários alternativos e procura de respostas eficazes as questões pertinentes da Gestão: – “O que aconteceria se os dados do problema fossem outros?” – “Seria interessante se os dados fossem outros?” – “Quais os dados que terão de se alterar para que se atinjam os objetivos delineados?” – “Que impacto nos resultados finais produziriam políticas alternativas?” – “Como reagiria a solução encontrada à incerteza do mercado?” Excel para Gestão – 65 Exercícios é constituído por dois capítulos: “1. Matemática Financeira” e “2. Aplicações à Gestão”. O primeiro capítulo resolve 35 exercícios e o segundo 30 exercícios de Economia, Gestão e disciplinas auxiliares (vide Figura 1).
Figura 1. Estrutura de Excel para Gestão – 65 Exercícios
Os 30 exercícios do primeiro capítulo ilustram a aplicação e a utilização das funções financeiras e funcionalidades várias do Excel ao cálculo do juro, do capital acumulado e do capital atual em regime de capitalização, às rendas e à amortização de empréstimos. Estes exercícios recorrem ainda às funcionalidades de Análise de Hipóteses para construir cenários alternativos, fazendo variar uma, duas ou mais variáveis de determinado modelo matemático (vide Figura 2).
Figura 2. Funções e funcionalidades do Excel utilizadas na resolução de exercícios de Matemática Financeira
Os 35 exercícios do segundo capítulo são exemplos de Gestão onde o recurso ao Excel é atualmente obrigatório. A resolução destes exercícios evidencia a utilidade da combinação de diversas funcionalidades do Excel, nomeadamente as funções de procura de valores em tabelas, a análise de hipóteses, a regressão linear e o gerador de gráficos (vide Figura 3).
Figura 3. Funções e funcionalidades do Excel utilizadas na resolução dos exercícios de Aplicações à Gestão
A resolução destes 65 exercícios observa metodologias de programação estruturada, nomeadamente a aproximação descendente (top-down) e privilegia a facilidade de compreensão da solução face à complexidade dos cálculos, incluindo na maioria dos exercícios, a explicitação da formulação dos problemas. A vocação académica orientada para a Gestão desta publicação reforça o seu uso no universo profissional, tratando questões que fazem parte do quotidiano da Economia e da Gestão. Crê-se, e a experiência da autora como professora confirma-o, que este livro poderá constituir um instrumento precioso para a motivação dos formandos e aceleração do processo de aprendizagem. Adelaide Carvalho
1. MATEMÁTICA FINANCEIRA Este capítulo versa o cálculo do juro em regime de capitalização composta e a sua extensão às rendas e amortização de empréstimos, apresenta as funções financeiras do Excel e as funcionalidades de análise de hipóteses para resolver problemas comuns de Matemática Financeira. Os 30 exercícios que contém ilustram: o cálculo de taxas de juros (efetivas e nominais, equivalentes e proporcionais); o cálculo do capital acumulado em regime de capitalização composta; o cálculo do valor atual e do desconto composto; o cálculo do valor atual e do valor acumulado das rendas; a determinação do valor do termo de uma renda, do número de termos e da respetiva taxa de juro; e a elaboração de vários quadros de amortização de empréstimos. Antes de iniciar a resolução dos exercícios deste capítulo: I – Defina, recorrendo ao sistema operativo Windows, a vírgula como separador decimal e o ponto e vírgula como separador de argumentos das funções. II – Ative o suplemento Analysis ToolPak. III – Ative o separador Programador.
? Exercício 1.1: Taxas proporcionais Considere a taxa anual nominal de 10%. Calcule as taxas efetivas: a) Semestral;
b) Quadrimestral;
c) Trimestral;
d) Bimestral;
e) Mensal;
f) Diária.
Formulação do problema 1. Cálculo das taxas proporcionais para cada período
Taxa anual nominal: TAN Número de períodos de capitalização por ano: n Resolução do problema 1. Digite a taxa anual nominal em B3 2. Digite as taxas efetivas a calcular e os respetivos números de períodos de capitalização por ano em A5:B10 3. Calcule as taxas periódicas efetivas (taxas proporcionais) 3.1. digitando =$B$3/B5 em C5 3.2. copiando C5 para C6:C10 4. Formate as taxas periódicas efetivas 4.1. selecionando 4.1.1. C5:C10 4.1.2. Base – Células – Formatar – Formatar Células 4.1.3. Percentagem em Número 4.1.4. 3 em Casas decimais Resultado esperado
A
B
Taxa anual 3 nominal 4
C 10%
N.º de períodos Taxa periódica por ano efetiva
Taxa
5 Semestral
2
5,000%
6 Quadrimestral
3
3,333%
7 Trimestral
4
2,500%
8 Bimestral
6
1,667%
9 Mensal 10 Diária
12
0,833%
365
0,027%
? Exercício 1.2: Taxas equivalentes Considere a taxa anual efetiva de 10%. Calcule as taxas efetivas: a) Semestral;
b) Quadrimestral;
d) Bimestral;
e) Mensal.
c) Trimestral;
Formulação do problema 1. Cálculo das taxas equivalentes para cada período
Taxa periódica efetiva ou
Número de períodos de capitalização por ano: n Taxa anual efetiva: TAE Resolução do problema 1. Digite a taxa anual efetiva em B3 2. Digite as taxas efetivas a calcular e os respetivos números de períodos de capitalização por ano em A5:B10 3. Calcule as taxas periódicas efetivas 3.1. digitando 3.1.1. =(1+$B$3)^(1/B5)-1 ou =NOMINAL($B$3;B5)/B5 em C5 3.2. copiando C5 para C6:C10 4. Formate as taxas periódicas efetivas 4.1. selecionando 4.1.1. C5:C10 4.1.2. Base – Células – Formatar – Formatar Células 4.1.3. Percentagem em Número 4.1.4. 3 em Casas decimais Resultado esperado A 3
Taxa anual efetiva
4
Taxa
B
C 10%
N.º de períodos por ano
Taxa periódica efetiva
5 Semestral
2
4,881%
6 Quadrimestral
3
3,228%
7 Trimestral
4
2,411%
8 Bimestral
6
1,601%
12
0,797%
365
0,026%
9 Mensal 10 Diária
? Exercício 1.3: Taxas anuais efetivas (I) Qual a taxa anual efetiva que corresponde a 5,5% anual nominal, capitalizada trimestralmente? Formulação do problema 1. Cálculo da taxa anual efetiva
ou
Número de períodos de capitalização por ano: n=4 Taxa anual efetiva: TAE Taxa anual nominal: TAN Resolução do problema 1. Digite em A3:D4
A 3 4
B
C
D
Taxa N.º de Período de anual períodos capitalização nominal por ano 5,50% Trimestre
Taxa anual efetiva
4
2. Calcule a taxa anual efetiva 2.1. digitando em D4 2.1.1. =(1+A4/C4)^C4-1 ou =EFETIVA(A4;C4) 3. Formate a taxa anual efetiva 3.1. selecionando 3.1.1. D4 3.1.2. Base – Células – Formatar – Formatar Células 3.1.3. Percentagem em Número 3.1.4. 3 em Casas decimais Resultado esperado D 3 4
Taxa anual efetiva 5,614%
? Exercício 1.4: Taxas anuais efetivas (II) Qual a taxa anual efetiva que corresponde à taxa mensal efetiva de 0,5%?
Formulação do problema 1. Cálculo da taxa anual efetiva
ou
Número de períodos de capitalização por ano: n Taxa anual efetiva: TAE Resolução do problema 1. Digite em A3:D4 A 3 4
Taxa mensal efetiva
B
C
D
N.º de Período de períodos capitalização por ano
0,50% Mês
Taxa anual efetiva
12
2. Calcule a taxa anual efetiva 2.1. digitando em D4 2.1.1. =(1+A4)^C4 - 1 ou =EFETIVA(A4*C4;C4) 3. Formate a taxa anual efetiva 3.1. selecionando 3.1.1. D4 3.1.2. Base – Células – Formatar – Formatar Células
3.1.3. Percentagem em Número 3.1.4. 3 em Casas decimais Resultado esperado D 3 4
Taxa anual efetiva 6,168%
? Exercício 1.5: Gráfico para capital acumulado Colocaram-se 1 000 € em regime de capitalização composta durante 6 anos, a uma taxa de juro anual de 5%. Represente graficamente o capital no início e no fim de cada ano. Formulação do problema 1. Cálculo do juro e do capital acumulado no fim de cada ano
2. Cálculo do capital no início de cada ano
Resolução do problema 1. Digite em A3:D5
A 3
Capital inicial
4
1000
5 Ano
B
C
Duração (anos)
Taxa anual de juro (%)
6 Capital – Início
D
5 Juro anual
Capital – Fim
2. Calcule o capital no início e no fim de cada ano 2.1. digitando 2.1.1. 1, 2, …, 6 em A6:A11 2.1.2. =A4 em B6 2.1.3. =B6*$C$4/100 em C6 2.1.4. =B6+C6 em D6 2.1.5. =D6 em B7 2.2. copiando 2.2.1. B7 para B8:B11 2.2.2. C6:D6 para C7:B11 3. Desenhe um gráfico de dispersão para representar o capital no início e no fim de cada ano 3.1. selecionando 3.1.1. A5:D11 3.1.2. Inserir – Gráficos – Dispersão – Dispersão com Linhas Suaves 4. Retire a curva que representa os juros anuais 4.1. selecionando a curva dos juros no gráfico 4.2. premindo Delete 5. Altere outros formatos do gráfico 5.1. selecionando
5.1.1. os respetivos elementos (curvas, legendas, títulos, etc.) 5.1.2. Ferramentas de Gráfico – Formatar – Seleção Atual – Formatar seleção Resultado esperado
? Exercício 1.6: Variação da taxa de juro Acrescente ao Exercício 1.5 um botão que permita fazer variar a taxa anual de juro entre 0% e 20%, com incrementos de 1%. Resolução do problema 1. Copie A3:D11 e o gráfico da folha de cálculo 1.5 para uma nova folha, 1.6 2. Altere a fonte dos dados do gráfico 2.1. selecionando 2.1.1. o gráfico 2.1.2. Estrutura – Dados – Selecionar Dados 2.2. digitando
2.2.1. ='1.6'!$A$5:$B$11;'1.6'!$D$5:$D$11 em Intervalo de dados do gráfico 3. Associe à célula da taxa anual de juro um controlo 3.1. Programador – Controlos – Inserir – Controlos do Formulário 3.2. desenhe um botão giratório (Controlo de Formulário) 3.3. altere as propriedades do botão 3.3.1. selecione o botão 3.3.2. escolha Programador – Controlos – Propriedades 3.3.3. digite 3.3.3.1. 0 em Valor Mínimo 3.3.3.2. 20 em Valor Máximo 3.3.3.3. 1 em Incremento 3.3.3.4. C4 em Ligação à Célula 4. Altere o título do gráfico 4.1. digitando em B14 4.1.1. ="Capitalização composta a "&C4&"% ao ano" 4.2. selecionando o título do gráfico 4.3. digitando ='1.6'!$B$14 na barra de fórmulas 5. Altere a escala do eixo das ordenadas 5.1. selecionando 5.1.1. o eixo das ordenadas 5.1.2. Ferramentas de Gráfico – Formatar – Seleção Atual – Formatar seleção – Opções do Eixo 5.1.3. digitando 5.1.3.1. 0 em Mínimo 5.1.3.2. 3000 em Máximo 5.1.3.3. 200 em Unidade Principal 5.1.3.4. 100 em Unidade Secundária Resultado esperado