Gérer un portefeuille de contrats sans CRM dédié

Une société de services ou un bailleur qui gère 500 contrats annuels risque de manquer des échéances : renouvellement, revalorisation, résiliation, préavis. Excel avec alertes conditionnelles et notifications automatiques est la solution low-cost efficace.

Structure du classeur

Feuille Contrats : NumeroContrat, Client, TypeContrat, DateDebut, DateFin, DureeMois, Montant, Frequence, Statut, ResponsableCommercial, Conditions, PJ.

Calculs de vie du contrat

JoursRestants : =DateFin - AUJOURDHUI()
EstBientotExpire : =SI(JoursRestants <= 60; "ALERTE"; "OK")
ProchaineRevalorisation : =DateDebut + Periodicite (ex 1 an)
Progression : =(AUJOURDHUI() - DateDebut) / (DateFin - DateDebut)

Mise en forme conditionnelle

Ligne entière rouge si JoursRestants < 30. Orange si entre 30 et 60. Vert si > 60. Police barrée si Statut = Résilié.

Dashboard de vision globale

Contrats actifs (nombre et CA récurrent annuel)
Contrats expirant dans 30/60/90 jours
Contrats à revaloriser ce mois
Top 10 clients par CA contractuel
Churn prévu (contrats résiliés sur les 3 derniers mois)

Alertes automatiques par email

Macro VBA planifiée à l’ouverture du classeur ou via Power Automate :

Sub AlertesEcheances()
 Dim i As Long
 For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
 Dim jours As Long
 jours = Range("DateFin").Cells(i) - Date
 If jours <= 60 And jours > 0 Then
 EnvoyerAlerte Range("ResponsableEmail").Cells(i).Value, _
 Range("Client").Cells(i).Value, jours
 End If
 Next
End Sub

Scénarios contractuels courants

Renouvellement tacite

Contrat reconduit automatiquement sauf dénonciation 3 mois avant. Colonne DateLimiteDenonciation = DateFin – 90 jours. Alerte ciblée si on est à moins de 2 semaines.

Indexation annuelle

Révision selon indice BTP ou ICC. Colonne calcul :

NouveauMontant = MontantActuel × (IndiceNouveau / IndiceReference)

Excel fait le calcul, alerte envoyée au commercial pour négociation.

Résiliation prévue

Champ StatutFutur : « Résiliation au DD/MM/YYYY ». Dashboard qui affiche les futures résiliations pour anticiper les efforts de rétention.

Modèles d’avenants

Macro qui génère automatiquement : projet d’avenant tarifaire, lettre de résiliation, certificat de fin de contrat. Fusion Word depuis les données Excel.

Historique des modifications

Feuille Historique : chaque changement (montant, durée, etc.) est tracé avec date, utilisateur, ancien/nouveau. Essentiel en cas de contentieux.

Analyse par cohorte de signature

TCD : année de signature × durée moyenne effective. Révèle les tendances : contrats signés en 2022 durent en moyenne 2,3 ans, ceux de 2024 sont à 2,8 ans = amélioration de la rétention.

Conclusion

Ce système Excel évite les pertes de CA par manque de suivi et professionnalise la gestion commerciale. Pour 500 contrats actifs, le ROI est immédiat. Au-delà de 2 000 contrats ou pour un cycle commercial complexe (devis-contrat-facturation), un CRM dédié (HubSpot, Pipedrive) devient pertinent.

Voir aussi

Étape 1 — Préparer la structure de la base contrats

Avant toute formule, posez une feuille propre. Dans une PME d’Almadies qui gère 500 contrats de maintenance, la dérive habituelle vient d’un classeur où chaque commercial ajoute ses colonnes. Le résultat : DATEDIF qui renvoie #NOMBRE!, des alertes silencieuses, et un contrat de 12 millions FCFA qui expire un dimanche sans renouvellement signé.

Ouvrez un classeur vide et nommez la feuille Contrats. Convertissez la plage en tableau structuré (Ctrl+T) — c’est ce qui permettra aux formules de s’étendre automatiquement quand vous ajouterez le 501ᵉ contrat.

Colonnes minimales :
A : ID_Contrat (texte, ex. CTR-2026-0001)
B : Client
C : Date_Signature (date)
D : Duree_Mois (nombre entier)
E : Date_Fin (=DATE.DECALER([@Date_Signature];[@Duree_Mois];0))
F : Montant_FCFA (nombre)
G : Statut (liste : Actif / Expiré / Renouvelé / Résilié)
H : Responsable

Après validation, le tableau prend un nom (par défaut Tableau1, renommez-le tblContrats via Création de tableau). Toute formule pourra désormais référencer tblContrats[Date_Fin] au lieu d’une plage figée. Confirmation pratique : ajoutez une ligne en bas, la mise en forme se propage seule.

Étape 2 — Calculer les jours restants avant expiration

Le cœur du suivi est une colonne calculée qui dit, chaque matin à l’ouverture, combien de jours il reste sur chaque contrat. C’est elle qui pilotera les alertes et la mise en forme conditionnelle.

Ajoutez une colonne Jours_Restants en I et saisissez :

=SI([@Statut]="Actif";[@Date_Fin]-AUJOURDHUI();"")

La fonction AUJOURDHUI() recalcule à chaque ouverture du fichier. Le test sur le statut évite d’afficher des nombres négatifs sur des contrats déjà résiliés.

Sortie de référence : un contrat signé le 15 janvier 2026 pour 12 mois expire le 15 janvier 2027. Au 5 mai 2026, la cellule affiche 255. Si vous voyez #####, élargissez la colonne ; si vous voyez une date au lieu d’un nombre, forcez le format Standard sur la colonne.

Étape 3 — Mettre en forme conditionnelle les paliers d’alerte

Trois paliers couvrent 95 % des besoins commerciaux : 90 jours pour préparer la proposition de renouvellement, 30 jours pour la relance ferme, 7 jours pour l’escalade au directeur commercial.

Sélectionnez la plage I2:I501, allez dans Accueil → Mise en forme conditionnelle → Nouvelle règle → Utiliser une formule. Saisissez successivement les trois règles :

=ET($I2>0;$I2<=7)      → fond rouge, texte blanc gras
=ET($I2>7;$I2<=30)     → fond orange
=ET($I2>30;$I2<=90)    → fond jaune

Cochez Interrompre si vrai pour la règle rouge afin qu’un contrat à 5 jours ne soit pas écrasé par la règle jaune. Confirmation pratique : triez la colonne par couleur, vous obtenez en haut les contrats critiques de la semaine.

Étape 4 — Générer la liste des contrats à relancer cette semaine

Le tableau brut de 500 lignes est illisible en réunion commerciale du lundi matin. Il faut une vue filtrée qui ne montre que les contrats à traiter dans les 30 jours.

Sur une nouvelle feuille Alertes, en A2, saisissez la formule dynamique :

=FILTRE(tblContrats[[ID_Contrat]:[Responsable]];
        (tblContrats[Jours_Restants]<=30)*(tblContrats[Jours_Restants]>0);
        "Aucune alerte cette semaine")

La fonction FILTRE n’existe que sur Excel 2021 et Microsoft 365. Sur Excel 2019 ou antérieur, il faut passer par un Tableau croisé dynamique avec un segment chronologique. Vérifiez votre version via Fichier → Compte.

Sortie attendue : la plage A2:G? se remplit automatiquement et grandit ou rétrécit selon la date du jour. Si vous voyez #PROPAGATION!, c’est qu’une cellule en dessous bloque la zone de débordement — videz-la.

Étape 5 — Compteur de tableau de bord

Le directeur veut un chiffre, pas un tableau. Posez une zone synthèse en haut de la feuille Alertes, en cellules J1:K4.

J1 : Critiques (≤7j)   K1 : =NB.SI(tblContrats[Jours_Restants];"<=7")-NB.SI(tblContrats[Jours_Restants];"<=0")
J2 : À relancer (≤30j)  K2 : =NB.SI.ENS(tblContrats[Jours_Restants];"<=30";tblContrats[Jours_Restants];">7")
J3 : À préparer (≤90j)  K3 : =NB.SI.ENS(tblContrats[Jours_Restants];"<=90";tblContrats[Jours_Restants];">30")
J4 : Montant exposé    K4 : =SOMME.SI(tblContrats[Jours_Restants];"<=30";tblContrats[Montant_FCFA])

La cellule K4 est l’argument financier qui débloque les budgets : un cabinet d’audit du Plateau d’Abidjan qui voit 47 millions FCFA en zone rouge réagit autrement qu’à une simple liste de noms. Formatez K4 en nombre avec séparateur de milliers et suffixe « FCFA » via le format personnalisé # ##0" FCFA".

Étape 6 — Notification Outlook automatique le lundi matin

L’alerte visuelle ne suffit pas si personne n’ouvre le fichier. Le réflexe utile : un mail récapitulatif envoyé chaque lundi à 8 h via Power Automate (inclus dans Microsoft 365 Business Standard).

Créez un flux planifié dans flow.microsoft.com avec trois actions : Récurrence (lundi 8 h), Lister les lignes présentes dans un tableau (filter query : Jours_Restants le 30), Envoyer un courrier électronique (V2). Stockez le classeur sur OneDrive Business pour que le connecteur Excel puisse y accéder — un fichier en local ne marchera pas.

Test concluant : le premier lundi suivant l’activation, un mail arrive avec le tableau HTML des contrats à traiter. Si rien n’arrive, vérifiez l’historique d’exécution du flux : 90 % des échecs viennent d’un nom de tableau Excel mal orthographié dans le filter query.

Étape 7 — Sauvegarde et journal de renouvellements

Un fichier Excel critique sans journal est une bombe à retardement. Ajoutez une feuille Journal avec colonnes Date, ID_Contrat, Action, Utilisateur. À chaque renouvellement, l’opérateur duplique la ligne dans tblContrats, met l’ancienne en statut « Renouvelé », et trace l’opération dans Journal.

Pour la sauvegarde, activez l’historique de versions OneDrive (gratuit, conserve 30 jours) — c’est ce qui vous sauve quand un stagiaire écrase la colonne Date_Fin avec un copier-coller malheureux. Pour creuser ce sujet sur la modélisation Excel, consultez notre tutoriel RECHERCHEX pour remplacer RECHERCHEV et le scoring RFM pour prioriser les renouvellements selon la valeur client.

Modele de courrier de relance contractuelle pre-expiration

Trois mois avant l’echeance d’un contrat critique, envoyez un courrier de relance qui ouvre la conversation de renouvellement sans precipiter le client vers la sortie. Le ton est important : ni alarmiste ni complaisant, mais professionnel et orienté valeur. Le canevas tient en quatre paragraphes : rappel du contexte (date de signature, perimetre), bilan factuel des livrables ou volumes traites, proposition de creneau pour un point bilan, conclusion qui projette deja sur la nouvelle periode.

Pour une agence digitale a Dakar qui suit cinquante contrats clients, automatiser ce courrier depuis Excel est un gain de temps majeur. Une cellule fusionne les variables (nom client, date echeance, montant annuel, nombre de livrables) avec un template texte stable. Il suffit ensuite de copier la cellule, coller dans Outlook et personnaliser les deux phrases qui necessitent un toucher humain.

Mesurez le taux de reponse a 14 jours : si plus de 30 pour cent des clients ne repondent pas, c’est un signal a remonter au commercial qui doit declencher une relance telephonique avant que le delai de preavis ne soit atteint. Cette discipline transforme un suivi contractuel passif en pilotage actif du renouvellement.

Gerer proprement les avenants successifs sans perdre la trace

Un contrat de prestation evolue souvent au fil du temps : nouvelle prestation ajoutee, prix renegocie, perimetre etendu. Chaque modification doit etre formalisee par un avenant signe par les deux parties, et chaque avenant porte un numero sequentiel rattache au contrat initial. Dans Excel, ajoutez une feuille Avenants avec les colonnes Numero contrat, Numero avenant, Date signature, Description courte, Montant impact, Document associe.

Conservez le PDF de chaque avenant signe dans un dossier dedie au contrat, avec une convention de nommage stricte type CONTRAT_2026_001_AVENANT_002.pdf. Cette discipline previent les litiges qui surviennent typiquement deux ans apres la derniere modification, quand les memoires se brouillent et que personne ne sait plus qui a accepte quoi.

Au moment du renouvellement, consolidez le contrat initial et tous ses avenants dans un nouveau document complet plutot que de prolonger un empilement de papiers. Ce travail de remise a plat prend deux heures par contrat majeur mais simplifie radicalement les annees suivantes.

Calculer et piloter le taux de renouvellement contractuel

Le taux de renouvellement, ou retention rate, mesure la part des contrats qui sont effectivement reconduits a leur echeance. Pour un cabinet de services au Plateau ou une SSII a Abidjan, c’est l’indicateur le plus structurant de la sante commerciale. Une formule Excel calcule, sur les douze derniers mois glissants, le ratio nombre de contrats renouveles divise par nombre de contrats arrivés à échéance. Un taux superieur a 85 pour cent est excellent, entre 70 et 85 sain, en dessous de 70 il faut comprendre pourquoi.

Decomposez ce taux par segment client (TPE, PME, grand compte) et par type de prestation (recurrent, ponctuel) pour identifier les zones de fragilite. Souvent, un taux global rassurant masque un taux catastrophique sur un segment specifique qui exige une action prioritaire. Voir aussi notre tutoriel scoring RFM Excel qui complete cette analyse cote clientele e-commerce.

Anticiper les ruptures contractuelles via un score de risque

Au-dela du suivi mecanique des dates, ajoutez a votre tableau Excel une colonne Score de risque calculee a partir de signaux faibles : retard de paiement sur les trois derniers mois, baisse du volume facture, changement d’interlocuteur cote client, absence de reponse aux mails de suivi. Une formule simple SI imbriquee attribue un score de 0 a 5 par signal et la somme produit un indice global. Au-dessus de 8, un commercial doit immediatement organiser un point client.

Cette approche predictive permet de declencher une action avant l’echeance, quand il est encore temps d’agir. Les commerciaux experimentes confirment que dans 60 a 70 pour cent des cas, un client qui rompt un contrat l’avait signale par plusieurs micro-signaux dans les six mois precedents. Capter ces signaux sauve des dizaines de milliers de FCFA de chiffre d’affaires recurrent.

Partagez le tableau du score de risque avec la direction commerciale chaque debut de mois et arbitrez collectivement les actions a mener. Cette routine, etalee sur un an, transforme la gestion contractuelle d’une corvee administrative en levier strategique de pilotage commercial.