Comment calculer le MAPE pondéré dans Excel

Dans le domaine de l’économétrie appliquée, de la gestion prévisionnelle de la chaîne logistique et de la modélisation financière, l’évaluation de la précision des prédictions constitue une exigence méthodologique fondamentale. La prise de décision stratégique, qu’il s’agisse de l’optimisation des niveaux d’inventaire, de la planification des capacités de production ou de l’allocation des flux de trésorerie, repose sur la capacité des analystes à quantifier rigoureusement l’écart entre les réalisations empiriques et les projections quantitatives. Historiquement, l’indicateur le plus couramment mobilisé au sein des comités de direction a été le Mean Absolute Percentage Error (MAPE), loué pour son universalité apparente, son interprétabilité immédiate et son affranchissement des unités de mesure physiques ou monétaires.

Toutefois, la pratique statistique contemporaine a mis en exergue les faiblesses structurelles et les distorsions mathématiques inhérentes au MAPE standard, en particulier dans les contextes de demandes sporadiques, de faibles volumétries de vente ou d’asymétrie sévère des distributions de séries temporelles. Lorsque les observations réelles approchent de zéro, le MAPE traditionnel tend vers l’infini ou génère des pourcentages d’erreur démesurés qui masquent la performance réelle des algorithmes sur les articles à fort enjeu. Cette instabilité a conduit les théoriciens et les praticiens de l’analytique d’entreprise à privilégier une métrique considérablement plus robuste et représentative : l’erreur moyenne absolue pondérée en pourcentage, ou Weighted Mean Absolute Percentage Error (WMAPE).

Le tableur Microsoft Excel demeure le vecteur d’analyse et de reporting opérationnel le plus universellement déployé au sein des organisations. Maîtriser le calcul du WMAPE dans cet environnement ne relève pas d’une simple manipulation arithmétique, mais exige une compréhension exhaustive des structures de données sous-jacentes, des fonctions vectorielles avancées, des moteurs de modélisation relationnelle via Power Query et Power Pivot, ainsi que des scripts d’automatisation en Visual Basic for Applications (VBA). Le présent traité se propose d’explorer, avec une rigueur académique et une exhaustivité opératoire intégrale, les fondements mathématiques, les procédures d’implémentation et les implications décisionnelles associées au calcul du MAPE pondéré dans Excel.

1. Fondements théoriques de l’évaluation prévisionnelle et émergence du MAPE

1.1 Origine et définition formelle de l’erreur moyenne absolue en pourcentage

L’analyse quantitative des séries temporelles s’est historiquement structurée autour de métriques d’erreur absolue issues de la théorie statistique classique, telles que l’écart quadratique moyen (Root Mean Squared Error ou RMSE) et l’erreur absolue moyenne (Mean Absolute Error ou MAE). Si ces indicateurs présentaient d’excellentes propriétés mathématiques, notamment la différentiabilité pour le RMSE dans le cadre de l’optimisation par la méthode des moindres carrés, leur principale limite résidait dans leur dépendance intrinsèque à l’échelle de la variable étudiée. Un écart absolu de cent unités ne revêt pas la même gravité selon que l’on modélise la vente d’avions commerciaux ou celle de composants électroniques de grande consommation. Face à cette contrainte d’échelle, les chercheurs en prévision économique ont développé des métriques relatives, formalisant ainsi le Mean Absolute Percentage Error au cours du dernier quart du vingtième siècle.

Sur le plan mathématique, le MAPE est défini comme la moyenne arithmétique des écarts absolus en pourcentage observés sur un ensemble d’horizons temporels ou d’entités distinctes. Soit $y_t$ la valeur réelle observée à la période $t$ et $\hat{y}_t$ la valeur prédite par le modèle pour cette même période, sur un échantillon totalisant $n$ observations. L’erreur absolue relative pour chaque période s’écrit formellement sous la forme :

$$\text{APE}_t = \frac{|y_t – \hat{y}_t|}{y_t}$$

L’indicateur global du MAPE s’obtient alors par l’agrégation non pondérée de ces erreurs relatives individuelles, multipliée par cent pour exprimer le résultat sous forme de pourcentage :

$$\text{MAPE} = \frac{1}{n} \sum_{t=1}^{n} \frac{|y_t – \hat{y}_t|}{y_t} \times 100$$

Les atouts de cette formulation ont immédiatement séduit le monde professionnel. D’une part, le MAPE fournit une mesure adimensionnelle, ce qui autorise des comparaisons transversales directes entre des catégories de produits hétérogènes, des filiales géographiques aux chiffres d’affaires disparates ou des gammes temporelles distinctes. D’autre part, la normalisation en pourcentage résonne parfaitement avec le vocabulaire de la gestion d’entreprise, où les marges, les taux de croissance et les variations budgétaires sont universellement formulés sous une forme relative. Cette propriété d’invariance d’échelle apparente a fait du MAPE le standard de référence mondial pour la communication des performances des prévisions entre les équipes de science des données et les comités exécutifs.

1.2 Pertinence méthodologique du MAPE dans l’analyse empirique

Dans l’audit conventionnel des modèles de régression et de séries chronologiques, le MAPE a longtemps été considéré comme l’étalon d’évaluation par excellence, en particulier lors des compétitions internationales de prévision comme les compétitions de Makridakis (M-Competitions). L’adéquation cognitive du MAPE repose sur l’intuition managériale selon laquelle une erreur de prévision doit être pénalisée proportionnellement au volume de l’élément prédit. De surcroît, le MAPE minimise implicitement une perte liée à la médiane de la distribution relative, conférant une certaine clarté comportementale lorsque les données sont régulières, continues et exemptes d’asymétries pathologiques.

Toutefois, la validité théorique du MAPE standard repose sur des hypothèses sous-jacentes extrêmement strictes, qui sont rarement satisfaites dans les jeux de données réels d’entreprise. Pour que le MAPE constitue un estimateur statistiquement convergent et non biaisé de l’erreur prévisionnelle globale, il est impératif que toutes les valeurs réelles $y_t$ soient strictement positives et significativement éloignées de zéro ($y_t gg 0$). De plus, le processus générateur de données doit présenter une variance relative stationnaire, sans quoi la métrique tend à surestimer systématiquement l’erreur en raison de la concavité de la fonction ratio lorsque le dénominateur décroît. Dès lors que ces hypothèses de régularité sont violées, l’utilisation du MAPE sans ajustement méthodologique expose l’analyste à des distorsions d’une ampleur telle qu’elles invalident l’ensemble du processus de gouvernance de la chaîne logistique.

2. Limites structurelles et distorsions statistiques du MAPE conventionnel

2.1 L’asymétrie statistique face aux faibles volumes

L’imperfection mathématique la plus critique du MAPE conventionnel réside dans son comportement asymétrique asymétrique induit par la présence de la variable réelle $y_t$ au dénominateur de chaque terme de sommation. Lorsque $y_t$ prend une valeur infinitésimale par rapport à l’erreur commise $|y_t – \hat{y}_t|$, le ratio individuel explose vers des grandeurs disproportionnées. Par symétrie inverse, si la valeur prédite $\hat{y}_t$ sous-estime massivement la réalité ($y_t$ très grand, $\hat{y}_t = 0$), l’erreur relative maximale ne peut excéder 100 %. En revanche, en cas de surestimation ($y_t$ petit, $\hat{y}_t$ grand), l’erreur relative n’a pas de borne supérieure et peut atteindre des milliers de pourcents.

Cette asymétrie entraîne une surpondération désastreuse des articles à faible rotation (souvent dénommés références de la classe C selon la méthodologie ABC) au détriment des produits stratégiques à très fort volume (classe A). Pour illustrer analytiquement cette distorsion, considérons un portefeuille composé de deux articles distincts au sein d’une organisation :

• Article 1 (Produit stratégique à fort volume) : La demande réelle s’élève à 10 000 unités, tandis que la prévision algorithmique a anticipé 11 000 unités. L’écart absolu est de 1 000 unités. L’erreur relative individuelle s’établit à $|10,000 – 11,000| / 10,000 = 10,%$.
• Article 2 (Pièce de rechange marginale à faible volume) : La demande réelle n’est que de 2 unités, et le modèle a projeté 10 unités. L’écart absolu n’est que de 8 unités physiques. Cependant, l’erreur relative calculée par le MAPE explose à $|2 – 10| / 2 = 400,%$.

Si l’analyste applique la moyenne non pondérée du MAPE standard pour mesurer la performance globale de son modèle sur ce portefeuille de deux articles, le résultat obtenu est de $(10,% + 400,%) / 2 = 205,%$. D’un point de vue économique et opérationnel, une telle conclusion est absurde. L’organisation n’a commis qu’une erreur totale de 1 008 unités sur un flux global de 10 002 unités réelles (soit une imprécision volumique globale de l’ordre de 10,08 %). Néanmoins, le MAPE non pondéré suggère un échec catastrophique avec une erreur moyenne de 205 %, simplement en raison de l’hypertrophie mathématique générée par le dénominateur de la deuxième ligne.

2.2 Le problème de division par zéro et l’instabilité numérique

La limite la plus insurmontable du MAPE standard se manifeste dès lors que l’une des observations réelles s’annule rigoureusement ($y_t = 0$). Dans le cadre d’une telle éventualité, la fraction $|y_t – \hat{y}_t| / y_t$ produit une forme indéterminée ou une division par zéro stricte, ce qui se traduit par une non-définition mathématique de la métrique et une erreur d’exécution immédiate au sein des tableurs informatiques (affichage de la mention d’erreur fatale #DIV/0! sous Microsoft Excel).

Dans la pratique industrielle, de nombreux analystes tentent de contourner cette aporie par des expédients empiriques hautement contestables. La solution palliative la plus fréquente consiste à substituer artificiellement aux zéros réels une constante arbitraire infinitésimale (telle que $0{,}01$ ou $1$), ou à éliminer purement et simplement les périodes sans demande de l’échantillon d’audit. Ces deux démarches introduisent des biais statistiques rédhibitoires :

• L’addition d’une constante arbitraire rend la métrique ultra-sensible au choix de cette valeur plancher : une valeur réelle forcée à $0{,}1$ avec une prévision de $5$ générera une erreur relative de $4,900,%$, déstabilisant complètement la moyenne globale.
• L’exclusion des périodes nulles conduit à une troncation systématique de l’échantillon, introduisant un biais de sélection sévère qui empêche d’évaluer la capacité du modèle à prévoir correctement l’absence de demande, propriété pourtant essentielle dans les chaînes logistiques gérant des flux intermittents ou des pièces de maintenance stochastiques.

2.3 Biais cognitifs et interprétation managériale erronée

L’adoption aveugle du MAPE standard génère des déformations psychologiques majeures chez les décideurs non statisticiens. Face à un tableau de bord indiquant un MAPE global de 45 %, un directeur d’exploitation ou un responsable financier formulera l’hypothèse intuitive que près de la moitié des flux de l’entreprise fait l’objet d’une mauvaise anticipation. Cette perception erronée engendre un climat de méfiance envers les outils quantitatifs et incite fréquemment à des interventions manuelles correctives non fondées (le phénomène d’over-forecasting ou d’ajustement intempestif des prévisions par les forces de vente).

En réalité, ce taux d’erreur élevé n’est souvent que l’artefact mathématique produit par des centaines de produits de diversification ou de pièces détachées dont la prévision fluctue entre une et trois unités. Parallèlement, des dégradations de précision bien plus coûteuses, survenant sur des lignes de produits phares générant 80 % de la rentabilité de la firme mais limitées à des erreurs relatives de 15 %, passent inaperçues au milieu du bruit généré par les articles marginaux. Cette inadéquation stratégique entre la gravité économique des déviations et leur pondération dans l’indicateur d’évaluation démontre la nécessité impérieuse de transitionner vers le MAPE pondéré en fonction du volume réel (WMAPE).

3. Définition mathématique et formulation du MAPE pondéré (WMAPE)

3.1 Dérivation algébrique de l’erreur absolue pondérée

Pour neutraliser l’asymétrie induite par la division individuelle ligne par ligne et restaurer la cohérence économique de la mesure, la recherche méthodologique a proposé de pondérer l’erreur relative de chaque observation par son importance volumique au sein de l’ensemble considéré. Soit un poids $w_t$ affecté à l’observation $t$, défini conventionnellement comme la part de la valeur réelle de l’observation $y_t$ dans la somme globale des valeurs réelles de l’échantillon :

$$w_t = \frac{y_t}{\sum_{j=1}^{n} y_j}$$

En intégrant ce système de pondération dans l’équation standard du MAPE, nous obtenons la dérivation formelle du MAPE pondéré :

$$\text{WMAPE} = \sum_{t=1}^{n} \left( w_t \times \text{APE}_t \right) = \sum_{t=1}^{n} \left( \frac{y_t}{\sum_{j=1}^{n} y_j} \times \frac{|y_t – \hat{y}_t|}{y_t} \right)$$

En procédant à la simplification algébrique fondamentale, nous constatons que la valeur réelle individuelle $y_t$, présente simultanément au numérateur du facteur de pondération et au dénominateur de l’erreur relative, s’annule complètement :

$$\text{WMAPE} = \sum_{t=1}^{n} \left( \frac{|y_t – \hat{y}_t|}{\sum_{j=1}^{n} y_j} \right) = \frac{\sum_{t=1}^{n} |y_t – \hat{y}_t|}{\sum_{t=1}^{n} y_t}$$

Exprimé en pourcentage, le WMAPE se résume ainsi au rapport strict entre la somme globale des écarts absolus et la somme globale des valeurs réelles observées sur l’échantillon d’analyse :

$$\text{WMAPE} = \frac{\sum_{t=1}^{n} |y_t – \hat{y}_t|}{\sum_{t=1}^{n} y_t} \times 100$$

Cette formulation remarquable porte également le nom d’indicateur MAD/Mean (ou rapport de l’écart absolu moyen sur la moyenne arithmétique de la série temporelle), puisque multiplier le numérateur et le dénominateur par $1/n$ ramène l’expression à :

$$\text{WMAPE} = \frac{\frac{1}{n}\sum_{t=1}^{n} |y_t – \hat{y}_t|}{\frac{1}{n}\sum_{t=1}^{n} y_t} = \frac{\text{MAD}}{\bar{y}}$$

L’élimination du dénominateur microscopique ligne par ligne résout instantanément la pathologie d’instabilité numérique propre au MAPE conventionnel. Chaque unité physique ou monétaire d’écart pèse désormais exactement le même poids dans le résultat final, quel que soit l’article ou la période temporelle sur laquelle cet écart s’est manifesté.

3.2 Propriétés statistiques et robustesse du WMAPE

Le WMAPE bénéficie de propriétés statistiques remarquables qui en font un estimateur de premier ordre pour la validation empirique des modèles prédictifs. La première de ces propriétés est l’invariance d’agrégation : la somme globale des erreurs absolues d’un ensemble de sous-familles de produits divisée par le volume total réel de l’organisation restitue rigoureusement le WMAPE consolidé de l’entreprise. Cette linéarité de décomposition permet d’auditer sans distorsion la performance à tous les niveaux hiérarchiques de la chaîne de valeur (SKU, ligne de produits, canal de distribution, usine, pays).

La seconde propriété capitale concerne sa résilience face aux distributions discontinues ou stochastiques. Alors que le MAPE standard requiert impérativement que chaque terme $y_t$ soit non nul, le WMAPE tolère parfaitement qu’un nombre quelconque d’observations empiriques soient rigoureusement égales à zéro, pourvu que la somme cumulée du dénominateur $\sum y_t$ demeure strictement positive sur l’ensemble de l’horizon d’évaluation. Dans le cas d’une demande intermittente où un article enregistre des ventes nulles durant dix semaines consécutives suivies d’une vente ponctuelle de cinquante unités, le WMAPE s’exécute de manière limpide, continue et mathématiquement irréprochable.

4. Préparation et structuration rigoureuse du jeu de données dans Excel

4.1 Organisation tabulaire et hygiène des données sources

Pour implémenter avec succès le calcul du MAPE pondéré sous Microsoft Excel, il est indispensable de structurer les données primaires selon les canons de l’ingénierie des données et de l’architecture relationnelle en forme normale. Trop souvent, les feuilles de calcul opérationnelles disséminent les prévisions et les réalisations au sein de matrices multidimensionnelles non normalisées (avec des mois disposés horizontalement en en-têtes de colonnes et des références produits en lignes), ce qui complique inutilement l’écriture des formules et hypothèque la dynamicité des plages.

La disposition recommandée repose sur une table tabulaire stricte (tidy data), où chaque ligne représente une observation atomique unique et chaque colonne un attribut mesurable ou catégoriel déterminé. La table minimale doit impérativement comporter au moins trois colonnes fondamentales :

• Identifiant ou Clé Primaire : Le code article (SKU), la référence magasin ou la date correspondant à l’occurrence observée (par exemple, Colonne A).
• Valeur Réelle Observée ($y_t$) : Les flux réels constatés a posteriori (chiffre d’affaires facturé, unités vendues, consommation de pièces) (par exemple, Colonne B).
• Valeur Prévisionnelle ($\hat{y}_t$) : L’estimation calculée préalablement par le modèle statistique, le progiciel APS (Advanced Planning and Scheduling) ou le consensus commercial (par exemple, Colonne C).

Dès l’importation de ces données, il est fortement conseillé de convertir la plage de cellules standard en Tableau Structuré Excel officiel (en mobilisant le raccourci clavier standard Ctrl + L ou Ctrl + T, ou via le ruban Accueil > Mettre sous forme de tableau). L’attribution d’un nom explicite à ce tableau (par exemple Tableau_Previsions) offre des bénéfices opérationnels décisifs : la gestion dynamique automatique de l’extension des lignes lors des ajouts ultérieurs, l’utilisation de références structurées lisibles au sein des formules (telles que Tableau_Previsions[Reel]) et l’élimination quasi-totale des erreurs d’indexation relatives causées par des insertions manuelles de lignes.

4.2 Détection et traitement préalable des anomalies empiriques

Avant d’exécuter la moindre sommation arithmétique, un protocole d’hygiène rigoureux doit être mené pour détecter d’éventuelles corruptions de typage au sein du classeur. Dans de nombreux flux d’exportation ERP (tels que SAP, Oracle ou Microsoft Dynamics), les nombres importés sont parfois encodés sous forme de chaînes de texte, masqués par des espaces insécables (code ASCII 160) ou configurés avec des séparateurs décimaux incompatibles avec les paramètres régionaux du système d’exploitation de l’utilisateur.

Une formule d’audit de typage numérique peut être appliquée temporairement dans une colonne d’inspection pour valider que chaque enregistrement est reconnu comme un nombre réel par le moteur de calcul d’Excel :

=ET(ESTNUM(Tableau_Previsions[Reel]); ESTNUM(Tableau_Previsions[Prevision]))

Par ailleurs, l’analyste doit vérifier méthodiquement l’absence de valeurs négatives dans la colonne des réalisations si la variable modélisée correspond à un flux physique intrinsèquement positif (comme des ventes physiques brutes). Dans l’hypothèse où des retours de marchandises ou des avoirs comptables génèrent des chiffres réels négatifs ponctuels, ceux-ci doivent être soit retraités en amont dans le cadre d’un protocole de gestion des flux inversés, soit neutralisés, car un volume réel total négatif ou nul rendrait le calcul global du WMAPE dépourvu de signification statistique.

5. Calcul pas à pas dans Excel : L’approche par colonnes décomposées

5.1 Calcul des écarts absolus individuels

La méthodologie la plus didactique, particulièrement indiquée lors de la phase de prototypage ou d’audit interne au sein d’un département financier, consiste à décomposer le calcul du WMAPE en plusieurs étapes séquentielles à l’aide de colonnes intermédiaires transparentes. Cette démarche favorise la détection visuelle immédiate des singularités et permet de valider le comportement algorithmique à chaque niveau d’agrégation.

La première composante opératoire réside dans le calcul de la magnitude pure de l’erreur pour chaque ligne d’enregistrement, en faisant abstraction de son orientation positive ou négative. À cette fin, nous mobilisons la fonction native ABS d’Excel, qui calcule la valeur absolue de l’écart arithmétique entre l’observation empirique et la prévision correspondante. Dans la quatrième colonne de notre tableau structuré, baptisée formellement Ecart_Absolu, nous implémentons la référence structurée suivante :

=ABS([@Reel] - [@Prevision])

Si la plage n’a pas été convertie en tableau structuré et s’étend de la ligne 2 à la ligne 1001, la formule standard appliquée en cellule D2 et incrémentée vers le bas sera :

=ABS(B2 - C2)

Cette étape isole strictement l’amplitude vectorielle de la déviation. L’audit immédiat de cette colonne doit révéler exclusivement des valeurs positives ou rigoureusement nulles. Toute présence de valeur négative à ce stade traduirait une erreur de syntaxe ou une corruption du calcul matriciel.

5.2 Sommation des composantes vectorielles

Une fois les écarts absolus individuels calculés pour chaque entité de la population statistique, il convient d’agréger d’une part la totalité des imprécisions commises et d’autre part la totalité des volumes réels distribués par le système.

Dans une cellule dédiée à la consolidation du numérateur (par exemple en cellule G2), nous configurons la sommation globale des écarts absolus via la fonction SOMME :

=SOMME(Tableau_Previsions[Ecart_Absolu])

Ou, en notation de coordonnées de cellules traditionnelles :

=SOMME(D2:D1001)

Simultanément, dans une seconde cellule de consolidation dédiée au dénominateur (par exemple en cellule G3), nous appliquons l’agrégation volumique des données réelles observées :

=SOMME(Tableau_Previsions[Reel])

Ou selon les coordonnées fixes de cellules :

=SOMME(B2:B1001)

Il est impératif de s’assurer de l’isomorphisme parfait des plages sélectionnées : tout décalage d’un rang entre le vecteur des écarts et le vecteur des valeurs réelles fausserait immédiatement le rapport global d’échelle.

5.3 Consolidation du ratio final et formatage

La dernière étape du calcul séquentiel réside dans l’exécution de la division rationnelle entre le numérateur consolidé et le dénominateur agrégé. Dans la cellule récapitulative destinée à afficher le score du WMAPE (par exemple en cellule G4), nous formulons le quotient :

=G2 / G3

Pour assurer une parfaite lisibilité institutionnelle du résultat, cette cellule doit être formatée en style de pourcentage avec un degré de précision approprié, idéalement fixé à deux décimales (ce qui correspond sous Excel au code de format personnalisé 0,00%). Il est formellement déconseillé de multiplier manuellement le ratio par cent dans la formule (ex : =(G2/G3)*100) si la cellule reçoit ultérieurement le format Pourcentage natif du ruban Excel, sous peine de multiplier l’indicateur par un facteur parasite de 10 000.

6. Calcul optimisé dans Excel : La méthode matricielle SOMMEPROD

6.1 Principe de la vectorisation sans colonnes intermédiaires

Bien que l’approche par colonnes décomposées soit irréprochable sur le plan conceptuel et pédagogique, elle présente des inconvénients logistiques manifestes dans les classeurs de production de grande envergure. L’adjonction systématique d’une colonne calculée d’écarts absolus au sein de tables comprenant des centaines de milliers de lignes entraîne un alourdissement significatif de la mémoire vive mobilisée, accroît la taille de stockage du fichier sur disque et surcharge la lecture visuelle des tableaux de bord.

Pour pallier ces désagréments, Microsoft Excel propose une fonction d’algèbre matricielle hautement performante et optimisée : la fonction SOMMEPROD. Historiquement introduite pour effectuer la somme des produits d’éléments homologues entre plusieurs vecteurs, SOMMEPROD possède la propriété fondamentale d’évaluer nativement des expressions vectorielles sans exiger la validation matricielle complexe (le traditionnel Ctrl + Maj + Entrée requis sur les anciennes versions d’Excel antérieures à la refonte du moteur de calcul dynamique en 2019).

En exploitant cette capacité de traitement matriciel implicite, nous pouvons condenser l’intégralité du calcul du numérateur (la somme des valeurs absolues des différences terme à terme) en une expression atomique, calculée entièrement en mémoire cache sans jamais faire appel à une colonne intermédiaire dans la grille physique du tableur.

6.2 Construction syntaxique de la formule unique

La formulation condensée directe du WMAPE s’obtient en combinant la fonction SOMMEPROD au numérateur et la fonction SOMME classique au dénominateur. Supposons que les données réelles soient localisées sur la plage B2:B1001 et les valeurs prévisionnelles correspondantes sur la plage C2:C1001. La formule unique d’évaluation s’écrit formellement comme suit :

=SOMMEPROD(ABS(B2:B1001 - C2:C1001)) / SOMME(B2:B1001)

Si l’on opère au sein d’un Tableau Structuré nommé Tableau_Previsions, la syntaxe gagne considérablement en élégance, en robustesse et en autodocumentation :

=SOMMEPROD(ABS(Tableau_Previsions[Reel] - Tableau_Previsions[Prevision])) / SOMME(Tableau_Previsions[Reel])

Sur les versions modernes d’Excel (disposant du moteur de calcul matriciel dynamique d’Office 365 ou d’Excel 2021 et versions ultérieures), la fonction standard SOMME est devenue capable de propager nativement les calculs matriciels en mémoire. Par conséquent, l’analyste peut également mobiliser la formule allégée :

=SOMME(ABS(B2:B1001 - C2:C1001)) / SOMME(B2:B1001)

Cependant, la construction reposant sur SOMMEPROD demeure universellement recommandée par mesure de sécurité méthodologique, car elle assure une rétrocompatibilité absolue et intégrale avec toutes les versions antérieures d’Excel (Excel 2010, 2013, 2016), garantissant ainsi que le classeur pourra être ouvert et manipulé par n’importe quel collaborateur de l’écosystème d’entreprise sans générer d’anomalies de validation matricielle.

7. Gestion des cas critiques, zéros et valeurs manquantes dans Excel

7.1 Traitement robuste de l’erreur de division par zéro

Bien que le WMAPE élimine le risque d’erreur de division par zéro au niveau des lignes individuelles, une vulnérabilité résiduelle persiste au niveau de la consolidation globale de l’échantillon. Si la totalité des observations réelles au sein de la plage sélectionnée est égale à zéro ($\sum y_t = 0$), le dénominateur de la fraction s’annule, déclenchant inexorablement l’erreur système #DIV/0! sous Excel.

Ce cas de figure n’est pas purement académique : il survient fréquemment lors de l’application de filtres dynamiques au sein d’un tableau de bord, par exemple lorsqu’un gestionnaire sélectionne une référence nouvellement introduite dans le catalogue mais pour laquelle aucune vente effective n’a encore été enregistrée, ou lorsqu’un magasin a été temporairement fermé pour travaux au cours de la période d’observation.

Pour immuniser le modèle de reporting contre ces ruptures de calcul, il convient d’encapsuler la formule matricielle au sein de structures conditionnelles défensives. La fonction SIERREUR permet d’intercepter proprement toute défaillance numérique et de lui substituer un libellé textuel explicite ou une valeur nulle contrôlée :

=SIERREUR(SOMMEPROD(ABS(Tableau_Previsions[Reel] - Tableau_Previsions[Prevision])) / SOMME(Tableau_Previsions[Reel]); "Volume réel nul")

Une alternative plus rigoureuse sur le plan logique consiste à utiliser la fonction conditionnelle SI afin de tester préalablement la positivité stricte du dénominateur, évitant ainsi d’exécuter un calcul matriciel inutile lorsque la condition d’existence n’est pas satisfaite :

=SI(SOMME(Tableau_Previsions[Reel]) > 0; SOMMEPROD(ABS(Tableau_Previsions[Reel] - Tableau_Previsions[Prevision])) / SOMME(Tableau_Previsions[Reel]); 0)

7.2 Filtrage dynamique des valeurs aberrantes ou négatives

Dans de nombreux modèles de prévision de la demande, la présence de valeurs négatives au sein des données historiques réelles constitue une anomalie comptable qui fausse mécaniquement l’agrégation du dénominateur volumique. Si un article présente des ventes réelles négatives en raison d’un volume exceptionnel d’avoirs ou de reprises logistiques, sa contribution réduit artificiellement le dénominateur, ce qui a pour effet pervers de gonfler artificiellement le ratio d’erreur global.

Pour neutraliser ces anomalies sans altérer physiquement le fichier source de données brutes, l’analyste peut intégrer des conditions logiques de filtrage directement au sein du moteur matriciel de calcul. Par exemple, pour restreindre dynamiquement le calcul du WMAPE aux seules observations dont la valeur réelle est strictement supérieure à zéro ($y_t > 0$), nous pouvons exploiter l’algèbre booléenne dans la fonction SOMMEPROD :

=SOMMEPROD((Tableau_Previsions[Reel] > 0) * ABS(Tableau_Previsions[Reel] - Tableau_Previsions[Prevision])) / SOMME.SI(Tableau_Previsions[Reel]; ">0")

Dans cette architecture de formule :

• L’expression booléenne (Tableau_Previsions[Reel] > 0) génère un vecteur logique de VRAI et FAUX.
• La multiplication par les écarts absolus convertit implicitement ces booléens en 1 et 0, annulant instantanément la contribution des lignes non conformes dans le numérateur.
• La fonction SOMME.SI au dénominateur agrège exclusivement les volumes strictement positifs, préservant ainsi l’intégrité de l’échelle d’évaluation.

Cette méthodologie garantit une traçabilité analytique irréprochable en documentant mathématiquement le critère d’exclusion au cœur même de la formule de reporting.

8. Automatisation et reproductibilité via Power Query

8.1 Ingestion et transformation des séries de prévision

Lorsque les volumes de données à traiter dépassent les dizaines de milliers d’enregistrements mensuels ou proviennent d’extractions multiples et hétérogènes, le recours exclusif aux formules de grille dans Excel peut s’avérer insuffisant pour garantir la scalabilité et l’intégrité des calculs. L’utilisation du moteur ETL intégré d’Excel, Power Query, offre un cadre méthodologique professionnel, automatisé et parfaitement reproductible.

Le processus d’ingestion s’amorce via le ruban Données > Récupérer des données > À partir d’un fichier (ou directement À partir d’une feuille/table). Une fois le tableau source chargé dans l’éditeur Power Query, une séquence méthodique d’étapes de nettoyage standardisées doit être appliquée :

• Suppression des lignes vides et des doublons : Élimination des enregistrements corrompus par le biais des options de transformation contextuelles.
• Typage strict des données : Forçage systématique des colonnes Reel et Prevision vers le type Nombre décimal (ou Entier 64 bits selon la granularité physique). Cette étape neutralise définitivement les anomalies liées aux formats textes résiduels.
• Remplacement des erreurs et valeurs nulles : Substitution contrôlée des valeurs null par des zéros numériques grâce à la fonction Remplacer les valeurs.

Toutes ces manipulations sont traduites séquentiellement en langage M dans le panneau des étapes appliquées, assurant une parfaite auditabilité pour les réviseurs externes ou les auditeurs internes.

8.2 Création de colonnes personnalisées en langage M

L’étape cardinale sous Power Query consiste à programmer le calcul de l’écart absolu au niveau de chaque ligne de la table à l’aide des fonctions scalaires du langage M. Dans l’onglet Ajouter une colonne, l’utilisateur active l’option Colonne personnalisée et configure l’expression suivante :

Number.Abs([Reel] - [Prevision])

Cette nouvelle colonne, baptisée Ecart_Absolu, calcule l’écart arithmétique sans générer aucune surcharge de formules dynamiques au sein de la mémoire vive du tableur. Une fois cette colonne dérivée validée, l’analyste dispose de deux options de déploiement en fonction des finalités du reporting :

• Agrégation immédiate dans Power Query : L’utilisation de l’opération Transformer > Regrouper par permet d’agréger la table (soit au niveau mondial, soit par segment de marché) en définissant deux métriques d’agrégation : la somme de la colonne Ecart_Absolu et la somme de la colonne Reel. Une colonne personnalisée finale peut alors diviser directement ces deux sommes pour restituer le WMAPE consolidé.
• Chargement dans le modèle de données : La table enrichie est chargée sans agrégation préalable dans le modèle de données interne d’Excel (Power Pivot) en sélectionnant l’option Fermer et charger dans > Ajouter ces données au modèle de données. Cette approche hybride combine la puissance de nettoyage de Power Query et la flexibilité d’analyse matricielle du moteur relationnel VertiPaq de Microsoft.

9. Analyse empirique comparative : Démonstration sur cas d’étude

9.1 Présentation du jeu de données expérimental

Afin d’illustrer de façon irréfutable la divergence structurelle entre le MAPE non pondéré et le WMAPE, examinons un échantillon représentatif de cinq articles stratégiques issus du catalogue d’une entreprise industrielle. Ce jeu d’essai a été délibérément calibré pour combiner des produits à très fort volume de vente (soumis à une variabilité relative modérée) et des références à très faible rotation (caractérisées par des fluctuations relatives apparentes considérables) :

• SKU-001 (Produit Star – Classe A) : Réel = 25 000 unités | Prévision = 27 500 unités. L’écart est de 2 500 unités en surestimation.
• SKU-002 (Volume Moyen – Classe B) : Réel = 8 000 unités | Prévision = 7 200 unités. L’écart est de 800 unités en sous-estimation.
• SKU-003 (Accessoire Courant – Classe B) : Réel = 1 500 unités | Prévision = 1 350 unités. L’écart est de 150 unités en sous-estimation.
• SKU-004 (Composant de Niche – Classe C) : Réel = 15 unités | Prévision = 45 unités. L’écart est de 30 unités en surestimation.
• SKU-005 (Pièce de Rechange Critique – Classe C) : Réel = 5 unités | Prévision = 25 unités. L’écart est de 20 unités en surestimation.

Au niveau macroéconomique de cette entreprise, le volume réel cumulé s’élève à $25,000 + 8,000 + 1,500 + 15 + 5 = 34,520$ unités. L’erreur physique globale commise par la fonction de planification s’élève à $2,500 + 800 + 150 + 30 + 20 = 3,500$ unités.

9.2 Calcul parallèle et divergence des résultats

Procédons à l’exécution comparative des deux méthodologies de mesure au sein de Microsoft Excel sur ce jeu de données contrôlé :

1. Calcul selon le MAPE conventionnel (non pondéré) : Nous calculons l’erreur relative individuelle $\text{APE}_i$ pour chaque référence :

• SKU-001 : $|25,000 – 27,500| / 25,000 = 2,500 / 25,000 = 10{,}00,%$
• SKU-002 : $|8,000 – 7,200| / 8,000 = 800 / 8,000 = 10{,}00,%$
• SKU-003 : $|1,500 – 1,350| / 1,500 = 150 / 1,500 = 10{,}00,%$
• SKU-004 : $|15 – 45| / 15 = 30 / 15 = 200{,}00,%$
• SKU-005 : $|5 – 25| / 5 = 20 / 5 = 400{,}00,%$

La moyenne arithmétique de ces cinq ratios individuels (implémentée via la formule Excel =MOYENNE(D2:D6)) produit le résultat suivant :

$$\text{MAPE} = \frac{10,% + 10,% + 10,% + 200,% + 400,%}{5} = \frac{630,%}{5} = 126{,}00,%$$

2. Calcul selon le WMAPE (pondéré par les volumes réels) : En appliquant la formule matricielle préconisée :

$$\text{WMAPE} = \frac{\sum |y_i – \hat{y}_i|}{\sum y_i} = \frac{3,500}{34,520} \approx 0{,}10139 \text{ soit } 10{,}14,%$$

Le contraste entre les deux indicateurs est saisissant : le MAPE standard affiche une valeur de 126 %, suggérant un système de prévision totalement défaillant et inutilisable, alors que le WMAPE révèle que l’erreur réelle de la chaîne d’approvisionnement n’est que de 10,14 %. L’inflation artificielle du MAPE est causée quasi-exclusivement par les articles SKU-004 et SKU-005, qui représentent pourtant à eux deux moins de 0,06 % du chiffre d’affaires volumique total de l’entreprise.

9.3 Décomposition de la variance et analyse de sensibilité

L’analyse de sensibilité met en exergue l’extrême vulnérabilité du MAPE non pondéré aux variations de bordure. Si la prévision de l’article SKU-005 avait été de 50 unités au lieu de 25 (un décalage anecdotique de 25 unités physiques seulement dans l’entrepôt), son ratio individuel se serait élevé à $|5 – 50| / 5 = 900,%$, propulsant le MAPE global de l’entreprise à 226 % ! À l’inverse, sous le WMAPE, cette même déviation de 25 unités ne fait osciller l’indicateur global que de 10,14 % à 10,21 %, reflétant rigoureusement la proportion marginale de cet événement au sein de l’organisation.

Cette démonstration empirique valide les conclusions de la littérature en recherche opérationnelle : le WMAPE assure une invariance de structure face aux bruits périphériques et garantit que l’attention des équipes d’optimisation soit allouée exactement là où se situent les enjeux capacitaires et financiers de l’entreprise.

10. Visualisation avancée et tableaux de bord analytiques dans Excel

10.1 Représentation graphique de la dispersion des erreurs

La restitution visuelle des résultats de prévision constitue un vecteur critique d’alignement au sein des équipes de direction. Un tableau de bord Excel performant ne doit pas se cantonner à la divulgation d’un ratio scalaire isolé, mais doit contextualiser la dispersion des erreurs en fonction des volumétries réelles associées.

Le type de représentation graphique le plus éclairant pour expliciter la pertinence du WMAPE est le diagramme de dispersion à bulles (Bubble Chart) ou le nuage de points enrichi :

• Axe des abscisses (X) : Le volume réel de ventes par article ou par catégorie ($y_i$), configuré idéalement sur une échelle logarithmique pour absorber les disparités monumentales de volume entre les classes ABC.
• Axe des ordonnées (Y) : L’erreur absolue relative en pourcentage ($\text{APE}_i$).
• Taille de la bulle : La contribution volumique absolue à l’erreur globale ($|y_i – \hat{y}_i|$).

Ce graphique permet d’identifier immédiatement la nature des défaillances prévisionnelles : les bulles situées en haut à gauche (très forte erreur en pourcentage, mais volume minuscule) correspondent aux bruits statistiques que le WMAPE pondère à la baisse, tandis que les larges bulles situées en bas à droite (faible pourcentage d’erreur, mais volume gigantesque) représentent les chantiers prioritaires pour les prévisionnistes. Par ailleurs, l’application de formats conditionnels (jeux d’icônes ou barres de données intégrées) directement au sein des colonnes de données facilite l’exploration visuelle rapide des anomalies au niveau transactionnel.

10.2 Intégration dynamique dans les tableaux croisés dynamiques (TCD)

L’intégration du calcul du WMAPE au sein d’un Tableau Croisé Dynamique classique se heurte à une limitation structurelle bien connue des analystes avancés : les Champs Calculés standards d’un TCD exécutent invariablement la somme des composants avant l’application des opérateurs mathématiques non linéaires, rendant impossible l’évaluation de la fonction ABS ligne par ligne au niveau atomique.

Pour surmonter cette barrière sous Excel, il est indispensable de faire appel au modèle de données interne et au langage DAX (Data Analysis Expressions) via l’interface Power Pivot. Une fois la table de données intégrée au modèle, l’analyste peut formuler une Mesure DAX explicite qui recalculera fidèlement et dynamiquement le WMAPE, quel que soit le contexte de filtre appliqué par les segments (Slicers), les hiérarchies temporelles ou les regroupements organisationnels :

WMAPE := DIVIDE(SUMX('Tableau_Previsions', ABS('Tableau_Previsions'[Reel] - 'Tableau_Previsions'[Prevision])), SUM('Tableau_Previsions'[Reel]), BLANK())

Cette mesure DAX opère avec une rigueur absolue :

• L’itérateur SUMX parcourt chaque ligne individuelle de la table dans son contexte de filtre courant, calcule la différence absolue entre le réel et le prévisionnel, puis en effectue la sommation en mémoire ultra-rapide.
• La fonction DIVIDE réalise la division par la somme du volume réel tout en gérant de façon native et élégante les dénominateurs nuls, renvoyant une valeur vide (BLANK()) sans jamais générer d’erreur d’exécution dans le TCD.

11. Programmation VBA : Calcul du WMAPE par macro personnalisée

11.1 Écriture d’une fonction personnalisée (UDF)

Dans les environnements d’entreprise où la standardisation des classeurs est cruciale et où l’on souhaite épargner aux utilisateurs finaux la saisie complexe de formules matricielles ou de mesures DAX, le développement d’une fonction personnalisée en Visual Basic for Applications (User-Defined Function ou UDF) constitue une solution hautement qualitative. Cette fonction peut être appelée dans la grille d’Excel exactement comme une fonction native du tableur (telle que SOMME ou RECHERCHEV).

Voici l’architecture algorithmique complète et sécurisée d’une fonction UDF dédiée au calcul robuste du WMAPE, intégrant la vérification des dimensions vectorielles, l’exclusion des cellules non numériques et l’interception de l’annulation du dénominateur :

Function CalculerWMAPE(PlageReel As Range, PlagePrevision As Range) As Variant
    Dim ArrReel As Variant
    Dim ArrPrev As Variant
    Dim SommeEcartsAbs As Double
    Dim SommeReel As Double
    Dim i As Long
    Dim NbLignes As Long

    ' Validation de la conformité dimensionnelle des vecteurs
    If PlageReel.Cells.Count <> PlagePrevision.Cells.Count Then
        CalculerWMAPE = CVErr(xlErrRef)
        Exit Function
    End If

    ' Chargement des plages dans des tableaux dynamiques en mémoire vive
    If PlageReel.Cells.Count = 1 Then
        ReDim ArrReel(1 To 1, 1 To 1)
        ReDim ArrPrev(1 To 1, 1 To 1)
        ArrReel(1, 1) = PlageReel.Value
        ArrPrev(1, 1) = PlagePrevision.Value
    Else
        ArrReel = PlageReel.Value
        ArrPrev = PlagePrevision.Value
    End If

    NbLignes = UBound(ArrReel, 1)
    SommeEcartsAbs = 0#
    SommeReel = 0#

    ' Boucle d'accumulation matricielle hautement optimisée
    For i = 1 To NbLignes
        If IsNumeric(ArrReel(i, 1)) And IsNumeric(ArrPrev(i, 1)) Then
            If Not IsEmpty(ArrReel(i, 1)) And Not IsEmpty(ArrPrev(i, 1)) Then
                SommeEcartsAbs = SommeEcartsAbs + Abs(CDbl(ArrReel(i, 1)) - CDbl(ArrPrev(i, 1)))
                SommeReel = SommeReel + CDbl(ArrReel(i, 1))
            End If
        End If
    Next i

    ' Évaluation de la condition de division par zéro
    If SommeReel <= 0# Then
        CalculerWMAPE = CVErr(xlErrDiv0)
    Else
        CalculerWMAPE = SommeEcartsAbs / SommeReel
    End If
End Function

Cette implémentation VBA se distingue par le chargement préalable des plages d’entrée dans des tableaux internes en mémoire (Arrays). Cette technique évite les accès répétitifs et chronophages aux cellules individuelles de la feuille Excel (mécanisme de liaison COM extrêmement coûteux en temps CPU), autorisant l’évaluation quasi-instantanée de tables contenant plus de 500 000 lignes.

11.2 Déploiement et sécurisation de la fonction dans l’environnement Excel

Pour rendre cette fonction UDF accessible au sein du classeur opérationnel, le code source doit être inséré dans un Module de code standard (via l’éditeur Visual Basic activé par le raccourci Alt + F11, puis menu Insertion > Module). Le classeur doit impérativement être enregistré sous un format autorisant les macros, c’est-à-dire avec l’extension .xlsm (Classeur Excel prenant en charge les macros) ou .xlsb (Classeur binaire Excel).

Dans l’optique d’un déploiement centralisé à l’échelle d’un département ou d’une direction financière complète, il est fortement conseillé de convertir le classeur contenant la macro en un macro-complément Excel autonome (extension .xlam). Une fois ce complément installé via le menu Fichier > Options > Compléments > Compléments Excel, la fonction CalculerWMAPE devient disponible universellement sur tous les fichiers Excel ouverts sur le poste de travail des collaborateurs, sans qu’il soit nécessaire d’insérer le moindre script VBA dans leurs propres fichiers opérationnels.

12. Gouvernance des prévisions, prise de décision et atténuation des biais

12.1 Implications décisionnelles du choix de la métrique

Le choix d’un indicateur de performance pour l’audit des prévisions ne relève pas d’une simple querelle théorique entre statisticiens : il conditionne directement les comportements opérationnels, les investissements en capital circulant et la qualité de service rendue aux clients finaux. L’évaluation de la performance par le MAPE standard induit un biais pervers chez les planificateurs de la chaîne logistique : ces derniers consacrent une énergie démesurée à tenter de prédire avec précision les articles marginaux de faible volume afin d’éviter les pourcentages d’erreur astronomiques (200 % ou 500 %), délaissant paradoxalement l’optimisation fine des produits à très fort impact sur le chiffre d’affaires.

Ce phénomène de sur-ajustement (overfitting) sur les faibles volumes est extrêmement coûteux pour l’entreprise. En basculant la gouvernance du cycle Sales and Operations Planning (S&OP) vers le WMAPE, le management aligne organiquement les objectifs des planificateurs sur les impératifs financiers de l’organisation. Puisque chaque unité physique pèse de manière égale dans le WMAPE, une déviation de 5 000 unités sur le produit phare de l’entreprise recevra une attention proportionnellement cinquante fois supérieure à une déviation de 100 unités sur une pièce secondaire, restaurant une stricte rationalité économique dans l’allocation du temps et des ressources analytiques.

12.2 Atténuation des biais cognitifs dans l’interprétation des erreurs

L’interprétation des pourcentages d’erreur fait l’objet d’un biais cognitif bien documenté en psychologie décisionnelle : le biais d’ancrage. Lorsqu’un dirigeant est confronté à un taux de 30 % d’erreur, il tend à transposer intuitivement cette proportion sur les flux financiers globaux de l’organisation. L’utilisation du MAPE conventionnel exacerbe ce biais en fournissant des valeurs structurellement gonflées et déconnectées de la réalité des flux physiques.

La mise en place du WMAPE comme métrique officielle de référence au sein des tableaux de bord institutionnels constitue un puissant outil de pédagogie managériale. Pour maximiser son adoption et garantir une transition culturelle harmonieuse, les organisations doivent formaliser des protocoles d’évaluation standardisés dans leurs processus de gouvernance :

• Double affichage transitoire : Durant les premiers trimestres de déploiement, présenter conjointement le WMAPE consolidé et le WMAPE par classe ABC pour démontrer empiriquement la convergence de l’indicateur sur les catégories stratégiques.
• Règle de granularité temporelle : Standardiser rigoureusement l’horizon temporel de calcul (mensuel, trimestriel ou annuel) afin de prévenir les distorsions induites par la volatilité saisonnière à court terme.
• Évaluation du biais directionnel : Compléter systématiquement le WMAPE par une mesure du biais global (la somme des erreurs signées divisée par la somme du réel, ou Tracking Signal), afin de déterminer si le système souffre d’une tendance structurelle à la sur-prévision ou à la sous-prévision, dimension que la valeur absolue du WMAPE ne peut révéler à elle seule.

En ancrant le calcul du MAPE pondéré au cœur de ses procédures de modélisation dans Microsoft Excel, toute organisation se dote d’un appareil analytique robuste, mathématiquement inattaquable et parfaitement synchronisé avec ses impératifs de rentabilité et d’excellence opérationnelle.

Références

Armstrong, J. S., & Collopy, F. (1992). Error measures for generalizing about forecasting methods: Empirical comparisons. International Journal of Forecasting, 8(1), 69-80. https://doi.org/10.1016/0169-2070(92)90008-W

Hyndman, R. J., & Athanasopoulos, G. (2018). Forecasting: principles and practice (2nd ed.). OTexts. https://otexts.com/fpp2/

Hyndman, R. J., & Koehler, A. B. (2006). Another look at measures of forecast accuracy. International Journal of Forecasting, 22(4), 679-688. https://doi.org/10.1016/j.ijforecast.2006.03.001

Kolassa, S., & Schütz, W. (2007). Advantages of the MAD/Mean ratio over the MAPE. Foresight: The International Journal of Applied Forecasting, (6), 40-43.

Makridakis, S., Spiliotis, E., & Assimakopoulos, V. (2020). The M4 Competition: 100,000 time series and 61 forecasting methods. International Journal of Forecasting, 36(1), 54-74. https://doi.org/10.1016/j.ijforecast.2019.04.014

Microsoft Corporation. (2023). Fonction SOMMEPROD – Support Microsoft. Support technique Microsoft. https://support.microsoft.com/fr-fr/office/sommeprod-fonction-sommeprod-174fac7e-a4d1-4493-88c7-404832345fac

Silver, E. A., Pyke, D. F., & Thomas, D. J. (2016). Inventory and production management in supply chains (4th ed.). CRC Press. https://doi.org/10.1201/9781315374406

Syntetos, A. A., & Boylan, J. E. (2005). The accuracy of intermittent demand estimates. International Journal of Forecasting, 21(2), 303-314. https://doi.org/10.1016/j.ijforecast.2004.10.001