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
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).
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.
Ping : Excel: Jouer aisément et de façon...
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
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