Piloter un stock au pointage manuel, c'est immobiliser du cash sur des références dormantes tout en tombant en rupture sur celles qui tournent vite. Dans cet exercice, on va voir ensemble comment construire la base opérationnelle qui affiche un statut en temps réel et calcule, pour chaque article, ce qu'il reste à commander.
L'objectif est de faire décider le tableau à ta place, en traduisant des seuils en statut, en remontant les infos fournisseur automatiquement et en repérant l'article le plus proche de la rupture. Une fois cette fiche en place, le réflexe se prolonge vers le calcul de rotation et tient sur un catalogue de cinquante comme de cinq cents références !
Ce que tu vas construire
Afficher en temps réel le statut de chaque article : OK, À commander, Surstock ou Rupture.- Calculer automatiquement la quantité à commander pour revenir au niveau maxi avec MAX.
- Repérer la référence la plus proche de la rupture grâce au ratio stock sur mini avec MIN.
- Récupérer les coordonnées fournisseur en une formule pour préparer les commandes.
- Ajouter le taux de rotation (consommation annuelle sur stock moyen) une fois la fiche en place.
À connaître avant de commencer
- Savoir recopier une formule le long d'une liste d'articles.
- Savoir figer une plage avec les $ pour verrouiller la table des fournisseurs.
- Connaître RECHERCHEV au moins de nom : on s'en sert pour ramener les infos fournisseur.
Voici les données de départ de cet exercice. Copie-les ou télécharge le fichier Excel, puis entraîne-toi avant de regarder le corrigé.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Code | Article | Stock | Mini | Maxi | Délai (j) | Fournisseur |
| 2 | REF-001 | Vis M6x20 | 1 500 | 500 | 3 000 | 5 | Acier Plus |
| 3 | REF-002 | Boulon M8 | 200 | 300 | 2 000 | 7 | Fix Pro |
| 4 | REF-003 | Rondelle plate | 4 500 | 1 000 | 5 000 | 3 | Acier Plus |
| 5 | REF-004 | Écrou M6 | 800 | 400 | 2 500 | 5 | Acier Plus |
| 6 | REF-005 | Tige filetée M10 | 50 | 100 | 800 | 10 | Métal Export |
| 7 | REF-006 | Joint torique 25mm | 3 200 | 500 | 4 000 | 4 | Caoutchouc SA |
| 8 | REF-007 | Plaque alu 2mm | 120 | 80 | 500 | 12 | Métal Export |
| 9 | REF-008 | Goupille élastique | 6 000 | 2 000 | 8 000 | 3 | Fix Pro |
| 10 | REF-009 | Ressort compression | 350 | 200 | 1 500 | 8 | Spring Tech |
| 11 | REF-010 | Entretoise nylon | 90 | 150 | 1 000 | 6 | Plasti Indus |
Exercice guidé
Coche chaque étape au fur et à mesure. Tente-la dans ton fichier, puis déplie le corrigé.
Utilise SI imbriqué pour afficher un statut : 'Rupture' si stock = 0, 'A commander' si stock < mini, 'OK' si entre mini et maxi, 'Surstock' si stock > maxi. Le statut se met à jour en temps réel.
=SI(C2=0;"Rupture";SI(C2<D2;"A commander";SI(C2>E2;"Surstock";"OK")))Résultat attendu : "A commander" pour REF-002 (200 < 300), "OK" pour REF-001, "Surstock" si le stock dépasse le maxi.
Attention
- L'ordre des SI imbriqués compte : teste d'abord la rupture (cas le plus critique), puis le seuil bas, puis le seuil haut.
Astuces pour aller plus loin
Rotation faible = argent bloqué
Un taux de rotation inférieur à 2 signifie que tu renouvelles ce stock moins de deux fois par an. Commence par les 10% d'articles les moins rotatifs : c'est là que tu libères le plus de trésorerie en réduisant les commandes ou en soldant les surplus.
Ajuste le seuil mini selon la variabilité
Un délai fournisseur qui fluctue d'une semaine à l'autre mérite un coefficient de sécurité plus élevé (1,5 au lieu de 1,2). Pour les fournisseurs fiables et rapides, tu peux descendre à 1,1 pour alléger ton stock sans prendre de risque.
INDEX/EQUIV si tu cherches à gauche du code
RECHERCHEV ne peut chercher que vers la droite. Si ton code fournisseur est en colonne C et son email en colonne A, passe à INDEX/EQUIV : =INDEX(Fournisseurs!A:A;EQUIV(G2;Fournisseurs!C:C;0)). Même logique, zéro contrainte de position.
Questions fréquentes
Rotation = consommation annuelle / stock moyen. Dans Excel, si la consommation est en B2 et le stock moyen en C2, écris =B2/C2. Un résultat de 6 signifie que tu renouvelles ce stock 6 fois par an. Les articles sous 2 sont des dormants à surveiller en priorité.
Utilise SI imbriqué : =SI(C2=0;"Rupture";SI(C2<D2;"À commander";SI(C2>E2;"Surstock";"OK"))). Chaque ligne de ta fiche article affiche son statut en temps réel dès que tu mets à jour le stock actuel. Ajoute une mise en forme conditionnelle pour colorer les alertes.
La formule =MAX(E2-C2;0) donne la quantité nécessaire pour revenir au stock maxi sans jamais afficher un nombre négatif. E2 est le stock maxi, C2 le stock actuel. Si le stock est déjà suffisant, la cellule affiche 0.
Stock mini = consommation journalière x délai de réappro x coefficient de sécurité. Avec 100 pièces consommées par jour, un délai de 5 jours et un coeff de 1,3, le mini est 650. Dans Excel, la formule donne =Conso_jour * Delai * Coeff.
Crée un onglet Fournisseurs avec le nom, l'email et le délai. Dans la fiche article, =RECHERCHEV(G2;Fournisseurs!$A:$C;2;FAUX) renvoie l'email du fournisseur dès que tu saisis son nom en colonne G. Enveloppe dans SIERREUR pour afficher un tiret si le fournisseur n'est pas encore saisi.
Oui, sans aucun problème. Excel tient facilement 500 à 1 000 lignes avec toutes les formules actives. Au-delà de 1 000 références avec des mouvements quotidiens, un WMS dédié devient plus adapté, mais Excel reste utile pour les rapports et le contrôle même avec un logiciel spécialisé.
3 exercices similaires à la gestion des stocks et réappro
Matrice de décision multicritère
Construis une matrice de décision pondérée pour comparer plusieurs options sur des critères chiffrés et sortir une recommandation objective.
Voir l'exercice
Planning de production
Créer un planning de production qui calcule automatiquement les dates de livraison en tenant compte des jours ouvrés, des contraintes de capacité et des priorités.
Voir l'exercice
Retrouver un prix avec RECHERCHEV
Utiliser RECHERCHEV pour relier une commande à un catalogue produit : retrouver le nom, le prix et gérer les codes introuvables.
Voir l'exercice
Envie de t'entraîner plus ?
Des dizaines de cas pratiques Excel corrigés pour progresser pour de vrai.
Voir tous les exercices