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 et l’implémentation des algorithmes avancés de gestion des stocks, en mettant l’accent sur l’analyse ABC et la quantité économique de commande (EOQ), en VBA Excel.
1. Analyse ABC
L’Analyse ABC est une méthode de classification des stocks en trois catégories (A, B et C) en fonction de leur importance, généralement en utilisant la valeur de consommation annuelle.
Étapes pour implémenter l’Analyse ABC :

• Catégorie A : Ce sont les articles les plus importants, représentant généralement 70 à 80 % de la valeur, mais seulement un petit pourcentage (10 à 20 %) des articles.
• Catégorie B : Ces articles représentent une valeur modérée, représentant environ 15 à 25 % de la valeur, et 20 à 30 % des articles.
• Catégorie C : Ce sont les articles les moins importants, mais qui représentent souvent un grand pourcentage des stocks en termes de volume (50 à 60 % des articles), mais ne comptent que pour 5 à 10 % de la valeur.

Étapes pour implémenter l’Analyse ABC en VBA :
1. Calculez la valeur de consommation annuelle pour chaque article en multipliant le coût unitaire par la demande annuelle (usage).
2. Triez les articles par valeur de consommation annuelle de manière décroissante.
3. Calculez le pourcentage cumulé de consommation et classez les articles en A, B ou C en fonction de leur contribution à la valeur totale.
Code VBA pour l’Analyse ABC :

Sub AnalyseABC()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Stock") ' Changez cela selon le nom de votre feuille
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Supposons que les données commencent à partir de la ligne 2
    ' Colonnes (Supposées) : A=Article, B=Coût Unitaire, C=Demande Annuelle, D=Valeur de Consommation Annuelle, E=Catégorie ABC
    Dim totalValeurConsommation As Double
    totalValeurConsommation = Application.WorksheetFunction.Sum(ws.Range("D2:D" & lastRow)) ' Somme des Valeurs de Consommation
    Dim valeurConsommationCumulative As Double
    valeurConsommationCumulative = 0
    ' Étape 1 : Calculer la Valeur de Consommation Annuelle (Coût Unitaire * Demande Annuelle)
    Dim i As Long
    For i = 2 To lastRow
        ws.Cells(i, 4).Value = ws.Cells(i, 2).Value * ws.Cells(i, 3).Value ' Valeur de Consommation Annuelle (Coût Unitaire * Demande Annuelle)
    Next i
    ' Étape 2 : Trier les articles par Valeur de Consommation Annuelle (Colonne D) de manière décroissante
    ws.Range("A1:E" & lastRow).Sort Key1:=ws.Range("D2:D" & lastRow), Order1:=xlDescending, Header:=xlYes
    ' Étape 3 : Calculer le pourcentage cumulé de la consommation totale
    For i = 2 To lastRow
        valeurConsommationCumulative = valeurConsommationCumulative + ws.Cells(i, 4).Value
        ws.Cells(i, 5).Value = valeurConsommationCumulative / totalValeurConsommation * 100 ' Pourcentage Cumulé
        ' Étape 4 : Classifier en A, B ou C en fonction du pourcentage cumulé
        If ws.Cells(i, 5).Value <= 80 Then
            ws.Cells(i, 5).Value = "A"
        ElseIf ws.Cells(i, 5).Value <= 95 Then
            ws.Cells(i, 5).Value = "B"
        Else
            ws.Cells(i, 5).Value = "C"
        End If
    Next i
    MsgBox "Analyse ABC Terminée !"
End Sub

Explication du Code de l’Analyse ABC :
1. Classification des articles :

• • Valeur de Consommation Annuelle est calculée dans la colonne D en multipliant le coût unitaire (colonne B) par la demande annuelle (colonne C).
  • Les données sont triées par la valeur de consommation annuelle (colonne D), de la plus grande à la plus petite.
  • Le pourcentage cumulé est calculé, qui est la somme de la valeur de consommation annuelle divisée par la valeur totale de consommation.
  • La classification ABC se base sur le pourcentage cumulé :
    • A : Les 70-80 % de la valeur la plus élevée.
    • B : Les 15-25 % suivants.
    • C : Les 5-10 % restants.

2. Quantité Économique de Commande (EOQ)
La Quantité Économique de Commande (EOQ) est un modèle utilisé pour déterminer la quantité de commande optimale qui minimise les coûts totaux d’inventaire, comprenant les coûts de stockage et de commande.
La formule pour EOQ est la suivante :
EOQ = √(2DS / H)
Où :

• D = Taux de demande (unités par an)
• S = Coût de commande par commande
• H = Coût de stockage par unité par an

Étapes pour implémenter EOQ en VBA :
1. Définir les paramètres : Demande (D), Coût de commande (S), et Coût de stockage (H).
2. Calculer l’EOQ en utilisant la formule.
3. Vous pouvez également calculer le coût total d’inventaire, qui est la somme des coûts de commande et des coûts de stockage.
Code VBA pour le calcul de l’EOQ :

Sub CalculEOQ()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Stock") ' Changez cela selon le nom de votre feuille
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Supposons que les données commencent à partir de la ligne 2
    ' Colonnes (Supposées) : A=Article, B=Demande Annuelle (D), C=Coût de Commande (S), D=Coût de Stockage (H), E=EOQ, F=Coût Total
    Dim EOQ As Double
    Dim coutCommande As Double, coutStockage As Double, demande As Double
    Dim i As Long
    For i = 2 To lastRow
        ' Étape 1 : Récupérer la Demande, le Coût de Commande, et le Coût de Stockage
        demande = ws.Cells(i, 2).Value
        coutCommande = ws.Cells(i, 3).Value
        coutStockage = ws.Cells(i, 4).Value
        ' Étape 2 : Calculer l'EOQ en utilisant la formule EOQ
        EOQ = Sqr((2 * demande * coutCommande) / coutStockage)
        ' Étape 3 : Calculer le Coût Total (Coût de Commande + Coût de Stockage)
        ws.Cells(i, 5).Value = EOQ ' Stocker l'EOQ dans la colonne E
        ws.Cells(i, 6).Value = (demande / EOQ) * coutCommande + (EOQ / 2) * coutStockage ' Coût Total dans la colonne F
    Next i
    MsgBox "Calcul de l'EOQ Terminé !"
End Sub

Explication du Code de l’EOQ :
1. Entrées :

• Demande (D), Coût de commande (S), et Coût de stockage (H) sont récupérés depuis la feuille Excel pour chaque article.

2. Calcul de l’EOQ : La formule EOQ est implémentée à l’aide de Sqr((2 * D * S) / H) et le résultat est stocké dans la colonne E.
3. Calcul du coût total : Le coût total d’inventaire pour chaque article est calculé comme suit :

• Coût de commande : D / EOQ × S
• Coût de stockage : EOQ / 2 × H

Résumé :

• L’Analyse ABC permet de classer les stocks en trois catégories selon leur importance, ce qui aide à prioriser les articles pour une gestion plus efficace des stocks.
• Le modèle EOQ permet de calculer la quantité optimale de commande afin de minimiser les coûts d’inventaire en équilibrant les coûts de commande et de stockage.

Ces deux techniques sont fondamentales pour une gestion des stocks efficace et peuvent être facilement automatisées dans Excel VBA.