KHIZHNYAKOVSuivi des dates limites de consommation dans Excel
Suivi des dates limites de consommation en Excel : lots et remises — Modèle Excel — Voir le modèle →
Dans un magasin, un café, une pharmacie ou un entrepôt, la date limite est une horloge silencieuse. Un lot de yaourts oublié derrière les autres, un carton de médicaments en fond de réserve : on s'en aperçoit quand il est trop tard, et la perte se chiffre directement en euros.
Un tableau Excel bien pensé transforme cette surveillance en routine : il calcule les jours restants de chaque lot, lui attribue un statut, propose la remise adaptée et dresse la liste du jour. Voici la méthode.
Suivre des lots, pas des produits
Un même produit peut avoir plusieurs dates limites : un lot reçu en mars, un autre en avril. La bonne unité de suivi est donc le lot. Chaque ligne contient :
- le produit et sa catégorie ;
- le numéro de lot ou de livraison ;
- la quantité en stock ;
- le prix d'achat et le prix de vente ;
- la date limite.
Pas besoin de code-barres : un nom de produit et un numéro de livraison suffisent pour retrouver un lot en rayon.
Calculer les jours restants et le statut
Les jours restants sont la différence entre la date limite et aujourd'hui :
=F2-AUJOURDHUI()
Définissez ensuite deux seuils, par exemple 14 jours pour « Bientôt » et 3 jours pour « Critique » :
=SI(G2<0;"Périmé";SI(G2<=3;"Critique";SI(G2<=14;"Bientôt";"OK")))
Placez ces seuils dans des cellules de paramètres, pas dans la formule : quand vous voulez passer de 14 à 10 jours pour les produits frais, une seule cellule à modifier.
La remise pour solder
Plus la date approche, plus il faut baisser le prix pour écouler le stock. Associez à chaque statut une remise (par exemple 0 % pour « OK », 20 % pour « Bientôt », 50 % pour « Critique ») et calculez le prix remisé :
=H2*(1-RECHERCHEV(statut;Paramètres!A2:B5;2;FAUX))
Exemple : un lot de 30 pots de crème, prix d'achat 1,20 €, prix de vente 2,50 €, date limite dans 3 jours. Le statut est « Critique », la remise de 50 % ramène le prix à 1,25 €. Vous vendez tout à marge réduite plutôt que de jeter 30 × 1,20 € = 36 € de marchandise.
Quantifier le risque
Deux chiffres aident à décider :
- valeur à risque = quantité × prix d'achat des lots « Bientôt » et « Critique » :
=SOMME.SI.ENS(valeur;statut;"Critique"); - valeur perdue = quantité × prix d'achat des lots « Périmé ».
| Statut | Lots | Valeur d'achat |
|---|---|---|
| Périmé | 2 | 72 € |
| Critique | 4 | 180 € |
| Bientôt | 9 | 420 € |
Suivre ce tableau semaine après semaine montre si vos commandes sont trop importantes ou si certains produits se vendent mal.
Une liste « à traiter aujourd'hui »
Le plus utile au quotidien : une liste qui n'affiche que les lots à solder ou à retirer. Avec Excel 365, =FILTRE(Lots!A2:J301;(Lots!H2:H301="Critique")+(Lots!H2:H301="Périmé")) fait ce travail ; avec une version plus ancienne, une colonne d'aide numérotée suivie d'un INDEX/EQUIV aboutit au même résultat. Imprimez-la le matin pour le responsable de rayon.
Les erreurs à éviter
- Suivre uniquement les produits, pas les lots.
- Oublier de retirer les lots vendus ou jetés : le tableau doit refléter le stock réel.
- Fixer des seuils identiques pour tous les produits : une salade et une conserve n'ont pas la même durée de vie.
- Saisir les dates en texte (« 12.03 » sans année) : Excel ne pourra pas calculer.
- Ne pas regarder le tableau : une revue quotidienne de cinq minutes suffit.
Un modèle de suivi des dates limites prêt à l'emploi
Le modèle « Suivi des dates limites de consommation » met ces mécanismes en place dans un fichier payant, sans macros, compatible Excel 2007 et versions ultérieures et LibreOffice, en français.
- « Paramètres » : seuils « Bientôt » et « Critique » (14 et 3 jours par défaut) et remise pour chaque statut.
- « Lots » : produit, catégorie, lot, quantité, prix d'achat et de vente, date limite ; jours restants, statut, remise et prix remisé sont calculés automatiquement (jusqu'à 300 lots).
- « À traiter aujourd'hui » : liste automatique des lots à solder ou à retirer, avec la remise et le montant à risque.
- « Synthèse » : nombre de lots périmés, critiques et bientôt à échéance, valeur des produits à risque, graphique.
Il s'adresse aux propriétaires de magasins, responsables d'entrepôt, vendeurs de produits alimentaires, cafés et pharmacies. Les seuils sont des paramètres à vérifier ; le calcul est indicatif.
FAQ
Quelle différence entre DLC et DDM ?
La date limite de consommation (DLC) concerne les produits très périssables : au-delà, le produit ne doit plus être consommé ni vendu. La date de durabilité minimale (DDM) indique une qualité optimale, mais le produit peut rester consommable après. Adaptez vos seuils et vos remises selon le type de date.
À quelle fréquence contrôler les dates ?
Une fois par jour pour les produits frais, une fois par semaine pour les produits secs. Le tableau vous évite de tout vérifier en rayon : vous traitez seulement la liste du jour.
Comment éviter le gaspillage au-delà des remises ?
Pensez au don à des associations, à la transformation (fruits trop mûrs en compote) ou à la révision de vos quantités commandées. Le suivi des pertes chiffrées sur plusieurs mois vous montre quoi réduire.
Peut-on suivre aussi les médicaments ou les produits d'hygiène ?
Oui, tant que chaque lot a une date limite. Adaptez les seuils et les catégories et vérifiez les règles de retrait applicables à votre activité.
