Como uma empresa pode usar o Solver para determinar quais projetos ela deve realizar?
A cada ano, uma empresa como a Eli Lilly precisa determinar quais medicamentos desenvolver; uma empresa como a Microsoft, quais programas de software desenvolver; uma empresa como a Proctor & Gamble, que novos produtos de consumo desenvolver. O recurso Solver no Excel pode ajudar uma empresa a tomar essas decisões.
Como uma empresa pode usar o Solver para determinar quais projetos ela deve realizar?
A maioria das empresas deseja realizar projetos que contribuam com o maior valor presente líquido (VPL), sujeitos a recursos limitados (geralmente capital e trabalho). Digamos que uma empresa de desenvolvimento de software esteja tentando determinar qual dos 20 projetos de software ela deve realizar. O VPL (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, são fornecidos na planilha do Modelo Básico no arquivo Capbudget.xlsx, que é mostrado na Figura 30-1 na próxima página. Por exemplo, o Projeto 2 rende US$ 908 milhões. Requer 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, até US$ 2,5 bilhões em capital e 900 programadores estão disponíveis.
A empresa deve decidir se deve realizar cada projeto. Vamos supor que não possamos realizar uma fração de um projeto de software; Se alocarmos 0,5 dos recursos necessários, por exemplo, teríamos um programa não funcional que nos traria uma receita de US$ 0!
O truque na modelagem de situações em que você faz ou não faz algo é usar células binárias variáveis. Uma célula binária que muda sempre é igual a 0 ou 1. Quando uma célula binária variável que corresponde a um projeto é igual a 1, nós fazemos o projeto. Se uma célula binária que corresponde a um projeto for igual a 0, não faremos o projeto. Você configura o Solver para usar um intervalo de células binárias que mudam adicionando uma restrição. Selecione as células que mudam que deseja usar e escolha Compartimento na lista da caixa de diálogo Adicionar restrição.
Com esse pano de fundo, estamos prontos para resolver o problema de seleção de projetos de software. Como sempre acontece com um modelo Solver, começamos identificando nossa célula de destino, as células variáveis e as restrições.
- Célula de destino. Maximizamos o VPL gerado por projetos selecionados.
- Células variáveis. Procuramos uma célula de alteração binária 0 ou 1 para cada projeto. Localizei essas células no intervalo A6:A25 (e nomeei o intervalo doit). Por exemplo, um 1 na célula A6 indica que realizamos o Projeto 1; um 0 na célula C6 indica que não realizamos o Projeto 1.
- Restrições. Precisamos garantir que, para cada ano t (t = 1, 2, 3), o ano t de capital usado seja menor ou igual ao ano t de capital disponível, e o ano t de trabalho usado seja menor ou igual ao ano t de trabalho 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, uso a fórmula SOMARPRODUTO(doit,NPV) para calcular o VPL total gerado pelos projetos selecionados. (O nome do intervalo VPL refere-se ao intervalo C6:C25.) Para cada projeto com um 1 na coluna A, esta fórmula seleciona o VPL do projeto, e para cada projeto com um 0 na coluna A, esta fórmula não seleciona o VPL do projeto. Portanto, podemos calcular o VPL de todos os projetos, e nossa célula de destino é linear porque é calculada somando termos que seguem a forma (célula variável)*(constante). De maneira semelhante, calculo o capital usado a cada ano e o trabalho usado a cada ano copiando de E2 para F2:J2 a fórmula SOMARPRODUTO(doit,E6:E25).
Agora preencho a caixa de diálogo Parâmetros do Solver, conforme mostrado na Figura 30-2.
Nosso objetivo é maximizar o VPL dos projetos selecionados (célula B2). Nossas células variáveis (o intervalo chamado doit) são as células binárias variáveis para cada projeto. A restrição E2: J2< = E4: J4 garante que, durante cada ano, o capital e o trabalho usados 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 seleciono Bin na lista no meio da caixa de diálogo. A caixa de diálogo Adicionar restrição deve aparecer conforme mostrado na Figura 30-3.
Nosso modelo é linear porque a célula de destino é calculada como a soma dos termos que têm a forma (célula variável)*(constante) e porque as restrições de uso 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 (US$ 9,293 bilhões) escolhendo os Projetos 2, 3, 6–10, 14–16, 19 e 20.
Lidando com outras restrições
Às 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. Como nossa solução ideal atual seleciona o Projeto 3, mas não o Projeto 4, sabemos que nossa solução atual não pode permanecer ideal. Para resolver esse problema, basta adicionar a restrição de que a célula binária de alteração para o Projeto 3 seja menor ou igual à célula binária de alteração para o Projeto 4.
Você pode encontrar este exemplo na planilha If 3 then 4 no arquivo Capbudget.xlsx, que é mostrada na Figura 30-4. A célula L9 refere-se ao valor binário relacionado ao Projeto 3 e a célula L12 ao valor binário relacionado ao Projeto 4. Ao adicionar a restrição L9<=L12, se escolhermos o Projeto 3, L9 será igual a 1 e nossa restrição forçará L12 (o binário do Projeto 4) a ser igual a 1. Nossa restrição também deve deixar o valor binário na célula variável do Projeto 4 irrestrito 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 ótima é mostrada na Figura 30-4.
Uma nova solução ótima é calculada se selecionar o Projeto 3 significa que também devemos selecionar o Projeto 4. Agora suponha que possamos fazer apenas quatro projetos entre os Projetos 1 a 10. (Veja 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.
Resolvendo problemas de programação binária e inteira
Os modelos do Solver Linear nos quais algumas ou todas as células variáveis devem ser binárias ou inteiras geralmente são mais difíceis de resolver do que os modelos lineares nos quais todas as células variáveis podem ser frações. Por esse motivo, muitas vezes ficamos satisfeitos com uma solução quase ótima para um problema de programação binária ou inteira. Se o seu modelo do Solver for executado por um longo tempo, você pode querer considerar o ajuste da configuração de tolerância na caixa de diálogo Opções do Solver. (Veja a Figura 30-6.) Por exemplo, uma configuração de Tolerância de 0,5% significa que o Solver será interrompido na primeira vez que encontrar uma solução viável que esteja dentro de 0,5% do valor teórico da célula-alvo ideal (o valor teórico ideal da célula-alvo é o valor ideal encontrado quando as restrições binária e inteira são omitidas). Muitas vezes, nos deparamos 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 no computador! O valor de Tolerância padrão é 0,05%, o que significa que o Solver é interrompido quando encontra um valor de célula de destino dentro de 0,05% do valor teórico ideal da célula-mãe.
Problemas
- Uma empresa tem nove projetos em consideração. O VPL adicionado por cada projeto e o capital necessário para cada projeto durante os próximos dois anos são mostrados na tabela a seguir. (Todos os números estão em milhões.) Por exemplo, o Projeto 1 adicionará US$ 14 milhões em VPL e exigirá despesas de US$ 12 milhões durante o Ano 1 e US$ 3 milhões 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.
| NPV | Despesas do 1º ano | Despesas do Ano 2 | |
|---|---|---|---|
| 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?
- Suponha que, se o Projeto 4 for realizado, o Projeto 5 deverá ser realizado. Como podemos maximizar o VPL?
Uma editora está tentando determinar qual dos 36 livros deve publicar este ano. O arquivo Pressdata.xlsx 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 totalizando até 8500 páginas este ano e deve publicar pelo menos quatro livros voltados para desenvolvedores de software. Como a empresa pode maximizar 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.