Analyse de portefeuille d'un courtier : méthode

Analyser un portefeuille d'assurance, c'est répondre à quatre questions avec l'export de vos contrats en cours : d'où viennent mes primes, de qui je dépends, depuis quand mes clients sont là, et où se trouvent les clients à revoir. Un export propre et un tableau croisé dynamique suffisent pour les trois premières. La quatrième demande de croiser le portefeuille avec les sinistres et les échéances.

La photo de départ

Exportez de votre logiciel de gestion la liste des contrats en cours à une date donnée, une ligne par contrat. Gardez le fichier tel quel, daté : c'est votre photo, qui servira de point de comparaison la prochaine fois.

Colonnes minimales : numéro de police, numéro de client, type de client (particulier, professionnel, entreprise), branche, compagnie, date d'effet, échéance principale, prime annuelle (hors taxes et toutes taxes comprises, ou TTC, si possible), commission annuelle attendue, gestionnaire ou apporteur. Le détail des formats est sur la page tableau de suivi du portefeuille.

Les quatre découpages

1. Par branche

« Quelle part de mes primes vient de l'automobile, de l'habitation, de la santé, de la prévoyance, des risques d'entreprise ? »

Dans un tableau croisé dynamique : branche en lignes, somme des primes et nombre de contrats en valeurs. Affichez aussi le pourcentage du total. Une branche peut peser beaucoup en nombre de contrats et peu en primes : les deux colonnes racontent des choses différentes.

2. Par compagnie

« Quelle part de mes primes et de mes commissions dépend de chaque compagnie ? »

Même tableau, avec la compagnie en lignes. Triez par primes décroissantes et ajoutez une colonne de cumul : vous voyez combien de compagnies portent l'essentiel du portefeuille. La même lecture sur la colonne des commissions est souvent plus parlante, puisque c'est votre revenu.

3. Par client

« Quels sont mes vingt premiers clients en primes, et que représentent-ils ensemble ? »

Mettez le numéro de client en lignes, triez, et calculez la part des vingt premiers. Si un seul client pèse lourd, son échéance devient une date à surveiller en priorité.

4. Par ancienneté

« Depuis combien d'années mes clients sont-ils au cabinet ? »

Ajoutez une colonne « année de première entrée du client » (la plus ancienne date d'effet de ses contrats), puis regroupez par tranche. Cela montre si le portefeuille se renouvelle ou s'il vieillit.

Les croisements qui font agir

Une fois les découpages faits, ces questions croisent deux sources :

• « Parmi mes vingt premiers clients, lesquels ont une échéance dans les 120 prochains jours ? » (portefeuille × échéancier)
• « Quels clients n'ont qu'un contrat chez nous ? » (voir multi-équipement)
• « Quels clients entreprise ont eu plusieurs sinistres cette année ? » (portefeuille × sinistres)
• « Quelles compagnies ont un écart entre commissions attendues et reçues ? » (portefeuille × bordereaux)

Dans un tableur, NB.SI.ENS compte les lignes qui répondent à plusieurs conditions, et SOMME.SI.ENS additionne selon plusieurs conditions, d'après l'aide Microsoft. Avec un outil d'IA, donnez-lui la définition de chaque colonne et demandez-lui de montrer la formule ou le filtre utilisé, pour pouvoir le refaire.

Exemple, à titre d'illustration

Lecture : trois compagnies portent un peu plus des trois quarts des primes de ce cabinet fictif. Ce n'est ni bien ni mal en soi ; c'est une information à garder en tête quand une de ces compagnies change ses conditions.

Vérifier avant de commenter

1. Total des contrats et des primes identique à celui de votre logiciel à la même date.
2. Pas de contrats résiliés restés dans l'export : filtrez le statut.
3. Une seule unité de prime : annuelle, hors taxes ou TTC, mais pas un mélange.
4. Clients en double : un même client saisi deux fois fausse le classement par client. Triez par nom et adresse pour les repérer.
5. Cinq lignes contrôlées à la main dans les dossiers.