Implémentation des algorithmes avancés de gestion des stocks, Excel VBA

Implémentation des algorithmes avancés de gestion des stocks, Excel VBA

Voici une explication détaillée en français sur l’implémentation des algorithmes avancés de gestion des stocks en Excel VBA, ainsi qu’un code avec des explications sur chaque étape.
L’objectif est de créer un système qui incorpore des techniques avancées de gestion des stocks, telles que :
1. Quantité économique de commande (EOQ) – Calcule la quantité optimale à commander pour minimiser les coûts totaux de gestion des stocks.
2. Point de commande (ROP) – Détermine quand passer une nouvelle commande pour éviter les ruptures de stock.
3. Stock de sécurité – Stock supplémentaire pour prévenir les ruptures de stock lors de fluctuations de la demande.
4. Demande pendant le délai de livraison (Lead Time Demand) – Calcule la demande attendue pendant le délai de livraison.
Explication détaillée des concepts :
1. Quantité Économique de Commande (EOQ)
La quantité économique de commande (EOQ) permet de déterminer la quantité optimale à commander afin de minimiser les coûts totaux des stocks (coût de commande + coût de stockage). La formule est :
EOQ = √(2 ⋅ D ⋅ S / H)
Où :

• D = Demande (unités par période)
• S = Coût de commande (coût par commande)
• H = Coût de stockage (coût par unité par période)

2. Point de Commande (ROP)
Le point de commande permet de savoir quand passer une commande. Il dépend du délai de livraison (temps entre la commande et la réception des marchandises) et de la demande moyenne pendant ce délai.
ROP = Demande pendant le délai de livraison = Délai de livraison × Demande moyenne par jour
3. Stock de Sécurité
Le stock de sécurité est une quantité de stock supplémentaire pour éviter les ruptures de stock en cas de variations imprévues de la demande ou du délai de livraison.
SS = Z × σL × LT
Où :

• Z = Facteur de service (selon le niveau de service désiré)
• σL = Écart-type de la demande pendant le délai de livraison
• LT = Délai de livraison (en jours)

4. Demande Pendant le Délai de Livraison (LTD)
La demande pendant le délai de livraison (LTD) représente la demande attendue pendant la période où l’on attend la livraison des articles commandés. Cela permet de prévoir combien de stocks seront utilisés avant la réception de la prochaine commande.
Code VBA Excel pour la gestion avancée des stocks :
Voici un code VBA détaillé qui met en œuvre ces algorithmes de gestion des stocks avancée :

Sub GestionAvanceeDesStocks()
    ' Déclaration des variables pour le calcul de l'EOQ, du ROP, du Stock de Sécurité et de la Demande pendant le délai de livraison
    Dim demande As Double, coutCommande As Double, coutStockage As Double
    Dim delaiLivraison As Double, demandeMoyenne As Double
    Dim facteurService As Double, ecartTypeDemande As Double, LT As Double
    Dim eoq As Double, rop As Double, stockSecurite As Double, ltd As Double
    Dim niveauService As Double
    ' Saisie des valeurs pour le modèle (ces valeurs peuvent être remplacées par des références de cellules si nécessaire)
    demande = 12000 ' Demande annuelle (unités par an)
    coutCommande = 100 ' Coût de commande (par commande)
    coutStockage = 5 ' Coût de stockage (par unité par an)
    delaiLivraison = 7 ' Délai de livraison (en jours)
    demandeMoyenne = 30 ' Demande moyenne quotidienne (unités)
    ' Paramètres pour le stock de sécurité
    niveauService = 0.95 ' Niveau de service souhaité (niveau de service de 95% correspond à Z=1.645)
    facteurService = Application.WorksheetFunction.NormSInv(niveauService) ' Valeur Z pour le niveau de service
    ' Estimation de l'écart-type de la demande pendant le délai de livraison (basée sur des données historiques ou des estimations)
    ecartTypeDemande = 10 ' Écart-type de la demande pendant le délai de livraison
    LT = delaiLivraison ' Délai de livraison en jours
    ' Calcul de l'EOQ (Quantité économique de commande)
    eoq = Sqr((2 * demande * coutCommande) / coutStockage)
    ' Calcul du Point de Commande (ROP)
    rop = delaiLivraison * demandeMoyenne
    ' Calcul du Stock de Sécurité
    stockSecurite = facteurService * ecartTypeDemande * Sqr(LT)
    ' Calcul de la Demande pendant le Délai de Livraison (LTD)
    ltd = delaiLivraison * demandeMoyenne
    ' Affichage des résultats dans la fenêtre immédiate pour le débogage et sur la feuille Excel
    Debug.Print "Quantité Economique de Commande (EOQ) : " & eoq
    Debug.Print "Point de Commande (ROP) : " & rop
    Debug.Print "Stock de Sécurité : " & stockSecurite
    Debug.Print "Demande pendant le Délai de Livraison (LTD) : " & ltd
    ' Optionnellement, affichage des résultats dans des cellules spécifiques de la feuille Excel (vous pouvez ajuster les références de cellules)
    Range("B1").Value = "Quantité Economique de Commande (EOQ)"
    Range("B2").Value = eoq
    Range("B3").Value = "Point de Commande (ROP)"
    Range("B4").Value = rop
    Range("B5").Value = "Stock de Sécurité"
    Range("B6").Value = stockSecurite
    Range("B7").Value = "Demande pendant le Délai de Livraison (LTD)"
    Range("B8").Value = ltd
End Sub

Explication du code :
1. Déclaration des Variables :

• Les variables sont définies pour chaque paramètre : demande, coût de commande, coût de stockage, etc. Ces valeurs peuvent être directement liées à des cellules Excel si vous préférez une entrée dynamique.

2. Calcul de l’EOQ :

• La formule de l’EOQ est utilisée pour calculer la quantité optimale de commande.

3. Calcul du ROP :

• Le point de commande est calculé en multipliant le délai de livraison par la demande moyenne par jour.

4. Calcul du Stock de Sécurité :

• Le stock de sécurité est calculé en utilisant la valeur Z correspondant au niveau de service souhaité et l’écart-type de la demande pendant le délai de livraison.

5. Calcul de la Demande pendant le Délai de Livraison (LTD) :

• La demande pendant le délai de livraison est simplement calculée en multipliant la demande quotidienne moyenne par le délai de livraison (en jours).

6. Affichage des Résultats :

• Les résultats sont affichés dans la fenêtre immédiate pour un débogage rapide et également dans des cellules spécifiques de la feuille Excel pour une consultation facile.

Comment utiliser ce code :
1. Saisie des Données :

• Vous pouvez ajuster les valeurs d’entrée comme la demande, le coût de commande, le coût de stockage, le délai de livraison et la demande moyenne. Ces valeurs peuvent aussi être liées directement à des cellules de la feuille Excel.

2. Exécution du Code :

• Appuyez sur Alt + F11 pour ouvrir l’éditeur VBA dans Excel, puis collez ce code dans un nouveau module.
• Vous pouvez ensuite exécuter la subroutine GestionAvanceeDesStocks directement ou la lier à un bouton sur votre feuille Excel.

3. Affichage des Résultats :

• Les résultats seront affichés dans la fenêtre immédiate pour le débogage et dans les cellules Excel spécifiées, où vous pourrez facilement consulter les valeurs de l’EOQ, du ROP, du stock de sécurité et de la demande pendant le délai de livraison.

Améliorations possibles :

• Entrées dynamiques : Vous pouvez créer des formulaires utilisateurs ou lier les valeurs d’entrée à des cellules pour permettre à l’utilisateur de saisir les données directement dans la feuille Excel.
• Suivi des Stocks : Vous pouvez intégrer un système de suivi des stocks et de notifications automatiques lorsque le point de commande est atteint ou que les stocks tombent en dessous du niveau de sécurité.
• Gestion de multiples articles : Ce modèle peut être étendu pour gérer plusieurs produits en ajoutant une boucle pour traiter les différentes lignes de produits et leurs entrées respectives.

Ce code vous permet d’implémenter des algorithmes de gestion des stocks avancée en Excel VBA et vous pouvez l’adapter en fonction de vos besoins spécifiques.