Planification Excel à court et moyen terme en agroalimentaire

Création et mise en place d’un fichier Excel de planification permettant de planifier des OF sur 15 jours avec une vision de la contrainte matière.

Contexte et enjeux du projet de planification Excel

Ce projet de planification Excel repose sur la création d’un Excel et de macros. Il a été réalisé dans une grande entreprise agroalimentaire française, leader sur le marché de la volaille. L’entreprise vise un système d’information (SI) totalement intégré, en standardisant tous ses flux dans les applications métiers, son ERP et ses outils associés.

Pourquoi développer une macro si l’objectif est un SI intégré ? Parce que mettre en place un système intégré prend beaucoup de temps.

Pour répondre à un besoin urgent d’une usine de 200 personnes, l’entreprise a choisi une solution temporaire. Elle a lancé un projet de planification Excel pour structurer le processus de planification et d’ordonnancement. Le fichier inclut la gestion des prévisions, la planification à long terme et moyen terme et enfin l’ordonnancement à très court terme sur lignes de production.

Cet article présente très succinctement le fichier de planification Excel à moyen terme. Vous pouvez néanmoins consulter les articles des fichiers liés:

• Fichier de gestion des prévisions
• Fichier de planification long terme (article non écris à ce jour)
• Fichier d’ordonnancement sur ligne court terme

Ces fichiers ne sont pas reliés par des liaisons entre tableurs Excel, une méthode source de lenteurs. Ils communiquent grâce à des systèmes d’importation et d’exportation performants.

Les contraintes de la planification dans la volaille

Tout d’abord, le prérequis au processus de planification consiste à récupérer les prévisions de ventes élaborées dans le fichier conçu à cet effet.

Dans le fichier « Planning OF », le planificateur peut importer toutes les prévisions. En parallèle, il doit également récupérer, depuis l’ERP, les données concernant le planning d’abattage pour la semaine en cours.

Contrainte de disponibilité matière

La planification dans le secteur de la volaille est complexe, notamment en raison de la gestion de la matière. Il faut définir un plan de fabrication en tenant compte des stocks pour maximiser le taux de service, tout en considérant la matière à abattre. Cela crée une double contrainte. Aucun ERP ne répondait correctement au besoin du client.

Si vous regardez vos prévisions de vente et définissez votre plan de fabrication idéal en fonction d’objectifs de couverture, vous pourriez avoir besoin de 1.5 tonnes de cuisses d’un poulet jaune standard et de 2 tonnes de filets de poulet jaune standard. Mais allez-vous recevoir cette matière de l’abattage ? Si vous ne la recevez pas, votre plan de production devient alors caduque et non réalisable ! A l’inverse, que faire si vous avez trop de matière ? Dans ce secteur, les DLC sont très courtes. Il faut donc rapidement trouver une solution pour écouler la matière excédentaire.

Contrainte de déséquilibre des ventes

Comment gérer les déséquilibres entre les ventes de filets et de cuisses ? Si votre volaille est normalement découpée, vous aurez 2 filets et 2 cuisses. Mais si les promotions ont touché les filets sans affecter les cuisses, vous vous trouvez avec de la matière excédentaire. Et ce n’est pas tout : une cuisse peut également être découpée en haut de cuisse et pilon, chacun ayant des niveaux de ventes différents.

Il faut donc ajuster constamment les niveaux de couverture, à la hausse ou à la baisse, en fonction du déséquilibre. En parallèle, des « partenariats » avec d’autres usines deviennent nécessaires pour échanger les morceaux. La planification devient essentielle pour avoir une vue claire sur 15 jours de l’atterrissage des stocks.

Contrainte de synchronisation des capacités

A ce stade, nous n’avons pas encore abordé la détermination des effectifs par ligne ni la capacité de production des machines d’abattage, de découpe et de conditionnement. Car dans un atelier de ce type, environ 200 personnes travaillent sur 6 lignes.

La chaine d’abattage traite des poulets entiers, abattus et déplumés. Chaque chaîne fonctionne à une vitesse propre, gérée par l’atelier d’abattage. Ensuite, elle rejoint la chaîne de découpe, sur laquelle il faut déterminer quels morceaux découper et quels calibres choisir (les barquettes sont souvent gérées au poids, pas au nombre de morceaux).

Sur un lot de poulets, les poids des cuisses et des filets varient. Si vous souhaitez des cuisses d’un certain poids, tous les poulets du lot ne seront pas découpés de la même manière. La chaîne continue sans s’arrêter. Les morceaux tombent sur des tapis (convoyeurs) ou sont récupérés par le personnel, puis placés dans des barquettes. Les barquettes suivent ensuite un convoyeur secondaire pour passer par les machines d’operculage. A la fin du processus, elles sont stockées dans un transtockeur.

Comme vous pouvez le voir, la production de barquettes de volaille pour la GMS est complexe. Le MRP classique proposé par tous les ERP traditionnels ne suffit pas. Voilà pourquoi ce projet a vu le jour.

Conception du fichier de planification Excel

Visualisation des colonnes du planning

Le fichier couvre un horizon de planification de 15 jours, renouvelé chaque semaine. Chaque jour présente 5 colonnes principales:

• Stock initial
• Achat/Approvisionnement
• Fabrication
• Ventes (combiné entre ventes réelles et prévisions)
• Stock fin de journée

En raison du nombre élevé de colonnes, nous avons configuré des visualisations. Avec un bouton, une macro masquera certaines colonnes. Cela permet d’avoir une vue rapide des ventes, des fabrications, des stocks, ou de se concentrer sur la journée souhaitée.

Les 5 colonnes principales peuvent avoir des « sous-colonnes » secondaires. Un bouton « + » ou « – » permet d’afficher ou de masquer facilement ces colonnes.

Importation des données

Le fichier Excel importe plusieurs types de données :

• Prévisions de ventes
• Planning d’abattage
• Commandes de ventes
• Stock initial
• Fabrication en cours

Les prévisions des ventes proviennent d’un autre fichier réalisé dans le cadre du projet. Le planificateur demande simplement l‘importation des prévisions. La macro effectue cette tâche automatiquement. Il peut ensuite consulter facilement les prévisions si nécessaire. A l’ouverture du fichier, un contrôle vérifie l’importation des prévisions chaque jour, évitant ainsi un oubli qui pourrait entraîner des erreurs.

Toutes les autres données proviennent de l’ERP. Comme je n’avais pas accès directement aux tables de l’ERP, le planificateur copie et colle une extraction dans un onglet du fichier Excel. Ensuite il lance une macro qui effectue des mises en formes et calculs intermédiaires pour s’insérer correctement au sein du planning. Les temps de traitement sont quasiment instantanés.

Paramétrage de la relation Prévisions / Ventes réelles

Une question fréquente en planification Excel concerne l’utilisation de deux données de vente. Il n’y a pas de réponse unique ; cela dépend de votre activité. Dans le cas présent, nous utilisons la donnée la plus élevée entre les ventes réelles et les prévisions pour calculer le stock de fin de journée. Une heure est toutefois paramétrable, et après cette heure, les prévisions ne sont plus prise en compte. En effet, on considère que passé 10h du matin, le total des ventes est plus fiable que le total des prévisions.

Le fichier propose un tableau récapitulatif pour comparer les ventes aux prévisions. Cette vue d’ensemble permet d’anticiper et de planifier les actions pour les jours suivants. Le planificateur peut aussi appliquer un coefficient aux fonds de rayon directement dans ce fichier. Ainsi si les ventes sont faibles par rapport aux prévisions plusieurs jours de suite, il peut ajuster le coefficient pour réduire les fonds de rayon des jours suivants.

Gestion de la contrainte matière

Nous avons décidé de ne pas utiliser un plan de production automatisé. En effet, après des tests, nous avons constaté que l’application des taux de couverture par référence génère toujours un plan de production irréalisable. Il ne prend pas en compte la capacité de la matière. Un simple MRP n’est donc pas adapté

De ce fait , nous avons implémenté un système de fenêtre qui apparait au-dessus du tableau de planification Excel pour afficher les disponibilités matière. Une macro recalcule cette fenêtre chaque seconde. Chaque fois que le planificateur entre une quantité à fabriquer pour une référence, le système calcule la quantité de matière nécessaire (selon la nomenclature) et met à jour le stock disponible. Cela permet une vision en direct de la double contrainte Matière/Taux de couverture.

Idéalement, un script de type solveur sous-contrainte pourrait calculer un plan de production prenant en compte ces deux contraintes simultanément. A mon avis, l’avenir des systèmes de planification réside dans leur capacité à générer des plans de fabrication adaptés aux contraintes des entreprises. C’est l’objectif de Planitools : concevoir et déployer ce type de programme.

Une autre fonctionnalité ajoutée permet de modifier à la volée les références de type « morceaux choisis ». Ces barquettes contiennent 6 morceaux : théoriquement, 3 hauts de cuisse et 3 pilons. Le cahier des charge de la marque autorise de modifier le nombre de hauts et de pilons, tant que le total reste de 6 morceaux. Afin de rééquilibrer un déphasage de consommations ou de stock entre les hauts et les pilons, le planificateur peut ajuster les quantités dans les nomenclatures pour une journée donnée.

Si le planificateur se retrouve avec trop de matière dans une référence et pas assez dans une autre, il pourra procéder à la substitution de matière. Il aura 2 types de substitutions:

• la substitution au sein de la même référence produit, ce qui impliquera une gestion spécifique en logistique, car deux stocks de même référence seront différents
• la fabrication du besoin sur une autre référence, où la préparation de commande devra prélever une autre référence pour satisfaire certains clients

Indicateurs de planification Excel

Le planificateur gère surtout la partie matière à ce stade. En effet, le fichier inclut un « bilan matière » qui indique les atterrissages de stock à différents niveau macro. Celui lui permet de piloter sa stratégie de congélation, ses approvisionnements et ses exports à moyen terme.

Voir à moyen terme lui permet d’anticiper les montées en stock et d’éviter de se retrouver la tête dans le guidon. Toutefois, un mauvais pilotage peut parfois entraîner une dégradation rapide du taux de service, qui peut perdurer quelques jours. Depuis l’implémentation du bilan matière, les erreurs de pilotage se sont nettement rarifiées.

Exports des données

L’atelier de production est informatisé, et tout le personnel déclare les fabrications sur des Ordres de Fabrications dans l’ERP. Nous avons donc prévu un export de fichier CSV des OF afin d’importer les données dans l’ERP sans ressaisie.

D’autre part, un listing papier s’édite également avec une classification particulière, entièrement gérée par une macro. Ce listing permet notamment aux chefs d’équipes d’avoir une vision à la journée.

Retour sur la maintenance du fichier Excel

Le fichier est utilisé très intensément (6 jours sur 7) et en continu de 7H le matin jusqu’à 19H. Un planificateur principal et un planificateur secondaire s’occupent du fichier. Il arrive également qu’une personne tierce s’occupe du fichier le samedi matin.

Les utilisateurs du fichier maîtrisent Excel à un niveau « moyen ». Par exemple, ils ne savent pas utiliser les fonctions rechercheV ou créer des graphiques. De plus, ils n’ont pas non plus de connaissances poussées en macros.

Le fichier existe depuis plus de 2 ans, car il n’a pas encore été remplacé par des fonctionnalités dans l’ERP. Ainsi, le projet a donc été d’une grande aide pour cette usine.

En terme de robustesse, on peut voir qu’un fichier de planification Excel n’est pas l’idéal. En effet, au début, les utilisateurs ont fait plusieurs mauvaises utilisations, entraînant des bugs dans les macros. Petit à petit, ils ont progressé, et ont compris le fonctionnement du système. Lorsque les macros ont bogué, ils ont bien souvent pu déterminer eux même l’origine du problème.

Cependant ,malgré cela, des utilisateurs non expérimentés m’ont contacté plusieurs fois, notamment pendant les congés des planificateurs principaux, en cas d’arrêts maladies ou de démission. Il est donc difficile de garantir un suivi informatique sans une présence continue. On peut cependant mentionner que, malgré la livraison de 6 fichiers dotés de macro, je n’ai parfois pas été sollicité pendant plusieurs mois.

Voici quelques conseils pour une entreprise qui souhaite développer des fichiers de cette envergure avec un prestataire externe:

• Faire monter en compétence les utilisateurs du fichier sur Excel d’une manière générale avant le début du projet
• Prévoir une présence intensive et régulière avec le prestataire pendant le premier mois, car des bugs surviendront et des erreurs d’utilisation se produiront
• Mettre en place une méthode de maintenance avec le prestataire à long terme, pour qu’il puisse accéder au fichier en cas de besoin
• Assurer une formation complète sur le fichier si un utilisateur principal doit être remplacé (ajouter de la documentation d’utilisation)
• Noter les actions de résolution des bugs de macros, car ce sont souvent les même problème qui apparaissent
• Ne pas mettre à jour Excel avant que le prestataire n’ait testé et corrigé tous les bugs de la nouvelle version
• Dès maintenant, penser à l’avenir du projet Excel (transfert dans une application sur mesure ou sur l’ERP)

Développement dans l’ERP du groupe

Comme je le dis souvent, un projet Excel de ce type est un peu ambitieux par rapport à l’utilisation intensive qui en est faite. Ce projet Excel avait les objectifs suivants :

• Eteindre le feu dans une usine qui comptait beaucoup d’erreurs de pilotage
• Engager une réflexion sur la méthodologie de planification
• Tester rapidement une méthode de planification
• Pouvoir d’évoluer facilement la méthode de planification (de manière AGILE)
• Anticiper un projet de développement plus lent, plus long et plus coûteux au sein de l’ERP

Lorsque le projet de développement d’un outil de planification dans l’ERP du groupe a été initié, des discussions variées sur la méthode de planification dans la volaille ont démarré. Il a été assez difficile de faire comprendre qu’un MRP et un CBN classique ne conviendraient pas.

Une démonstration de l’utilisation du fichier Excel s’est avérée nécessaire. Nous avons transmis au prestataire une vidéo de présentation. La vidéo ne comporte pas de bande sonore, mais elle est légendée en direct afin de faciliter sa compréhension.

C’est notamment l’affichage et le recalcul chaque seconde de l’équilibrage matière qui est le plus intéressant. Des données confidentielles m’empêchent de la mettre ici en ligne.

Conclusion

Si vous avez pris le temps de lire cet article jusqu’au bout, je vous en remercie vivement. J’espère que vous y avez trouvé des informations utile pour avancer dans vos projets.

L’univers de la planification dans la volaille est vraiment passionnant. Et ce qui le sera encore plus sera la mise en place d‘algorithme intelligent comme des solveurs sous-contraintes.

N’hésitez pas à me laisser des commentaires ou à me contacter via le formulaire de contact pour toute collaboration.

Synthèse des principales fonctionnalités

• Bilan matière complet donnant une visibilité sur 15 jours par morceau anatomique
• Affichage dynamique et temps réels de la consommation matière lors de la construction du PDP, Calcul des stocks projetés
• Importation d’extractions issues de l’ERP (abattage, commandes clients, fabrication en cours) et exportation des OF du fichier Excel vers l’ERP
• Différentes configurations d’affichage des colonnes du tableau pré-enregistrées
• Automatisation des différents rapports à destination des fournisseurs et du service logistique pour préparer la place de la production, Calcul d’un plan de production automatisé sur base de couverture de stock
• Importation des prévisions de ventes, Editions automatisés du plan de production, Visualisation de l’écart Ventes Réelles/Prévisions, Modification dynamiques des nomenclatures de produits variables, Subsitution de matière

Découvrez les services que je propose : « Développement GPAO Web« , « Développement GPAO Excel« .