KHIZHNYAKOVCalcul du point de commande sur Excel : savoir quoi commander et quand
Point de commande et plan d'achats — modèle Excel — Voir le modèle →
Dans un magasin ou un petit entrepôt, le stock pose deux problèmes opposés. Certains produits manquent au moment où les clients les demandent, d'autres dorment pendant des mois et immobilisent de la trésorerie. Souvent, les commandes se font « à l'œil », en regardant les rayons, et dépendent de la personne qui les passe.
Le point de commande est une méthode simple pour sortir de l'approximation : pour chaque article, il indique le niveau de stock à partir duquel il faut recommander. Ce guide présente le calcul, la quantité à commander, un exemple chiffré et les formules Excel pour obtenir chaque matin la liste de ce qu'il faut acheter.
Les trois données de base
Pour chaque article, il vous faut :
- les ventes moyennes par jour, calculées sur une période représentative (par exemple les 90 derniers jours) ;
- le délai de livraison du fournisseur, en jours, entre la commande et la mise en rayon ;
- le stock de sécurité, exprimé en jours de ventes, pour absorber un pic de demande ou un retard de livraison.
La formule du point de commande
Point de commande = ventes moyennes par jour × (délai de livraison + stock de sécurité en jours).
Si le stock actuel est inférieur ou égal à ce seuil, il faut commander aujourd'hui. Sinon, la livraison risque d'arriver après la rupture.
Dans Excel, avec les ventes de la période en C2, le nombre de jours de la période en B1, le délai en D2 et le stock de sécurité en jours en B2 :
- ventes par jour :
=C2/$B$1 - point de commande :
=ARRONDI.SUP(E2*(D2+$B$2);0) - alerte :
=SI(F2<=G2;"Commander";""), où F2 est le stock actuel et G2 le point de commande.
Combien commander ?
Commander juste au point de commande ne suffit pas : il faut que la livraison couvre aussi la période jusqu'à la commande suivante. Une approche simple :
Quantité = ventes par jour × (délai + stock de sécurité + jours entre deux commandes) − stock actuel.
On arrondit ensuite au conditionnement du fournisseur : =PLAFOND(H2;I2), où I2 contient la taille du carton.
Exemple : une épicerie et ses capsules de café
| Donnée | Valeur |
|---|---|
| Ventes sur 90 jours | 540 boîtes |
| Ventes par jour | 6 boîtes |
| Délai de livraison | 5 jours |
| Stock de sécurité | 3 jours |
| Point de commande | 6 × (5 + 3) = 48 boîtes |
| Stock actuel | 40 boîtes |
Le stock (40) est sous le point de commande (48) : il faut commander aujourd'hui. Le fournisseur passe toutes les deux semaines, soit 14 jours entre deux commandes :
Quantité = 6 × (5 + 3 + 14) − 40 = 92 boîtes. Livrées par cartons de 12, on commande 96 boîtes, soit 8 cartons. À 3,20 € la boîte, la commande représente 307,20 €.
Regrouper par fournisseur
Une fois les quantités calculées pour tous les articles, le plan d'achats se résume par fournisseur :
=SOMME.SI(Articles!$J:$J;A2;Articles!$L:$L)
où J contient le fournisseur et L le montant de la commande. Vous voyez immédiatement si une commande atteint le minimum de commande ou le franco de port du fournisseur, et vous pouvez ajuster en ajoutant quelques articles proches de leur seuil.
Choisir le stock de sécurité
Il n'existe pas de valeur universelle. Quelques repères :
- un fournisseur fiable et des ventes régulières : 2 à 3 jours peuvent suffire ;
- un fournisseur qui livre parfois en retard ou des ventes irrégulières : 5 à 7 jours ;
- les produits phares, dont la rupture fait fuir les clients : soyez plus généreux ;
- les articles secondaires : restez serré pour ne pas immobiliser d'argent.
Les erreurs fréquentes
- Calculer les ventes moyennes sur une période de rupture. Si l'article était absent du rayon dix jours, ses ventes sous-estiment la vraie demande.
- Oublier la saisonnalité. Pour un produit saisonnier, prenez comme période d'analyse les semaines comparables de l'année précédente ou les semaines récentes, et non une moyenne annuelle.
- Ignorer les commandes déjà en cours. Ajoutez au stock ce qui est commandé mais pas encore livré, sinon vous commandez deux fois.
- Un délai de livraison théorique. Notez le délai réel constaté, pas celui promis par le fournisseur.
Le modèle prêt à l'emploi
Le modèle Point de commande et plan d'achats (payant) automatise ce calcul pour tout votre assortiment.
- « Paramètres » : stock de sécurité en jours et période d'analyse.
- « Articles » : pour chaque référence, stock, ventes, délai de livraison, prix d'achat et fournisseur. Le point de commande se calcule selon la formule ventes moyennes par jour × (délai de livraison + stock de sécurité).
- « À commander aujourd'hui » : la liste des références à commander, avec la quantité et le montant.
- « Plan d'achats » : la synthèse par fournisseur et le montant des achats.
Pour les produits saisonniers, il suffit de choisir la période d'analyse manuellement. Le fichier fonctionne sous Excel 2007 et versions ultérieures et sous LibreOffice, sans macros, avec un tableau vierge et un exemple fictif.
Questions fréquentes
À quelle fréquence mettre à jour les stocks dans le tableau ?
Idéalement à chaque commande, à partir d'un export de votre caisse ou de votre logiciel de stock. Une mise à jour hebdomadaire convient si vous commandez une fois par semaine.
Que faire pour un nouveau produit sans historique de ventes ?
Partez d'une estimation prudente (les ventes d'un produit comparable, par exemple) et prévoyez un stock de sécurité plus élevé les premières semaines. Remplacez l'estimation par les vraies ventes dès que possible.
Le point de commande convient-il aux produits périssables ?
Oui, mais avec une contrainte supplémentaire : la quantité commandée ne doit pas dépasser ce que vous vendez avant la date limite. Réduisez dans ce cas le nombre de jours entre deux commandes.
Faut-il un inventaire physique ?
Oui, régulièrement. Les écarts entre le stock théorique et le stock réel (casse, vol, erreurs de saisie) faussent le point de commande s'ils ne sont jamais corrigés.
