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

Publié le 18 février 2014
par Sophie Marchand M.Sc., CPA, CGA, MVP
Dates modèles financiers

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

Il y a quelques temps, je publiais sur ce blogue l’article Excel: Date de lancement dynamique, qui  suggérait une approche pour changer la date de lancement d’un produit, tout en maintenant exact les calculs rattachés. Aujourd’hui, je vous présente plutôt une approche pour rendre vos entêtes de dates dynamiques dans vos modèles financiers. Le but est triple:

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

.

1 – Afficher les mois de façon dynamique

Dans le modèle ci-bas, vous pouvez inscrire le mois désiré dans la cellule H8 et toutes les entêtes de dates se mettront à jour en conséquence, en commençant par les mois suivants. Pour ce faire, nous avons utilisé la formule suivante:

=Date(Annee(H8);mois(H8)+2;0)  ou  =Date(Year(H8),Month(H8)+2,0)

À noter que nous n’avons pas utilisé la fonction Mois.decaler (Edate), à cause des limites de cette fonction, présentées dans le paragraphe d’introduction.

Décortiquons maintenant la formule ci-haut.

D’abord, la fonction date, recompose une date, à partir d’une année, d’un mois et d’une journée.

Le premier paramètre va chercher l’année de la date entrée en H8. Mais attention! À cause des deux paramètres suivants, si la date entrée en H8 est le 31 décembre 2012, alors l’année sera 2013. Il ne faut donc pas analyser ce paramètre de façon isolée, mais plutôt l’analyser en combinaison avec les deux paramètres suivants.

Le deuxième paramètre va chercher le mois de la date entrée en H8 et va ensuite y ajouter 2 mois. Pourquoi? La réponse réside en partie dans le troisième paramètre.

Le troisième paramètre va chercher le jour 0 d’un mois, c’est donc dire le dernier jour du mois précédent. Nous procédons ainsi car notre modèle est en fin de mois et que nous ne voulons pas déterminer quelle est la dernière journée de chacun des mois faisant partie du modèle.

Donc, avec cette combinaison de paramètres, en I8, tout comme dans les cellules comprenant les mois suivants, nous obtiendrons toujours la date correspondant à la fin du mois suivant le mois de la cellule précédente.

Sceptiques? Faites le test!.

Dates modèles financiers.

2 – Afficher les trimestres correspondants

Afin de renvoyer le trimestre correspondant à chacun des mois du modèle financier, nous avons utilisé la formule suivante:

= ‘ ‘Q’ ‘&Arrondi.sup(mois(I8)/3;0)  ou  =’ ‘Q’ ‘&Roundup(month(I8)/3,0)

Décortiquons maintenant cette formule.

D’abord, nous avons concatené un “Q” pour “Quarter” avec un calcul dynamique pour trouver le trimestre sous-jacent.

Nous trouvons le numéro de trimestre en divisant simplement le mois sous-jacent par 3 et en arrondissant le résultat à l’unité supérieure.

Autrement dit, lorsqu’on divise les mois 1, 2 et 3 par 3 et qu’on arrondit le résultat à l’unité supérieure, on obtient 1. Lorsqu’on divise les mois 4, 5, et 6 par 3 et qu’on arrondit le résultat à l’unité supérieure, on obtient 2. Et ainsi de suite..

Dates modèles financiers

.

3 – Afficher les années correspondantes

Pour afficher les années correspondantes, la formule est beaucoup plus simple. Il s’agit simplement d’utiliser la fonction Annee() ou Year() et Excel renverra la portion Année de la date sous-jacente..

Dates modèles financiers

 

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

1 – D’abord, ne manquez pas de lire la suite de cet article: Modélisation financière dans Excel: Entêtes de dates dynamiques (2 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_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

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

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

  2. Bonjour Sophie,
    La formule =EOMONTH(H8;1) donne le même résultat et me semble plus simple que =Date(Year(H8),Month(H8)+2,0).
    Cordialement.
    Jacques

  3. Sophie,
    Désolé j’ai peut être trop ‘survolé votre article’ : “l’ajout d’un add-in dans la version d’Excel des utilisateurs de votre fichier Excel”. Y a t il un add in dans Excel 2010 et 2013 pour les dates ? je ne l’ai pas trouvé dans ma version 2013…
    Cordialement.
    Jacques

Laisser un commentaire

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

Scroll to Top