Graphiques dynamiques dans la modélisation financière - mannequins

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 devriez toujours lier autant que possible dans 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 soit reflété dans les données du graphique, ainsi que dans les titres et les libellés.

Construction du graphique sur les données basées sur une formule

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

Modifier la liste déroulante.

Si vous masquez des données dans votre feuille source, elles n'apparaîtront pas sur le graphique. Testez-le en masquant l'une des colonnes de la feuille financière 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 qu'elle affiche les cellules masquées.

Lier les titres des graphiques aux formules

Comme 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 reliant ce titre à la carte. Procédez comme suit:

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

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

   Au lieu de l'esperluette, vous pouvez aussi utiliser la fonction CONCATENATE, qui fonctionne de manière très similaire en assemblant des cellules singulières, ou la fonction TEXTJOIN est une nouvelle addition à Excel 2016, qui va réunir de grandes 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 difficile. Assurez-vous de n'avoir 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 indiquer ce qui se trouve dans la cellule A1.

Lier les titres des graphiques aux formules.

Vous ne pouvez pas insérer de formules dans un graphique. Vous ne pouvez lier qu'une seule cellule à celle-ci. Tous les calculs doivent être effectués dans une cellule, puis liés au titre comme indiqué.

Création de texte dynamique

Consultez le rapport budgétaire mensuel. Nous avons déjà construit 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.

Création d'un histogramme en cluster.

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.Procédez comme suit:

1. Mettez en surbrillance les données indiquant les valeurs de compte, réelles et budgétaires 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 graphique à histogrammes en cluster.
3. Dans la cellule A1, créez un en-tête avec une date dynamique.
4. Liez le titre du graphique à la formule dans la cellule A1.
5. Modifiez le graphique afin que les titres soient alignés horizontalement et modifiez les couleurs.

   Ce graphique sera beaucoup mieux s'il est trié de sorte que les plus grandes barres soient sur le côté gauche.

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

   La boîte de dialogue Trier apparaît.

7. Trier par Réel du plus grand au plus petit.

   

   Tri des données du graphique.

   Il est très facile de rattraper les formules lors du tri, alors assurez-vous de mettre en surbrillance toutes les colonnes des colonnes A à G avant d'appliquer le tri.

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

8. Dans la cellule A15, créez une formule qui calcule 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 voir que deux éléments de ligne sont terminés budget, convertissez-le en texte dynamique avec la formule = COUNTA (G3: G12) -COUNT (G3: G12) & "Items over Budget". "

   

   Tableau complété 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 Insérer du 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 de graphique.
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 changeant les nombres afin que plus d'éléments dépassent le budget, et assurez-vous que le commentaire dans la zone de texte change.