Comment créer un rapport de vieillissement des comptes clients dans Excel (30, 60, 90 jours)

Un rapport de balance âgée classe les factures impayées dans des tranches en fonction de leur retard de paiement, généralement 0–30, 31–60, 61–90 et plus de 90 jours. Deux décisions déterminent si le vôtre est correct. La première consiste à savoir si vous calculez l'ancienneté à partir de la date d'échéance ou de la date de facturation. La seconde est de savoir si une facture partiellement payée affiche son montant total ou son solde restant dû.

Trompez-vous sur ces deux points et le total de chaque tranche sera faux, ce qui est pire que de ne pas avoir de rapport du tout.

Ce guide explique pourquoi la construction du rapport échoue, les trois approches généralement utilisées et les limites de chacune d'elles. Il s'agit d'un flux de travail de données et non d'un conseil comptable, veuillez donc confirmer le traitement avec le responsable de votre grand livre.

Pourquoi un rapport de balance âgée fait planter un tableur

Le premier problème est la question de la date. Calculer l'ancienneté à partir de la date de facturation vous indique l'âge du document administratif. Le faire à partir de la date d'échéance vous indique le retard du client, et pour le recouvrement, c'est ce chiffre qui vous intéresse.

Les deux approches se défendent et produisent des rapports différents. Le piège classique est un tableur où personne n'a noté l'option choisie.

Le second problème concerne les paiements partiels. Une facture de $10,000 avec $7,000 reçus représente une créance de $3,000, et elle doit apparaître pour un montant de $3,000 dans une seule et unique tranche. Les rapports de balance âgée basés sur une liste de factures plutôt que sur une liste de postes ouverts surestiment discrètement tous les montants.

Le troisième problème est que le rapport est un instantané. Les tranches sont calculées par rapport à aujourd'hui, de sorte que le fichier d'hier est déjà obsolète, et chaque mise à jour recalcule chaque ligne.

Viennent ensuite les lignes complexes. Les avoirs, les acomptes, les factures contestées et les soldes multidevises nécessitent chacun une règle spécifique. Chaque règle doit ensuite survivre au passage de la prochaine personne qui ouvrira le fichier.

Aucun de ces problèmes n'est difficile à résoudre individuellement. Ils le deviennent parce qu'ils se présentent tous en même temps, une fois par mois, dans l'urgence d'une date limite.

Ce que cela vous coûte

Une liste de recouvrement inexploitable. L'intérêt de la répartition par tranches est de savoir qui appeler en premier. Un rapport qui surestime les soldes pousse quelqu'un à réclamer de l'argent déjà reçu.

Un travail à refaire chaque mois. Comme les tranches sont relatives à la date du jour, le rapport de balance âgée n'est jamais figé. Chaque cycle répète les mêmes jointures, les mêmes formules et les mêmes vérifications manuelles.

Des totaux qui ne correspondent pas au grand livre. Lorsque la somme des tranches ne correspond pas au solde des comptes clients, le rapport perd toute crédibilité. Trouver l'erreur prend généralement plus de temps que la création initiale du rapport.

Un rapport de balance âgée est digne de confiance parce que son total correspond au grand livre. Rien d'autre n'a d'importance si cette condition n'est pas remplie.

Les solutions de contournement généralement essayées

Option 1 : Fixer les définitions avant de toucher à une formule

Inscrivez quatre éléments en haut de la feuille : la date de référence pour l'ancienneté, les limites des tranches, si les montants sont bruts ou nets de paiements, et la date d'arrêté.

Cela prend dix minutes et évite les litiges les plus courants. Le Journal of Accountancy détaille la même méthode en insistant également sur l'importance de bien configurer le rapport dès le départ.

Cela détermine également votre source de données. Vous avez besoin d'un extrait des postes ouverts avec les soldes restants, et non d'une liste de toutes les factures jamais émises.

La limite est que les définitions ne calculent rien. Elles vous évitent simplement de calculer la mauvaise chose.

Option 2 : Créer la colonne des tranches, puis pivoter les totaux

Calculez le nombre de jours de retard en soustrayant la date d'échéance de la date d'arrêté, puis associez ce nombre à une étiquette de tranche. TODAY vous donne une date d'arrêté dynamique, et DATEDIF renvoie le nombre de jours entre deux dates.

Pour l'étiquette elle-même, IFS est plus lisible que des instructions IF imbriquées six mois plus tard. Ensuite, totalisez par client et par tranche avec SUMIFS, ce qui permet de garder le calcul auditable ligne par ligne.

Utilisez une date d'arrêté figée plutôt que la fonction TODAY lorsque le rapport est diffusé. Un fichier qui recalcule silencieusement l'ancienneté la semaine suivante contredira la version déjà présente dans la boîte de réception de votre destinataire.

La limite réside dans le volume et les cas particuliers. Les formules tiennent la route, mais les avoirs, les paiements partiels et les litiges doivent toujours être traités à la main.

Option 3 : Conserver un onglet de règles à côté des chiffres

Centralisez les décisions complexes au même endroit : la façon dont les avoirs se déduisent, si les factures contestées sont exclues ou signalées, comment les soldes en devises étrangères sont convertis et à quel taux.

C'est ce qui permet au rapport de rester exploitable lorsque quelqu'un d'autre l'exécute. C'est aussi l'onglet que l'on a tendance à ignorer lorsque la date limite de fin de mois approche.

La limite est qu'un onglet de règles documente les décisions sans les appliquer. Quelqu'un doit toujours mettre en œuvre chaque règle à chaque cycle. Notre guide pour rapprocher les transactions dans un tableur couvre le travail de lettrage qui alimente ce processus.

La limite commune. Ces trois options supposent que vous partiez d'un extrait propre des postes ouverts. Lorsque la source est un export brut de factures accompagné d'un fichier de paiements distinct, le véritable travail consiste à les associer avant même de commencer la répartition par tranches.

Comment créer un rapport de balance âgée avec Powerdrill Bloom

Étape 1 : Importez vos données de factures et de paiements

Importez l'export des postes ouverts, ou les fichiers de factures et de paiements ensemble. Powerdrill Bloom analyse les colonnes dès leur importation, de sorte que les dates d'échéance manquantes, les montants vides et les numéros de facture en double apparaissent avant même le calcul des tranches.

Étape 2 : Décrivez les règles de répartition en langage naturel

Énoncez les règles plutôt que de les programmer. Indiquez que vous calculez l'ancienneté à partir de la date d'échéance à une date d'arrêté spécifique. Donnez les limites des tranches et précisez que les montants doivent être nets des paiements reçus.

Demandez ensuite les vérifications au cours de la même étape. Demandez quelles factures ont des paiements supérieurs au montant facturé, et lesquelles ont des dates d'échéance antérieures à leurs dates de facturation. Demandez enfin si les totaux des tranches correspondent au solde des comptes clients.

Étape 3 : Exportez le graphique, le rapport ou la présentation

Obtenez un tableau de balance âgée par client, un graphique de la répartition des tranches ou une liste de recouvrement triée par solde le plus ancien.

Pourquoi cette méthode est bien meilleure que de tout reconstruire chaque mois

	Méthode manuelle	Powerdrill Bloom
Associer les factures aux paiements	Formules de recherche par fichier	Importez les deux et demandez
Modifier la date d'arrêté	Recalculer et revérifier	Indiquer la nouvelle date
Déduire les paiements partiels	Colonne de solde manuelle	Demander les soldes nets de paiements
Faire correspondre les totaux au grand livre	Vérification manuelle à chaque cycle	Demander si les totaux correspondent

C'est dans les étapes intermédiaires que passe tout votre temps. La répartition par tranches n'est que de l'arithmétique ; obtenir une liste propre des postes ouverts est le véritable travail.

Erreurs courantes

Calculer l'ancienneté à partir de la date de facturation au lieu de la date d'échéance. Pour le recouvrement, la date d'échéance est presque toujours le bon choix. Quel que soit votre choix, indiquez-le clairement sur le rapport.

Afficher les montants des factures au lieu des soldes restants. Une facture partiellement payée doit figurer dans une tranche pour son solde impayé. Utiliser les montants totaux gonfle artificiellement tous les totaux.

La laisser la fonction TODAY recalculer l'ancienneté d'un fichier diffusé. Figez la date d'arrêté avant d'envoyer le rapport, sinon deux personnes liront des chiffres différents dans le même fichier.

Ignorer les avoirs. Un avoir non appliqué est rattaché à un client et réduit ce qu'il doit. L'omettre donne l'impression que le solde est plus critique qu'il ne l'est en réalité.

Répartir par client plutôt que par facture. Les tranches se calculent par facture, puis sont sommées par client. Faire la moyenne de l'ancienneté d'un client masque l'élément le plus ancien, qui est pourtant celui que vous devez cibler.

Ne jamais vérifier par rapport au grand livre. La somme des totaux des tranches doit correspondre au solde de contrôle des comptes clients. Sans cette vérification, votre rapport n'est qu'une simple décoration.

Tout reconstruire à partir de zéro à chaque cycle. Les règles ne changent pas tous les mois, seules les données changent. Conservez les règles et remplacez simplement l'export, selon la même discipline que pour un rapport budget versus réel.

Conclusion

Déterminez la date de référence, utilisez les soldes restants, figez la date d'arrêté et faites correspondre les totaux au grand livre. Ces quatre étapes font toute la différence entre un rapport sur lequel on peut s'appuyer pour agir et un tableau qui suscite des contestations.

Ce qui rend cette tâche coûteuse en temps, c'est que tout est relatif à la date du jour, ce n'est donc jamais fini. Les jointures et les vérifications reviennent à chaque cycle.

Si c'est là que passe votre fin de mois, essayez Powerdrill Bloom sur vos exports de factures et de paiements. Consultez également notre guide pour transformer des états financiers PDF en graphiques et la page d'analyse de flux de trésorerie par IA.

Questions fréquentes

Quelles sont les tranches standard dans un rapport de balance âgée des comptes clients ?

La plupart des rapports utilisent les tranches 0–30, 31–60, 61–90 et plus de 90 jours, souvent avec une colonne pour les montants non échus. Ces limites relèvent d'une convention plutôt que d'une règle stricte, indiquez donc clairement celles que vous avez choisies.

Dois-je calculer l'ancienneté des factures à partir de la date de facturation ou de la date d'échéance ?

Utilisez la date d'échéance si vous souhaitez connaître le retard d'un client, ce qui est l'objectif habituel du recouvrement. Utilisez la date de facturation si vous souhaitez connaître l'ancienneté du document administratif.

Comment gérer les paiements partiels ?

Affichez le solde restant dû, et non le montant initial de la facture, et placez ce solde dans une seule tranche. Travailler à partir d'un extrait des postes ouverts plutôt que d'une liste de factures permet de gérer cela automatiquement.

De quelles fonctions Excel ai-je besoin ?

TODAY ou une date fixe pour la date d'arrêté, et DATEDIF pour les jours de retard. IFS attribue l'étiquette de la tranche, et SUMIFS calcule les totaux par client et par tranche. Aucune d'elles n'est compliquée ; ce sont les définitions qui constituent la partie difficile.

À quelle fréquence le rapport doit-il être reconstruit ?

Au moins une fois par mois, et chaque semaine si le recouvrement est actif, car chaque tranche est relative à la date d'arrêté. Figez cette date sur chaque version que vous diffusez.