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) :

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

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

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

The following two tabs change content below.

• Bio
• Derniers articles

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

Derniers articles parBenoît RIVIERE (voir tous)

• Excel : collecter des données juridiques sur les entreprises avec l’API SIRENE – 1ère partie : base SIREN (entreprises) - lundi 15 avril 2024
• Obtenir les données financières de sociétés au format Excel - mardi 2 avril 2024
• Robotic Process Automation (RPA) et Python : piloter des applications web - dimanche 24 mars 2024
• Initiation à la programmation en Basic : nouveau compilateur FreeBASIC - lundi 18 mars 2024
• Retrogaming : (re)jouer aux jeux vidéo d’antan - lundi 11 mars 2024