Gestion des prévisions de ventes : lissage, promotions et fonds de rayon

Création et mise en place d’une macro de traitement des prévisions de ventes de plusieurs sources, afin d’avoir un lissage global avant intégration dans l’ERP groupe.

Contexte des prévisions de ventes et des stratégies associées

Une grande entreprise agroalimentaire française, leader sur le marché de la volaille, a développé ce projet de macro Excel. Par ailleurs, l’entreprise nourrit de grandes ambitions pour son système d’information (SI), avec pour objectif de l’intégrer totalement. Cela implique, en effet, qu’elle souhaite standardiser tous ses flux au sein de ses applications métiers, son ERP et ses autres applications en relation.

Pourquoi développer une macro si l’objectif est un SI totalement intégré ?

Mettre en place un système intégré prend beaucoup de temps. A l’époque du projet sur Excel, l’entreprise utilisait un système de prévisions de ventes développé en interne (en JAVA) et un ERP de chez Infologic (Copilote). Cependant, ce duo d’applications présentait plusieurs limites:

• Le module de prévisions développé en JAVA ne gérait que la partie promotions des prévisions, en négligeant la partie Fonds de rayon. Il restait donc incomplet.
• Le module ne permettait pas d’exporter les données vers l’ERP. De plus, même avec un export, le fichier aurait été inutilisable sans intégrer les prévisions complètes, y compris les fonds de rayon.
• Le processus lié aux promotions rencontrait des difficultés pour appliquer une méthodologie standard au niveau du groupe. En effet, certaines sociétés n’étaient pas prêtes à adopter ce processus. Par conséquent, il était nécessaire de mettre en place un système à la fois flexible et performant pour corriger ces écarts tout en accompagnant progressivement les sociétés dans l’adaptation à cette méthodologie. Cependant, le module initial, développé en JAVA, manquait de souplesse pour répondre à ces besoins spécifiques..

Face à ces contraintes, l’entreprise a choisi de développer un fichier Excel qui servirait d’interface entre le module de prévisions et l’ERP.

Comment les promotions influencent les prévisions de ventes

Avant de décrire le fonctionnement du fichier Excel, il convient d’expliquer les règles fondamentales de ce processus ambitieux de gestion des promotions.

Prévisions de ventes et statuts des promotions

Chaque société du groupe saisit dans le module de promotions toute offre qu’elle souhaite mettre en place. Ensuite, les statuts des promotions se répartissent comme suit:

• Niveau 1: C’est le niveau le moins précis. La société divise la quantité totale prévue pour la promotion par sa durée. Cela produit une prévision journalière uniforme.
• Niveau 2: Le système applique des clés de répartition basées sur les comportements des centrales et les durées de promotions. Ces clés permettent d’obtenir un lissage plus précis.
• Niveau 3: Ce niveau se base sur les commandes réelles reçues, généralement 15 jours avant la production. Le service commercial analyse les commandes et affine le lissage par la suite.
• Niveau 4: Ce statut de « souplesse » permet aux usines de saisir directement les données en cas de retard des services commerciaux. L’usine effectue la saisie directement sur base d’informations reçues par email.

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

Pour garantir un approvisionnement adapté, il est essentiel que les promotions soient confirmées dans un délai qui permet de respecter le cycle de mise en place de la volaille chez les éleveurs. En effet, la durée de ce cycle varie en fonction de l’espèce et de la qualité de la volaille. Par ailleurs, plus la volaille est de qualité, plus l’approvisionnement doit être anticipé.

Les services commerciaux doivent atteindre le niveau 2 de lissage dans ce délai d’approvisionnement. Cela les oblige à étudier les comportements des centrales et à répartir les volumes avec précision.

Enfin, la qualité du lissage doit passer au niveau 3 au moins 15 jours avant le début de la production.

Ce niveau garantit:

• La planification de la production avec 15 jours d’avance.
• La communication des plannings aux salariés.
• La réservation des espaces de stockage et des mètres linéaires pour le transport.
• L’ajustement des approvisionnements de consommables (étiquettes, barquettes, cartons, etc.).

Les usines portent la responsabilité des mises en place de volailles. En outre, elles surveillent attentivement les informations promotionnelles transmises par les services commerciaux. Afin de faciliter cette tâche, l’entreprise a intégré des fonctionnalités dans Excel, ce qui permet d’identifier rapidement les promotions hors délai et d‘envoyer des relances.

Spécificités des promotions groupe sur les prévisions de ventes

Dans certaines situations, le groupe émet directement une promotion impliquant plusieurs sociétés. Le workflow suit alors une logique différente : le groupe estime un volume total de ventes, puis notifie chaque société de sa part à prendre. Une fois qu’une société accepte ce volume, elle intègre le processus classique d’une promotion, en respectant les délais mentionnés précédemment.

Cependant, ce processus présente un problème majeur : l’absence de contrôle sur la cohérence entre le volume total estimé par le groupe et la somme des volumes alloués à chaque société. Les usines se retrouvent ainsi face à deux volumes distincts pour organiser leurs mises en place. Pour résoudre cette incohérence, une fonctionnalité Excel identifiant les écarts entre les volumes est indispensable.

Fonctionnement du fichier Excel

Partie Promotions

Extractions

Le module de prévisions développé en JAVA propose deux types d’extractions Excel :

1. Données principales

Cette extraction, composée de plusieurs centaines de lignes, inclut les informations suivantes :

• Numéro de promotion
• Libellé de la promotion
• Code article et libellé correspondant
• Société concernée
• Statut
• Prévision totale
• Date de début et fin de promotion

2. Données de lissage

Cette extraction, beaucoup plus volumineuse, contient :

• Numéro et libellé de promotion
• Code article et libellé correspondant
• Société concernée
• Dates de prévision
• Volumes selon les niveaux de lissage 2 et 3

Les extractions sont collées dans deux onglets distincts. Il est donc essentiel que ces fichiers conservent toujours le même format (colonnes identiques, même disposition).

Macros pour promotions et lissages

Une fois les extractions collées, deux macros se lancent successivement

1. Macro de synthèse des promotions

Cette macro analyse l’extraction principale pour alimenter un onglet de synthèse. Elle :

• Ajoute toutes les nouvelles promotions
• Actualise les données modifiées (statut, dates, volumes groupe, etc.)
• Conserve un historique des anciennes valeurs via des commentaires

Cette méthode accélère considérablement le traitement des données, en comparaison avec une vérification manuelle par les planificateurs.

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

2. Macro de lissage

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

Cette macro répartit les volumes prévisionnels des sociétés dans les colonnes correspondantes :

• Elle identifie le statut et le code de qualité de lissage (1, 2, 3 ou 4).
• Si le statut est « non retenu » ou si le code est « 1 », elle ignore la ligne.
• Pour les autres cas, elle cherche un lissage dans l’extraction. Si un lissage existe, elle remplit les colonnes correspondantes avec les volumes associés (codes 2 ou 3). Dans ce cas, si des données existaient auparavant, elles sont remplacées.

Cependant, il reste possible de lisser manuellement en renseignant le code 4. La macro répartit alors le volume équitablement sur les jours ouvrés de la promotion. Un futur lissage de meilleure qualité dans le module de prévision remplacera automatiquement ces données.

Grâce à l’ergonomie d’Excel, les utilisateurs filtrent facilement les promotions par statut, qualité de lissage, code article ou date. Enfin, toutes les données regroupées dans un tableau unique facilitent également la réalisation de statistiques.

Optimisation des prévisions de ventes grâce aux fonds de rayon

Les ventes fonds de rayon correspondent aux ventes hors promotions. La prévision totale combine donc ces deux éléments : promotions et fonds de rayons. En cas de problème de stock, les promotions sont priorisées, d’où l’importance de transmettre des prévisions détaillées au module GPAO de l’ERP, distinguant chaque part.

Méthodes de gestion des 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.

Approche basée sur une Semaine Standard

Etant donné que les ventes fonds de rayon sont relativement stables, une semaine standard est utilisée comme référence. Ainsi, les prévisions de vente par UVC et par jour sont saisies dans une table, et par conséquent, les semaines à venir se basent par défaut sur ces données.

Pour garantir la fiabilité de cette méthode, il est essentiel de :

• Comparer les ventes réelles à la semaine standard : Les écarts importants déclenchent une analyse et une mise à jour de la prévision.
• Adapter les prévisions pour les semaines atypiques : Par exemple, les semaines contenant des jours fériés nécessitent une redistribution des volumes sur d’autres jours.

Utilisation d’un Historique de Vente

Dans certaines activités, les semaines futures sont calquées sur des semaines passées. Les ventes fonds de rayon historiques, épurées des promotions, servent alors de base pour les prévisions.

Ajustement des prévisions de ventes

Les prévisions peuvent être affinées en fonction :

• De la saisonnalité (début ou fin de mois)
• Des congés (comme les vacances parisiennes)
• D’un effet de cannibalisme lié aux promotions. Un coefficient d’ajustement est appliqué à un ou plusieurs produits selon les besoins.

Fonctionnement du fichier Excel

L’outil Excel centralise les données de fonds de rayon et facilite la gestion des prévisions via plusieurs fonctionnalités clés.

Données de base et Semaine 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. A cet égard, il existe différentes colonnes telles que « Type », « Espèce », « Couleur », « Matière » et « Equiv » qui doivent être renseignées de manière aussi précise que possible. En outre, ces données servent de base à un onglet de statistiques, lequel permet d’obtenir une synthèse globale 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. Ainsi, en l’absence de toute modification, et si rien n’est entrepris par l’ordonnanceur, toutes les semaines futures vont prendre pour prévisions de ventes ces données là. 

Modification des Fonds et Rayon Standard

Les prévisions standards peuvent être ajustées en fonction des variations saisonnières, des congés ou de l’effet des promotions. Ainsi, une macro dédiée permet de modifier les prévisions pour une semaine ou un jour précis.

Un formulaire utilisateur (userform) simplifie cette opération:

• Renseigner la semaine sur laquelle vous souhaitez modifier les prévisions de ventes. 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 ventes 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 ».

Une barre de chargement suit l’avancement du calcul, et un message vous notifie une fois le programme terminé.

S’il ne s’agit pas de la première modification sur cette semaine, alors le système renseigne automatiquement la semaine de référence choisie et les coefficients éventuellement positionnés. Un coefficient positionné apparaît en vert, ce qui facilite la lecture et indique clairement 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 se révèle être une véritable force. En effet, il est possible de retrouver dans ce tableau toutes les informations concernant un fond, de rayon et toutes les données sont filtrables. De plus, nous avons inséré une colonne nommée « Réel » entre chaque jour de prévision. Cette colonne est renseignée automatiquement grâce à l’ajout d’une extraction provenant de l’ERP. Cette extraction, récupérée chaque lundi, nous fournit le détail des ventes historiques, épurées des promotions de la semaine passée. Par la suite, une macro positionne les résultats dans ces colonnes, et une mise en forme conditionnelle va permet d’identifier visuellement les écarts importants entre les ventes et les prévisions. L’objectif principal de cette pratique est ainsi 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 de ventes

Une fois que le planificateur (ou le prévisionniste) a finalisé les prévisions pour les promotions et les fonds de rayon, il peut choisir de générer un fichier au format txt. La macro dépose ensuite directement ce fichier dans le répertoire d’arrivée de l’ERP. De plus, elle génère un fichier avec l’extension .txt.ok, afin de signaler que le fichier est prêt pour l’intégration dans l’ERP. Cela permet ainsi d’éviter le risque que l’ERP commence à intégrer un fichier de prévision incomplet.

Processus de génération du fichier de prévisions de ventes

Pour générer ce fichier de prévisions, la macro commence par parcourir les onglets de synthèse des promotions et des fonds de rayon. Ensuite, elle repositionne les données, ligne par ligne et date par date, en fonction de la référence d’article. Enfin, le fichier résultant est structuré 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: Date de la prévision
• Colonne 5: Prévision totale
• Colonne 6: Prévision fonds de rayon

Conclusion du projet

Points positifs

Le projet a été un succès pour plusieurs raisons.

Premièrement, l’équipe a mis en place ce processus dans trois usines différentes, permettant ainsi de tester un standard commun avant son intégration dans l’ERP et les outils du groupe. Cette approche a facilité la standardisation du processus.

Deuxièmement, le développement interne des outils du groupe a été long. Si nous n’avions pas lancé ce projet intermédiaire, il aurait mis en difficulté les usines concernées.

Points négatifs

Cependant, certains points négatifs méritent d’être mentionnés. Le fichier généré est gourmand en ressources mémoire. En conséquence, les utilisateurs ont fréquemment dû fermer toutes leurs applications, voire redémarrer leurs ordinateurs, pour que la macro fonctionne correctement. De plus, les tests de développement étaient rapides (< 1 minute), mais sur le terrain, l’exécution était plus lente, ce qui peut s’expliquer par plusieurs facteurs :

• La différence de puissance des ordinateurs utilisés en usine
• La non-fermeture d’autres fichiers Excel avant le lancement de la macro
• L’ouverture d’autres applications lors de l’exécution
• Le lancement de la macro en fin de journée, lorsque la mémoire RAM était saturée
• L’utilisation de nombreux onglets avec des liens externes, rendant les fichiers plus lourds et lents

Des utilisateurs ont rencontré d’autres incidents mineurs, comme le renommage d’onglets ou des extractions de l’ERP mal réalisées.

Amélioration du processus : vers une application web

Afin de surmonter ces défis, je pense que la mise en place d’une application web semble être la solution idéale. Elle offrirait une grande flexibilité et ne nécessiterait aucune installation locale, un simple navigateur suffisant. Les utilisateurs pourraient importer les extractions de l’ERP et du module de prévisions de ventes grâce à un système de glisser-déposer. Cela garantirait des performances bien supérieures et un projet nettement plus efficace.

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. N’hésitez pas à me laisser des commentaires ou à me contacter via le formulaire de contact pour toute collaboration.

Enfin, sachez que j’ai mené à bien un projet de digitalisation du fichier de prévision en format webapp . Vous pouvez découvrir ce retour d’expérience en suivant ce lien.

Synthèse des principales fonctionnalités

• Gestion différenciée des fonds de rayon avec l’application d’une semaine « type » ou d’un historique et possibilité ensuite de modifier par coefficient un groupe de produits, ou manuellement jusqu’à la ligne produit
• Traitement de 3 extractions issues du système de prévisions
• Intégration des nouvelles promotions et modifications au besoin des prévisions de ventes déjà connues avec conservation de l’historique des modifications
• Génération d’un fichier de relance auprès du service commercial pour toutes les promotions n’ayant pas atteint le niveau de précision maximal
• Génération d’un fichier de prévisions avec un détail (fonds de rayon et promotions) au format spécifique prévu pour l’importation automatique dans l’ERP (format txt)
• Lissage des promotions entre 2 dates avec prise en compte des jours fériés et des Dimanche. Système de statuts de promotions permettant d’identifier par couleur la qualité de la prévision.

Technologies

• VBA
• Userform

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