Évaluation des stocks avec ACCESS

FIFO LIFO CMUP

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 : 3 commentaires Donner une note à l'article (5)

Article lu   fois.

L'auteur

Site personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

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.

Image non disponible

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.

Image non disponible

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 :

Image non disponible

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

Image non disponible

III-C. Le code FIFO

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
Option Compare Database
Option Explicit
Public Function CalculerPUSortieFifo(Produit As Long, _
                      NumMVT As Long, Nombre As Double) As Double
  Dim oRst As Recordset
  Dim dStockAVentiler As Double
  Dim sSQL As String
  Dim dResteAImputer As Double
  'Calcul du stock avant la sortie
  dStockAVentiler = DSum("Mvt", "tMouvements", "tMouvementsPK<" & NumMVT _
            & " AND tProduitsFK=" & Produit)
  'Création du recordset des entrées
  sSQL = "SELECT Mvt, PUEntree, StockRecalcule FROM tMouvements " _
            & "WHERE tMouvementsPK<" & NumMVT _
            & " AND tProduitsFK=" & Produit _
            & " AND Mvt>0;"
  Set oRst = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
  'Ventilation du stock par poste d'entrée
  oRst.MoveLast
  Do While dStockAVentiler > oRst("Mvt")
    dStockAVentiler = dStockAVentiler - oRst("Mvt")
    If dStockAVentiler > 0 Then oRst.MovePrevious
  Loop
  'À ce stade, on se trouve sur l'entrée la plus ancienne encore provisionnée
  'On va prélever la sortie sur ce poste et les suivants (si nécessaire)
  'Valorisation de la sortie FIFO
  dResteAImputer = Nombre
  
  If dStockAVentiler >= dResteAImputer Then 'le solde restant de cette entrée suffit
      CalculerPUSortieFifo = CalculerPUSortieFifo _
             + dResteAImputer * oRst("PUEntree")
      GoTo PrixUnitaireSortieFIFO
    Else
      CalculerPUSortieFifo = CalculerPUSortieFifo _
             + dStockAVentiler * oRst("PUEntree")
      dResteAImputer = dResteAImputer - dStockAVentiler
      oRst.MoveNext
  End If
  Do
    
    If dResteAImputer > oRst("Mvt") Then
        CalculerPUSortieFifo = CalculerPUSortieFifo _
                             + oRst("Mvt") * oRst("PUEntree")
        dResteAImputer = dResteAImputer - oRst("Mvt")
        
    
      Else
        CalculerPUSortieFifo = CalculerPUSortieFifo _
                             + (dResteAImputer * oRst("PUEntree"))
        Exit Do
    End If
    oRst.MoveNext
  Loop
PrixUnitaireSortieFIFO:
  CalculerPUSortieFifo = CalculerPUSortieFifo / Nombre
  'Libérer la mémoire
  oRst.Close
  Set oRst = Nothing
End Function

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

Image non disponible
Cliquez sur l'image pour l'agrandir
 
Sélectionnez
SELECT tMouvements.tMouvementsPK, tMouvements.tProduitsFK, tMouvements.MvtDate, tMouvements.Mvt, tMouvements.PUEntree, IIf([Mvt]<0,CalculerPUSortieFiFo([tProduitsFK],[tMouvementsPK],-[mvt]),"") AS PUSortieFIFO
FROM tMouvements;
Image non disponible
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

Image non disponible
Cliquez sur l'image pour l'agrandir

IV-C. Le code LIFO

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
Function CalculerPUSortieLifo(Produit As Long, NumMVT As Long, Nombre As Double) As Double
  Dim oRst As Recordset
  Dim dResteAImputer As Double
  Dim sSQL As String
  Dim dCumulSorties As Double
  'Créer le recordset pour lire les mouvements qui précèdent
  sSQL = "SELECT Mvt, PUEntree FROM tMouvements" _
        & " WHERE tMouvementsPK <" & NumMVT & " And tProduitsFK =" & Produit & ";"
  Set oRst = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset)
  oRst.MoveLast
  dResteAImputer = Nombre
  Do While Not oRst.EOF
    If oRst("Mvt") < 0 Then
        'Sortie => il faut cumuler pour l'imputer à l'entrée qui précède
        dCumulSorties = dCumulSorties + oRst("Mvt")
      Else
        'C'est donc une entrée.
        'Reste-t-il du disponible ?
        If oRst("Mvt") + dCumulSorties > 0 Then
        'si oui De 2 choses l'une :
        '- ou bien elle suffit pour alimenter la sortie
            If dResteAImputer <= oRst("Mvt") + dCumulSorties Then
                CalculerPUSortieLifo = CalculerPUSortieLifo + _
                              (dResteAImputer * oRst("PUEntree"))
                Exit Do
        '- ou bien,il n'y a pas encore assez et il faut continuer
              Else
                CalculerPUSortieLifo = CalculerPUSortieLifo + _
                              (oRst("Mvt") + dCumulSorties) * oRst("PUEntree")
                dResteAImputer = dResteAImputer - (oRst("Mvt") + dCumulSorties)
               dCumulSorties = 0
            End If
          Else
        'Sinon, il faut retenir le solde négatif dans dSortiesImputées et passer au suivant
            dCumulSorties = oRst("Mvt") + dCumulSorties
        End If
    End If
    oRst.MovePrevious
  Loop
  'Prix unitaire sortie
  CalculerPUSortieLifo = CalculerPUSortieLifo / Nombre
  'Libérer la mémoire
  oRst.Close
  Set oRst = Nothing
End Function

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

Image non disponible
Cliquez sur l'image pour l'agrandir
 
Sélectionnez
SELECT tMouvements.tMouvementsPK, tMouvements.tProduitsFK, tMouvements.MvtDate, tMouvements.Mvt, tMouvements.PUEntree, IIf([Mvt]<0,CalculerPUSortieLiFo([tProduitsFK],[tMouvementsPK],-[mvt]),"") AS PUSortieLIFO
FROM tMouvements;
Image non disponible
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.

Image non disponible

V-B. Algorithme CMUP

Image non disponible
Cliquez sur l'image pour l'agrandir

V-C. Le code CMUP

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
Public Function CalculerCMUP(Produit As Long, NumMVT As Long, _
                               Optional Nombre As Double, _
                               Optional PU As Double) As Double
  On Error GoTo GestionErreurs
  Dim oRst As Recordset
  Dim sSQL As String
  Dim dSorties As Double
  Dim dStockDernEntree As Double
  Dim dNouveauCMUP As Double
    'Créer le Recordset de tMouvements
  Set oRst = CurrentDb.OpenRecordset("SELECT * FROM tMouvements " _
                                       & "WHERE tProduitsFK=" _
                                       & Produit & " AND tMouvementsPK<" _
                                       & NumMVT & ";", dbOpenDynaset)
  'Cumul des sorties depuis la dernière entrée
  oRst.MoveLast
  Do While oRst("Mvt") < 0
    dSorties = dSorties + oRst("Mvt")
    oRst.MovePrevious
  Loop
  'Ici, on est sur la dernière entrée
  If Nombre > 0 Then
      'C'est donc la comptabilisation d'une nouvelle entrée
      'Nous calculons le nouveau CMUP
      ' déterminer le stock après cette dernière entrée
      dStockDernEntree = DSum("Mvt", "tMouvements", "tProduitsFK=" & Produit _
                                & " AND tMouvementsPK <=" & oRst("tMouvementsPK"))
      ' Calcul du Nouveau CMUP
      ' C'est ce qui reste du stock précédent valorisé au CMUP d'alors,
      'auquel on ajoute la nouvelle entrée (Nombre*PU)
      'le tout divisé par le nouveau stock
      CalculerCMUP = ((dStockDernEntree + dSorties) * oRst("CMUP") + (Nombre * PU)) _
                           / (dStockDernEntree + dSorties + Nombre)
    Else
      'C'est une sortie
      'Elle est valorisée au dernier CMUP calculé (celui sur lequel on est positionné
      CalculerCMUP = oRst("CMUP")
  End If
  'Libérer la mémoire
  oRst.Close
  Set oRst = Nothing
GestionErreurs:
  Select Case Err.Number
    Case 0 'pas d'erreur
    Case 3021 'première entrée
      CalculerCMUP = PU
      Set oRst = Nothing
    Case Else
      MsgBox "Erreur dans EntreeCMUP  " & Err.Number & " " & Err.Description
  End Select
End Function

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

Image non disponible
Cliquez sur l'image pour l'agrandir
 
Sélectionnez
UPDATE tMouvements SET tMouvements.CMUP = calculerCMUP([tProduitsFK],[tMouvementsPK],[Mvt],nz([PUEntree],0));
Image non disponible
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

Image non disponible

VI-C. Le code de chaque méthode

 
Sélectionnez
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 :

Image non disponible
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.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

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 ni 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.