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
Derniers articles parBenoît RIVIERE (voir tous)
- Interpréteur de formules de calcul en Python - dimanche 13 octobre 2024
- Les données de la facturation électronique - mercredi 9 octobre 2024
- VBA/SQL vs Power Query : deux solutions complémentaires - mercredi 2 octobre 2024
- L’IA dans les cabinets comptables : cas concrets - jeudi 26 septembre 2024
- EXCEL : insérer une image ou un logo dans une cellule - lundi 16 septembre 2024
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
Bonjour,
Non désolé, je n’ai pas de macro pour calculer le CUMP. Il est toutefois plus aisé de calculer un CUMP que le PEPS.
Cordialement,
Benoît RIVIERE
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
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