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 avec SI et MAX la quantité qui ramène au maxi les articles en rupture ou passés sous le mini.
- 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.
À 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, réparties sur 2 onglets comme dans le fichier (clique sur un onglet sous le tableau pour changer de feuille). Copie-les ou , puis entraîne-toi avant de regarder le corrigé.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Code | Article | Stock | Mini | Maxi | Délai (j) | Fournisseur | Statut | Quantité à commander | Email 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 | 5 600 | 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 | 0 | 100 | 800 | 10 | Métal Export | |||
| 7 | REF-006 | Joint torique 25mm | 4 000 | 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 | 200 | 200 | 1 500 | 8 | Spring Tech | |||
| 11 | REF-010 | Entretoise nylon | 90 | 150 | 1 000 | 6 | Plasti Indus |
| A | B | C | |
|---|---|---|---|
| 1 | Fournisseur | Téléphone | |
| 2 | Acier Plus | commandes@acier-plus.fr | 03 53 01 12 40 |
| 3 | Fix Pro | contact@fix-pro.fr | 01 99 00 27 15 |
| 4 | Métal Export | ventes@metal-export.fr | 04 65 71 45 67 |
| 5 | Caoutchouc SA | achats@caoutchouc-sa.fr | 05 36 49 30 82 |
| 6 | Spring Tech | commandes@spring-tech.fr | 02 61 91 58 06 |
| 7 | Plasti Indus | adv@plasti-indus.fr | 04 65 71 73 21 |
Résoudre l'exercice étape par étape
Coche chaque étape au fur et à mesure. Tente-la dans ton fichier, puis déplie le corrigé.
Astuces pour aller plus loin
Traque les rotations faibles, c'est de l'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.
Passe à INDEX/EQUIV pour chercher vers la gauche
RECHERCHEV cherche dans la première colonne de sa plage et ne renvoie qu'une valeur située à sa droite. Pour retrouver le nom d'un fournisseur (colonne A de l'onglet Fournisseurs) à partir de son téléphone (colonne C), passe à INDEX/EQUIV. =INDEX(Fournisseurs!$A:$A;EQUIV("04 65 71 45 67";Fournisseurs!$C:$C;0)) renvoie Métal Export. Même logique, zéro contrainte de position.
S'entraîner sur 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
Tu as peut-être encore une question
Rotation = consommation annuelle / stock moyen. La fiche de l'exercice ne porte pas ces deux données. Dans ta propre fiche, ajoute une colonne pour chacune, puis divise sur chaque ligne la consommation par le stock moyen. 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é.
Les formules de gestion de stock dans Excel tiennent en cinq calculs, le statut avec un SI imbriqué, =SI(C2=0;"Rupture";SI(C2<D2;"À commander";SI(C2>E2;"Surstock";"OK"))), la quantité à commander avec =SI(H2="OK";0;MAX(E2-C2;0)), le stock mini égal à la consommation journalière multipliée par le délai de réappro et par un coefficient de sécurité, la rotation égale à la consommation annuelle divisée par le stock moyen, et les coordonnées du fournisseur avec RECHERCHEV. Le statut, la quantité et l'email du fournisseur se recopient sur chaque ligne de la fiche article de l'exercice, et une mise en forme conditionnelle colore ensuite les statuts pour que les ruptures sautent aux yeux.
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, soit =100*5*1,3 dans Excel. Dans ta fiche, remplace chaque nombre par la cellule qui le porte, le délai de réappro étant déjà en colonne F.
Les quatre niveaux de stock sont le stock minimum, en dessous duquel on ne doit pas descendre, le stock de sécurité, la réserve qui absorbe un retard de livraison ou une consommation plus forte que prévu, le stock d'alerte ou point de commande, qui déclenche la commande, et le stock maximum, au-delà duquel on immobilise de l'argent pour rien. La fiche de cet exercice en porte deux, le mini en colonne D et le maxi en colonne E, et le statut « À commander » joue le rôle du stock d'alerte dès que le stock passe sous le mini. Le stock de sécurité se déduit du délai fournisseur, comme l'explique la question sur le stock minimum.
Excel suffit pour gérer le stock d'une petite activité, un classeur bien construit comme la fiche article de cet exercice tient sans peine 500 à 1 000 références avec toutes ses formules actives, et un logiciel de gestion d'entrepôt ne devient plus adapté qu'au-delà d'un millier de références avec des mouvements quotidiens, ou dès que plusieurs personnes saisissent en même temps. Le fichier se télécharge au-dessus de l'exercice guidé, et le modèle de gestion de stock du Dojo, proposé en bas de page, complète cet exercice. Même avec un logiciel, Excel garde le rôle des rapports et du contrôle.
Pour aller plus loin
Tout ce dont tu as besoin pour continuer à t’entraîner se trouve ici.
Récupère notre modèle de gestion de stock
Télécharger le fichier
Ton cours Excel gratuit
3 chapitres pour apprendre les bases et découvrir l'automatisation.
Voir le cours
Télécharge notre cours Excel complet en PDF
Télécharger
Le quiz qui révèle ton vrai niveau
Faire le test
Le dictionnaire des formules Excel
Chercher une formule