¿Cómo puede una empresa utilizar Solver para determinar qué proyectos debe emprender?
Cada año, una empresa como Eli Lilly necesita determinar qué medicamentos desarrollar; una empresa como Microsoft, qué programas de software desarrollar; una empresa como Proctor & Gamble, qué nuevos productos de consumo desarrollar. La característica Solver de Excel puede ayudar a una empresa a tomar estas decisiones.
¿Cómo puede una empresa utilizar Solver para determinar qué proyectos debe emprender?
La mayoría de las corporaciones quieren emprender proyectos que contribuyan con el mayor valor actual neto (VAN), sujeto a recursos limitados (generalmente capital y mano de obra). Digamos que una empresa de desarrollo de software está tratando de determinar cuál de los 20 proyectos de software debe emprender. El VAN (en millones de dólares) aportado por cada proyecto, así como el capital (en millones de dólares) y el número de programadores necesarios durante cada uno de los próximos tres años se dan en la hoja de trabajo del Modelo Básico en el archivo Capbudget.xlsx, que se muestra en la Figura 30-1 en la página siguiente. Por ejemplo, Project 2 produce 908 millones de dólares. Requiere $151 millones durante el Año 1, $269 millones durante el Año 2 y $248 millones durante el Año 3. Project 2 requiere 139 programadores durante el primer año, 86 programadores durante el año 2 y 83 programadores durante el año 3. Las celdas E4:G4 muestran el capital (en millones de dólares) disponible durante cada uno de los tres años, y las celdas H4:J4 indican cuántos programadores están disponibles. Por ejemplo, durante el año 1 hay hasta $ 2.5 mil millones en capital y 900 programadores disponibles.
La empresa debe decidir si debe acometer cada proyecto. Supongamos que no podemos emprender una fracción de un proyecto de software; Si asignamos 0.5 de los recursos necesarios, por ejemplo, ¡tendríamos un programa que no funciona y que nos generaría ingresos de $ 0!
El truco para modelar situaciones en las que haces o no haces algo es usar celdas cambiantes binarias. Una celda cambiante binaria siempre es igual a 0 o 1. Cuando una celda binaria cambiante que corresponde a un proyecto es igual a 1, hacemos el proyecto. Si una celda binaria cambiante que corresponde a un proyecto es igual a 0, no hacemos el proyecto. Configure Solver para usar un rango de celdas binarias cambiantes agregando una restricción: seleccione las celdas cambiantes que desea usar y, a continuación, elija Bin en la lista del cuadro de diálogo Agregar restricción.
Con estos antecedentes, estamos listos para resolver el problema de selección de proyectos de software. Como siempre con un modelo Solver, comenzamos identificando nuestra célula objetivo, las células cambiantes y las restricciones.
- Celda de destino. Maximizamos el VAN generado por los proyectos seleccionados.
- Células cambiantes. Buscamos una celda cambiante binaria 0 o 1 para cada proyecto. He localizado estas celdas en el rango A6:A25 (y he denominado al rango doit). Por ejemplo, un 1 en la celda A6 indica que llevamos a cabo el Proyecto 1; un 0 en la celda C6 indica que no llevamos a cabo el Proyecto 1.
- Restricciones. Necesitamos asegurarnos de que para cada año t (t = 1, 2, 3), el capital utilizado del año t sea menor o igual al capital disponible del año t , y la mano de obra del año t utilizada sea menor o igual a la mano de obra del año t disponible.
Como puede ver, nuestra hoja de trabajo debe calcular para cualquier selección de proyectos el VAN, el capital utilizado anualmente y los programadores utilizados cada año. En la celda B2, utilizo la fórmula SUMAPRODUCTO(doit,VAN) para calcular el VAN total generado por los proyectos seleccionados. (El nombre del rango VAN hace referencia al rango C6:C25.) Para cada proyecto con un 1 en la columna A, esta fórmula recoge el VAN del proyecto, y para cada proyecto con un 0 en la columna A, esta fórmula no recoge el VAN del proyecto. Por lo tanto, podemos calcular el VAN de todos los proyectos, y nuestra celda objetivo es lineal porque se calcula sumando términos que siguen la forma (celda cambiante) * (constante). De manera similar, calculo el capital utilizado cada año y la mano de obra utilizada cada año copiando de E2 a F2:J2 la fórmula SUMAPRODUCTO(doit,E6:E25).
Ahora completo el cuadro de diálogo Parámetros de Solver como se muestra en la Figura 30-2.
Nuestro objetivo es maximizar el VAN de los proyectos seleccionados (celda B2). Nuestras celdas cambiantes (el rango llamado doit) son las celdas cambiantes binarias para cada proyecto. La restricción E2:J2<=E4:J4 asegura que durante cada año el capital y la mano de obra utilizados sean menores o iguales que el capital y la mano de obra disponibles. Para agregar la restricción que hace que las celdas cambiantes sean binarias, hago clic en Agregar en el cuadro de diálogo Parámetros de Solver y, a continuación, selecciono Bin en la lista en el medio del cuadro de diálogo. El cuadro de diálogo Agregar restricción debe aparecer como se muestra en la Figura 30-3.
Nuestro modelo es lineal porque la celda objetivo se calcula como la suma de términos que tienen la forma (celda cambiante)*(constante) y porque las restricciones de uso de recursos se calculan comparando la suma de (celdas cambiantes)*(constantes) con una constante.
Con el cuadro de diálogo Parámetros de Solver completado, haga clic en Resolver y tenemos los resultados que se muestran anteriormente en la Figura 30-1. La empresa puede obtener un VAN máximo de 9.293 millones de dólares (9.293 millones de dólares) eligiendo los Proyectos 2, 3, 6-10, 14-16, 19 y 20.
Control de otras restricciones
A veces, los modelos de selección de proyectos tienen otras restricciones. Por ejemplo, supongamos que si seleccionamos el proyecto 3, también debemos seleccionar el proyecto 4. Dado que nuestra solución óptima actual selecciona el Proyecto 3 pero no el Proyecto 4, sabemos que nuestra solución actual no puede seguir siendo óptima. Para resolver este problema, simplemente agregue la restricción de que la celda cambiante binaria para Project 3 sea menor o igual que la celda cambiante binaria para Project 4.
Puede encontrar este ejemplo en la hoja de trabajo If 3 then 4 en el archivo Capbudget.xlsx, que se muestra en la Figura 30-4. La celda L9 hace referencia al valor binario relacionado con el Proyecto 3 y la celda L12 al valor binario relacionado con el Proyecto 4. Al agregar la restricción L9<=L12, si elegimos el Proyecto 3, L9 es igual a 1 y nuestra restricción fuerza a L12 (el binario del Proyecto 4) a ser igual a 1. Nuestra restricción también debe dejar el valor binario en la celda cambiante del Proyecto 4 sin restricciones si no seleccionamos el Proyecto 3. Si no seleccionamos el Proyecto 3, L9 es igual a 0 y nuestra restricción permite que el binario del Proyecto 4 sea igual a 0 o 1, que es lo que queremos. La nueva solución óptima se muestra en la Figura 30-4.
Se calcula una nueva solución óptima si seleccionar el Proyecto 3 significa que también debemos seleccionar el Proyecto 4. Ahora supongamos que podemos hacer solo cuatro proyectos de entre los Proyectos 1 a 10. (Consulte la hoja de trabajo At Most 4 Of P1–P10 , que se muestra en la Figura 30-5). En la celda L8, se calcula la suma de los valores binarios asociados con los proyectos 1 a 10 con la fórmula SUMA(A6:A15). Luego agregamos la restricción L8<=L10, que asegura que, como máximo, se seleccionen 4 de los primeros 10 proyectos. La nueva solución óptima se muestra en la Figura 30-5. El VAN ha caído a 9.014 millones de dólares.
Resolución de problemas de programación binaria y entera
Los modelos de Solver lineal en los que se requiere que algunas o todas las celdas cambiantes sean binarias o enteras suelen ser más difíciles de resolver que los modelos lineales en los que se permite que todas las celdas cambiantes sean fracciones. Por esta razón, a menudo estamos satisfechos con una solución casi óptima para un problema de programación binaria o entera. Si el modelo de Solver se ejecuta durante mucho tiempo, puede considerar la posibilidad de ajustar la configuración de Tolerancia en el cuadro de diálogo Opciones de Solver. (Consulte la Figura 30-6). Por ejemplo, un valor de tolerancia del 0,5 % significa que Solver se detendrá la primera vez que encuentre una solución factible que se encuentre dentro del 0,5 % del valor teórico óptimo de la celda diana (el valor teórico óptimo de la celda diana es el valor óptimo que se encuentra cuando se omiten las restricciones binarias y enteras). A menudo nos enfrentamos a una elección entre encontrar una respuesta dentro del 10 por ciento de lo óptimo en 10 minutos o encontrar una solución óptima en dos semanas de tiempo de computadora. El valor de tolerancia predeterminado es 0,05 %, lo que significa que Solver se detiene cuando encuentra un valor de celda de destino dentro del 0,05 por ciento del valor de celda de destino óptimo teórico.
Problemas
- Una empresa tiene nueve proyectos en consideración. El VAN agregado por cada proyecto y el capital requerido por cada proyecto durante los próximos dos años se muestran en la siguiente tabla. (Todos los números están en millones). Por ejemplo, el Proyecto 1 agregará $14 millones en VAN y requerirá gastos de $12 millones durante el Año 1 y $3 millones durante el Año 2. Durante el año 1, $50 millones en capital están disponibles para proyectos y $20 millones están disponibles durante el año 2.
| VNA | Gastos del año 1 | Gastos del año 2 | |
|---|---|---|---|
| Proyecto 1 | 14 | 1,2 | 3 |
| Proyecto 2 | 17 | 54 | 7 |
| Proyecto 3 | 17 | 6 | 6 |
| Project 4 | 15 | 6 | 2 |
| Proyecto 5 | 40 | 30 | 35 |
| Project 6 | 1,2 | 6 | 6 |
| Project 7 | 14 | 48 | 4 |
| Project 8 | 10 | 36 | 3 |
| Project 9 | 1,2 | 18 | 3 |
- Si no podemos emprender una fracción de un proyecto, sino que debemos emprender todo o nada de un proyecto, ¿cómo podemos maximizar el VAN?
- Supongamos que si se lleva a cabo el Proyecto 4, el Proyecto 5 debe llevarse a cabo. ¿Cómo podemos maximizar el VAN?
Una editorial está tratando de determinar cuál de los 36 libros debe publicar este año. La Pressdata.xlsx de archivos proporciona la siguiente información sobre cada libro:
- Ingresos proyectados y costos de desarrollo (en miles de dólares)
- Páginas de cada libro
- Si el libro está dirigido a una audiencia de desarrolladores de software (indicado por un 1 en la columna E)
Una editorial puede publicar libros con un total de hasta 8500 páginas este año y debe publicar al menos cuatro libros dirigidos a desarrolladores de software. ¿Cómo puede la empresa maximizar sus ganancias?
Sobre el artículo
Este artículo fue adaptado de Microsoft Office Excel 2007 Data Analysis and Business Modeling por Wayne L. Winston.
Este libro estilo aula fue desarrollado a partir de una serie de presentaciones de Wayne Winston, un conocido estadístico y profesor de negocios que se especializa en aplicaciones creativas y prácticas de Excel.