Formulación de raciones con el Solver de Excel

Si bien existen muchos programas de formulación de raciones, la mayoría de los estudiantes y profesionales tienen acceso a hojas de cálculo de Microsoft Office, por lo que a continuación se dan las principales instrucciones para usar el procedimiento Solver para resolver problemas de programación lineal de raciones a mínimo costo descritas por Rosero et al. (2011).

Primero se debe de plantear el problema en forma de ecuación, para esto hay que asignarle una identificación a cada ingrediente que se emplee en la dieta.

Se define la Función objetivo o Z, la cual es la información de los costos ($) de los ingredientes para minimizar: Z=($) X1+($) X2+……… +($) Xn.

Se establecen las restricciones del problema, basado en los requerimientos de la especie a la que se pretenda elaborara la ración. (PC, EM, ED, etc.), y en las restricciones de niveles de los alimentos.

Se plantea el modelo en forma de ecuaciones, empleando los datos de los costos, nutrientes y restricciones de cada ingrediente.

Se elabora la matriz de las ecuaciones, para facilitar el procedimiento y revisar que existan ingredientes que aporten niveles mayores y menores al requerimiento para que se pueda encontrar una solución factible. Esta información se puede capturar en Microsoft Excel para resolverse con el Solver. Para usar el Solver, debe instalarse en el botón de inicio, complementos.

La información del cuadro 32.1 corresponde a los alimentos disponibles para una ración de engorda de ovinos y el cuadro 32.2 presenta los requerimientos que se desean formular para la fase de finalización (NRC, 1985) con algunos límites de alimentos sugeridos.

Cuadro 32.1 Ingredientes para la formulación de raciones de ovinos en finalización

Las concentraciones sugeridas en el cuadro 32.2 pueden ser modificadas de acuerdo con las características de edad, razas, sexo (castrados, enteros, hembras) y etapas del proceso. Algunos productores hacen la engorda en dos etapas; para corderos recién destetados incrementan el porcentaje de proteína (13 a 15 %) y una segunda etapa de finalización (> 30 kg peso vivo) hasta la venta, se reduce 2 y 14 % de proteína). La EM se puede incrementar de 2.7 a 3.0 Mcal/kg de materia seca con las precauciones de incrementar gradualmente los niveles de grano para evitar acidosis subclínica.

Cuadro 32.2 Concentración de nutrientes recomendadas para ovinos

El siguiente paso, en este caso, es la información de las concentraciones de nutrientes recomendadas para los ovinos (cuadro 32.3 en Excel), en los casos que no se cuente con esta información es necesario consultar los requerimientos (PC, EM, ED, Ca, P, etc.) de los animales para los que se desee balancear en las tablas de requerimientos (NRC, INRA, etc.). Esto para generar las restricciones del problema. Posteriormente se debe elaborar la matriz con los datos de las ecuaciones ya realizadas, colocando la función objetivo, además de dividir las restricciones, en los ingredientes (sorgo, pasta de soya, etc.) se debe de realizar una división (% del ingrediente/100), por otra parte, al final de la pantalla, se muestra una fila "% de inclusión a la ración" en la cual se debe de colocar ceros (0), tal como se muestra en el cuadro 32.3.

Es importante para facilitar el uso del Solver, que primero se pongan juntas las restricciones por signo. Por ejemplo, primero las que tienen igualdad (=), después las de < y al final las de > (cuadro 32.3).

Realizar las restricciones por separado de los nutrientes e ingredientes, se usa función de Excel “sumaproducto”. Cada suma producto (ecuación de restricción) se tiene que referir la ecuación de % de inclusión en la ración (que será la solución final), esto se hace ecuación por ecuación. Por ejemplo, si la línea de % de inclusión de la ración se encuentra en B29:H29 y la ecuación de “proteína máximo” en B18:H18, la suma producto que se pone en la casilla (cuadro 32.4) sería =sumaproducto (B29:H29, B18:H18). Si la restricción de “EM mínimo” se encuentra en B7:H7, la casilla correspondiente sería: sumaproducto (B29:H29, B7:H7).

Cuadro 32.3 Información para incluir en el Excel para el Solver

Suponiendo que la línea de costo está en B5:H5, se procede a realizar una celda la cual nombrará “Total $/kg”, y se hace la ecuación “sumaproducto” es decir=sumaproductos (% de inclusión a la ración, función objetivo) o =sumaproducto (B29:H29, B5:H5), es aquí donde aparecerá el resultado de costo de la ración después de solucionar el problema.

Cuadro 32.4 Elaboración de cuadro de sumaproducto en Excel

Con todas las “sumaproducto” de las restricciones y condiciones del problema establecidos, se selecciona la celda Total ($/kg) y se presiona la función Solver, y le aparecerá la pantalla de “Parámetros de Solver” en el primer recuadro “Celda objetivo" se debe confirmar que este la celda de Total ($/kg). Se continúa modificando y llenando la pantalla de "Parámetros de Solver” y en la siguiente celda “Valor de la celda objetivo”, se debe de seleccionar Mínimo para que el Solver trabaje a minimizar. En la celda “Cambios de celdas” se elige la matriz de % de inclusión a la ración.

En la celda de “Restricciones” se agregan en la celda de “Referencia de la celda” se selecciona la matriz que se realizó con las sumaproducto y en la celda “Restricciones” se selecciona la matriz de restricciones y se selecciona el signo dependiendo del caso (=, > o < ). En el caso de la igualdad únicamente hay que elegir el signo (=) y seleccionar las condiciones de igualdad (cuadro 32.5).

Cuadro 32.5 Información que requiere la función Solver

Posteriormente, debe de seleccionar “Opciones” (cuadro 32.5) y aparecerá la pantalla en la cual se activarán las celdas “Adoptar Modelo Lineal” y “Adoptar no Negativos” donde se debe de seleccionar aceptar, y lo regresara a la pantalla de “Parámetros de Solver”. Finalmente debe de seleccionar la celda “Resolver”, para que le aparezca la pantalla de resultados. 

La pantalla de “Resultados de Solver” debe de aparecer la leyenda “Solver ha hallado una solución. Se han satisfecho todas la restricciones y condiciones”. Además, podrá observar que en donde se realizaron las sumaproductos ya aparecen valores, estos (máximos, mínimos y fijos) son los que contendrá a ración y en la celda Total ($/kg) aparecerá el costo y por otra parte en la celda % de inclusión a la ración aparecerán los porcentajes de ingredientes que se deben de incluir para una ración con las condiciones y restricciones del problema.

En el cuadro 32.6, se pueden ver en la fila inferior los porcentajes de inclusión de la ración de cada ingrediente y en el cuadro 32.7 se puede ver el costo por kg de ración y se pueden deducir los niveles de nutrientes aportados por la ración.

Una vez que se tiene el modelo en Solver se pueden modificar costos, contenidos de nutrientes, niveles en la ración, etcétera y volver a resolver la matriz. Existen algunos programas de formulación que tienen integrado el Solver y que pueden ser más sencillos de usar a elección del usuario.

Cuadro 32.6 Solución del problema con Excel de Solver

Cuadro 32.7 Costo y contenido de nutrientes de la solución óptima