Excel : 7 fonctions pour un forecast qui se met à jour seul

Un forecast financier qui applique la même formule à toutes les lignes d'un compte de résultat produit des chiffres propres, mais rarement crédibles. Les ventes suivent un taux de croissance, les achats dépendent du chiffre d'affaires, certaines charges sont figées au budget, et un mois exceptionnel peut fausser toute une tendance. Cet article détaille sept fonctions Excel — de FORECAST.LINEAR à LET — pour construire une prévision poste par poste, qui se recalcule automatiquement dès qu'un nouveau mois d'actuels arrive. Il s'adresse aux contrôleurs de gestion et aux administrateurs de modèles financiers qui travaillent sous Excel pour Microsoft 365.

Pourquoi une seule formule de prévision ne suffit jamais

Dans un compte de résultat réel, chaque ligne obéit à une logique différente :

• les ventes en ligne croissent selon un taux mensuel connu ;
• les ventes de gros suivent une tendance linéaire historique ;
• les achats de produits et le fret entrant varient en proportion du chiffre d'affaires ou des achats ;
• les salaires et les loyers sont figés par le budget ;
• certains postes comme les abonnements logiciels ou l'assurance ont besoin d'un lissage pour ne pas être déformés par un pic ponctuel.

Appliquer une extrapolation linéaire uniforme à toutes ces lignes revient à ignorer ce que l'on sait déjà de l'activité. La bonne approche consiste à choisir la méthode de calcul compte par compte, tout en gardant des formules qui se rafraîchissent automatiquement à mesure que les actuels remplacent les prévisions.

Préparer un classeur de forecast maintenable

Avant de saisir la première formule, il vaut la peine de structurer le classeur pour que le travail reste lisible et auditable :

• ajoutez trois colonnes de travail : Méthode, Taux/Ratio et Notes, qui documentent la logique appliquée à chaque ligne ;
• regroupez ces colonnes via l'onglet Données > Grouper, pour pouvoir les masquer facilement — la direction n'a pas besoin de voir les calculs intermédiaires ;
• conservez une ligne de statut (Actual / Forecast) sur toute la largeur du tableau : c'est elle qui pilotera dynamiquement les filtres utilisés dans les formules.

Cette organisation permet à la quasi-totalité des formules qui suivent de continuer à fonctionner sans modification lorsque juillet passe du statut « Forecast » à « Actual ».

Modéliser la croissance et la tendance avec FORECAST.LINEAR

Croissance composée mensuelle

Pour un compte qui croît d'un pourcentage fixe chaque mois — typiquement les ventes en ligne à +3 % mensuel — une simple multiplication suffit :

1=$G4*(1+$L$4)

Où $G4 correspond aux ventes réelles du mois précédent et $L$4 au taux de croissance saisi dans la colonne de travail, verrouillé pour pouvoir copier la formule vers la droite sans qu'il ne se décale.

Tendance linéaire ajustée aux seuls mois réels

Pour un compte qui suit sa trajectoire historique — ventes de gros, revenus de services et installation — FORECAST.LINEAR combiné à FILTER permet de ne prendre en compte que les mois marqués « Actual », sans jamais réintégrer une prévision dans le calcul de la tendance :

1=FORECAST.LINEAR(N$4,
2    FILTER($B10:$M10,$B$3:$M$3="Actual"),
3    FILTER($B$4:$M$4,$B$3:$M$3="Actual"))

Le premier FILTER renvoie les valeurs réelles du compte (les Y connus), le second renvoie les numéros de mois correspondants (les X connus). Comme les deux FILTER référencent la même ligne de statut, dès qu'un mois bascule d'« Actual » à autre chose (ou l'inverse), les deux tableaux se recalculent en cohérence — la formule n'a jamais besoin d'être retouchée.

Bâtir des prévisions par ratio : ventes, achats et marge

Certains postes ne suivent ni une croissance fixe ni une tendance propre, mais un ratio historique à un autre poste. Les frais de port, par exemple, sont calculés comme un pourcentage du total des ventes réelles :

1=SUM(FILTER($B9:$M9,$B$3:$M$3="Actual"))
2 /SUM(FILTER($B10:$M10+$B11:$M11,$B$3:$M$3="Actual"))

Ce ratio historique, une fois calculé et mis en pourcentage, s'applique ensuite aux ventes prévues pour obtenir le montant projeté :

1=($H10+$H11)*$M$9

Le même principe s'applique au fret entrant et droits de douane, calculé cette fois en ratio aux achats plutôt qu'aux ventes. Des variantes plus fines existent selon le compte : les frais de commerçant intègrent aussi le revenu de services dans la base de calcul, tandis que le marketing ne se rapporte qu'aux ventes en ligne, puisque l'entreprise ne fait de la publicité que pour sa boutique en ligne. Dans les deux cas, il suffit de dupliquer une formule existante et d'ajuster le périmètre des ventes incluses dans le FILTER.

Reprendre le budget avec XLOOKUP verrouillé

Certains postes — salaires, cotisations de retraite, loyers, dépréciation — ne se prévoient pas : ils sont repris tels quels du budget. XLOOKUP fait l'affaire, à condition de bien gérer les références mixtes pour que la formule se comporte correctement à la fois quand on la copie vers le bas (changement de compte) et vers la droite (changement de mois) :

1=XLOOKUP($A4,$A$2:$A$50,G$2:G$50)

• $A4 référence le numéro de compte de la ligne courante : la colonne est verrouillée, la ligne reste relative pour s'adapter en copiant vers le bas ;
• $A$2:$A$50 référence la liste des comptes budgétés : totalement verrouillée, elle ne bouge jamais ;
• G$2:G$50 référence la colonne du mois de juillet dans le budget : seule la ligne est verrouillée, ce qui permet à la lettre de colonne de glisser automatiquement vers août, septembre, etc. quand on tire la formule vers la droite.

Si votre plage source est convertie en tableau Excel structuré (Ctrl+T), une référence structurée verrouillée sur la colonne compte permet d'obtenir le même comportement avec une formule plus lisible pour un tiers qui audite le classeur.

Lisser les comptes volatils avec LET, TAKE et l'exclusion des valeurs aberrantes

Moyenne mobile sur trois mois

Pour un compte comme les abonnements logiciels, dont certains contrats démarrent et d'autres s'arrêtent, une moyenne glissante sur les trois derniers mois réels lisse le bruit sans figer un montant obsolète. LET rend la formule lisible en nommant chaque étape intermédiaire, et TAKE isole les trois dernières colonnes du tableau filtré :

1=LET(
2    valeursReelles, FILTER($B12:$M12,$B$3:$M$3="Actual"),
3    AVERAGE(TAKE(valeursReelles,,-3))
4)

À chaque nouvelle clôture mensuelle, FILTER récupère un mois réel supplémentaire et TAKE glisse automatiquement la fenêtre de calcul sur les trois mois les plus récents.

Exclure une valeur aberrante ponctuelle

Un compte comme l'assurance peut présenter un pic isolé (une prime annuelle payée en janvier, par exemple) qui fausserait une moyenne simple. En multipliant deux tests logiques à l'intérieur de FILTER, on exclut le mois problématique sans toucher au reste de la série :

1=AVERAGE(FILTER($B14:$M14,($B$3:$M$3="Actual")*($B$4:$M$4<>1)))

Le même schéma s'applique aux voyages et frais de représentation (exclusion du mois 5) et aux réparations et entretien (exclusion du mois 6) : seule la valeur exclue change dans le second test logique.

Pour un poste à cycle trimestriel comme les honoraires professionnels, la logique est différente : on reprend simplement le montant du même mois du trimestre précédent, en tirant la formule sur trois colonnes à la fois pour reproduire le motif de pic observé chaque trimestre.

Fiabiliser et simplifier les formules avec les noms définis

Presque toutes les formules ci-dessus répètent la même vérification : $B$3:$M$3="Actual". Plutôt que de la ressaisir à chaque ligne, définissez-la une fois comme nom via l'onglet Formules > Gestionnaire de noms :

1Status = ($Feuil1!$B$3:$M$3="Actual")

Une fois ce nom créé, chaque formule peut remplacer le test logique complet par Status, ce qui raccourcit considérablement la saisie et réduit le risque de faute de frappe sur une plage aussi utilisée. Un Rechercher/Remplacer ciblé sur la chaîne exacte permet de propager le changement à l'ensemble des formules existantes en une seule opération.

Mise en œuvre : un moteur de sélection avec LET et SWITCH

Une fois les méthodes validées compte par compte, il est possible d'aller plus loin en centralisant la logique dans une formule unique qui lit la colonne Méthode et applique automatiquement le bon calcul. Cela évite d'avoir neuf familles de formules différentes à maintenir dans la feuille.

Prérequis : Excel pour Microsoft 365 (fonctions matricielles dynamiques FILTER, LET, TAKE et SWITCH natif). Accès minimal requis : droit d'écriture sur le classeur uniquement — protégez la feuille de calcul (Révision > Protéger la feuille) une fois le modèle finalisé pour éviter une modification accidentelle des formules par un utilisateur final.

1=LET(
2    methode, $L4,
3    tauxRatio, $M$4,
4    moisCourant, N$4,
5    statutActuel, Status,
6    ventesLigne, FILTER($B10:$M10,statutActuel),
7    ventesGros, FILTER($B11:$M11,statutActuel),
8    moisReels, FILTER($B$4:$M$4,statutActuel),
9    SWITCH(methode,
10        \"Croissance\", $G4*(1+tauxRatio),
11        \"Lineaire\", FORECAST.LINEAR(moisCourant,ventesLigne,moisReels),
12        \"RatioVentes\", (ventesLigne+ventesGros)*tauxRatio,
13        \"Budget\", XLOOKUP($A4,$A$2:$A$50,G$2:G$50),
14        \"Moyenne3M\", AVERAGE(TAKE(ventesLigne,,-3)),
15        \"MoyenneSansAberrant\", AVERAGE(FILTER($B10:$M10,statutActuel*(moisReels<>1))),
16        \"Méthode inconnue\"
17    )
18)

Vérification : après avoir déployé la formule sur l'ensemble des lignes, comparez ses résultats à ceux des formules individuelles construites plus haut avec l'outil d'audit (Ctrl+\``pour basculer l'affichage des formules, ou Formules > Afficher les formules). Les deux approches doivent produire des valeurs identiques ; en cas d'écart, le problème vient presque toujours d'un intitulé de méthode mal orthographié dans la colonne **Méthode**, ce que SWITCH` remonte alors comme « Méthode inconnue » plutôt qu'un simple mauvais chiffre silencieux.

Dépannage : erreurs fréquentes lors de la construction du forecast

• #CALC! avec FILTER : aucun élément ne correspond au critère, généralement parce que la cellule de statut contient un espace superflu ou une casse différente de \"Actual\". Ajoutez un troisième argument if_empty à FILTER pour éviter que l'erreur ne se propage.
• #VALUE! avec FORECAST.LINEAR : les tableaux known_ys et known_xs renvoyés par les deux FILTER n'ont pas la même taille, souvent parce que les deux plages sources ne couvrent pas exactement les mêmes colonnes. Vérifiez que la ligne de valeurs et la ligne de mois ont la même largeur.
• #N/A avec XLOOKUP : le numéro de compte de la ligne courante n'existe pas dans la table budget (compte renommé ou ajouté après coup). Ajoutez l'argument if_not_found de XLOOKUP plutôt que de laisser l'erreur remonter dans le total.
• #NAME? après création d'un nom défini : le nom existe déjà avec une portée différente (feuille contre classeur). Vérifiez le champ Scope dans le Gestionnaire de noms.
• Résultats incohérents après Rechercher/Remplacer : une formule utilisait la chaîne recherchée dans un contexte différent. Testez toujours sur une copie du classeur avant un remplacement massif.

Pour aller plus loin : FORECAST.ETS et la feuille de prévision automatique

Si votre historique couvre plusieurs années avec un motif saisonnier récurrent, Excel propose une approche complémentaire : l'outil Feuille de prévision (Forecast Sheet) et la fonction FORECAST.ETS, qui génèrent automatiquement une projection avec intervalles de confiance et statistiques de qualité d'ajustement. Cette approche convient mieux aux séries longues et saisonnières qu'aux comptes pilotés par des règles métier explicites comme ceux traités ici.

Ce qu'il faut retenir

• Il n'existe pas de formule de prévision universelle : chaque compte mérite une méthode alignée sur son comportement réel (croissance, tendance, ratio, budget ou moyenne lissée).
• FILTER couplé à une ligne de statut Actual/Forecast est la clé pour que toutes les formules se recalculent automatiquement sans intervention manuelle à chaque clôture.
• LET et TAKE rendent lisibles les calculs de moyenne mobile qui seraient illisibles en formule imbriquée classique.
• Nommer les tests logiques répétitifs via le Gestionnaire de noms accélère la saisie, mais dégrade l'auditabilité — à réserver aux classeurs que vous maintenez seul.
• Pour un historique long et saisonnier, préférez FORECAST.ETS et la Feuille de prévision plutôt que des règles manuelles compte par compte.

Si votre reporting mensuel repose encore sur un copier-coller de l'ancien forecast suivi d'ajustements manuels, la prochaine clôture est le bon moment pour migrer ne serait-ce qu'un onglet vers cette structure basée sur FILTER et la ligne de statut : le gain de fiabilité se mesure dès le deuxième mois d'actualisation.