Contexte

Ce projet de macro Excel 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. A l'époque de ce projet sur Excel, la société avait un système de prévisions développé en interne (en JAVA) et un ERP de chez Infologic (Copilote), et ce couple d'applications rencontraient les difficultés suivantes:

• Le module de prévisions développé en JAVA ne gère à ce moment que la partie promotions des prévisions mais pas la partie Fonds de rayon. Il n'est donc pas encore complet.
• Le module de prévisions n'a pas encore de système d'export des données vers l'ERP. De toute façon, le fichier exporté devrait contenir des prévisions complètes avec également la partie fonds de rayon, ce qui ne serait pas possible en l'état.
• Sur le processus de promotions, la difficulté rencontrée est de faire respecter un nouveau processus standard au niveau du groupe. Plusieurs sociétés ne sont pas encore à même de respecter ce processus Par conséquent, il faudrait avoir un système de prévisions suffisamment souple pour que l'on puisse intervenir et corriger les mauvaises pratiques, le temps que tout le monde respecte la méthodologie demandée. Le module développé en JAVA est très rigide et n'offre pas de souplesse à ce niveau là.

La décision a donc été prise de développer un fichier Excel à positionner entre le module de prévision et l'ERP:

Principe de fonctionnement des promotions

Avant de rentrer dans la description du fonctionnement du fichier Excel, il faut expliquer les grandes règles de ce processus ambitieux de gestion des promotions qui se met en place au sein du groupe.

Statuts de promotions

Chaque société du groupe doit saisir dans le module de promotions toute offre qu'elle souhaite mettre en place:

• Une promotion est tout d'abord créé avec un statut "simulation" afin de permettre à l'usine de simuler cette promotion dans sa charge afin de valider sa capacité à la réalise
• Si l'offre est réalisable d'un point de vue industrielle, la promotion passe à un statut "proposition" et elle est envoyée au client. Si l'usine est dans l'incapacité de la réaliser, alors la promotion passe au statut "non retenue"
• Si l'offre est retenue par le client alors elle passe au statut "confirmée" et si elle est non retenue par le client, alors elle prends le statut "non retenue"

Niveaux de Qualité de lissage

Un système de niveau de qualité de la prévision a été mise en place. On peut brièvement décrire le barème ainsi:

• Niveau 1: c'est le niveau le moins précis. On divise la quantité totale que l'on pense vendre sur la promotion par la durée de la promotion. On obtient une prévision journalière de même quantité chaque jour.

• Niveau 2: le système de promotions possède un système de clés de répartition fonction des comportements de la centrale et des durées de promotions. Il a été constaté que les centrales avaient des comportements d'approvisionnement qui se ressemblaient d'une année sur l'autre. Ainsi certaines centrales vont commander très fort dans les premiers jours de promotions, quand d'autres vont plutôt commander lentement au départ. De ce fait, au niveau 2, on applique ces clés de répartitions au total prévisionnel, et on obtient ainsi un lissage de meilleure qualité

• Niveau 3: On peut passer à ce niveau de détail lorsque l'on a reçu la majeure partie des commandes de la centrale. Ce niveau ne peut donc être atteint que peu de temps avant le début effectif de la production. Dans plus de 90% des cas, les centrales ont envoyé leurs commandes à J-15. Le service commercial va analyser les commandes reçues de la centrale et va effectuer un nouveau lissage de qualité

• Niveau 4: ce niveau ne devrait pas être atteint mais il s'agit en fait de la même qualité que le niveau 3. Simplement c'est l'usine qui va effectuer la saisie directement sur base d'informations reçues par email en lieu et place du service commercial. Il s'agit en fait d'un statut de "souplesse" mis dans la macro Excel pour permettre aux usines de palier au fait que les services commerciaux n'aient pas effectués leurs saisies.

Délais de saisies des promotions et qualité de lissage

1) Il est demandé à ce les promotions soient saisies et qu'elles passent en statut "confirmée" dans le temps imparti pour faire une "mise en place" de volaille chez les éleveurs. Ce temps imparti correspond ni plus ni moins qu'au temps d'approvisionnement d'une volaille correspondant à l'espèce et à la qualité requise par le cahier des charges de la marque.

En général, plus les volailles sont de qualité, plus leur âge est élevé au moment de l'abattage. Comprenez donc que plus la volaille est de qualité, plus l'approvisionnement doit être anticipé et donc plus les promotions doivent être travaillées en amont.

Il est demandé à ce que les usines aient donc atteint un statut de promotion "confirmée" ou "non retenue" et éviter les promotions au statut "proposition" car l'usine ne sait pas si elle doit commander les volailles pour ces promotions. Les dates de consommation de ces produits sont très courtes ce qui fait que toute erreur d'approvisionnement a une conséquence vraiment néfaste. Dans le pire des cas, la volaille non consommée sera mise en congélation et pourra servir sur des marchés moins valorisés.

2) D'autre part il est demandé aux services commerciaux d'atteindre le niveau 2 de qualité de lissage dans le délais d'approvisionnement. On oblige ainsi le service commercial à étudier le comportement de sa centrale et donc d'affiner la répartition des volumes entre le début et la fin de la promotion.

3) Et pour terminer, il est demandé aussi à ce que la qualité de lissage passe au niveau 3 au plus tard 15 jours avant le début effectif de la production car cela va permettre :

• d'effectuer des plannings de production à 15 jours

• de communiquer auprès des salariés les plannings de rotation avec de l'avance

• de réserver les espaces de stockage et les mètres linéaires pour le transport

• d'ajuster les approvisionnements de consommables (étiquettes, encarts, barquettes, film PVC, cartons ...)

Il est toujours compliqué de suivre tous les intervenants d'un tel processus, surtout au niveau d'un groupe de grande taille. Les usines portent la responsabilité des mises en place de volaille et elles suivent donc de près les informations promotionnelles transmises par les services commerciaux. Il a donc été demandé d'avoir des fonctionnalités pour identifier toutes les promotions qui n'étaient pas dans les clous afin de faire des relances rapidement et facilement.

Spécificité des promotions groupe

Parfois la promotion est émise directement par la groupe et implique plusieurs sociétés du groupe. Dans ce cas là, le workflow est différent. Le groupe estime un volume total de ventes et ensuite chaque société est notifiée qu'elle doit prendre une part de ce volume. A partir du moment ou la société prends part à ce volume, elle rentre dans le processus classique d'une promotion, à savoir qu'elle doit respecter les délais évoqués au paragraphe précédent.

Le problème identifié dans ce processus est qu'il manque un garant de la cohérence entre un volume estimé total groupe et la somme des volumes de chaque société qui y prenne part. Les usines se retrouvent donc avec 2 volumes différents pour gérer leurs mises en places. On souhaite donc bénéficier d'une fonctionnalité Excel pour identifier les écarts sur chaque promotion groupe.

Fonctionnement du fichier EXCEL

Partie Promotions

Extractions

Le module de prévisions développé en JAVA nous permet d'extraire 2 types d'extraction Excel

Une extraction contenant les données principales Cette extraction comporte plusieurs centaines de lignes:

• numéro de promotion
• libellé de la promotion
• code article
• libellé du code article
• société concernée
• son statut
• sa prévision totale
• date de début de promotion
• date de fin de promotion

Une extraction contenant les données de lissage de la promotion. Cette extraction comporte plusieurs milliers de lignes:

• numéro de promotion
• libellé de la promotion
• code article
• libellé du code article
• société concernée
• date de prévision
• volume date qualité lissage 2
• volume date qualité lissage 3

Il est prévu 2 onglets séparés pour coller distinctement ces 2 extractions. Il va de soi que les extractions doivent tout le temps avoir le même format, c'est-à-dire les mêmes colonnes, au même endroit.

Macros promos et lissages

Une fois ces 2 extractions récupérées et collées, on va lancer 2 macros de manière successives.

La première macro va traiter la première extraction et compléter un onglet de synthèse des promotions. Le programme alimente l’onglet de synthèse en y mettant toutes les nouvelles promotions. Il scanne également les promotions qui étaient déjà présentes dans l’onglet et si l’une des données majeures comme « Statut », « Premier Départ », « Dernier Départ » ou « Volume Barquette groupe » a été modifié dans le module de prévision alors il écrase les anciennes données par les nouvelles et conserve un historique des anciennes valeurs en commentaires.

La vitesse d'exécution est fantastique comparée au temps que mettaient les planificateurs à regarder toutes les promotions manuellement.

Toutes les données ci-dessus sont fictives (codes produits, volumes etc)

La deuxième macro va lisser le volume de la colonne prévision société dans les colonnes de droite. Elle trouve cette information dans la 2ème extraction.

En fonction de code de qualité de lissage de la prévision, la couleur ne sera pas la même (mise en forme conditionnelle).

Le programme consulte chacune des lignes de l’onglet de synthèse des promotions et regarde le statut de la promotion et le code du qualité du lissage (le code peut être 1,2, 3 ou 4).

• Si le statut de la promotion est « non retenu » ou que le code de qualité de lissage est à « 1 » alors le programme ne fait rien et passe à la ligne suivante.

• Dans le cas contraire, le programme regarde si un lissage existe dans l’extraction.

• Si un lissage est trouvé, le programme note le code « 2 » ou le code "3" dans la colonne « Jour-jour » et positionne les valeurs. En fonction de ce code, le code couleur sera pris automatiquement. Si des valeurs existaient avant l’importation des données, celles-ci sont alors écrasées.

Il est possible de lisser une promotion bien que le lissage n’existe pas dans le module de prévisions. Dans ce cas, il faut venir manuellement renseigner le code 4 dans la colonne jour-jour sur les lignes que l’on souhaite lisser et cliquer sur le bouton "lissage" et la macro effectuera ce travail pour vous. Le volume sera alors réparti équitablement selon le nombre de jours ouvrés de la promotion. Si par la suite, un lissage de meilleur qualité est effectué dans le module de prévision, alors celui-ci viendra écraser les données précédemment lissées.

Il faut reconnaitre la très bonne ergonomie du tableau offerte par Excel. On peut ensuite facilement filtrer par statut de promotion, par code de qualité de lissage, par code article, par jour etc. Sans compter que toutes les informations sont présentes dans un seul et unique tableau ce qui permet ensuite de pouvoir effectuer des statistiques.

Principe de fonctionnement des fonds de rayon

Les ventes fonds de rayon sont classiquement les ventes qui sont réalisées hors promotions. La prévision totale est donc l'addition des promotions et des fonds de rayons. Si un problème de stock se fait ressentir on va chercher à privilégier le taux de service des promotions. On a donc besoin de transmettre au module GPAO de l'ERP des prévisions qui gardent le détail de la part promotion de la part fonds de rayon.

Une analyse des pratiques de plusieurs ateliers a permis d'identifier un fonctionnement commun sur la gestion de ces fonds de rayon.

• Travailler à partir d’une semaine standard : on considère que les ventes Fonds de rayon sont plutôt stables. On rentre donc dans une table des prévisions de vente par UVC et par jour. On considère par défaut que les prévisions de vente pour les semaines à venir sont égales à cette semaine standard.

• Mettre à jour une semaine standard : une usine qui travaille à partir d’une semaine standard a besoin de pouvoir vérifier que sa semaine standard correspond toujours bien à la réalité de ses ventes. Elle compare donc les ventes réelles aux prévisions rentrées dans la semaine standard et les gros écarts donnent lieu à une analyse et une modification de la prévision standard

• Travailler à partir d’un historique de vente : pour certaines activités on part du postulat qu’une semaine future va ressembler à une semaine passée. On consulte donc les ventes Fonds de rayons passées (ventes dépolluées des promotions) et on les applique comme prévisions de vente pour une semaine future

• Gestion des jours fériés : les semaines qui contiennent des jours fériés nécessitent une adaptation des prévisions. En général on reporte le volume de ventes habituelles de la journée fériée sur les jours précédents ou suivants le férié

• Evolution des ventes : on peut vouloir appliquer un coefficient d’augmentation ou de diminution sur un ou plusieurs produits, ou sur une famille de produits particulière. Par exemple une semaine de début de mois est plus ou moins forte qu’une semaine de fin de mois

Fonctionnement du fichier EXCEL

Partie Fonds de rayon

Données de base et fonds de rayon standards

Tous les articles à gérer en FDR ou en promotion doivent être créés dans l’onglet de synthèse des fonds de rayon. Il y a différentes colonnes « Type », « Espèce », « Couleur », « Matière » et « Equiv » qui doivent être renseignées le plus proprement possible car ces données servent de base à un onglet de statistiques permettant d'avoir une synthèse global des prévisions.

Les colonnes de Lundi à Samedi de la semaine standard permettent de définir pour chaque article et chaque jour un fond de rayon standard. Par défaut et si rien n’est entrepris par l’ordonnanceur, toutes les semaines futures vont prendre pour prévisions ces données là. 

Modifier un fonds de rayon standard

Bien entendu, la vie des ventes fonds de rayon n'est pas toujours "standard". D'ailleurs les planificateurs expérimentés savent que le positionnement de la semaine dans le mois a son impact sur les ventes. Ils ont des écarts de 15% entre la première semaine du mois et la dernière. Les congés et particulièrement les congés des parisiens ont également des répercussions sur les fonds de rayon. Bien entendu, les promotions ont également un effet de cannibalisme. Pour toutes ces raisons il est possible de modifier des prévisions pour une semaine donnée, ou pour un jour donné, ou pour une famille d'articles voir même pour un article précis. On peut lancer une macro de modification de prévisions pour une semaine donnée.

Pour faciliter la modification des prévisions fonds de rayon, il a été développé un formulaire (userform):

• Renseigner la semaine sur laquelle vous souhaitez modifier les prévisions. S’il s’agit de la première modification, alors les 2 menus déroulants suivants sont vides et tous les coefficients à l’écran sont à 0.

• Sélectionner une semaine de référence dans le 2ème champs. Cette semaine servira de base au calcul de la prévision. Il est possible de choisir la semaine avec les FDR Standards mais il est aussi possible de venir choisir n’importe quelle autre semaine.

• Choisir ensuite si vous voulez prendre en compte les ventes réelles de la semaine de référence ou les prévisions de la semaine de référence. Si à l’étape précédente vous avez choisi de prendre les fonds de rayon standard, le système sélectionnera par défaut FDR standard dans ce 3ème champs. 

• Positionner ensuite, si besoin, des coefficients d’ajustement et cliquer sur « CREER/MODIFIER ».

L’avancement du calcul est suivi par une barre de chargement et vous êtes notifié ensuite de la fin du programme par un message.

S’il ne s’agissait pas de la première modification sur cette semaine, alors le système renseignera automatiquement la semaine de référence ayant été choisie et les coefficients éventuellement positionnés. Lorsqu’un coefficient a été positionné, il apparaît en vert pour faciliter la lecture et bien indiquer qu'un travail a déjà été effectué.

Par exemple, si nous rappelons la semaine 2 de 2018, nous voyons les coefficients que nous venions de poser.

Toutes les prévisions FDR sont consultables et modifiables manuellement sur l’onglet de synthèses des fonds de rayon. 

Les semaines qui ont fait l’objet d’un ajustement par le biais de la fonction automatique ont une ligne orange au-dessus du titre. Ainsi la semaine 02 de 2018, qui a servi d’exemple, nous pouvons bien voir cette bande orange.

Encore une fois, l'ergonomie d'Excel est une force, on peut retrouver dans ce tableau toutes les informations concernant un fond de rayon et toutes les données sont filtrables. Nous avons insérer une colonne nommée "Réel" entre chaque jour de prévision. Cette colonne va être renseignée automatiquement avec l'ajout d'une extraction provenant de l'ERP. Cette extraction, récupérée tous les Lundi, va nous fournir le détail des ventes historiques dépolluées des promotions de la semaine passée. Une macro va positionner les résultats dans ces colonnes et une mise en forme conditionnelle va permettre d'identifier visuellement très les gros écarts entre les ventes et les prévisions. L'objectif de cette pratique est de pouvoir réajuster les prévisions futures.

Exemple ci-dessous avec une surbrillance orange pour toutes les ventes ayant un écart de 30% avec la prévision.



Extraction des prévisions

Une fois que le planificateur (ou le prévisionniste) a effectué son travail sur les prévisions promotions et sur les prévisions fonds de rayon, il peut décider de générer un fichier au format txt qui sera déposé directement dans le répertoire d'arrivée de l'ERP. En plus du fichier de prévision, la macro va déposer un fichier avec l'extension .txt.ok pour indiquer à l'ERP que le fichier de prévision est prêt. On évite ainsi le risque que l'ERP commence à intégrer un fichier de prévision non complet.

Pour générer ce fichier de prévisions, la macro va parcourir les onglets de synthèse des promotions et des fonds de rayon et repositionner ligne à ligne et date à date toutes les données par référence. On obtient alors un fichier comme suit:

• Colonne 1: référence du code article

• Colonne 2: libellé du code article

• Colonne 3: un code, ici le 1 qui indique à l'ERP la maille des prévisions (dans le cas de ce projet -> journalier)

• Colonne 4: la date de la prévision

• Colonne 5: la prévision totale

• Colonne 6: la prévision fonds de rayon

Conclusion

Ce projet a été un succès. D'une part car il a été mis en place dans 3 usines différentes et il a permis de tester un processus commun standard avant intégration dans l'ERP et les outils du groupe. On se facilite ainsi la tâche de standardisation d'un processus. D'autre part, les temps de développement interne sur les outils du groupe ont été très longs. Si nous avions dû les attendre sans développer ce projet intermédiaire, cela aurait mis en difficulté les usines concernées.

Sur le plan négatif, on peut dire que ce fichier est très consommateur de ressources au niveau mémoire. Les utilisateurs ont régulièrement eu besoin de fermer toutes leurs applications, voir effectuer un redémarrage du PC. Bien souvent, la macro était lancée pendant une pause café. Les tests faits en développement de mon côté étaient rapides (< 1 minute de traitement). On peut expliquer cet écart par:

• la différence de puissance des ordinateurs utilisés en usine

• ne pas avoir fermé tous les autres fichiers Excel avant le lancement de la macro

• ne pas avoir fermé les autres applications avant lancement de la macro

• avoir lancé la macro en fin de journée alors que la mémoire RAM du PC était déjà saturée par les autres utilisations

• certains utilisateurs ont créé de nombreux onglets avec des liaisons avec d'autres classeurs, ce qui a rendu le fichier beaucoup plus lourd et lent

D'autres incidents mineurs ont pu avoir lieu, comme par exemple des utilisateurs qui ont renommé des onglets, ce qui n'a pas plus aux macros! Ou encore les extractions qui provenaient de l'ERP qui n'étaient pas effectuées convenablement.

Pour toutes ces raisons, je pense que l'idéal est de réaliser ce genre de projet directement sur une application web. En effet ce type d'application est très flexible, il n'y a pas besoin d'installation sur le PC des utilisateurs, un simple navigateur suffit. On pourrait envisager de mettre en place un cliqué-glissé pour importer les extractions de l'ERP et du module prévisions. Les performances seraient certainement exceptionnelles et on aurait alors un projet avec que du positif !

Si vous avez lu cet article de projet jusqu'au bout, je vous en remercie vivement. J'espère que vous y avez trouvé des informations qui vous permettent d'avancer dans vos problématiques. Vous pouvez me laisser des commentaires ou me contacter par le formulaire de contact pour une éventuelle collaboration.

Et pour terminer, sachez que j'ai réalisé le projet de digitalisation du fichier de prévision en format webapp et vous pouvez découvrir ce retour d'expériences !