Gestion des stocks et réappro sur Excel
Un exercice corrigé pour piloter ta gestion de stock avec des SI imbriqués et RECHERCHEV, afficher le statut en temps réel et la quantité à commander, avec le fichier Excel à télécharger.
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é.
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.
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
Le modèle prêt à l'emploi
Découvre notre fichier de gestion de stock pour suivre entrées, sorties et alertes de réapprovisionnement automatiquement
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.
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é.
Toute la boîte à outils Excel
Formules, exercices, modèles, raccourcis, lexique : tout ce qu’il te faut pour progresser, en accès libre.
Tout voir
