Budget contre réel dans Excel : comment analyser les écarts

Établir un budget, c’est la partie facile. Le plus dur arrive en fin de mois, quand les dépenses réelles ne collent pas aux prévisions et qu’il faut expliquer pourquoi. Une feuille Excel bien construite vous indique en quelques secondes quels postes ont dérapé, de combien, et si l’écart est inquiétant ou simplement anecdotique.

Dans ce guide, vous allez préparer un tableau budget contre réel, calculer l’écart en montant et en pourcentage, synthétiser les dépenses par poste avec SOMME.SI.ENS, mettre en évidence automatiquement ce qui dépasse un seuil et éviter les erreurs qui faussent le plus l’analyse.

Préparez le tableau : budget et réel côte à côte

L’analyse des écarts ne fonctionne que si chaque poste apparaît une seule fois, avec le même nom dans le budget et dans le registre des dépenses. Avant d’écrire la moindre formule, organisez la feuille ainsi :

1. Dans la colonne A, le nom de chaque poste (Loyer, Marketing, Énergie…), sans doublons.
2. Dans la colonne B, le montant budgété pour la période.
3. Dans la colonne C, la dépense réelle de la même période.
4. Réservez les colonnes D et E à l’écart en montant et en pourcentage.
5. Sélectionnez la plage et appuyez sur Ctrl + T pour la convertir en tableau : les formules se recopieront seules sur chaque nouvelle ligne.

Avec des données d’exemple, la feuille ressemblerait à ceci :

Remarquez que le total tombe presque juste (seulement 20 en dessous), alors qu’à l’intérieur certains postes s’écartent fortement dans les deux sens. C’est pourquoi regarder la dernière ligne ne suffit jamais.

Calculez l’écart en montant et en pourcentage

Écart absolu

C’est la différence entre ce que vous avez dépensé et ce que vous aviez prévu. Dans la cellule D2, saisissez :

=C2-B2

Avec cette convention, un résultat positif signifie que vous avez dépensé plus que prévu, et un résultat négatif, que vous avez dépensé moins. L’essentiel est de choisir une convention et de la conserver dans tout le classeur.

Écart en pourcentage

Le montant seul est trompeur : 150 de plus sur un poste de 800 ne pèse pas autant que 150 de plus sur un poste de 10 000. Pour comparer des postes de tailles différentes, divisez l’écart par le budget. En E2 :

=SI(B2=0;"";D2/B2)

La condition évite l’erreur #DIV/0! quand un poste n’a pas de budget. Appliquez ensuite le format pourcentage avec Accueil › Style de pourcentage et ajoutez une décimale si besoin.

Favorable ou défavorable ?

Le signe n’a pas toujours le même sens. Tout dépend si la ligne est une dépense ou une recette :

• Dépenses : un écart positif est défavorable (vous avez trop dépensé) et un écart négatif est généralement favorable, sauf s’il signifie qu’une action prévue n’a pas eu lieu.
• Recettes : c’est l’inverse. Encaisser plus que prévu est favorable ; encaisser moins, défavorable.
• Postes non budgétés : toute dépense réelle est un écart à justifier, même si le pourcentage ne peut pas être calculé.

Du détail des dépenses à la synthèse par poste

En général, les dépenses réelles n’arrivent pas déjà synthétisées, mais sous forme de liste de mouvements : chaque facture ou paiement sur une ligne. Imaginons une feuille nommée Dépenses avec la date en colonne A, le poste en B, le montant en C et le mois en D. Pour ramener le total réel de chaque poste dans le tableau d’analyse, saisissez en C2 :

=SOMME.SI.ENS('Dépenses'!$C:$C;'Dépenses'!$B:$B;$A2)

Pour analyser un mois précis, écrivez le mois dans une cellule de contrôle, par exemple H1, et ajoutez un second critère :

=SOMME.SI.ENS('Dépenses'!$C:$C;'Dépenses'!$B:$B;$A2;'Dépenses'!$D:$D;$H$1)

Remplacez janvier par février dans H1 et tout le tableau se recalcule. SOMME.SI.ENS existe depuis Excel 2007 : elle fonctionne donc dans pratiquement toutes les versions, y compris Excel pour le web.

Pour vérifier que rien n’est oublié, additionnez la colonne C du tableau et comparez-la au total des montants de la feuille Dépenses. S’ils diffèrent, certains mouvements portent un nom de poste mal orthographié ou absent du budget.

Mettez en évidence les écarts qui comptent

Tous les écarts ne méritent pas une réunion. Fixez un seuil, par exemple 10 % au-dessus ou en dessous, et laissez Excel classer chaque poste. En F2 :

=SI(E2="";"Sans budget";SI(E2>0,1;"À revoir";SI(E2<-0,1;"Sous-utilisé";"Conforme")))

Avec les données d’exemple, Marketing (18,8 %) et Déplacements (28 %) apparaissent en À revoir, Formation (-70 %) en Sous-utilisé et le reste en Conforme. Énergie, à exactement -10 %, reste dans la marge car la comparaison est stricte.

Pour le voir d’un coup d’œil, ajoutez de la couleur :

1. Sélectionnez les valeurs de la colonne E, sans l’en-tête.
2. Allez dans Accueil › Mise en forme conditionnelle › Règles de mise en surbrillance des cellules › Supérieur à.
3. Saisissez 0,1 et choisissez un remplissage rouge clair.
4. Recommencez avec Inférieur à, la valeur -0,1 et un remplissage jaune, pour repérer les dépenses non engagées.

Erreurs fréquentes en comparant budget et réel

• Noms de postes différents : « Marketing » dans le budget et « Marketing » suivi d’une espace dans les dépenses ne correspondent pas, et la somme renvoie 0. Nettoyez les textes avec SUPPRESPACE ou utilisez une liste déroulante pour saisir les postes.
• Périodes qui ne correspondent pas : comparer le budget annuel avec trois mois de dépenses produit des écarts énormes et faux. Proratisez le budget ou comparez cumul avec cumul.
• Montants stockés en texte : si les dépenses viennent d’un CSV ou d’un relevé bancaire, certains nombres peuvent rester au format texte et SOMME.SI.ENS les ignore. Vérifiez qu’ils sont alignés à droite.
• Mouvements en double : une facture enregistrée deux fois gonfle les dépenses réelles. Avant l’analyse, vérifiez qu’il n’y a pas de lignes répétées.
• Ne regarder que le total : comme le montre l’exemple, un total presque parfait peut cacher des postes très décalés qui se compensent.

Comparez les versions du budget

Le budget évolue lui aussi : quelqu’un ajuste un poste, une ligne est ajoutée ou un montant est corrigé en cours d’exercice. Si vous travaillez avec plusieurs versions du fichier, mieux vaut savoir exactement ce qui a changé avant d’analyser les écarts, car une partie de la différence peut venir d’un budget qui n’est plus le même. Avec notre outil gratuit pour comparer des fichiers Excel, vous importez l’ancienne et la nouvelle version et obtenez un fichier qui signale les modifications. Et si les dépenses réelles vous arrivent réparties en un fichier par mois ou par service, vous pouvez fusionner tous les fichiers en un seul avant d’appliquer les formules.

Conclusion

Analyser les écarts ne demande pas de logiciel financier. Il suffit d’un tableau bien structuré, d’une soustraction, d’une division protégée contre le zéro et d’une règle qui signale ce qui dépasse votre seuil. Ce qui fait vraiment la différence, c’est la rigueur : les mêmes noms de postes, les mêmes périodes et une convention de signe claire. Ainsi, chaque clôture mensuelle devient une revue de quelques minutes plutôt qu’une recherche à l’aveugle.

Questions fréquentes