Calculer une moyenne pondérée (exemple appliqué au CUMP)

Le calcul de moyennes pondérées est d’usage courant à l’image de la moyenne de notes à un examen (du DEC par exemple) ou du coût unitaire moyen pondéré (CUMP) de références en stock.

Calcul CUMP

Le calcul de la moyenne arithmétique (obtenue par la formule : totalisation / dénombrement) est assez simple à obtenir. Excel propose une fonction attitrée : MOYENNE(plage de cellules). Dans notre exemple : 5 + 3 + 2 = 10 € / 3, soit une moyenne de 3,33 €

La moyenne pondérée se calcule en additionnant la multiplication opérée entre les valeurs (ici la quantité) et leur coefficient de pondération (ici le prix unitaire), cette totalisation étant ensuite divisée par la somme des valeurs (ici les quantités). Dans notre exemple, la moyenne pondérée se détermine ainsi : (18 x 5 € + 15 x 3 €) /45… soit une moyenne pondérée (ou CUMP) de 3,53 €.

Excel n’offre aucune fonction dédiée mais l’usage de la fonction SOMMEPROD(plage de cellules;plage de cellules 2) associée à SOMME(plage de cellules) résout aisément ce calcul. Dans le cas qui nous occupe, la formule de calcul du CUMP est rédigée ainsi :

=SOMMEPROD(M7:M9;L7:L9)/SOMME(L7:L9)

La fonction SOMMEPROD renvoie la somme des produits des plages ou matrices spécifiées (plus d’informations sur cette fonction sur le site de Microsoft). Dans notre exemple, la formule calcule la somme des produits (SOMMEPROD) entre la plage M7:M9 (prix unitaires) et L7:L9 (quantités) divisée par la somme des quantités (SOMME(L7:L9)).

Ce même calcul de CUMP peut être obtenu à partir d’une requête SQL :

SELECT ACHATS.Réf, Sum([prix]*[quantité]/[totalqté]) AS CUMP
FROM (SELECT ACHATS.Réf, Sum(ACHATS.Quantité) AS TotalQté
FROM ACHATS
GROUP BY ACHATS.Réf) AS TOTALQTE INNER JOIN ACHATS ON TOTALQTE.Réf = ACHATS.Réf
GROUP BY ACHATS.Réf;

La sous-requête regroupement (SELECT ACHATS.Réf, Sum(ACHATS.Quantité) AS TotalQté calcule la totalisation des quantités (ici 45) puis la requête détermine le CUMP à l’aide de la formule suivante :

Sum([prix]*[quantité]/[totalqté]) AS CUMP

La requête SQL renvoie le même CUMP que la formule Excel, soit 3,53 € :

Calcul CUMP SQL

Approfondir le sujet : EXCEL, VBA : Valoriser un stock selon la méthode du PEPS (FIFO) à partir des mouvements d’une référence donnée

Share Button
The following two tabs change content below.
Benoît RIVIERE
Après seize années passées en cabinet d’expertise-comptable et de commissariat aux comptes (où j’ai exercé comme expert-comptable et chef de mission audit), j’ai pris le poste de directeur comptable d’un groupe de distribution automobile en novembre 2014. Au cours de ma carrière, j’ai acquis une expérience significative en audit et en exploitation des systèmes d’information (analyse de données, automatisation des tâches, programmation informatique) au service de la production des comptes annuels et consolidés. C’est cette expérience personnelle et ma passion pour l’informatique que je partage sur ce blog. Mon CV / Réalisations personnelles et projets informatiques / Ma collection / Me contacter

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.