Formuler avec un tableur : contraintes, coût minimal, substitution des matières premières

Cette leçon fait partie du Spécialisation Fabriquer l'aliment, niveau expert. Elle s’ouvre à tout élève inscrit, dès l’inscription gratuite. Les niveaux 1 à 5 et les filières restent lisibles par tous.

Le carré de Pearson du niveau 11 règle la protéine entre deux ingrédients ; il ne sait rien du prix, de la lysine ou de la limite d'incorporation du tourteau de coton. Dès qu'un atelier a huit ou dix matières premières dont les prix changent chaque mois, il faut un outil qui tienne toutes les contraintes à la fois et trouve la formule la moins chère qui les respecte toutes. Cet outil est un tableur ordinaire, avec sa fonction de somme de produits et son solveur. Cette leçon construit la feuille ligne par ligne, résout un exemple complet en « unités » (vous mettrez vos propres prix), et montre ce que le solveur voit que l'œil ne voit pas : la vraie économie d'une substitution, et son point d'équilibre.

• ✓Construire une feuille de formulation : ingrédients en lignes, nutriments en colonnes, totaux par somme de produits, cibles minimum et maximum
• ✓Écrire les contraintes d'une formule (total, nutriments, limites d'incorporation) et lancer le solveur pour un coût minimal
• ✓Calculer à la main l'effet d'une substitution (manioc contre maïs) sur les nutriments et sur le coût, et trouver son prix d'équilibre
• ✓Lire un résultat de solveur : contraintes saturées, coût marginal d'un nutriment, solution infaisable, arrondis

1. Pourquoi le tableur, et ce qu'il fait exactement
2. Construire la feuille, ligne par ligne
3. Lancer le solveur : variables, objectif, contraintes
4. La substitution à la main : comprendre ce que le solveur fait
5. Lire la réponse du solveur comme un expert
6. La feuille de contraintes : un modèle à copier
7. Pour aller plus loin

• •La formulation à coût minimal cherche la formule la moins chère qui respecte toutes les contraintes à la fois ; le tableur la calcule par SOMMEPROD et son solveur (méthode linéaire).
• •Feuille : une ligne par ingrédient (kg, prix tel quel rendu atelier, min, max, teneurs), une colonne par nutriment, ligne Total, lignes Cible min et max, cellule Coût ; exemple C2 = 184,46 u pour 100 kg, 0,60 u pour 1 000 kcal, 9,22 u par kg de protéine.
• •Substitution à la main : effet brut, correction de la protéine (soja), correction de l'énergie (huile), coût comparé ; 10 kg de manioc à 0,70 u coûtent + 1,10 u une fois la formule corrigée ; prix d'équilibre 0,59 u, soit environ 60 % du prix du maïs.
• •Lecture du solveur : contraintes saturées (énergie, protéine, méthionine), coût marginal (0,25 u pour 100 kg par centième de point de méthionine), solution infaisable = cibles contradictoires à relâcher, arrondis rebouclés à 100 kg, limites d'incorporation jamais négociées.
• •Routine : recalcul mensuel avec prix et analyses à jour ; changement à l'atelier seulement si l'économie dépasse 1 à 2 % ; chaque formule a une version datée.