Formules de gestion des stocks : QEC, stock de sécurité, point de commande
La QEC est =RACINE((2*B2*B3)/B4). L'exemple t-shirts utilise une demande de 1200, un coût de commande de 50 et un coût de possession de 3. Sans stock de sécurité, le point de commande est l'usage quotidien fois le délai : l'exemple café est =B2*B3 avec 10 et 5, soit 50. Le stock de sécurité est =(A2*A4)-(A3*A5). Les casques sont 40, 25, 10 et 7.
Les formules de gestion des stocks dans Excel servent à décider combien commander, quand commander, et combien garder en réserve. Ce guide détaille la quantité économique de commande (QEC, ou EOQ), le point de commande, le stock de sécurité, puis les indicateurs de rotation et un classeur en trois feuilles. Les chiffres ci-dessous sont ceux des exemples de l'article, pas une moyenne de marché.
La quantité économique de commande
La QEC cherche la taille de commande qui équilibre le coût de passer commande et le coût de stocker. Commander trop gros immobilise de la trésorerie et du stockage. Commander trop souvent multiplie les frais de passage.
Trois entrées, sur un an :
- Demande annuelle (D) : unités prévues sur l'année, à partir de la prévision de ventes.
- Coût de commande (K) : coût fixe d'une commande (transport, traitement), pas le prix d'achat des pièces.
- Coût de possession (H) : coût de garder une unité en stock pendant un an.
La formule est la racine de (2 × D × K) / H. Dans Excel : =RACINE((2*B2*B3)/B4). Le nom anglais est SQRT.
Exemple t-shirts du guide. A2 demande annuelle, A3 coût de commande, A4 coût de possession. B2 vaut 1200 (100 t-shirts par mois × 12). B3 vaut 50. B4 vaut 3. La demande, le coût de commande et le coût de possession doivent parler de la même année et de la même monnaie. La formule ne connaît pas une rupture d'approvisionnement : elle suppose une demande stable et un coût de possession proportionnel au stock.
Le point de commande
La QEC dit combien commander. Le point de commande dit quand. Sans stock de sécurité, c'est l'usage quotidien moyen multiplié par le délai en jours.
Exemple café du guide. A2 est l'usage quotidien moyen, A3 le délai en jours. B2 vaut 10 (dix sachets par jour). B3 vaut 5 (cinq jours de livraison). En B5 : =B2*B3, soit 50. Quand le stock tombe à 50 sachets, vous commandez. La livraison arrive au moment où les 50 sachets s'écoulent, si l'usage et le délai restent ceux que vous avez saisis.
Avec un stock de sécurité, le point de commande devient : usage quotidien moyen × délai + stock de sécurité. L'usage quotidien moyen se calcule avec MOYENNE sur l'historique des ventes. Le nom anglais est AVERAGE. N'utilisez pas un délai « à peu près » si le fournisseur a déjà mis dix jours : le point de commande serait trop bas.
Le stock de sécurité
Le point de commande couvre un mois normal. Le stock de sécurité couvre un pic de ventes ou un retard fournisseur. La méthode du guide prend le pire cas connu moins le cas moyen.
Quatre cellules, exemple casques :
- A2 : ventes quotidiennes maximales, 40
- A3 : ventes quotidiennes moyennes, 25
- A4 : délai maximal en jours, 10
- A5 : délai moyen en jours, 7
Formule : (ventes max × délai max) − (ventes moyennes × délai moyen), soit =(A2*A4)-(A3*A5). Le calcul donne (40×10) − (25×7) = 400 − 175 = 225 unités. Ce stock s'ajoute au point de commande. Il ne se rajoute pas une seconde fois si le point de commande l'inclut déjà.
Une autre écriture du guide, pour un tableau nommé, est (MAX des unités − MOYENNE des unités) × délai. Ce n'est pas le même calcul que les quatre cellules ci-dessus. Choisissez une méthode et tenez-la. Les deux ne s'additionnent pas.
Le stock de sécurité se périme si les ventes changent de régime. Le guide suggère de le revoir quand la demande ou le fournisseur change, plutôt que de figer le 225 pour l'année.
Rotation et durée de stock
La QEC et le point de commande règlent les commandes. Ils ne disent pas si le stock se transforme en trésorerie. Deux indicateurs du guide :
La rotation = coût des ventes / stock moyen. Le coût des ventes est sur le compte de résultat. Le stock moyen est la moyenne de la valeur de stock sur la période. Une rotation haute veut dire que le stock tourne. Une rotation basse peut vouloir dire trop de stock ou des ventes faibles. Comparez des produits entre eux, pas un produit isolé sans référence.
La durée de stock = (stock moyen / coût des ventes) × nombre de jours de la période. Le guide l'appelle aussi durée d'écoulement. Une durée plus basse veut dire un passage plus rapide en trésorerie. Utilisez des montants des deux côtés, ou des unités des deux côtés. Mélanger un stock en unités et un coût des ventes en euros fausse le ratio.
Dans la feuille, le coût des ventes et le stock moyen sont des cellules, pas des nombres tapés dans la formule. =B2/B3 pour la rotation si B2 est le coût des ventes et B3 le stock moyen. La durée reprend ce résultat.
Un classeur en trois feuilles
Le guide sépare les rôles pour que le tableau de bord se mette à jour sans que vous retapiez les formules.
- Feuille de données : ventes quotidiennes, réceptions, délais fournisseurs. Une ligne par mouvement. C'est la source.
- Feuille de calcul : QEC, point de commande, stock de sécurité, rotation. Chaque formule pointe vers la feuille de données ou vers un tableau. Exemple du guide pour l'usage moyen :
=MOYENNE(Données!C:C), ou=MOYENNE(Ventes[Unités])si les ventes sont dans un tableau nommé Ventes. - Feuille de présentation : les indicateurs et un graphique du stock actuel contre le point de commande, pour les produits que vous suivez. Vous ne saisissez pas de chiffres ici.
Le point de commande lié s'écrit à partir des cellules calculées, pas d'une constante. Quand une vente est ajoutée dans la feuille de données, MOYENNE change, le point de commande change, le graphique suit. Un nombre collé en dur dans le tableau de bord ne bouge pas.
Le guide propose aussi de demander à un agent, dans le classeur ouvert, de marquer en rouge les articles sous le point de commande, ou de construire le graphique du stock contre le point de commande. Elyx peut le faire sur le fichier ouvert. Il ne passe pas la commande fournisseur.
Questions fréquentes du guide
Une petite structure peut-elle s'en servir ? Oui. Le guide conseille de commencer par le point de commande du produit qui se vend le plus, dans une feuille simple, pour éviter la rupture. La QEC et le stock de sécurité viennent ensuite. Excel suffit pour ce premier pas. Un logiciel spécialisé n'est pas le préalable.
D'où viennent les erreurs ? Surtout des entrées, pas de la syntaxe. Une demande annuelle devinée, un coût de possession qui oublie le stockage, un délai moyen alors que le fournisseur est souvent en retard : la formule est juste, le résultat ne l'est pas. Le guide insiste pour mesurer ces entrées sur l'historique, puis les revoir.
Faut-il un stock de sécurité en plus du délai moyen ? Oui si la demande ou le délai bougent. Un point de commande calculé seulement sur la moyenne tombe en rupture au premier pic ou au premier retard. C'est le rôle des 225 unités de l'exemple casques, pas un pourcentage universel à appliquer à tout le catalogue.
Elyx est un agent dans Excel. Vous pouvez lui demander d'ajouter ces formules sur la feuille ouverte. L'essai dure 7 jours, sans carte bancaire. Ensuite Plus est à 12 €/mois et Pro à 29 €/mois. Il n'y a pas de forfait gratuit permanent.
Commencez l'essai gratuit de 7 jours, sans carte bancaire.
Réponses directes
Quelles sont les formules de gestion des stocks dans Excel ?
La QEC fixe la taille de commande. Le point de commande dit quand commander. Le stock de sécurité est le coussin. La rotation est le coût des ventes divisé par le stock moyen.
Comment calculer le stock de sécurité dans Excel ?
=(A2*A4)-(A3*A5) : ventes max fois délai max, moins ventes moyennes fois délai moyen. Les cellules de l'exemple casques sont 40, 25, 10 et 7.
Quelle est la formule de la QEC dans Excel ?
=RACINE((2*B2*B3)/B4). Le nom anglais est SQRT. B2 est la demande annuelle, B3 le coût d'une commande, B4 le coût annuel de possession d'une unité. L'exemple t-shirts est 1200, 50 et 3.
Dans Excel, demandez à Elyx : « Ajoute la QEC, le stock de sécurité et le point de commande à partir des cellules de cette feuille. » Elyx le fait dans le classeur. Essai de 7 jours, sans carte.
Commencer l'essai de 7 jours