Gestion de stock sur Excel : créer son fichier pas à pas (formules incluses)
Créer un fichier Excel de gestion de stock : onglets articles, entrées et sorties, état du stock avec SOMME.SI.ENS, stock d'alerte, valorisation au CMUP, rotation des stocks et inventaire physique.
Réponse rapide : un fichier Excel de gestion de stock fiable repose sur quatre onglets — Articles, Entrées, Sorties et État du stock. On saisit uniquement les mouvements (réceptions fournisseurs, ventes, retours) ; l’onglet État du stock calcule tout seul le stock disponible avec SOMME.SI.ENS, le signale quand il passe sous le stock d’alerte et le valorise au coût moyen unitaire pondéré (CMUP).
Ce modèle convient à une boutique en ligne, un commerce de proximité ou un artisan qui revend une partie de sa production. Il fonctionne aussi dans Google Sheets. Voici comment le construire, les formules à copier et les indicateurs à suivre chaque mois.
La structure du fichier
| Onglet | Une ligne par | Colonnes |
|---|---|---|
| Articles | référence | Référence, désignation, famille, unité, fournisseur, délai de livraison (jours), stock de sécurité |
| Entrées | réception ou retour client | Date, référence, quantité, prix unitaire HT, n° de bon de livraison, motif |
| Sorties | vente, casse ou perte | Date, référence, quantité, n° de commande, canal, motif |
| État du stock | référence | Entrées, sorties, stock disponible, CMUP, valeur, consommation journalière, stock d’alerte, statut |
Séparer entrées et sorties dans deux onglets rend la saisie plus simple pour une petite équipe : la personne qui réceptionne les colis ne touche qu’à l’onglet Entrées, celle qui prépare les commandes qu’à l’onglet Sorties. Mettez chaque plage sous forme de tableau (Insertion → Tableau ou Ctrl + L) pour que les formules s’étendent automatiquement aux nouvelles lignes.
Étape 1 : la liste des articles
Attribuez à chaque article une référence unique qui ne changera jamais, par exemple CARN-A5-LIGNE. Chaque déclinaison gérée séparément (couleur, taille, format) a sa propre référence. Ajoutez le délai de livraison du fournisseur et un stock de sécurité, la quantité minimale à garder pour absorber un retard ou un pic de ventes.
Utilisez Données → Validation des données pour que les onglets Entrées et Sorties n’acceptent que des références existantes. C’est la meilleure protection contre les fautes de frappe qui faussent les totaux.
Étape 2 : saisir les entrées et les sorties
Dans Entrées (A Date, B Référence, C Quantité, D Prix unitaire HT, E N° de BL, F Motif), enregistrez chaque réception au moment où vous la contrôlez, avec un prix unitaire qui inclut les frais de port fournisseur répartis par article. Un retour client remis en vente est aussi une entrée, avec le motif « Retour ».
Dans Sorties (A Date, B Référence, C Quantité, D N° de commande, E Canal, F Motif), enregistrez les ventes, mais aussi la casse, les échantillons offerts et les pertes constatées, chacune avec son motif. Si vous vendez en ligne, collez chaque semaine l’export des commandes de votre boutique plutôt que de les ressaisir.
Étape 3 : l’état du stock
Dans l’onglet État du stock, avec la référence en colonne A, voici les formules (Excel en français utilise le point-virgule comme séparateur) :
- Total entrées (B) :
=SOMME.SI.ENS(Entrées!C:C;Entrées!B:B;A2) - Total sorties (C) :
=SOMME.SI.ENS(Sorties!C:C;Sorties!B:B;A2) - Stock disponible (D) :
=B2-C2 - CMUP (E) :
=SIERREUR(SOMMEPROD((Entrées!B2:B5000=A2)*Entrées!C2:C5000*Entrées!D2:D5000)/B2;0) - Valeur du stock (F) :
=D2*E2
Si votre Excel est en anglais, les fonctions s’appellent SUMIFS, IFERROR et SUMPRODUCT, avec la virgule comme séparateur. Pour le CMUP, saisissez les retours clients sans prix unitaire s’ils ne doivent pas modifier le coût moyen, ou excluez le motif « Retour » de la formule.
Étape 4 : le stock d’alerte
Le stock d’alerte (ou point de commande) est le niveau à partir duquel il faut passer commande pour ne pas tomber en rupture pendant le délai de livraison :
Stock d’alerte = consommation journalière moyenne × délai de livraison + stock de sécurité
- Consommation journalière (G), sur les 30 derniers jours :
=SOMME.SI.ENS(Sorties!C:C;Sorties!B:B;A2;Sorties!F:F;"Vente";Sorties!A:A;">="&AUJOURDHUI()-30)/30 - Stock d’alerte (H) :
=G2*RECHERCHEX(A2;Articles!A:A;Articles!F:F)+RECHERCHEX(A2;Articles!A:A;Articles!G:G) - Statut (I) :
=SI(D2<=H2;"Commander";"OK")
Exemple illustratif : un carnet A5 se vend en moyenne à 6 exemplaires par jour. Le fournisseur livre en 12 jours et vous gardez 30 carnets de sécurité. Le stock d’alerte est de 6 × 12 + 30 = 102. Avec 95 carnets en rayon, le fichier affiche « Commander ». Une mise en forme conditionnelle sur la colonne Statut fait ressortir d’un coup d’œil toutes les références à réapprovisionner.
Étape 5 : suivre la rotation des stocks
Un stock qui ne tourne pas immobilise de la trésorerie. Deux indicateurs simples, à calculer une fois par an ou par trimestre :
- Taux de rotation = coût d’achat des marchandises vendues ÷ stock moyen
- Durée moyenne de stockage = 365 ÷ taux de rotation
Par exemple, avec 48 000 € de coût d’achat des marchandises vendues sur l’année et un stock moyen de 8 000 €, le stock tourne 6 fois par an : un article reste en moyenne environ 61 jours en stock. Calculé par famille ou par référence, cet indicateur montre les produits à déstocker et ceux qu’il faut commander plus souvent, en plus petites quantités.
Le coût d’achat utilisé ici sert aussi au calcul de vos prix : le guide du calcul du taux de marge explique comment passer du coût d’achat HT au prix de vente.
Étape 6 : l’inventaire physique
Aucun fichier ne remplace un comptage réel. Pour les commerçants, le Code de commerce impose au moins un inventaire tous les douze mois ; vérifiez avec votre expert-comptable les obligations qui s’appliquent à votre statut et la méthode de valorisation retenue (le CMUP et la méthode PEPS/FIFO sont les plus courantes).
Entre deux inventaires annuels, pratiquez un inventaire tournant : chaque mois, comptez une famille d’articles, en commençant par ceux qui représentent le plus de valeur ou de mouvements. Quand le comptage diffère du stock calculé, enregistrez l’écart dans Entrées ou Sorties avec le motif « Inventaire », plutôt que de corriger une cellule à la main.
Les erreurs qui faussent un fichier de stock
- Modifier directement le stock au lieu d’enregistrer un mouvement : l’historique disparaît.
- Oublier la casse, les échantillons et les retours : ce sont les premières causes d’écart.
- Saisir en retard : un stock mis à jour en fin de mois ne sert à rien pour décider d’une commande.
- Effacer les anciennes lignes pour alléger le fichier : clôturez plutôt l’année et reportez le stock final comme première entrée du nouveau fichier.
- Laisser tout le monde modifier les formules : protégez l’onglet État du stock (Révision → Protéger la feuille).
Excel ou logiciel de gestion de stock ?
| Excel suffit souvent si… | Un logiciel devient utile si… |
|---|---|
| Vous gérez quelques dizaines à quelques centaines de références | Le stock doit se synchroniser automatiquement entre plusieurs boutiques ou marketplaces |
| Une ou deux personnes saisissent les mouvements | Plusieurs personnes préparent des commandes en même temps |
| Vous avez un seul lieu de stockage | Vous gérez plusieurs entrepôts, des lots ou des numéros de série |
| Vous voulez garder la main sur les calculs | La saisie manuelle prend plus de temps que la vente |
Questions fréquentes
Existe-t-il un fichier Excel de gestion de stock gratuit ?
Il en existe beaucoup, mais un modèle que vous construisez vous-même avec la structure ci-dessus a un avantage : vous comprenez chaque formule et pouvez l’adapter à vos articles. Comptez une heure pour le mettre en place.
Comment gérer les produits en plusieurs tailles ou couleurs ?
Créez une référence par déclinaison et ajoutez une colonne « Produit » dans l’onglet Articles. Un tableau croisé dynamique vous donnera ensuite le total par produit.
Peut-on utiliser une douchette code-barres avec Excel ?
Oui. Une douchette USB ou Bluetooth classique saisit le code comme un clavier : utilisez le code-barres comme référence, ou ajoutez-le en colonne et retrouvez l’article avec RECHERCHEX.
Quelle différence entre stock de sécurité et stock d’alerte ?
Le stock de sécurité est le minimum à garder en réserve. Le stock d’alerte est le niveau qui déclenche la commande : il comprend le stock de sécurité plus ce que vous vendrez pendant le délai de livraison.
Le fichier fonctionne-t-il dans Google Sheets ?
Oui, avec la même structure. Google Sheets est pratique si plusieurs personnes saisissent les mouvements depuis le magasin, l’atelier ou un téléphone.
D’autres guides pour vendre en ligne et piloter vos stocks sont réunis dans la section Vente en ligne et gestion de stock.