Attention à vos calculs d’écarts dans vos analyses et tableaux de bord

Publié le 16 mars 2015
par Sophie Marchand M.Sc.
Excel : Calculs d'écarts

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).

Excel : Calculs d'écarts

 

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.

Excel : Calculs d'écarts

 

Vous devez consolider des données, les analyser, les interpréter et présenter des conclusions utiles pour des fins de prise de décision ? Apprenez à développer des solutions robustes et automatisées avec nos formations en Tableaux de bord avec Excel.

 

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$.

Excel : Calculs d'écarts

 

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.

Excel : Calculs d'écarts

 


 

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) :

Commentaire d'apprenant - Excel – Tableaux de bord (niveau 1)

CFO-Masque_Formations-en-ligne_FBLa 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é.

4 réflexions sur “Attention à vos calculs d’écarts dans vos analyses et tableaux de bord”

  1. 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.

  2. 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?

  3. 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)

Laisser un commentaire

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

Retour en haut