Modélisation financière dans Excel : Entêtes de dates dynamiques (2 de 2)

Publié le 24 février 2014
par Sophie Marchand M.Sc.
Dates dynamiques

Le présent article fait suite à l’article Modélisation financière dans Excel: Entêtes de dates dynamiques (1 de 2). Il a pour but de partager quelques trucs afin de rendre les entêtes de dates de vos modèles financiers dynamiques. Rappelons maintenant les avantages d’avoir des entêtes de dates dynamiques dans un modèle financier:

 

  • Avoir la flexibilité de changer le mois de départ de votre modèle financier, afin que toutes les autres dimensions de dates s’ajustent automatiquement (mois suivants, trimestres et années) dans vos entêtes de colonnes.
  • Respecter les meilleures pratiques d’affaires qui demandent à ce qu’un modèle financier présente des données mensuelles et ensuite des calculs consolidés par trimestre et par année.
  • Éviter d’obtenir des messages d’erreurs comme #Nom? (#Name?), en utilisant des fonctions de dates comme Fin.Mois (EOMonth). En effet, ces fonctions de dates nécessitent l’ajout d’un add-in. Les utilisateurs de vos modèles financiers pourraient ne pas savoir comment installer cet add-in et ainsi, se trouver dans l’impossibilité d’utiliser votre modèle financier.

 

Article précédent

Dans le premier article Modélisation financière dans Excel: Entêtes de dates dynamiques (1 de 2), nous avons vu comment afficher les mois, les trimestres et les années de façon dynamique dans un modèle financier, de sorte qu’en changeant simplement la date du premier mois du modèle, toutes les entêtes de dates du modèle puissent se mettre à jour. Ci-bas, nous veillons plutôt à automatiser les entêtes de dates des sections de consolidation du modèle financier par trimestre et par année (afin de répondre aux meilleures pratiques d’affaires en modélisation financière). En effet, selon les meilleures pratiques d’affaires en modélisation financière, si votre modèle s’étend sur 3 ans, vous devriez présenter 36 mois de prévisions financières dans une section distincte, et ensuite seulement, présenter les 12 trimestres consolidés dans une section distincte et finalement, présenter les 3 années consolidées, dans une troisième section. Le tout a pour but de répondre à une autre meilleure pratique d’affaires qui recommande d’utiliser une seule et même formule pour une plage de données, une colonne de données ou une ligne de données avec des vocations similaires. Bien sûr, rien ne vous empêche ensuite de présenter un sommaire exécutif avec des totaux intercalés pour les trimestres et ou les années mais jamais, dans votre modèle financier comme tel, vous ne devriez intercaler de tels totaux. Le risque d’erreurs s’en verrait autrement décupler.

 

1 – Automatiser les trimestres des prévisions financières par trimestre

Dans une première section du modèle financier, nous avons présenté les prévisions financières mensuelles et nous avons ajouté des formules afin de rendre les entêtes de dates dynamiques. Maintenant, dans la section du modèle financier qui présente les prévisions financières par trimestre, nous pouvons utiliser la formule suivante afin de voir apparaître le bon trimestre dans la première entête de colonnes par trimestre.

=’ ‘Q’ ‘&arrondi.sup(mois(H8)/3;0)  ou  =’ ‘Q’ ‘&roundup(month(H8)/3,0)

Que fait cette formule?

Elle s’assure de concatener le ‘ ‘Q’ ‘ de Quarter (Trimestre) avec le bon numéro de trimestre. Ce numéro de trimestre est obtenu grâce à la fonction arrondi.sup (roundup) qui arrondi à l’unité supérieure le résultat de la division du mois de la date de départ (date inscrite en H8) divisée par 3. Cette fonction est la même que celle utilisée au point 2 dans notre premier article sur le sujet. N’hésitez pas à vous y référer pour plus de détails.

Dates modèles financiers

 

L’équation ci-haut vaut pour le premier trimestre à consolider. Pour les autres trimestres, nous pourrons plutôt utiliser la formule suivante:

=Si(Droite(BR7;1)<“4″,”Q”&(Droite(BR7;1)+1);”Q1”)  ou
=IF(RIGHT(BR7,1)<“4″,”Q”&(RIGHT(BR7,1)+1),”Q1”)

Que fait cette formule?

Elle demande à Excel de procéder comme suit:

Si le numéro de trimestre du trimestre précédent est plus petit que 4;

Il faut concatener Q avec le numéro qui correspond au numéro de trimestre précédent +1;

Sinon (ça veut dire que le trimestre précédent est Q4):

Il faut mettre Q1.

Dates dynamiques

..

2 – Afficher les années correspondantes aux trimestres

Ensuite, nous devons inscrire les années correspondantes aux trimestres ainsi trouvés. Pour ce faire, nous utilisons la formule suivante:

=SI(BQ6=0;H6;SI(DROIT(BQ7,1)=”4″;BQ6+1;BQ6))  ou  =IF(BQ6=0,H6,IF(RIGHT(BQ7,1)=”4″,BQ6+1,BQ6))

Que fait cette formule?

Elle demande à Excel de procéder comme suit:

Si l’année précédente est 0 (cela veut dire qu’il s’agit du premier trimestre à consolider), inscrire l’année de départ du modèle financier;

Sinon:

Si le numéro du trimestre précédent (premier caractère à droite de l’expression du trimestre précédent) = 4, ajouter 1 à l’année précédente;

Sinon (donc si le trimestre précédent est 1, 2 ou 3):

Mettre la même année que l’année du trimestre précédent.

Dates modèles financiers

 

3 – Afficher les années dans la section des prévisions financières par année

Afin d’afficher automatiquement les bonnes années dans la section des prévisions financières annuelles, une formule simple peut être utilisée:

=Si(CM6=0;H6;CM6+1)  ou  =IF(CM6=0,H6,CM6+1)

Que fait cette formule?

Si l’année précédente est 0 (donc première année à consolider), aller chercher l’année de la date d’ouverture du modèle;

Sinon:

Prendre l’année précédente +1

Dates modèles financiers

 

Vous souhaiteriez obtenir plus de trucs comme celui-là?

1 – D’abord, il est recommandé de lire l’article précédent Modélisation financière dans Excel: Entêtes de dates dynamiques (1 de 2).

2 – Ensuite, si vous souhaitez améliorer vos compétences en modélisation financière, je vous invite à vous inscrire à ma formation Excel – Modélisation financière (niveau 1) et si vous souhaitez apprendre plus de trucs sur le type de formules dont il est question dans cet article, je vous invite à vous inscrire à ma formation Excel – Analyse et modélisation de données (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é.

1 réflexion sur “Modélisation financière dans Excel : Entêtes de dates dynamiques (2 de 2)”

  1. Ping : Modélisation financière dans Exce...

Laisser un commentaire

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

Retour en haut