Prévision des ventes sur Excel : tendance, saisonnalité et pipeline commercial
Trois méthodes pour construire une prévision des ventes sur Excel dans une TPE : tendance et coefficients saisonniers à partir de l'historique, fonction PREVISION.ETS, et pipeline commercial pondéré, avec exemples et suivi prévu/réalisé.
Une prévision des ventes sur Excel estime le chiffre d’affaires des prochains mois à partir de ce que l’on sait : l’historique des ventes, leur saisonnalité, les affaires en cours et les décisions prévues (prix, nouveaux produits, actions commerciales). Pour une TPE, trois méthodes suffisent et peuvent se combiner : la tendance avec coefficients saisonniers, la fonction de prévision intégrée d’Excel, et le pipeline commercial pondéré. Ce guide les présente avec leurs formules, puis montre comment suivre l’écart entre prévu et réalisé.
Préparer les données
Partez d’un tableau avec une ligne par mois : mois (premier jour du mois), chiffre d’affaires HT et, si possible, la décomposition par famille de produits ou par canal. Deux ans d’historique sont un minimum pour repérer une saisonnalité. Corrigez les mois exceptionnels (fermeture pour travaux, grosse commande unique) dans une colonne « CA retraité », sinon ils fausseront la prévision. Le suivi des ventes sur Excel fournit directement ces totaux mensuels.
Méthode 1 : tendance et coefficients saisonniers
- Coefficient saisonnier de chaque mois : moyenne des ventes de ce mois sur les années disponibles, divisée par la moyenne mensuelle globale. Un coefficient de 1,6 en décembre signifie que décembre vend 60 % de plus qu’un mois moyen.
- Chiffre d’affaires annuel prévu : CA de l’année écoulée × (1 + taux de croissance retenu), le taux étant justifié par la tendance récente et vos décisions.
- Prévision mensuelle : CA annuel prévu ÷ 12 × coefficient du mois.
En paramètres régionaux français, avec les mois en colonne A et le CA en colonne B :
- Coefficient de janvier :
=MOYENNE.SI.ENS(B:B;C:C;1)/MOYENNE(B:B), où la colonne C contient=MOIS(A2). - Prévision d’un mois :
=CAAnnuelPrevu/12*RECHERCHEX(MOIS(A30);Coef[Mois];Coef[Coefficient])
Vérifiez que la somme des douze coefficients vaut 12 ; sinon, divisez chacun par leur moyenne.
Exemple
Une boutique a réalisé 240 000 € HT l’an dernier et vise 8 % de croissance, soit 259 200 € cette année, ou 21 600 € par mois moyen. Avec un coefficient de 1,6 en décembre et de 0,7 en février, la prévision est de 34 560 € en décembre et de 15 120 € en février. Ces écarts sont précisément ce dont le plan de trésorerie a besoin pour anticiper les mois creux.
Méthode 2 : la fonction PREVISION.ETS
Depuis Excel 2016, la fonction PREVISION.ETS (lissage exponentiel) détecte automatiquement tendance et saisonnalité :
=PREVISION.ETS(A30;B$2:B$29;A$2:A$29;12)
Le dernier argument (12) indique une saisonnalité de douze mois. PREVISION.ETS.CONFINT donne une marge d’incertitude, utile pour construire un scénario bas et un scénario haut. Le menu Données > Feuille de prévision crée le même calcul avec un graphique en quelques clics. Cette méthode est rapide, mais elle ne connaît pas vos décisions : un changement de prix ou un nouveau produit doit être ajouté à la main.
Méthode 3 : le pipeline commercial pondéré
Pour une activité B2B ou de services sur devis, l’historique dit peu de choses sur les prochains mois ; ce sont les affaires en cours qui comptent. Listez les opportunités avec le client, le montant, l’étape et la date de signature probable, puis appliquez une probabilité par étape :
| Étape | Probabilité indicative |
|---|---|
| Premier contact qualifié | 10 % |
| Besoin validé, devis en préparation | 30 % |
| Devis envoyé | 50 % |
| Négociation finale | 80 % |
| Signé | 100 % |
CA pondéré d’un mois : =SOMMEPROD((MOIS(Pipe[DateSignature])=MOIS(A2))*(ANNEE(Pipe[DateSignature])=ANNEE(A2))*Pipe[Montant]*Pipe[Probabilite]). Ajustez les probabilités avec vos taux de transformation réels après quelques mois. Le fichier clients sur Excel peut accueillir ce suivi commercial.
Combiner les méthodes et construire des scénarios
Beaucoup de TPE additionnent une base récurrente (méthode 1 ou 2) et les affaires nouvelles du pipeline (méthode 3). Préparez trois scénarios (prudent, central, ambitieux) avec un sélecteur et =CHOISIR(Scenario;Prudent;Central;Ambitieux). Le scénario prudent sert à vérifier que l’entreprise couvre ses charges : comparez-le au seuil de rentabilité.
Suivre l’écart prévu/réalisé
- Écart en valeur :
=Realise-Prevu - Écart en % :
=SIERREUR(Realise/Prevu-1;"") - Précision moyenne sur 12 mois : moyenne des écarts absolus en %, avec
=MOYENNE(ABS(Ecarts))validée comme formule matricielle ou dans une colonne dédiée.
Expliquez chaque écart supérieur à 10 % en une phrase. Au bout de quelques mois, ces explications montrent si la méthode est biaisée (toujours trop optimiste, par exemple) et comment la corriger. Pour l’affichage mensuel, le tableau de bord financier reprend le prévu et le réalisé.
De la prévision au budget
La prévision des ventes est la première ligne du budget prévisionnel : achats, personnel et marketing en découlent. Retrouvez les autres guides dans Gestion financière des TPE.
Questions fréquentes
Quelle méthode choisir pour une petite entreprise ?
Avec un historique de deux ans et une clientèle de passage, la tendance avec coefficients saisonniers. Pour une activité sur devis, le pipeline pondéré. Souvent, une combinaison des deux.
Combien de mois prévoir ?
Douze mois glissants, mis à jour chaque mois, donnent une bonne visibilité pour la trésorerie et le budget.
Comment prévoir les ventes d’une entreprise qui démarre ?
Sans historique, partez de la capacité (clients servis par jour, heures facturables), du marché local et de votre plan d’action commercial, puis remplacez ces hypothèses par les chiffres réels dès les premiers mois.
PREVISION.ETS fonctionne-t-elle sur Google Sheets ?
Google Sheets ne propose pas d’équivalent exact ; on y utilise la tendance avec coefficients saisonniers ou la fonction FORECAST pour une tendance linéaire.
