Contexte

Ce projet de conception d'un fichier Excel et de macros a été réalisé dans une grande entreprise agroalimentaire française, leader sur le marché de la volaille. Cette entreprise a de grandes ambitions au niveau SI avec le souhait d'avoir un SI totalement intégré. Nous entendons par là, le souhait de standardiser tous ses flux au sein des applications métiers, son ERP et ses autres applications en relation.

Mais alors pourquoi réaliser une macro si la société souhaite un SI totalement intégré ? Et bien il faut comprendre que pour avoir un système intégré, cela prends du temps, voir beaucoup de temps.

Pour ne pas pénaliser une usine de 200 personnes qui avait un besoin urgent de structurer son processus de planification et ordonnancement, la société décide de construire un projet de conception sur Excel. Ce projet doit inclure à la fois 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 va présenter très succinctement le fichier de planification moyen terme. Vous pouvez néanmoins consulter également les articles des autres fichiers:

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

Les fichiers sont liés entre eux, non pas par simple liaison de tableurs Excel car je bannis ce type de pratique qui conduit inévitablement à des lenteurs. Ils sont liés par des systèmes d'importation/ exportation.

Les contraintes de planification dans la volaille

Tout d'abord le prérequis au processus de planification est d'avoir récupéré les prévisions de ventes élaborées dans le fichier conçu à cet effet.

Dans le fichier "Planning OF" le planificateur va pouvoir importer toutes les prévisions. En parallèle des prévisions de vente, le planificateur va devoir récupérer des données de l'ERP concernant le planning d'abattage de la semaine en cours.

Contrainte de disponibilité matière

La planification dans le milieu de la volaille est très particulière notamment par sa complexité de gestion matière. Il faut décider de son plan de fabrication en tenant compte des couvertures de stocks afin de maximiser son taux de service mais il faut en même temps tenir compte de la matière qui sera abattue. Nous avons donc une double contrainte. Aucun ERP ne semblait répondre convenablement au besoin du client.

Si vous regardez vos prévisions de vente et que vous définissez votre plan de fabrication idéal en fonction d'objectifs de taux de couverture, vous aurez peut-être besoin de 1.5 tonnes de cuisses d'un poulet standard jaune et 2 tonnes de filets de poulet standard jaune. Mais allez-vous recevoir cette matière de l'abattage ? Si vous ne recevez pas cette quantité de matière, votre plan de production devient alors caduque car non réalisable ! A l'inverse, que faire de la matière si vous n'en consommez pas assez ? Dans ce milieu, les DLC sont très courtes, il faut donc vite trouver une porte de sortie pour votre matière.

Contrainte de déséquilibre des ventes

Comment gérer les déséquilibre des ventes entre des filets et des cuisses ? Si votre volaille est normalement conçue, elle devrait comporter 2 filets et 2 cuisses ! Mais si les forces de ventes ont fait passer des promotions sur les filets mais pas sur les cuisses, vous vous retrouvez avec de la matière sur les bras... Et là je ne mentionne pas qu'une cuisse peut également être découpée en un haut de cuisse et un pilon qui n'auront eu non plus pas forcément le même niveau de ventes, ce serait trop simple ! Il va donc falloir ajuster constamment les niveaux de couverture à la hausse ou à la baisse suivant le déséquilibre du moment. En parallèle, il va falloir nouer des "partenariats" avec d'autres usines qui rencontreraient le déséquilibre inverse afin de pouvoir faire des échanges de morceaux. Tout ceci nécessite une bonne visualisation sur 15 jours de ses atterrissages de stock.

Contrainte de synchronisation des capacités

A ce stade, nous n'avons pas non plus parlé de la détermination des effectifs par ligne et de la capacité de production des machines d'abattages, de découpes et de conditionnement. Car dans un atelier de ce type, on compte tout de même environ 200 personnes et environ 6 lignes.

La chaine d'abattage va porter des poulets entiers abattus et déplumés, cette chaine a sa propre vitesse géré par l'atelier d'abattage. Elle rejoint ensuite une chaine de découpe sur laquelle il va falloir déterminer quels morceaux vous souhaitez découper, et quels calibres vous intéressent (car souvent les barquettes sont gérées au poids et non au nombre de morceaux).

Sur un lot de poulet d'abattage, il y aura des poulets de différents poids et donc leurs cuisses et filets ne feront pas tous le même poids. Si vous désirez découper des cuisses d'un certains poids, alors lorsque le lot de poulet passera en découpe, tous les poulets ne seront pas découpés. La chaine ne s'arrête pas, les morceaux tombent sur des tapis (convoyeurs) ou le personnel le récupèrent pour les positionner dans des barquettes. Les barquettes continuent leur chemin sur un convoyeur secondaire afin de passer dans les machines d'operculage. En toute fin de convoyeur la barquette se retrouvera stockée dans un transtockeur.

Comme vous pouvez le sentir, la production de barquette de volaille pour la GMS est d'une très grande complexité. Nous pouvons tout de suite oublier le sacro saint MRP - CBN classique proposé par tous les ERP du marché ... On comprends ainsi mieux pourquoi ce projet a vu le jour.

Conception du fichier de planification

Visualisation des colonnes du planning

Le fichier a un horizon de planification 15 jours et il sera renouvelé chaque semaine de manière tournante. Pour chaque jour on retrouvera 5 colonnes principales:

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

De fait d'un nombre de colonnes très important, nous avons configuré des visualisations. A l'appui d'un bouton, une macro viendra masquer certaines colonnes. On peut ainsi avoir une visualisation rapide des ventes, des fabrications, des atterrissages de stock, ou alors se positionner sur la journée de notre choix.

Les 5 colonnes principales peuvent aussi avoir un certains nombre de "sous-colonnes" secondaires. On utilisera un système de bouton "+" et "-" pour afficher ou masquer facilement ces colonnes.

Importation des données

Le fichier va importer 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 sont issues d'un autre fichier réalisé dans le cadre du projet. Le planificateur a eu juste à demander l'importation des prévisions. La macro fera le travail seule. Il aura ensuite accès aux prévisions s'il le souhaite afin de les consulter facilement. A l'ouverture du fichier, il y a un contrôle pour vérifier qu'il y a bien eu une importation des prévisions chaque jour. On évite ainsi un bête oubli du planificateur qui se retrouverait à travailler sur des données erronées.

Toutes les autres données seront importées de l'ERP. Comme je n'avais pas accès directement aux tables de l'ERP, le planificateur copie/colle une extraction dans un onglet du fichier Excel. Ensuite il lance une macro qui va se charger d'effectuer 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 qui revient souvent dans la planification est de savoir comment concilier l'utilisation de 2 données au niveau des ventes. Il n'y a pas de réponses toutes faites pour cela, il faut voir en fonction de votre activité. Dans le cas présent, nous utilisons la donnée la plus forte entre les ventes réelles et les prévisions pour calculer le stock d'atterrissage fin de journée. Une heure est toutefois paramétrable et passée cette heure, nous ne tenons plus compte des prévisions. En effet, on considère que passé 10h du matin, le total des ventes est plus fiable que le total des prévisions.

Une tableau récapitulatif est proposé par le fichier pour comparer le niveau de ventes par rapport aux niveaux des prévisions. Cette vision macro permet d'anticiper et de planifier une réaction pour les jours suivants. Nous donnons la possibilité au planificateur d'appliquer un coefficient à ses fonds de rayon directement dans ce fichier. Ainsi s'il constate que les ventes sont faibles par rapport au niveau des prévisions et cela plusieurs jours d'affilées, il pourra positionner un coefficient pour baisser les fonds de rayon des jours suivants.

Gestion de la contrainte matière

Il a été décidé de ne pas travailler avec un plan de production automatisé. En effet, suite à des tests, nous nous apercevons que l'application de taux de couverture par référence nous donne systématiquement un plan de production irréalisable car il ne tient pas compte de la capacité de la matière. Un simple MRP n'est donc pas envisageable.

De ce fait , nous avons implémenté un système de fenêtre qui apparait au dessus du tableau Excel, pour afficher les disponibilités matière. Une macro recalcule cette fenêtre toutes les secondes. A chaque fois que le planificateur entre une quantité à fabriquer sur une référence, le système calcule la quantité de matière que cela va consommer (au regard de sa nomenclature) et réactualise le disponible matière. Le tableau Excel affichera lui aussi en dynamique la projection de stock. On aura donc une vision en temps réel de la double contrainte Matière/Taux de couverture.

Il va de soi que l'idéal serait d'avoir un script de type solveur sous-contrainte capable de calculer un plan de production tenant compte de ces 2 contraintes en simultané. De mon point de vue, il est clair que l'avenir des systèmes informatiques de planification se situera dans leurs capacités à calculer des plans de fabrication adaptés aux contraintes des entreprises. C'est l'objectif de Planitools que de concevoir et de mettre en place ce type de programme.

Une autre fonctionnalité implémentée est la modification à la volée des références de type "morceaux choisis". Ce sont des barquettes qui contiennent 6 morceaux: en théorie 3 hauts de cuisse et 3 pilons. Le cahier des charge de la marque autorise de modifier le nombre de hauts et de pilons dès lors que le total des morceaux soient de 6. Afin de rééquilibrer un déphasage de consommations ou de stock entre des hauts et des pilons, le planificateur va pouvoir modifier pour une journée donnée les quantités présentes dans ces nomenclatures.

Si le planificateur se retrouve avec trop de matière dans une espèce de poulet et pas assez dans une autre, il pourra faire de la substitution de matière. Il aura accès à 2 types de substitutions:

• la substitution de matière au sein d'une même référence produit (ce qui impliquera une gestion particulière au niveau logistique car 2 stocks de même référence seront différents)
• la fabrication du besoin sur une autre référence (la préparation de commande devra prélever une autre référence pour satisfaire certains clients)

Indicateurs de planification

Le planificateur va surtout gérer la partie matière à ce stade. Il est inclus dans ce fichier un "bilan matière" qui indique les atterrissages de stock à différents niveau macro qui lui permettent de piloter sa stratégie de congélation, ses approvisionnements et ses exports à moyen terme.

Le fait de voir à moyen terme lui permet d'anticiper des montées en stock et éviter de se retrouver la tête dans le guidon. Il peut arriver, lors d'un mauvais pilotage, que le taux de service se dégrade rapidement et que cela perdure pendant quelques jours. Depuis l'implémentation de ce bilan matière, les erreurs de pilotage se sont nettement rarifiées.

Exports des données

L'atelier de production est informatisé et toutes les fabrications sont déclarées sur des Ordres de Fabrications dans l'ERP. Il a donc été prévu un export de fichier CSV des OF afin d'importer sans ressaisies les données dans l'ERP.

D'autre part, un listing papier est également édité avec une classification des feuilles bien particulière et gérée entièrement par une macro. Ce listing permet notamment aux chefs d'équipes d'avoir une vision à la journée.

Retour sur la maintenance du fichier

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

Les utilisateurs du fichier ont un niveau sur Excel que l'on peut qualifier de "moyen". Ils ne sont par exemple pas en mesure de mettre en place des rechercheV, ou des graphiques. Ils n'ont pas non plus de connaissance poussée en macro.

Le fichier perdure depuis plus de 2 ans car il n'a pas encore été remplacé par des fonctionnalités dans l'ERP. On peut donc dire que le projet a été d'un grand secours pour cette usine.

En terme de robustesse, on peut entrevoir qu'un fichier Excel n'est pas l'idéal. Le temps que les utilisateurs prennent en main l'outil il y a eu plusieurs mauvaises utilisations qui ont conduit à des bugs sur les macros. Au fur et à mesure les utilisateurs sont montés en compétences et ils ont compris comment fonctionnait le système. Lorsque les macros ont bogué, ils ont bien souvent pu déterminer eux même l'origine du problème.

Malgré tout, j'ai été contacté plusieurs fois car des utilisateurs non aguerris ont utilisé le fichier (pendant les congés des planificateurs principaux, pendant des arrêts maladies ou suite à une démission etc.) Il est donc assez compliqué de ne pas assurer une présence informatique pour le suivi de ce fichier. On peut toutefois mentionner que malgré la livraison de 6 fichiers dotés de macro, je n'ai parfois pas été appelé 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 il y aura des bugs et aussi beaucoup de mauvaises utilisations du fichier
• prévoir une méthode de maintenance avec le prestataire sur du long terme pour qu'il puisse accéder au fichier en cas de besoin
• s'assurer d'une formation complète sur le fichier en cas de remplacement du ou des utilisateurs principaux (adjoindre de la documentation d'utilisation)
• noter les actions de résolution des différents bugs de macro car ce sont souvent les même problème qui ressortent
• ne pas faire de mise à jour de Excel avant que le prestataire n'est testé et corrigé tous les bugs sur cette nouvelle version
• d'ors et déjà penser à la suite du projet Excel (transfert dans une application surmesure 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 faire é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 au sujet de la méthode de planification dans la volaille ont démarré. Il a été assez difficile de faire prendre conscience qu'un MRP et un CBN classique ne conviendrait pas.

Une démonstration de l'utilisation du fichier Excel a donc été 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.

Je remercie tous ceux qui auront pris le temps de lire cet article. 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.

Bien entendu je reste à votre disposition pour tout type de projets de développement. Vous pouvez me contacter en utilisant le formulaire de contact de ce site.

Bonne journée.