Suivi des commissions par mois en fonction de la facturation

Bonjour à tous,

Je vous écris car je rencontre des difficultés à réaliser un classeur Excel pour faire un suivi des commissions.

Dans mon entreprise, la vente de contrat génère une commission.

J'aimerais à partir d'une base de données de contrats signés réaliser un suivi des commissions par mois.

Il y a donc une feuille 'base de données' et une feuille 'récapitulatif des commissions'.

À partir de la date de facturation, j'aimerais que dans un tableau (dans la feuille 'récapitulatif des commissions) les sommes des commissions se mettent automatiquement dans les mois auxquels elles correspondent.

Ma deuxième difficulté c'est qu'il y a différents types de facturations : annuelle (payé immédiatement), semestrielle, trimestrielle, à terme échu (payé à la fin du contrat).

Est-ce que quelqu'un sait comment faire ?

Je vous remercie par avance pour votre temps et vos conseils qui me font progresser sur cet outil tout simplement génial.

Cordialement.

Ps: Je suis sur la version Excel 16.35 pour Mac

Bonjour

À partir de la date de facturation, j'aimerais que dans un tableau (dans la feuille 'récapitulatif des commissions) les sommes des commissions se mettent automatiquement dans les mois auxquels elles correspondent.

Pourquoi vous n'utilisez pas le TCD pour faire cela ? Vous avez un TCD à droite pourtant

Cordialement

Bonjour,

Merci pour votre réponse.

Car le TCD m'indique uniquement les données affichées dans ma base de données or certains contrats sont sur plusieurs années et il faudrait (en fonction de la période de facturation) que la commission indiquée se mette automatiquement pour le mois de janvier de l'année d'après.

Exemple simple :

Contrat sur 2 ans facturé annuellement d'un montant total de 20 000 euros

Du 20/01/2020 au 20/01/2022 première facturation de 10 000 euros le 20/01/2020

Donc première commission le 20/01/2020

Deuxième commission (que je ne sais pas comment faire apparaitre) le 20/01/2021.

Après plus compliquer à faire apparaitre si les facturations sont effectuées tous les trois ou six mois.

On dira que les commissions sont de 2,5% du hors taxe facturé.

Vous pensez que les TCD peuvent être la solution ?

Cordialement.

Bonjour Max_MM, le forum

Voyez dans la zone P1:R13 de la feuille Base de données contrats si les formules proposées peuvent vous aider.

Nb échéances dues en P2:

=SI(OU(L2="annuelle";L2="à terme échu");1;SI(L2="trimestrielle";4;SI(L2="semestrielle";2)))*J2

Commission par échéance en Q2:

=(2,5*N2)/(P2*100*SI(B2="MMFA";2;3))

Commissions à payer en R2:

=SOMME(SI(AUJOURDHUI()>K2);ENT(DATEDIF(H2;MIN(AUJOURDHUI();K2);"m")/12)*SI(L2="semestrielle";2;SI(L2="trimestrielle";4;1)))*Q2

Bonsoir,

Je vous remercie pour votre temps et pour les formules.

Celles-ci pourront surement m'aider pour le tableau de la feuille "récapitulatif des commissions"

je vous remercie une fois de plus.

Cordialement.

Bonjour

Vous pensez que les TCD peuvent être la solution ?

Probablement mais comme vous avez une réponse dites moi si vous désirez une proposition. Cela pourrait donner ceci au départ

Dan a écrit :

Bonjour

Vous pensez que les TCD peuvent être la solution ?

Probablement mais comme vous avez une réponse dites moi si vous désirez une proposition. Cela pourrait donner ceci au départ

tcd.png

Bonjour,

Tout d'abord, je vous remercie pour votre temps.

Oui je suis à l'écoute de toutes solutions possibles.

Comment comptez vous faire pour que le TCD prenne en compte que des contrats qui sont facturés semestriellement par exemple apparaissent dans deux mois différents et pour les contrats sur plusieurs années apparaissent sur les années (et mois) futures ?

Dans l'attente de votre retour, je vous remercie une fois de plus du temps que vous y consacrez.

Cordialement.

Re

Pouvez vous me dire si par exemple la photo ci-joint répondrait à votre attente. Elle reprend les mois pour l'année 2020 dont la facturation est annuelle

Cordialement

Dan a écrit :

Re

Pouvez vous me dire si par exemple la photo ci-joint répondrait à votre attente. Elle reprend les mois pour l'année 2020 dont la facturation est annuelle

Cordialement

tcd.png

Re,

Pour janvier et mars je suis d'accord par contre avril j'ai uniquement 62,50 euros en facturation annuelle.

Cordialement.

Re

Pour janvier et mars je suis d'accord par contre avril j'ai uniquement 62,50 euros en facturation annuelle.

Je vois cela où dans votre fichier. Là j'ai 93.75 en avril 2020 (ligne 7)

Crdlt

Dan a écrit :

Re

Pour janvier et mars je suis d'accord par contre avril j'ai uniquement 62,50 euros en facturation annuelle.

Je vois cela où dans votre fichier. Là j'ai 93.75 en avril 2020 (ligne 7)

Crdlt

Re,

C'est exact, vous avez raison.

Excusez moi.

Cordialement.

Re,

Voyez le fichier en retour - onglet Commissions TCD

Si vous ajoutez des données dans la base de données, mettez à jour le TCD par click droite dans le TCD puis Actualiser

Crdlt

Dan a écrit :

Re,

Voyez le fichier en retour - onglet Commissions TCD

Si vous ajoutez des données dans la base de données, mettez à jour le TCD par click droite dans le TCD puis Actualiser

Crdlt

test suivi contrats copie.xlsx

Re,

Je vous remercie pour cette solution apportée.

Je vais essayer de l'exploiter mais je pense que j'ai du mal constituer mon tableau "Base de données" car les commissions dans le tableau sont pour une année avec une facturation annuelle. C'est a dire si le contrat dure 2 ans cette somme doit automatiquement apparaitre l'année suivante à la même période et si la facturation est semestrielle par exemple, la moitié de cette somme doit apparaitre à la date de facture et l'autre moitié 6 mois plus tard (de façons automatique) je ne sais pas si vous voyez ce que je veux dire ?

En tout cas merci beaucoup pour votre temps et votre aide.

Cordialement.

Re

C'est a dire si le contrat dure 2 ans cette somme doit automatiquement apparaitre l'année suivante à la même période et si la facturation est semestrielle par exemple, la moitié de cette somme doit apparaitre à la date de facture et l'autre moitié 6 mois plus tard (de façons automatique) je ne sais pas si vous voyez ce que je veux dire ?

Je vois plus ou moins.

Prenons un exemple dans la ligne 8 :

• Contrat : semestriel
• Durée : 3 ans

La commission de 34.27 Eur sera versée tous les 6 mois ?

Actuellement dans le fichier le TCD vous donne la somme globale de qui doit être versé chaque mois en fonction de l'année et de la facturation choisies en haut.

Crdlt

Dan a écrit :

Re

C'est a dire si le contrat dure 2 ans cette somme doit automatiquement apparaitre l'année suivante à la même période et si la facturation est semestrielle par exemple, la moitié de cette somme doit apparaitre à la date de facture et l'autre moitié 6 mois plus tard (de façons automatique) je ne sais pas si vous voyez ce que je veux dire ?

Je vois plus ou moins.

Prenons un exemple dans la ligne 8 :

• Contrat : semestriel
• Durée : 3 ans

La commission de 34.27 Eur sera versée tous les 6 mois ?

Actuellement dans le fichier le TCD vous donne la somme globale de qui doit être versé chaque mois en fonction de l'année et de la facturation choisies en haut.

Crdlt

Re,

Idéalement, il faudrait que toutes les dates de facturations apparaissent pour que ce soit plus facile, mais je suis bloqué sur ce point la.

Je viens de faire la formule permettant de calculer le montant de chaque commission en fonction du nombre de facturations.

Ex: si un contrat est sur 1 an et qu'il est facturé par trimestre, il y aura donc 4 facturations.

J'ai donc un problème sur la formule qui me permettrait de faire apparaitre toutes les dates.

Les problèmes rencontrés sur le document ci-joint :

• faire apparaitre dans la colonne Facturation 2 la date des contrats annuels sur plusieurs années (la formule que j'ai faite ne me permet pas de les afficher)
• faire apparaitre les autres échéances de facturations.

Lorsque j'ai toutes les dates de facturation il faudra les lier au bon montant.

Qu'en pensez-vous ?

Cordialement.

Re

A la vue de votre fichier cela me semble un peu compliqué cela

Pourriez-vous me faire un tableau du résultat attendu ? Sur cette base je vais réfléchir au moyen le plus simple pour vous proposer quelque chose

Crdlt

Dan a écrit :

Re

A la vue de votre fichier cela me semble un peu compliqué cela

Pourriez-vous me faire un tableau du résultat attendu ? Sur cette base je vais réfléchir au moyen le plus simple pour vous proposer quelque chose

Crdlt

Re,

Dans l'idéal, ce serait que dans la deuxième feuille "récapitulatif des commissions" il y est le total de chaque commissions par mois en fonction de l'année sélectionnée.

Vous voyez ce que je veux dire ?

Cordialement.

Re

A moins que je n'ai pas compris, cela correspondrait au tableau que je vous ai montré dans un post précédent ?

Dan a écrit :

Re

A moins que je n'ai pas compris, cela correspondrait au tableau que je vous ai montré dans un post précédent ?

Re,

Oui mais avec les différentes dates de facturations et les montants adaptés

Bonjour

Dans le fichier joint, je vous ai trouvé une solution par VBA plutot que par formule.

La seule différence par rapport à votre tableau demandé en feuille "Recapitulatif commission" est que j'ai placé les mois en colonne plutot qu'en ligne. Cela me semblait d'ailleurs plus facile notamment si vous voulez l'imprimer.

Pour l'utilisation :

• Allez dans la feuille Recapitulatif Commission TCD
• Dans la cellule A1, entrez l'année désirée pour voir le résultat

J'ai laissé le TCD à droite mais je pense qu'il ne sera pas utile

Attention que, afin que le code fonctionne, l'extension de votre fichier est devenu XLSM au lieu de XLSX

Faite un test sous votre version Excel MAC sachant que ma version 2011 ne me permet pas de confirmer que cela fonctionne.

Cordialement