Pour avoir travaillé avec de nombreux CFOs, je suis à même de constater qu’il existe dans le milieu, 2 définitions de ce que sont les “rolling forecast” ou “prévisions en continu”. À mon avis, la définition la plus juste mais aussi la plus difficile à mettre en place, est celle qui vise à effectuer des prévisions sur une période de temps constante. À titre d’exemple, l’entreprise met en place une solution qui lui génère en tout temps les prévisions des 12 prochains mois, basées sur son historique et sur certaines règles d’affaires et pouvant être ajustées au besoin. Toutefois, ce que l’on retrouve le plus souvent comme pratique, c’est une fusion des données actuelles et des données du budget. Par exemple, en supposant que 5 mois ce sont écoulés dans l’année, le “rolling forecast” présente les actuels pour les 5 premiers mois de l’année et utilise les données du budget pour les 7 derniers mois de l’année. Ainsi, les prévisions annuelles sont ajustées à chaque mois, au fur et à mesure qu’un mois se termine et que l’on connaît la valeur des données actuelles. Cet article vise à montrer comment créer un “rolling forecast” dans Excel selon la deuxième définition.
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. |
|---|
Organiser le fichier Excel pour le “rolling forecast”
Pendant une grande partie de ma carrière, j’ai audité des modèles financiers en entreprise et j’en ai vu de toutes les couleurs en ce qui concerne l’élaboration de “rolling forecast”. Pourtant, l’approche à prioriser est excessivement simple à mettre en place et performante. Si vous êtes ce ceux qui se compliquent la vie inutilement, voici donc la recette!
Il s’agit d’abord d’organiser votre fichier Excel de façon à ce que vous ayez un onglet Budget, un onglet Actuels et un onglet Rolling Forecast construits exactement de la même façon. Chaque donnée doit débuter à la même colonne, même ligne de chaque onglet.
L’onglet Actuels sera alimenté par vos données actuelles, au fur et à mesure que les mois vont passer. Comment? C’est à vous de voir… L’idéal serait d’utiliser Power Query et d’automatiser le tout. Vous pourriez également songer à utiliser le VBA ou un add-in Excel qui irait lire directement dans votre système source… Les possibilités sont nombreuses mais au final, cet onglet doit être alimenté des données actuelles au fil du temps.
L’onglet comprenant le Rolling Forecast doit être structuré de la même façon mais vous devrez utiliser une fonction pour permettre d’aller chercher les x mois d’actuels et de compléter la suite avec les données du budget.
Pour obtenir le dernier mois d’actuels, vous pourriez utiliser la fonction ci-dessous: EQUIV(0,C4:N4,0)-1.
Pour construire votre Rolling Forecast, vous pourriez utiliser la fonction ci-dessous: SI(EQUIV(C3,$C$3:$N$3,0)<=Actuels!$D$1,Actuels!C4,Budget!C4).
Et voilà! Pas plus compliqué.
Fichier d’accompagnement VIP à télécharger
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
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é.
Bonjour Sophie et bravo pour votre titre de MVP
Il y aura un problème avec le EQUIV(0,C4:N4,0)-1 si vous n’avez pas de vente durant un mois (par exemple si le magasin est fermé pour travaux) et au mois de décembre (pas de valeur 0 donc N/A)
je propose de rajouter en C2:N2 le numéro du mois : 1 à 12 et ensuite faire un MAX.SI(C2:N2;C4:N4;”>0″)
Cordialement
Stéphane
Je ne connais pas de fonction MAX.SI sous Excel (à part des combinaisons matricielle max(si(….)), et il y a des caractères bizarres dans votre formule… Mais il y a une solution toute simple qui marche si l’on renseigne les Actuels mois après mois en laissant vides les cellules non actuelles. Dans ce cas, on recherche simplement la référence de la dernière colonne de cellule non vide : =EQUIV(9^9;C4:N4) et là, pas de soucis si on a des mois à zéro.
Ouais… c’est quand même assez rare en entreprise qu’il n’y ait rien du tout qui se passe pendant un mois… il y aura toujours minimalement des frais fixes, même si un magasin est fermé… donc si on utilise la ligne de bénéfice net, on devrait être OK 😉 …
Merci Sauphie,
J’avais une autre façon d’y parvenir mais celle-ci est beaucoup plus simple! Merci beaucoup et félicitations pour le titre MVP encore cette annéee!
Bonjour Sophie,
J’ai dû louper un truc. Dans la mesure où EQUIV recherche le premier 0, pourquoi ne pas avoir faire =si(Actuels!C4>0;Actuels!C4;Budget!C4) dans la feuille Rolling Forecast ?
Par ailleurs, je me pose la question de la mise à jour de l’onglet “Actuels”. Si je fais la mise à jour via une requête, ce qui est plutôt bien, la valeur prise en compte peut être que partielle en cours de mois. Dans ce cas il faudrait à mon sens rester sur le budget et ne prendre en compte “Actuels” que lorsque le mois est terminé, non ?
Bravo pour le MVP !
Laurent
Bonjour Laurent,
Le Equiv est pour faire ressortir le mois actuel dans la cellule du haut.
Une autre approche est de mettre un menu déroulant et de laisser l’usager choisir le mois d’actuel.
Toutefois ici, on souhaitait une approche automatisée.
Pour ce qui est des requêtes, c’est sûr que la mise à jour de la requête doit être synchronisée avec la mise à jour réelle des données sous-jacentes.
Au plaisir,
Sophie
Cela revient donc simplement a remplir les mois futurs avec ce qui était prévu dans le budget sans prendre en compte les résultats des mois précédents ? Je ne vois pas comment une entreprise pourrait avoir besoin de faire cela.
Par contre, en remplacant les chiffres des mois futurs, non par la suite du budget, mais par une fonction forecast, cela pourrait être intéressant.
Bonjour Vincent,
Je vous dirais qu’une entreprise sur 2 au Québec a recours à ce type de prévision. Il faut comprendre que le budget est élaboré à partir d’un modèle financier qui permet de générer des prévisions basées sur un ensemble de règles d’affaires. Les prévisions au niveau budgétaire ne concernent pas seulement les ventes mais l’ensemble des postes des états financiers. La fonction forecast est simplement une projection dans le futur de comportements observés dans le passé (c’est basé sur des lois statistiques). Elle pourrait être utilisée pour prévoir des ventes dans une entreprise très très stable mais habituellement, dans un modèle financier, on va tenir compte d’un paquet d’éléments autres que la tendance passée. Par exemple, peut-être que l’entreprise aura fait des acquisitions d’autres entreprises dans les derniers mois et devra inclure les chiffres de ventes de ces entreprises dans ses prévisions, peut-être qu’elle aura modernisé ses équipements, fermé des succursales, embaucher davantage de vendeurs ou fait des mises à pieds importantes, peut-être qu’une loi prévue dans les prochains mois viendra en vigueur et affectera le niveau des ventes, etc., etc., etc. Pour effectuer des prévisions budgétaires, on ne peut pas simplement se fier aux modèles statistiques. Il faut également considérer un paquet d’autres facteurs.
Au plaisir,
Sophie
Bonjour Sophie,
Au final, le rolling forecast=budget ?
Bonjour,
Tel que mentionné au début de l’article, ce serait plutôt une fusion des données actuelles et des données du budget.