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

À propos de l’auteure

Sophie Marchand, M.Sc., CPA, CGA

Auteure du blogue Mon Cher Watson et du site le CFO masqué, Sophie Marchand est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA et cumule plus de 12 années d’expérience dans le milieu des affaires. Elle se spécialise particulièrement en modélisation financière et en intelligence d’affaires. À ce titre, elle développe des modèles financiers rigoureux, des tableaux de bord sophistiqués et des outils de gestion performants. Elle offre ses services en tant que consultanteformatrice et conférencière.

smarchand@lecfomasque.com
514-605-7112

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

Si le sujet d’aujourd’hui vous intéresse, je vous invite à vous inscrire à ma formation sur les Excel – Modélisation financière (niveau 1) et si vous souhaitez notamment maîtriser davantage les fonctions de dates et d’heures, je vous invite à vous inscrire à ma formation Excel – Analyse et modélisation de données (niveau 1).

.

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)

Formations ExcelCalendrier de formations

Pour en savoir davantage sur nos formations ou pour vous inscrire, vous pouvez consulter notre calendrier de formation.

Sophie Marchand, M.Sc., CPA, CGA, MVP
514-605-7112
Experte en modélisation financière et intelligence d’affaires

 

Vous avez des questions?

Posez toutes vos questions sur le forum du CFO masqué.

Tagués avec : , , , ,
Publié dans Excel, Modèles financiers, Tous les articles
Un commentaire pour “Modélisation financière dans Excel: Entêtes de dates dynamiques (2 de 2)
1 Pings/Trackbacks pour "Modélisation financière dans Excel: Entêtes de dates dynamiques (2 de 2)"
  1. […] L'article qui suit 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:  […]

Laisser un commentaire

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

*


*

Vous pouvez utiliser ces balises et attributs HTML : <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Recevoir les articles par courriel
Abonnement Le CFO masqué
Gagnez une formation en ligne
Concours
Tableau de bord gratuit à l’inscription
Tableau de bord gratuit à l’inscription
Présentation à télécharger
Outils indispensables aux CFOs
Boutique en ligne
Microsoft Most Valuable Professional
Formations Excel
Formations Excel
Activités à venir
  1. Excel – Mise à niveau

    septembre 16 @ 9 h 00 min - 16 h 00 min
  2. Excel – Tableaux de bord (niveau 1)

    septembre 23 @ 9 h 00 min - 16 h 00 min