Si vous avez à élaborer des analyses ou des tableaux de bord dans Excel, nul doute que vous devez calculer des écarts. En effet, il est souvent intéressant de comprendre si les données ont évolué de façon position ou négative dans le temps et dans quel ordre de grandeur ce changement s’est effectué. “Rien de plus facile”, je vous entends déjà me dire. Pourtant, j’observe souvent des erreurs fondamentales dans les analyses et les tableaux de bord que j’audite, au niveau des calculs d’écarts. L’article suivant vous met en garde contre l’une de ces erreurs courantes.
Calculs d’écarts dans Excel
Dans l’exemple ci-bas, nous avons une première section qui présente la valeur de 3 composantes au temps t ainsi que la valeur du total des 3 composantes au temps t. Les flèches rouges et vertes indiquent le sens de l’écart entre la valeur au temps t et la valeur au temps t-1. Dans la section du dessous, on élabore davantage sur ces écarts. D’abord, on présente l’écart en $ vs t-1, ensuite on présente la valeur en $ à t-1 et finalement, on présente l’écart en % vs t-1. Vous noterez que la première méthode donne des résultats erronés alors que la deuxième méthode donne le résultat recherché. En effet, si vous jetez un coup d’oeil à la composante 2, elle est passée de -13,105,055$ au temps t-1 à -14,235,8134$ au temps t. Elle a donc diminué de -1,130,758$. C’est pourquoi on voit une flèche rouge dans la section tout en haut de l’image. Dans l’autre section, toutefois, on voit que la “mauvaise méthode” montre une flèche verte (alors que la “bonne méthode” une flèche rouge).
Mise en forme conditionnelle pour faire ressortir les écarts
Avant d’aller plus loin, jetons rapidement un coup d’oeil au format conditionnel de la section tout en haut de l’image. Ce format conditionnel a été ajouté sur le calcul de l’écart entre la valeur au temps t et la valeur au temps t-1. On demande simplement à Excel de mettre en rouge les écarts négatifs, en vert les écarts positifs et en jaune les écarts nuls. On demande également de présenter seulement l’icône (Show Icon Only) pour ne voir que l’icône, sans la valeur associée.
Erreur au niveau des calculs d’écarts
Dans la deuxième section, soit celle de l’explication des écarts, si vous inscrivez machinelement vos formules d’écarts tel qu’illustré ci-bas, vous obtiendrez la bonne variation en % mais pas dans le bon sens pour la composante 2. En effet, les deux signes négatifs se combineront e variation positive pour vous laisser avec un écart positif de 9% alors que la valeur de la composante 2 a chuté au temps t par rapport au temps t-1. C’est d’ailleurs ce que nous indique le montant -1,130,758$.
Calculs d’écarts adéquats
Pour vous assurer de présenter votre écart dans le bon sens (surtout si vous utilisez un format conditionnel), n’oubliez pas d’inclure dans votre formule un Si ou un If en anglais, pour traiter le cas des dénominateurs négatifs, tel qu’illustré ci-bas.
Formation complémentaire
Pour une introduction à l’élaboration d’un tableau de bord dans Excel, et ainsi apprendre à alimenter, structurer et présenter vos données, suivez notre formation Excel – Tableaux de bord (niveau 1).
Voici quelques commentaires d’apprenants ayant suivi la formation Excel – Tableaux de bord (niveau 1) :
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
Excellent post et effectivement, j’ai vu le cas même dans de grandes entreprises internationales.
On peut encore simplifier on mettant le dénominateur en valeur absolue grâce à ABS.
Bien dit et expliqué Sophie! Également, pour ajouter au sujet du calcul des écarts, à travers mon expérience au sein de grandes entreprises nord-américaines et avec des clients d’envergure mondiale, j’ai participé à de longues et parfois animées discussions sur les différentes façons de présenter les écarts entre période dans les États des Résultats et autres rapports. Un sujet qui semble si simple peut initier des débats passionnés chez les comptables et financiers! J’en suis venu à me faire une opinion personnelle avec le temps que la méthode la plus claire pour moi (et que j’utilise dans mes propres entreprises maintenant) est d’avoir une calcul différent/opposé pour les postes de revenus versus les postes des dépenses. Mon but est de toujours présenter les variances/écarts favorables au positif et les variances/écarts défavorables au négatif, avec une petite légende à cet effet pour le lecteur. De cette manière, le lecteur n’a pas besoin de réfléchir à quel type de poste fait référence une rangée (revenu vs dépense) pour savoir si la variance est de de bon augure ou ou non. Dans ta pratique, est-ce que tu recommandes une méthode particulière? Ou est-ce que tu préfères y aller avec la méthode actuelle/préférée de ton client vu qu’il n’y a pas vraiment de “mauvaise” méthode?
Bonjour,
J’utilise pour ma part une alternative sans test conditionnel avec la fonction signe() appliquée au dénominateur :
=signe(F15)*((F$4-F15)/F15)
Excellent cledieu