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 Excel, plus spécifiquement 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.

1 – Afficher les mois dans nos entêtes de dates dynamiques dans Excel

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!

.

Vous aimeriez créer des états financiers prévisionnels complets et dynamiques dans Excel, capables de se moduler à vos hypothèses, afin d’éclairer vos décisions d’affaires ? Apprenez les meilleures pratiques avec nos formations en modélisation financière.

2 – Afficher les trimestres correspondants dans nos entêtes de dates dynamiques dans Excel

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.

3 – Afficher les années correspondantes dans nos entêtes de dates dynamiques dans Excel

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.

Formation complémentaire

Pour une introduction aux principes de base et aux meilleures pratiques d’affaires en modélisation financière dans Excel et vous familiariser avec les fonctions et les fonctionnalités qui permettent de faire des prévisions financières, des analyses de sensibilité et des analyses de scénarios, suivez notre formation Excel – Modélisation financière niveau 1.

Voici quelques commentaires d’apprenants ayant suivi la formation Excel – Modélisation financière niveau 1 :

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