Automatiser les processus de planification de la demande? Excel VBA

Automatiser la planification de la demande dans Excel à l’aide de VBA (Visual Basic for Applications) peut permettre de rationaliser plusieurs tâches, telles que l’analyse de la demande passée, la prévision des besoins futurs, et la gestion des stocks en fonction des données historiques. Le but de ce processus est d’optimiser les ressources et d’assurer que les produits ou services sont disponibles en fonction des prévisions de demande.

Dans cet exemple, je vais vous guider à travers un processus qui inclut les éléments suivants :

1. Collecte des données historiques de la demande.
2. Calcul des prévisions de demande.
3. Analyse de l’écart entre la demande prévue et le stock disponible.
4. Automatisation de la mise à jour des prévisions dans un tableau.

Étape 1 : Organiser vos données dans Excel

Assurez-vous que vos données de demande historique sont organisées dans une feuille de calcul. Par exemple, voici une disposition simple :

Dans cet exemple, la colonne « Date » représente la date de la demande, et « Demande réelle » représente la quantité demandée pour chaque jour.

Étape 2 : Le code VBA pour automatiser la planification de la demande

Voici un code VBA détaillé qui automatise ce processus :

Sub AutomatiserPlanificationDemande()
    Dim wsData As Worksheet
    Dim wsPrevision As Worksheet
    Dim dernierLigne As Long
    Dim i As Long
    Dim dateActuelle As Date
    Dim demandeReelle As Double
    Dim demandePrevue As Double
    Dim moyenneDemande As Double
    Dim ecartDemande As Double
    Dim stockDisponible As Double
    Dim seuilReappro As Double
    Dim planificationCol As Range
    ' Définir les feuilles de travail
    Set wsData = ThisWorkbook.Sheets("HistoriqueDemande") ' Historique des demandes
    Set wsPrevision = ThisWorkbook.Sheets("PrevisionsDemande") ' Prévisions de demande
    ' Trouver la dernière ligne de données dans HistoriqueDemande
    dernierLigne = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
    ' Initialiser la colonne de planification dans la feuille de prévision
    Set planificationCol = wsPrevision.Range("B2:B" & dernierLigne)
    ' Calculer la moyenne des demandes pour la prévision
    Dim totalDemande As Double
    totalDemande = 0
    For i = 2 To dernierLigne
        totalDemande = totalDemande + wsData.Cells(i, 2).Value
    Next i
    moyenneDemande = totalDemande / (dernierLigne - 1)
    ' Définir le seuil de réapprovisionnement (en fonction de la politique de stock)
    seuilReappro = 200 ' Valeur à ajuster selon votre entreprise
    ' Remplir les prévisions de demande dans la feuille "PrevisionsDemande"
    For i = 2 To dernierLigne
        ' Obtenir la date et la demande réelle
        dateActuelle = wsData.Cells(i, 1).Value
        demandeReelle = wsData.Cells(i, 2).Value
        ' Calculer la prévision de demande (utilisation de la moyenne pour simplifier)
        demandePrevue = moyenneDemande
        ' Calculer l'écart entre la demande réelle et la prévision
        ecartDemande = demandeReelle - demandePrevue
        ' Vérifier la disponibilité des stocks
        stockDisponible = wsPrevision.Cells(i, 3).Value ' Supposons que la colonne C contient le stock actuel  
        ' Ajouter la prévision et l'écart dans la feuille de prévision
        wsPrevision.Cells(i, 1).Value = dateActuelle ' Date
        wsPrevision.Cells(i, 2).Value = demandePrevue ' Demande prévue
        wsPrevision.Cells(i, 3).Value = stockDisponible ' Stock disponible
        wsPrevision.Cells(i, 4).Value = ecartDemande ' Ecart entre la demande réelle et la prévision
        ' Vérifier si le stock est insuffisant et signaler le besoin de réapprovisionnement
        If stockDisponible < seuilReappro Then
            wsPrevision.Cells(i, 5).Value = "Réapprovisionnement requis"
        Else
            wsPrevision.Cells(i, 5).Value = "Suffisant"
        End If
    Next i
    MsgBox "Planification de la demande automatisée avec succès!", vbInformation
End Sub

Explication détaillée du code :

1. Définir les variables et les feuilles de travail :
   • wsData : Feuille contenant les données historiques de la demande.
   • wsPrevision : Feuille où les prévisions de demande seront enregistrées.
   • dernièreLigne : Identifie la dernière ligne de données dans la feuille « HistoriqueDemande ».
2. Calculer la moyenne de la demande historique :
   • Cette moyenne sert de base pour la prévision de la demande future.
3. Remplir la feuille des prévisions :
   • Le code remplit la feuille « PrevisionsDemande » avec les informations suivantes :
     • Date : Date de la demande.
     • Demande prévue : Valeur de la prévision (basée sur la moyenne).
     • Demande réelle : La demande passée (historique).
     • Écart : Différence entre la demande réelle et la prévision.
     • Stock disponible : Quantité en stock à la date donnée (supposée être présente dans la feuille des prévisions).
     • Réapprovisionnement requis : Indication si le stock est inférieur au seuil de réapprovisionnement.
4. Alerte de réapprovisionnement :
   • Le code vérifie si le stock est inférieur à un seuil et affiche un message d’alerte pour un réapprovisionnement nécessaire.

Étape 3 : Utiliser le code dans Excel

1. Ouvrez Excel et créez deux feuilles :
   • HistoriqueDemande : Contient les données historiques de demande.
   • PrevisionsDemande : Contiendra les résultats de la prévision, y compris la planification et les alertes de réapprovisionnement.
2. Ouvrez l’éditeur VBA (Alt + F11), puis créez un nouveau module et copiez-collez le code ci-dessus.
3. Pour exécuter la macro, allez dans Outils > Macro > Macros, sélectionnez AutomatiserPlanificationDemande et cliquez sur Exécuter.

Personnalisation du code

• Méthode de prévision : Ce code utilise une méthode simple basée sur la moyenne des demandes passées. Vous pouvez la remplacer par des techniques plus avancées comme la lissage exponentiel, l’analyse de régression, ou l’utilisation de prévisions plus détaillées.
• Seuil de réapprovisionnement : Modifiez la valeur du seuil de réapprovisionnement en fonction de vos besoins spécifiques.

Ce processus vous permettra d’automatiser une partie du travail de planification de la demande, mais vous pouvez le compléter avec des analyses plus poussées et des outils de gestion de stock pour optimiser vos prévisions.