Évaluation des stocks avec ACCESS

FIFO LIFO CMUP

Claude Leloup

Voici un court tutoriel qui propose un code VBA pour le calcul du coût moyen des sorties de stock suivant les trois méthodes généralement utilisées en comptabilité :

- premier entré, premier sorti (FIFO) ;

- dernier entré, premier sorti (LIFO) ;

- le coût moyen unitaire pondéré, recalculé après chaque entrée (CMUP).

N.B. Denis Hulo (User) a écrit une suite à ce tutoriel pour aller plus loin. Il y présente une série de tableaux de synthèse qui montrent comment les calculs sont opérés.

Pour réagir à cet article, une discussion est ouverte : Commentez

- Titre : Évaluation des stocks avec ACCESS

- Auteur : Claude Leloup

- Parution : 11 janvier 2018

- Mise à jour : 17 mai 2022

- Licence : Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright ® 2018 Claude Leloup. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. Au sujet des méthodes d'évaluation d'un stock

- II. Exemple de calcul

- III. Application de la méthode FIFO

- III-A. Analyse de la démarche FIFO

- III-B. Algorithme FIFO

- III-C. Le code FIFO

- III-D. Une requête pour afficher le prix unitaire des sorties FIFO

- IV. Application de la méthode LIFO

- IV-A. Analyse de la démarche LIFO

- IV-B. Algorithme LIFO

- IV-C. Le code LIFO

- IV-D. Une requête pour afficher le prix unitaire des sorties LIFO

- V. La méthode du CMUP

- V-A. Analyse de la démarche CMUPLe CMUP d'une sortie est celui qui a été recalculé pour l'entrée la plus récente.

- V-B. Algorithme CMUP

- V-C. Le code CMUP

- V-D. Une requête pour mettre à jour la colonne CMUP de la table tMouvements

- VI. Calculer la valeur du stock à une date donnée

- VI-A. Une astuce pour exploiter ce qui précède

- VI-B. Algorithme

- VI-C. Le code de chaque méthode

- VI-D. Exemples d'utilisation

- VII. Téléchargement

- VIII. Remerciements

I - Au sujet des méthodes d'évaluation d'un stock

Pour ce qui concerne les entrées, elles se comptabilisent au prix d'achat plus éventuellement divers frais accessoires supportés jusqu'à l'entrée au lieu d'entreposage.

Pour la valorisation des sorties, on propose classiquement trois méthodes d'évaluation :

- celle qui suppose que l'utilisation des marchandises respecte le même ordre chronologique que les entrées dans l'entrepôt. En d'autres mots, on prélève d'abord les marchandises stockées depuis plus longtemps : les premières entrées sont les premières sorties, d'où l'acronyme FIFO (First In First Out) ;

- celle qui suppose, au contraire, que ce sont les dernières entrées qui sont d'abord consommées, d'où l'acronyme LIFO (Last In First Out) ;

- celle qui consiste à recalculer à chaque nouvelle entrée, un coût moyen unitaire pondéré : CMUP.

II - Exemple de calcul

Les mouvements positifs représentent les entrées, les mouvements négatifs, les sorties.

La colonne « PU » renseigne le prix d'achat unitaire.

Pour la suite de l'exposé, nous allons supposer que, en amont dans notre application, les mouvements de stock sont enregistrés dans cette table :

Dans la colonne Mvt, un nombre positif représente une entrée, un nombre négatif représente une sortie.

La colonne PUEntree renseigne le prix unitaire à l'achat.

Le système d'encodage situé en amont garantit que :

- les opérations sont enregistrées dans l'ordre chronologique ;

- l'enregistrement d'une sortie n'est accepté que si le solde en stock est suffisant.

III - Application de la méthode FIFO

III-A - Analyse de la démarche FIFO

Il faut prélever la sortie sur le stock restant en commençant pour les plus anciennes entrées encore approvisionnées. En d'autres mots, avant d'imputer, il faut d'abord ventiler le stock restant en différents postes selon leur ancienneté, et ensuite, imputer en suivant l'ordre chronologique.

Pour valoriser une sortie, la démarche va donc comporter plusieurs étapes :

- déterminer le nombre d'unités qui étaient en stock avant de constater la sortie ;

- considérer chaque entrée en commençant par la dernière et déterminer la part de cette entrée qui subsiste en stock. (Puisque les sorties ont été imputées dans l'ordre des entrées, ce qui reste se trouve nécessairement dans les dernières entrées !) ;

- on peut alors valoriser la sortie en l'imputant sur les entrées encore provisionnées, en commençant par la plus ancienne.

III-B - Algorithme FIFO

III-C - Le code FIFO

1. Option Compare Database

2. Option Explicit

3. Public Function CalculerPUSortieFifo(Produit As Long, _

4. NumMVT As Long, Nombre As Double) As Double

5. Dim oRst As Recordset

6. Dim dStockAVentiler As Double

7. Dim sSQL As String

8. Dim dResteAImputer As Double

9. 'Calcul du stock avant la sortie

10. dStockAVentiler = DSum("Mvt", "tMouvements", "tMouvementsPK<" & NumMVT _

11. & " AND tProduitsFK=" & Produit)

12. 'Création du recordset des entrées

13. sSQL = "SELECT Mvt, PUEntree FROM tMouvements " _

14. & "WHERE tMouvementsPK<" & NumMVT _

15. & " AND tProduitsFK=" & Produit _

16. & " AND Mvt>0;"

17. Set oRst = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)

18. 'Ventilation du stock par poste d'entrée

19. oRst.MoveLast

20. Do While dStockAVentiler > oRst("Mvt")

21. dStockAVentiler = dStockAVentiler - oRst("Mvt")

22. If dStockAVentiler > 0 Then oRst.MovePrevious

23. Loop

24. 'À ce stade, on se trouve sur l'entrée la plus ancienne encore provisionnée

25. 'On va prélever la sortie sur ce poste et les suivants (si nécessaire)

26. 'Valorisation de la sortie FIFO

27. dResteAImputer = Nombre

29. If dStockAVentiler >= dResteAImputer Then 'le solde restant de cette entrée suffit

30. CalculerPUSortieFifo = CalculerPUSortieFifo _

31. + dResteAImputer * oRst("PUEntree")

32. GoTo PrixUnitaireSortieFIFO

33. Else

34. CalculerPUSortieFifo = CalculerPUSortieFifo _

35. + dStockAVentiler * oRst("PUEntree")

36. dResteAImputer = dResteAImputer - dStockAVentiler

37. oRst.MoveNext

38. End If

39. Do

41. If dResteAImputer > oRst("Mvt") Then

42. CalculerPUSortieFifo = CalculerPUSortieFifo _

43. + oRst("Mvt") * oRst("PUEntree")

44. dResteAImputer = dResteAImputer - oRst("Mvt")

47. Else

48. CalculerPUSortieFifo = CalculerPUSortieFifo _

49. + (dResteAImputer * oRst("PUEntree"))

50. Exit Do

51. End If

52. oRst.MoveNext

53. Loop

54. PrixUnitaireSortieFIFO:

55. CalculerPUSortieFifo = CalculerPUSortieFifo / Nombre

56. 'Libérer la mémoire

57. oRst.Close

58. Set oRst = Nothing

59. End Function

III-D - Une requête pour afficher le prix unitaire des sorties FIFO

Cliquez sur l'image pour l'agrandir

SELECT tMouvements.tMouvementsPK, tMouvements.tProduitsFK, tMouvements.MvtDate, tMouvements.Mvt, tMouvements.PUEntree, IIf([Mvt]<0,CalculerPUSortieFiFo([tProduitsFK],[tMouvementsPK],-[mvt]),"") AS PUSortieFIFO
FROM tMouvements;

Cliquez sur l'image pour l'agrandir

IV - Application de la méthode LIFO

IV-A - Analyse de la démarche LIFO

Pour valoriser une sortie, il faut lire à rebours tous les enregistrements qui précèdent, et prélever la sortie sur les entrées encore provisionnées.

IV-B - Algorithme LIFO

Cliquez sur l'image pour l'agrandir

IV-C - Le code LIFO

1. Function CalculerPUSortieLifo(Produit As Long, NumMVT As Long, Nombre As Double) As Double

2. Dim oRst As Recordset

3. Dim dResteAImputer As Double

4. Dim sSQL As String

5. Dim dCumulSorties As Double

6. 'Créer le recordset pour lire les mouvements qui précèdent

7. sSQL = "SELECT Mvt, PUEntree FROM tMouvements" _

8. & " WHERE tMouvementsPK <" & NumMVT & " And tProduitsFK =" & Produit & ";"

9. Set oRst = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset)

10. oRst.MoveLast

11. dResteAImputer = Nombre

12. Do While Not oRst.EOF

13. If oRst("Mvt") < 0 Then

14. 'Sortie => il faut cumuler pour l'imputer à l'entrée qui précède

15. dCumulSorties = dCumulSorties + oRst("Mvt")

16. Else

17. 'C'est donc une entrée.

18. 'Reste-t-il du disponible ?

19. If oRst("Mvt") + dCumulSorties > 0 Then

20. 'si oui De 2 choses l'une :

21. '- ou bien elle suffit pour alimenter la sortie

22. If dResteAImputer <= oRst("Mvt") + dCumulSorties Then

23. CalculerPUSortieLifo = CalculerPUSortieLifo + _

24. (dResteAImputer * oRst("PUEntree"))

25. Exit Do

26. '- ou bien,il n'y a pas encore assez et il faut continuer

27. Else

28. CalculerPUSortieLifo = CalculerPUSortieLifo + _

29. (oRst("Mvt") + dCumulSorties) * oRst("PUEntree")

30. dResteAImputer = dResteAImputer - (oRst("Mvt") + dCumulSorties)

31. dCumulSorties = 0

32. End If

33. Else

34. 'Sinon, il faut retenir le solde négatif dans dSortiesImputées et passer au suivant

35. dCumulSorties = oRst("Mvt") + dCumulSorties

36. End If

37. End If

38. oRst.MovePrevious

39. Loop

40. 'Prix unitaire sortie

41. CalculerPUSortieLifo = CalculerPUSortieLifo / Nombre

42. 'Libérer la mémoire

43. oRst.Close

44. Set oRst = Nothing

45. End Function

IV-D - Une requête pour afficher le prix unitaire des sorties LIFO

Cliquez sur l'image pour l'agrandir

SELECT tMouvements.tMouvementsPK, tMouvements.tProduitsFK, tMouvements.MvtDate, tMouvements.Mvt, tMouvements.PUEntree, IIf([Mvt]<0,CalculerPUSortieLiFo([tProduitsFK],[tMouvementsPK],-[mvt]),"") AS PUSortieLIFO
FROM tMouvements;

Cliquez sur l'image pour l'agrandir

V - La méthode du CMUP

V-A - Analyse de la démarche CMUPLe CMUP d'une sortie est celui qui a été recalculé pour l'entrée la plus récente.

Le CMUP d'une entrée se calcule en trois temps :

1° on valorise le stock restant au CMUP qui avait été calculé lors de la dernière entrée ;

2° on y ajoute la nouvelle entrée valorisée à son prix unitaire d'achat ;

3° on divise la somme obtenue en 2° par le nombre d'unités du nouveau stock.

Pour la première entrée, le CMUP est naturellement égal au prix unitaire !

Pour appliquer la méthode proposée, il est indispensable d'ajouter une colonne à la table tMouvements pour y stocker le CMUP.

V-B - Algorithme CMUP

Cliquez sur l'image pour l'agrandir

V-C - Le code CMUP

1. Public Function CalculerCMUP(Produit As Long, NumMVT As Long, _

2. Optional Nombre As Double, _

3. Optional PU As Double) As Double

4. On Error GoTo GestionErreurs

5. Dim oRst As Recordset

6. Dim sSQL As String

7. Dim dSorties As Double

8. Dim dStockDernEntree As Double

9. Dim dNouveauCMUP As Double

10. 'Créer le Recordset de tMouvements

11. Set oRst = CurrentDb.OpenRecordset("SELECT * FROM tMouvements " _

12. & "WHERE tProduitsFK=" _

13. & Produit & " AND tMouvementsPK<" _

14. & NumMVT & ";", dbOpenDynaset)

15. 'Cumul des sorties depuis la dernière entrée

16. oRst.MoveLast

17. Do While oRst("Mvt") < 0

18. dSorties = dSorties + oRst("Mvt")

19. oRst.MovePrevious

20. Loop

21. 'Ici, on est sur la dernière entrée

22. If Nombre > 0 Then

23. 'C'est donc la comptabilisation d'une nouvelle entrée

24. 'Nous calculons le nouveau CMUP

25. '1° déterminer le stock après cette dernière entrée

26. dStockDernEntree = DSum("Mvt", "tMouvements", "tProduitsFK=" & Produit _

27. & " AND tMouvementsPK <=" & oRst("tMouvementsPK"))

28. '2° Calcul du Nouveau CMUP

29. ' C'est ce qui reste du stock précédent valorisé au CMUP d'alors,

30. 'auquel on ajoute la nouvelle entrée (Nombre*PU)

31. 'le tout divisé par le nouveau stock

32. CalculerCMUP = ((dStockDernEntree + dSorties) * oRst("CMUP") + (Nombre * PU)) _

33. / (dStockDernEntree + dSorties + Nombre)

34. Else

35. 'C'est une sortie

36. 'Elle est valorisée au dernier CMUP calculé (celui sur lequel on est positionné

37. CalculerCMUP = oRst("CMUP")

38. End If

39. 'Libérer la mémoire

40. oRst.Close

41. Set oRst = Nothing

42. GestionErreurs:

43. Select Case Err.Number

44. Case 0 'pas d'erreur

45. Case 3021 'première entrée

46. CalculerCMUP = PU

47. Set oRst = Nothing

48. Case Else

49. MsgBox "Erreur dans EntreeCMUP N° " & Err.Number & " " & Err.Description

50. End Select

51. End Function

V-D - Une requête pour mettre à jour la colonne CMUP de la table tMouvements

Cliquez sur l'image pour l'agrandir

UPDATE tMouvements SET tMouvements.CMUP = calculerCMUP([tProduitsFK],[tMouvementsPK],[Mvt],nz([PUEntree],0));

Cliquez sur l'image pour l'agrandir

VI - Calculer la valeur du stock à une date donnée

VI-A - Une astuce pour exploiter ce qui précède

On va faire comme s'il s'agissait de comptabiliser une sortie de tout le stock présent à cette date.

VI-B - Algorithme

VI-C - Le code de chaque méthode

Public Function ValStockFIFO(LaDate As Date, Produit As Long) As Double
Dim dStock As Double
Dim lDernNumMvt As Long
'Quel stock à cette date
dStock = Nz(DSum("Mvt", "tMouvements", "MvtDate<=#" _
& Format(LaDate, "mm/dd/yyyy") _
& "# AND tProduitsFK=" & Produit), 0)
'tMouvementPK du dernier Mvt à cette date
lDernNumMvt = DMax("tMouvementsPK", "tMouvements", "MvtDate<=#" _
& Format(LaDate, "mm/dd/yyyy") _
& "# AND tProduitsFK=" & Produit)
'Simuler la sortie pour déterminer la valeur unitaire
ValStockFIFO = CalculerPUSortieFifo(Produit, lDernNumMvt + 1, dStock) * dStock
End Function

Public Function ValStockLIFO(LaDate As Date, Produit As Long) As Double
Dim dStock As Double
Dim lDernNumMvt As Long
'Quel stock à cette date
dStock = DSum("Mvt", "tMouvements", "MvtDate<=#" _
& Format(LaDate, "mm/dd/yyyy") _
& "# AND tProduitsFK=" & Produit)
'tMouvementPK du dernier Mvt à cette date
lDernNumMvt = DMax("tMouvementsPK", "tMouvements", "MvtDate<=#" _
& Format(LaDate, "mm/dd/yyyy") _
& "# AND tProduitsFK=" & Produit)
'Simuler la sortie pour déterminer la valeur unitaire
ValStockLIFO = CalculerPUSortieLifo(Produit, lDernNumMvt + 1, dStock) * dStock
End Function

Public Function ValStockCMUP(LaDate As Date, Produit As Long) As Double
Dim dStock As Double
Dim lDernNumMvt As Long
'Quel stock à cette date
dStock = DSum("Mvt", "tMouvements", "MvtDate<=#" _
& Format(LaDate, "mm/dd/yyyy") _
& "# AND tProduitsFK=" & Produit)
'tMouvementPK du dernier Mvt à cette date
lDernNumMvt = DMax("tMouvementsPK", "tMouvements", "MvtDate<=#" _
& Format(LaDate, "mm/dd/yyyy") _
& "# AND tProduitsFK=" & Produit)
'Simuler la sortie pour déterminer la valeur unitaire
'N.B. Dans CalculerCMUP(), une sortie = un nombre négatif => ici,moins dStock
ValStockCMUP = CalculerCMUP(Produit, lDernNumMvt + 1, -dStock) * dStock
End Function

VI-D - Exemples d'utilisation

Considérons le cas du produit 123 pour l'évaluation du stock au 30/4/17 :

Cliquez sur l'image pour l'agrandir

VII - Téléchargement

La base de données au format Access2000 est ici.

VIII - Remerciements

Merci à Denis (User) pour sa suggestion qui m'a permis de simplifier la fonction CalculerPUSortieFifo.

Merci à Chrtophe pour ses remarques.

Merci à Maxi35 pour la correction orthographique.