Conteúdo
Formulação do problema
Suponha que a empresa onde você trabalha tenha três armazéns, de onde as mercadorias vão para cinco de suas lojas espalhadas por Moscou.
Cada loja é capaz de vender uma certa quantidade de mercadorias que conhecemos. Cada um dos armazéns tem uma capacidade limitada. A tarefa é escolher racionalmente de qual armazém para qual loja entregar as mercadorias, a fim de minimizar os custos totais de transporte.
Antes de iniciar a otimização, será necessário compilar uma tabela simples em uma planilha do Excel – nosso modelo matemático descrevendo a situação:
Entende-se que:
- A tabela amarela clara (C4:G6) descreve o custo de envio de um item de cada depósito para cada loja.
- As células roxas (C15:G14) descrevem a quantidade de bens necessária para cada loja vender.
- As células vermelhas (J10:J13) exibem a capacidade de cada depósito – a quantidade máxima de mercadorias que o depósito pode conter.
- As células amarelas (C13:G13) e azuis (H10:H13) são as somas de linha e coluna para células verdes, respectivamente.
- O custo total de envio (J18) é calculado como a soma dos produtos do número de mercadorias e seus custos de envio correspondentes – para cálculo, a função é usada aqui SUMPRODUCT (SUMPRODUTO).
Assim, nossa tarefa é reduzida à seleção de valores ótimos de células verdes. E para que o valor total da linha (células azuis) não exceda a capacidade do armazém (células vermelhas), e ao mesmo tempo cada loja receba a quantidade de mercadorias que precisa vender (a quantidade de cada loja no as células amarelas devem estar o mais próximo possível dos requisitos – células roxas).
Solução
Em matemática, esses problemas de escolha da distribuição ótima de recursos foram formulados e descritos há muito tempo. E, é claro, as maneiras de resolvê-los há muito foram desenvolvidas não por enumeração direta (que é muito longa), mas em um número muito pequeno de iterações. O Excel fornece ao usuário essa funcionalidade usando um suplemento. Soluções de Pesquisa (Solucionador) da guia Data (Encontro: Data):
Se na aba Data seu Excel não tem esse comando - tudo bem - isso significa que o suplemento simplesmente não está conectado ainda. Para ativá-lo abra Envie o, Em seguida, selecione parâmetros - Add-ons - Sobre (Opções — Suplementos — Ir para). Na janela que se abre, marque a caixa ao lado da linha que precisamos Soluções de Pesquisa (Solucionador).
Vamos executar o complemento:
Nesta janela, você precisa definir os seguintes parâmetros:
- Otimize a função de destino (Definir tdinheiro célula) – aqui é necessário indicar o objetivo principal final de nossa otimização, ou seja, caixa rosa com o custo total de envio (J18). A célula alvo pode ser minimizada (se for despesas, como no nosso caso), maximizada (se for, por exemplo, lucro) ou tentar trazê-la para um determinado valor (por exemplo, caber exatamente no orçamento alocado).
- Alterando Células Variáveis (By mudança células) – aqui indicamos as células verdes (C10: G12), variando os valores dos quais queremos alcançar nosso resultado – o custo mínimo de entrega.
- Consistente com as restrições (Assunto para que o Restrições) – uma lista de restrições que devem ser levadas em consideração ao otimizar. Para adicionar restrições à lista, clique no botão Adicionar (Adicionar) e insira a condição na janela que aparece. No nosso caso, esta será a restrição de demanda:
e limite do volume máximo de armazéns:
Para além das limitações óbvias associadas a fatores físicos (capacidade dos armazéns e meios de transporte, restrições orçamentais e de tempo, etc.), por vezes é necessário adicionar restrições “especiais para Excel”. Assim, por exemplo, o Excel pode facilmente providenciar para você “otimizar” o custo de entrega, oferecendo o transporte de mercadorias das lojas de volta ao armazém – os custos se tornarão negativos, ou seja, teremos lucro! 🙂
Para evitar que isso aconteça, é melhor deixar a caixa de seleção ativada. Tornar variáveis ilimitadas não negativas ou mesmo, às vezes, registrar explicitamente tais momentos na lista de restrições.
Depois de definir todos os parâmetros necessários, a janela deve ficar assim:
Na lista suspensa Selecionar um método de resolução, você também precisa selecionar o método matemático apropriado para resolver uma escolha de três opções:
- Método Simplex é um método simples e rápido para resolver problemas lineares, ou seja, problemas onde a saída é linearmente dependente da entrada.
- Método geral de gradiente rebaixado (OGG) – para problemas não lineares, onde existem dependências não lineares complexas entre dados de entrada e saída (por exemplo, a dependência de vendas em custos de publicidade).
- Busca evolutiva por uma solução – um método de otimização relativamente novo baseado nos princípios da evolução biológica (olá Darwin). Este método funciona muitas vezes mais do que os dois primeiros, mas pode resolver quase qualquer problema (não linear, discreto).
Nossa tarefa é claramente linear: entregue 1 peça – gasto 40 rublos, entregue 2 peças – gasto 80 rublos. etc., então o método simplex é a melhor escolha.
Agora que os dados para o cálculo foram inseridos, pressione o botão Achar uma solução (Resolver)para iniciar a otimização. Em casos graves com muitas células e restrições em mudança, encontrar uma solução pode levar muito tempo (especialmente com o método evolucionário), mas nossa tarefa para o Excel não será um problema - em alguns momentos, obteremos os seguintes resultados :
Observe como os volumes de abastecimento foram distribuídos de maneira interessante entre as lojas, sem exceder a capacidade de nossos armazéns e atendendo a todos os pedidos de quantidade de mercadorias necessária para cada loja.
Se a solução encontrada nos convém, podemos salvá-la ou reverter para os valores originais e tentar novamente com outros parâmetros. Você também pode salvar a combinação selecionada de parâmetros como Cenário. A pedido do usuário, o Excel pode construir três tipos Relatórios sobre o problema sendo resolvido em folhas separadas: um relatório sobre os resultados, um relatório sobre a estabilidade matemática da solução e um relatório sobre os limites (restrições) da solução, porém, na maioria dos casos, são de interesse apenas para especialistas .
Existem, no entanto, situações em que o Excel não consegue encontrar uma solução adequada. É possível simular tal caso se indicarmos em nosso exemplo as necessidades das lojas em quantidade maior que a capacidade total dos armazéns. Em seguida, ao realizar uma otimização, o Excel tentará chegar o mais próximo possível da solução e exibirá uma mensagem informando que a solução não pode ser encontrada. No entanto, mesmo neste caso, temos muitas informações úteis – em particular, podemos ver os “elos fracos” de nossos processos de negócios e entender as áreas de melhoria.
O exemplo considerado, é claro, é relativamente simples, mas escala facilmente para resolver problemas muito mais complexos. Por exemplo:
- Otimização da distribuição de recursos financeiros por item de despesa no plano de negócios ou orçamento do projeto. As restrições, neste caso, serão o valor do financiamento e o prazo do projeto, e o objetivo da otimização é maximizar os lucros e minimizar os custos do projeto.
- Otimização de agendamento de funcionários para minimizar o fundo salarial da empresa. As restrições, neste caso, serão a vontade de cada funcionário de acordo com o horário de trabalho e as exigências da tabela de pessoal.
- Otimização de investimentos – a necessidade de distribuir corretamente fundos entre vários bancos, títulos ou ações de empresas para, mais uma vez, maximizar os lucros ou (se mais importante) minimizar os riscos.
Em qualquer caso, complemento Soluções de Pesquisa (Solucionador) é uma ferramenta Excel muito poderosa e bonita e digna de sua atenção, pois pode ajudar em muitas situações difíceis que você precisa enfrentar nos negócios modernos.