Excel : suivre 500 contrats clients avec alertes d’expiration

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.