Gestion des stocks et reapproCas pratique Excel pas a pas
Le contexte
Construis un tableau de gestion des stocks qui calcule le stock disponible, déclenche les alertes de réappro et identifie les surstocks. Tu gères 200 références sans stress.
Les données de depart
Crée un tableau avec toutes tes références : code article, désignation, stock actuel, stock minimum (seuil de réappro), stock maximum, délai de réappro (en jours), fournisseur. Tu gères 200 références.
| 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 |
Solution pas a pas
2Calculer le statut du stock
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 a jour en temps réel.
=SI(C2=0;"Rupture";SI(C2<D2;"A commander";SI(C2>E2;"Surstock";"OK")))Resultat 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.
3Calculer la quantité a commander
Utilise MAX pour calculer la quantité a commander : elle doit ramener le stock au niveau maxi, mais jamais en dessous de zéro. La formule renvoie 0 si le stock est suffisant.
=MAX(E2-C2;0)Resultat attendu : La quantité a commander (ex: 1 800 pour REF-002 car 2 000 - 200 = 1 800). 0 si le stock est suffisant.
4Identifier le stock le plus critique
Utilise MIN pour trouver le ratio stock/mini le plus bas de tout le catalogue. Ce ratio te dit quelle référence est la plus proche de la rupture. Un ratio de 0,5 signifie qu'il reste la moitié du stock mini.
=MIN(C2:C201/D2:D201)Resultat attendu : Le ratio stock/mini le plus bas du catalogue (ex: 0,67 pour REF-002 car 200/300).
Attention
- Cette formule est matricielle. Dans les versions avant Microsoft 365, valide avec Ctrl+Shift+Entrée.
5Récupérer les infos fournisseur
Utilise RECHERCHEV pour aller chercher l'email et le délai du fournisseur dans la table fournisseurs. Tu peux ensuite préparer un email de commande semi-automatique avec les quantités calculées.
=RECHERCHEV(G2;Fournisseurs!$A:$C;2;FAUX)Resultat attendu : L'email du fournisseur (ex: contact@acierpplus.fr pour Acier Plus).
Formules utilisées dans ce cas pratique
Questions fréquentes
Comment définir le stock minimum ?
Stock mini = consommation moyenne journalière x délai de réappro x coefficient de sécurité (1,2 a 1,5). Si tu consommes 100 vis/jour et le délai est de 5 jours, stock mini = 100 x 5 x 1,3 = 650.
Comment calculer la rotation des stocks dans Excel ?
Rotation = coût des ventes annuel / stock moyen. Un taux de rotation de 6 signifie que tu renouvelles ton stock 6 fois par an. Plus c'est élevé, mieux c'est (sauf si tu tombes en rupture).
Comment gérer les stocks avec des dates de péremption ?
Ajoute une colonne 'Date de péremption' et trie par FIFO (premier entré, premier sorti). Utilise une mise en forme conditionnelle pour colorer en orange les articles qui périment dans les 30 jours.
Excel suffit-il pour gérer 200 références ?
Oui, jusqu'a 500-1 000 références. Au-delà, un WMS (Warehouse Management System) est plus adapté. Excel reste utile comme outil de contrôle et de reporting même si tu as un WMS.
Comment éviter les erreurs de saisie dans les mouvements de stock ?
Utilise la validation de données pour limiter les codes articles a la liste existante. Ajoute une colonne 'Type de mouvement' (entrée/sortie/inventaire) avec une liste déroulante. Protège les colonnes calculées.
Cas pratiques similaires
Comparaisons liees
RECHERCHEV vs RECHERCHEX
RECHERCHEX remplace RECHERCHEV mais n'est pas disponible partout.
INDEX vs RECHERCHEV
INDEX/EQUIV est plus flexible que RECHERCHEV, mais plus complexe à écrire.
SI vs SI.CONDITIONS
SI.CONDITIONS remplace les SI imbriqués par une syntaxe plus lisible.
GRANDE.VALEUR vs MAX
MAX renvoie le plus grand nombre, GRANDE.VALEUR renvoie le Nième plus grand.
EXACT vs SI
EXACT compare deux textes en respectant la casse, SI teste une condition et renvoie un résultat.
Envie de pratiquer plus ?
Rejoins Le Dojo Club pour accéder a des dizaines de cas pratiques corrigés, des formations complètes et une communauté de pros qui s'entraident.
Essayer pendant 30 jours