EXCEL, VBA : Valoriser un stock selon la méthode du PEPS (FIFO) à partir des mouvements d’une référence donnée

Le contrôle des valorisations de stocks selon la méthode du CUMP (coût unitaire moyen pondéré) ne pose généralement pas de difficulté majeure sous EXCEL pour l’auditeur. La méthode du PEPS (Premier Entré Premier Sorti ou FIFO en anglais) est quant à elle moins facile à mettre en œuvre. La procédure fournie ci-après se propose de calculer la valorisation d’une référence de stock donnée à partir des mouvements entrées/sorties.

Exemple de mouvements entrées/sorties (onglet Feuil1) :

Date mvt Qté PU
01/01/2010 10 12
01/02/2010 25 13,5
23/02/2010 -30 17
01/03/2010 50 14
15/04/2010 120 13,8
18/04/2010 -70 16,2
19/04/2010 30 13,1
22/04/2010 50 12,9
25/04/2010 -10 15

 (quantités positives = entrées en stocks, quantités négatives = sorties)

Valorisation calculée par la procédure (onglet Feuil2) :

N° lot Qté Entrées Qté Sorties Solde Qté PU achat Valo stock final
Lot n° 1 10 -10 0 12 0
Lot n° 2 25 -25 0 13,5 0
Lot n° 3 50 -50 0 17 0
Lot n° 4 120 -25 95 14 1330
Lot n° 5 30 0 30 13,8 414
Lot n° 6 50 0 50 16,2 810
175 2554

Code source de la procédure de calcul :

'Calcul de la valorisation d'une référence de stock selon la méthode du PEPS à partir des mouvements E/S de stocks
'Ecrit par Benoît-René RIVIERE
 
'Contenu des onglets :
'Feuil1 : mouvements de stock d'une référence
'Feuil2 : valorisation du stock final de la référence
 
Option Explicit
 
Sub PEPS()
'Calcul le stock final selon la méthode du PEPS (attention : ne gère pas les stocks négatifs)
    Dim nblots As Integer
    Dim numlotASortir As Integer
    Dim QtéLot(1 To 50000) As Integer
    Dim numligne As Variant
    Dim qté As Double
    Dim qté2 As Double
    Dim PU As Double
       
    'Entête des valorisations
    Feuil2.Cells(1, 1).Value = "N° lot"
    Feuil2.Cells(1, 2).Value = "Qté Entrées"
    Feuil2.Cells(1, 3).Value = "Qté Sorties"
    Feuil2.Cells(1, 4).Value = "Solde Qté"
    Feuil2.Cells(1, 5).Value = "PU achat"
    Feuil2.Cells(1, 6).Value = "Valo stock final"
   
    numligne = 1
    nblots = 0
    numlotASortir = 1
    Do
        numligne = numligne + 1
        qté = Feuil1.Cells(numligne, 2).Value
        If qté > 0 Then
            nblots = nblots + 1
            QtéLot(nblots) = qté
            Feuil2.Cells(nblots + 1, 1).Value = "Lot n° " & nblots
            Feuil2.Cells(nblots + 1, 2).Value = qté
            Feuil2.Cells(nblots + 1, 3).Value = 0
            Feuil2.Cells(nblots + 1, 4).FormulaLocal = "=somme(B" & nblots + 1 & ":C" & nblots + 1 & ")"
            Feuil2.Cells(nblots + 1, 5).FormulaLocal = "=Feuil1!C" & nblots + 1
            Feuil2.Cells(nblots + 1, 6).FormulaLocal = "=D" & nblots + 1 & "*E" & nblots + 1
        ElseIf qté < 0 Then
            qté2 = -qté
            Do
                If qté2 > QtéLot(numlotASortir) Then
                    qté2 = qté2 - QtéLot(numlotASortir)
                    Feuil2.Cells(numlotASortir + 1, 3).Value = Feuil2.Cells(numlotASortir + 1, 3).Value - QtéLot(numlotASortir)
                    QtéLot(numlotASortir) = 0
                    numlotASortir = numlotASortir + 1
                Else:
                    QtéLot(numlotASortir) = QtéLot(numlotASortir) - qté2
                    Feuil2.Cells(numlotASortir + 1, 3).Value = Feuil2.Cells(numlotASortir + 1, 3).Value - qté2
                    qté2 = 0
                End If
            Loop While qté2 > 0 And numlotASortir <= nblots
        End If
    Loop While qté <> 0
 
    'Totalisations
    Feuil2.Cells(nblots + 3, 4).FormulaLocal = "=somme(D" & 2 & ":D" & nblots + 1 & ")"
    Feuil2.Cells(nblots + 3, 6).FormulaLocal = "=somme(F" & 2 & ":F" & nblots + 1 & ")"
End Sub

Approfondir le sujet : programmer en VBA

Share Button
The following two tabs change content below.
Benoît RIVIERE
Après seize années passées en cabinet d’expertise-comptable et de commissariat aux comptes (où j’ai exercé comme expert-comptable et chef de mission audit), j’ai pris le poste de directeur comptable d’un groupe de distribution automobile en novembre 2014. Au cours de ma carrière, j’ai acquis une expérience significative en audit et en exploitation des systèmes d’information (analyse de données, automatisation des tâches, programmation informatique) au service de la production des comptes annuels et consolidés. C’est cette expérience personnelle et ma passion pour l’informatique que je partage sur ce blog. Mon CV / Réalisations personnelles et projets informatiques / Ma collection / Me contacter

4 commentaires

  1. Bonjour Monsieur,

    Je suis actuellement en troisième année de licence de gestion en IAE.

    Je suis tombé sur votre article concernant votre macro VBA permettant de valoriser les stocks à l’aide de la méthode PEPS.

    Je n’ai malheureusement pas trouvé d’article sur la méthode CMUP (à chaque fin de période).
    Possédez-vous un exemple de macro concernant cette méthode ?

    Bien cordialement
    Luca BARTHEL

  2. Bonjour,
    Merci pour votre partage.
    Il me semble que les sorties ne sont pas valorisées au PEPS par la macro.
    Auriez vous SVP le programme qui permette de valoriser les sorties ?
    Données d’entrée : achats Q*PU à différentes périodes / Quantités sorties à différentes dates
    Données de sortie (via VBA) : Sorties valorisées au PEPS par date
    Respectueusement,
    JM

  3. Bonjour,
    Le but de ce code VBA est bien de valoriser un stock selon la méthode PEPS, pas de valoriser les sorties.
    Pour valoriser les sorties, il faudrait modifier le code, ce qui ne semble pas très compliqué.
    Cordialement,
    B. RIVIERE

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.