Excel comme WMS pour PME multi-sites

Une PME avec 3 entrepôts (Dakar, Thiès, Saint-Louis) et 500 références peut gérer efficacement son stock dans Excel avant de passer à un WMS spécialisé. Structure bien conçue = fiabilité supérieure à 95 pour cent et alertes automatiques qui évitent les ruptures coûteuses.

Architecture du classeur

Feuilles : Produits (référentiel), Mouvements (entrées/sorties), StockActuel (calculé), Seuils (paramètres de réapprovisionnement), Dashboard (vue de pilotage).

Feuille Produits

Colonnes : Référence, Libellé, Catégorie, PrixAchat, PrixVente, Fournisseur, DelaiLivraison, ConditionnementAchat.

Feuille Mouvements

Chaque ligne = un mouvement. Colonnes : Date, Reference, Entrepot, TypeMouvement (Entrée/Sortie/Transfert), Quantite, MotifEcart, Utilisateur.

Calcul du stock actuel

Stock par site :
=SOMMEPROD((Mouvements[Reference]=A2)*(Mouvements[Entrepot]=B$1)*(Mouvements[TypeMouvement]="Entrée")*Mouvements[Quantite]) 
 - SOMMEPROD((Mouvements[Reference]=A2)*(Mouvements[Entrepot]=B$1)*(Mouvements[TypeMouvement]="Sortie")*Mouvements[Quantite])
 + SOMMEPROD((Mouvements[EntrepotDest]=B$1)*(Mouvements[Reference]=A2)*Mouvements[Quantite])
 - SOMMEPROD((Mouvements[EntrepotSrc]=B$1)*(Mouvements[Reference]=A2)*Mouvements[Quantite])

Pour 500 références × 3 entrepôts = 1 500 cellules calculées. Préférer Power Pivot avec DAX pour la performance.

Seuils et alertes

Feuille Seuils : pour chaque référence, stock minimum, stock maximum, quantité de réapprovisionnement. Calcul formule :

=SI(StockActuel <= StockMinimum; "ALERTE"; SI(StockActuel <= StockMinimum * 1,2; "ATTENTION"; "OK"))

Mise en forme conditionnelle : rouge pour ALERTE, orange pour ATTENTION, vert pour OK.

Rotation et valeur

Calcul de rotation mensuelle :

Rotation = Sorties du mois / Stock moyen du mois

Classement ABC : les 20 pour cent de références qui font 80 pour cent du CA méritent la plus grande attention. Classement automatique dans une colonne dédiée.

Dashboard de pilotage

Nombre de références en alerte (gros chiffre visible)
Valeur totale du stock (multi-sites, détail par site)
Top 10 produits en rupture
Top 10 produits surstockés (cash dormant)
Graphique évolution valeur stock sur 12 mois

Inventaire tournant

Au lieu d’un inventaire annuel général, comptez par rotation : 20 pour cent des références chaque trimestre. Feuille Inventaire : Reference, DateComptage, StockCompte, StockThéorique, Ecart, Commentaire. Automatisez la détection d’écarts supérieurs à 5 pour cent.

Transfers entre entrepôts

Gestion particulière : une sortie d’un site = une entrée dans l’autre. Deux lignes liées par un même numéro de bon de transfert. Contrôle de cohérence : somme des quantités transférées = 0 par référence.

Commandes fournisseurs automatiques

Générer automatiquement le bon de commande dès qu’un produit passe en ALERTE. VBA simple qui remplit un modèle avec : références, quantités de réapprovisionnement, fournisseur, prix, délai. Export PDF et envoi email.

Conclusion

Excel reste viable pour la gestion de stock jusqu’à environ 2 000 références et 10 000 mouvements par mois. Au-delà, un WMS dédié (TradeGecko, Odoo Inventory, Sage X3) devient incontournable. Dans l’intervalle, ce modèle rend un service équivalent à 90 pour cent d’un logiciel dédié, à zéro coût.

Voir aussi

Étape 1 : modéliser la base stock à plat plutôt qu’en croisé

La première erreur classique est de créer une colonne par entrepôt. Ça marche pour deux dépôts, ça devient ingérable au cinquième. Le bon réflexe est la table à plat : une ligne par mouvement ou par état, avec des colonnes Entrepôt, Produit, Quantité. Cette structure est aussi celle qu’attendent toutes les fonctions modernes d’Excel comme SOMMEPROD, NB.SI.ENS et les tableaux croisés dynamiques.

Créez une feuille Stock avec les colonnes ci-dessous. Considérez quatre entrepôts : Dakar Plateau, Thiès, Saint-Louis, et Touba — c’est une configuration courante pour un grossiste sénégalais.

A : Entrepôt
B : Code produit
C : Désignation
D : Quantité en stock
E : Seuil minimum
F : Statut

Chaque ligne représente un produit dans un entrepôt donné. Si la même référence existe dans trois entrepôts, vous avez trois lignes. Cette redondance apparente est le prix à payer pour la souplesse de calcul qui suit.

Étape 2 : calculer le statut de chaque ligne avec SI imbriqué

Le statut de stock se décline en trois niveaux : OK quand la quantité est confortable, Alerte quand on approche du seuil, Rupture quand on est en dessous. C’est la base de tout système de réapprovisionnement.

Placez-vous en F2 et tapez la formule suivante. On considère qu’on entre en alerte à 1,5 fois le seuil minimum.

=SI(D2<=E2;"Rupture";SI(D2<=E2*1,5;"Alerte";"OK"))

Recopiez vers le bas. Excel affiche désormais un statut textuel par ligne. Sur un fichier de mille références réparties sur quatre entrepôts, vous obtenez en quelques secondes l’état réel de tout le stock. La logique imbriquée traite d’abord le cas le plus critique (rupture) avant l’alerte, ce qui évite qu’une cellule à zéro soit classée Alerte par erreur.

Étape 3 : colorer automatiquement les lignes critiques

Le texte ne suffit pas dans un tableau de mille lignes. La mise en forme conditionnelle permet de colorer la ligne entière selon le statut. Sélectionnez la plage A2:F1000, ouvrez Accueil puis Mise en forme conditionnelle puis Nouvelle règle, et choisissez Utiliser une formule.

Saisissez la formule suivante pour le rouge des ruptures. Notez la référence mixte avec dollar sur la colonne F uniquement, qui permet d’évaluer la même cellule de statut pour toutes les colonnes de la ligne.

=$F2="Rupture"

Choisissez un remplissage rouge clair, validez. Créez ensuite une seconde règle avec =$F2="Alerte" et un orange. Les lignes en rupture apparaissent en rouge, celles en alerte en orange, le reste reste neutre. Un responsable logistique à Thiès voit instantanément les références à recommander, sans lire chaque cellule.

Étape 4 : agréger les totaux par entrepôt avec SOMMEPROD

Pour piloter les flux, on a besoin du stock total par entrepôt et du nombre de références en alerte. SOMMEPROD est plus puissant que SOMME.SI car il accepte plusieurs critères et fonctionne dans toutes les versions d’Excel.

Sur une feuille Tableau de bord, listez les quatre entrepôts en colonne A, puis tapez les formules de totalisation en B et C.

B2 =SOMMEPROD((Stock!A2:A1000=A2)*Stock!D2:D1000)
C2 =NB.SI.ENS(Stock!A2:A1000;A2;Stock!F2:F1000;"Rupture")
D2 =NB.SI.ENS(Stock!A2:A1000;A2;Stock!F2:F1000;"Alerte")

B2 totalise les unités présentes dans l’entrepôt nommé en A2. C2 et D2 comptent respectivement les références en rupture et en alerte. Recopiez sur quatre lignes pour les quatre dépôts. Vous obtenez un tableau de bord d’une demi-page qui résume la situation logistique de toute l’entreprise.

Étape 5 : retrouver une fiche produit avec RECHERCHEX

Quand un commercial à Bamako appelle pour vérifier la disponibilité d’un produit dans tel entrepôt, on a besoin d’un outil de consultation rapide. RECHERCHEX, disponible dans Microsoft 365 et Excel 2021, remplace l’ancien RECHERCHEV avec une syntaxe plus claire et la possibilité de chercher dans n’importe quelle direction.

Sur une feuille Recherche, placez en B1 la liste déroulante des entrepôts et en B2 le code produit. La formule de consultation s’écrit comme suit en B4.

=RECHERCHEX(1;(Stock!A2:A1000=B1)*(Stock!B2:B1000=B2);Stock!D2:D1000;"Non trouvé")

RECHERCHEX renvoie la quantité en stock du produit sélectionné dans l’entrepôt sélectionné, ou Non trouvé si la combinaison n’existe pas. C’est une opération qui prendrait deux RECHERCHEV imbriqués en version classique : ici, une seule formule lisible suffit. Pour un fallback compatible Excel 2019, utilisez INDEX et EQUIV à la place.

Étape 6 : générer la liste de réapprovisionnement à imprimer

Le but final est de produire la liste des références à recommander aujourd’hui. FILTRE, vue dans notre tutoriel FILTRE UNIQUE TRIER, fait le travail sans macro ni intervention manuelle.

Sur une feuille Réappro, écrivez en A1 la formule suivante.

=FILTRE(Stock!A2:F1000;(Stock!F2:F1000="Rupture")+(Stock!F2:F1000="Alerte");"Aucune action requise")

Le signe plus joue le rôle d’un OU logique. Excel affiche un tableau dynamique avec uniquement les lignes en rupture ou en alerte, prêt à imprimer ou à exporter en PDF pour transmettre au service achats. Voir aussi notre tutoriel Excel : suivi SAV multi-agents qui réutilise la même logique de filtrage.

Étape 7 : automatiser les transferts inter-entrepôts

Quand un entrepôt est en rupture et qu’un autre a un surplus, le transfert interne évite la commande fournisseur. Une colonne de calcul auxiliaire détecte ces opportunités. Sur la feuille Stock, ajoutez une colonne G nommée Surplus disponible, calculée comme suit.

=MAX(0;D2-E2*2)

Le surplus est défini comme tout ce qui dépasse deux fois le seuil minimum, marge de sécurité raisonnable. Sur une feuille Transferts, on liste pour chaque produit en rupture le ou les entrepôts qui ont du surplus. Cela transforme Excel en aide à la décision logistique : un livreur fait une tournée Thiès vers Touba avant de passer une commande fournisseur, économisant les frais de port internationaux. Vous gardez la main sur les arbitrages sans automatiser à l’aveugle.

Mettre en place un inventaire tournant pour fiabiliser le stock

Un inventaire annuel complet bloque l’entrepot pendant deux a trois jours et accumule les erreurs de comptage en raison de la fatigue. La methode professionnelle, appliquee dans les grandes surfaces a Dakar comme dans les entrepots logistiques d’Abidjan, est l’inventaire tournant : chaque semaine, on compte 5 a 10 pour cent du stock selon une rotation programmee, en croisant le compte physique avec le stock theorique calcule par les formules Excel. Sur trois mois, l’integralite du stock est passee en revue sans interrompre l’activite.

Dans Excel, ajoutez une colonne Date dernier inventaire a votre tableau de stock et une colonne Ecart constate. Cette derniere se remplit lors du comptage et alimente automatiquement un onglet de pilotage qui identifie les references chroniquement deficitaires (souvent vol ou casse non declaree) et les references chroniquement excedentaires (souvent erreur de saisie a la reception). Documentez chaque ecart superieur a 5 pour cent dans un journal des incidents pour identifier les causes racines.

Cette discipline, partagee entre la responsable logistique et la comptabilite, transforme la culture de l’entrepot : les equipes savent qu’elles seront contolees, donc la rigueur de saisie remonte, et le taux d’erreur en stock chute de 3 a 5 pour cent vers moins de 1 pour cent en six mois.

Calculer les rotations de stock pour eviter les surstocks

La rotation de stock, exprimee en nombre de fois par an, mesure combien de fois le stock moyen est ecoule sur une periode. La formule classique est cout des marchandises vendues divise par stock moyen. Une rotation de 12 signifie que le stock se renouvelle chaque mois, ce qui est sain pour des produits de consommation courante. Une rotation de 2 ou 3 indique un surstock probable, avec un risque d’obsolescence et un cout financier de 8 a 15 pour cent par an du capital immobilise.

Dans Excel, ajoutez une formule qui calcule la rotation par reference sur les douze derniers mois glissants, et triez ce tableau par rotation croissante. Les vingt premieres lignes representent vos suspects prioritaires a destocker, eventuellement par promotion ciblee ou par retour fournisseur.

Croisez la rotation avec le poids du capital immobilise (rotation faible plus stock cher = priorite absolue) pour orienter les decisions de destockage. Un tableau de bord trimestriel partage avec la direction generale rend cette donnee visible et permet d’arbitrer rapidement.

Appliquer la methode ABC ou Pareto sur la classification des references

La loi de Pareto appliquee a la gestion de stock dit que 20 pour cent des references representent 80 pour cent du chiffre d’affaires. La methode ABC pousse cette logique plus loin en classant les references en trois categories : A (20 pour cent des refs, 80 pour cent du CA), B (30 pour cent des refs, 15 pour cent du CA), C (50 pour cent des refs, 5 pour cent du CA). Cette classification, simple a calculer dans Excel avec QUARTILE.INCLURE et SI, permet d’allouer l’attention de gestion la ou elle paie le plus.

Pour les references A, mettez en place un suivi hebdomadaire et un seuil d’alerte agressif (point de commande haut, stock de securite genereux). Pour les C, acceptez une gestion plus relachee, voire un arret pur et simple si la rotation est faible. Cette discipline libere le temps de la responsable logistique pour les vraies priorites. Voir aussi notre tutoriel scoring RFM qui applique une logique similaire au pilotage clients.

Configurer des declencheurs automatiques de reapprovisionnement

Au-dela de l’alerte visuelle dans Excel, organisez la transition vers un workflow de reapprovisionnement semi-automatique. Une cellule de la feuille Stock peut produire, par concatenation de chaines, un mail pre-redige avec le nom du fournisseur, la reference, la quantite a commander et le delai souhaite. Il suffit ensuite de copier-coller ce texte dans Outlook ou un client de messagerie pour valider la commande en deux clics au lieu de plusieurs minutes de saisie manuelle.

Pour les structures plus matures, exportez les commandes a passer dans un fichier CSV digere par votre ERP ou par un outil low-code type n8n auto-heberge. Ce niveau d’automatisation reduit de moitie le temps consacre aux taches administratives de la responsable logistique et lui rend du temps pour la negociation fournisseurs et l’analyse de la performance.

Verifiez systematiquement, avant validation, les conditions tarifaires : un decalage de 2 pour cent sur un fournisseur cle peut effacer la marge sur plusieurs centaines de produits. Cette verification de cinq minutes paie chaque mois.