Création d'un calculateur de matières premières

Bonjour à tous je suis agriculteur et je cherche à créer un logiciel de rationnement pour mes moutons, j'ai donc besoins d'avis de Pro d'Excel

Afin que vous compreniez mieux je vais vous expliquer les bases du rationnement, pour en venir au cœur du problème : Comment l'automatiser sur Excel ?

Tout d'abord il faut considérer les besoins individuels de chaque animal, chaque brebis a des besoins différents suivant les stades physiologiques qu'elle traverse. Par ex : brebis vide, brebis gestante, brebis allaitante. Les besoins sont exprimés de la manière suivante :

Ex : Voici les besoins d’une brebis allaitante

Ici nous voyons que la brebis une capacité d’ingestion de 2,40 kg, qu’elle a besoins de 2,44 points d’énergie et de 241 PDIN et PDIE. Pour répondre à ces besoins nous avons à disposition 3 catégories d’aliments :

Les Fourrages : ce sont des aliments peu chers qui servent à remplir au maximum le pense de la brebis et à faire ruminer les animaux. Dans les fourrages on retrouve : la paille, le foin, l’ensilage de maïs…

Ils répondent majoritairement au besoin d’encombrement > faiblement au besoin énergétique > très faiblement aux besoins en protéines.

Ex : Voici les valeurs nutritives d’un ensilage de maïs :

Les céréales : ce sont des aliments concentrés en énergie, par exemple les céréales comme : le blé, l’orge, l’avoine…

Les céréales répondent majoritairement aux besoins en énergie > faiblement aux besoins en protéine et ne peuvent pas être utilisées en trop grande quantité sous peine de dérégler le système digestif des animaux.

Ex : voici les valeurs nutritives de l’orge :

La dernière catégorie d’aliment concerne les aliments protéiques.

Dans cette catégorie on retrouve Les tourteaux de soja, de colza, les pois… Ces aliments très couteux ne doivent pas être utilisés en grande quantité afin de contenir le coût de la ration :

Ils répondent majoritairement aux besoins en protéine > faiblement aux besoins en énergie.

Ex : voici les valeurs nutritives du Tourteau de colza

Maintenant que nous avons vu les bases, voici la stratégie de rationnement.

Le but est de remplir aux maximums la pense de la brebis avec du fourrage, car ce dernier est peu cher et stimule la rumination, le reste des besoins étant complété par les autres aliments.

Il existe 2 cas de figures :

Soit le fourrage ne couvre pas tous les besoins en énergie, dans ce cas :

Besoins = x * Fourrage + y * céréale + z * aliment protéique

Soit le fourrage couvre tous les besoins en énergie:

Besoins = x * Fourrage + z * aliment protéique

Grâce à la méthode des carrés de Pearson je sais aujourd’hui résoudre l’équation à 3 inconnues afin de connaitre la quantité exacte des 3 aliments pour répondre aux 3 besoins. Cependant cette méthode me semble incomplète car elle ne prend pas en compte le coût des matières premières.

Je cherche donc un moyen de calculer au plus juste la quantité de chaque matière première à intégrer dans la ration, tout en priorisant les matières premières les moins chères disponible dans une table

Mes questions sont les suivantes :

Y’a-t-il moyen d’automatiser le solveur d’Excel afin qu’il résolve l’équation permettant de trouver la quantité des 3 aliments ?

Peut-on ajouter une contrainte de coût dans la résolution de cette équation avec le solveur ?

PS : J’aborde ici un sujet très spécifique, j’ai donc simplifié à l’extrême afin que tout le monde comprenne les problématiques, mais s’il y a besoins d’infos supplémentaires (Tables des besoins, tables de valeurs nutritives je suis capable de les fournir)

Par avance Merci

bonjour,

une proposition via solveur.

si on considère la matière sèche et les prix que tu as donnés, le fourrage n'est pas le moins cher (2x plus cher!)

bonjour,

un feedback ?

Bonjour, je m'excuse pour mon temps de réponse, j'étais en déplacement toute cette semaine, si bien que je n'ai pas pris le temps de vous répondre..

Tout d'abord Merci de votre réponse claire et rapide ! j'aime beaucoup l'idée d'un écart à respecter cependant dans la conception quelques détail me manquent. Premièrement le solveur que vous présentez mets en relation les 3 matières premières entre elles, afin de diminuer le prix de la ration, il est vrai que je n'ai pas été très clair sur ce point.

Ma demande est de trouver la combinaison de 3 matières premières la moins chère, tout en répondants aux besoins de l'animal. Vu votre proposition et pour sécuriser le résultat on pourrait ajouter la contrainte fourrage > 0 ou > un pourcentage du poids de la ration totale.

En m'inspirant de votre travail j'ai fais une ébauche, afin de trouver les matières ayant le meilleur ratio €/UF et €/PDIN

Puis trouver la proportion de chaque aliment pour répondre aux besoins à l'aide du solveur. Je dois avouer que de ce côté là je sèche

Très bonne journée à vous

bonjour,

je n'étais pas sûr si ces valeurs UF, PDIN et PDIE étaient par kg MS ou par kg produit, parce que si je donne cela, il y aura des problèmes d'estomac, presque 2 kg de blé

les colonnes I&K y sont pour limiter un produit.

La ligne 13 y est pour plafonner ces valeur à 120% du besoin.

Les prix, ils sont à jour ?

bonjour, 2ième essai

bonjour,

pas de réaction ?

Avec un rapport de sensibilité, on sait encore plus (ici en version néerlandais), mais attention ce rapport est uniquement pour ce calcul à ces conditions !!!

- colonne J : les aliments qui ne sont pas sélectionnés sont donc trop chèr, par exemple le foin qui coûte 0.130€ (F11) sera utilisé dès que son prix est moins que 0.073 (F11). Le blé et l'orge doit être inférieur à 0.97€ (J13-J14)

- K16 : le soja sera toujours choisi avec le mais (en autres quantités évidamment) dans le trajet 0.37-0.66€, hors ce trajet, cela change.

- en feuil1, on voit que la limite inférieure de PDIN et la limite supérieur de PDIE sont déterminatives. En E23 et E27, on voit leur valeurs ! Pour cette solution un unité de PDIN est 0.0022€, un unité de PDIE est -0.0006€

- le PDIN de cette solution était 241, mais la combinaison "mais-soja" reste comme ca dans le trajet 215-257 (K23) de PDIN.

Donc, avec ce rapport, on sait beaucoup en plus, mais ces infos sont valable que pour cette solution.