Aller au contenu principal
Le DojoLe Dojo
CommerciauxIntermédiaire40 min

Pipeline commercial CRM sur Excel

Un exercice corrigé pour piloter un pipeline commercial dans Excel, compter les deals avec NB.SI.ENS, calculer leur valeur pondérée et repérer les relances, avec le fichier Excel à télécharger.

Un pipeline commercial, le pipe comme disent les équipes de vente, c'est la liste de tes opportunités avec leur étape, leur probabilité et leur montant, de la prospection à la signature. Tant qu'elles vivent dans la tête du commercial ou dans des notes éparses, la prévision de chiffre d'affaires reste une estimation au doigt mouillé, et des deals s'éteignent en silence faute de relance. Dans cet exercice, on va structurer tout ça ensemble.

L'objectif est de passer d'une intuition à un prévisionnel chiffré et réaliste, qui te montre l'équilibre de ton entonnoir et te pointe les affaires qui dorment. C'est le réflexe attendu d'un commercial indépendant ou d'un responsable d'équipe qui veut piloter sans CRM payant !

Dans la vraie vie
Piloter un pipeline de tête finit toujours de la même façon. La prévision de chiffre d'affaires reste une estimation au doigt mouillé, et des deals s'éteignent en silence faute de relance. Dans le fichier de l'exercice, Groupe Alma pèse 27 000 euros pondérés et n'a donné aucune nouvelle depuis 21 jours, exactement le genre d'affaire qu'une colonne Relance fait remonter.

Ce que tu vas construire

  • Calculer la valeur pondérée de chaque opportunité à partir de sa propre probabilité.
  • Repérer les deals sans activité depuis plus de 14 jours, à partir d'une date du point fixée.
  • Compter les deals par étape et par commercial avec NB.SI.ENS, en une seule formule recopiée sur tout un tableau croisé.
  • Totaliser la valeur pondérée par étape avec SOMME.SI pour lire l'équilibre de l'entonnoir.
  • Prévoir le closing du mois avec NB.SI.ENS et SOMME.SI.ENS, puis mesurer l'écart avec l'objectif.

À connaître avant de commencer

  • Savoir saisir une multiplication entre deux cellules (montant par probabilité).
  • Savoir figer une ligne ou une colonne avec le symbole dollar, comme dans $A2 ou B$1.
  • Connaître NB.SI.ENS au moins de nom, on s'en sert pas à pas pour compter les deals par étape, par commercial et par période.

Voici les données de départ, réparties sur 3 onglets comme dans le fichier (clique sur un onglet sous le tableau pour changer de feuille). Copie-les ou , puis entraîne-toi avant de regarder le corrigé.

ABCDEFGHIJ
1ProspectMontantÉtapeProbaDate créationDernière activitéClosing prévuCommercialValeur pondéréeRelance
2Tech Corp35 000Négociation70%15/01/202602/04/202630/04/2026Dupont
3PME Martin12 000Proposition40%01/02/202620/03/202615/05/2026Martin
4Startup XYZ8 000Prospection10%10/03/202613/04/202630/06/2026Dupont
5Groupe Alma45 000Négociation60%20/01/202625/03/202624/04/2026Garcia
6Innov SAS22 000Proposition50%05/02/202608/04/202615/05/2026Garcia
7BTP Horizon18 500Qualification20%01/03/202601/04/202630/07/2026Martin
8Retail Express9 000Signature90%10/12/202510/04/202620/04/2026Martin
9Cabinet Leblanc6 500Qualification25%15/02/202616/03/202601/05/2026Dupont
10Maison Verte5 000Prospection15%02/04/202603/04/202630/09/2026Dupont
11Santé Plus15 000Prospection20%05/03/202627/03/202631/08/2026Garcia
12Nova Conseil11 000Qualification30%18/02/202614/04/202612/06/2026Garcia
13Agence Pixel4 000Prospection10%06/04/202606/04/202631/07/2026Martin
14Atelier Brun14 000Perdu0%12/01/202607/04/202617/04/2026Dupont

Résoudre l'exercice étape par étape

Coche chaque étape au fur et à mesure. Tente-la dans ton fichier, puis déplie le corrigé.

Ta progression
0/7
1
Calculer la valeur pondérée de chaque dealDans la colonne I (Valeur pondérée) de l'onglet Opportunités, calcule la valeur pondérée de chaque deal à partir de son montant et de sa propre probabilité.
2
Identifier les deals à relancerDans la colonne J (Relance) de l'onglet Opportunités, affiche un message du type « À relancer (21 jours sans activité) », avec le nombre de jours de silence du deal, quand la dernière activité remonte à plus de jours que le seuil de relance, en comptant jusqu'à la date du point de l'onglet Pilotage. Sinon, laisse la cellule vide.
3
Compter les opportunités par étape et par commercialDans l'onglet Entonnoir, remplis les colonnes Dupont, Martin et Garcia avec le nombre d'opportunités de chaque commercial à chaque étape, en écrivant une seule formule que tu recopies sur tout le tableau.
4
Totaliser la valeur pondérée par étapeDans la colonne E (Valeur pondérée) de l'onglet Entonnoir, totalise la valeur pondérée des deals de chaque étape.
5
Compter les closings prévus dans le moisDans l'onglet Pilotage, en B7, compte les deals dont le closing est prévu entre le début et la fin du mois, sans compter les deals perdus.
6
Totaliser la valeur pondérée du moisEn B8 de l'onglet Pilotage, totalise la valeur pondérée des deals dont le closing est prévu dans le mois.
7
Mesurer l'écart avec l'objectifEn B9 de l'onglet Pilotage, calcule l'écart entre la valeur pondérée du mois et l'objectif du mois.
Le combo gagnant
Le duo qui pilote l'entonnoir, c'est NB.SI.ENS qui compte les deals par étape et par commercial, et SOMME.SI qui totalise leur valeur pondérée. Dans l'exercice, la prospection aligne 4 deals pour 4 950 euros pondérés, quand la négociation n'en compte que 2 pour 51 500 euros. Tu obtiens un prévisionnel réaliste, pas le chiffre d'affaires rêvé.

Astuces pour aller plus loin

Calibre tes probabilités sur l'historique réel

Si tu mets 50 % à toutes tes propositions par défaut, ta valeur pondérée sera fausse. Regarde tes données des 12 derniers mois et compte combien de propositions envoyées ont abouti. Si c'est 4 sur 10, tu es à 40 %, pas à 50 %. Pars de ce taux pour chaque étape, puis ajuste deal par deal, comme dans l'onglet Opportunités où deux négociations sont à 70 % et à 60 %.

Nettoie le pipeline une fois par mois

Un pipeline encombré de deals inactifs depuis 60 jours fausse tes prévisions. Réserve 30 minutes chaque premier lundi du mois pour passer les deals morts en Perdu, avec une probabilité de 0 %. Leur valeur pondérée tombe alors à zéro, ta prévision redevient fiable et tes vraies priorités remontent.

Tiens la date de dernière activité à jour

C'est la colonne la plus utile du pipeline. Note la date du dernier échange (email, appel, rendez-vous) à chaque contact. Dans ton propre fichier, écris =AUJOURDHUI() à la place de la date du point de l'onglet Pilotage, et la colonne Relance te donne chaque matin la liste des deals à rappeler. Un deal silencieux depuis 3 semaines est souvent déjà perdu.

S'entraîner sur 3 exercices similaires au pipeline commercial CRM

01

Tableau de bord commercial

Construire un tableau de bord commercial pour suivre le CA, le taux de conversion, le classement et l'atteinte des objectifs de chaque vendeur.
Voir l'exercice

02

Analyse des ventes par région

Construire un tableau d'analyse des ventes par région pour identifier tes zones fortes, tes zones en retard et prioriser tes efforts commerciaux.
Voir l'exercice

03

Retrouver un prix avec RECHERCHEV

Utiliser RECHERCHEV pour relier une commande à un catalogue produit : retrouver le nom, le prix et gérer les codes introuvables.
Voir l'exercice

Tu as peut-être encore une question

Crée un tableau avec une ligne par opportunité et une colonne pour le prospect, le montant, l'étape, la probabilité, la date de dernière activité, le closing prévu et le commercial. Une colonne Valeur pondérée multiplie ensuite le montant par la probabilité. NB.SI.ENS compte les deals par étape et par commercial, et SOMME.SI totalise leur valeur pondérée par étape.

Pour aller plus loin

Tout ce dont tu as besoin pour continuer à t’entraîner se trouve ici.

Besoin d’aide sur Excel ?

Rejoins le Dojo pour poser tes questions, obtenir de l’aide sur tes fichiers Excel et participer chaque mois à un cours collectif en direct.

Rejoindre gratuitement

Gratuit · Sans carte bancaire