Excel permet de créer un système de gestion de stock complet et efficace pour les TPE/PME. Ce guide vous montre comment gérer votre inventaire, suivre les mouvements et mettre en place des alertes automatiques.
1. Créer la fiche produit (référentiel)
Structure du tableau produits
| Référence | Désignation | Catégorie | Prix achat | Prix vente | Stock min | Emplacement |
|---|---|---|---|---|---|---|
| REF001 | Stylo bleu | Fournitures | 0,50€ | 1,20€ | 100 | A-01 |
| REF002 | Cahier A4 | Fournitures | 2,00€ | 4,50€ | 50 | A-02 |
| REF003 | Agrafeuse | Matériel | 8,00€ | 15,00€ | 20 | B-01 |
Colonnes essentielles
| Colonne | Description |
|---|---|
| Référence | Code unique du produit |
| Désignation | Nom du produit |
| Catégorie | Pour filtrer et analyser |
| Prix achat | Coût unitaire |
| Prix vente | Prix de vente HT |
| Stock minimum | Seuil d'alerte |
| Emplacement | Localisation physique |
2. Tableau des stocks en temps réel
Structure
| Référence | Désignation | Stock initial | Entrées | Sorties | Stock actuel | Valeur stock |
|---|---|---|---|---|---|---|
| REF001 | Stylo bleu | 200 | 500 | 450 | 250 | 125€ |
| REF002 | Cahier A4 | 100 | 200 | 180 | 120 | 240€ |
Formules
Stock actuel :
=C2+D2-E2
Valeur du stock (au prix d'achat) :
=F2*PrixAchat
Utilisez RECHERCHEX pour récupérer le prix d'achat :
=F2*RECHERCHEX(A2;Produits[Référence];Produits[Prix achat])
Valeur totale du stock
=SOMME(G:G)
3. Alertes de stock automatiques
Formule d'alerte simple
=SI(F2<=StockMin;"⚠️ RUPTURE";SI(F2<=StockMin*1.5;"🟡 Stock bas";"🟢 OK"))
Avec RECHERCHEX pour récupérer le stock minimum
=SI(F2<=RECHERCHEX(A2;Produits[Référence];Produits[Stock min]);"⚠️ RUPTURE";
SI(F2<=RECHERCHEX(A2;Produits[Référence];Produits[Stock min])*1.5;"🟡 Stock bas";"🟢 OK"))
Mise en forme conditionnelle
- Sélectionnez la colonne Stock actuel
- Mise en forme conditionnelle → Nouvelle règle
- Utiliser une formule :
=$F2<=$G2
(où G2 contient le stock minimum)
Format : Fond rouge, texte blanc
4. Suivi des mouvements de stock
Journal des mouvements
| Date | Référence | Type | Quantité | Motif | Utilisateur |
|---|---|---|---|---|---|
| 15/01/2025 | REF001 | Entrée | 500 | Réception commande | Marie |
| 16/01/2025 | REF001 | Sortie | 100 | Vente client A | Pierre |
| 17/01/2025 | REF002 | Sortie | 50 | Vente client B | Marie |
Types de mouvements
| Type | Description |
|---|---|
| Entrée | Réception, production, retour client |
| Sortie | Vente, consommation, perte |
| Ajustement + | Inventaire (surplus) |
| Ajustement - | Inventaire (manquant) |
| Transfert | Changement d'emplacement |
Calcul des entrées totales par produit
=SOMME.SI.ENS(Mouvements[Quantité];Mouvements[Référence];A2;Mouvements[Type];"Entrée")
Calcul des sorties totales
=SOMME.SI.ENS(Mouvements[Quantité];Mouvements[Référence];A2;Mouvements[Type];"Sortie")
5. Inventaire physique
Feuille d'inventaire
| Référence | Désignation | Emplacement | Stock théorique | Stock compté | Écart | Valeur écart |
|---|---|---|---|---|---|---|
| REF001 | Stylo bleu | A-01 | 250 | 248 | -2 | -1,00€ |
| REF002 | Cahier A4 | A-02 | 120 | 120 | 0 | 0€ |
Formule de l'écart
=E2-D2
Valeur de l'écart
=F2*RECHERCHEX(A2;Produits[Référence];Produits[Prix achat])
Total des écarts
=SOMME(G:G)
Mise en forme des écarts
Colorez en rouge les écarts négatifs, en vert les positifs :
Règle : =$F2<0 → Rouge
Règle : =$F2>0 → Vert
6. Analyse ABC (classification des stocks)
Principe de la loi de Pareto
- Classe A : 20% des références = 80% de la valeur
- Classe B : 30% des références = 15% de la valeur
- Classe C : 50% des références = 5% de la valeur
Tableau d'analyse
| Référence | Valeur consommée | % du total | % cumulé | Classe |
|---|---|---|---|---|
| REF005 | 50 000€ | 35% | 35% | A |
| REF002 | 35 000€ | 24% | 59% | A |
| REF008 | 20 000€ | 14% | 73% | A |
| REF001 | 15 000€ | 10% | 83% | B |
| ... | ... | ... | ... | ... |
Formule de classification
=SI(D2<=80%;"A";SI(D2<=95%;"B";"C"))
Intérêt
- Classe A : Suivi quotidien, commandes fréquentes
- Classe B : Suivi hebdomadaire
- Classe C : Suivi mensuel, commandes groupées
7. Calcul du point de commande
Formule théorique
Point de commande = (Consommation journalière × Délai livraison) + Stock de sécurité
Tableau
| Référence | Conso. mensuelle | Conso. jour | Délai (jours) | Stock sécu | Point commande |
|---|---|---|---|---|---|
| REF001 | 450 | 15 | 5 | 30 | 105 |
Formules
Consommation journalière :
=B2/30
Point de commande :
=C2*D2+E2
Alerte de réapprovisionnement
=SI(StockActuel<=PointCommande;"📦 À COMMANDER";"")
8. Valorisation du stock
Méthodes de valorisation
| Méthode | Description | Usage |
|---|---|---|
| CUMP | Coût Unitaire Moyen Pondéré | Standard |
| FIFO | Premier Entré, Premier Sorti | Périssables |
| LIFO | Dernier Entré, Premier Sorti | Rare en France |
| Prix standard | Prix fixe défini | Industrie |
Calcul du CUMP
=(Valeur_stock_avant + Valeur_entrée)/(Quantité_avant + Quantité_entrée)
Exemple
| Opération | Qté | Prix unit. | Valeur | Stock | CUMP |
|---|---|---|---|---|---|
| Stock initial | 100 | 10€ | 1 000€ | 100 | 10€ |
| Entrée | 50 | 12€ | 600€ | 150 | 10,67€ |
| Sortie | 30 | 10,67€ | 320€ | 120 | 10,67€ |
CUMP après entrée :
=(1000+600)/(100+50) = 10,67€
9. Tableau de bord stock
KPIs essentiels
| Indicateur | Valeur | Cible | Statut |
|---|---|---|---|
| Valeur totale stock | 125 000€ | - | - |
| Nombre de références | 245 | - | - |
| Références en rupture | 3 | 0 | 🔴 |
| Références en alerte | 12 | <10 | 🟡 |
| Taux de rotation | 4,2 | >4 | 🟢 |
| Couverture stock (jours) | 45 | 30-60 | 🟢 |
Formules
Nombre de références en rupture :
=NB.SI(Stock[Statut];"⚠️ RUPTURE")
Taux de rotation :
=CA_annuel/Stock_moyen
Couverture en jours :
=(Stock_actuel/Consommation_annuelle)*365
10. Commandes fournisseurs
Proposition de commande automatique
| Référence | Stock actuel | Point cde | Qté à cder | Fournisseur | Coût estimé |
|---|---|---|---|---|---|
| REF001 | 80 | 105 | 200 | Fournisseur A | 100€ |
| REF003 | 15 | 30 | 50 | Fournisseur B | 400€ |
Quantité à commander
=SI(B2<=C2;ARRONDI.SUP((C2-B2)/LotMinimum;0)*LotMinimum;0)
Arrondit au lot minimum supérieur.
Filtrer les produits à commander
Créez un filtre sur "Qté à commander > 0".
11. Historique et statistiques
Consommation mensuelle
| Référence | Jan | Fév | Mar | Avr | Moyenne | Tendance |
|---|---|---|---|---|---|---|
| REF001 | 420 | 450 | 480 | 510 | 465 | 📈 +7% |
| REF002 | 180 | 175 | 170 | 160 | 171 | 📉 -4% |
Moyenne des 3 derniers mois
=MOYENNE(B2:D2)
Tendance (variation)
=(D2-B2)/B2
Sparklines pour visualisation rapide
- Sélectionnez une cellule vide
- Insertion → Graphiques sparkline → Courbe
- Sélectionnez la plage de données
12. Bonnes pratiques
1. Utilisez des tableaux structurés
Ctrl + T pour convertir en tableau :
- Formules automatiques
- Filtres intégrés
- Références claires
2. Protégez les formules
- Déverrouillez les cellules de saisie
- Révision → Protéger la feuille
3. Validez les saisies
Listes déroulantes pour :
- Références produits
- Types de mouvement
- Emplacements
4. Sauvegardez régulièrement
- Copie quotidienne
- Version mensuelle archivée
- Cloud pour sécurité
5. Auditez régulièrement
- Inventaire tournant (partiel)
- Inventaire complet annuel
- Analyse des écarts
Conclusion
Excel permet de gérer efficacement le stock d'une TPE/PME avec des outils simples mais puissants. Des alertes automatiques au calcul du point de commande, en passant par l'analyse ABC, vous avez toutes les clés pour optimiser votre gestion des stocks et éviter les ruptures.
Astuce ExcelGenius : Besoin de créer rapidement un tableau de gestion de stock avec les bonnes formules ? Notre IA génère automatiquement les tableaux et calculs adaptés à votre activité. Décrivez simplement vos besoins. C'est GRATUIT → Essayer ExcelGenius maintenant
Articles connexes :
- Maîtrisez RECHERCHEX pour lier vos tableaux
- Découvrez SOMME.SI.ENS pour calculer par critères
- Créez des tableaux croisés dynamiques pour analyser vos stocks