Synthèses matricielles sur les échéances de paiement

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Echéances de paiement par calculs matriciels

Pour établir le suivi des factures en cours, un tableau de bord peut s'avérer précieux. Il consiste à émettre une contrainte de date pour isoler les commandes ayant enregistré un retard de paiement. Et ce sont des formules matricielles qui vont nous permettre de dresser un bilan statistique. L'objectif est de mettre en exergue des dysfonctionnements récurrents, s'ils existent.



Dans l'exemple finalisé illustré par la capture ci-dessus, deux synthèses sont dressées. La première concerne le mois défini pour isoler une période précise. La seconde considère l'ensemble des factures pour l'exercice en cours. Il s'agit d'un bon moyen de déceler si certaines périodes en particulier sont plus propices à ce type de désagréments.

Source et présentation de la problématique
Pour développer une telle solution, nous proposons de débuter à partir d'un tableau de données existant.

• Télécharger le classeur echeances-matricielles.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous réceptionnons un classeur muni d'une seule feuille nommée Echeances. Son tableau, situé entre les colonnes B et E énumère des factures émises avec leurs montants en colonne E. Chaque date d'échéance de paiement inscrite en colonne C, est confrontée à la date effective du règlement. Et très vite, nous pouvons observer de nombreux retards.

Un petit tableau de synthèse est présent entre les colonnes G et H. En cellule H5, une liste déroulante permet de définir un mois à isoler pour dresser des statistiques précises et personnalisées.

Ces données sont attendues dans les deux sections inférieures, entre les lignes 7 et 9 pour le mois choisi et entre les lignes 11 et 13 pour l'exercice complet.

Dénombrer les échéances dépassées
Pour commencer par le plus simple, nous proposons de réaliser le dénombrement total des dépassements. Pour chaque facture, il s'agit de comparer la date de paiement avec la date d'échéance. Si la première dépasse l'autre, l'enregistrement doit être comptabilisé. Et pour une comparaison sur les lignes respectives des colonnes, les calculs matriciels sont tout à fait dédiés. Nous devons aussi considérer le cas où aucune date de paiement n'a encore été enregistrée, comme c'est le cas en cellule D22. Cette condition est à ajouter et non à recouper.

De plus et pour simplifier la syntaxe, nous souhaitons désigner les colonnes, soit les matrices, par des noms. Et ces plages sont déjà nommées.

• En haut à gauche de la feuille Excel, déployer la liste déroulante de la zone Nom,

Comme vous le constatez, chaque colonne a hérité de son titre de champ en guise de nom. Si vous cliquez sur l'un d'entre eux, toutes les données correspondantes sont désignées.

• Sélectionner la cellule du premier résultat à livrer, soit H11,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Taper la fonction matricielle pour la somme suivie d'une parenthèse, soit : SommeProd(,
• Ouvrir deux nouvelles parenthèses,

En effet, la première est nécessaire pour accueillir la condition à recouper sur les deux matrices de dates. La seconde doit héberger la première de ces deux matrices.

• Désigner les dates de paiement par le nom de la plage, soit : Date_val,
• Fermer alors la parenthèse de cette première matrice,
• Inscrire le symbole supérieur (>) pour l'inégalité à vérifier,
• Ouvrir une nouvelle parenthèse pour accueillir la nouvelle matrice,
• Désigner les dates d'échéance par le nom de la plage, soit : Echeance,
• Puis, fermer la parenthèse de cette deuxième matrice,
• Fermer alors la première parenthèse englobant cette première condition recoupée,
• Taper le symbole + pour ajouter un nouveau critère à empiler,
• Ouvrir de nouveau une parenthèse pour accueillir la matrice,
• Désigner encore les dates de paiement par le nom de plage, soit : Date_Val,
• Taper le symbole égal suivi de deux guillemets, soit : ='',

Avec la première condition, nous dénombrons toutes les dates de paiement intervenues après les dates d'échéance. Avec ce second critère, nous ajoutons le cas où la date de paiement n'est pas encore renseignée.

• Fermer la parenthèse de cette matrice conditionnelle,
• Puis, fermer la parenthèse de la fonction SommeProd,
• Dès lors, valider la formule avec la touche Entrée du clavier,

Le résultat tombe. Il indique que 10 retards de paiement sont à déplorer sur la totalité de l'exercice. La formule matricielle que nous avons construite est la suivante :

=SOMMEPROD(((Date_Val)>(Echeance)) + (Date_Val=''))

Valeur cumulée des dépassements
Le résultat suivant se déduit naturellement de ce précédent calcul. Il consiste à évaluer l'argent sorti, durant ces périodes ou le paiement n'a pas encore été honoré. Comme nous venons de dénombrer les factures ayant enregistré un dépassement, il suffit, par calcul matriciel, de multiplier ces résultats par chaque montant respectif. Bref, nous devons multiplier les précédentes données par la matrice des montants.

• Sélectionner la cellule H12 et adapter la précédente formule comme suit :

=SOMMEPROD((((Date_Val)>(Echeance)) + (Date_Val=''))*(Montant))

Attention, deux nouvelles parenthèses doivent encadrer les précédents critères afin de pouvoir multiplier leur résultat par la nouvelle matrice. La donnée obtenue est loin d'être anodine pour qu'elle représente un volume total de 24 200 Euros.

Ensuite, il s'agit de connaître le taux de retard. Ce résultat doit naturellement être livré en pourcentage. Le calcul consiste à diviser tous les retards par l'ensemble des factures émises. Ces retards, nous venons de les dénombrer grâce au premier des deux calculs. Le nombre de facture peut être obtenu grâce à la fonction NbVal appliquée sur la première colonne du tableau par exemple.

• En cellule H13, adapter le calcul du dénombrement comme suit :

=SOMMEPROD(((Date_Val)>(Echeance)) + (Date_Val=''))/NbVal(Num_fact)

42% des factures enregistrent donc un retard de paiement. Ce volume est très important. Les responsables en tireront toutes les conclusions utiles. Un service contentieux est il nécessaire ? Quoiqu'il en soit, ces calculs matriciels démontrent une fois de plus tout leur intérêt, et ce n'est pas fini.

Dénombrer les échéances sur une période ciblée
Pour compter toutes les factures qui n'ont pas été honorées à échéance sur une période précise, il suffit de recouper la condition supplémentaire sur le mois sélectionné, dans le calcul matriciel. Dans la liste déroulante en H5, l'indication est fournie sous forme de texte. Mais il s'agit bien d'une date complète affichée ainsi grâce à un format personnalisé.



C'est grâce à ce subterfuge que nous allons pouvoir faire les correspondances avec la matrice des échéances. Pour comparer les mois de deux dates, il suffit d'exploiter la fonction Excel Mois. Le calcul de la somme des montants offre une construction très similaire.

• Sélectionner la cellule H12,
• Dans sa barre de formule, sélectionner l'intégralité de la syntaxe,
• La copier grâce au raccourci clavier CTRL + C,
• Sortir de la barre de formule par la touche Entrée,
• Puis, sélectionner la cellule H7,
• Dans sa barre de formule, coller cette syntaxe, puis l'adapter comme suit :

=SOMMEPROD((((Date_Val)>(Echeance)) + (Date_Val=''))*(MOIS(Echeance) = MOIS(H5)))

Dans la matrice des échéances, nous comparons le mois de chaque date avec celui défini par l'utilisateur. Lorsqu'ils coïncident, ce nouveau critère est recoupé avec les précédents. Pour le mois de Juin par exemple, nous obtenons un total de 2 factures n'ayant pas été honorées à échéance.

Désormais, pour obtenir la somme des montants, il suffit de reprendre cette syntaxe et de multiplier les résultats par la matrice de la dernière colonne.

• En cellule H8, adapter la précédente syntaxe comme suit :

=SOMMEPROD((((Date_Val)>(Echeance)) + (Date_Val=''))*(MOIS(Echeance) = MOIS(H5))*(Montant))

En revanche, pour obtenir le pourcentage de retard sur une période précise, une astuce est nécessaire. La fonction NbVal comptabilise les éléments d'une plage sans se soucier des conditions. Et c'est là, une fois de plus, que les calculs matriciels prouvent tout leur intérêt et leur puissance. Pour comptabiliser toutes les factures du mois défini, nous pouvons exploiter de nouveau la fonction SommeProd. Elle doit recouper la condition du mois choisi sur la matrice des échéances.

• En cellule H9, ajuster la syntaxe du dénombrement comme suit :

=SOMMEPROD((((Date_Val)>(Echeance)) + (Date_Val=''))*(MOIS(Echeance) = MOIS(H5)))/SOMMEPROD((MOIS(Echeance) = MOIS(H5))*1)

Nous utilisons la même astuce que précédemment en multipliant ce résultat par 1 afin de forcer les valeurs booléennes en chiffres.



Immédiatement, nous constatons que la proportion des dépassements pour le mois de Juin, est bien inférieure à la moyenne de l'exercice. En revanche, si vous choisissez le mois d'Août, vous constatez que cette statistique explose. Dans le même temps, nous vérifions que tous ces calculs sont parfaitement dynamiques. Finalement, ce mois correspond au mois de fermeture de nombreuses sociétés. Il y a donc peut être une explication logique et non alarmiste à ces dépassements d'échéances qu'il convient néanmoins de surveiller avec ce type de tableau de bord.

Repérer les échéances non respectées
Pour renforcer les interprétations livrées par les calculs matriciels statistiques, des règles de mise en forme conditionnelle s'avèrent souvent judicieuses. Nous proposons de faire surgir dynamiquement sur fond gris, la ligne de toutes les factures présentant un dépassement. Puis, sur fond bleu clair, nous souhaitons repérer les dépassements pour le mois spécifié.

• Sélectionner toutes les données du tableau, soit la plage de cellules B6:E29,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Puis, cliquer dans la zone de saisie qui se propose juste en dessous,
• Dès lors, taper la syntaxe suivante pour la première règle :

=OU($D6=''; $D6>$C6)

Soit la date de paiement n'est pas renseignée, soit elle dépasse la date d'échéance. Dès lors, nous devons faire ressortir les lignes concernées. Notez que pour respecter l'analyse chronologique de la mise en forme conditionnelle, nous libérons la ligne des cellules impliquées et conservons la colonne figée.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Activer l'onglet Remplissage de la boîte de dialogue qui suit,
• Dans la palette de couleurs, choisir un gris clair,
• Activer alors l'onglet Police de la boîte de dialogue,
• Avec la liste déroulante, choisir un rouge foncé pour le texte,
• Valider ces attributs de format avec le bouton Ok,
• De retour sur la première boîte de dialogue, valider la création de la règle avec le bouton Ok,

Instantanément, toutes les factures qui n'ont pas été honorées à temps, surgissent dans les attributs de format dynamique que nous avons définis.

Selon une procédure strictement identique, pour l'intégralité des données du tableau, il convient de bâtir cette seconde règle :=ET(MOIS($C6)=MOIS($H$5); OU($D6=''; $D6>$C6)) .

Elle vérifie toujours que la date d'échéance n'est pas respectée, mais pour le mois défini par le biais de la liste déroulante. Il convient de choisir un remplissage bleu clair avec un texte rouge foncé.



De retour sur la feuille, vous notez que les alertes visuelles sont très intéressantes. Elles renforcent l'interprétation des résultats de synthèse et donnent une bonne idée sur les proportions. De plus, ces couleurs s'adaptent selon la contrainte émise sur le mois depuis le tableau de bord.