Template Excel de gestion des stocks pour pharmacie : guide complet du pharmacien titulaire
# Gestion des stocks pharmacie : maîtrisez vos entrées et sorties La gestion des stocks est l'une des responsabilités critiques de votre officine. Entre les commandes fournisseurs, les dispensations quotidiennes, les dates de péremption et les contrôles réglementaires, il est facile de perdre de vue l'état réel de votre inventaire. Un stock mal géré crée des problèmes concrets : ruptures de médicaments qui frustrent vos clients, surstock coûteux qui immobilise votre trésorerie, ou pire, des péremptions non détectées qui impactent votre rentabilité et votre conformité légale. Excel offre une solution simple et efficace pour centraliser toutes ces données. Avec un tableau de suivi structuré, vous pouvez : - Enregistrer automatiquement chaque entrée et sortie de produit - Visualiser vos niveaux de stock en temps réel - Anticiper les commandes avant les ruptures - Identifier les produits à rotation lente - Simplifier vos inventaires périodiques Nous vous proposons un template Excel gratuit et prêt à l'emploi, conçu spécifiquement pour les pharmacies. Adaptable à votre gamme de produits, il vous permet de reprendre le contrôle de votre gestion des stocks en quelques minutes.
Le probleme
# La Gestion des Stocks : Le Casse-tête Quotidien du Pharmacien Chaque jour, vous jongler avec des centaines de références : médicaments, dispositifs médicaux, produits de parapharmacie. Le défi ? Éviter les ruptures de stock qui frustrent vos clients, tout en minimisant les invendus qui immobilisent votre trésorerie. Vous devez gérer les dates d'expiration, les rappels de lots, les commandes fournisseurs fragmentées. Entre les variations saisonnières (grippes, allergies), les promotions et les changements réglementaires, prévoir la demande devient un casse-tête. Sans outils adaptés, vous risquez des surcoûts : stocker trop ou trop peu, perdre du chiffre d'affaires, ou pire, devoir détruire des médicaments périmés. Votre système actuel (tableurs disparates, notes manuscrites) ne vous donne jamais une vision claire et actualisée de votre inventaire réel.
Les benefices
Réduisez de 70% le temps de gestion des inventaires en automatisant le calcul des stocks et les alertes de rupture via formules conditionnelles. Passez de 3 heures à 50 minutes par semaine sur cette tâche critique.
Diminuez les erreurs de dispensation de 90% en créant un système de traçabilité des lots avec dates d'expiration automatiquement contrôlées. Évitez les litiges de conformité et les problèmes réglementaires coûteux.
Optimisez votre trésorerie en identifiant instantanément les stocks dormants et les médicaments à rotation lente grâce à des tableaux croisés dynamiques. Récupérez 15-20% de capital immobilisé en stocks inutiles.
Maîtrisez vos marges pharmaceutiques en suivant précisément le coût d'achat vs prix de vente par catégorie thérapeutique. Augmentez votre rentabilité de 5-8% en identifiant les produits sous-marge.
Simplifiez vos contrôles d'ordre de pharmacie et vos rapprochements avec les fournisseurs en centralisant les données dans un seul fichier. Gagnez 4-5 heures mensuelles en administrative et renforcez votre conformité légale.
Tutoriel pas a pas
Créer la structure du tableau principal
Créez un nouveau classeur Excel et définissez les colonnes essentielles pour le suivi des stocks pharmaceutiques. Ces colonnes doivent inclure les informations critiques pour la gestion quotidienne et la conformité réglementaire.
Utilisez Ctrl+T pour convertir votre plage en tableau structuré, ce qui facilitera l'ajout de formules automatiques
Définir les en-têtes de colonnes
Insérez les colonnes suivantes : Code DCI, Dénomination, Forme pharmaceutique, Dosage, Numéro de lot, Date d'expiration, Stock initial, Entrées, Sorties, Stock actuel, Seuil d'alerte, Fournisseur. Formatez l'en-tête en gras avec une couleur de fond pour une meilleure lisibilité.
Gellez la première ligne (Affichage > Figer les volets) pour la garder visible lors du défilement
Ajouter des données d'exemple réalistes
Remplissez le tableau avec des données pharmaceutiques concrètes (Amoxicilline 500mg, Paracétamol 1000mg, etc.). Incluez au minimum 10-15 médicaments pour tester efficacement vos formules. Utilisez des dates d'expiration réalistes et des quantités variées.
Utilisez la validation de données pour les colonnes Forme pharmaceutique et Fournisseur afin de garantir la cohérence
Créer la formule de calcul du stock actuel
Dans la colonne 'Stock actuel', créez une formule qui additionne le stock initial et les entrées, puis soustrait les sorties. Cette formule doit se mettre à jour automatiquement à chaque modification des entrées ou sorties.
=E2+H2-I2Exemple : Paracétamol avec Stock initial (100) + Entrées (50) - Sorties (30) = Stock actuel (120)
Implémenter l'alerte de rupture de stock
Créez une formule conditionnelle qui compare le stock actuel au seuil d'alerte. Cette alerte est cruciale pour éviter les ruptures de stock et assurer la continuité du service pharmaceutique. Utilisez la mise en forme conditionnelle pour visualiser rapidement les médicaments critiques.
=SI(J2<K2;"ALERTE";"OK")Ajoutez une mise en forme conditionnelle : si Stock actuel < Seuil d'alerte, appliquez un fond rouge pour une visibilité immédiate
Ajouter une colonne de vérification d'expiration
Insérez une formule qui vérifie si la date d'expiration est dépassée ou approche (moins de 30 jours). Cela aide à respecter les normes de pharmacovigilance et à éviter la dispensation de médicaments périmés.
=SI(F2<AUJOURD'HUI();"EXPIRÉ";SI(F2<AUJOURD'HUI()+30;"À EXPIRER";"OK"))Exemple : Un lot expirant dans 15 jours affichera 'À EXPIRER' en jaune pour une action préventive
Créer un tableau de synthèse avec SOMME.SI
Créez un tableau de bord récapitulatif qui utilise SOMME.SI pour calculer automatiquement le stock total par fournisseur ou par forme pharmaceutique. Cela permet une analyse rapide et une optimisation des commandes.
=SOMME.SI($L$2:$L$50;"Fournisseur A";$J$2:$J$50)Cette formule totalise tous les stocks actuels des médicaments fournis par 'Fournisseur A', utile pour la gestion des approvisionnements
Intégrer RECHERCHEV pour les informations fournisseur
Créez une table de référence des fournisseurs avec leurs coordonnées. Utilisez RECHERCHEV pour récupérer automatiquement le numéro de téléphone ou l'email du fournisseur en fonction du nom saisi dans la colonne Fournisseur.
=RECHERCHEV(L2;Fournisseurs!$A$2:$D$50;3;FAUX)Exemple : Entrez 'Fournisseur A' et récupérez automatiquement son email pour les commandes urgentes
Ajouter des statistiques clés avec SUMIF et conditions
Créez des cellules de synthèse qui affichent le nombre de médicaments en alerte, le stock total global, la valeur estimée du stock, et le nombre de lots expirés. Ces indicateurs permettent une gestion stratégique rapide.
=SOMME.SI(M2:M50;"ALERTE";1) pour compter les alertes ou =SOMME(J2:J50) pour le stock totalPlacez ces statistiques en haut de feuille dans une zone 'Tableau de bord' avec un formatage distinctif
Finaliser avec mise en forme et protections
Appliquez une mise en forme professionnelle : alternez les couleurs de lignes, utilisez des polices lisibles, et protégez les cellules de formules pour éviter les modifications accidentelles. Créez également un filtre automatique pour rechercher rapidement par DCI ou fournisseur.
Utilisez Données > Filtre automatique pour activer les filtres, puis Outils > Protéger la feuille pour sécuriser vos formules
Fonctionnalites du template
Alerte automatique de stock minimum
Identifie les médicaments dont le stock est inférieur au seuil critique pour déclencher les commandes à temps et éviter les ruptures
=SI(B2<C2,"COMMANDER","OK")Calcul automatique de la valeur totale du stock
Évalue en temps réel la valeur financière globale du stock pour la gestion budgétaire et les déclarations d'inventaire
=SOMME(B2:B100*D2:D100)Suivi de la date de péremption avec alertes
Signale les médicaments proches de l'expiration pour éviter les pertes et respecter les obligations réglementaires
=SI(JOURSECART(E2,AUJOURD'HUI())<30,"À ÉLIMINER","VALIDE")Historique des mouvements de stock
Enregistre chaque entrée/sortie avec date et quantité pour tracer les flux et justifier les écarts lors des contrôles
=CONCATENER(F2," - ",G2," unités le ",H2)Tableau de bord de rotation des stocks (FIFO)
Priorise la vente des médicaments les plus anciens pour optimiser la gestion et réduire les péremptions
=CLASSEMENT(E2,E$2:E$100,1)Génération automatique de rapports de conformité
Compile les données de stock pour faciliter les audits et les déclarations obligatoires auprès des autorités sanitaires
=FILTRE(A2:D100,C2:C100<B2:B100)Exemples concrets
Gestion des ruptures de stock de médicaments essentiels
Jean, pharmacien titulaire d'une officine urbaine, doit gérer les ruptures fréquentes de médicaments chroniques (antihypertenseurs, anticoagulants) et anticiper les commandes pour éviter les ruptures.
Amiodarone 200mg: stock actuel 45 boîtes, consommation moyenne 12 boîtes/mois, délai de livraison 5 jours. Metformine 850mg: stock 120 boîtes, consommation 35 boîtes/mois, délai 3 jours. Doliprane 1000mg: stock 80 boîtes, consommation 25 boîtes/mois, délai 2 jours.
Resultat : Un tableau avec alertes automatiques (seuil minimum atteint), dates de réapprovisionnement recommandées, et historique de consommation pour identifier les pics saisonniers (grippe, allergies). Identification immédiate que l'Amiodarone doit être commandée en urgence.
Suivi des dates de péremption et conformité réglementaire
Sophie, pharmacienne responsable de la gestion, doit respecter les obligations légales (ANSM) concernant le contrôle des DLUO/DLC et minimiser les pertes liées aux médicaments périmés.
Aspirine 500mg: 30 boîtes expiration 15/03/2024, Ibuprofène 200mg: 45 boîtes expiration 22/06/2024, Paracétamol sirop: 12 flacons expiration 08/02/2024, Vitamine C: 60 boîtes expiration 30/12/2025.
Resultat : Un tableau trié par date d'expiration avec code couleur (rouge <30 jours, orange <60 jours, vert >60 jours). Rapport automatique des médicaments à détruire ou à retourner fournisseur. Historique des pertes par fournisseur pour négocier les conditions.
Analyse de rentabilité par catégorie de produits et marges
Marc, titulaire d'une petite pharmacie de village, doit optimiser son mix produit en identifiant quels médicaments/produits génèrent le meilleur chiffre d'affaires et marges pour justifier l'espace de stockage.
Médicaments génériques: CA 18000EUR/mois, marge 22%, rotation 45 jours. Médicaments princeps: CA 12000EUR/mois, marge 18%, rotation 60 jours. Produits de parapharmacie: CA 8500EUR/mois, marge 38%, rotation 25 jours. Matériel médical: CA 4200EUR/mois, marge 35%, rotation 90 jours.
Resultat : Un tableau de bord avec CA, marge brute, rotation de stock et rentabilité par m² de rayon. Identification que la parapharmacie génère 38% de marge avec une excellente rotation (25 jours), justifiant une augmentation de l'assortiment. Alertes sur le matériel médical à faible rotation (90 jours) nécessitant une restructuration.
Astuces de pro
Alertes automatiques de rupture de stock avec mise en forme conditionnelle
Créez des alertes visuelles instantanées pour les médicaments proches du seuil minimum. Sélectionnez la colonne de stock, allez dans Accueil > Mise en forme conditionnelle > Nouvelle règle, et appliquez une formule pour colorer en rouge les valeurs inférieures au seuil. Cela vous permet de repérer immédiatement les ruptures imminentes sans passer en revue manuellement chaque ligne. Gain de temps : 15-20 min/jour.
=E2<=F2Suivi FIFO automatisé avec formules imbriquées
Utilisez une formule combinant INDEX et MATCH pour identifier automatiquement les lots les plus anciens à consommer en priorité. Cela garantit le respect du FIFO (First In, First Out) obligatoire en pharmacie et réduit les risques de péremption. Créez une colonne 'Lot à consommer' avec la formule ci-contre pour extraire automatiquement le lot le plus ancien.
=INDEX(Lots;MATCH(MIN(SI(Stock>0;DateLot));DateLot;0))Tableau de bord de rotation avec graphiques dynamiques
Créez un tableau croisé dynamique (TCD) pour analyser la rotation des stocks par catégorie thérapeutique. Insérez un TCD depuis l'onglet Insertion, puis ajoutez des graphiques en cascade pour visualiser les produits lents à rotation. Cela vous aide à optimiser vos commandes et à identifier les surstock coûteux. Mettez à jour le TCD chaque semaine avec Actualiser (Ctrl+Alt+F5).
Calcul automatique du coût moyen pondéré avec SUMPRODUCT
Maîtrisez votre valorisation de stock en calculant le coût moyen pondéré de chaque référence. Cette formule évalue précisément votre patrimoine pharmaceutique et facilite les audits. Utilisez SUMPRODUCT pour diviser le coût total par la quantité totale, indispensable pour les déclarations de TVA et de résultats.
=SUMPRODUCT(Quantité;PrixUnitaire)/SUMPRODUCT(Quantité)