XL 2013 calcul PMP

[fm1616]

Bonsoir á ttes et á ts.

J'ai besoin de contrôler l'évolution du PMP de plusieurs articles.
Pourriez-vous m'aider à trouver la formule svp ?
Je vous joins un fichier exemple.

Merci d'avance.

Cdlt.

 

[R@chid]

Bonsoir,
pourrais-tu nous dire que veut dire PMP?
et si c'est possible la formule mathématique et nous on va la traduire en Excel ?

 

[soan]

Bonjour R@chid, fm1616,

comme l'exo est sur la gestion de stocks, je pense que PMP = Prix Moyen Pondéré.

pour la formule mathématique, je laisse fm1616 nous l'indiquer.

soan

 

[soan]

@R@chid, fm1616,

en fait, je crois que c'est plutôt la méthode du CUMP = Coût Unitaire Moyen Pondéré.

si c'est bien ça, alors le calcul est :

        Coût d’achat du stock initial + Coût d’achat de la nouvelle entrée en stock
CUMP = ──────────────────────────────────────────────────────────────────────────────
           Quantité initialement en stock + Quantité nouvellement entrée en stock

on calcule ce CUMP de sortie après chaque entrée de stock ; plus d'infos ICI.

voici encore un autre lien.

---

sur le fichier de fm1616 : les Achats sont les Entrées de stock, et les Ventes sont les Sorties de stock.

soan

 

[fm1616]

Bonsoir.
Le Prix Moyen Pondéré dépend de la quantité et du nombre de pièces achetées (entrées) ainsi que du nombre de pièces vendues (sorties).

Exemple :
Stock initial de 10 piéces - valeur du stock : 120 € -> PMP : 120/10 = 12 €
Vente de 2 pièces - valeur du stock : 120-2x12 = 96 € -> PMP : 96/8 = 12 €
Achat de 5 pièces à 14 € (valeur : 5x14 = 70 €) - valeur du stock : 96+70 = 166 € - nombre de piéces : 8+5 = 13 -> PMP : 166/13 = 12,77 €

J'ai besoin de voir l'évolution du PMP au fur et à mesure des mouvements d'entrées et de sorties.
Si vous avez besoin de plus d'explications, n´hésitez pas.

Cdlt.

 

[JHA]

Bonjour à tous,

Un début de piste à vérifier,

JHA

 

[soan]

Bonjour fm1616, le fil,

j'ai bien lu ton post #5 ; c'est bien : tu as été très clair ! ​

---

dans le fichier joint de ton post #1 :

c'est ok pour les quantités du mouvement et du stock restant. (colonnes D et E)​

MAIS la colonne F "prix" est incohérente !

en F6, il y a 17,38 ; dans la barre de formule, c'est affiché : 17,375 ; donc ça a l'air d'un prix unitaire moyen, pas d'un prix total ! et si c'est le cas, il ne devrait y avoir des prix unitaires que pour les mouvements de type "achat" (stock initial ou achats suivants), pas pour les mouvements de type "vente" puisque justement, l'exo est pour déterminer le PMP adéquat !

si 17,38 est un prix total et pas un prix unitaire, alors pour une quantité de 8, le prix unitaire serait de 17,38 / 8 = 2,1725 ; est-ce qu'il s'agit vraiment d'articles de moins de 3 € ? ou c'est plutôt : prix unitaire = 17,38 ➯ prix total = 8 × 17,38 = 139,04 ; mais dans ce cas, on retombe sur l'incohérence mentionnée dans le paragraphe précédent : il ne devrait pas y avoir de prix unitaire pour les ventes !

donc ta colonne F "prix", elle est pour le prix unitaire du mouvement ? pour le prix total du mouvement ? ou pour le prix total du stock après le mouvement ?
​

---

pour info : j'ai déjà commencé à faire un début de solution, mais pour la suite, je suis bloqué à cause de ces incohérences de la colonne F !
​

soan

 

[fm1616]

Bonsoir.
Le problème de la solution de JHA réside dans le fait que les ventes ne doivent influer sur le PMP : la vente d'un article fait varier la valeur du stock mais la quantité d'article variant aussi, le PMP reste inchangé.
Je me suis tout de même inspiré de ses formules pour résoudre le problème du passage d'un article à un autre : en effet, avec mes formules, le calcul de la valeur du stock se fait en réference à la ligne précédente, ce qui pose un problème quand on change de code article.
Je pense avoir trouvé la solution (en vert dans la PJ).
Si vous voulez me donner votre avis, il sera le bienvenu.

PS : je ne maîtrise pas bien les fonctions utilisées par JHA. J'ai essayé de faire avec mes moyens. Mais si quelqu'un peut corriger la proposition de JHA et m'expliquer le fonctionnement de ses formules, je suis preneur.

Merci encore pour tout.

 

[soan]

Bonjour,

fm1616 a écrit : « Le problème de la solution de JHA réside dans le fait que les ventes ne doivent (pas) influer sur le PMP : la vente d'un article fait varier la valeur du stock mais la quantité d'article variant aussi, le PMP reste inchangé. »

c'est exact, et je l'avais bien compris ; si le sieur fm1616 n'avait pas zappé (volontairement ou non) mon post #7, et s'il avait répondu à ce qui concerne les incohérences de la colonne F, j'aurais pu avancer la solution que j'avais déjà commencée à son intention. ​

---

moi, je n'ai pas zappé son post #8 ; mais le fichier joint de ce post, qui est aussi la solution de JHA, ne permet toujours pas de répondre au sujet des incohérences de la colonne F ; d'ailleurs, preuve en est les nombres négatifs en rouge de la colonne J "PMP" : la valeur d'un PMP ne peut pas être négative, car ça signifierait que la valeur du stock est passée en négatif ! or c'est strictement impossible !
​

---

d'abord en Quantité : s'il reste par exemple 10 articles en stock, on peut retirer au maximum 10 articles ; il restera alors 0 article : y'a plus aucun article en stock = stock épuisé ; essayez donc de retirer 11 articles au lieu de 10 ! si vous y arrivez, moi je dis que c'est de la magie !

ou peut-être qu'un article auparavant subtilisé et noté ensuite en « produit démarqué » (= euphémisme pour dire produit volé) a été miraculeusement restitué par un voleur qui a été pris d'un remords de conscience, et qui s'est repenti ? sait-on jamais ? le voleur a peut-être reçu la visite d'un ange qui lui a fait comprendre qu'il était à la croisée des chemins, et que c'était sa dernière chance d'éviter le début de la chute du côté de la mauvaise pente ? (mais on n'est pas dans "Les routes du Paradis" ; ni dans "Les anges du bonheur")

surtout, ne venez pas me dire que j'invente : en colonne H "stock restant", ligne 71 : quantité du stock restant = -9 ! ouaf ! c'est aussi absurde qu'un solde de compte Caisse négatif ! essayez de retirer 200 € de la Caisse alors qu'y'a seulement un billet de 100 € dans le tiroir-caisse ! à moins d'être Jésus et de faire comme la multiplication des pains et des poissons... on peut toujours rêver, hein ? moi ça m'arrangerait bien si j'pouvais faire ça au DAB de ma Banque !

@JHA : je précise que ce n'est pas la faute de tes formules ; c'est la faute des données incohérentes de la colonne F, dans le fichier initial de fm1616.
​

---

ensuite en Valeur : ça signifierait que la sortie a été valorisée à un coût tellement élevé que la valeur du stock est devenue négative ! or d'après la formule de calcul d'un CMUP (idem qu'un PMP), c'est tout simplement mathématiquement impossible ! ​

---

si fm1616 zappe aussi ce post, c'est tout à fait son droit ; libre à lui de lire mes posts ou non ; mais en l'état actuel des choses, j'suis curieux d'savoir comment il va s'en sortir ! d'après moi, s'il continue dans le même sens, il est vraiment pas sorti d'l'auberge ! ​

soan

 

[fm1616]

Bonjour Soan.

dans le fichier joint du post#1, la colonne H correspond au prix unitaire du mouvement : le prix d'achat ou le prix de vente selon.
Je pensais que c'était clair dans la mesure ou j'avais laissé en blanc la colonne G correspondant au PMP.

Bonne journée.

 

[JHA]

Bonjour à tous,

Une autre proposition sans grande conviction.

JHA

 

[Victor21]

Bonjour à tous.

Une proposition à vérifier avec sommeprod()
Les mouvements doivent être classés chronologiquement; Inutile de les classer par article.
Les mouvements doivent être logiques (on ne peut soustraire 2 de 1, sinon valeur PMP négative erronée)

 

[soan]

Bonjour fm1616, le fil,

dans le fichier joint du post#1, la colonne H correspond au prix unitaire du mouvement : le prix d'achat ou le prix de vente selon.

y'a une petite erreur : c'est pas en colonne H mais en colonne F ; merci quand même pour ton info : c'est le « prix unitaire du mouvement » ; ça m'a permis de finir ton exo. ​

---

IMPORTANT : cette solution n'exige PAS de faire un tri au préalable ! ni par référence d'article, ni par ordre chronologique, ni autre ➯ l'ordre des lignes est INCHANGÉ : c'est le même AVANT et APRÈS exécution de la macro. ​

---

ouvre le fichier joint ; à part G1, la colonne G est vide : c'est normal ; oh ! à part E1, la colonne E est vide ! ne t'en préoccupe pas, et fais Ctrl e ➯ travail effectué ! la colonne E a été remplie correctement ; et en colonne G, il y a un PMP pour tout mouvement de type "vente" ; j'ai fait une vérification, pour moi c'est ok.

je précise que le PMP est calculé uniquement par rapport aux mouvements de type "achat" ; le prix unitaire des ventes n'est pas utilisé ! rappel de la formule du PMP :​

       Coût d’achat du stock initial + Coût d’achat de la nouvelle entrée en stock
PMP = ──────────────────────────────────────────────────────────────────────────────
          Quantité initialement en stock + Quantité nouvellement entrée en stock

d'après cette formule, on voit bien que les ventes n'ont pas d'incidence sur le calcul du PMP ; c'est déterminé seulement à partir du 1er achat (= stock initial) et des achats suivants.
​

---

petit rappel de comptabilité analytique :

1) pour un compte de stock :
a) les entrées se font au Coût d'achat = prix d'achat + frais d'achat
b) les sorties se font au CMUP = Coût Moyen Unitaire Pondéré
(ton PMP = Prix Moyen Pondéré en est l'équivalent)

2) une fois qu'on a déterminé le Coût de Revient (CR), le Coût de Distribution (CD) est : CR + les frais de distribution ; le prix de vente (PV) est bien sûr celui choisi par le PDG ou par le service commercial ; le PV n'est pas le PMP ! c'est pour ça que pour faire ton exo, je n'ai pas utilisé le prix unitaire des ventes ; 3 cas : a) si PV > CD : Bénéfice ; b) si PV < CD : Perte (= dumping si c'est volontaire, mais c'est normalement illégal !) ; c) si PV = CD, y'a ni Bénéfice, ni Perte.
​

---

maintenant, c'est à toi de vérifier tous les PMP. ​

(si tout est ok, tu pourras marquer ce post comme solution)

---

code VBA de Module1 :

Option Explicit

Sub Essai()
  Dim n1&: n1 = Cells(Rows.Count, 1).End(3).Row: If n1 = 1 Then Exit Sub
  Dim T, QM%, QT%, QA#, PU#, MT#, ref$, mvt$, n2&, i&, j&
  Application.ScreenUpdating = 0: Range("E:E, G:H").Columns.ClearContents
  [E1] = "stock restant": [G1] = "PMP": n1 = n1 - 1: T = [A2].Resize(n1, 8)
  Do
    'recherche d'une 1ère référence
    ref = "": i = 1
    Do
      If ref = "" And T(i, 8) = 0 And T(i, 1) = "achat" Then
        ref = T(i, 3): QT = T(i, 4): T(i, 5) = QT: QA = QT: PU = T(i, 6)
        MT = QT * PU: T(i, 8) = 1: n2 = n2 + 1: j = i + 1
      Else
        i = i + 1
      End If
    Loop Until i > n1
    'traitement de toutes les lignes de la 1ère référence ci-dessus
    For i = j To n1
      If ref <> "" And T(i, 3) = ref And T(i, 8) = 0 Then
        mvt = T(i, 1): QM = T(i, 4)
        If mvt = "achat" Then
          QT = QT + QM: MT = MT + QM * T(i, 6): QA = QA + QM
          If QA <> 0 Then PU = Round(MT / QA, 5)
        Else
          QT = QT - QM: T(i, 7) = PU
        End If
        T(i, 5) = QT: T(i, 8) = 1: n2 = n2 + 1
      End If
    Next i
  Loop Until n2 = n1
  [E2].Resize(n1) = Application.Index(T, Evaluate("Row(" & "1:" & n1 & ")"), 5)
  [G2].Resize(n1) = Application.Index(T, Evaluate("Row(" & "1:" & n1 & ")"), 7)
End Sub

---

edit : j'ai ajouté une petite optimisation dans le fichier de mon post #15.

soan

 

[fm1616]

Super !!!
Merci Soan.

Je regarde ça et je reviens vers toi.

Bonne journée.

 

[soan]

Bonjour fm1616, le fil,

j'ai oublié de faire cette petite optimisation :

pour la ligne qui est juste avant le Else inclus dans le Do .. Loop
ajoute au bout : Exit Do ; ce qui donne :

MT = QT * PU: T(i, 8) = 1: n2 = n2 + 1: j = i + 1: Exit Do

---

cette partie de code VBA est donc :

    'recherche d'une 1ère référence
    ref = "": i = 1
    Do
      If ref = "" And T(i, 8) = 0 And T(i, 1) = "achat" Then
        ref = T(i, 3): QT = T(i, 4): T(i, 5) = QT: QA = QT: PU = T(i, 6)
        MT = QT * PU: T(i, 8) = 1: n2 = n2 + 1: j = i + 1: Exit Do
      Else
        i = i + 1
      End If
    Loop Until i > n1

ça améliore juste un peu la vitesse d'exécution, car ça sort de la boucle dès qu'on a trouvé un premier achat (= stock initial) ; les résultats seront tous exactement les mêmes.​

---

d'autre part, tu seras sûrement intéressé par le fichier de ce post. peut-être même que tu aimerais avoir le même genre de présentation pour ton fichier ? (et c'est aussi bien plus complet !)​

soan