Vaut-il mieux tenir le tableau à la main ou partir d'un export ?

Partir d'un export de votre logiciel de gestion, refait à date fixe. Un tableau ressaisi à la main finit par diverger du logiciel, et vous ne savez plus lequel croire.

Pourquoi une colonne de numéro de client en plus du nom ?

Parce que deux clients peuvent porter le même nom et qu'un même client peut être écrit de deux façons. Un identifiant unique évite les deux erreurs.

Tableau Excel de suivi d'un portefeuille d'assurance

Un tableau de suivi de portefeuille d'assurance tient en une ligne par contrat, avec un numéro de police unique, un numéro de client, des dates au format date et des montants dans une seule unité. Avec ces quatre règles, vous pourrez le croiser avec vos sinistres, vos bordereaux et vos devis, et poser vos questions à un tableur comme à un outil d'IA. Sans elles, chaque réponse demandera une vérification ligne à ligne.

Les colonnes à prévoir

Colonne	Format	Exemple illustratif	À quoi elle sert
N° de police	texte	AB-2026-00418	Clé unique du contrat
N° de client	texte	C-00217	Relier les contrats d'un même client
Type de client	liste	particulier / professionnel / entreprise	Séparer des règles différentes
Branche	liste	habitation	Découpage du portefeuille
Compagnie	liste	Compagnie A	Rapprochement des bordereaux
Date d'effet	date	01/04/2024	Ancienneté, affaires nouvelles
Échéance principale	date	01/04	Échéancier
Préavis prévu au contrat	nombre de mois	2	Date limite de dénonciation
Prime annuelle toutes taxes comprises	nombre	684,00	Montants
Prime annuelle hors taxes	nombre	560,00	Base de commission, selon vos conventions
Taux de commission	pourcentage	15 %	Commission attendue
Fractionnement	liste	annuel / mensuel	Encaissements et impayés
Statut	liste	en cours / suspendu / résilié	Filtre de base
Date de fin	date	vide si en cours	Résiliations, rétention
Motif de fin	liste	voir suivi des résiliations	Analyse des départs
Contrat remplacé	texte	n° de l'ancienne police	Ne pas compter un remplacement comme un départ
Gestionnaire ou apporteur	liste	initiales	Répartition du travail

Les montants et taux de cet exemple sont fictifs.

Les quatre règles de préparation

Des listes plutôt que du texte libre pour la branche, la compagnie, le statut et le motif. « Habitation », « multirisque habitation » et son abréviation « MRH » écrits dans la même colonne donnent trois branches dans un tableau croisé.
Des en-têtes sur une seule ligne, sans cellules fusionnées, sans ligne vide : c'est ce que demande l'aide Microsoft pour un tableau croisé dynamique, qui conseille aussi de mettre les données sous forme de tableau pour que les nouvelles lignes soient prises en compte à l'actualisation.
Des dates au format date. Une date stockée comme du texte ne se compare pas et ne se décale pas. Pour calculer une date à partir d'une autre, MOIS.DECALER ajoute ou retire un nombre de mois à une date.
Un seul type de donnée par colonne : pas de « voir dossier » dans une colonne de montants.

Relier le portefeuille aux autres fichiers

Le portefeuille devient utile quand on le relie :

aux sinistres, par le numéro de police ;
aux bordereaux de commissions, par le numéro de police et la période ;
aux devis, par le numéro de client ;
aux encaissements, par le numéro de police et l'échéance.

Deux outils pour cela :

RECHERCHEX cherche une valeur dans une plage et renvoie la valeur correspondante d'une autre colonne. L'argument facultatif si_non_trouvé permet d'afficher un texte (« absent du bordereau ») plutôt qu'une erreur. D'après l'aide Microsoft, la fonction n'existe pas dans Excel 2016 ni Excel 2019.
Power Query, et sa fonction de fusion de requêtes : elle associe les lignes de deux tables qui partagent une valeur de colonne. Les jointures « anti » sont précieuses ici : l'anti jointure gauche garde les lignes de la première table sans correspondance dans la seconde (par exemple, les contrats absents des bordereaux).

Les questions à poser une fois le tableau prêt

« Combien de contrats en cours, et quel total de primes, par branche et par compagnie ? »
« Quels contrats ont une date de fin mais un statut "en cours" ? » (contrôle de cohérence)
« Quels clients ont un contrat résilié et un autre toujours en cours ? »
« Quels contrats n'ont pas de taux de commission renseigné ? »

Les deux questions du milieu servent à nettoyer le fichier avant les analyses.

Vérifier le tableau lui-même

Doublons de police : un numéro de police doit apparaître une seule fois. Un tableau croisé avec le numéro de police en lignes et le nombre en valeurs fait apparaître les doublons.
Cellules vides dans les colonnes clés (police, client, compagnie, date d'effet) : filtrez-les une par une.
Total identique à celui du logiciel de gestion à la même date.

Le même tableau sert ensuite pour analyser le portefeuille et pour tenir l'échéancier.