Office Excel 2003 Exercicios

Prévia do material em texto

Sistemas de Informação em Gestão 
Ano Lectivo 2008/09 
EXERCÍCIOS DE EXCEL 
LICENCIATURA EM ECONOMIA 
LICENCIATURA EM GESTÃO DE EMPRESAS 
LICENCIATURA EM TURISMO 
Sistemas de Informação em Gestão 
 
2
Exercício n.º 1 
PARTE I – Funções da categoria Data 
1. Numa folha de cálculo, altere o nome da folha 1 (sheet 1) para Empregados e, nela, 
construa uma tabela de acordo com as seguintes indicações: 
1.1. Começar em A1. 
1.2. Nomes das colunas: Número de Identificação; Nome; Data de Nascimento; 
Endereço; Localidade; Código Postal; Data de Admissão. 
1.3. Introduza dados relativos a 6 (seis) empregados. 
1.4. Dê o nome de “Empregados” à folha de cálculo. 
1.5. Altere as alturas das sete linhas. 
1.6. Centre o conteúdo das células de Idade. 
1.7. Atribua cores diferentes às células de Nome e de Data de Admissão. 
1.8. Faça a formatação dos valores numéricos das respectivas colunas e da Data 
de Admissão. 
1.9. Com as funções de data e tempo, crie colunas que indiquem os anos de 
empresa, a idade dos empregados e a idade que tinham quando foram 
admitidos na empresa. 
1.10. Se necessário, faça as modificações pertinentes para que o resultado final 
seja o mais parecido possível com o apresentado. 
 
 
 
 
Sistemas de Informação em Gestão 
 
3
PARTE II – Funções da categoria Lógica 
2. Altere o nome da folha 2 (sheet 2) para Prémios e, nela, com base na tabela 
construída no ponto anterior, elabore uma segunda tabela de acordo com as 
seguintes indicações: 
2.1. A empresa decidiu oferecer no fim do ano aos seus empregados prémios 
pecuniários em função do número de anos na empresa e tendo em conta a 
idade civil. Calcule o prémio a atribuir a cada empregado e a soma desses 
valores, com duas condições. 
 A primeira obedece ao seguinte critério de número de anos de trabalho na 
empresa: 
• Entre 3 e 5 anos = 1000 € 
• De 5 a 9 anos = 1500 € 
• Igual ou superior a 10 anos = 2000 € 
 Segunda condição: 
• Se o trabalhador tem idade superior a 30 anos e inferior a 40 anos, o 
prémio aumenta 20%; 
• Se o trabalhador tem idade igual ou superior a 40 anos, o prémio aumenta 
35%. 
 
2.2. Elabore um relatório explicando os passos dados. Como todos os seguintes, 
o relatório deve ser devidamente identificado (nome, número e curso) e 
entregue à Equipa Docente num ficheiro do tipo *,pdf. 
 
Sistemas de Informação em Gestão 
 
4
Exercício n.º 2 
Comando Filtro do menu Dados e Funções da categoria Base de Dados 
 
Parta da tabela construída no Exercício n.º 1 e execute este exercício acordo com as 
seguintes indicações: 
1) Aumente o número de empregados para 24, utilizando as colunas: Número de 
Identificação; Nome; Data de Nascimento; Endereço; Localidade; Código Postal; 
Data de Admissão, Idade, Idade de Admissão e outras colunas como se indica a 
seguir. Introduza dados de acordo com a sua escolha pessoal; 
2) Acrescente as seguintes colunas: “Área Funcional”; “Cargo”; 
3) A coluna da área funcional tem as seguintes possibilidades: Gerência (2), 
Comercial (6), Armazém (2), Secretariado (3), Pessoal (2), Contabilidade (2), 
Produção (7). Os números entre parêntesis indicam o número de funcionários na 
respectiva área funcional; 
4) Entre outras designações à sua escolha, a coluna “Cargo” inclui gerente, 
director, chefe de vendas, vendedor, secretária e chefe de sector; 
Resolva os dois pontos seguinte usando o comando Filter do menu Data. 
5) Ordene os funcionários pela data de admissão na empresa e indique o número 
de identificação e o nome dos três funcionários que nela se encontram há mais 
tempo; 
6) Indique os funcionários que residem numa dada localidade (por exemplo, 
Lisboa) e que trabalham na empresa há 5 anos ou mais; 
Recorrendo a funções da categoria Database, resolva os dois pontos seguintes. 
7) Calcule a média aritmética de idades dos funcionários que trabalham na 
Produção; 
8) Calcule a soma dos anos de empresa dos funcionários que trabalham na área 
Comercial e no Armazém. 
 
 
Elabore um relatório explicando os passos dados e entregue-o à Equipa Docente num 
ficheiro do tipo *,pdf. 
 
 
 
 
 
Sistemas de Informação em Gestão 
 
5
Exercício 3 
Endereços absolutos e Funções das categorias Lógica, Aritmética e Estatística 
Construa a tabela a seguir apresentada: 
 
Observação: Todas as células das colunas com fundo sombreado (amarelo) recorrem 
a fórmulas para obter os valores numéricos apresentados. 
 
Elabore um relatório explicando os passos dados e entregue-o à Equipa Docente num 
ficheiro do tipo *,pdf. 
 
 
 
Sistemas de Informação em Gestão 
 
6
Exercício 4 
Recolha de dados na Internet e criação de Gráficos 
1. Construa uma tabela que contenha os 20 títulos do PSI20 e as respectivas 
cotações ao longo de um mês. Dê à folha o nome “Rates”. 
Sugestão: importe alguns dos dados necessários a partir de um site de uma 
instituição financeira. Os restantes, introduza-os de forma coerente tendo em 
atenção que esta mesma tabela será utilizada em exercícios seguintes. 
 
2. Crie um gráfico de linhas, numa folha de Gráfico denominada “Cotações”, 
mostrando a evolução das cotações de todos os títulos ao longo do mês. 
3. Seleccione as 3 empresas com os preços máximos mais elevados e os 3 com os 
preços máximos mais baixos. Copie para uma nova folha, a que vai chamar 
“Portfolio”, os referidos 6 títulos. Para cada um desses 6 títulos crie uma 
hiperligação para um site ou uma célula da folha de cálculo onde se encontrem 
informações sobre a empresa respectiva. 
Sugestão: esta informação encontra-se disponível em muitos sites de 
instituições financeiras. Pode recorrer ao mesmo site utilizado para resolver o 
ponto 1 deste Exercício. 
4. Na mesma folha, crie um gráfico circular que traduza o peso relativo (em 
percentagem) dos valores máximos de cada um dos 6 títulos. 
Sistemas de Informação em Gestão 
 
7
 
5. Elabore um relatório explicando os passos dados e entregue-o à Equipa Docente 
num ficheiro do tipo *,pdf. 
Sistemas de Informação em Gestão 
 
8
Exercício 5 
Funções da categoria Estatística e a Ferramenta Análise de Dados 
1. Copie a tabela criada no ponto 1 do exercício anterior para um novo Livro de 
Excel. Atribua à folha o nome “Cotações”. 
2. Utilizando as funções estatísticas do Excel, calcule, sucessivamente, para cada 
um dos títulos: 
 Medidas de tendência central – média aritmética, média geométrica, média 
harmónica, mediana e moda; 
 Medidas de dispersão: variância, variância corrigida, desvio-padrão, desvio-
padrão corrigido, coeficiente de variação, soma do quadrado dos desvios e 
desvio absoluto médio; 
 Outras medidas: medida de curtose, medida de assimetria, máximo, mínimo, 2º 
maior valor, 2º menor valor, número de observações (registos por título) 
3. Recorrendo à ferramenta Análise de Dados calcule todas as medidas de 
estatística descritiva disponíveis. Apresente o resultado numa nova folha 
denominada “Indicadores”. 
Sugestão: caso a ferramenta Análise de Dados (Data Analysis) não se encontre 
disponível no menu Tools, instale-a a partir do comando Add-Ins do referido 
menu. 
4. Elabore um relatório explicando os passos dados e entregue-o à Equipa 
Docente num ficheiro do tipo *,pdf. 
 
 
Sistemas de Informação em Gestão 
 
9
Exercício 6 
(Extraído do livro “Domine a 110% o Excel 2003”, de Maria José Sousa) 
 
1. Crie a tabela ao lado – mapa de 
caracterização dos trabalhadores. 
 
 
2. Na mesma folha, recorrendo a 
funções estatísticas conhecidas, 
crie uma segunda tabela com os 
seguintes dados: 
 
 
Nota: nesta e nas restantes tabelas, as células com fundo sombreado (amarelo) 
contém fórmulas ou foram obtidascom recurso à ferramenta Análise de Dados. 
3. Apresente o quadro de 
correlações entre as variáveis 
absentismo, antiguidade e idade e, 
ao lado, faça a sua interpretação. 
 
 Na resolução deste podo pode utilizar a função =CORREL() ou recorrer à 
ferramenta Análise de Dados. 
4. Recorrendo à ferramenta Análise de 
Dados apresente um modelo de 
regressão linear que permita avaliar a 
importância da idade no absentismo. 
Apresente, outras medidas estatísticas 
e gráficos obtidos igualmente através 
do recurso à mesma ferramenta 
Análise de Dados. 
 
5. Formate os resultados de modo a obter um resultado semelhante ao 
apresentado na figura seguinte. 
Sistemas de Informação em Gestão 
 
10
 
6. Elabore um relatório explicando os passos dados e entregue-o à Equipa 
Docente num ficheiro do tipo *,pdf. 
 
 
Sistemas de Informação em Gestão 
 
11
Exercício 7 
Endereços absolutos e Funções da categoria Financeira 
Com recurso a funções da categoria Financeiras, crie, em três folhas de cálculo, um 
Mapa de Amortizações, uma Simulação de Investimento e uma Simulação de 
Empréstimo Bancário. 
Atenção: nas três tabelas, as células com fundo sombreado (amarelo) contêm fórmulas 
1. Altere o nome da primeira folha de cálculo para Amortizacao e, nela, crie a 
seguinte tabela: 
 
2. Altere o nome da segunda folha para Investimento e, nela, crie a seguinte 
tabela: 
 
Sistemas de Informação em Gestão 
 
12
Observação: Na indicação dos argumentos da função =FV() considere que o 
pagamento ocorre no início do período. 
3. Altere o nome da terceira folha para Emprestimo e, nela, crie a seguinte tabela: 
 
Observação: Na indicação dos argumentos da função =PMT() considere que o 
pagamento (mensal) ocorre no final do período. 
4. Elabore um relatório explicando os passos dados e entregue-o à Equipa 
Docente num ficheiro do tipo *,pdf. 
 
Nota: Este exercício baseia-se em exercícios do livro “Domine a 110% o Excel 2003”, 
de Maria José Sousa. 
 
Sistemas de Informação em Gestão 
 
13
Exercício 8 
Funções da categoria Procura e Referência e Tabelas de Dados (de Simulação) 
Este exercício baseia-se em exercícios extraídos do livro “Exercícios resolvidos com 
Excel para Economia e Gestão”, de Adelaide Carvalho, e aborda problemas típicos de 
gestão. 
Em todas as tabelas, as células com fundo sombreado (amarelo) contêm fórmulas 
1. Altere o nome da primeira folha de cálculo para Vencimentos e, nela, crie as 
tabelas: 
 
Dados do Problema: 
o vencimento base da categoria E é de 600 euros; 
as categorias estão ordenadas por ordem crescente de importância hierárquica; 
a diferença percentual entre os vencimentos de duas categorias consecutivas é de 
+10%; 
os vencimentos base são arredondados a 0 casas decimais; 
o subsídio de deslocação é 5% do vencimento base; 
a célula com o valor base destina-se a ser utilizada na 2ª parte deste exercício. 
2. Utilizando os dados do problema, calcule, sucessivamente: 
a) os vencimentos base de todas as categorias e de cada funcionário; 
b) os subsídios de deslocação, por categoria e por funcionário; 
Sistemas de Informação em Gestão 
 
14
c) o vencimento ilíquido de cada funcionário. 
d) pressupondo que o vencimento da categoria E será aumentado 75, 90 ou 100 
euros, verifique o efeito sobre a massa salarial. 
Sugestões: 
a) Utilize endereços absolutos nas fórmulas para evitar ter que introduzir mais do que 
uma vez a mesma função. 
b) Para calcular os vencimentos base e subsídios de deslocação de cada um dos 
funcionários, utilize uma função da categoria Consulta e Referência: =PROCV() - 
=VLOOKUP(), em inglês. 
c) Para resolver a alínea d) do enunciado, recorra a funcionalidade Tabela de Dados 
(Data Table) do menu Dados (Data). As tabelas de Dados, permitem simular valores 
(resultados) alterando dados (variáveis) do problema. Para definir a tabela de 
simulação:1º - seleccione a tabela D22H23; 2º - seleccione a opção Tabela do 
menu Dados e indique, como valor de entrada de linha, a célula H16. Confirme com 
<OK>. 
Solução: 
 
3. Elabore um relatório explicando os passos dados e entregue-o à Equipa Docente 
num ficheiro do tipo *,pdf. 
 
Sistemas de Informação em Gestão 
 
15
Exercício 9 
Funções das categorias Procura e Referência e Lógica; Tabelas de Dados (de 
Simulação) 
1. O estudo sobre o lançamento de um novo produto que se pretende lançar no 
mercado revelou os seguintes dados: 
 a) o custo variável por unidade produzida é de 50 euros; 
 b) os encargos gerais de fabrico fixos são de 800.000 euros; 
 c) a procura (PR) variará de acordo com o preço unitário de venda (PUV): 
 PUV = 70 ⇒ PR = 75.000 unid.; PUV = 80 ⇒ PR = 60.000; PUV = 90 ⇒ PR = 
40.000 
2. Altere o nome da segunda folha de cálculo para LucroEstimado e, nela, prepare 
um quadro de valores para determinar o melhor preço unitário de venda: 
 
Sugestões: 
a) Utilize endereços absolutos nas fórmulas para evitar ter que introduzir 
mais do que uma vez a mesma função. 
b) Para calcular os valores da procura (em euros) utilize uma função da 
categoria Consulta e Referência: =PROCH() - =HLOOKUP(), em inglês. 
c) Para calcular o lucro estimado subtraia ao valor da procura (em euros) o 
total de custos. 
c) Para determinar qual o preço que gera o maior lucro estimado recorra à 
Sistemas de Informação em Gestão 
 
16
função =SE() - =IF() em inglês. 
Solução: 
 
3. Elabore um relatório explicando os passos dados e entregue-o à Equipa 
Docente num ficheiro do tipo *,pdf. 
 
 
Sistemas de Informação em Gestão 
 
17
Exercício 10 
Tabelas de Dados com uma e duas variáveis; ferramentas para Criação de 
Cenários e para Atingir o Objectivo 
1. Utilização de Tabelas de Dados com uma variável 
Pretende-se calcular os preços de venda ao público de um determinado produto, tendo 
por base diferentes valores percentuais de margens de lucro. Neste exercício será 
criada uma tabela de simulação recorrendo à funcionalidade tabelas de dados. 
Altere o nome da primeira 
folha para Simulacao e, 
nela, construa a seguinte 
tabela: 
Nota: nesta e nas restantes 
tabelas, as células com 
fundo sombreado 
contêm fórmulas. 
 
1.1. Consideram-se vários 
fornecedores para o Produto 5, 
praticando diferentes preços. Tal 
realidade será retratada numa 
nova tabela que será utilizada na 
simulação. 
 
 
Com recurso à funcionalidade Tabela de Dados, calcule as margens de lucro obtidas 
de acordo com os respectivos preços de custo. 
 
Observações: 
• a Tabela de Dados/Simulação consiste 
no intervalo de células D12:E17; 
• a célula E12 resulta do cálculo da 
margem de lucro que servirá de base ao 
cálculo dos valores de saída; 
• $D$7 é a célula onde se encontram os valores de entrada (em coluna). 
 
2. Utilização de Tabelas de Dados com duas variáveis 
Sistemas de Informação em Gestão 
 
18
Pretende-se agora calcular o PVP considerando vários valores de entrada para duas 
variáveis – Preço de Custo e Margem de Lucro (%). 
2.1. Para tal é necessário construir uma tabela de duas entradas – as margens de 
lucro, nas linhas, e os preços de custo, nas colunas – tendo como valores de saída 
(as células) os valores dos PVP correspondentes. 
 No canto superior esquerdo, será introduzida a fórmula que permite calcular o PVP. 
 
 Esta fórmula será utilizada 
para obter os vários PVP 
de acordo com as 
margens e preços de 
custo considerados.) 
2.2. Novamente com recurso à 
funcionalidade Tabela de 
Dados, calcule os PVP com 
base nas três margens de lucro 
e nos dois preços de custo 
indicados. 
 
 
3. Criação de Cenários 
Pretende-se elaborar uma folha de cálculo que permita indicar diferentescenários 
relativos a um empréstimo, de acordo com a variação do valor e do prazo respectivos. 
3.1. Altere o nome de uma nova folha de cálculo para 
Cenários e crie uma tabela com os dados do problema: 
 Observações: 
 Célula C6: C3*80% 
 Célula C7: utilize a função =PGTO(). =PMT(), em 
inglês. 
 
3.2. Recorrendo ao comando Cenários (Scenarios) do menu Ferramentas (Tools), 
calcule o valor do Pagamento de acordo com a variação das variáveis Preço (valor 
do empréstimo) e Anos (prazo do empréstimo). Para tal, crie três cenários: 
 Cenário 1: Empréstimo de 10.000.000 euros, a 30 anos, com uma taxa de 17%; 
Sistemas de Informação em Gestão 
 
19
 Cenário 2: Empréstimo de 10.000.000 euros, a 35 anos, com uma taxa de 17%; 
 Cenário 3: Empréstimo de 15.000.000 euros, a 40 anos, com uma taxa de 17%; 
 
Observações a ter em conta na construção dos sumários: 
 as células variáveis a indicar são as células que contêm o valor do Preço (C3) e dos 
Anos (C5); 
 indique como comentário de cada um dos cenários a respectiva descrição (na 
tabela com o sumário do cenário, os comentários são visíveis na linha 4; 
 os valores dos cenários 1, 2 e 3 são os valores das variáveis Preço e Ano nos 
respectivos cenários; 
 após ter definido (adicionado) os três cenários, clique no botão Sumário (Summary) 
da janela Gestor de Cenários para obter a tabela acima reproduzida. 
 
4. Atingir Objectivo 
Com recurso à ferramenta Atingir Objectivo 
(Goal Seek), pretende-se elaborar uma folha 
de cálculo para determinar o valor do 
vencimento base que permita atingir um 
vencimento líquido de 1.750 euros. 
4.1. Altere o nome de uma nova folha para 
Objectivo e, nela, construa a tabela com o 
modelo que se pede: 
 
Sistemas de Informação em Gestão 
 
20
4.2. No menu Ferramentas, active a opção Atingir Objectivo (Goal Seek) e, na janela de 
diálogo aberta, indique os valores que permitem atingir o resultado pretendido para 
o Vencimento Líquido (1750) alterando o valor do Vencimento Base. 
 Após confirmar, é visível a tabela actualizada – para se atingir o vencimento líquido 
de 1.750 euros, é necessário um vencimento base de 2.583 euros. 
 
 
5. Elabore um relatório explicando os passos dados e entregue-o à Equipa Docente 
num ficheiro do tipo *,pdf. 
 
 
 
 
 
 
 
 
 
 
Este exercício baseia-se em exercícios extraídos do livro “Domine a 110% Excel 2003”, 
de Maria José Sousa.