Marsden Fold Packaging compra 24.000 bandejas moldeadas al mes a cuatro proveedores. El plan de abril estaba en el mismo libro que el de marzo, valorado al céntimo en 109.149 €. Las facturas sumaron 111.906 €.
No falló nada. La celda del coste se recalculó en cuanto entraron los precios de abril, así que el número en pantalla era nuevo. Las cantidades que había debajo tenían cinco semanas.
Solver no deja una fórmula detrás. Escribe constantes en las celdas que le dejas cambiar, y una constante no sabe que la capacidad de Cowden se redujo en dos tercios el 1 de abril.
| Medida | Marzo | Abril |
|---|---|---|
| Bandejas pedidas | 24.000 | 24.000 |
| Capacidad de Cowden | 6.000 | 3.600 |
| Precio de Ashgrove | 4,10 € | 4,38 € |
| Cantidades en las celdas | resueltas | las de marzo |
| Coste según la hoja | 107.385 € | 109.149 € |
| Coste según las facturas | 107.385 € | 111.906 € |
Tres fallos, y ninguno tenía una celda a su nombre. El plan estaba caducado, la capacidad que cambió vivía en un cuadro de diálogo, y la regla de calidad por la que existía el plan no podía cumplirse en abril.
Qué necesitas. Solver viene con Excel y llega desactivado. Archivo → Opciones → Complementos → Administrar: Complementos de Excel → Ir, y marca Solver; en Mac es Herramientas → Complementos de Excel. Aparece al final de la pestaña Datos.
Solver admite 200 celdas cambiantes y 100 restricciones, que es el tamaño de un plan y no de un modelo. Este usa cuatro celdas cambiantes, tres reglas y un objetivo.
El envase es solo el ejemplo. Cuadrantes de turnos, recetas de mezcla, presupuestos de medios, disposiciones de un préstamo: cualquier cosa con un total que cubrir, límites que respetar y un coste que contener.
01El Plan Que la Hoja Seguía Teniendo
Cuatro Proveedores, un Plan Caducado y 2.757 € de Urgencia
La conciliación de abril de Marsden Fold Packaging, en A2:I10. La columna B tiene los precios de abril y la D las capacidades de abril, ambas escritas el día 1. La columna E tiene las cantidades que Solver escribió en la hoja en marzo y que nadie volvió a resolver: 6.300, 7.500, 6.000 y 4.200 bandejas. La columna F es el exceso sobre la capacidad, y vale cero en todas las filas menos en Cowden, cuyo cierre técnico la dejó en 3.600 bandejas. La columna G es lo que se sirvió de verdad. Nada falló en ningún momento: la celda del coste es un SUMAPRODUCTO vivo, así que se recalculó en cuanto llegaron los precios de abril y mostró 109.149 € — el reparto de marzo con los precios de abril, la respuesta a una pregunta que nadie había hecho. Las facturas sumaron 111.906 €.
fxLas celdas con fórmulas están resaltadas en verde
Pasa el mouse sobre las celdas con fórmulas para ver la fórmula y resaltar las celdas referenciadas
La hoja de arriba es la conciliación de abril, no el plan de abril. Una fila por proveedor: lo que pedían las celdas, lo que el proveedor pudo servir y lo que decía la factura.
La columna E es la respuesta de marzo, donde Solver la escribió. La columna D es la capacidad de abril, escrita por el comprador el día 1. Nada en la hoja comparaba las dos.
| Proveedor | Plan en las celdas | Capacidad de abril | Servido |
|---|---|---|---|
| Ashgrove | 6.300 | 9.000 | 6.300 |
| Brantham | 7.500 | 7.500 | 7.500 |
| Cowden | 6.000 | 3.600 | 3.600 |
| Deeping | 4.200 | 5.100 | 6.600 |
A Cowden le faltaron 2.400 bandejas. Deeping cubrió el hueco con tres días de aviso: 1.500 de ellas a una tarifa de urgencia de 6,20 € frente a los 5,05 € de su contrato.
Escenario: Abre el último libro con el que corriste Solver. Pon =MAX(0;E2-D2) junto a cada cantidad y =SUMA(F2:F5) debajo, y lee eso antes de leer el coste. En esta hoja devuelve 2.400, y cualquier cosa distinta de cero significa que el plan es más viejo que los límites.
02Solver Escribe Números, no Fórmulas
Solver y Buscar objetivo trabajan igual: escribiendo en tu hoja. Cambian las celdas que les señalas hasta que el objetivo se porta bien, y dejan ahí los últimos números que probaron.
La respuesta es una foto, no un enlace. Cambia después un precio, una capacidad o la cantidad del pedido y no se vuelve a resolver nada.
El objetivo sí se recalcula, porque es una fórmula, y eso es justo lo que hace creíble a un plan caducado. La celda del coste de abril decía 109.149 €: las cantidades de marzo con los precios de abril.
Una celda lo detecta. Mantén el objetivo vivo en F14 y pega su valor en F16 en cuanto aceptes una solución:
=SI(REDONDEAR(F14-F16;2)<>0;"RESOLVER DE NUEVO";"resuelto con estas entradas")
F16 es el coste que Solver firmó. F14 es lo que cuestan hoy esas mismas cantidades. En cuanto se mueve una entrada, los dos se separan y la celda lo dice con una palabra.
Escenario: Añade ese par a tu modelo ahora. Pega el valor del objetivo en la celda de al lado, cambia un precio en un céntimo y mira cómo gira el aviso antes de que termines de escribir.
Pruébalo en la cuadrícula03Una Restricción Vive en un Diálogo, no en la Hoja
Las restricciones de Solver se guardan con la hoja, pero no están en ella. Viven en el cuadro Parámetros de Solver, invisibles hasta que alguien lo abre.
Y aguantan tan contentas una cifra que ha caducado. La regla de marzo decía $K$4 <= 20. Veinte palés de Cowden, escritos como número, en marzo, por un comprador que ya cambió de equipo.
La capacidad de abril fue a la columna D, donde la hoja podía verla. El diálogo no se enteró. Escribe una referencia en Agregar restricción, nunca un número, y la regla leerá la celda que el comprador actualiza.
Una referencia además sobrevive a un cambio de diseño y aparece en Rastrear precedentes. Un número escrito a mano no hace ninguna de las dos cosas.
Escenario: Abre Solver en tu modelo y lee la lista de restricciones en voz alta. Sustituye cada número suelto por la celda que lo contiene, resuelve otra vez y comprueba que la respuesta sigue siendo la misma.
Pruébalo en la cuadrícula04Escribe Cada Regla Donde Puedas Verla
Una regla que nadie ve es una regla que nadie comprueba. Dale a cada una tres celdas en la hoja: lo que usa el plan, cuál es el límite y la holgura entre ambos.
| Regla | Usado | Límite | Holgura |
|---|---|---|---|
| Capacidad de Cowden | 6.000 | 3.600 | −2.400 |
| Total del pedido | 24.000 | 24.000 | 0 |
| Defectos medios | 1,19 % | 1,20 % | 0,01 % |
Luego apunta Solver a esas mismas celdas. La hoja y el diálogo dicen ya lo mismo, y la columna de holgura te enseña qué regla está haciendo el trabajo.
Una regla con holgura de sobra es una regla que podrías haber ignorado. La regla que aprieta es la que merece una discusión, porque es la que cuesta dinero: aquí, el límite del 1,20 %.
Escenario: Monta las tres columnas junto a tu modelo, con =D2-E2 para la holgura, y ordénalas de menor a mayor con =ORDENAR(H2:H5). Lo que quede arriba es la restricción que hay que llevarle a quien la fijó.
05La Celda Objetivo Tiene Que Ser una Fórmula
Solver lee una celda y cambia otras. Si la celda que lee no depende de las celdas que cambia, informará de una solución sin haber movido absolutamente nada.
El objetivo aquí es una celda, viva, y no más lista de lo que hace falta:
=SUMAPRODUCTO(B2:B5;E2:E5)
Precios por cantidades. El modelo que hay debajo ocupa seis celdas:
| Celdas | Contienen |
|---|---|
| K2:K5 | palés: las celdas cambiantes, números enteros |
| E2:E5 | bandejas, =K2*300 |
| D2:D5 | capacidad en bandejas, escrita cada mes |
| F14 | el objetivo, =SUMAPRODUCTO(B2:B5;E2:E5) |
| F15 | defectos medios, =SUMAPRODUCTO(C2:C5;E2:E5)/SUMA(E2:E5) |
| F16 | el coste que Solver firmó, pegado como valor |
Minimizar F14, cambiando K2:K5, con E2:E5 ≤ D2:D5, SUMA(E2:E5) = 24.000 y F15 ≤ 1,20 %. Ese es el modelo entero.
Una cosa más antes de resolver: Convertir variables sin restricciones en no negativas. Viene marcada en el Excel actual, y una casilla sin marcar es de donde sale un proveedor con una cantidad negativa.
Escenario: Busca la celda objetivo de tu modelo y pulsa F2. Si es un número en vez de una fórmula, o una fórmula que nunca llega a las celdas cambiantes, ahí tienes el fallo entero: reconstrúyela como un SUMAPRODUCTO sobre las dos columnas.
06Simplex LP, GRG Nolineal y Evolutionary
Solver trae tres motores y viene con el que peor le sienta a una hoja de esta forma. La caja del método está debajo de las restricciones y nadie la lee dos veces.
| Método | Úsalo cuando | Qué te devuelve |
|---|---|---|
| Simplex LP | toda fórmula es una suma o un múltiplo de las celdas cambiantes | el óptimo demostrado |
| GRG Nolineal | hay curvas suaves: cuadrados, tasas, crecimiento compuesto | una respuesta local que depende de dónde empezó |
| Evolutionary | hay SI, REDONDEAR, MIN, MAX o BUSCARV entre las celdas y el objetivo | una respuesta decente, despacio y sin demostrar nada |
Este modelo es lineal. Un coste es un precio por una cantidad y una capacidad es una suma, así que Simplex LP lo resuelve en un segundo y te dice que no hay nada mejor.
GRG también llega, casi siempre. Y también llega a otro sitio cuando cambian los valores de partida, que es como dos personas con el mismo libro acaban con dos planes y sin forma de saber cuál vale.
Un modelo lleno de SI y REDONDEAR no tiene pendiente que seguir. Las búsquedas, los tramos y el redondeo hacen escalones, y un método de gradiente se atasca en un escalón. Es la misma escalera que derrota a Buscar objetivo.
07El Palé Que Alguien Redondeó a Mano
El óptimo de marzo era 21,25 palés de Ashgrove. Las bandejas vienen de 300 en palé, nadie sirve un cuarto de palé, y un comprador lo subió a 22 y le quitó uno a Deeping para cuadrar el total.
La hoja salió 285 € más barata. También rompió la única regla por la que existía el plan.
| Plan | Ashgrove | Deeping | Coste | Defectos medios |
|---|---|---|---|---|
| Solver, con decimales | 21,25 | 13,75 | 107.313,75 € | 1,20 % |
| Redondeado a mano | 22 | 13 | 107.100,00 € | 1,22 % |
| Solver, palés enteros | 21 | 14 | 107.385,00 € | 1,19 % |
El redondeo movió un palé del proveedor más limpio al más sucio, que es la dirección en la que siempre lo mueve redondear hacia la columna barata.
Redondear es una decisión, así que deja que la tome el modelo. Pon los palés en las celdas cambiantes, deriva las bandejas con =K2*300 y añade una restricción: $K$2:$K$5 = int.
REDONDEAR y MULTIPLO.SUPERIOR siguen teniendo trabajo aquí — =MULTIPLO.SUPERIOR(6375/300;1) te dice qué saldría de una respuesta con decimales — pero describen una respuesta en lugar de elegirla.
Escenario: Coge un modelo cuya respuesta redondeaste a mano, añade la restricción de enteros a las celdas cambiantes y resuelve otra vez. Compara los dos costes y pasa tu versión redondeada por la regla que debía respetar.
Pruébalo en la cuadrícula08La Optimalidad de Enteros no Es Cero Hasta Que la Pones a Cero
Añade int y Solver cambia en silencio su definición de terminado. Para cuando la mejor respuesta entera que ha encontrado está dentro de un porcentaje de lo mejor a lo que todavía podría aspirar.
Ese porcentaje no es cero. Opciones → Optimalidad de enteros (%): el Excel actual la trae al 1 y las versiones antiguas al 5, y la casilla está a tres clics de donde mira cualquiera.
En un plan de 107.385 €, un 1 % es permiso para dejarse unos mil euros por el camino. No lo verás pasar, porque Solver sigue diciendo que encontró una solución.
Ponla a 0 en un modelo de este tamaño y la resolución sigue tardando un segundo. Súbela a propósito, o no la subas — en un modelo con cientos de celdas enteras, esa holgura es la diferencia entre una respuesta y una tarde perdida.
09«Solver no Pudo Encontrar una Solución Factible» Es una Respuesta
Pasa por Solver los números reales de abril y no te devuelve un plan. Te devuelve una frase, y esa frase es lo más útil de esta página.
Los cuatro contratos de abril sumaban 25.200 bandejas frente a un pedido de 24.000, así que había que dejar 1.200 bandejas de capacidad sin usar. Con Cowden en 12 palés, el proveedor más limpio de la lista estaba racionado.
Quita los cuatro peores palés disponibles — los de Ashgrove, al 2,4 % — y la tasa media de defectos se queda igualmente en el 1,30 %. El límite era el 1,20 %.
No había ningún plan que cumpliera las reglas de abril, y ninguna fórmula de la hoja iba a decirlo.
Cuando Solver se niega, no empieces a borrar restricciones. Copia el modelo, convierte la regla que aprieta en el objetivo y pregunta por cuánto se pasa:
=SUMAPRODUCTO(C2:C5;E2:E5)/SUMA(E2:E5)
Minimiza eso, mantén las capacidades y el total del pedido, y Solver devuelve un 1,30 % con un coste de 109.176 €. Son 2.730 € menos de lo que costó abril, y estaban disponibles el día 1.
Lo infactible tiene fecha. Saber el día 1 que el límite no se puede cumplir es una llamada al cliente. Saberlo el día 28 es un recargo de urgencia.
Escenario: Haz imposible una de tus restricciones a propósito — parte una capacidad por la mitad — y ejecuta Solver. Lee el mensaje y vuelve a ejecutarlo con esa restricción como objetivo: apunta el número que no puede batir.
Pruébalo en la cuadrícula10Conservar Solución de Solver, y la Base Que Sobrescribe
El cuadro de resultados ofrece Conservar solución de Solver o Restaurar valores originales, y viene marcado Conservar. Aceptas y la respuesta pasa a ser tu columna de entradas.
Está bien cuando era lo que querías. No lo está cuando solo probabas un escenario, porque el plan que tenías antes ya no está y la hoja parece tan deliberada como hace un minuto.
Ese mismo cuadro tiene un botón Guardar escenario. Escribe las celdas cambiantes actuales en el Administrador de escenarios con un nombre, y es el seguro más barato del complemento.
Ctrl+Z deshace una resolución, pero solo en ese momento. Guarda, cierra, vuelve a abrir, y los números que tenías antes no vuelven sin el escenario que no guardaste.
11Cinco Controles Encima de la Tabla
Cinco celdas, en la fila de encima del modelo, que se leen antes de que nadie lea el coste:
- ¿Sigue el plan siendo la respuesta?
=SI(REDONDEAR(F14-F16;2)<>0;"RESOLVER DE NUEVO";"resuelto con estas entradas"), el objetivo vivo contra el que firmó Solver. - ¿Algo se pasa de su límite?
=SUMA(F2:F5)sobre=MAX(0;E2-D2)tiene que dar 0. En abril daba 2.400. - ¿El pedido sigue cuadrando?
=SUMA(E2:E5)-F7, con F7 la cantidad pedida. Cero, o el plan es de otro mes. - ¿Se cumple la regla de calidad?
=SUMAPRODUCTO(C2:C5;E2:E5)/SUMA(E2:E5)contra el límite. El plan redondeado a mano da 1,22 % frente a 1,20 %. - ¿Son palés enteros?
=SUMAPRODUCTO(--(RESIDUO(E2:E5;300)<>0))cuenta las filas que no son múltiplo de 300, y=CONTAR.SI(E2:E5;"<0")caza la cantidad negativa que permite una casilla sin marcar.
12Cuando el Modelo no Necesita Solver
Si la capacidad es la única regla, lo más barato primero es el óptimo y el complemento sobra. Ordena los proveedores por precio y arrastra una fórmula:
=MIN(D2;$F$7-SUMA($E$1:E1))
Cada fila coge lo que puede hasta su capacidad, o lo que quede del pedido, lo que sea menor. =ORDENAR(A2:D5;2) los pone antes en orden de precio.
Ese es el plan que devuelve Solver para un modelo de una sola regla, y con los números de marzo cuesta 104.820 €: más barato que los 107.385 € de Solver, y con un 1,42 % de defectos frente a un límite del 1,20 %.
Solver se gana el sueldo en cuanto dos reglas tiran en sentidos contrarios. Una regla es un arrastre; dos son un compromiso, y ahí es donde se gastan a propósito los 2.565 € que separan esos dos planes.
13Diez Trampas
- Solver escribe valores. Nada se vuelve a resolver cuando cambia una entrada.
- Las restricciones aguantan números escritos a mano. Usa referencias, siempre.
- Un objetivo que es una constante deja que Solver cante éxito sin mover nada.
- GRG viene por defecto. En un modelo lineal, Simplex LP demuestra lo que GRG solo encuentra.
- SI y BUSCARV hacen escalones. Los métodos de gradiente se atascan en ellos.
- La Optimalidad de enteros no es 0. Es 1 % en el Excel actual y 5 % en los antiguos.
- Redondear a mano rompe restricciones. El modelo debe llevar
int. - Lo no negativo es una casilla. Desmárcala y las cantidades pueden bajar de cero.
- Conservar solución sobrescribe tus entradas. Guarda el escenario antes.
- Lo infactible es información. Es el único mensaje que te dice la verdad sobre el mes.
14Ejercicios
- Cadúcalo a propósito. Resuelve el modelo, sube un precio un 10 % y mira cómo se mueve el objetivo mientras las cantidades no.
- Endurece las reglas. Sustituye cada número escrito en la lista de restricciones por una referencia, cambia una capacidad en la hoja y confirma que la siguiente resolución la respeta.
- Redondéalo mal. Coge la respuesta con decimales, redondéala a mano y pásale el control 4 contra el límite de calidad.
- Cambia de motor. Resuelve el mismo modelo con Simplex LP y con GRG partiendo de cero, y compara las dos respuestas y los dos tiempos.
- Rómpelo del todo. Baja una capacidad hasta que Solver se niegue, resuelve con la regla de calidad como objetivo y apunta lo que abril podía conseguir de verdad.
Qué Llevarte
En este libro no había nada roto. La celda del coste era un SUMAPRODUCTO correcto, la columna de capacidad tenía los números reales de abril, y Solver había hecho exactamente lo que se le pidió en marzo.
Lo que nadie había escrito es que una respuesta de Solver es una foto del modelo en un momento: estos precios, estos límites, este pedido. La hoja se queda la foto y sigue recalculando a su alrededor.
Tres celdas lo habrían dicho. El objetivo vivo contra el firmado, la suma de los excesos de capacidad y la tasa media de defectos contra su límite.
Tres celdas, frente a 2.757 € de transporte urgente y un límite de calidad que en abril no se podía cumplir escribiera quien escribiera lo que escribiera.