Microsoft Excel Exercício 5
No livro de trabalho Excel_5.xls encontram-se dados relativos a uma empresa de produção e comercialização de equipamentos de frio. Pretende-se efectuar um conjunto de análises a esses dados. Nesse sentido, siga os seguintes passos:
1. Iniciar o Microsoft Excel.
2. Abrir o livro de trabalho com o nome Excel_5.xls que se encontra guardado em C:\APLI Menu File > Open ou clique no botão Open da barra de ferramentas Standard
I. No campo Look in seleccionar C: II. Abrir a Pasta APLI
III. Seleccionar o ficheiro Excel_5 IV. Premir o botão Open
3. Guardar uma cópia do livro de trabalho Excel_5 na sua pasta pessoal I. Menu File > Save As …
II. Abrir a sua pasta pessoal III. Premir o botão Save
4. Na folha Prev. Resultados pretende-se efectuar uma previsão dos resultados para os anos de 1999 e 2000 tendo por base os resultados já conhecidos do ano de 1998. Para o ano de 1998, preencha as células que se encontram vazias com as fórmulas adequadas, tendo em conta as seguintes informações:
Margem Bruta (Célula B11) = Vendas – Custo das Vendas
a) Despesas Administrativas (Célula B13) = 8% do valor das Vendas b) Despesas Operacionais (Célula B16) = 25% do valor das Vendas
c) Total Despesas (Célula B17) = Soma das Despesas (Administrativas, Comerciais, Financeiras e Operacionais)
d) Resultados Ilíquidos (Célula B18) = Margem Bruta – Total Despesas e) Impostos (Célula B19) = % de Imposto (Cél. C6) sobre o Resultado Ilíquido f) Resultado Líquido (Célula B20) = Resultado Ilíquido – Imposto
5. Calcule o valor das vendas para os anos de 1999 e 2000, sabendo que estas vão aumentar percentualmente relativamente ao ano anterior de acordo com a taxa prevista (Célula C5).
6. Determine o Custo das Vendas e das Despesas (Administrativas, Comerciais, Financeiras e Operacionais) para os anos de 1999 e 2000, sabendo que se prevê que estas aumentem de acordo com a taxa de inflação considerada (Célula C4).
7. Determine a Margem Bruta, o Total Despesas, o Resultado Ilíquido, o Imposto e o Resultado Líquido para os anos de 1999 e 2000, copiando as fórmulas necessárias do ano de 1998.
8. Aplique a formatação automática Colorful 2 à tabela de suposições.
9. Exceptuando a linha de cabeçalho, formate todos os outros valores numéricos com o estilo monetário e sem casas decimais. Neste momento o aspecto da sua folha de cálculo deverá ser o seguinte:
Microsoft Excel Exercício 5
10. Elabore um gráfico do tipo Cluster Column 3-D que represente a evolução dos Resultados Ilíquidos, Impostos e Resultados Líquidos ao longo dos 3 anos.
Formate-o de acordo com a seguinte figura:
Nota: Para alterar as etiquetas do eixo dos XX, seleccione a Chart Area, faça clique com o botão direito do rato, e escolha Source Data, clique o separador Series e preencha o campo Category (X) axis labels.
11. No ponto seguinte vai adicionar uma linha de tendência ao gráfico. Atendendo a que estas não podem ser adicionadas a gráficos 3-D, altere o tipo do gráfico que elaborou para o tipo 2-D Clustered Column
I. Seleccionar o gráfico, fazendo clique numa área vazia do fundo do gráfico (Chart Area)
II. Menu Chart > Chart Type
III. Utilize o separador Standard Types para seleccionar o tipo de gráfico Column e o sub-tipo Clustered Column
Linhas de Tendência, Cenários, Simulações, Goal Seek e Solver Pág. 2/10
1 2 3 4 5 6 7 M ilh õ e s d e E s c u d o s 1998 1999 2000 Anos Previsão de Resultados Resultados Ilíquidos Impostos Resultados Líquidos
Microsoft Excel Exercício 5
12. Adicione uma linha de tendência (trendline) que represente a evolução dos Resultados Líquidos ao longo dos 3 anos. Formate-a com a cor vermelha
a) Adicionar linha de tendência:
I. Seleccionar o gráfico, fazendo clique numa área vazia do fundo do gráfico (Chart Area)
II. Menu Chart > Add Trendline III. Seleccione o separador Type
IV. Seleccione o tipo Linear uma vez que é aquele que melhor se ajusta à evolução dos Resultados Líquidos
V. Na caixa de listagem Based on Series, seleccione Resultados Líquidos VI. Faça Ok
b) Formatar a linha com a cor vermelha:
I. Seleccione a linha de tendência, fazendo clique sobre esta II. Menu Format > Selected Trendline
III. Seleccione o separador Patterns
IV. Utilize a caixa de selecção Color para definir a cor vermelha para a linha de tendência
V. Faça Ok
13. Através da linha de tendência, perspective a evolução do Resultado Líquido para o ano 2001
I. Seleccione a linha de tendência, fazendo clique sobre esta II. Menu Format > Selected Trendline
III. Seleccione o separador Options IV. Na opção Forward, introduza 1
Linhas de Tendência, Cenários, Simulações, Goal Seek e Solver Pág. 3/10
Previsão de Resultados 1 2 3 4 5 6 7 8 1998 1999 2000 Anos M ilh õ es d e E sc u d o s Resultados Ilíquidos Impostos Resultados Líquidos Previsão de Resultados 1 2 3 4 5 6 7 8 1998 1999 2000 Anos M ilh õ es d e E scu d o s Resultados Ilíquidos Impostos Resultados Líquidos Linear (Resultados Líquidos)
Microsoft Excel Exercício 5
14. As suposições apresentadas reflectem a situação mais provável. No entanto, há a possibilidade da situação vir a ser mais favorável (optimista) ou desfavorável (pessimista). Atendendo a estas três possibilidades, elabore três cenários a que corresponda cada uma destas três situações distintas
Cenário Tx. Inflação Tx. Crescimento Tx. Imposto
Provável 4% 6% 40%
Optimista 3% 8% 35%
Pessimista 5% 4% 40%
a) Criação do cenário Provável: I. Menu Tools > Scenarios
II. Clique sobre o botão Add na caixa de diálogo Scenario Manager III. Introduza Provável na caixa de texto Scenario name
IV. Defina o bloco C4:C6 na caixa de texto Changing cells V. Clique sobre o botão Ok na caixa de diálogo Add Scenario
VI. Na caixa de diálogo Scenario Values, introduza respectivamente 4%, 6% e 40% em cada uma das caixas de texto respeitantes ao valor que cada uma das células deve assumir neste cenário
VII. Clique sobre o botão Ok
b) Utilizando o mesmo procedimento, crie os cenários Optimista e Pessimista com base nos valores correspondentes apresentados na tabela anterior
15. Visualize cada um dos cenários e observe as alterações verificadas na tabela e no gráfico. I. Na caixa de diálogo Scenario Manager, seleccione o cenário na caixa de listagem
Scenarios
II. Clique sobre o botão Show
16. Altere os valores da Tx. Inflação e da Tx. Crescimento no cenário pessimista, respectivamente para 7% e 2% e visualize as consequências deste cenário na tabela e no gráfico
I. Na caixa de diálogo Scenario Manager, seleccione o cenário Pessimista da caixa de listagem Scenarios
II. Clique sobre o botão Edit
III. Na caixa de diálogo Edit Scenario, clique sobre o botão Ok
IV. Na caixa de dialogo Scenario Values, altere as caixas de texto relativas à Tx. Inflação e à Tx. de Crescimento respectivamente para 7% e 2%
17. O cenário que inquestionavelmente melhor representa a possível evolução dos resultados é o cenário provável. Visualize-o e elimine o cenário pessimista uma vez que tem muito poucas probabilidades de se vir a verificar
a) Visualizar o cenário Provável:
I. Na caixa de diálogo Scenario Manager, seleccione o cenário Provável da caixa de listagem Scenarios
II. Clique sobre o botão Show
Microsoft Excel Exercício 5
b) Eliminar o cenário Pessimista:
I. Na caixa de diálogo Scenario Manager, seleccione o cenário Pessimista da caixa de listagem Scenarios
II. Clique sobre o botão Delete
III. Clique sobre o botão Close, para fechar a caixa de diálogo Scenario Manager
18. Uma das componentes mais elevadas em qualquer um dos tipos de despesa numa empresa, são os custos com o pessoal. Na folha Salários Abril, apresenta-se ainda por elaborar o esboço do mapa de salários do mês de Abril de 1998. Crie as fórmulas adequadas para o processamento do salário de cada funcionário, sabendo que:
a) Tx. IRS = X%, de acordo com a seguinte tabela:
Venc. Ilíquido Taxa
< 145.000$ 10%
[145.000$; 165.000$[ 13% [165.000$; 185.000$[ 15%
>=185.000$ 20%
b) Seg. Social = 11% do valor do Venc. Ilíquido c) Imp. Selo = 0,5 % do valor do Venc. Ilíquido
d) Tot. Descontos = Tx. IRS * Venc. Ilíquido + Seg. Social + Imp. Selo
e) Venc. Líquido = Venc. Ilíquido – Tot. Descontos + Subs. Almoço + Ajudas Custo Após a realização do processamento de salários, o aspecto da sua tabela deverá ser o seguinte:
19. Sem considerar as ajudas de custo, os Técnicos de Vendas reclamam um Venc. Líquido de 165.000$. Utilizando a Tabela de Simulação 1, determine qual deverá ser o montante do Venc. Ilíquido, de modo que após a realização de todos os descontos obrigatórios, o Venc. Líquido se situe no valor pretendido
I. Menu Tools > Goal Seek
II. Na caixa de texto Set cell, introduza D20 III. Na caixa de texto To value, introduza 165000 IV. Na caixa de texto By changing cell, introduza D14 V. Clique sobre o botão Ok
20. Venc. Ilíquido encontrado é de 219.124$ (aprox. 220.000$). Utilizando a Tabela de Simulação 2, verifique qual a progressão do Venc. Líquido mediante acréscimos de 10.000$ ao Venc. Ilíquido desde 150.000$ (Venc. Ilíquido actual) até 220.000$ (Venc. Ilíquido encontrado).
Microsoft Excel Exercício 5
I. Seleccione a célula G15
II. Introduza a fórmula que permite calcular o Venc. Líquido de um Técnico de Vendas, sem considerar as Ajudas de Custo: C6-G6+H6
III. Seleccione o bloco F15:G22 IV. Menu Data > Table
V. Na caixa de texto Column input cell, introduza C6 (Célula cujo valor será substituído pelos valores em coluna G15:G22)
VI. Clique sobre o botão Ok
21. Na folha Prev. Vendas, pretende-se efectuar para o ano de 1999, a previsão de vendas por trimestre do principal artigo produzido e comercializado pela empresa. Preencha as células que se encontram vazias, tendo em conta as seguintes informações:
a) Vendas (unidades) = Publicidade & Marketing 0,3 * Factor Sazonalidade Nota: use o operador ^ para efectuar a potência
b) Vendas (valor) = Vendas (unidades) * Preço de Venda
c) Custo das Vendas = Vendas (unidades) * Custo de Produção d) Margem Bruta = Vendas (valor) - Custo das Vendas
e) Custo Diversos = 1% do valor das Vendas (valor) f) Lucro Bruto = Margem Bruta - Total Despesas g) Margem de Lucro = Lucro Bruto/Margem Bruta
Nota: as células encontram-se já formatadas como % h) Determine os valores da coluna Total
Microsoft Excel Exercício 5
A sua folha de cálculo deverá ficar com o seguinte aspecto:
22. O 1º trimestre é um dos que apresenta menor Lucro Bruto. Uma forma de o aumentar passa pelo incremento das unidades vendidas. Estas por sua vez dependem directamente do investimento efectuado em Publicidade e Marketing. Determine qual o montante que será necessário despender neste item de modo a maximizar o Lucro Bruto
I. Menu Tools > Solver
II. Na caixa de texto Set Target Cell, introduza B19 III. Seleccione a opção Max
IV. Na caixa de texto By Changing Cells, introduza B15 V. Clique sobre o botão Solve
VI. Mova a caixa de diálogo Solver Results de modo a que fique visível o valor encontrado (995.142$)
VII. Seleccione a opção Restore Original Values para restaurar os valores originais VIII. Clique sobre o botão Ok
23. Outro dos factores que influencia as unidades vendidas é a sazonalidade de vendas ao longo dos trimestres. Facilmente se depreende que os investimentos em Publicidade e Marketing deverão ser superiores nos trimestres em que as vendas tendêncialmente serão mais fracas. Determine qual a melhor distribuição do investimento actualmente destinado a este item (2.000.000$), de modo a maximizar o total de Lucro Bruto no final dos quatro trimestres
I. Menu Tools > Solver
II. Na caixa de texto Set Target Cell, introduza F19 III. Seleccione a opção Max
IV. Na caixa de texto By Changing Cells, introduza o bloco B15:E15 V. No quadro Subject to the Constraints, clique sobre o botão Add
VI. Na caixa de diálogo Add Constraint, introduza a restrição F15 = 2000000 VII. Clique sobre o botão Ok
VIII. Clique sobre o botão Solve
Microsoft Excel Exercício 5
IX. Na caixa de dialogo Solver Results, seleccione a opção Keep Solver Solution para aceitar a solução encontrada
24. A empresa coloca a possibilidade de investir em Publicidade e Marketing a quantia de 3.000.000$, ao longo dos 4 trimestres. Face a este novo montante, determine qual a sua melhor distribuição pelos trimestres, de modo a maximizar o total de Lucro Bruto no final dos quatro trimestres
I. Menu Tools > Solver
II. No quadro Subject to the Constraints, seleccione a restrição e clique sobre o botão Change
III. Na caixa de diálogo Change Constraint altere o valor para 3000000 IV. Clique sobre o botão OK
V. Clique sobre o botão Solve
VI. Na caixa de dialogo Solver Results, seleccione a opção Keep Solver Solution para aceitar a solução encontrada
25. Qual o montante que seria necessário despender em Publicidade e Marketing de modo a maximizar o total de Lucro Bruto no final dos quatro trimestres
I. Menu Tools > Solver
II. No quadro Subject to the Constraints, seleccione a restrição e clique sobre o botão Delete
III. Clique sobre o botão Solve
IV. Na caixa de dialogo Solver Results, seleccione a opção Keep Solver Solution para aceitar a solução encontrada
Nota: Ainda que o Lucro Bruto se encontre maximizado, a Margem de Lucro diminuiu relativamente à simulação anterior. Tal fica-se a dever ao aumento considerável dos gastos em Publicidade e Marketing.
Microsoft Excel Exercício 5