¿Cómo calcular una dieta óptima con Excel?

¡Hola! ¿Qué tal están?

En esta entrada vamos a ver cómo podemos utilizar Microsoft Excel, u otro software de hoja de cálculo en donde esté implementada una herramienta como Solver, para resolver un problema propio de la disciplina conocida como Programación Lineal; la disciplina en cuestión, por si alguno no lo conoce, se centra en la optimización, y para ello utiliza un modelo matemático de la realidad basado en funciones lineales, motivo por el cual fue nombrada de dicha manera.

En cuanto al tema a tratar en específico, consiste en la resolución de uno de los problemas planteados desde hace mucho en Programación Lineal por su enorme utilidad en la práctica diaria en un mundo cada vez más competitivo, en especial cuando nos dedicamos a la cría de ganado y debemos escoger entre un conjunto de alimentos para los animales de entre los disponibles en el mercado, de modo se logren satisfacer las necesidades de nutrición de estos con el menor costo total posible.

Me imagino es innecesario decir los beneficios de lograr lo antes comentado en donde se pretenda ser competente en ganadería y se necesite comprarle los alimentos a un tercero, puesto es fácil notar como invertir en ellos del modo correcto redundaría no sólo en una considerable reducción del costo de la crianza y del engorde, y por tanto en la correspondiente reducción del precio de la carne, sino también incluso se podría lograr un posible incremento en la producción, al posibilitarse la cría de muchos más animales con la misma cantidad de alimentos tal vez antes desperdiciados en vano, o al disponer de más recursos financieros para la compra de más alimentos al utilizarse los disponibles de un modo más eficiente.

Por último y no menos importante, también se podría incrementar la calidad de la carne, puesto se lograría un mejoramiento sustancial en la nutrición del ganado y esto posibilitaría garantizarle unas mejores condiciones de vida y de salud a los animales.

En efecto, entre los muchos problemas resueltos por medio de la Programación Lineal se encuentra el nombrado Problema de la Dieta, dedicado a resolver los problemas de optimización en donde se pretende escoger entre un grupo de alimentos por su contenido en nutrientes de manera se satisfagan las necesidades de los consumidores de los mismos en cuanto a dichos nutrientes a la vez se consigue la reducción al mínimo de los gastos por concepto de la compra de los alimentos.

Los criadores de ganado interesados en mejorar sus resultados productivos utilizando el método comentado en los párrafos anteriores, y descrito en más detalle más adelante, en primer lugar deberán asegurarse de contar con la información listada en los siguientes puntos:

  1. Los alimentos para el ganado criado por usted disponibles en su mercado, y sus correspondientes precios de compra por unidad de masa (podría ser en kilogramos).
  2. Los nutrientes considerados importantes para la alimentación de su ganado en específico contenidos en una unidad de masa de alimento (podría ser en gramos de nutriente por kilogramo de alimento de utilizarse el kilogramo como unidad de masa de los alimentos).
  3. Las necesidades diarias del animal en cuestión en cuanto a los nutrientes considerados importantes para su nutrición, medida en una unidad de masa acorde a la utilizada en los puntos anteriores.
  4. La cantidad máxima de unidades de masa de alimentos capaz de ser consumida en un día por un animal promedio de la especie criada por usted.

Es innecesario decir como de la fidelidad de la información listada en los cuatro puntos anteriores depende en mucho el éxito obtenido en la reducción de los costos de alimentación de su ganado, así pues, no debe escatimarse tiempo al reunir dichos datos, si es posible consultando con un veterinario sobre las necesidades diarias de nutrientes de la especie a ser alimentada.

Nota: Por intermedio de un buen amigo me enteré de la existencia de un sitio web en donde se pueden encontrar las características de muchos alimentos para animales; el contenido de la página está en inglés, pero si la visitan en https://www.feedipedia.org/ tal vez podrían ver información de utilidad en su caso.

En todo caso, no vamos a internarnos en teorías innecesarias para la gente interesada más bien en hacer prosperar su negocio ganadero, aun si más adelante si debemos de ver el modelo matemático para representar el problema; por eso vamos a ver enseguida un caso hipotético de un mercado en donde podemos escoger entre 5 alimentos disponibles para alimentar a los animales, y vamos a considerar nuestro ganado necesita de 4 nutrientes fundamentales contenidos en ellos; es necesario tener presente la información ofrecida a continuación, por cierto tomada del libro “Formulación y Resolución de Modelos de Programación Matemática en Ingeniería y Ciencia”, no debería de corresponderse con la necesaria en su caso particular, y debe considerarla solamente como datos hipotéticos para una demostración práctica del método.

En la tabla siguiente se listan por las filas los alimentos disponibles en nuestro mercado ideal, y por las columnas se muestran los niveles de los nutrientes considerados de un cierto valor para la nutrición de nuestro ganado ficticio:

Alimentos Nutrientes digeribles (DN)Proteínas digeribles (DP)Calcio (Ca)Fósforo (P)
Maíz A78.66.500.020.27
Avena70.19.400.090.34
Maíz B80.18.800.030.30
Salvado67.213.70.141.29
Linaza77.030.40.410.86

Nota: En este caso demostrativo vamos a considerar los nutrientes dados en la tabla en gramos por cada kilogramo de alimento correspondiente, por tanto, la unidad de medida de masa para los alimentos será el kilogramo.

Por otro lado, imaginemos el precio por unidad de masa considerada de cada uno de los alimentos en el mercado es como se lo representa en la siguiente tabla; y debemos tomar también el kilogramo como unidad de masa para ser concordantes con la unidad utilizada con la información en la tabla previa:

Alimento Precio por unidad de masa
Maíz A1
Avena0.5
Maíz B2
Salvado1.2
Linaza3

En adición, vamos a considerar nuestro ganado tiene unas necesidades en cuanto a los nutrientes tenidos en cuenta por nosotros como las mostradas en la siguiente tabla, en gramos por día, puesto en esta ocasión también debemos ser concordantes con la unidad de medida de los datos dados antes:

Ganado Nutrientes digeribles (DN)Proteínas digeribles (DP)Calcio (Ca)Fósforo (P)
Ganado X74.214.70.140.55

Nota: Es imprescindible exista concordancia entre la unidad de medida utilizada por los datos de todas las tablas mostradas, y deben tener eso presente cuando recolecten la información real en su caso específico.

Por último, vamos a considerar un individuo de nuestro ganado no puede consumir más de 5 kilogramos de alimento cada día, de modo la conformación de su dieta tenga presente este importante hecho, puesto en caso contrario, si no tenemos en cuenta este dato, podría darse el caso de no llegar a satisfacerse las necesidades diarias de nutrientes debido a la necesidad de consumir cada día una masa de alimentos más grande de la posible a ser comida por el animal en específico en ese período.

Nota: La restricción anterior garantiza un animal pueda conseguir los nutrientes necesarios cada día para su existencia, sin embargo, esto podría lograrlo con una cantidad de alimentos insuficiente para hacerlo sentir satisfecho; esto significa un problema si se da ese caso, puesto si todos los animales comieran en libertad uno de ellos podría consumir la parte dedicada a sus congéneres por tener sensación de hambre a pesar de, en teoría, estar alimentado.

En este momento tenemos toda la información necesaria para elaborar el modelo matemático de la Programación Lineal y resolverlo para obtener un resultado óptimo, es decir, llegar a saber cuál o cuáles alimentos deberemos comprar de preferencia de entre los disponibles para alimentar a un espécimen de nuestro ganado, y en cuánta cantidad comprarlo según la unidad de masa utilizada, en nuestro caso hipotético la cantidad de kilogramos.

Los resultados deberán multiplicarse luego por la cantidad de cabezas de ganado para saber la cantidad total de kilogramos de alimentos necesarios por día, y utilizar esto como base para los cálculos posteriores para poder conocer cuánto alimento se necesitará en un período dado de tiempo (una semana, un mes, etc.).

En fin, en primer lugar vamos a exponer el modelo matemático general para la resolución de un Problema de la Dieta como el propuesto, y a continuación vamos a implementar dicho modelo matemático en Microsoft Excel con los datos hipotéticos de modo Solver pueda solucionarlo para nosotros.

En un problema de la dieta, como en todo problema de Programación Lineal, tenemos por lo menos tres factores a tener en cuenta:

  1. La variable o variables de decisión
  2. La función objetivo
  3. Las restricciones

En nuestro caso específico del Problema de la Dieta, la declaración de la variable de decisión será como sigue:

xj – Cantidad de unidades de masa de alimento “j” a ser consumida en cada día por un animal.

En cuanto a la función objetivo, ésta consiste en el costo de la compra del alimento a ser minimizado, y se expresa en forma matemática:

donde:

xj – Es la variable de decisión declarada antes.

cj – Es el precio de una unidad de masa del alimento “j” a ser comprado.

n – Es el número total de alimentos tenidos en cuenta (en nuestro caso hipotético 5).

Por último están las restricciones, las cuales son las condiciones imposibles de ser violadas para lograr la resolución correcta del problema:

donde:

aij – Es la cantidad de nutriente “i” en una unidad de masa de alimento “j” (en nuestro caso hipotético se dio en gramos por kilogramo).

bi – Es la cantidad mínima del nutriente “i” necesaria para un espécimen (en nuestro caso hipotético en gramos por día).

m – Es la cantidad de nutrientes tenidos en cuenta (en nuestro caso hipotético 4).

M – Es la cantidad de unidades de masa capaces de ser consumidas en un día por un espécimen promedio del ganado a ser alimentado (un animal nunca podría comer una cantidad superior a esta por sentirse lleno).

El modelo matemático descrito ahora puede ser llevado a Microsoft Excel, de manera lo podamos resolver por medio de la herramienta Solver con la información reunida en las tablas de datos, y así conocer cuánto alimento de cada clase debería de ser comprado para alimentar a un animal en un día, y cuál sería su costo al comprarlo en el mercado ideal.

La Figura 1 a continuación muestra cómo podría conformarse el modelo matemático del Problema de la Dieta en Microsoft Excel, utilizando para ello los datos hipotéticos, con los elementos factibles de ser modificados (Datos de entrada) con un fondo color verde, y los elementos no modificables (Fórmulas) con un fondo color amarillo.

Nota: Los interesados en ver cómo se han colocado las fórmulas del modelo matemático sólo deben revisar las celdas correspondientes de la tabla dada una vez descarguen el libro de Excel.

Figura 1: El modelo matemático del Problema de la Dieta representado en Excel.

En la figura se ha resuelto el problema utilizando Solver, y como se puede ver, el modelo recomienda la compra de 1.530 Kg de Avena (X-2) y de 0.023 Kg de Salvado (X-4) para alimentar a un animal durante un día, satisfaciendo sus necesidades de nutrientes con un costo de 0.79 unidades monetarias (puede notarse como las necesidades de nutrientes se satisfacen con un consumo diario de 1.55 Kg de alimentos combinados).

Por su parte, la Figura 2 muestra la ventana del Solver, en donde se han establecido los valores de las celdas necesarios para obtener la solución mostrada antes.

Nota: Es posible necesite instalar el complemento Solver en su instalación de Microsoft Excel antes de poder utilizarlo.

Figura 2: La ventana de Solver con los parámetros necesarios para la resolución del modelo matemático.

En todo caso, explicar cómo crear las tablas de Excel expuestas en la Figura 1, así como la introducción de las fórmulas para la representación del modelo matemático, se sale del marco de este texto y no la llevaré a cabo; por mi parte considero lo mencionado no representa complicación alguna para una persona con conocimientos básicos de Microsoft Excel, y más teniendo en las manos el libro con la implementación, puesto como he mencionado antes éste puede ser descargado.

Por último es necesario decir se debe realizar un estudio antes y después de la utilización de un modelo matemático como el presentado de manera se pueda discernir si su uso en la práctica en verdad mejora los indicadores productivos y financieros como se lo esperaba (la práctica es el criterio de la verdad).

En caso de tener alguna duda acerca de cómo implementar un modelo matemático para resolver un Problema de la Dieta en su caso particular, es libre de comunicarse conmigo y con gusto le prestaré mi asistencia hasta donde me sea posible; para esto puede escribir un comentario en esta entrada, aun si podría ser más recomendable escribirme un correo electrónico directamente a la dirección expuesta en la página principal de este sitio.

Los interesados en conseguir el libro de Microsoft Excel con la implementación del modelo matemático presentado en este texto pueden hacerlo visitando la entrada correspondiente en mi blog en https://cubansolutions.blogspot.com puesto debido a las reglas de este sitio no se me permite poner archivos compactados.

¡Hasta pronto!

Esta entrada ha sido publicada en Cómo hacer... y etiquetada como , , , , , , . Guarda el enlace permanente.

Deja una respuesta

Tu dirección de correo electrónico no será publicada.