Calcul commissions en fonction du CA

AlaMontagne.immo -
PapyLuc51 Messages postés 4516 Date d'inscription Statut Membre Dernière intervention - 28 nov. 2020 à 15:26

Bonjour,

Je suis en train de mettre en place un tableur pour calculer les commissions de mes commerciaux (en immobilier, donc notion d'entrée et de sortie pour le calcul des coms) en fonction d'un barème évolutif basé sur leur CA.

J'ai réussi à répartir les commissions en fonction de "entrée" et "sortie" des ventes entre tous les commerciaux (intégrant mon associé et moi même).

Toutefois, ma formule de calcul doit intégrer le dépassement d'un seuil (X€) pour passer au pourcentage de rémunération supérieur. Et ainsi de suite. Ca c'est ok.

Mon problème est que lorsque je dépasse le 1er palier, toutes les commissions de la colonne sont modifiées alors que je veux que seulement les commissions intervenant après le 1er seuil soient touchées par le pourcentage supérieur... Et ainsi de suite, palier après palier... Je ne parviens pas à trouver la solutions à ce premier problème.

Tout en tenant compte du fait que passer un seuil modifiera la coms du commercial (augmentation) et cela doit impacter également la rémunération des dirigeants (mon associé et moi même) en la réduisant, ce que je n'arrive pas à faire non plus, n'arrivant pas à résoudre l'étape précédente...

J'ai mal à la tête :) !!

Je suis preneur de votre aide si vous avez une solution. Et si mon besoin n'est pas assez clair (ce qui ne m'étonnerai pas) je peux vous transmettre mon fichier...

Au secouuuuuuur!

Cordialement

Xavier

Configuration: Macintosh / Safari 14.0

A voir également:

Calcul commissions en fonction du CA
Fonction si et - Guide
Calcul moyenne excel - Guide
Calcul km marche à pied gratuit - Télécharger - Sport
Fonctionnement du protocole http - Guide
Calcul charpente bois gratuit - Télécharger - Architecture & Déco

8 réponses

Réponse 1 / 8

PapyLuc51 Messages postés 4516 Date d'inscription Statut Membre Dernière intervention 1 486

Bonjour,

Et bien oui, c'est ça, il faut nous transmettre le fichier car c'est comme si c'était un appel au 18 sans indiquer le lieu de l'incendie ;))

Allez dans https://www.cjoint.com/
Cliquez sur "PARCOURIR" pour sélectionner le classeur ou "FAIRE UN GLISSER DEPOSER" dans la fenêtre ;
Clic sur le bouton "CREER LE LIEN" ;
"CLIC DROIT" sur le lien créé "COPIER L'ADRESSE DU LIEN" ;
Sur le post Clic droit "COLLER".

ou dans https://mon-partage.fr/

Cordialement

Réponse 2 / 8

Raymond PENTIER Messages postés 58988 Date d'inscription Statut Contributeur Dernière intervention 17 353

Bonjour Xavier.

Désolé, mais je me refuse à travailler sur des formules aussi inutilement lourdes ... 1155 caractères dans la cellule L3, c'est abominable !

Tu gagnes déjà 2 caractères en remplaçant
SI((D3="ALM")*ET(E3="ALM");I3*0; (32 caractères) par
SI(ET(D3="ALM";E3="ALM");I3*0; (30 caractères)
ce qui, répété 34 fois dans la formule, te fait économiser 68 caractères.
D'ailleurs on se demande à quoi servent tes 34 fonctions ET, puisque si tu les supprimes tu obtiens le même résultat. En effet
SI((D3="ALM")*ET(E3="ALM");I3*0;
est identique à
SI((D3="ALM")*(E3="ALM");I3*0;

Tu as 36 fois I3*
dans ta formule
=SI((D3="ALM")*ET(E3="ALM");I3*0;SI((D3="ALM")*(E3="AA");I3*0,25;SI((D3="AA")*ET(E3="ALM");I3*0,25;SI((D3="AA")*ET(E3="AA");I3*0,5; ...
alors que tu pourrais ne l'avoir qu'une seule fois :
=I3*SI((D3="ALM")*ET(E3="ALM");0;SI((D3="ALM")*(E3="AA");0,25;SI((D3="AA")*ET(E3="ALM");0,25;SI((D3="AA")*ET(E3="AA");0,5; ...

Réponse 3 / 8

Raymond PENTIER Messages postés 58988 Date d'inscription Statut Contributeur Dernière intervention 17 353

Illustration : https://www.cjoint.com/c/JKCa44PSD2g

Réponse 4 / 8

PapyLuc51 Messages postés 4516 Date d'inscription Statut Membre Dernière intervention 1 486

Encore mieux que le tableau de concordance ; une bonne sieste et le cerveau repart :))

En se servant de la tête des deux colonnes ( L1 pour L3) cette formule en L3

=SI(NB.SI($D3:$E3;L$1)=2;$I$3*0,5;SI(NB.SI($D3:$E3;L$1)=1;$I$3*0,25;0))

C'est à dire lorsqu'on trouve 2 fois L1 en D3:E3 ont multiplie par 0,5 ; pour 1 fois ont multiplie par 0,25 et on laisse la cellule à 0 dans les autres cas.

A incrémenter sur les autres colonnes (N3 P3 R3 et T3) et modifier T3 comme ceci

=SI(NB.SI($D3:$E3;T$2)=2;$I$3*0,1;SI(NB.SI($D3:$E3;T$2)=1;$I$3*0,05;0))

Cordialement

PapyLuc51 Messages postés 4516 Date d'inscription Statut Membre Dernière intervention 1 486

Une petite erreur à rectifier enlever $ devant le 3 de I3 pour incrémenter vers le bas

=SI(NB.SI($D3:$E3;L$1)=2;$I3*0,5;SI(NB.SI($D3:$E3;L$1)=1;$I3*0,25;0))

=SI(NB.SI($D3:$E3;T$2)=2;$I3*0,1;SI(NB.SI($D3:$E3;T$2)=1;$I3*0,05;0))

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question

Réponse 5 / 8

AlaMontagne.immo

Ok, merci!
Voici le lien du fichier : https://www.cjoint.com/c/JKBmWtSd3EL

J'ai résolu mon premier problème qui était de réduire les commission de mon associé et moi même (MS et SOL) en fonction de l'évolution des commissions des commerciaux. reste le problème de faire en sorte que les commissions des commerciaux évoluent en fonction de leur CA et du barème, sans que cela modifie les commissions de la première tranche du barème, et ainsi de suite...

Dites moi si c'est assez clair ou si besoin de plus d'explications.

Merci d'avance,

Xavier

Réponse 6 / 8

Raymond PENTIER Messages postés 58988 Date d'inscription Statut Contributeur Dernière intervention 17 353

Cerise sur le gateau : utiliser la fonction OU pour remplacer
SI((D3="AA")*(E3="ALM"); 0,25; SI((D3="ALM")*(E3="AA");0,25;
SI((D3="AA")*(E3="ES"); 0,25; SI((D3="ES")*(E3="AA");0,25;
SI((D3="AA")*(E3="CT"); 0,25; SI((D3="CT")*(E3="AA");0,25;
SI((D3="AA")*(E3="MK"); 0,25; SI((D3="MK")*(E3="AA");0,25;
SI((D3="AA")*(E3="MC"); 0,25; SI((D3="MC")*(E3="AA");0,25;
par
SI(OU((D3="AA")*(E3="ALM");(D3="ALM")*(E3="AA");
(D3="AA")*(E3="ES");(D3="ES")*(E3="AA");(D3="AA")*(E3="CT");
(D3="CT")*(E3="AA");(D3="AA")*(E3="MK");(D3="MK")*(E3="AA");
(D3="AA")*(E3="MC");(D3="MC")*(E3="AA"));0,25;

AlaMontagne.immo

Bonjour Raymond,

Merci, je ne savais pas que je pouvais fair comme cela, un peu plus court en effet :p
Toutefois ça ne résout pas mon problème de répartition et augmentation des commissions en fonction de l'évolution du CA de la personne... Pour ça j'ai pensé à créer un cumul entre les colonnes "COM BASE" et "COM REVUE" de "AA" (et des autres) pour appliquer les paliers en fonction du CA...
Ca ne me parait pas le plus simple à mettre en place, je suis sur qu'il y a des façon plus intelligentes de mettre ça en place...mais là....j'ai l'oeil gauche qui part en tourbillon... :p

Réponse 7 / 8

PapyLuc51 Messages postés 4516 Date d'inscription Statut Membre Dernière intervention 1 486

Bonjour,

Salut Raymond

Encore plus réduit une rechercheV et un tableau de concordances que j'ai nommé Entrées_Sorties

=I3*RECHERCHEV(D3&"/"&E3;Entrées_Sorties;2;0)

Cordialement

PapyLuc51 Messages postés 4516 Date d'inscription Statut Membre Dernière intervention 1 486

Je modifie ma formule pour supprimer les erreurs N/A

=SIERREUR(I3*RECHERCHEV(D3&"/"&E3;Entrées_Sorties;2;0);0)

Ce tableau de concordance et la formule ne sont valables que pour la colonne AA pour les autres cas il faudra ajouter les concordances pour chaque cas dans une autres colonnes - renommer le tableau entier - changer la colonne du résultat attendu dans la formule

Cordialement

AlaMontagne.immo > PapyLuc51 Messages postés 4516 Date d'inscription Statut Membre Dernière intervention

Bonjour,

Ok, je pense que je comprend le principe.
Je sais bien que ma formule est lourde mais je dois avouer ne pas vraiment maitriser ce genre de chose, j'improvise!
Mais cela me fait ajouter sur ma feuille (ou ailleurs...) autant de tableau de concordance que j'ai de commerciaux. Dans mon cas je n'ai que "entrée et "sortie"... N'est pas mieux? Vaut il mieux plusieurs tableaux de concordance, quitte à les basculer sur une autre feuille?

Quoiqu'il en soit, merci vivement pour votre aide.

Cdlt,

Xavier

Réponse 8 / 8

PapyLuc51 Messages postés 4516 Date d'inscription Statut Membre Dernière intervention 1 486

Bonjour Xavier

Si j'ai bien compris la formule en M3 tu dois ajouter à L3 un supplément de COM répartis comme ceci :

Si L101 est :
• <=50000 = sans ajout
• >50000 = L3*0,275
• >80000 = L3*0,3
• >100000 = L3*0,325
• >150000 = L3*0,35

donc un ajout de 0,25 au dépassement de chaque tranche

La formule peut donc être réduite à

=SI(L3<>"";L3+SI(L$101>50000;L3*0,275;SI(L$101>80000;L3*0,3;SI(L$101>100000;L3*0,325;SI(L$101>150000;L3*0,35;0))));0)

Ton fichier en retour avec les formules rectifiées et le tableau de concordances,

https://www.cjoint.com/c/JKCjzxvorwz

en espérant être dans la bonne direction

Cordialement

Discussions similaires

Remplir une cellule (texte) en fonction d'une autre (texte).

Excel : colorer une cellule sous condition d'une autre ?

Votre réponse

Discussions similaires