Contact: 514-605-7112 / info@lecfomasque.com

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

Publié le : 16 mars 2015

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

.

Format conditionnel

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 caclul 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

 

Erreur de calcul

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

 

Calcul adéquat

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

 


NOTRE OFFRE DE FORMATIONS


Microsoft Most Valuable Professional

Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Finance corporative et Compléments avancés. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et ils sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

 

Découvrez quelles formations vous conviennent

Pour info: 514-605-7112 ou info@lecfomasque.com

 

Sophie Marchand

Sophie Marchand, M.Sc., CPA, CGA, MVP, est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA. Elle est également MVP Office Apps and Services (Excel) et MVP Data Platform (Power BI). Le titre de MVP est un titre honorifique remis par Microsoft et signifie « Most Valuable Professional ». Il est attribué à des experts de solutions Microsoft qui se distinguent non seulement par leurs compétences mais également par leur implication dans la communauté. Seuls 4 professionnels possèdent cette double-qualification dans le monde. Mme Marchand cumule de nombreuses années d’expérience dans le milieu des affaires et se spécialise en modélisation financière et en intelligence d’affaires avec Excel et Power BI. Après avoir longtemps offert ses services comme consultante, elle se consacre désormais à la formation, sous toutes ses formes (formations en classe, formations en ligne, formations en entreprise, webinaires, animation de groupe d'usagers, rédaction d'articles de blogue, animation de forum en ligne et plus encore). Vous pouvez d'ailleurs la retrouver à la tête du groupe d'usagers Montreal Modern Excel and Power BI.

  • réponse Rylewski ,

    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.

    • réponse Eric ,

      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?

      • réponse cledieu ,

        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)

        • réponse Christophe ,

          Excellent cledieu

          Répondre à Christophe Annuler