Modélisation financière dans Excel: Entêtes de dates dynamiques (1 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

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

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 (1 de 2)
1 Pings/Trackbacks pour "Modélisation financière dans Excel: Entêtes de dates dynamiques (1 de 2)"
  1. […] 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).  […]

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 – Modélisation financière (niveau 1)

    septembre 30 @ 9 h 00 min - 16 h 00 min
  2. Finance corporative – Analyse de projets d’investissement

    octobre 7 @ 9 h 00 min - 16 h 00 min