Excel 2020: Encontre as melhores soluções com o Solver

Se você tiver o Excel, terá o Solver. Pode não estar habilitado, mas você o tem. Para ativar o Solver no Excel, pressione Alt + T seguido por I . Adicione uma marca de seleção ao lado do Suplemento Solver.

Para usar o Solver com sucesso, você deve construir um modelo de planilha que tenha três elementos:

• Deve haver uma única célula de objetivo. Esta é uma célula que você deseja minimizar, maximizar ou definir para um valor específico.
• Pode haver muitas células de entrada. Esta é uma melhoria fundamental em relação ao Goal Seek, que pode lidar com apenas uma célula de entrada.
• Pode haver restrições.

Seu objetivo é construir os requisitos de agendamento para um parque de diversões. Cada funcionário trabalhará cinco dias seguidos e depois terá dois dias de folga. Existem sete maneiras diferentes de agendar alguém para cinco dias consecutivos e dois dias de folga. Eles são mostrados como texto em A4: A10 na figura abaixo. As células azuis em B4: B10 são as células de entrada. Aqui é onde você especifica quantas pessoas você tem trabalhando em cada horário.

A célula da meta é a folha de pagamento total / semana, mostrada em B17. Isso é matemática simples: Total de pessoas de B11 vezes R$ 68 de salário por pessoa por dia. Você pedirá ao Solver para encontrar uma maneira de minimizar a folha de pagamento semanal.

A caixa vermelha mostra valores que não serão alterados. É quantas pessoas você precisa para trabalhar no parque em cada dia da semana. Você precisa de pelo menos 30 pessoas nos dias movimentados de fim de semana – mas apenas 12 na segunda e terça-feira. As células laranja usam SOMARPRODUTO para calcular quantas pessoas serão agendadas por dia, com base nas entradas nas células azuis.

Os ícones na linha 15 indicam se você precisa de mais ou menos pessoas ou se tem exatamente o número certo de pessoas.

Primeiro, tentei resolver esse problema sem o Solver. Fui com 4 funcionários por dia. Isso foi ótimo, mas não tive gente suficiente no domingo. Então, comecei a aumentar as agendas para conseguir mais funcionários aos domingos. Acabei com algo que funciona: 38 funcionários e R$ 2.584 de folha de pagamento semanal.

Claro, existe uma maneira mais fácil de resolver esse problema. Clique no ícone Solver na guia Dados. Diga ao Solver que você está tentando definir o valor mínimo da folha de pagamento em B17. As células de entrada são B4: B10.

As restrições se enquadram em categorias óbvias e não tão óbvias.

A primeira restrição óbvia é que D12: J12 deve ser > = D14: J14 .

Mas, se você tentasse executar o Solver agora, obteria resultados bizarros com números fracionários de pessoas e possivelmente um número negativo de pessoas trabalhando em determinados horários.

Embora pareça óbvio para você que você não pode contratar 0,39 pessoas, você precisa adicionar restrições para dizer ao Solver que B4: B10 são > = 0 e que B4: B10 são inteiros.

Escolha Simplex LP como o método de solução e clique em Solve. Em alguns momentos, o Solver apresenta uma solução ótima.

O Solver encontra uma maneira de cobrir a equipe do parque de diversões usando 30 funcionários em vez de 38. A economia por semana é de US $ 544 – ou mais de US $ 7.000 durante o verão.

Observe as cinco estrelas abaixo de Funcionários necessários na figura acima. O cronograma proposto pelo Solver atende às suas necessidades exatas de cinco dos sete dias. O subproduto é que você terá mais funcionários às quartas e quintas do que realmente precisa.

Posso entender como o Solver surgiu com essa solução. Você precisa de muitas pessoas no sábado, domingo e sexta-feira. Uma maneira de fazer as pessoas chegarem nesses dias é dar-lhes folga na segunda e na terça. É por isso que o Solver deu folga a 18 pessoas na segunda e na terça.

Mas só porque o Solver apresentou uma solução ótima não significa que não existam outras soluções igualmente ótimas.

Quando estava apenas adivinhando a equipe, não tinha realmente uma boa estratégia.

Agora que o Solver me deu uma das soluções ideais, posso colocar meu chapéu lógico. Ter 28 funcionários em idade universitária na quarta e quinta-feira, quando você só precisa de 15 ou 18 funcionários, vai causar problemas. Não haverá o suficiente para fazer. Além disso, com a contagem de cabeças exata em cinco dias, você terá que chamar alguém para horas extras se outra pessoa ligar dizendo que está doente.

Confio no Solver, pois preciso de 30 pessoas para fazer este trabalho. Mas aposto que posso reorganizar essas pessoas para equilibrar a programação e fornecer um pequeno buffer nos outros dias.

Por exemplo, dar a alguém folga na quarta e quinta-feira também garante que a pessoa esteja no trabalho na sexta, no sábado e no domingo. Então, eu movo manualmente alguns trabalhadores da linha de segunda, terça para a linha de quarta, quinta. Eu continuo conectando manualmente diferentes combinações e venho com a solução mostrada abaixo, que tem a mesma despesa de folha de pagamento do Solver, mas intangíveis melhores. A situação de excesso de pessoal agora existe em quatro dias em vez de dois. Isso significa que você pode lidar com ausências de segunda a quinta-feira sem ter que ligar para alguém do fim de semana.

É ruim que eu consegui encontrar uma solução melhor do que o Solver? Não. O fato é que eu não teria conseguido chegar a essa solução sem usar o Solver. Depois que o Solver me deu um modelo que minimizou os custos, pude usar a lógica sobre intangíveis para manter a mesma folha de pagamento.

Foto do título: MontyLov em Unsplash.com