Introducción al complemento Solver de Excel
Hoy quisiera mostraros una herramienta de Excel, poco conocida, pero a la que se le puede sacar mucho partido con un poco de paciencia y mucha lógica, se trata del complemento Solver de Excel.
Es un instrumento que nos permite hallar la solución a un problema con varias variables de una forma sencilla y eficaz, por ejemplo, cuál sería el tamaño del pedido idóneo de un determinado producto para minimizar su coste cuando existen una serie de restricciones o condicionantes.
Instalación
Lo primero que hemos de saber es que el complemento Solver de Excel no aparece por defecto en la barra de herramientas de Excel, sino que hay que instalarlo, pero es muy sencillo. Los pasos son los siguientes:
Archivo
Opciones
Solver opcionesComplementos
Complementos de Excel
Ir
Clicar en el box de Solver
Así de fácil se instalará en la pestaña datos de vuestra hoja de cálculo en la pestaña datos. Yo tengo instalados más complementos, para este artículo solo necesitamos Solver
Utilidad con un ejemplo de uso del complemento Solver de Excel
Ya tenemos instalado el complemento Solver de Excel en Datos, ahora vamos a ver su utilidad con un ejemplo:
Enunciado de un ejemplo de uso del complemento Solver de Excel
Vamos a calcular el número de pedidos óptimo para un determinado ingrediente con los siguientes datos:
Planteamiento I
Este enunciado hay que transformarlo en datos de la hoja de cálculo:
En la columna D podéis ver las fórmulas que hemos utilizado.
Es importante que la casilla C13, donde he escrito 1000,00 unidades, no tenga fórmulas, puesto que será el complemento Solver de Excel el que se encargue de hallar esta cantidad, para ello tenemos que indicar los valores de la ecuación:
- Número de pedidos: resultado de dividir el pedido máximo entre el tamaño del pedido minimizando el coste total.
- Coste del pedido: número de pedidos por el coste de un pedido.
- Coste de almacenamiento: 0,05 € por existencias iniciales menos finales, entre paréntesis, entre dos, en este caso, las existencias iniciales es la compra y las finales cero hasta que renovamos el pedido, dividimos el pedido, por lo tanto, el pedido entre dos, porque sería lógico que a medida que pasen los días las existencias vayan disminuyendo.
- Coste administrativo: es el resultado de multiplicar el pedido máximo por 0,05 entre 2. Aunque veáis que en la casilla C11 el resultado es 0,01 €, en realidad es 0,005 € redondeado.
- Coste total: es la suma de los tres costes anteriores.
Resolver el problema I
Ahora sí, vamos con el complemento Solver de Excel
Los parámetros son:
Establecer objetivo: casilla C18, coste total.
Para: Mín, es decir, coste mínimo de la suma de los costes.
Cambiando las celdas variables: C13, que es el tamaño del pedido.
Sujeto a las restricciones: C13<=C8, es decir, que el pedido tiene que ser menor o igual que el pedido máximo.
No hemos tocado nada más.
Clicamos en resolver y tenemos el primer resultado, que no es el esperado:
Esto es así porque se trata de un problema de Solver no suavizado. Vaya… He entrado en la página de Microsoft y no aclara demasiado esta cuestión, tampoco he indagado mucho más. Pues utilicemos Evolutionary para este tipo de problemas. Clicamos en el modo de resolución y buscamos Evolutionary y resolvemos, la aplicación nos devuelve un resultado y un cuadro de diálogo:
Nosotros le hemos dado a aceptar y ya tenemos el resultado anterior.
Pero hay un problema, no puede haber 3,54 pedidos son 4 pedidos, aunque uno sea menor, por lo tanto, el coste total de 19,14 € no es correcto.
Planteamiento II
Esto nos obliga a cambiar la fórmula del número de pedidos, nos hemos decantado por la siguiente fórmula en la casilla C14: =REDONDEAR.MAS(C8/C13;0) que ha sustituido a: =C8/C13. Lo que hemos conseguido es que el resultado de la división lo redondee al alza sin decimales, eso indica el cero, de esta manera 3,54 pedidos se convertiría en 4.
Resolver el problema II
Vamos a probar:
El complemento Solver para Excel ha estado haciendo bastantes iteraciones, más de 15.000 y nos ha ofrecido el siguiente resultado, aceptamos:
Vemos como ahora el pedido es de 250 unidades y que son 4 pedidos, eso hace que el coste total sea de 19,25 €, resultado algo superior al anterior, 19,14 €.
Vídeo
Os dejo un vídeo con todo el proceso.
Conclusiones
Como habréis deducido, el complemento Solver para Excel requiere un trato inteligente y una labor preliminar de análisis del problema para hallar las variables correctas y las formulaciones adecuadas para una solución satisfactoria. En este caso hemos planteado un ejercicio con un solo objetivo, una variable y tres restricciones, como podréis imaginar esto se podría complicar muchísimo.
Espero que os haya resultado interesante.
