KHIZHNYAKOVGestion de stock sur Excel : entrées, sorties et alerte de réapprovisionnement
Gestion de stock dans Excel — modèle — Voir le modèle →
Pour un petit magasin, un atelier ou un bureau, un logiciel de gestion de stock complet est souvent disproportionné. Un tableur suffit, à condition de respecter une règle : on ne modifie jamais le stock à la main. On enregistre des mouvements, et le stock se calcule.
Voici une méthode qui tient en trois feuilles, avec les formules en français, pour savoir à tout moment ce qu'il y a en rayon, ce que cela vaut et ce qu'il faut recommander.
Trois feuilles, une règle
Articles
Une ligne par référence : code, désignation, groupe, unité, prix d'achat unitaire, stock minimal. Le code doit être unique et ne jamais changer.
Entrées et sorties
Deux journaux séparés. Chaque ligne contient la date, le code article et la quantité. Dans la colonne code, utilisez une liste déroulante alimentée par la feuille Articles pour éviter les fautes de frappe. La désignation s'affiche ensuite automatiquement :
=SIERREUR(RECHERCHEV(B2;Articles!$A:$B;2;FAUX);"")
Le calcul du stock
Dans la feuille Articles :
=SOMME.SI(Entrées!$B:$B;A2;Entrées!$C:$C)-SOMME.SI(Sorties!$B:$B;A2;Sorties!$C:$C)
La valeur du stock est ce stock multiplié par le prix d'achat. Pour obtenir le statut :
=SI(F2<0;"Erreur";SI(F2=0;"Rupture de stock";SI(F2<=E2;"À commander";"OK")))
Exemple chiffré
Article « Cartouche d'encre noire » : prix d'achat 18 €, stock minimal 4.
| Mouvement | Quantité |
|---|---|
| Entrée du 3 janvier | 10 |
| Sortie du 12 janvier | 3 |
| Sortie du 20 janvier | 4 |
Stock : 10 − 3 − 4 = 3. Valeur : 3 × 18 = 54 €. Comme 3 est inférieur au minimum de 4, le statut passe à « À commander ». Si le fournisseur livre en une semaine et que vous consommez deux cartouches par semaine, le seuil de 4 est correct ; sinon, relevez-le.
Fixer le stock minimal
Un stock minimal n'est pas un chiffre au hasard. Une règle simple :
stock minimal = consommation moyenne par jour × délai de livraison en jours + stock de sécurité
Si vous écoulez 6 rames de papier par semaine (environ 1 par jour), que le délai est de 4 jours et que vous voulez 3 rames de marge : minimum = 1 × 4 + 3 = 7.
Les erreurs classiques
- Corriger le stock à la main : vous perdez l'historique et la source de l'écart.
- Des codes qui changent ou qui existent en double.
- Oublier de saisir les sorties : le stock théorique grimpe, le stock réel baisse. Saisissez les sorties au fil de l'eau, pas en fin de mois.
- Ne jamais faire d'inventaire physique : comptez au moins une fois par an, et saisissez l'écart comme un mouvement.
- Traiter les casses et mises au rebut comme des oublis : enregistrez-les comme une sortie, avec un commentaire.
Modèle de gestion de stock prêt à l'emploi
Le modèle Gestion de stock est un fichier Excel payant avec un tableau vierge et un exemple rempli de données fictives. Il ne nécessite aucune macro, fonctionne sous Excel 2007 et versions ultérieures et sous LibreOffice, et il est en français.
- Paramètres : groupes d'articles, unités de mesure, nom de l'organisation.
- Articles : jusqu'à 300 articles avec stock minimal, prix, stock, valeur et statut.
- Entrées et Sorties : 1 000 lignes chacune, code choisi dans une liste, désignation automatique, contrôle des sorties supérieures au stock.
- Synthèse : valeur des stocks par groupe, nombre de références à commander, mouvements par mois et graphique.
Une sortie supérieure au stock s'affiche en rouge avec une indication, et l'article passe au statut « Rupture de stock ». Le calcul est indicatif, les seuils sont des paramètres à vérifier. Le modèle s'adresse aux magasiniers, aux gérants de boutique, aux petites unités de production et aux bureaux.
FAQ
Excel suffit-il pour un stock ?
Pour quelques centaines de références et un ou deux utilisateurs, oui. Au-delà, ou si plusieurs entrepôts sont en jeu, un logiciel dédié devient pertinent.
Comment suivre plusieurs entrepôts ?
La façon la plus simple est un fichier par site. Le modèle présenté ici est conçu pour un seul lieu de stockage.
Comment valoriser le stock ?
La méthode la plus simple consiste à multiplier la quantité par le dernier prix d'achat. Pour un suivi comptable précis (coût moyen pondéré, premier entré premier sorti), rapprochez-vous de votre comptable.
À quelle fréquence faire l'inventaire ?
Au moins une fois par an, et plus souvent pour les articles chers ou à forte rotation. Comptez par zone, un peu chaque semaine, plutôt que tout d'un coup.
