Comment réaliser une analyse d'écart dans Excel : étape par étape

Pour effectuer une analyse d'écarts dans Excel, placez les chiffres du budget et les chiffres réels côte à côte pour chaque poste. Soustrayez le budget du réel pour obtenir l'écart monétaire. Divisez cet écart par le budget pour obtenir le pourcentage. Ensuite, marquez chaque ligne comme favorable ou défavorable et expliquez les écarts les plus importants.
Ce guide détaille les formules, la structure de la feuille de calcul et propose un exemple complet. Il explique également comment représenter le résultat sous forme de graphique et comment diviser un écart de revenus entre prix et volume. Les étapes Excel proviennent des pages de support de Microsoft consultées le 10 novembre 2026.
Ce qu'une analyse d'écarts révèle
Un écart est la différence entre ce que vous aviez prévu et ce qui s'est réellement passé. Une analyse d'écarts répertorie ces différences ligne par ligne et explique celles qui sont significatives.
La plupart des équipes comparent les résultats réels à l'un des trois référentiels suivants :
- Budget. Le plan établi au début de l'année.
- Forecast. Un plan mis à jour en cours d'année.
- Période précédente. Le mois dernier, le trimestre dernier ou la même période l'année précédente.
La compétence propre de Microsoft Copilot pour cette tâche décrit bien le travail. Sa compétence d'analyse d'écarts « compare les résultats réels aux budgets, prévisions ou périodes précédentes et explique les différences par le prix, le volume, le mix, le taux ou le calendrier. »
Le résultat est généralement un tableau accompagné de quelques notes écrites. Le tableau montre chaque écart. Les notes expliquent les trois ou quatre points sur lesquels les dirigeants doivent agir.
Les formules de base
Trois formules effectuent l'essentiel du travail. Supposons que le budget soit dans la colonne B et le réel dans la colonne C.
| Mesure | Formule | Ce qu'elle vous indique |
|---|---|---|
| Écart monétaire | =C2-B2 | L'écart entre le réel et le budget, en devise |
| Écart en pourcentage | =IFERROR((C2-B2)/ABS(B2),"") | L'importance de l'écart par rapport au plan |
| Statut | =IF(D2=0,"On plan",IF(D2>0,"Above plan","Below plan")) | La direction dans laquelle la ligne a évolué |
La fonction ABS permet de conserver le bon signe lorsqu'un budget est négatif. IFERROR laisse la cellule vide lorsque le budget est à zéro, au lieu d'afficher une erreur.
La direction n'est pas synonyme de bon ou de mauvais. Un revenu supérieur au plan est une bonne nouvelle. Des coûts supérieurs au plan sont une mauvaise nouvelle. Les sections suivantes montrent comment gérer cela.
Comment configurer la feuille de calcul
Une mise en page claire facilite la copie des formules. Utilisez une ligne par poste et ces colonnes :
- Poste. Revenus, coût des marchandises vendues, paie, marketing, etc.
- Type. Saisissez « Revenue » ou « Cost » pour qu'Excel sache quelle direction est favorable.
- Budget. Le montant prévu pour la période.
- Réel. Le montant provenant de votre export comptable.
- Écart. Réel moins budget.
- Écart %. Écart divisé par le budget.
- Favorable ou défavorable. Une étiquette basée sur le type et la direction.
- Explication. Une courte note pour les lignes significatives.
Conservez le budget et le réel pour la même période et le même plan comptable. Une analyse d'écarts ne vaut que par la qualité de cette correspondance. Si la paie est divisée en deux comptes dans le budget, divisez-la de la même manière dans les chiffres réels.
Formatez le tableau en tant que tableau Excel avec Ctrl+T. Les nouvelles lignes reprendront alors automatiquement les formules.
Comment calculer les écarts dans Excel
Une fois la mise en page prête, les étapes sont courtes. Supposons que les colonnes ci-dessus commencent à la ligne 2, avec le Type en B, le Budget en C et le Réel en D.
- En E2, saisissez =D2-C2 pour l'écart monétaire.
- En F2, saisissez =IFERROR((D2-C2)/ABS(C2),"") et formatez-le en pourcentage.
- Dans la colonne Statut, saisissez le test de favorabilité illustré ci-dessous.
- Copiez les formules sur chaque ligne.
- Ajoutez une ligne de total et calculez son écart à partir des totaux, et non en additionnant les pourcentages.
Voici le test de favorabilité pour la ligne 2 de la colonne Statut :
=IF(E2=0,"On plan",IF(B2="Revenue",IF(E2>0,"Favorable","Unfavorable"),IF(E2<0,"Favorable","Unfavorable")))
Il lit d'abord la colonne Type. Pour les revenus, un écart positif est favorable. Pour les coûts, un écart négatif est favorable car vous avez dépensé moins que prévu.
Les pourcentages nécessitent de la prudence dans la ligne de total. L'addition des pourcentages de ligne donne un résultat erroné. Divisez plutôt l'écart total par le budget total.
Comment signaler les écarts importants
Tous les écarts ne nécessitent pas une note. La plupart des équipes fixent une règle de matérialité, telle que « plus de 5 % et plus de 2,000 $ ». Les lignes qui passent ces deux tests reçoivent une explication.
Une formule simple les marque. Ajoutez une colonne « Material » et saisissez =AND(ABS(F2)>0.05,ABS(E2)>2000) à la ligne 2. La cellule affiche TRUE pour les lignes significatives.
La couleur permet de parcourir le tableau plus rapidement. La mise en forme conditionnelle d'Excel peut ajouter des jeux d'icônes. Microsoft précise qu'un jeu d'icônes peut « annoter et classer les données en trois à cinq catégories séparées par une valeur de seuil. »
Pour en ajouter un, sélectionnez la colonne Écart %. Allez ensuite dans Accueil > Mise en forme conditionnelle > Jeux d'icônes. Microsoft note que dans le jeu de 3 flèches, « la flèche verte vers le haut représente les valeurs les plus élevées. » Pour les lignes de coûts, inversez l'ordre des icônes afin qu'une hausse apparaisse comme un avertissement.
Comment expliquer les facteurs déterminants
Les chiffres montrent où se situe l'écart. Les facteurs (drivers) expliquent pourquoi. La description des compétences de Microsoft liste cinq facteurs communs : prix, volume, mix, taux et calendrier.
Prix. Vous avez vendu ou acheté à un prix différent de celui prévu. Écart de prix = (prix réel − prix budgété) × unités réelles.
Volume. Vous avez vendu ou utilisé une quantité différente. Écart de volume = (unités réelles − unités budgétées) × prix budgété.
Mix. La part des produits, des régions ou des clients a changé. Un glissement vers des articles à plus faible marge réduit le profit même si le volume se maintient.
Taux. Un taux de coût a changé, comme un salaire horaire ou des frais de fournisseur.
Calendrier (Timing). L'argent a bougé entre les périodes. Une facture de mars payée en avril apparaît comme un écart dans les deux mois.
Le prix et le volume expliquent ensemble l'intégralité de l'écart de revenus. Cette vérification vaut la peine d'être faite à chaque fois, car elle confirme les calculs.
Un exemple d'analyse d'écarts
Voici un exemple concret pour une entreprise fictive, Cedar and Pine Coffee. Son responsable financier compare les résultats réels du troisième trimestre au budget.
| Poste | Type | Budget | Réel | Écart | Écart % | Statut |
|---|---|---|---|---|---|---|
| Revenue | Revenue | 250,000 $ | 236,500 $ | −13,500 $ | −5.4% | Unfavorable |
| Cost of goods sold | Cost | 100,000 $ | 97,200 $ | −2,800 $ | −2.8% | Favorable |
| Payroll | Cost | 60,000 $ | 64,800 $ | 4,800 $ | 8.0% | Unfavorable |
| Marketing | Cost | 20,000 $ | 26,000 $ | 6,000 $ | 30.0% | Unfavorable |
| Rent | Cost | 15,000 $ | 15,000 $ | 0 $ | 0.0% | On plan |
| Operating profit | 55,000 $ | 33,500 $ | −21,500 $ | −39.1% | Unfavorable |
Le total est cohérent. Les revenus ont perdu 13,500 $, le coût des marchandises vendues a économisé 2,800 $, la paie a coûté 4,800 $ de plus et le marketing a coûté 6,000 $ de plus. Ensemble, cela fait −21,500 $, ce qui correspond à l'écart de profit.
Trois lignes passent la règle de matérialité de plus de 5 % et plus de 2,000 $ : revenus, paie et marketing. Le coût des marchandises vendues n'a varié que de 2.8 %, il ne reçoit donc pas de note.
L'écart de revenus nécessite un examen plus approfondi. Le budget prévoyait 50,000 unités à 5.00 $. Le trimestre a vu la vente de 43,000 unités à 5.50 $.
- Écart de volume. (43,000 − 50,000) × 5.00 $ = −35,000 $.
- Écart de prix. (5.50 $ − 5.00 $) × 43,000 = +21,500 $.
- Total. −35,000 $ + 21,500 $ = −13,500 $, ce qui correspond à la ligne des revenus.
Ainsi, l'augmentation de prix a tenu, mais elle a coûté en volume. La note aux dirigeants dit exactement cela. Le marketing a dépensé 30 % de plus que prévu, et pourtant les unités ont chuté. Cela soulève une question sur l'efficacité des campagnes.
La paie nécessite sa propre vérification. L'écart de 4,800 $ pourrait être un facteur de taux, comme une augmentation de salaire, ou un facteur de volume, comme des heures supplémentaires. Le responsable financier interroge le responsable des opérations et apprend que deux magasins ont ajouté des équipes le week-end. Il s'agit donc d'un écart de volume lié aux heures d'ouverture, et non d'une augmentation de salaire.
Le résumé final pour les dirigeants tient en une ligne. Le profit est inférieur de 21,500 $ au budget, car une hausse de prix a coûté plus de volume qu'elle n'en a gagné, et le marketing ainsi que les équipes du week-end ont dépassé les prévisions.
Comment rédiger les notes d'écarts
Le tableau n'est que la moitié d'une analyse d'écarts. Les dirigeants lisent les notes, donc chacune doit répondre à trois questions : qu'est-ce qui a bougé, pourquoi cela a bougé et que se passe-t-il ensuite.
Une bonne note suit un modèle simple :
- Quoi. « Le marketing a dépensé 6,000 $ de plus que le budget, soit 30.0 % au-dessus du plan. »
- Pourquoi. « Deux campagnes payantes ont duré plus longtemps que prévu. »
- Ensuite. « Nous avons mis en pause une campagne le 3 octobre et prévoyons que le T4 sera conforme au plan. »
Limitez chaque note à deux ou trois phrases. Utilisez le même ordre de lignes que le tableau, afin que les lecteurs puissent faire correspondre les notes aux chiffres.
Séparez les écarts ponctuels des tendances. Une facture de réparation unique est différente d'une augmentation de salaire qui se répétera chaque mois. Marquez clairement les écarts récurrents, car ils modifient les prévisions.
Rédigez les notes avec le responsable de chaque ligne. L'équipe financière voit les chiffres, mais le responsable du budget en connaît la raison. Un court point avec chaque responsable transforme une supposition en explication.
Enfin, ajoutez un résumé d'une ligne en haut. Pour l'exemple ci-dessus, on lirait : « Le bénéfice d'exploitation du T3 est inférieur de 21,500 $ au budget, principalement en raison d'un volume plus faible et de dépenses marketing plus élevées. »
Le calendrier compte autant que la formulation. La plupart des équipes effectuent une analyse d'écarts chaque mois, après la clôture des comptes. Un rythme mensuel permet de détecter les problèmes tant qu'il est encore temps d'agir.
Les revues trimestrielles vont plus loin. Elles ajoutent la répartition prix et volume, une nouvelle prévision et un examen de la pertinence du budget lui-même. Les revues de fin d'année examinent l'année complète par rapport au plan initial.
Conservez le même modèle à chaque fois. Lorsque les colonnes et les règles restent fixes, une analyse d'écarts prend moins de temps chaque mois et les tendances sont plus faciles à identifier d'une période à l'autre.
Comment représenter les écarts sous forme de graphique
Un graphique aide les dirigeants à visualiser le passage du budget au réel. Le format le plus adapté est le graphique en cascade (waterfall chart).
Microsoft indique qu'un graphique en cascade « affiche un total cumulé au fur et à mesure que des valeurs sont ajoutées ou soustraites. » Il ajoute que « les graphiques en cascade sont également appelés graphiques en pont (bridge charts). »
Pour en construire un, listez les étapes du profit budgété au profit réel. Commencez par 55,000 $, puis ajoutez l'effet de chaque ligne sur le profit, et terminez par 33,500 $. Sélectionnez les données et choisissez Insertion > Insérer un graphique en cascade > Cascade.
Définissez la première et la dernière barre comme des totaux. La page de Microsoft note que les colonnes de début et de fin « commencent souvent sur l'axe horizontal », tandis que les barres intermédiaires flottent. Les augmentations et les diminutions sont codées par couleur, de sorte que les principaux facteurs ressortent.
Pour un suivi mois par mois, un simple graphique en colonnes de l'écart % fonctionne également. Notre guide sur comment construire un rapport budget vs réel couvre le rapport mensuel complet autour de ce tableau.
Réaliser une analyse d'écarts avec l'IA
Les formules prennent quelques minutes une fois que le tableau existe. La partie lente consiste souvent à faire correspondre les comptes, à trouver les facteurs déterminants et à rédiger les notes. Un outil d'IA peut préparer ces étapes à partir de vos exports.
Voici la méthode en trois étapes, en utilisant Powerdrill Bloom.
Étape 1 : Téléchargez votre budget et vos chiffres réels
Exportez le budget et les chiffres réels de votre outil de comptabilité ou de planification. L'AI Budget Analysis Tool accepte les « exports Excel, CSV ou PDF de la comptabilité et du FP&A. »
Incluez les données d'unité et de prix si vous souhaitez une répartition prix et volume. Gardez les noms de comptes identiques dans les deux fichiers.
Étape 2 : Demandez les écarts et les facteurs en langage naturel
Décrivez le résultat dont vous avez besoin. Par exemple : « Compare les chiffres réels du T3 au budget par poste. Signale les lignes de plus de 5 % et 2,000 $, et divise l'écart de revenus entre prix et volume. »
La page de l'outil liste « Écart par poste avec seuils de matérialité appliqués » comme l'une des vues. Elle liste également un classeur de prévisions révisées avec « budget original, cumul annuel réel et fin d'année révisée. »
Étape 3 : Vérifiez les chiffres et exportez le rapport
Comparez les totaux avec votre propre feuille avant de partager quoi que ce soit. Ensuite, exportez le résultat. La page Budget Analysis liste un « Tableau de bord des écarts avec les lignes significatives mises en évidence et les facteurs joints. »
Pour une vue continue, l'AI Finance Dashboard Builder couvre le P&L, la trésorerie, le besoin en fonds de roulement et les prévisions. Essayez Powerdrill Bloom avec le budget et les chiffres réels du dernier trimestre.
Erreurs courantes à éviter
Quelques erreurs apparaissent dans presque chaque première analyse d'écarts.
- Confondre le signe. Décidez une fois pour toutes si l'écart est réel moins budget, et utilisez cette règle partout.
- Considérer chaque écart positif comme bon. Des coûts plus élevés sont défavorables même si le chiffre est positif.
- Additionner les pourcentages. Recalculez les pourcentages totaux à partir des montants totaux.
- Comparer des périodes différentes. Un réel cumulé à date comparé à un budget mensuel donne un faux écart.
- Ignorer le calendrier. Une facture tardive peut créer deux écarts opposés sur deux mois.
- Tout expliquer. Les notes sur de petits écarts masquent ceux qui comptent vraiment.
- Saisir les chiffres à la main. Collez ou liez les chiffres réels depuis l'export, afin qu'une faute de frappe ne crée pas un faux écart.
- Sauter la vérification du total. Les écarts par ligne doivent s'additionner pour donner l'écart total. Si ce n'est pas le cas, une ligne est manquante ou comptée deux fois.
Foire aux questions
Quelle est la formule de l'écart dans Excel ?
Pour l'écart budgétaire, utilisez réel moins budget, par exemple =C2-B2. Pour le pourcentage, utilisez =IFERROR((C2-B2)/ABS(B2),"") et formatez la cellule en pourcentage.
Comment savoir si un écart est favorable ?
Cela dépend du type de ligne. Pour les revenus, un réel supérieur au budget est favorable. Pour les coûts, un réel inférieur au budget est favorable. Une colonne Type et une formule IF peuvent étiqueter chaque ligne.
Qu'est-ce qu'un écart significatif (material variance) ?
C'est un écart suffisamment important pour être expliqué. Les équipes fixent leur propre règle, comme plus de 5 % et plus de 2,000 $. Les lignes qui passent ces deux tests reçoivent une note écrite.
Quel graphique est le meilleur pour une analyse d'écarts ?
Un graphique en cascade, également appelé graphique en pont, montre comment chaque ligne déplace le total du budget vers le réel. Excel le propose sous Insertion > Insérer un graphique en cascade.
Copilot dans Excel peut-il effectuer une analyse d'écarts ?
Microsoft répertorie une compétence d'analyse d'écarts pour Copilot dans Excel. Il compare les chiffres réels aux budgets, prévisions ou périodes précédentes et prépare des explications pour révision. Cela nécessite une licence Copilot.
Sources : Microsoft Support, Utiliser les compétences intégrées avec Copilot dans Excel · Microsoft Support, Créer un graphique en cascade · Microsoft Support, Utiliser la mise en forme conditionnelle pour mettre en évidence des informations dans Excel.