Communication d’informations financières

Les Données financières sont la pierre angulaire des affaires. Presque tous les tableaux de bord d’entreprise contiennent des Données financières. Alors que les opérations déterminent les finances, les Données financières sont le principal instrument de mesure de la performance d’une entreprise.

Savoir quelles Données surveiller et quoi en faire n’est pas une tâche facile. Mais tout commence par la surveillance, et les tableaux de bord sont l’outil parfait pour cela.

Étude de cas: informations financières et ratios

Votre entreprise vous a demandé de créer un tableau de bord financier pour le service Finance et Comptabilité. Ils aimeraient voir les Données de l’année en cours ainsi que les tendances au cours des cinq dernières années.

Planification et mise en page

Le service financier a identifié des paramètres qui, selon lui, sont essentiels pour comprendre la santé financière de l’entreprise. En plus de ces mesures, ils aimeraient voir un résumé du compte de résultat de l’année en cours et une tendance des revenus sur cinq ans sur le tableau de bord. Les paramètres et une brève description de chacun sont les suivants :

  • Ratio courant : Actifs courants divisés par les passifs courants. Ce ratio aide les utilisateurs à comprendre la capacité de l’entreprise à s’acquitter de ses obligations au cours de l’année prochaine.
  • Quick Ratio : Trésorerie et créances divisées par les passifs courants. Ce ratio est une indication de la capacité de l’entreprise à s’acquitter de ses obligations à court terme.
  • Dette sur capitaux propres : Traditionnellement calculée comme le total du passif divisé par le total des capitaux propres. Cependant, la société a modifié la formule de reporting interne pour n’inclure que les dettes portant intérêt.
  • Cycle de conversion de trésorerie : jours de ventes d’inventaire plus jours de ventes en cours moins jours de créditeurs en souffrance. Cela mesure le temps nécessaire à l’entreprise pour convertir les stocks en espèces.
  • Pourcentage de marge brute : ventes moins le coût des ventes divisé par les ventes.
  • Pourcentage de marge nette : Marge brute nette des dépenses d’exploitation et autres dépenses ou revenus divisée par les ventes.

Pour chacun de ces paramètres, ils aimeraient voir la valeur de l’année en cours et la tendance sur cinq ans. Vous soumettez la mise en page approximative illustrée à la figure 1 pour approbation.

Figure 1: Une présentation approximative du tableau de bord financier

Collecte des Données

Vous disposez du bilan et des Données sur les revenus illustrés à la figure 2. Vous devrez calculer les ratios pour piloter les graphiques.

Figure 2: Données du bilan et du compte de résultat

Créez de nouvelles lignes sous les Données financières fournies et utilisez les formules du tableau 2 pour calculer les métriques.

Tableau 2: Formules de métriques financières

MÉTRIQUE FORMULE
Dette sur capitaux propres = C10 / SOMME (C11: C13)
Jours de vente de l’inventaire = MOYENNE (B4: C4) / C17 * 365
Jours de vente en cours = MOYENNE (B3: C3) / – C16 * 365
Jours créditeurs impayés = -MOYENNE (B8: C8) / C17 * 365
Cycle de conversion monétaire = C25 + C26-C27
Rapport actuel = -SOMME (C2: C5) / SOMME (C8: C9)
Ratio rapide = -SOMME (C2: C3) / SOMME (C8: C9)
Marge brute = C18 / C16
Marge nette = C22 / C16

 

Construction des éléments visuels

Créez une nouvelle feuille de calcul nommée Dashboard et commencez à préparer les Données pour chaque élément visuel.

Compte de résultat sommaire

Vous choisissez de présenter le compte de résultat résumé sous forme de graphique en cascade.

Heureusement, les Données sources sont presque déjà bien formatées pour ce graphique.

Les comptables ont présenté les comptes de solde créditeur, comme les ventes, comme négatifs. Pour le graphique en cascade, vous devrez inverser les signes en suivant ces étapes:

  1. Dans une cellule inutilisée, entrez -1.
  2. Copiez la cellule en appuyant sur Ctrl + C.
  3. Sélectionnez les cellules C16: G23.
  4. Dans l’onglet Accueil du ruban, sélectionnez Collage ➪ Collage spécial pour afficher la boîte de dialogue Collage spécial.
  5. Sélectionnez Multiplier comme illustré à la figure 3 et cliquez sur OK.
  6. Supprimez le contenu de la cellule contenant -1.

Figure 3: Inversion des signes via Collage spécial ➪ Multiplier

Pour créer le graphique en cascade, sélectionnez les cellules A16: A22, maintenez la touche Ctrl enfoncée et sélectionnez G16: G22. Dans le ruban, choisissez Insérer ➪ Cascade pour créer le graphique initial illustré à la figure 4.

Figure 4: Le graphique en cascade initial

Coupez le graphique et collez-le dans la feuille de calcul du tableau de bord. Mettez en forme le graphique en suivant ces étapes:

  1. Remplacez le titre du graphique par Compte de résultat de l’année en cours (en milliers).
  2. Supprimez la légende.
  3. Supprimez l’axe vertical.
  4. Cliquez deux fois sur le point de Données Marge pour sélectionner uniquement ce point de Données. Cliquez avec le bouton droit sur le point de Données et choisissez Formater le point de Données. Cochez la case Définir comme total.
  5. Répétez ce processus pour les points de Données Bénéfice d’exploitation et Bénéfice net. La figure 5 montre le graphique après avoir vérifié Définir comme total pour ces points de Données.

Figure 5: Définissez les points de Données totaux.

  1. Supprimez le quadrillage.
  2. Mettez en forme les étiquettes de Données au format numérique personnalisé * #, ###,; *

(#, ###,).

  1. Ajoutez des sauts de ligne à toutes les étiquettes d’axe trop longues, telles que Bénéfice d’exploitation et Dépenses d’intérêts. Dans les cellules de Données source, placez votre curseur à l’endroit où vous voulez le saut de ligne et appuyez sur Alt + Entrée. Le graphique final en cascade est illustré à la figure 6.

Figure 6: Le graphique en cascade du compte de résultat de l’année en cours

Ratios de l’année en cours

Les éléments du ratio de l’année en cours seront quatre graphiques de taille égale montrant le ratio actuel, le ratio rapide, le ratio d’endettement et le cycle de conversion de trésorerie. Ce seront de simples nombres, mais vous décidez de mettre un graphique de style jauge derrière eux. Vous consultez le service des finances pour déterminer une limite supérieure raisonnable pour chacun des ratios. Ils vous disent d’utiliser 4 pour les ratios actuels et rapides, 2 pour le ratio dette / capitaux propres et 30 pour le cycle de conversion de trésorerie.

Commencez par créer un tableau sur la feuille de calcul Données comme celui illustré à la Figure 7. Entrez la valeur maximale fournie pour chaque métrique. Pour la valeur, pointez sur la cellule de la colonne G pour cette métrique. Par exemple, la formule Valeur actuelle dans la cellule B37 est = G30. Pour le calcul du blanc, entrez la formule = B40-B37 et remplissez. La formule pour le bas dans la cellule B39 est = B40 / 0.8-B40 et remplissez-la également dans les autres colonnes.

Figure 7: Mettre en scène les Données des graphiques à jauge.

Sélectionnez les cellules A37: B39 et dans le ruban, choisissez Insérer ➪ Insérer un graphique à secteurs ou en anneau ➪ En anneau pour créer le graphique illustré à la Figure 8.

Figure 8: Le graphique en anneau du rapport de courant initial

Coupez le graphique et collez-le dans la feuille de calcul du tableau de bord. Redimensionnez le graphique à la taille approximative du graphique final. Suivez ces étapes pour mettre en forme le graphique:

  1. Supprimez la légende.
  2. Modifiez le titre du graphique en Ratio actuel et définissez sa taille de police 10,5.
  3. Cliquez avec le bouton droit sur la série et choisissez Formater la série de Données. Définissez l’angle de la première tranche sur 216 et la taille du trou de l’anneau sur 57.
  4. Dans le ruban, choisissez Outils de graphique ➪ Format et sélectionnez Zone de traçage dans la liste déroulante Element de graphique pour afficher les poignées de dimensionnement de la zone de traçage. Redimensionnez la zone de tracé pour remplir davantage le graphique, comme illustré à la Figure 9.

Figure 9: Redimensionnez la zone de tracé pour remplir davantage la zone de graphique.

  1. Sélectionnez le point de Données Valeurs en cliquant deux fois dessus et modifiez la couleur du remplissage à 40% de bleu.
  2. Sélectionnez le point de Données Vide et changez la couleur de remplissage en 15% de blanc.
  3. Sélectionnez le point de Données Bas et modifiez le remplissage en Aucun remplissage. La figure 10 montre le graphique après ces modifications.

Figure 10: définissez les couleurs de remplissage pour chaque point de Données.

  1. Sélectionnez le point de Données Valeur et choisissez Étiquettes de Données ➪ Afficher dans la boîte de dialogue Element du graphique.
  2. Sélectionnez l’étiquette de Données, déplacez-la et redimensionnez-la afin que l’étiquette de Données soit au centre de la jauge. Modifiez la taille de la police en 24 et la couleur de la police en bleu foncé. Le tableau de jauge final est illustré à la figure 11.

Figure 11: Le graphique de la jauge de rapport actuel

Pour créer les autres graphiques à trois jauges, copiez le graphique de rapport actuel et collez-le trois fois dans la feuille de calcul. Modifiez les titres des nouveaux graphiques afin de savoir sur lequel vous travaillez. Vous devrez peut-être recentrer les titres des graphiques.

Sélectionnez la série dans le graphique des ratios rapides et remplacez la formule SERIE dans la barre de formule par = SERIE(, Données! $ A $ 37: $ A $ 39, Données! $ C $ 37: $ C $ 39,1) pour pointer vers les Données correctes . Excel conserve les propriétés du graphique, comme la taille, mais malheureusement les propriétés de la série sont perdues. Répétez l’étape à partir du graphique de ratio actuel pour formater le graphique de ratio rapide. Répétez ensuite le processus de modification de la formule SERIE et de la mise en forme des trois autres graphiques. Les graphiques à quatre jauges sont illustrés à la figure 12.

Figure 12: Quatre graphiques de jauge de ratio de bilan

Marges de l’année en cours

Pour les deux autres graphiques plus petits dans la partie supérieure droite du tableau de bord, vous décidez des jauges derrière des nombres similaires aux ratios du bilan. Pour les distinguer, vous utiliserez une couleur différente pour la série. Ajoutez deux colonnes au tableau que vous avez créé dans la section précédente.

Pointez sur les cellules appropriées pour la formule Valeur. Utilisez respectivement 40% et 7% pour la valeur Max pour la marge brute et nette. Remplissez les formules Vide et Bottom à droite pour les nouvelles colonnes. La figure 13 montre le tableau mis à jour.

Figure 13: Calculs des marges brutes et nettes Copiez l’un des autres graphiques à jauge et collez-le deux fois sur la feuille de calcul.

Sélectionnez l’un des nouveaux graphiques et, dans le ruban, choisissez Outils de graphique ➪ Format et sélectionnez Série 1 dans la liste déroulante Eléments du graphique. Modifiez la formule SERIE dans la barre de formule pour qu’elle pointe vers la colonne F pour la marge brute.

Répétez le processus pour l’autre nouveau graphique, sauf pointez sur la colonne G pour la marge nette.

Modifiez la couleur de remplissage des points de Données et ajoutez et formatez l’étiquette de Données comme vous l’avez fait pour les autres graphiques à jauge. Le dernier panneau de six graphiques est illustré à la figure 14.

Figure 14: Le panneau de jauge à six cartes

Mesures du compte de résultat sur cinq ans

Les trois graphiques suivants présentent les tendances sur cinq ans pour trois mesures du compte de résultat: les revenus, la marge brute et la marge nette. Vous pouvez créer des zones de transit pour ces graphiques, mais il n’y a pas de formules supplémentaires, vous pouvez donc trouver tout aussi simple de conserver les Données source là où elles se trouvent.

Pour créer le graphique des revenus, sélectionnez les cellules C1: G1 dans la feuille de calcul Données. Maintenez la touche Ctrl enfoncée et sélectionnez les cellules C16: G16. Dans l’onglet Insertion du ruban, cliquez sur Graphiques recommandés et choisissez l’option Colonne en cluster. Coupez le graphique et collez-le dans la feuille de calcul du tableau de bord. Suivez ces étapes pour mettre en forme le graphique:

  1. Remplacez le titre du graphique par Revenus (en millions).
  2. Formatez l’axe vertical pour commencer à zéro, puis supprimez-le.
  3. Supprimez le quadrillage.
  4. Modifiez la couleur de remplissage de la série de Données pour qu’elle corresponde aux graphiques à jauge liés au compte de résultat. Les graphiques à jauge étant censés être en arrière-plan, choisissez un accent plus sombre dans la même colonne de couleur.
  5. Ajoutez des étiquettes de Données et modifiez le format des nombres en 0,00 ,,. Le graphique des revenus est illustré à la figure 15.

Figure 15: Le graphique des revenus sur cinq ans

Copiez le graphique des revenus et collez-le deux fois dans la feuille de calcul pour créer les graphiques des marges brutes et nettes. Remplacez les titres des graphiques par Marge brute et Marge nette. Notez dans la feuille de calcul Données que la marge brute est à la ligne 33 et la marge nette à la ligne 34. Sélectionnez la série de Données sur chaque graphique et modifiez la série dans la barre de formule comme suit:

Marge brute: = SERIE (, Données! $ C $ 1: $ G $ 1, Données! $ C $ 33: $ G $ 33,1)

Marge nette: = SERIE (, Données! $ C $ 1: $ G $ 1, Données! $ C $ 34: $ G $ 34,1)

Modifiez les couleurs de remplissage pour qu’elles correspondent au graphique des revenus. Ajoutez des étiquettes de Données et, si nécessaire, modifiez le format des nombres en Pourcentage avec deux décimales. Les trois graphiques du compte de résultat sur cinq ans sont présentés à la figure 16.

Figure 16: Trois graphiques du compte de résultat sur cinq ans

Mesures du bilan quinquennal

Pour les graphiques de tendance du bilan, utilisez un graphique en courbes pour le distinguer visuellement des graphiques du compte de résultat. Copiez l’un des graphiques du compte de résultat et collez-le. Changez le type de graphique en Ligne avec marqueurs. Modifiez les étiquettes de Données pour qu’elles soient au-dessus des marqueurs en sélectionnant Étiquettes de Données ➪ Au-dessus dans la boîte de dialogue Element du graphique. Maintenant, faites deux copies de ce tableau pour compléter l’ensemble.

Remplacez les titres du graphique par Cycle de conversion de trésorerie, Ratio dette / capitaux propres et supprimez le titre du troisième graphique car vous utiliserez la légende comme titre.

Pour le graphique de conversion de trésorerie, modifiez la formule SERIE pour être

= SÉRIE (, Données! $ C $ 1: $ G $ 1, Données! $ C $ 29: $ G $ 29,1).

Modifier la SÉRIE formule pour que le graphique dette / capitaux propres soit

= ​​SERIE (, Données! $ C $ 1: $ G $ 1, Données! $ C $ 25: $ G $ 25,1).

Pour le graphique du ratio actuel / rapide, modifiez la formule SERIE comme suit:

= SERIE (, Données! $ C $ 1: $ G $ 1, Données! $ C $ 30: $ G $ 30,1). Remplacez le format numérique des étiquettes de Données des trois graphiques par Nombre avec deux décimales.

Pour ajouter les Données de rapport rapide au dernier graphique, sélectionnez C31: G31 dans la feuille de calcul Données et appuyez sur Ctrl + C pour le copier. Sélectionnez le graphique ratio actuel / rapide sur la feuille de calcul du tableau de bord et sélectionnez Accueil ➪ Coller ➪ Collage spécial sur le ruban pour ajouter une nouvelle série. La boîte de dialogue Collage spécial est illustrée à la Figure 17.

Figure 17: Collage d’une nouvelle série dans un graphique existant

Pour chaque graphique, modifiez la couleur de remplissage de ligne et les couleurs de remplissage et de bordure du marqueur pour obtenir un accent plus foncé de la même couleur que celle utilisée dans les graphiques de jauge du bilan. Pour la série de ratios rapides, choisissez une couleur différente, mais n’utilisez pas la même couleur que les graphiques du compte de résultat.

Ajoutez une légende en haut du graphique ratio actuel / rapide à l’aide de la boîte de dialogue Element de graphique. Pour modifier les noms de séries dans la légende, cliquez avec le bouton droit sur le graphique et choisissez Sélectionner les Données. Dans la boîte de dialogue Sélectionner la source de Données, sélectionnez Série 1 et cliquez sur le bouton Modifier pour afficher la boîte de dialogue Modifier la série, illustrée à la figure 18. Changez le nom de la série en = Données! $ A $ 30 et cliquez sur OK.

Répétez le processus pour changer la série 2 pour qu’elle pointe vers la cellule avec l’étiquette de rapport rapide. Cliquez sur OK pour fermer la boîte de dialogue Sélectionner la source de Données. Sélectionnez la légende et définissez la taille de la police pour qu’elle soit la même que celle des deux autres graphiques de tendance du bilan. La figure 19 montre les six graphiques de tendance.

Figure 18: pointez le nom de la série vers une cellule.

Figure 19: Six graphiques de tendance pour les Données financières

Disposition du tableau de bord

Avec autant d’éléments sur ce tableau de bord, il est préférable d’utiliser VBA pour positionner et redimensionner les graphiques. Les graphiques à jauge sont les plus difficiles en raison de leur petite taille et du placement de l’étiquette de Données au centre de la jauge, utilisez-les donc comme point d’ancrage et ajustez tous les autres graphiques par rapport à ceux-ci.

Commencez par nommer chaque graphique afin que le code VBA soit plus facile à lire. Sélectionnez chaque graphique et tapez un nouveau nom dans la zone Nom juste à gauche de la barre de formule. Appuyez sur Alt + F11 pour ouvrir le VBE. Appuyez sur Ctrl + R pour afficher l’explorateur de projet s’il ne s’affiche pas déjà. Recherchez votre projet dans l’Explorateur de projets, cliquez avec le bouton droit sur le nom du projet et choisissez Insérer ➪ Module dans le menu contextuel.

Cela ouvrira un nouveau volet de code pour votre code VBA. Entrez le code suivant dans le volet de code:

Sub MoveCharts()

 With ActiveSheet

    ‘ligne supérieure des graphiques de jauge

                MoveSingleChart chtDest:=.Shapes(“JaugeRapide”), _ chtSource:=.Shapes(“JaugeActuel”), _ LeftAdd:=.Shapes(“JaugeActuel”).Width,TopAdd:=0, _ WidthFactor:=1, HeightFactor:=1

 MoveSingleChart chtDest:=.Shapes(“GrossJauge”), _ chtSource:=.Shapes(“JaugeRapide”), _ LeftAdd:=.Shapes(“JaugeRapide”).Width,TopAdd:=0, _ WidthFactor:=1, HeightFactor:=1

 ‘Rangée inférieure des graphiques à jauge

 MoveSingleChart chtDest:=.Shapes(“DtoEJauge”), _ chtSource:=.Shapes(“JaugeActuel”), _ LeftAdd:=0,TopAdd:=.Shapes(“JaugeActuel”).Height, _ WidthFactor:=1, HeightFactor:=1

 MoveSingleChart chtDest:=.Shapes(“CCCJauge”), _ chtSource:=.Shapes(“DtoEJauge”), _ LeftAdd:=.Shapes(“DtoEJauge”).Width, TopAdd:=0,_ WidthFactor:=1, HeightFactor:=1

                MoveSingleChart chtDest:=.Shapes(“JaugeNet”), _ chtSource:=.Shapes(“CCCJauge”), _ LeftAdd:=.Shapes(“CCCJauge”).Width, TopAdd:=0,_ WidthFactor:=1, HeightFactor:=1

                ‘Cascade. Définissez d’abord la largeur car elle soustrait

 .Shapes(“Eau”).Width =.Shapes(“JaugeActuel”).Width * 3

 MoveSingleChart chtDest:=.Shapes(“Eau”), _ chtSource:=.Shapes(“JaugeActuel”), _ LeftAdd:=.Shapes(“Eau”).Width * -1,TopAdd:=0, _ WidthFactor:=3, HeightFactor:=2

 

 ‘Rangée supérieure de graphiques de tendance, 75% de la hauteur de la cascade

 MoveSingleChart chtDest:=.Shapes(“RevCourbe”), _ chtSource:=.Shapes(“Eau”), _  LeftAdd:=0, TopAdd:=.Shapes(“Eau”).Height, _ WidthFactor:=2 / 3, HeightFactor:=0.75

 MoveSingleChart chtDest:=.Shapes(“GrandeCourbe”), _ chtSource:=.Shapes(“RevCourbe”), _ LeftAdd:=.Shapes(“RevCourbe”).Width, TopAdd:=0,_ WidthFactor:=1, HeightFactor:=1

                MoveSingleChart chtDest:=.Shapes(“CourbeNet”), _ chtSource:=.Shapes(“GrandeCourbe”), _ LeftAdd:=.Shapes(“GrandeCourbe”).Width,TopAdd:=0, _ WidthFactor:=1, HeightFactor:=1

 

 ‘Rangée inférieure des graphiques de tendance

 MoveSingleChart chtDest:=.Shapes(“CCCCourbe”), _ chtSource:=.Shapes(“RevCourbe”), _ LeftAdd:=0, TopAdd:=.Shapes(“RevCourbe”).Height,_ WidthFactor:=1, HeightFactor:=1

                MoveSingleChart chtDest:=.Shapes(“DtoECourbe”), _ chtSource:=.Shapes(“CCCCourbe”), _ LeftAdd:=.Shapes(“CCCCourbe”).Width, TopAdd:=0,_ WidthFactor:=1, HeightFactor:=1

                MoveSingleChart chtDest:=.Shapes(“CourbeActuel”), _ chtSource:=.Shapes(“DtoECourbe”), _ LeftAdd:=.Shapes(“DtoECourbe”).Width, TopAdd:=0,_ WidthFactor:=1, HeightFactor:=1

  End With

End Sub

Sub MoveSingleChart(chtDest As Shape, chtSource As Shape, _ LeftAdd As Double, _ TopAdd As Double,_ WidthFactor As Double, _ HeightFactor As Double)

 

 chtDest.Top = chtSource.Top + TopAdd

 chtDest.Width = chtSource.Width * WidthFactor

 chtDest.Left = chtSource.Left + LeftAdd

 chtDest.Height = chtSource.Height * HeightFactor

End Sub

La première procédure appelle la deuxième procédure pour chaque graphique du tableau de bord. Il passe dans le graphique pour changer, le graphique pour changer par rapport à, combien ajouter aux propriétés Top ou Left et combien changer les propriétés Height ou Width par rapport à chtSource.

Fermez le VBE et revenez à Excel. Appuyez sur Ctrl + F8 pour afficher la boîte de dialogue Macros et exécutez la macro MoveCharts. Si vous n’êtes pas satisfait de la mise en page du graphique, redimensionnez simplement manuellement le graphique de la jauge de ratio actuel et réexécutez la macro afin que tous les autres graphiques s’ajustent par rapport à celui-ci. Le tableau de bord final est illustré à la figure 20.

Figure 20: Le tableau de bord des Données financières

S’abonner
Notifier de
0 Commentaires
Inline Feedbacks
Voir tous les commentaires

Initiation à Excel

Fonctions Excel

Excel VBA

Macros VBA Utiles

Plus d'outils

Sur Facebook

Sur YouTube

0
Nous aimerions avoir votre avis, veuillez laisser un commentaire.x