Sugestões de otimização com OpenOffice Solver

Sugestões de Dez Temas em Otimização Com o Uso do Solver do OpenOffice Calc

Trago, nesta postagem, 10 sugestões de temas em otimização para demonstrar o poder do Solver do OpenOffice Calc:

1. Otimização de Rota de Entregas (Problema do Caixeiro Viajante): Crie um exemplo simples de um entregador que precisa visitar várias localidades e voltar ao ponto inicial, minimizando a distância total percorrida.
2. Planejamento de Produção em uma Fábrica: Modele um problema de alocação de recursos e tempo para fabricar diferentes produtos, considerando limites de matéria-prima e tempo disponível.
3. Problema de Dieta (Nutrição Ótima): Determine uma combinação de alimentos que minimize custos enquanto satisfaz as necessidades nutricionais diárias.
4. Alocação de Recursos em Projetos: Demonstre como alocar um orçamento limitado entre diferentes projetos para maximizar o retorno esperado.
5. Escalonamento de Turnos de Funcionários: Planeje turnos de funcionários para cobrir horários de trabalho, minimizando custos e respeitando as preferências dos trabalhadores.
6. Distribuição de Estoque em Lojas: Otimize a alocação de estoques entre lojas para maximizar as vendas e minimizar os custos de transporte.
7. Problema de Mistura de Combustíveis: Encontre a combinação ideal de diferentes combustíveis para atender a requisitos mínimos de qualidade e reduzir custos.
8. Design de Campanhas de Marketing: Modele como alocar um orçamento de marketing em diferentes canais (TV, rádio, internet) para maximizar o alcance ou o retorno financeiro.
9. Otimização de Portfólio de Investimentos: Distribua investimentos em diferentes ações para maximizar o retorno esperado, minimizando o risco (desvio padrão).
10. Planejamento de Transporte Logístico: Modele como transportar mercadorias de um armazém para vários destinos, minimizando os custos de transporte e atendendo à demanda.

Esses temas podem ser simplificados para uso no OpenOffice Solver, e cada um deles oferece uma oportunidade para explorar técnicas de programação linear ou não linear de forma prática e visual.

Detalhamento do Procedimento para "Otimização de Portfólio de Investimentos"

O objetivo é alocar um orçamento em diferentes opções de investimento para maximizar o retorno esperado, minimizando o risco.

Premissas do Problema (exemplo)

• Você possui um orçamento total a ser investido (ex: R$ 100.000,00).
• Há 3 ativos ou fundos de investimento, cada um com:
• Retorno esperado (%).
• Desvio padrão (%) como medida de risco.
• Deseja maximizar o retorno esperado, mas com um limite máximo aceitável de risco.

Preparação dos Dados

Abra o OpenOffice Calc e crie uma nova planilha. Organize os dados da seguinte forma (valores em decimal):

Fundo A0.64%3.46%0.00%=D2*B10Fundo B0.45%2.31%42.92%=D3*B10Fundo C0.80%4.33%57.08%=D4*B10

Abaixo da tabela, defina o total do investimento:

• Célula B10: Orçamento total (exemplo: R$ 100.000,00).
• Célula D6: Soma de todas as alocações percentuais: \(=SUM(D2:D4)\)
• Célula E6: Soma de todas as alocações em valores absolutos: \(=SUM(E2:E4)\)

Insira a fórmula do Retorno Esperado Total:

• Célula B12: \(=SUMPRODUCT(B2:B4;D2:D4)\)

Calcule o Risco Total (considerando correlações):

• Em uma célula (ex: B14), use a fórmula do desvio padrão do portfólio, implementada via matriz de covariância ou diretamente no Solver.
• Exemplo simplificado (sem correlações, para referência):\(=SUMPRODUCT(C2:C4;D2:D4)\)
• Para o risco total com correlações, use uma célula auxiliar for each term:\( \sqrt{\text{SUMPRODUCT}(D2:D4, D2:D4, C2:C4, C2:C4 * \text{matriz\_correlacao})} \), onde `matriz_correlacao` é a matriz de correlações.

Configuração do Solver

Para ativar o Solver no OpenOffice Calc:

• Vá em Ferramentas > Solver.
• Defina o problema:
• Célula alvo: B12 (Retorno Esperado Total).
• Otimizador: Maximizar.
• Células variáveis: D2:D4 (Percentual de Alocação por Ativo).
• Inserir restrições:
• D6 = 1 (A soma dos percentuais deve ser 100%).
• D2:D4 >= 0 (Não pode haver alocação negativa).
• B14 <= 0,0346 (Limite de Risco mensal, equivalente a 12% anualizado).
• E6 = B10 (Valor total disponível para investir, R$ 100.000,00).
• Clique em "Resolver" e aguarde o resultado.

Análise dos Resultados

O Solver ajustará os valores em D2:D4 para maximizar o retorno esperado dentro das restrições fornecidas. Verifique:

• A soma dos percentuais em D6.
• O retorno esperado em B12.
• O risco total em B14.

Exemplo Prático

Orçamento: R$ 100.000,00. Fundos e parâmetros (mensais, em decimal):

• Fundo A: 0.64% retorno mensal, 3.46% risco mensal.
• Fundo B: 0.45% retorno mensal, 2.31% risco mensal.
• Fundo C: 0.80% retorno mensal, 4.33% risco mensal.

Restrições:

• Máximo de 3.46% de risco mensal (equivalente a 12% anualizado).

Resultado do Solver:

Fundo A0.00%R$ 0.00Fundo B42.92%R$ 42920.00Fundo C57.08%R$ 57080.00Total:100%R$ 100.000,00

Nota: Inverter os pesos entre Fundo A e C aumenta o risco acima do limite de 12% anualizado, reduzindo o retorno otimizado.

Detalhamento do Cálculo de Risco (Desvio-Padrão)

1. Obtenção do Risco Individual

O desvio-padrão reflete a volatilidade dos retornos de um ativo. Para calcular o risco individual:

• Obtenha os preços históricos dos ativos para um período consistente (ex: mensal).
• Calcule os retornos periódicos do ativo: \( R_t = \frac{P_t - P_{t-1}}{P_{t-1}} \)
• Calcule o desvio-padrão com =STDEVP no OpenOffice Calc.
• Exemplo: Para retornos mensais do Fundo A em A2:A13, use: =STDEVP(A2:A13)

2. Cálculo do Risco Total

O risco total do portfólio combina os riscos individuais e suas correlações:

\( \sigma_P = \sqrt{\sum_{i=1}^n \sum_{j=1}^n w_i w_j \rho_{ij} \sigma_i \sigma_j}\)

• Onde:
• \(\sigma_i, \sigma_j\): Desvios-padrão dos ativos.
• \(\rho_{ij}\): Correlação entre os retornos dos ativos.
• \(w_i, w_j\): Percentuais de alocação.

Dados Fictícios

Tabela de Retornos Mensais

JaneiroR$ 100.00-%R$ 100.00-%R$ 100.00-%FevereiroR$ 100.330.33%R$ 99.93-0.07%R$ 108.288.28%MarçoR$ 100.08-0.25%R$ 100.210.28%R$ 110.772.30%AbrilR$ 96.74-3.33%R$ 99.74-0.47%R$ 107.73-2.74%MaioR$ 102.335.78%R$ 99.68-0.06%R$ 109.451.60%JunhoR$ 104.872.48%R$ 99.29-0.39%R$ 108.85-0.55%JulhoR$ 102.23-2.52%R$ 101.882.60%R$ 103.50-4.91%AgostoR$ 102.660.42%R$ 102.500.61%R$ 97.25-6.04%SetembroR$ 99.56-3.02%R$ 98.98-3.43%R$ 100.633.47%OutubroR$ 101.772.22%R$ 96.90-2.11%R$ 102.581.95%NovembroR$ 99.70-2.03%R$ 100.393.60%R$ 102.17-0.40%DezembroR$ 106.687.01%R$ 104.754.35%R$ 108.135.83%

Tabela de Resumo de Retornos

Fundo A0.6434%7.72%3.4645%12.00%Fundo B0.4472%5.37%2.3133%8.01%Fundo C0.7974%9.57%4.3315%15.00%

Cálculo do Risco Total do Portfólio

O cálculo do risco total considera a correlação entre os ativos, seus respectivos riscos individuais e os pesos no portfólio.

Dados dos Ativos

Fundo A0.6434%3.4645%0.00%Fundo B0.4472%2.3133%42.92%Fundo C0.7974%4.3315%57.08%

(Observação: O risco mensal foi calculado pelo desvio-padrão populacional (STDEVP) dos retornos mensais, e os pesos foram obtidos após rodar o Solver.)

Matriz de Correlação

Fundo A1.000.240.39Fundo B0.241.00-0.11Fundo C0.39-0.111.00

Resultados

Risco Total Mensal (σ_T)2.5610%Risco Total Anualizado8.8715%

Veja, em complemento, estes vídeos sobre otimização:

Maximize a satisfação! Técnica para escolher o Destino de viagemProgramação Linear