Évaluation des stocks avec ACCESS (aller plus loin)

Objectif : afficher sous forme tabulaire les entrées/sorties et la valorisation du stock.

Niveau requis : avancé.

Commentez cet article : 2 commentaires Donner une note à l'article (5)

Article lu   fois.

L'auteur

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Introduction

On se propose de calculer et d'afficher dans des tableaux les coûts moyens des sorties de stock, en détaillant les quantités et prix unitaires impliqués dans ces calculs, 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).
Méthodes FIFO et LIFO
Méthodes FIFO et LIFO

Pour ce faire, on se basera sur le travail effectué par Claude Leloup dans son article Évaluation des stocks avec ACCESSÉvaluation des stocks avec ACCESS.

II. Entrées et sorties du stock

Nous aurons besoin tout d'abord de regrouper les entrées de stock dans une requête rEntrees et les sorties dans une autre nommée rSorties.

Il nous suffit pour cela de filtrer les données de la table tMouvementsPK sur le champ Mvt :

  • mouvements en entrée : Mvt>0 ;
  • mouvements en sortie : Mvt<0.

III. Requêtes sélection basées sur les entrées et sorties du stock

III-A. rQteSortiesFIFO

On relie les entrées et sorties sur le champ identifiant le produit en ne conservant pour chaque sortie du produit que les entrées antérieures à elle.

Cette requête va nous permettre de calculer, suivant la méthode FIFO, pour chaque entrée associée à une sortie, la quantité que l'on retire pour cette sortie.

Voici le détail des données et résultats par mouvement en entrée accompagné de la formule associée au PU en sortie :

Requête rQteSortiesFIFO
Requête rQteSortiesFIFO

Voici la requête en mode création :

Requête rQteSortiesFIFO
Requête rQteSortiesFIFO

Filtres sur les données

  • On filtre sur le produit choisi dans le formulaire fParamPeriode.
  • On filtre sur la date de sortie en fonction du choix effectué dans ce formulaire.
  • On affiche, pour chaque sortie, uniquement les entrées antérieures à elle.

Critères correspondants

  • Comme VraiFaux([Forms]![fParamPeriode]![ParamProduit]="[Tous]";"*";[Forms]![fParamPeriode]![ParamProduit]).Claude Leloup 2018-03-02T13:58:58Le code en texte proportionnel pour le protéger des corrections automatiques de guillemets.
  • [rSorties].MvtDate between [Forms]![fParamPeriode]![ParamDateDebut] and [Forms]![fParamPeriode]![ParamDateFin].
  • rEntrees.tMouvementsPK < [rSorties].[tMouvementsPK].

Champs calculés

  • IndiceSortie : renvoie l'indice de sortie en comptant le nombre de sorties depuis la date entrée en paramètre ([Forms]![fParamPeriode]![ParamDate]).
  • QteSortie : renvoie la quantité sortie pour l'entrée affichée.
  • FormulePUSortie : donne la formule utilisée pour le calcul du prix unitaire en sortie.
Formules des champs calculés
Sélectionnez
IndiceSortie: "S" & Format(CpteDom("*";"rSorties";"tProduitsFK=" & [rSorties].[tProduitsFK] & " and tMouvementsPK<=" & [rSorties].[tMouvementsPK])-CpteDom("*";"rSorties";"tProduitsFK=" & [rSorties].[tProduitsFK] & " and MvtDate<#" & Format([Forms]![fParamPeriode]![ParamDateDebut];"mm/jj/aaaa") & "#");"00")
QteSortie: CalculerQteSortieFiFo([rEntrees].[tProduitsFK];[rEntrees].[tMouvementsPK];[rSorties].[tMouvementsPK];-[rSorties].[mvt])
FormulePUSortie: FormulePUSortieFiFo([rSorties].[tProduitsFK];[rSorties].[tMouvementsPK];-[rSorties].[mvt])

Fonctions utilisées dans les champs calculés

  • CalculerQteSortieFifo : utilisée pour le calcul de la quantité en sortie pour chaque entrée de produit impliquée dans le calcul.
  • FormulePUSortieFifo : utilisée pour générer la formule employée pour le calcul de la quantité en sortie.
fonction CalculerQteSortieFifo
CacherSélectionnez
fonction FormulePUSortieFifo
CacherSélectionnez

III-B. rQteSortiesLIFO

On relie les entrées et sorties sur le champ identifiant le produit, en ne conservant pour chaque sortie de produit que les entrées antérieures à elle.

Cette requête va nous permettre de calculer, suivant la méthode LIFO, pour chaque entrée associée à une sortie, la quantité que l'on retire pour cette sortie.

Voici le détail des données et résultats par mouvement en entrée accompagné de la formule associée au PU en sortie :

Requête rQteSortiesLIFO
Requête rQteSortiesLIFO

Voici la requête en mode création :

Requête rQteSortiesLIFO
Requête rQteSortiesLIFO

Filtres sur les données

  • On filtre sur le produit choisi dans le formulaire fParamaPeriode.
  • On filtre sur la date de sortie en fonction du choix effectué dans ce formulaire.
  • On affiche, pour chaque sortie, uniquement les entrées antérieures à elle.

Critères correspondants

  • Comme VraiFaux([Forms]![fParamPeriode]![ParamProduit]="[Tous]";"*";[Forms]![fParamPeriode]![ParamProduit]).
  • [rSorties].MvtDate between [Forms]![fParamPeriode]![ParamDateDebut] and [Forms]![fParamPeriode]![ParamDateFin].
  • rEntrees.tMouvementsPK < [rSorties].[tMouvementsPK].

Champs calculés

  • IndiceSortie : renvoie l'indice de sortie en comptant le nombre de sorties depuis la date entrée en paramètre ([Forms]![fParamPeriode]![ParamDate]).
  • QteSortie : renvoie la quantité sortie pour l'entrée affichée.
  • FormulePUSortie : donne la formule utilisée pour le calcul du prix unitaire en sortie.
Formules des champs calculés
Sélectionnez
IndiceSortie: "S" & Format(CpteDom("*";"rSorties";"tProduitsFK=" & [rSorties].[tProduitsFK] & " and tMouvementsPK<=" & [rSorties].[tMouvementsPK])-CpteDom("*";"rSorties";"tProduitsFK=" & [rSorties].[tProduitsFK] & " and MvtDate<#" & Format([Forms]![fParamPeriode]![ParamDateDebut];"mm/jj/aaaa") & "#");"00")
QteSortie: CalculerQteSortieLiFo([rEntrees].[tProduitsFK];[rEntrees].[tMouvementsPK];[rSorties].[tMouvementsPK];-[rSorties].[mvt])
FormulePUSortie: FormulePUSortieLiFo([rSorties].[tProduitsFK];[rSorties].[tMouvementsPK];-[rSorties].[mvt])

Fonctions utilisées dans les champs calculés

  • CalculerQteSortieLifo : utilisée pour le calcul de la quantité en sortie pour chaque entrée de produit impliquée dans le calcul.
  • FormulePUSortieLifo : utilisée pour générer la formule employée pour le calcul de la quantité en sortie.
fonction CalculerQteSortieLifo
CacherSélectionnez
fonction FormulePUSortieLifo
CacherSélectionnez

III-C. rPUSortiesCMUP

Cette requête basée sur les sorties de stock va nous permettre de calculer, suivant la méthode CMUP, le prix unitaire du produit pour chaque sortie.

Voici des exemples de valeurs obtenues pour le prix unitaire de chaque sortie de stock suivant la méthode CMUP :

Requête rQteSortiesCMUP
Requête rPUSortiesCMUP

Voici la requête en mode création :

Requête rPUSortiesCMUP
Requête rPUSortiesCMUP

Filtres sur les données

  • On filtre sur le produit choisi dans le formulaire fParamPeriode.
  • On filtre sur la date de sortie en fonction du choix effectué dans ce formulaire.

Critères correspondants

  • Comme VraiFaux([Forms]![fParamPeriode]![ParamProduit]="[Tous]";"*";[Forms]![fParamPeriode]![ParamProduit]).
  • [rSorties].MvtDate between [Forms]![fParamPeriode]![ParamDateDebut] and [Forms]![fParamPeriode]![ParamDateFin].

Champs calculés

  • IndiceSortie : renvoie l'indice de sortie en comptant le nombre de sorties depuis la date entrée en paramètre ([Forms]![fParamPeriode]![ParamDate]).
  • PUCMUP : renvoie le prix unitaire moyen pondéré (CMUP) fonction des mouvements antérieurs.
Formules des champs calculés
Sélectionnez
IndiceSortie: "S" & Format(CpteDom("*";"rSorties";"tProduitsFK=" & [rSorties].[tProduitsFK] & " and tMouvementsPK<=" & [rSorties].[tMouvementsPK])-CpteDom("*";"rSorties";"tProduitsFK=" & [rSorties].[tProduitsFK] & " and MvtDate<#" & Format([Forms]![fParamPeriode]![ParamDateDebut];"mm/jj/aaaa") & "#");"00")
PUCMUP: CalculerPUSortieCMUP([tProduitsFK];[tMouvementsPK])

Fonction utilisée dans le champ calculé

  • CalculerPUSortieCMUP : utilisée pour calculer le prix unitaire en sortie suivant la méthode CMUP.
fonction CalculerPUSortieCMUP
CacherSélectionnez

IV. Requêtes analyse croisée

IV-A. racFIFO

On croise les données issues de la requête rQteSortiesFIFO avec les entrées en entêtes de ligne, les indices des sorties en colonnes, et les quantités sorties à l'intersection.

Voici le détail de la requête en mode création :

Requête racFIFO
Requête racFIFO
formule du champ valeur
Sélectionnez
TotSortieFIFO: Somme(VraiFaux([QteSortie]=0;Null;[QteSortie]))

IV-A-1. Gros plan sur le champ IndiceSortie

Ce champ permet de générer des entêtes de colonne fixes en figeant les noms des sorties avec les termes successifs « S01 »,« S02 »,« S03 », …,« S10 » appliqués à la propriété « entêtes des colonnes ».

Image non disponible
entêtes des colonnes

IV-B. racLIFO

On croise les données issues de la requête rQteSortiesLIFO avec les entrées en entêtes de ligne, les indices des sorties en colonnes, et les quantités sorties à l'intersection.

Voici le détail de la requête en mode création :

Requête racLIFO
Requête racLIFO
formule du champ valeur
Sélectionnez
TotSortieLIFO: Somme(VraiFaux([QteSortie]=0;Null;[QteSortie]))

IV-B-1. Gros plan sur le champ IndiceSortie

Ce champ permet de générer des entêtes de colonne fixes en figeant les noms des sorties avec les termes successifs « S01 »,« S02 »,« S03 », …,« S10 » appliqués à la propriété « entêtes des colonnes ».

Image non disponible
entêtes des colonnes

IV-C. racCMUP

On croise les données issues de la requête rPUSortiesCMUP avec les sorties en entêtes de lignes, les indices des sorties en colonne, et les quantités sorties à l'intersection.

Voici le détail de la requête en mode création :

Requête racLIFO
Requête racCMUP
formule du champ valeur
Sélectionnez
TotSortieCMUP: Somme(VraiFaux([MvtSortie]=0;Null;[MvtSortie]))

IV-C-1. Gros plan sur le champ IndiceSortie

Ce champ permet de générer des entêtes de colonne fixes en figeant les noms des sorties avec les termes successifs « S01 »,« S02 »,« S03 », …,« S10 » appliqués à la propriété « entêtes des colonnes ».

Image non disponible
entêtes des colonnes

V. Formulaire de paramétrage des tableaux

Il va servir à définir la période de filtrage des sorties et le ou les produits à afficher sur les tableaux de synthèse.

Formulaire fParamPeriode
Formulaire fParamPeriode

V-A. Bouton de commande pour l'ouverture des états

Permet d'afficher la synthèse des sorties suivant la méthode FIFO, LIFO ou CMUP et la période choisies sur le formulaire.

Bouton de commande CmdValider
Bouton de commande CmdValider
Code sur l'événement clic du bouton CmdValider
Sélectionnez
Private Sub CmdValider_Click()
' On ouvre le tableau de synthèse de la valorisation des stocks suivant la méthode choisie dans la liste

DoCmd.Minimize ' On minimise le formulaire de paramétrage
   
Select Case Me.ParamMethode.Value
   
Case "FIFO" ' Si la méthode FIFO a été choisie
DoCmd.Close acReport, "eFIFO" ' On ferme l'état si déjà ouvert
DoCmd.OpenReport "eFIFO", acViewPreview ' On ouvre l'état en mode aperçu avant impression
   
Case "LIFO"
DoCmd.Close acReport, "eLIFO"
DoCmd.OpenReport "eLIFO", acViewPreview
   
Case "CMUP"
DoCmd.Close acReport, "eCMUP"
DoCmd.OpenReport "eCMUP", acViewPreview
   
End Select
   
End Sub

V-B. Boutons de commandes pour se déplacer d'une période à l'autre

Permet de se déplacer dans le temps et d'afficher l'état correspondant à la période choisie.

Boutons de déplacement
Boutons de déplacement

La base jointe au format accdb dispose en plus de boutons de commande sur le ruban lié aux états pour se déplacer dans le temps.

V-B-1. Bouton CmdPremier

Il permet d'afficher les premières sorties sur les tableaux de synthèse.

Procédure événementielle sur clic
Sélectionnez
Private Sub CmdPremier_Click()
' On passe aux premières sorties
CmdPrem ' Exécution de la procédure CmdPrem
End Sub
Procédure CmdPrem
CacherSélectionnez

V-B-2. Bouton CmdPrecedent

Il permet d'afficher les sorties précédentes sur les tableaux de synthèse.

Procédure événementielle sur clic
Sélectionnez
Private Sub CmdPrecedent_Click()
' On passe aux sorties précédentes
CmdPrec ' Exécution de la procédure CmdPrec
End Sub
Procédure CmdPrec
CacherSélectionnez

V-B-3. Bouton CmdSuivant

Il permet d'afficher les sorties suivantes sur les tableaux de synthèse.

Procédure événementielle sur clic
Sélectionnez
Private Sub CmdSuivant_Click()
' On passe aux sorties suivantes
CmdSuiv ' Exécution de la procédure CmdSuiv
End Sub
Procédure CmdSuiv
CacherSélectionnez

V-B-4. Bouton CmdDernier

Il permet d'afficher les dernières sorties sur les tableaux de synthèse.

Procédure événementielle sur clic
Sélectionnez
Private Sub CmdDernier_Click()
' On passe aux dernières sorties
CmdDern ' Exécution de la procédure CmdDern
End Sub
Procédure événementielle sur clic
CacherSélectionnez

VI. Tableaux de synthèse des données

Nous présentons les données dans des états sous forme tabulaire avec un regroupement par produit :

  • en entêtes de ligne les entrées du stock ;
  • en colonnes les indices des sorties du stock ;
  • à l'intersection des deux les quantités sorties ;
  • dans le pied du groupe les valeurs sorties du stock par produit (Qté, PU, Total en sortie).

VI-A. eFIFO

Tableau de synthèse de la valorisation des stocks (méthode FIFO) :

Valorisation du stock : méthode FIFO
Valorisation du stock : méthode FIFO

Le tri se fait par produit et mouvement en entrée du stock.

VI-A-1. Gros plan sur l'entête de groupe de l'état

On réalise dans les états un regroupement des données par produit sur le champ ProduitsFK. On dispose donc d'une section de groupe dans laquelle on peut afficher dans son entête les identifiants des mouvements en sortie (tMouvementsPK) pour chaque produit :

Image non disponible
entête de groupe de l'état

On met à jour les entêtes de colonne du groupe au moyen d'une procédure événementielle exécutée sur Impression de l'entête de groupe.

entêtes de colonne

  • Étiquettes pour les numéros des mouvements en sortie : « EnteteS01 », « EnteteS02 », …,« EnteteS10 » ;
  • Étiquettes pour les dates des sorties : « EnteteDS01 », « EnteteDS02 », …,« EnteteDS10 ».
Procédure événementielle sur impression de l'entête de groupe
CacherSélectionnez

VI-A-2. Gros plan sur le pied de groupe de l'état

On réalise dans les états un regroupement des données par produit sur le champ ProduitsFK. On dispose donc d'un pied de groupe dans lequel on peut afficher les valeurs du stock (Qté, PU, Total) pour chaque sortie de produit :

Pied de groupe de l'état
Pied de groupe de l'état

Formules employées pour calculer les différentes valeurs en sortie de stock suivant la méthode FIFO :

  • QteSortiei :« =Somme([S0i]) » ;
  • PUSortiei :« =Somme([S0i]*[PUEntree])/Somme([S0i]) » ;
  • Totali :=« Somme([S0i]*[PUEntree]) ».

i désigne l'indice de la colonne de sortie, il est compris entre 1 et 10 dans notre cas.

VI-B. eLIFO

Tableau de synthèse de la valorisation des stocks (méthode LIFO) :

Valorisation du stock : méthode LIFO
Valorisation du stock : méthode LIFO

Le tri se fait par produit et mouvement en entrée du stock.

VI-B-1. Gros plan sur l'entête de groupe de l'état

On réalise dans les états un regroupement des données par produit sur le champ ProduitsFK. On dispose donc d'une section de groupe dans laquelle on peut afficher dans son entête les identifiants des mouvements en sortie (tMouvementsPK) pour chaque produit :

Image non disponible
entête de groupe de l'état

On met à jour les entêtes de colonne du groupe au moyen d'une procédure événementielle exécutée sur Impression de l'entête de groupe.

entêtes de colonne

  • Étiquettes pour les numéros des mouvements en sortie : « EnteteS01 », « EnteteS02 », …,« EnteteS10 » ;
  • Étiquettes pour les dates des sorties : « EnteteDS01 », « EnteteDS02 », …,« EnteteDS10 ».
Procédure événementielle sur impression de l'entête de groupe
CacherSélectionnez

VI-B-2. Gros plan sur le pied de groupe de l'état

On réalise dans les états un regroupement des données par produit sur le champ ProduitsFK. On dispose donc d'un pied de groupe dans lequel on peut afficher les valeurs du stock (Qté, PU, Total) pour chaque sortie de produit :

Pied de groupe de l'état
Pied de groupe de l'état

Formules employées pour calculer les différentes valeurs en sortie de stock suivant la méthode LIFO :

  • QteSortiei :« =Somme([S0i]) » ;
  • PUSortiei :« =Somme([S0i]*[PUEntree])/Somme([S0i]) » ;
  • Totali :=« Somme([S0i]*[PUEntree]) ».

i désigne l'indice de la colonne de sortie, il est compris entre 1 et 10 dans notre cas.

VI-C. eCMUP

Tableau de synthèse de la valorisation des stocks (méthode CMUP) :

Valorisation du stock : méthode CMUP
Valorisation du stock : méthode CMUP

Le tri se fait par produit et mouvement de sortie du stock.

VI-C-1. Gros plan sur l'entête de groupe de l'état

On réalise dans l'état un regroupement des données par produit sur le champ ProduitsFK. On dispose donc d'une section de groupe dans laquelle on peut afficher dans son entête les indices des mouvements en sortie pour chaque produit :

Image non disponible
entête de groupe de l'état

On met à jour les entêtes de colonne du groupe au moyen d'une procédure événementielle exécutée sur Impression de l'entête de groupe.

entêtes de colonne

  • Étiquettes pour les indices des mouvements en sortie : « EnteteS01 », « EnteteS02 », …,« EnteteS10 » ;
  • Étiquettes pour les dates des sorties : « EnteteDS01 », « EnteteDS02 », …,« EnteteDS10 ».
Procédure événementielle sur impression de l'entête de groupe
CacherSélectionnez

VI-C-2. Gros plan sur le pied de groupe de l'état

On réalise dans les états un regroupement des données par produit sur le champ ProduitsFK. On dispose donc d'une section de groupe dans laquelle on peut calculer les valeurs du stock (Qté, PU, Total) pour chaque sortie de produit :

Pied de groupe de l'état
Pied de groupe de l'état

Formules employées pour calculer les différentes valeurs en sortie de stock suivant la méthode CMUP :

  • QteSortiei:« =Somme([S0i]) » ;
  • PUSortiei:« =[PUCMUP] » ;
  • Totali:« =Somme([S0i]*[PUCMUP]) ».

i désigne l'indice de la colonne de sortie, il est compris entre 1 et 10 dans notre cas.

VII. Les bases de données à télécharger

Les bases jointesstockeval sont au format mdb et accdb. La version accdb utilise le fichier ruban.xml pour afficher les boutons de commande sur le ruban.

VIII. Remerciements

Je tiens à remercier Claude Leloup pour m'avoir conseillé pour la réalisation de cet article, ainsi que pour sa relecture.

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

  

Copyright © 2017 Denis Hulo. 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. Droits de diffusion permanents accordés à Developpez LLC.