Modélisation financière dans Excel : Date de lancement dynamique

Publié le 22 novembre 2013
par Sophie Marchand M.Sc.
Offset, Decaler et Match, Equiv

Il y a quelques jours, un client m’a demandé si je pouvais lui donner un coup de main avec un de ses modèles financiers. Il disait vouloir mettre en place un calcul de ventes cumulatives, en fonction d’une date de lancement d’un produit. J’ai jeté rapidement un coup d’oeil à son fichier et j’ai compris ce qui n’allait pas. Si vous souhaitez jouer aisément avec une date pour devancer ou repousser des flux monétaires dans le temps, vous devez d’abord songer à définir votre séquence de flux monétaires (sans égard au temps) et ensuite, à intégrer une formule dans votre modèle qui ira placer la séquence de flux monétaires à la bonne période. Il faut donc y aller par étapes. Cet article vous propose une approche pour créer une date de lancement dynamique dans un modèle financier Excel. Je suis d’avis que d’autres solutions existent également.

 

Problématique : Besoin d’une date de lancement dynamique

Vous trouverez la résolution de ce cas dans l’image plus bas. Vous verrez que j’ai proposé, aux lignes 16 et 17, deux solutions équivalentes. En fait, la solution à la ligne 17 est une fonction imbriquée dans une seule cellule alors que la solution à la ligne 16 nécessite l’implication de 3 formules séparées. Mais en bout de ligne, les deux formules sont équivalentes.

 

CLIQUEZ SUR L’IMAGE POUR L’AGRANDIR

Offset, Decaler et Match, Equiv

 

Date de lancement dynamique : Première étape

La première étape consiste à insérer un mécanisme pour choisir une date (voir la cellule C6 sur l’image plus bas). Pour cela, vous pouvez utiliser la fonctionnalité Validation de données (ou Data validation en anglais). Vous devez également prévoir un espace pour préciser la séquence de flux monétaires du projet (voir les cellues C8 à J8 sur l’image plus haut).

 

Date de lancement dynamique : Deuxième étape

Vous pouvez déjà calculer le cumulatif des flux monétaires du projet sur les 5 premières années, puisque la séquence de flux monétaires, elle, sera toujours précisée au même endroit dans votre fichier, soit dans les cellules C8 à J8. Vous pouvez donc calculer le cumulatif des flux monétaires des 5 premières années du projet et le maximum si vous le désirez également (voir les cellules C11 et C12 sur l’image plus haut).

 

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.

 

Date de lancement dynamique : Troisième étape

La troisième étape consiste à trouver une formule qui réagira efficacement au changement de date de lancement. La formule que je propose se trouve à la ligne 17 mais je l’ai également décomposée en trois parties en guise d’explications. D’abord, elle fait référence à la position du mois de la date de lancement dans la plage de dates de votre modèle financier. Pour cela, j’ai utilisé la fonction Equiv (ou Match en anglais). Vous pouvez voir à la cellule C16 que 2016 équivaut à la 4ième date dans la plage de dates de la ligne 15. Si vous changez 2016 pour 2018, vous obtiendrez 6 (et ainsi de suite). Ensuite, la formule fait référence à la position du mois de la cellule dans laquelle se trouve la formule dans la plage de dates de votre modèle financier. Pour cela, j’ai encore utilisé la fonction Equiv (ou Match en anglais). Le résultat se trouve à la ligne 14. Finalement, la formule détermine où placer les flux monétaires dans le modèle financier. Pour cela, j’ai utilisé la fonction SI (ou IF en anglais) et la fonction Decaler (ou Offset en anglais). Je demande d’abord à Excel d’inscrire 0$ si l’année en cours est inférieure à l’année de lancement. Sinon, je lui demande de se placer à la case C8 et de bouger vers la droite du nombre de cellules représentant l’écart entre mes deux positions de dates. Je vous laisse mijoter là-dessus!


 

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 :

CFO-Masque_Formations-en-ligne_FBLa 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é.

3 réflexions sur “Modélisation financière dans Excel : Date de lancement dynamique”

  1. Ping : Excel: Jouer aisément et de façon...

  2. Je l’aime, pourtant je l’aurais fait sans le compteur ni la cellule D6. A mon avis, c’est moi compliqué mais je comprends qu’il puisse exister d’autres objectifs pour ces cellules

    Voici mon solution

    1. Bonjour Jorge,

      Tel qu’indiqué dans l’article, il y a plusieurs façons de résoudre le tout et la cellule D6 n’est là que pour fins de démonstration de la formule. Je ne l’utilise pas dans ma formule finale.

      Au plaisir,

      Sophie

Laisser un commentaire

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

Retour en haut