{"id":633,"date":"2011-06-07T02:00:20","date_gmt":"2011-06-07T00:00:20","guid":{"rendered":"http:\/\/www.auditsi.eu\/?p=633"},"modified":"2015-02-15T11:25:46","modified_gmt":"2015-02-15T10:25:46","slug":"excel-vba-valoriser-un-stock-selon-la-methode-du-peps-a-partir-des-mouvements-dune-reference-donnee","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=633","title":{"rendered":"EXCEL, VBA : Valoriser un stock selon la m\u00e9thode du PEPS (FIFO) \u00e0 partir des mouvements d’une r\u00e9f\u00e9rence donn\u00e9e"},"content":{"rendered":"

Le contr\u00f4le des valorisations de stocks selon la m\u00e9thode du CUMP (co\u00fbt unitaire moyen pond\u00e9r\u00e9) ne pose g\u00e9n\u00e9ralement pas de difficult\u00e9 majeure sous EXCEL pour l’auditeur. La m\u00e9thode du PEPS (Premier Entr\u00e9 Premier Sorti ou FIFO en anglais) est quant \u00e0 elle moins facile \u00e0 mettre en \u0153uvre. La proc\u00e9dure fournie ci-apr\u00e8s se propose de calculer la valorisation d\u2019une r\u00e9f\u00e9rence de stock donn\u00e9e \u00e0 partir\u00a0des mouvements entr\u00e9es\/sorties.<\/p>\n

Exemple de mouvements entr\u00e9es\/sorties\u00a0(onglet Feuil1) :<\/span><\/span><\/em><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
Date mvt<\/span><\/span><\/td>\nQt\u00e9<\/span><\/span><\/td>\nPU<\/span><\/span><\/td>\n<\/tr>\n
01\/01\/2010<\/span><\/span><\/td>\n10<\/span><\/span><\/td>\n12<\/span><\/span><\/td>\n<\/tr>\n
01\/02\/2010<\/span><\/span><\/td>\n25<\/span><\/span><\/td>\n13,5<\/span><\/span><\/td>\n<\/tr>\n
23\/02\/2010<\/span><\/span><\/td>\n-30<\/span><\/span><\/td>\n17<\/span><\/span><\/td>\n<\/tr>\n
01\/03\/2010<\/span><\/span><\/td>\n50<\/span><\/span><\/td>\n14<\/span><\/span><\/td>\n<\/tr>\n
15\/04\/2010<\/span><\/span><\/td>\n120<\/span><\/span><\/td>\n13,8<\/span><\/span><\/td>\n<\/tr>\n
18\/04\/2010<\/span><\/span><\/td>\n-70<\/span><\/span><\/td>\n16,2<\/span><\/span><\/td>\n<\/tr>\n
19\/04\/2010<\/span><\/span><\/td>\n30<\/span><\/span><\/td>\n13,1<\/span><\/span><\/td>\n<\/tr>\n
22\/04\/2010<\/span><\/span><\/td>\n50<\/span><\/span><\/td>\n12,9<\/span><\/span><\/td>\n<\/tr>\n
25\/04\/2010<\/span><\/span><\/td>\n-10<\/span><\/span><\/td>\n15<\/span><\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\u00a0(quantit\u00e9s positives = entr\u00e9es en stocks, quantit\u00e9s n\u00e9gatives = sorties)<\/span><\/p>\n

Valorisation calcul\u00e9e par la proc\u00e9dure (onglet Feuil2)\u00a0:<\/span><\/span><\/em><\/p>\n\n\n\n\n\n\n\n\n\n\n\n
N\u00b0 lot<\/span><\/span><\/td>\nQt\u00e9 Entr\u00e9es<\/span><\/span><\/td>\nQt\u00e9 Sorties<\/span><\/span><\/td>\nSolde Qt\u00e9<\/span><\/span><\/td>\nPU achat<\/span><\/span><\/td>\nValo stock final<\/span><\/span><\/td>\n<\/tr>\n
Lot n\u00b0 1<\/span><\/span><\/td>\n10<\/span><\/span><\/td>\n-10<\/span><\/span><\/td>\n0<\/span><\/span><\/td>\n12<\/span><\/span><\/td>\n0<\/span><\/span><\/td>\n<\/tr>\n
Lot n\u00b0 2<\/span><\/span><\/td>\n25<\/span><\/span><\/td>\n-25<\/span><\/span><\/td>\n0<\/span><\/span><\/td>\n13,5<\/span><\/span><\/td>\n0<\/span><\/span><\/td>\n<\/tr>\n
Lot n\u00b0 3<\/span><\/span><\/td>\n50<\/span><\/span><\/td>\n-50<\/span><\/span><\/td>\n0<\/span><\/span><\/td>\n17<\/span><\/span><\/td>\n0<\/span><\/span><\/td>\n<\/tr>\n
Lot n\u00b0 4<\/span><\/span><\/td>\n120<\/span><\/span><\/td>\n-25<\/span><\/span><\/td>\n95<\/span><\/span><\/td>\n14<\/span><\/span><\/td>\n1330<\/span><\/span><\/td>\n<\/tr>\n
Lot n\u00b0 5<\/span><\/span><\/td>\n30<\/span><\/span><\/td>\n0<\/span><\/span><\/td>\n30<\/span><\/span><\/td>\n13,8<\/span><\/span><\/td>\n414<\/span><\/span><\/td>\n<\/tr>\n
Lot n\u00b0 6<\/span><\/span><\/td>\n50<\/span><\/span><\/td>\n0<\/span><\/span><\/td>\n50<\/span><\/span><\/td>\n16,2<\/span><\/span><\/td>\n810<\/span><\/span><\/td>\n<\/tr>\n
<\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/tr>\n
<\/td>\n<\/td>\n<\/td>\n175<\/span><\/span><\/td>\n<\/td>\n2554<\/span><\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Code source de la proc\u00e9dure de calcul\u00a0:<\/span><\/span><\/em><\/p>\n

'Calcul de la valorisation d'une r\u00e9f\u00e9rence de stock selon la m\u00e9thode du PEPS \u00e0 partir des mouvements E\/S de stocks\r\n'Ecrit par Beno\u00eet-Ren\u00e9 RIVIERE\r\n \r\n'Contenu des onglets :\r\n'Feuil1 : mouvements de stock d'une r\u00e9f\u00e9rence\r\n'Feuil2 : valorisation du stock final de la r\u00e9f\u00e9rence\r\n \r\nOption Explicit\r\n \r\nSub PEPS()\r\n'Calcul le stock final selon la m\u00e9thode du PEPS (attention : ne g\u00e8re pas les stocks n\u00e9gatifs)\r\n    Dim nblots As Integer\r\n    Dim numlotASortir As Integer\r\n    Dim Qt\u00e9Lot(1 To 50000) As Integer\r\n    Dim numligne As Variant\r\n    Dim qt\u00e9 As Double\r\n    Dim qt\u00e92 As Double\r\n    Dim PU As Double\r\n       \r\n    'Ent\u00eate des valorisations\r\n    Feuil2.Cells(1, 1).Value = \"N\u00b0 lot\"\r\n    Feuil2.Cells(1, 2).Value = \"Qt\u00e9 Entr\u00e9es\"\r\n    Feuil2.Cells(1, 3).Value = \"Qt\u00e9 Sorties\"\r\n    Feuil2.Cells(1, 4).Value = \"Solde Qt\u00e9\"\r\n    Feuil2.Cells(1, 5).Value = \"PU achat\"\r\n    Feuil2.Cells(1, 6).Value = \"Valo stock final\"\r\n   \r\n    numligne = 1\r\n    nblots = 0\r\n    numlotASortir = 1\r\n    Do\r\n        numligne = numligne + 1\r\n        qt\u00e9 = Feuil1.Cells(numligne, 2).Value\r\n        If qt\u00e9 > 0 Then\r\n            nblots = nblots + 1\r\n            Qt\u00e9Lot(nblots) = qt\u00e9\r\n            Feuil2.Cells(nblots + 1, 1).Value = \"Lot n\u00b0 \" & nblots\r\n            Feuil2.Cells(nblots + 1, 2).Value = qt\u00e9\r\n            Feuil2.Cells(nblots + 1, 3).Value = 0\r\n            Feuil2.Cells(nblots + 1, 4).FormulaLocal = \"=somme(B\" & nblots + 1 & \":C\" & nblots + 1 & \")\"\r\n            Feuil2.Cells(nblots + 1, 5).FormulaLocal = \"=Feuil1!C\" & nblots + 1\r\n            Feuil2.Cells(nblots + 1, 6).FormulaLocal = \"=D\" & nblots + 1 & \"*E\" & nblots + 1\r\n        ElseIf qt\u00e9 < 0 Then\r\n            qt\u00e92 = -qt\u00e9\r\n            Do\r\n                If qt\u00e92 > Qt\u00e9Lot(numlotASortir) Then\r\n                    qt\u00e92 = qt\u00e92 - Qt\u00e9Lot(numlotASortir)\r\n                    Feuil2.Cells(numlotASortir + 1, 3).Value = Feuil2.Cells(numlotASortir + 1, 3).Value - Qt\u00e9Lot(numlotASortir)\r\n                    Qt\u00e9Lot(numlotASortir) = 0\r\n                    numlotASortir = numlotASortir + 1\r\n                Else:\r\n                    Qt\u00e9Lot(numlotASortir) = Qt\u00e9Lot(numlotASortir) - qt\u00e92\r\n                    Feuil2.Cells(numlotASortir + 1, 3).Value = Feuil2.Cells(numlotASortir + 1, 3).Value - qt\u00e92\r\n                    qt\u00e92 = 0\r\n                End If\r\n            Loop While qt\u00e92 > 0 And numlotASortir <= nblots\r\n        End If\r\n    Loop While qt\u00e9 <> 0\r\n \r\n    'Totalisations\r\n    Feuil2.Cells(nblots + 3, 4).FormulaLocal = \"=somme(D\" & 2 & \":D\" & nblots + 1 & \")\"\r\n    Feuil2.Cells(nblots + 3, 6).FormulaLocal = \"=somme(F\" & 2 & \":F\" & nblots + 1 & \")\"\r\nEnd Sub<\/pre>\n
Approfondir le sujet : programmer en VBA<\/a><\/p>\n
\"Share<\/a>