Graphiques dynamiques dans la modélisation financière


Posted on 2017-09-11 11:56:49 category:Logiciel


Lorsque vous créez des graphiques dans des modèles financiers ou des rapports, vous devez toujours suivre les meilleures pratiques et essayer de rendre vos modèles aussi flexibles et dynamiques que possible.Vous devez toujours associer autant que possible vos modèles, et cela vaut également pour les graphiques.Il est logique que lorsque vous modifiez l'une des entrées de votre modèle, cela se reflète dans les données du graphique, ainsi que dans les titres et les étiquettes.

Création du graphique sur des données basées sur des formules

Télécharger le fichier 0901.xlsx.Ouvrez-le et sélectionnez l'onglet étiqueté 9-23 pour l'essayer par vous-même.

change drop down box
Modification de la liste déroulanteboîte vers le bas.

Si vous masquez des données dans votre feuille source, elles n'apparaîtront pas sur le graphique.Testez cela en masquant l'une des colonnes de la feuille Financials et vérifiez que le mois a disparu sur le graphique.Vous pouvez modifier les options sous Sélectionner la source de données afin d'afficher les cellules masquées.

Lier les titres des graphiques aux formules

Étant donné que toutes les données sont liées à la liste déroulante, vous pouvez facilement créer un titre dynamique dans le graphique en créant une formule pour le titre, puis en liant ce titre au graphique.Suivez ces étapes:

  1. Dans la cellule A1 de ce modèle, changez le titre comme suit:= "Coûts de prévision stratégique sur cinq ans pour le centre d'appels -"F1.

    L'esperluette () sert de connecteur qui enchaînera le texte et les valeurs des formules.

    Au lieu de l'esperluette, vous pouvez également utiliser la fonction CONCATENER, qui fonctionne de manière très similaire en joignant des cellules singulières ensemble, ou la fonction TEXTJOIN est un nouvel ajout à Excel 2016, qui se joindra ensemblegrandes quantités de données.

    Lorsque la formule de la cellule A1 fonctionne, vous devez lier le titre du graphique à la cellule A1.

  2. Cliquez sur le titre du graphique.

    Cette partie peut être délicate.Assurez-vous que vous n'avez sélectionné que le titre du graphique.

  3. Cliquez sur la barre de formule.
  4. Tapez= puis cliquez sur la cellule A1.
  5. Appuyez sur Entrée.

    Le titre du graphique change pour afficher le contenu de la cellule A1.

lier les titres des graphiques aux formules
Lier le graphiqueles titres des formules.

Vous ne pouvez insérer aucune formule dans un graphique.Vous ne pouvez y lier qu'une seule cellule.Tous les calculs doivent être effectués dans une cellule, puis liés au titre comme indiqué.

Création de texte dynamique

Jetez un œil au rapport mensuel sur le budget.Nous avons déjà créé des formules dans les colonnes F et G, qui seront automatiquement mises à jour au fur et à mesure que les données changent, et afficheront comment nous allons par rapport au budget.

clustered column chart
Création d'un histogramme groupé.

Vous allez maintenant créer un graphique basé sur ces données, et chaque fois que les chiffres changent, vous aimerez être en mesure de voir combien d'éléments de campagne dépassent le budget.Suivez ces étapes:

  1. Mettez en surbrillance les données indiquant les valeurs du compte, du réel et du budget dans les colonnes B, C et D, respectivement.
  2. Sélectionnez la première option Colonne 2D dans la section Graphiques de l'onglet Insertion du ruban pour créer un histogramme groupé.
  3. Dans la cellule A1, créez un en-tête avec une date dynamique.
  4. Liez le titre du graphique à la formule de la cellule A1.
  5. Modifiez le graphique afin que les titres soient alignés horizontalement et modifiez les couleurs.

    Ce graphique sera bien meilleur s'il est trié de manière à ce que les barres les plus grandes soient sur le côté gauche.

  6. Mettez en surbrillance toutes les données, y compris les en-têtes, puis cliquez sur le bouton Trier (dans la section Filtre de tri de l'onglet Données du ruban).

    La boîte de dialogue Trier s'affiche.

  7. Trier par réel du plus grand au plus petit.
    trier les données du graphique
    Tri des données du graphique.

    Il est très facile de gâcher les formules lors du tri. Assurez-vous donc de mettre en évidence toutes les colonnes des colonnes A à G avant d'appliquer le tri.

    Maintenant, ajoutez des commentaires textuels au graphique.Vous pouvez le faire en ajoutant un commentaire dans une seule cellule, qui est liée dynamiquement aux valeurs du modèle et en liant la cellule à une zone de texte pour afficher le commentaire sur le graphique.

  8. Dans la cellule A15, créez une formule qui calculera automatiquement le nombre d'éléments de campagne dépassant le budget.

    Vous pouvez le faire avec la formule= COUNTA (G3: G12) -COUNT (G3: G12), qui calcule le nombre de cellules non vides dans la colonne G.

  9. Vous pouvez constater que deux éléments de campagne dépassent le budget, alors convertissez-le en texte dynamique avec la formule= COUNTA (G3: G12) -COUNT (G3: G12) "Éléments en dépassement du budget".
    Dynamic chart
    Graphique terminé avec zone de texte dynamique.
  10. Insérez une zone de texte dans le graphique en appuyant sur le bouton Zone de texte dans le groupe Texte de l'onglet Insertion dans le ruban.
  11. Cliquez une fois sur le graphique.

    La zone de texte apparaît.

  12. Sélectionnez soigneusement l'extérieur de la zone de texte avec la souris, comme vous l'avez fait dans la dernière section lors de la liaison des titres des graphiques.
  13. Maintenant, allez dans la barre de formule et tapez=.
  14. Cliquez sur la cellule A15 et cliquez sur Entrée.
  15. Redimensionnez et repositionnez la zone de texte si nécessaire.
  16. Testez le modèle en modifiant les chiffres afin que davantage d'articles dépassent le budget et assurez-vous que le commentaire dans la zone de texte change.

82259