Le suivi des DDM courtes et de la couverture de stock se construit dans Excel à partir d'une base par lot, avec trois formules clés : la DDM la plus proche par référence (MIN.SI.ENS), la quantité en stock (SOMME.SI.ENS) et la couverture en mois (stock divisé par les ventes moyennes mensuelles). Un statut d'alerte, calculé sur ces deux indicateurs, permet ensuite de savoir en un coup d'œil quelles références écouler en priorité.
Ce guide suit la construction réelle d'un tel fichier, telle qu'on la rencontre dans une entreprise qui gère des produits en DDM (date de durabilité minimale), comme les surgelés. Définitions, formules, seuils, cas limites et vérification, chaque section répond à une question précise que se pose un responsable achats ou approvisionnement qui met à jour son fichier toutes les deux semaines.
Un assistant IA comme Claude accélère l'écriture des formules et la documentation des règles. Il ne remplace ni la définition des seuils métier ni la vérification avant diffusion, deux étapes qui restent sous contrôle humain à chaque cycle.
DDM, DLC, DDM courte : de quoi parle-t-on
La DLC (date limite de consommation) est une limite impérative. Elle concerne les produits frais très périssables (viandes, poissons, plats cuisinés réfrigérés) et interdit la vente passée cette date. La DDM (date de durabilité minimale) indique qu'un produit peut avoir perdu une partie de ses qualités gustatives, sans devenir dangereux, selon la fiche officielle d'economie.gouv.fr.
Un produit en DDM reste légalement vendable après la date affichée. C'est ce qui rend le sujet moins simple qu'il n'y paraît, un lot dont la DDM est passée n'est pas un lot à jeter, mais un lot que les distributeurs refusent souvent malgré tout, par politique commerciale interne.
Les surgelés sont typiquement des produits en DDM, présentée sous la forme « à consommer de préférence avant le... ». Une entreprise de distribution de surgelés que nous avons accompagnée gère ainsi des dizaines de références dont chaque lot porte sa propre DDM.
Ce qu'on appelle une DDM courte
Il n'existe pas de définition réglementaire d'une DDM courte. Dans la pratique, un lot est considéré comme DDM courte quand sa date tombe dans moins de trois mois (90 jours), un seuil que chaque entreprise ajuste selon ses délais de vente habituels.
Un lot en DDM courte doit être écoulé vite, souvent en promotion. Sans action, le lot dépasse sa date, le distributeur le refuse à réception, et la perte devient sèche : un stock invendable, valorisé au bilan, sans acheteur.
Le lot, l'unité de base du suivi
Un lot est une fabrication identifiée par un numéro, avec sa propre DDM. Une référence produit en stock correspond en général à plusieurs lots, donc à plusieurs DDM différentes selon la date de fabrication de chaque entrée.
C'est pour cela qu'un suivi fiable ne peut pas se contenter d'une DDM par référence. Il faut suivre la DDM la plus proche parmi tous les lots d'une même référence, celle qui donne l'urgence réelle, quitte à ce que d'autres lots de la même référence aient une date plus lointaine.
Couverture, rotation, surstock, sous-stockage : les définitions qui évitent les malentendus
La couverture de stock exprime le nombre de mois que le stock actuel permet de tenir au rythme des ventes. Elle se calcule en divisant le stock disponible par les ventes moyennes mensuelles (VMM) de la référence.
Un exemple simple, dix colis en stock et trois colis vendus par mois en moyenne donnent une couverture de 3,3 mois. Sans aucune vente sur la référence, la couverture devient infinie, ce qui est un signal en soi et pas une erreur de calcul.
Rotation, l'inverse théorique de la couverture
La rotation désigne, en théorie, le nombre de fois où le stock se renouvelle sur une année. C'est l'inverse de la couverture, ramené à l'échelle annuelle plutôt que mensuelle.
Le piège fréquent dans les fichiers existants, une colonne nommée « rotation » calcule en réalité une couverture (stock divisé par ventes moyennes), sans que personne ne l'ait renommée depuis sa création. Avant de comparer un chiffre entre deux fichiers ou deux équipes, il faut ouvrir la formule et vérifier ce qu'elle calcule réellement, pas se fier au nom de la colonne.
Surstock et sous-stockage, les deux dérives à surveiller
Une couverture trop longue signale un surstock, de l'argent immobilisé sur des références qui ne se vendent pas assez vite, avec un risque de DDM courte qui se superpose souvent au surstock. Une couverture trop courte signale un sous-stockage, avec un risque de rupture si la commande de réapprovisionnement n'est pas passée à temps.
Le temps idéal, ou couverture cible, est le nombre de mois de stock que l'entreprise souhaite garder en permanence sur une référence. La quantité à commander se calcule approximativement ainsi, VMM multipliée par la couverture cible, moins le stock disponible, en tenant compte du stock en transit (payé, pas encore arrivé) et des commandes clients déjà engagées.
À retenir
Attention aux unités mélangées dans une même colonne, colis, UVC (unité de vente consommateur), colisage, palette. Une couverture calculée en mélangeant colis et UVC donne un chiffre qui a l'air juste et qui ne l'est pas. La VMM et le stock doivent être exprimés dans la même unité avant toute division.
Fixer ses seuils d'alerte
Il n'existe pas de seuil universel pour la DDM courte ou la couverture excessive. Chaque entreprise fixe les siens selon ses délais de vente, sa saisonnalité et la durée de vie de ses produits. L'important est que ces seuils soient écrits noir sur blanc et connus de toute l'équipe qui lit le fichier.
Un exemple de règles observées sur le terrain, à ajuster à votre propre activité.
- Critique : DDM inférieure à 3 mois, ou couverture supérieure à 6 mois, ou couverture infinie (aucune vente)
- Vigilance : DDM entre 3 et 6 mois, ou couverture entre 3 et 6 mois
- Sous-stockage : couverture inférieure à 1 mois, risque de rupture, une commande doit partir
- OK : toutes les autres situations
Ces règles se traduisent en Excel par des SI imbriqués ou une fonction SI.CONDITIONS, plus lisible dès qu'on dépasse trois ou quatre cas. Une mise en forme conditionnelle sur la colonne de statut rend ensuite le fichier lisible d'un coup d'œil, sans qu'il soit nécessaire d'ouvrir chaque ligne.
| Indicateur | Formule Excel | Seuil d'exemple |
|---|---|---|
| DDM la plus proche par référence | MIN.SI.ENS(DDM, référence, réf. courante) | Critique sous 3 mois |
| Jours restants avant DDM | DDM moins date de la photo de stock | Négatif = déjà périmé |
| Quantité par référence | SOMME.SI.ENS(quantité, référence, réf. courante) | - |
| Couverture en mois | SIERREUR(stock / VMM ; "infini") | Critique au dessus de 6 mois |
| Valeur exposée | Quantité en alerte multipliée par le coût de revient | - |
La base par lot et les formules qui portent le suivi
La structure la plus robuste n'est pas un onglet par date de photo de stock, mais une base unique par lot et par date de photo, sous forme de tableau structuré. Chaque nouvelle photo de stock ajoute des lignes à la même table plutôt que de créer un nouvel onglet à recopier.
Les colonnes calculées indispensables
Sur cette base, quatre colonnes calculées portent l'essentiel du suivi.
- Jours restants avant DDM : la DDM du lot moins la date de la photo de stock
- DDM la plus proche par référence : MIN.SI.ENS(colonne DDM, colonne référence, référence de la ligne)
- Quantité totale par référence : SOMME.SI.ENS(colonne quantité, colonne référence, référence de la ligne)
- Couverture : quantité totale divisée par la VMM de la référence, avec SIERREUR pour gérer l'absence de vente
Les ventes moyennes mensuelles, une moyenne pondérée plutôt que brute
Une VMM calculée sur une moyenne brute lisse trop les à-coups récents. Une pratique de terrain observée pondère les périodes récentes plus lourdement, par exemple 40 % sur la moyenne des 3 derniers mois et 60 % sur la moyenne des 6 derniers mois, pour réagir plus vite à une baisse de vente tout en gardant une base stable.
La valorisation, au coût de revient réel
La valeur exposée d'un stock en DDM courte ou en surstock se calcule en multipliant les quantités concernées par le coût de revient, c'est-à-dire le coût rendu entrepôt (achat, transport, douane, stockage), souvent un coût moyen unitaire pondéré (CMUP) déjà calculé par l'ERP. Ce coût de revient diffère du simple prix d'achat, et c'est lui qui donne le montant réellement en jeu.
Les cas limites qui font planter une formule si on ne les traite pas
Quatre situations reviennent systématiquement sur un fichier de suivi de stock et cassent les formules si elles ne sont pas anticipées.
- Ventes nulles : une division par zéro renvoie #DIV/0!. SIERREUR encadre la formule de couverture et affiche « infini » plutôt qu'une erreur
- Référence absente de la liste produits : une recherche RECHERCHEX sur une référence inconnue renvoie #N/A, à traiter aussi avec SIERREUR pour ne pas casser la ligne entière
- Codes article tantôt en texte, tantôt en nombre : la même référence saisie une fois en texte et une fois en nombre casse silencieusement les SOMME.SI.ENS et MIN.SI.ENS, qui ne trouvent pas de correspondance
- Lots sans DDM saisie : MIN.SI.ENS ignore les cellules vides, donc le lot sans date disparaît du suivi sans prévenir. Et si aucun lot d'une référence n'a de DDM, la formule renvoie zéro, qu'Excel lit comme une date de 1900 : fausse alerte critique assurée
Traiter ces cas limites en amont, dans la structure du fichier, coûte moins de temps que de les découvrir un par un pendant la diffusion de la synthèse.
Se faire aider par Claude sans perdre la main
Un assistant comme Claude, via son extension dans Excel, aide à écrire une formule MIN.SI.ENS imbriquée ou à documenter une règle métier. Il va plus vite que de chercher la syntaxe exacte dans l'aide Excel, à condition de lui donner une consigne complète.
Une consigne complète, avec périmètre et exemple
Une consigne qui fonctionne précise le résultat attendu, le périmètre exact (onglet, ligne d'en-tête, plage), les règles métier et un exemple, en demandant la formule plutôt que la valeur brute. Trois exemples réalistes.
- « Dans l'onglet Stock, ligne d'en-tête ligne 1, colonne référence en B, colonne DDM en E, donne-moi la formule en colonne F qui calcule la DDM la plus proche pour chaque référence, avec la syntaxe de la formule, pas juste le résultat »
- « Dans l'onglet Stock, colonne stock en D et colonne VMM en G, écris la formule de couverture en mois en colonne H, qui affiche "infini" si la VMM est à zéro plutôt qu'une erreur de division »
- « Sur la base des seuils critique sous 3 mois de DDM ou au dessus de 6 mois de couverture, vigilance entre 3 et 6 mois, sous-stockage sous 1 mois de couverture, écris une formule SI.CONDITIONS pour la colonne statut en I »
Le piège du périmètre non précisé
La question « quelle est la valeur de mon stock ? », posée sans préciser l'onglet ni la date de photo, reçoit une réponse assurée qui peut être fausse. Si le classeur contient plusieurs onglets correspondant à des dates de photo différentes, l'assistant en choisit un sans le signaler.
Ce n'est pas une hallucination au sens strict, c'est une question posée sans périmètre. La reprise qui fonctionne, « sur la base Stock, à la photo du 15 septembre, somme de la colonne valeur exposée, cite la cellule ou la plage utilisée ». Notre article sur Claude pour Excel détaille plus largement ce que l'add-in gère bien et ses limites avant de généraliser son usage.
Vérifier avant de généraliser
Une formule proposée par Claude se teste d'abord sur quelques lignes, jamais appliquée directement à l'ensemble du fichier. Deux références contrôlées à la main, ou par un filtre manuel indépendant, avant de considérer la formule comme fiable sur toute la base.
La synthèse à envoyer au commerce
Le fichier complet, avec toutes ses colonnes et tous ses lots, ne se diffuse pas tel quel au commerce toutes les deux semaines. Un flux d'informations trop gros n'est pas lu, et les références qui comptent se noient dans celles qui n'ont pas bougé.
La synthèse utile reste sélective, les références en statut critique et vigilance, la valeur en jeu pour chacune, et une phrase d'action (à mettre en promotion, à relancer un client, à commander). Le détail complet reste disponible en annexe, pour qui veut vérifier une ligne précise.
Ce qui peut tourner seul, et ce qui reste sous contrôle
Le calcul des indicateurs suivant une formule déjà validée et le rafraîchissement du fichier à chaque nouvelle photo de stock peuvent tourner sans supervision constante. La définition des seuils, les exceptions (produit en fin de vie, lot bloqué qualité) et la lecture d'un écart inhabituel avant diffusion restent des décisions métier, à chaque cycle.
Vérifier avant de diffuser
Avant d'envoyer la synthèse, deux contrôles simples évitent la plupart des erreurs qui passeraient sinon inaperçues jusqu'au commerce.
Recalculer la valeur exposée totale par un filtre manuel indépendant du tableau croisé, puis la comparer à la période précédente. Un écart brutal et inexpliqué signale presque toujours une erreur de source (un onglet dupliqué, une référence en double), rarement une vraie rupture de tendance. Notre article sur la vérification d'une réponse de Claude détaille cette méthode de recoupement, applicable à tout chiffre produit avec l'aide d'un assistant IA.
C'est cette méthode, de la base par lot jusqu'à la synthèse fiable, que nous transmettons dans notre formation IA pour les achats et la supply chain, construite sur les fichiers réels de vos équipes.
Foire aux questions
Quelle est la différence entre DDM et DLC ?
La DLC (date limite de consommation) est une limite impérative, apposée sur les produits frais très périssables. Passée cette date, le produit ne peut plus être vendu. La DDM (date de durabilité minimale) indique qu'un produit peut avoir perdu une partie de ses qualités mais reste consommable sans risque, et donc légalement vendable après la date. Les surgelés sont en DDM, ce qui explique pourquoi les distributeurs refusent malgré tout les lots trop proches de leur DDM.
Comment calculer la couverture de stock en mois dans Excel ?
En divisant la quantité en stock par les ventes moyennes mensuelles (VMM) de la référence. Avec 10 colis en stock et 3 colis vendus par mois en moyenne, la couverture est de 3,3 mois. Il faut protéger la formule contre les ventes nulles avec SIERREUR ou un test SI, sinon Excel renvoie #DIV/0!.
Comment repérer la DDM la plus proche quand une référence a plusieurs lots ?
Avec MIN.SI.ENS, qui renvoie la date la plus proche parmi tous les lots d'une même référence. C'est cette DDM la plus proche qu'il faut suivre pour l'alerte, pas la DDM moyenne ni celle du dernier lot entré, puisqu'un seul lot en DDM courte suffit à rendre toute la référence sensible.
Quels seuils fixer pour une alerte DDM courte ou surstock ?
Il n'existe pas de seuil universel, chaque entreprise fixe le sien selon ses délais de vente. Un repère de terrain courant, DDM courte sous 3 mois et couverture supérieure à 6 mois passent en statut critique, entre 3 et 6 mois en vigilance, en dessous de 1 mois de couverture le risque devient la rupture plutôt que le surstock.
Pourquoi une colonne rotation calcule-t-elle parfois une couverture ?
Parce que rotation et couverture sont deux mesures inverses souvent confondues dans les fichiers existants. La rotation compte le nombre de fois où le stock se renouvelle sur une période, la couverture compte le nombre de mois que le stock permet de tenir. Une colonne nommée rotation qui divise le stock par les ventes moyennes calcule en réalité une couverture, il faut vérifier la formule avant de comparer des chiffres entre fichiers.
Claude peut-il fiabiliser un fichier de suivi de stock sans erreur ?
Il aide à écrire les formules et à documenter les règles, mais il ne connaît ni le périmètre exact de votre fichier ni vos seuils métier si vous ne les précisez pas. Une question posée sans préciser l'onglet ni la date de photo du stock reçoit une réponse assurée mais potentiellement fausse, il faut toujours vérifier deux références à la main avant de diffuser un chiffre.
Pour aller plus loin
- Pourquoi votre tableau Excel ne se met pas à jour : passer d'un onglet par photo de stock à une base unique qu'on réalimente en collant l'extraction.
- Construire un tableau de bord supply chain avec Claude : la méthode complète pour aller du fichier de stock nettoyé jusqu'au reporting consolidé.
- Claude pour Excel, ce que l'add-in gère bien et ses limites : pour cadrer l'usage de l'assistant IA sur des fichiers métier.
- IA en transport et logistique pour PME : panorama des cas d'usage possibles au delà du suivi de stock.
- Intégrer des tarifs fournisseurs avec l'IA sans risque : la consolidation multi-sources appliquée aux catalogues de prix, côté distributeurs B2B.
