Gestion de stock Excel : modèle prêt à l'emploi

Suivre des stocks avec Excel reste pertinent pour les TPE, artisans, e-commerçants débutants et auto-entrepreneurs. Encore faut-il structurer le fichier intelligemment pour ne pas se retrouver avec des erreurs en cascade dès la deuxième semaine. Ce guide vous donne la méthode complète : structure, formules clés, alertes, valorisation et pièges à éviter.

💡 Pourquoi Excel pour gérer son stock

Excel offre trois avantages décisifs : gratuité (logiciel déjà payé), flexibilité (vous adaptez aux spécificités de vos produits) et visibilité immédiate (un coup d'œil et vous savez où vous en êtes).

Les limites apparaissent quand vous dépassez 500 références ou que plusieurs personnes saisissent en simultané : les risques de doublons, d'erreurs et de désynchronisation deviennent importants. Mais en deçà, Excel fait parfaitement le travail.

📚 Les concepts clés du stock

• Stock initial : quantité en magasin à la date de référence
• Entrées : approvisionnements (livraisons fournisseurs, retours clients)
• Sorties : ventes, casse, prélèvements internes
• Stock final = Initial + Entrées − Sorties
• Stock minimum (ou seuil d'alerte) : niveau en dessous duquel il faut recommander
• Stock de sécurité : tampon pour absorber les variations de demande
• Quantité économique de commande (QEC) : taille optimale d'une commande pour minimiser les coûts
• Rotation : nombre de fois où le stock se renouvelle dans l'année (CA / stock moyen)

À retenir : un stock immobilisé coûte de l'argent (financement, espace, obsolescence). Un stock insuffisant coûte aussi cher (ruptures, pertes de ventes). L'objectif est l'équilibre, pas la maximisation.

🏗️ Structure du fichier Excel

Trois feuilles principales

• Articles : catalogue produits avec référence, désignation, fournisseur, prix d'achat, prix de vente, seuil mini, seuil maxi
• Mouvements : journal de toutes les entrées et sorties (1 ligne = 1 mouvement)
• État stock : tableau de bord récapitulatif avec stock actuel par article, valeur, alertes

🛠️ Tutoriel étape par étape

Étape 1 — Catalogue d'articles

Sur la feuille Articles, créez les colonnes :

Transformez en tableau Excel (Ctrl + L) et nommez-le « Articles ». La référence en colonne A est la clé unique.

Étape 2 — Journal des mouvements

Sur Mouvements, les colonnes : Date, Référence article, Type (Entrée/Sortie), Quantité, Motif, Prix unitaire, Utilisateur. Transformez en tableau « Mouvements ».

La saisie est simple : chaque livraison reçue = ligne d'Entrée. Chaque vente ou prélèvement = ligne de Sortie.

Étape 3 — Calculer le stock actuel

Sur la feuille État stock, en parallèle de chaque article, le stock actuel se calcule :

=SOMME.SI.ENS(Mouvements[Quantité]; Mouvements[Référence]; A2; Mouvements[Type]; "Entrée")
 -SOMME.SI.ENS(Mouvements[Quantité]; Mouvements[Référence]; A2; Mouvements[Type]; "Sortie")

Cette formule fait la différence entre toutes les entrées et toutes les sorties pour la référence donnée. Le résultat est le stock actuel à l'instant T.

Étape 4 — Alertes visuelles de seuil

Sur la colonne « Stock actuel », appliquez une mise en forme conditionnelle :

• Stock actuel ≤ seuil mini → fond rouge (à commander)
• Stock entre mini et 1,5 × mini → fond orange (à surveiller)
• Stock supérieur → fond vert

Formule pour le rouge : =B2<=RECHERCHEV(A2;Articles;8;FAUX). Vous savez en un coup d'œil quelles références demandent une commande.

Étape 5 — Valorisation du stock

La valeur du stock à un instant T : stock actuel × prix d'achat unitaire.

=StockActuel * RECHERCHEV(A2; Articles; 5; FAUX)

En bas de la colonne, somme totale = valeur totale immobilisée en stock. Indicateur clé pour le commissaire aux comptes et pour le pilotage financier.

Étape 6 — Quantité à commander

Quand le stock est sous le seuil mini, combien commander ? Une formule simple :

=SI(StockActuel <= SeuilMini; SeuilMaxi - StockActuel; 0)

Vous obtenez automatiquement la quantité à passer en commande pour remonter au seuil maxi.

Étape 7 — Liste de commande automatique

Filtrez les articles dont « Quantité à commander » > 0 (ou utilisez la fonction FILTRE sur Excel 365). Exportez cette liste : c'est votre bon de commande prêt à envoyer au fournisseur.

📌 Exemple complet : petit e-commerce de papeterie

Catalogue de 30 références, stock initial valorisé 4500 €. Vous saisissez chaque jour :

• Les ventes Shopify (export quotidien CSV importé dans Mouvements)
• Les retours clients
• Les livraisons fournisseurs

Tous les lundis matin, vous consultez l'État stock : 3 articles sont en rouge. Vous générez la liste de commande et l'envoyez à vos 2 fournisseurs. 30 minutes par semaine pour zéro rupture.

⚠️ Erreurs fréquentes

1. Modifier directement le stock dans la feuille Articles

Erreur fatale. Le stock doit toujours être calculé depuis les mouvements. Si vous saisissez en dur, vous perdez la traçabilité.

2. Saisir « entrée » ou « Entrée » ou « ENTREE »

Le NB.SI ou SOMME.SI.ENS est insensible à la casse, mais pas un éventuel filtre. Imposez la liste déroulante (Données → Validation → Liste : « Entrée;Sortie »).

3. Ne pas archiver les anciens mouvements

Après 2 ans, le journal peut atteindre 10 000 lignes. Les formules ralentissent. Solution : archivez chaque année dans un fichier séparé et ne gardez que l'année en cours dans le fichier de travail.

4. Confondre prix d'achat et prix de vente

La valorisation du stock se fait au prix d'achat (coût). Le calcul du CA et de la marge se fait au prix de vente. Deux colonnes distinctes, deux usages distincts.

5. Oublier l'inventaire physique

Le stock théorique (calculé) diverge toujours du stock physique (réellement présent). Casse, vol, erreurs de saisie. Un inventaire physique annuel (minimum) est obligatoire pour réconcilier.

6. Pas de sauvegarde

Le fichier stock est critique. Versionnez via OneDrive et faites une copie horodatée chaque fin de mois.

✅ Bonnes pratiques avancées

Méthode FIFO (First In, First Out)

Pour les produits périssables, vendez d'abord les plus anciens. Sur la feuille Mouvements, ajoutez une colonne « Date péremption ». Tri automatique des sorties par date la plus proche : =TRIER(FILTRE(...);"Date péremption";1).

Réapprovisionnement automatique calculé

QEC (Wilson) : =RACINE((2*ConsommationAnnuelle*CoûtCommande)/(CoûtStockage)). Cette formule économique vous donne la taille idéale de commande, qui minimise le total des coûts.

Rotation des stocks par catégorie

Indicateur clé : Rotation = Coût ventes annuelles / Stock moyen. Un ratio de 12 = vous renouvelez le stock une fois par mois. En dessous de 4, vous immobilisez trop. Au-delà de 24, attention aux ruptures.

Analyse ABC (Pareto)

Classez vos articles par CA : 20 % des références génèrent en général 80 % du CA. Concentrez votre attention sur ces articles A. Pour les C (faible CA), gardez un stock minimal et acceptez quelques ruptures.

Codes-barres

Avec une douchette USB (40 €), vous scannez le code-barres et la référence s'inscrit automatiquement dans la cellule active. Saisie quasi instantanée des mouvements en magasin.

Connecter à votre boutique e-commerce

Avec Power Query, importez automatiquement les commandes de votre boutique (Shopify, WooCommerce, Prestashop). Le stock se met à jour sans saisie manuelle.

⚖️ Comparaison Excel vs logiciels de gestion de stock

❓ FAQ : gestion de stock Excel

Comment connecter mon stock Excel à ma boutique en ligne ?

Réponse courte : via Power Query qui importe les commandes en CSV.

Détail : exportez quotidiennement les commandes depuis votre back-office. Power Query (Données → Obtenir des données) charge le CSV et alimente automatiquement le tableau Mouvements en sorties. À actualiser une fois par jour.

Comment gérer le stock avec plusieurs entrepôts ?

Réponse courte : ajoutez une colonne « Entrepôt » dans Mouvements et filtrez.

Détail : chaque mouvement précise l'entrepôt. Le stock par entrepôt × article se calcule avec SOMME.SI.ENS en ajoutant le critère entrepôt. Un tableau croisé dynamique offre une vue matricielle.

Comment calculer la marge sur stock ?

Réponse courte : Marge unitaire = Prix de vente − Prix d'achat. Marge globale = Marge unitaire × Stock.

Détail : sur la feuille État stock, ajoutez deux colonnes : marge unitaire et marge totale. La somme donne le potentiel de gain immobilisé en stock — utile pour orienter les promotions.

Que faire des produits invendables ?

Réponse courte : sortez-les du stock via un mouvement « Sortie - Destruction » et valorisez la perte.

Détail : ne laissez pas du stock fantôme. La sortie comptable de produits obsolètes ou cassés impacte le résultat mais clarifie la valorisation. Le motif « Destruction » permet d'isoler ces sorties dans les rapports.

Comment faire un inventaire physique avec Excel ?

Réponse courte : exportez le stock théorique, comptez physiquement, saisissez le réel et ajustez par un mouvement de régularisation.

Détail : sortez la liste des références avec quantité théorique. Compteur en main, saisissez la quantité réelle. La différence (positif ou négatif) génère un mouvement « Sortie - Régul » ou « Entrée - Régul » qui ramène le stock théorique au réel.

Comment suivre les dates de péremption ?

Réponse courte : ajoutez « Date péremption » dans Mouvements et alertez visuellement à 30 jours.

Détail : sur la feuille Mouvements, colonne supplémentaire pour la date limite. Une vue « Périme bientôt » filtre les lots dont la péremption est dans les 30 jours : =FILTRE(Mouvements; (Mouvements[Type]="Entrée") * (Mouvements[Péremption] - AUJOURDHUI() < 30)).

Combien d'articles Excel peut-il gérer sans ralentir ?

Réponse courte : 500 références avec 10 000 mouvements restent fluides ; au-delà, ça ralentit.

Détail : les SOMME.SI.ENS deviennent coûteux sur de gros volumes. Astuces : utilisez des tableaux structurés (plus performants que des plages), évitez les colonnes entières (A:A), archivez les vieux mouvements.

Comment partager le fichier en équipe sans risque de collision ?

Réponse courte : hébergez sur OneDrive ou SharePoint avec co-édition activée.

Détail : Microsoft 365 permet la co-édition en temps réel. Chacun voit les modifications de l'autre. Verrouillez la feuille Articles (référentiel) et n'autorisez les modifications que sur Mouvements pour éviter les accidents.

🏁 Conclusion

Un fichier Excel de gestion de stock bien construit transforme une activité chronophage en routine de 15 minutes par jour. Vous évitez les ruptures, vous limitez le sur-stockage, vous savez exactement combien d'argent dort en magasin. C'est l'outil parfait pour les TPE qui veulent passer du « pifomètre » à une gestion rigoureuse, sans débourser un euro de plus.

Au-delà de 500 références ou d'un fonctionnement multi-utilisateurs intensif, basculez vers un ERP. Mais commencez par maîtriser les bases sur Excel — c'est la meilleure école pour comprendre votre activité.

Télécharger le modèle de stock Tutoriel SOMME.SI.ENS