Contact: 514-605-7112 / info@lecfomasque.com

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)

.


NOTRE CATALOGUE DE FORMATIONS


Catalogue de formations

Le CFO masqué vous offre 13 formations, réparties dans 4 catégories: Tableaux de bord, Modélisation financière, Finance corporative et Analyse et modélisation de données. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

Pour info: 514-605-7112 ou info@lecfomasque.com

Téléchargez notre catalogue

Sophie Marchand, M.Sc., CPA, CGA, MVP

Instigatrice du CFO masqué, Sophie Marchand est détentrice d’une M.Sc. en finance corporative, d’un titre comptable CPA, CGA et d’un titre MVP (Most valuable professional) Excel de Microsoft, 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 consultante, formatrice et conférencière.

  • répondre Modélisation financière dans Exce... ,

    […] 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 date dynamiques dans vos modèles financiers. Le but est triple: Avoir la flexibilité changer le mois initial 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 de consolidation 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 dans la version d'Excel des utilisateurs de votre fichier Excel. Ces utilisateurs pourraient ne pas savoir comment installer le tout, ce qui renrait votre modèle financier inutilisable. Si le sujet d'aujourd'hui vous intéresse, je vous invite à vous inscrire à ma formation sur les Meilleures pratiques d'affaires en modélisation financière dans Excel (25 mars 2014) et si vous souhaitez notamment maîtriser davantage les fonctions de dates et d'heures, je vous invite à vous inscrire à ma formation Excel – Notions avancées (22 avril 2014).  […]

    Écrire un commentaire


    • *