Simulation de modèle d’inventaire avec des feuilles de calcul

Le terme inventaire désigne la quantité de matériaux ou de ressources disponibles, incluant les matières premières, les encours de production, les produits finis, les fournitures et les pièces de rechange. La gestion des stocks (IM) est la procédure par laquelle l’inventaire est mesuré et planifié selon des règles prédéfinies telles que la quantité économique de commande ou de production, le stock de sécurité, les niveaux minimum et maximum, et le point de commande. L’IM relève principalement de l’application de politiques, systèmes et procédures établis afin de réduire le coût des stocks.

Gestion des stocks (IM)

L’IM correspond au processus de commande, d’entreposage et d’utilisation des stocks d’une entreprise. Elle couvre la gestion des matières premières, des composants et des produits finis, ainsi que leur stockage et leur traitement. L’IM s’appuie sur diverses données pour suivre les articles tout au long du flux, notamment les numéros de lot, numéros de série, coût des marchandises, quantités et dates de passage dans le processus. L’IM aide à minimiser les coûts, optimiser l’exécution, améliorer le service client et éviter les pertes dues au vol, aux dommages et aux retours. La figure ci-dessous illustre un exemple de système de gestion des stocks.

Modèle de quantité économique de commande (EOQ)

F. Harries a introduit le concept d’EOQ en 1916. L’EOQ est la quantité de stock à commander en une seule fois afin de minimiser le coût annuel d’inventaire. C’est l’une des méthodes d’IM qui minimise le total des coûts de possession et de passation de commandes sur l’année. L’EOQ est une méthode qui aide le responsable des approvisionnements à résoudre ses arbitrages. La figure montre la courbe de coût du modèle EOQ.

Le modèle EOQ de base fournit l’équation d’estimation de l’EOQ. La formule suivante est utilisée pour l’estimer :

Où A = consommation ou demande annuelle, B = coût de passation d’une commande et C = coût de possession d’une unité sur un an.

L’EOQ est la quantité de commande optimale d’une entreprise qui réduit ses coûts totaux liés à la commande, la réception et la détention des stocks. Pour de nombreuses entreprises, l’inventaire est un actif majeur et elles doivent conserver des niveaux suffisants pour répondre aux besoins des clients. Ce modèle convient particulièrement lorsque la demande, les coûts de commande et de possession restent constants dans le temps.

Terminologie de base de l’EOQ

Le système d’inventaire peut être classé selon la nature de ses variables. Celles-ci incluent divers coûts : coût de pénurie, coût de possession, coût de commande, demande, délai d’approvisionnement, cycle de réapprovisionnement, cadence d’entrée et ruptures. La demande peut être connue et constante (statique), connue et variable (dynamique), ou estimée dans un système d’inventaire.

• Coût de pénurie : coût engendré par une rupture empêchant de satisfaire la demande à partir du stock. Les ruptures de stock peuvent entraîner l’annulation de commandes, de lourdes pertes de ventes, une dégradation de l’image, des pertes de marge, voire menacer l’activité elle-même.
• Coût de possession : coûts liés au stockage de l’inventaire invendu. Ils constituent, avec les coûts de commande et de pénurie, un élément du coût total des stocks. Ils incluent notamment la dépréciation ou la détérioration des biens, l’espace de stockage, la main-d’œuvre, l’assurance, le coût d’opportunité du capital et la démarque, etc.
• Demande annuelle : quantité nécessaire sur une année pour l’article d’inventaire.
• Coût unitaire de commande : coût total encouru pour passer une commande unique.
• Délai d’approvisionnement (lead time) : délai pertinent pour la gestion des stocks, somme du délai de fourniture et du délai de réapprovisionnement.

Comment fonctionne le modèle EOQ ?

L’IM est essentielle pour faire tourner une entreprise sans accroc et efficacement. Prenons l’exemple d’un producteur de sucre : s’il manque régulièrement de canne à sucre, la matière première principale, la production s’arrête. L’entreprise doit planifier les quantités de canne à sucre de sorte à ne jamais être en rupture et pouvoir produire dès que nécessaire. Mais l’IM ne consiste pas à surstocker : si la canne est achetée en excès alors que la demande de sucre fléchit, elle risque de se détériorer, occasionnant des pertes financières. L’IM vise à optimiser le niveau de stock. Elle s’assure que le producteur dispose d’une quantité de canne à sucre ajustée au plus juste, alignée sur la demande des clients. La figure présente quatre grands paramètres pour gérer efficacement l’inventaire.

Simulation avec une feuille de calcul

Définir les paramètres de base et créer la colonne Quantité

• Commencez par définir les paramètres de base : demande annuelle (A), coût de commande (B), coût de possession par unité (C) et coût unitaire. Créez ensuite une colonne Quantité en démarrant à 5 par pas de 5. Après les deux premières valeurs, faites glisser jusqu’à obtenir 50 valeurs.

Créer la colonne Coût de commande

• Après la colonne Quantité, créez une colonne Coût de commande en utilisant la formule suivante :

• Dans cette formule, la demande annuelle et le coût par unité restent constants, tandis que la quantité est variable. Rendez les références constantes en ajoutant des $ comme sur la capture d’écran.

• Après avoir calculé une valeur, sélectionnez-la et faites-la glisser pour remplir les autres lignes : le Coût de commande sera alors généré automatiquement pour chaque quantité.

Créer la colonne Coût de possession

• Après la colonne Coût de commande, créez une colonne Coût de possession avec la formule suivante :

• Dans cette formule, le coût de possession par unité est constant et la quantité est variable. Rendez à nouveau la référence constante en ajoutant des $ comme sur la capture.

• Après avoir calculé une valeur, sélectionnez-la et faites-la glisser pour les autres lignes : le Coût de possession sera automatiquement généré pour chaque quantité.

Créer la colonne Coût total

• Après la colonne Coût de possession, créez la colonne Coût total en additionnant les coûts de commande et de possession.
• Après avoir saisi une première somme, sélectionnez-la et faites-la glisser pour les autres lignes : le Coût total sera automatiquement calculé pour chaque quantité.

Créer un graphique linéaire

• Une fois les trois colonnes prêtes, créez un graphique linéaire pour les visualiser.
• Pour insérer un graphique linéaire, ouvrez le menu Insert et choisissez l’option correspondante dans la liste déroulante à droite de l’écran. Votre graphique devrait ressembler à la capture ci-dessous.

Appliquer une mise en forme conditionnelle

• Après avoir créé le graphique des coûts de commande, de possession et du coût total, il est temps de trouver la quantité de commande minimale.
• Pour la déterminer, recherchez le coût total minimal à l’aide de la mise en forme conditionnelle.
• Allez dans Format, puis cliquez sur conditional formatting. Une fenêtre s’ouvre sur la droite.

• Dans cette fenêtre :

• Dans Bottom 10 Items..., saisissez 1 et choisissez une couleur. Ici, nous avons sélectionné Light Red with Dark Red Color.

• Vous obtenez ainsi le Bottom 1 Item, c’est-à-dire la valeur minimale du coût total, qui est 3535.714, et la quantité économique de commande qui est de 70.

Avantages et limites du modèle EOQ

Les petites entreprises recherchent un système d’inventaire efficace pour maximiser leur rentabilité. Le modèle EOQ est un élément fréquemment utilisé dans un système d’inventaire en revue continue. Il repose sur une formule qui estime la quantité optimale à commander pour minimiser les coûts et maximiser la valeur lors du réapprovisionnement (The Advantages & Disadvantages of Economic Order Quantity (EOQ)). Son principal atout réside dans des recommandations adaptées sur le nombre d’unités le plus économique par commande.

Des modèles EOQ performants nécessitent des données complètes pour estimer plusieurs paramètres. Les limites du modèle EOQ sont les suivantes :

• Il requiert des calculs mathématiques parfois complexes.
• Le modèle repose sur l’hypothèse d’un seul produit et l’équation ne permet pas de regrouper plusieurs articles au sein d’une même commande.
• Le modèle EOQ ne tient pas compte des remises sur quantité susceptibles d’être obtenues lors d’achats en volume.

Conclusion

Félicitations, vous êtes arrivé au bout de ce tutoriel !

Dans ce tutoriel, vous avez exploré en détail la gestion des stocks : l’IM, le modèle EOQ, la terminologie associée et ses avantages comme ses limites. L’IM consiste à tenir des registres précis des marchandises prêtes à être expédiées. Elle est déterminante pour maîtriser les coûts tout en répondant aux exigences opérationnelles. La demande et l’offre doivent rester en équilibre, et l’IM contribue à préserver cet équilibre.

Vous pouvez désormais mobiliser le concept d’EOQ pour analyser vos propres jeux de données. Merci de votre lecture !

Si vous souhaitez en savoir plus sur les feuilles de calcul, suivez le cours Conditional Formatting in Spreadsheets de DataCamp.