MÉTODOS QUANTITATIVOS COM EXCEL 52 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-852-2 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 – Otimização ? Exercício 1.1: Solver para média aritmética ? Exercício 1.2: Sistema de equações para lotes de peúgas ? Exercício 1.3: Solver para lotes de peúgas ? Exercício 1.4: Resolução gráfica para lotes de peúgas ? Exercício 1.5: Representação da função objetivo ? Exercício 1.6: Função objetivo com animação ? Exercício 1.7: Simplex para lotes de peúgas ? Exercício 1.8: Quadros inicial e ótimo do Simplex ? Exercício 1.9: Batatas ou milho ? Exercício 1.10: Produção de confeções ? Exercício 1.11: Detetores de incêndio ? Exercício 1.12: Produção de quatro artigos ? Exercício 1.13: Produção de novos modelos ? Exercício 1.14: Contratação de pessoal ? Exercício 1.15: Produção de ração ? Exercício 1.16: Seleção de investimentos ? Exercício 1.17: Duração de projeto
? Exercício 1.18: Distribuição de produtos ? Exercício 1.19: Transporte de produtos ? Exercício 1.20: Plano de carregamento ? Exercício 1.21: Atribuição de tarefas ? Exercício 1.22: Preços sombra ? Exercício 1.23: Relatórios do Solver ? Exercício 1.24: Simplex para problema de mínimo ? Exercício 1.25: Método das duas fases 2 – Simulação ? Exercício 2.1: Lançamento de um dado ? Exercício 2.2: Gráfico das pontuações médias do dado ? Exercício 2.3: Extração de bolas de um saco ? Exercício 2.4: Jogo de dados ? Exercício 2.5: Amostragem dita de "Monte Carlo" ? Exercício 2.6: Procura para 10 dias ? Exercício 2.7: Processo de amostragem ? Exercício 2.8: Procura por cliente e caixa ? Exercício 2.9: Juro médio anual ? Exercício 2.10: Venda de jornais ? Exercício 2.11: Medidas dos resultados das simulações ? Exercício 2.12: Venda de calendários ? Exercício 2.13: Modelo para venda de cadeiras ? Exercício 2.14: Venda de cadeiras
? Exercício 2.15: Amostragem de distribuição exponencial ? Exercício 2.16: À boleia ? Exercício 2.17: À boleia – lugar na fila ? Exercício 2.18: Ainda à boleia – mais simulações ? Exercício 2.19: Amostragem para procura durante o prazo de entrega ? Exercício 2.20: Procura durante o prazo de entrega ? Exercício 2.21: Probabilidade da procura semanal ? Exercício 2.22: Duração de projeto ? Exercício 2.23: Chegada, espera e atendimento ? Exercício 2.24: Gráfico de chegada, espera e atendimento ? Exercício 2.25: Atendimento ao público (I) ? Exercício 2.26: Atendimento ao público (II) ? Exercício 2.27: Percentagem de ocupação do servidor Anexo – Edição e Execução da Função Durac ? A.1: Edição da função Durac ? A.2: Execução da função Durac 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 Este livro visa promover a utilização das folhas de cálculo no ensino e aprendizagem de métodos quantitativos que compõem os conteúdos programáticos de diversas disciplinas de cursos de licenciatura e de mestrado em Economia, Gestão e Engenharia. Os exercícios que selecionámos apresentam o Excel 2016 como ferramenta imprescindível à resolução de problemas de métodos quantitativos, já que efetuam cálculos morosos e complexos e libertam tempo que poderá ser dedicado à modelação ou à experimentação de novas soluções através da construção de cenários alternativos. As resoluções dos exercícios foram aprimoradas, de modo a privilegiarem 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. Métodos Quantitativos com Excel – 52 Exercícios é constituído por dois capítulos: “1. Otimização” e “2. Simulação”. O primeiro capítulo resolve 25 exercícios e o segundo 27 exercícios de Economia, Gestão e disciplinas auxiliares (vide Figura 1).
Figura 1. Estrutura de Métodos Quantitativos com Excel – 52 Exercícios
Os 25 exercícios do primeiro capítulo ilustram a modelação de problemas de programação linear e a sua resolução recorre a funcionalidades e
suplementos do Excel, nomeadamente o gerador de gráficos, a análise de hipóteses e o Solver (vide Figura 2).
Figura 2. Tipos dos exercícios resolvidos, métodos de resolução e suplemento Solver do Excel
Os 27 exercícios do segundo capítulo ilustram a simulação discreta, modelando imitações simplificadas de operações de gestão, executando-as muitas vezes e analisando os resultados dessas execuções para intuir a ação da gestão em ambiente de incerteza (vide Figura 3). A Estatística é, por conseguinte, matéria subjacente à resolução dos exercícios.
Figura 3. Problemas de Simulação e funcionalidades essenciais à sua resolução
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 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. OTIMIZAÇÃO Este capítulo versa a programação linear, apresentando o suplemento Solver e implementando vários modelos matemáticos de resolução de programas lineares. Os 25 exercícios que apresentamos modelam e resolvem problemas de maximização e minimização de funções objetivo sujeitas a conjuntos de restrições lineares. Estes exercícios cobrem áreas diversas, nomeadamente: definição de quantidades a produzir, seleção de investimentos, transportes, planeamento cronológico de projetos e afetação de recursos. A resolução destes exercícios recorre essencialmente ao Solver, mas também implementa soluções gráficas, sistemas de inequações e algoritmos – Simplex e suas variantes para problemas de minimização –, e gera e interpreta relatórios de análise de sensibilidade. 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 os suplementos Analysis ToolPak e Solver.
? Exercício 1.1: Solver para média aritmética Determine, usando o Solver, a classificação média dos seguintes alunos: Ana Rui Teresa Eva Miguel João 12
13
15
14
10
11
Formulação do problema 1. Minimização da soma dos desvios das classificações relativamente à classificação média Min Classificação do aluno i: Ci Média aritmética: Média Somatório dos quadrados dos desvios: DQ Resolução do problema 1. Digite os nomes e as classificações dos 6 alunos em A3:F4 2. Escolha B7 para representar a média aritmética 3. Calcule o somatório dos desvios das classificações relativamente à média 3.1. digitando =A4-$B$7 em A5 3.2. copiando A5 para B5:F5 3.3. digitando =SOMA(A5:F5) em G5 4. Minimize o somatório dos desvios relativamente à média 4.1. escolhendo Dados – Análise – Solver 4.2. selecionando 4.2.1. G5 em Definir Objetivo 4.2.2. 0 em Valor de 4.2.3. B7 em Alterando as Células de Variável 4.2.4. GRG Não Linear em Selec. Método Resolução Resultado esperado
? Exercício 1.2: Sistema de equações para lotes de peúgas Um vendedor dispõe de 84 pares de peúgas de lã e 24 pares de peúgas de algodão, que pretende vender em lotes A e B: a) Lote A, composto por 4 pares de peúgas de lã e 2 pares de peúgas de algodão; b) Lote B, composto por 8 pares de peúgas de lã e 2 pares de peúgas de algodão. Os preços de venda dos lotes A e B são, respetivamente, 6 e 8 unidades monetárias. O vendedor quer saber o número de lotes A e B que deve vender para maximizar as receitas. Formulação do problema 1. Variáveis de decisão, função objetivo e restrições do programa linear Número de lotes A que o vendedor deve fazer: A Número de lotes B que o vendedor deve fazer: B Max
Suj. a
2. Representação matricial do sistema de inequações das restrições lineares:
Resolução do problema 1. Digite em A3:F11
2. Escolha 2.1. F9 para representar o número de lotes A 2.2. F10 para representar o número de lotes B
3. Resolva o sistema de equações 3.1. selecionando F9:F10 3.2. digitando =MATRIZ.MULT(MATRIZ.INVERSA(C6:D7);F6:F7) 3.3. premindo
4. Calcule a função objetivo da solução 4.1. digitando =C4 * F9 + D4 * F10 em F11 Resultado esperado
? Exercício 1.3: Solver para lotes de peúgas Determine a solução do programa linear do Exercício 1.2, recorrendo ao Solver do Excel. Resolução do problema 1. Escolha
1.1. C9 para representar o número de lotes A 1.2. D9 para representar o número de lotes B 2. Digite os coeficientes das variáveis da função objetivo, os coeficientes das variáveis das inequações lineares e os coeficientes dos termos independentes das inequações lineares em A3:F9
3. Digite a função objetivo em F9 3.1. =SOMARPRODUTO(C4:D4;C9:D9) 4. Calcule os pares de peúgas afetados à formação dos lotes A e B 4.1. digitando =SOMARPRODUTO(C6:D6;$C$9:$D$9) em E6 4.2. copiando E6 para E7 5. Resolva o programa linear 5.1. escolhendo Dados – Análise – Solver 5.2. premindo Resolver
Resultado esperado
? Exercício 1.4: Resolução gráfica para lotes de peúgas Determine graficamente a solução do programa linear do Exercício 1.2. Formulação do problema 1. O conjunto-solução do sistema de inequações lineares:
2. As retas 4A + 8B = 84, 2A + 2B = 24 e os eixos ortogonais do 1.º quadrante delimitam a área poligonal de valores admissíveis Resolução do problema 1. Digite em A3:F7
2. Calcule alguns pontos das retas B=(84-4*A)/8, B=(24-2*A)/2 2.1. atribuindo valores à variável independente A 2.1.1. preencha A11:A22 com a série 0, 2, 4, …, 20 e 21 2.2. calculando os respetivos valores da variável dependente das 1.ª e 2.ª retas 2.2.1. digite 2.2.1.1. A em A10 2.2.1.2. ="B=("&F6&-C6&"*A)/"&D6 em B10 2.2.1.3. ="B=("&F7&-C7&"*A)/"&D7 em C10 2.2.1.4. =($F$6-$C$6*A11)/$D$6 em B11 2.2.1.5. =($F$7-$C$7*A11)/$D$7 em C11 2.2.2. copie 2.2.2.1. B11 para B12:B22 2.2.2.2. C11 para C12:C17 3. Construa o gráfico das 2 retas 3.1. selecionando 3.1.1. A10:C22 3.1.2. Inserir – Gráficos – Inserir Gráfico de Dispersão (X,Y) ou de Bolhas – Dispersão com Linhas Retas 3.2. definindo as escalas dos eixos ortogonais 3.2.1. selecione 3.2.1.1. o eixo das abcissas
3.2.1.2. 3.2.2. digite 3.2.2.1. 3.2.2.2. 3.2.2.3. 3.2.3. selecione 3.2.3.1. 3.2.3.2.
Ferramentas de Gráfico – Formatar – Seleção Atual – Formatar Seleção – Opções de Eixo 0 em Mínimo 22 em Máximo 1 em Unidades – Principal o eixo das ordenadas Ferramentas de Gráfico – Formatar – Seleção Atual – Formatar Seleção – Opções de Eixo
3.2.4. digite 3.2.4.1. 0 em Mínimo 3.2.4.2. 13 em Máximo 3.2.4.3. 1 em Unidades – Principal 3.3. definindo os títulos e a legenda apropriados 4. Altere outros formatos do gráfico 4.1. selecionando 4.1.1. os respetivos elementos (títulos, legendas, barras, eixos, fundo do gráfico, etc.) 4.1.2. Ferramentas de Gráfico – Formatar 4.1.3. Seleção Atual – Formatar Seleção Resultado esperado
O conjunto de soluções é um conjunto poligonal convexo limitado. Toda a função linear Z definida sobre um polígono convexo atinge um máximo (ou mínimo) num dos seus pontos extremos: A
B
Z
0
0
0
0
10,5 84
3
9
12 0
90 solução ótima 72
? Exercício 1.5: Representação da função objetivo Acrescente à resolução gráfica do Exercício 1.4 a representação da função objetivo para valores de Z = 60, 80, 90, 120. Formulação do problema 1. Representação das retas que correspondem a diversos valores arbitrários de Z: 6A + 8B = 60 6A + 8B = 80 6A + 8B = 90 6A + 8B = 120 Estas retas são paralelas e têm declive m = Resolução do problema 1. Copie A3:F9 e A10:C22 da folha 1.4 para nova folha 1.5 2. Copie o gráfico de 1.4 para a nova folha e altere a origem dos dados 2.1. selecionando 2.1.1. o gráfico 2.1.2. Ferramentas de gráfico – Estrutura – Dados – Selecionar Dados 2.1.3. digitando ='1.5'!$A$10:$C$22 em Intervalo de dados do gráfico 3. Defina valores arbitrários da função objetivo 3.1. digite 60, 80, 90 e 120 em H6:K6 4. Defina as 4 equações correspondentes aos valores da função objetivo 4.1. digitando ="Z="&H6 em D10
4.2. copiando D10 para E10:G10 5. Calcule alguns pontos das 4 retas 5.1. digitando =(H$6-$C$4*$A11)/$D$4 em D11 5.2. copiando 5.2.1. D11 para E11:G11 5.2.2. D11:G11 para D12:G22
6. Desenhe no gráfico as 4 retas dos valores da função objetivo 6.1. selecionando 6.1.1. D10:G22 6.1.2. Base – Área de Transferência – Copiar 6.1.3. o gráfico 6.1.4. Base – Área de Transferência – Colar 6.2. definindo 6.2.1. as escalas dos eixos ortogonais 6.2.2. os títulos e a legenda apropriados 6.2.3. o tracejado das retas das funções objetivos 6.2.3.1. selecione
6.2.3.1.1. cada reta 6.2.3.1.2. Ferramentas de Gráfico – Formatar – Seleção Atual – Formatar Seleção – Opções de Série – Linha 6.2.3.1.3. Pontos quadrados em Tipo de traço Resultado esperado