Como pode uma empresa utilizar o Solver para determinar quais os projetos que deve realizar?
Todos os anos, uma empresa como a Eli Lilly precisa determinar quais medicamentos desenvolver; uma empresa como a Microsoft, que programas de software desenvolver; uma empresa como a Proctor & a Gamble, que novos produtos de consumo desenvolver. A funcionalidade Solver no Excel pode ajudar uma empresa a tomar estas decisões.
Como pode uma empresa utilizar o Solver para determinar quais os projetos que deve realizar?
A maioria das corporações deseja realizar projetos que contribuam com o maior valor presente líquido (VAL), sujeito a recursos limitados (geralmente capital e trabalho). Digamos que uma empresa de desenvolvimento de software está tentando determinar qual dos 20 projetos de software ela deve realizar. O VAL (em milhões de dólares) contribuído por cada projeto, bem como o capital (em milhões de dólares) e o número de programadores necessários durante cada um dos próximos três anos é dado na planilha Modelo Básico no arquivo Capbudget.xlsx, que é mostrado na Figura 30-1 na página seguinte. Por exemplo, o Projeto 2 gera 908 milhões de dólares. São necessários US$ 151 milhões durante o Ano 1, US$ 269 milhões durante o Ano 2 e US$ 248 milhões durante o Ano 3. O Projeto 2 requer 139 programadores durante o Ano 1, 86 programadores durante o Ano 2 e 83 programadores durante o Ano 3. As células E4:G4 mostram o capital (em milhões de dólares) disponível durante cada um dos três anos, e as células H4:J4 indicam quantos programadores estão disponíveis. Por exemplo, durante o Ano 1 estão disponíveis até $2,5 mil milhões em capital e 900 programadores.
A empresa deve decidir se deve realizar cada projeto. Vamos supor que não podemos realizar uma fração de um projeto de software; Se alocássemos 0,5 dos recursos necessários, por exemplo, teríamos um programa sem trabalho que nos traria receita de US$ 0!
O truque na modelagem de situações em que você faz ou não algo é usar células binárias variáveis. Uma célula alterável binária é sempre igual a 0 ou 1. Quando uma célula alterável binária que corresponde a um projeto é igual a 1, fazemos o projeto. Se uma célula alterável binária que corresponde a um projeto for igual a 0, não efetuamos o projeto. Configure o Solver para utilizar um intervalo de células binárias alteradas adicionando uma restrição: selecione as células alteradas que pretende utilizar e, em seguida, selecione Bin na lista na caixa de diálogo Adicionar Constraint.
Com este histórico, estamos prontos para resolver o problema de seleção de projeto de software. Como sempre acontece com um modelo do Solver, começamos por identificar a nossa célula de destino, as células variáveis e as restrições.
- Célula de destino. Maximizamos o VAL gerado pelos projetos selecionados.
- Células variáveis. Procuramos uma célula de mudança binária 0 ou 1 para cada projeto. Localizei estas células no intervalo A6:A25 (e atribuí o nome doit ao intervalo). Por exemplo, um 1 na célula A6 indica que realizamos o Projeto 1; um 0 na célula C6 indica que não efetuamos o Projeto 1.
- Constrangimentos. Precisamos garantir que, para cada Ano t (t=1, 2, 3), o Capital t do Ano usado seja menor ou igual ao Capital t do Ano disponível, e o Trabalho t do Ano utilizado seja menor ou igual ao Trabalho t do Ano disponível .
Como você pode ver, nossa planilha deve calcular para qualquer seleção de projetos o VPL, o capital usado anualmente e os programadores usados a cada ano. Na célula B2, utilizo a fórmula SOMARPRODUTO(doit;VAL) para calcular o VAL total gerado pelos projetos selecionados. (O nome do intervalo VAL refere-se ao intervalo C6:C25.) Para cada projeto com um 1 na coluna A, esta fórmula seleciona o VAL do projeto e para cada projeto com um 0 na coluna A, esta fórmula não capta o VAL do projeto. Portanto, somos capazes de calcular o VAL de todos os projetos, e nossa célula alvo é linear porque é calculada somando termos que seguem a forma (célula em mudança)*(constante). Da mesma forma, calculo o capital usado a cada ano e o trabalho usado a cada ano, copiando de E2 a F2:J2 a fórmula SOMARPRODUTO(doit,E6:E25).
Agora preencho a caixa de diálogo Parâmetros do Solver como mostra a Figura 30-2.
O nosso objetivo é maximizar o VAL dos projetos selecionados (célula B2). As nossas células variáveis (o intervalo denominado doit) são as células binárias variáveis de cada projeto. A restrição E2:J2<=E4:J4 garante que, durante cada ano, o capital e o trabalho utilizados sejam menores ou iguais ao capital e ao trabalho disponíveis. Para adicionar a restrição que torna as células variáveis binárias, clico em Adicionar na caixa de diálogo Parâmetros do Solver e, em seguida, seleciono Bin na lista no meio da caixa de diálogo. A caixa de diálogo Add Constraint deve aparecer como mostra a Figura 30-3.
O nosso modelo é linear porque a célula de destino é calculada como a soma dos termos que têm a forma (célula em mudança)*(constante) e porque as restrições de utilização de recursos são calculadas comparando a soma de (células variáveis)*(constantes) com uma constante.
Com a caixa de diálogo Parâmetros do Solver preenchida, clique em Resolver e teremos os resultados mostrados anteriormente na Figura 30-1. A empresa pode obter um VPL máximo de US$ 9.293 milhões (R$ 9,293 bilhões) escolhendo os Projetos 2, 3, 6–10, 14–16, 19 e 20.
Lidar com outras restrições
Por vezes, os modelos de seleção de projetos têm outras restrições. Por exemplo, suponha que se selecionarmos o Projeto 3, também devemos selecionar o Projeto 4. Uma vez que a nossa solução ideal atual seleciona o Projeto 3, mas não o Projeto 4, sabemos que a nossa solução atual não pode permanecer a ideal. Para resolver este problema, basta adicionar a restrição de que a célula de alteração binária para o Project 3 é menor ou igual à célula de alteração binária para o Project 4.
Você pode encontrar este exemplo na planilha If 3 then 4 na Capbudget.xlsx do arquivo, que é mostrada na Figura 30-4. A célula L9 refere-se ao valor binário relacionado com o Projeto 3 e a célula L12 ao valor binário relacionado com o Projeto 4. Ao adicionar a restrição L9<=L12, se escolhermos o Projeto 3, L9 é igual a 1 e nossa restrição força L12 (o binário do Projeto 4) a ser igual a 1. A nossa restrição também tem de deixar o valor binário na célula de alteração do Projeto 4 sem restrições se não selecionarmos o Projeto 3. Se não selecionarmos o Projeto 3, L9 será igual a 0 e nossa restrição permitirá que o binário do Projeto 4 seja igual a 0 ou 1, que é o que queremos. A nova solução ideal é mostrada na Figura 30-4.
Uma nova solução ideal é calculada se selecionar o Projeto 3 significa que também devemos selecionar o Projeto 4. Agora suponha que só conseguimos executar quatro projetos entre os Projetos 1 a 10. (Consulte a planilha No máximo 4 De P1–P10, mostrada na Figura 30-5.) Na célula L8, calculamos a soma dos valores binários associados aos Projetos 1 a 10 com a fórmula SOMA(A6:A15). Em seguida, adicionamos a restrição L8<=L10, que garante que, no máximo, 4 dos primeiros 10 projetos sejam selecionados. A nova solução ótima é mostrada na Figura 30-5. O VPL caiu para US$ 9,014 bilhões.
Resolução de problemas de programação binária e inteira
Os modelos de Solver lineares nos quais algumas ou todas as células variáveis devem ser binárias ou inteiras são geralmente mais difíceis de resolver do que os modelos lineares em que todas as células variáveis podem ser frações. Por esta razão, muitas vezes estamos satisfeitos com uma solução quase ideal para um problema de programação binário ou inteiro. Se o seu modelo do Solver for executado durante muito tempo, recomendamos que ajuste a definição Tolerância na caixa de diálogo Opções do Solver. (Ver Figura 30-6.) Por exemplo, uma definição de Tolerância de 0,5% significa que o Solver irá parar quando encontrar pela primeira vez uma solução viável que esteja dentro dos 0,5% do valor teórico da célula de destino ótima (o valor da célula de destino ótima teórica é o valor de destino ideal encontrado quando as restrições binárias e de número inteiro são omitidas). Muitas vezes somos confrontados com uma escolha entre encontrar uma resposta dentro de 10% do ideal em 10 minutos ou encontrar uma solução ideal em duas semanas de tempo de computador! O valor de Tolerância predefinido é 0,05%, o que significa que o Solver para quando encontra um valor de célula de Destino dentro de 0,05% do valor teórico ideal da célula de destino.
Problemas
- Uma empresa tem nove projectos em análise. O VAL adicionado por cada projeto e o capital necessário para cada projeto durante os dois anos seguintes são apresentados no quadro seguinte. (Todos os números estão em milhões.) Por exemplo, o Projeto 1 adicionará 14 milhões de USD em VAL e exigirá despesas de 12 milhões de USD durante o Ano 1 e de 3 milhões de USD durante o Ano 2. Durante o Ano 1, US$ 50 milhões em capital estão disponíveis para projetos e US$ 20 milhões estão disponíveis durante o Ano 2.
| VAL | Despesas do 1º ano | Despesas do segundo ano | |
|---|---|---|---|
| Projeto 1 | 14 | 12 | 3 |
| Projeto 2 | 17 | 54 | 7 |
| Projeto 3 | 17 | 6 | 6 |
| Projeto 4 | 15 | 6 | 2 |
| Projeto 5 | 40 | 30 | 35 |
| Projeto 6 | 12 | 6 | 6 |
| Projeto 7 | 14 | 48 | 4 |
| Projeto 8 | 10 | 36 | 3 |
| Projeto 9 | 12 | 18 | 3 |
- Se não podemos realizar uma fração de um projeto, mas devemos realizar todo ou nenhum projeto, como podemos maximizar o VPL?
- Suponhamos que, se o Projeto 4 for realizado, o Projeto 5 deve ser realizado. Como podemos maximizar o VAL?
Uma editora está a tentar determinar qual dos 36 livros deverá publicar este ano. O Pressdata.xlsx de arquivo fornece as seguintes informações sobre cada livro:
- Receita projetada e custos de desenvolvimento (em milhares de dólares)
- Páginas em cada livro
- Se o livro é voltado para um público de desenvolvedores de software (indicado por um 1 na coluna E)
Uma editora pode publicar livros que totalizam até 8500 páginas este ano e deve publicar pelo menos quatro livros voltados para desenvolvedores de software. Como pode a empresa maximizar o seu lucro?
Sobre o artigo
Este artigo foi adaptado de Microsoft Office Excel 2007 Data Analysis and Business Modeling por Wayne L. Winston.
Este livro em estilo de sala de aula foi desenvolvido a partir de uma série de apresentações de Wayne Winston, um conhecido estatístico e professor de negócios especializado em aplicações criativas e práticas do Excel.