formule avec condition multiples pour commission Fermé

Question

Bonjour, 



Je dois effectué un tableau avec un calcul de commission sur un CA vendu avec différents palliers de rémunération. Je ne m'en sort pas du tout et n'arrive pas a trouver la bonne formule. Voici mes critères de calcluls et merci a celui qui arrivera a me trouver la solution

D'abord il y a plusieurs vente dans le mois donc j'ai beaucoup de vente a additionner, j'ai donc un colonne vente avec une ligne par affaire et une autre a coté pour le cumul sur le mois et souhaiterai en avoir une autre encore a coté pour la commission calculé pour cette affaire en fonction de:
Si le cumul du CA vendu est compris entre 0 et  10 000 alors commission appliqué sur le CA de l'affaire à hauteur de 3%
Si le cumul du CA vendu est compris entre 10 0001 et  20 000 alors commission appliqué sur le CA de l'affaire à hauteur de 4%
Si le cumul du CA vendu est compris entre 20 0001 et  30 000 alors commission appliqué sur le CA de l'affaire à hauteur de 5%
Si le cumul du CA vendu est compris entre 30 0001 et  40 000 alors commission appliqué sur le CA de l'affaire à hauteur de 6+%
Au déla de 40 001 commission de 8%...

Voilà vous savez tout, et merci de votre aide

Vaucluse · Accepted Answer

Bonjour
alors voyez ici, ce sera plus simple qu'un long discours

 https://www.cjoint.com/c/CKEiDOpTsaQ

et revenez si besoin.

crdlmnt

Vaucluse · Answer

Bonjour
à adapter
en A1 le CA en B1 la commission avec cette formule 
=SI(A1<=10000;A1*3%;SI(A1<=20000;A1*4%;SI(A1<=30000;A1*5%;SI(A1<=40000;A1*6%;A1*8%)))) 

Note 
cette formule applique le % sur le total du CA à chaque fois.

Dans certain cas, qui ne semble pas être le votre il est demandé de calculer
3% jusqu'à 10000+4% sur le dépassement jusqu'à 20000+ 5%sur le dépassement jusqu'à 30000
ect...
Revenez si c'est votre cas


crdlmnt
Errare humanum est, perseverare diabolicum

Gi-l · Answer

Merci pour votre retour et bien vue pour votre commentaire final car c'est effectivement le cas sur les affaires qui me font changer de pallier de rémunération de commission je dois appliquer les 2 pourcentage de commission le premier % jusqu'au pallier et le 2ème sur le dépassement!!!

Merci de votre aide avec cette nouvelle condition.

GI-L · Answer

Merci pour votre retour, on touche au but mais malheureusement on y est pas encore, en gros voici mon tableau

NOM          CA HT VENDU      CUMUL CA      MONTANT COMMISSION
DUPONT            700.70 € 	700.70 €	                          21.02 €
DURAND         2 494.48 €      3 195.18 €                            74.83 €
PIERRE         12 212.30 €    15 407.48 €	                        420.44 €

Mon souci c'est donc que j'ai besoin de calculer une commission par affaire (car elles peuvent etre rémunérée sur des mois différents) mais le taux de la commission est calculé en fonction du cumul vendu sur le mois en cours en fonction de l'ordre chrono a laquel le produit a été  vendu. Du coup sur la commission de Monsieur PIERRE la com est du 420.44€ calculé comme suit (10000-3195.18)*3% + (12212.30-(10000-3195.18))*4%

J'espère avoir été clair, je voulai vous faire parvenir mon tableau EXCEL mais je ne sais pas comment l'ajouter en lien...

En tout cas merci encore de votre aide.

Vaucluse · Answer

Déposez le sur ce site:
https://www.cjoint.com/
et revenez placer ici le lien donné par le site
A+

GI-L · Answer

http://www.cjoint.com/?3Lcj46cwjwf

Merci encore

Vaucluse · Answer

Bien vu le fichier, mais pouvez vous faire le lien avec l'explication du message précédent et les infos de votre fichiers. je ne trouve pas les exemples que vous évoquez, Mr PIERRE ayant probablement pris des vancances!

soyez le plus précis possible dans votre exemple et explication, ça n'a pas l'air très simple.

En tous cas, je n'a pas pigé

GI-L · Answer

Effectivement ce n'est pas simple, je vais donc essayer d'etre le plus clair possible et de vous donner tous les paramètres.
Le tableau que je vous ai transmis représente toutes les ventes d'un mois réalisé par un commercial. Pour sa rémunération je dois déterminé une commission PAR affaire avec un taux différent en fonction du cumul vendu. C'est à dire que lorsque le cumul de ses ventes dépassera un CA cumul de 10 000€ (ou un autre pallier) les ventes qui viendront se rajouter par la suite seront du coup commissionné a hauteur de 4% (ou du taux correspondant à la tranche) mais le calcul de la commission ne se fait pas sur le cumul mais sur le CA de la Vente seul, le cumul lui nous indique seulement de le taux de commissionnement. 
Pour les ventes qui nous font changer de tranche je dois appliquer le % jusqu'au plafond de la tranche puis sur le dépassement le % de la tranche du dessus.

Dans ce cas, quelques exemples :

Pour la vente DELGADO un taux de 3% a été appliqué sur le CA soit 700.70€ car nous sommes au cumul des ventes dans le premier pallier <10 000€ soit montant de la commission : 700.70 x 3% = 21.02€ 
Idem pour la Vente BARON.

Pour la vente PERROT, cette vente nous fait changer de tranche au cumul des ventes donc calcul de la commission :
Sur les 12 212.30€ de la vente 6 804.82€ sont rémunérés a 3% (10 000€ plafond du 1er pallier - 3 195.18€ cumul avant la vente ET 5 407.48€ sont rémunrés a 4% (12 212.30€ montant de la vente - 6804.82€ partie déjà remmunérées sur la tranche a 3%)

Pour la vente PERROT DROSS un taux de 4% a été appliqué sur le CA soit 3499.18€ car nous sommes au cumul des ventes dans le second pallier avec un CA Cumulé compris entre 10 000€ et 20 000€  soit montant de la commission : 3499.18 x 4% = 139.97€ 

Voilà j'espère avoir été assez clair...

Vaucluse · Answer

Re

voyez ce fichier en colonne N qui affiche ce que j'ai compris de votre problème, mais qui n'a rien à voir avec les résultats que vous trouvez vous?

Je ne comprend pas pourquoi les commissions sont calculées dans votre modèle par rapport au chiffre d'affaire total, alors que ma proposition ne prend en compte que le CA pour chaque personne séparément.

Attention, la formule se base sur le tableau que j'ai rajouté en haut de page, pour en simplifier l'écriture

https://www.cjoint.com/c/CLcmKw9wk13 

Revenez si ça ne convient pas, mais alors revoyez les explications

crdlmnt

GI-L · Answer

Désolé mais effectivement ca ne fonctionne tjrs pas, je ne comprend pas votre formule donc ne peux vous dire ce qu'il ne va pas, je peux par contre vous dire ou sont les erreurs :

- La commission de PERROT pour les 12 212.30€ est calculé entièrement sur une commission a 4% et ne tient pas compte du pallier des 3% entre le cumul déjà atteint et les 10 000€
- Le reste des com. est calculé avec un taux a 3% et ne tient pas compte du pallier atteint avec le cumul du CA...

Je ne sais que vous dire d'autre sauf vous demandez si vous m'avez bien compris : Le calcul de la commission se fait sur le CA VENDU uniquement mais le taux de la commission est quand a lui fonction du cumul de ses ventes...

Désolé je garde espoir!

GI-L · Answer

C'est un cumul mensuel

Vaucluse · Answer

Alors voyez ici cette fois

https://www.cjoint.com/c/CLcpaqhQ4ZN

Par contre j'"ai scindé en deux pour alléger les formules, mais vous pouvez obtenir ça directement en remplaçant en O la valeur de N par la formule en N si vous le souhaitez.
Ceci dit je ne trouve toujours pas les mêmes valeurs que vous, et je pense que les votres ne sont pas cohérentes avec votre demande , car elle s'appuient sur le CA total tous acteurs confondus au lieu d'un calcul individuel.

La base du raisonnement dans la formule en O ou en Q est de dire que l'on calcule au taux inférieur tout ce qui complète entre le plafond correspondant et le montant précédent, et au taux supérieur tout ce qui dépasse du plafond

Au dela de ça, je crois que je ne saurais plus faire mieux

crdlmnt

GI-L · Answer

Je suis bien d'accord avec votre raisonnement sauf que il doit y avoir une erreur dans votre formule car elle calcul les com sur un taux a 3% sur presque toutes les ventes...

Vaucluse · Answer

Alors ça m'échappe totalement et je vous souhaite bonne chance avec quelqu'un d'autre
vous avez en colonne N le cumul par intervenant, cumul progressif dans l'année
tant que ce cumul ne dépasse pas 10000 pour chaque intervenant le calcul se fait effectivement sur 3%.
Bonne chance

GI-L · Answer

Enfin nous nous comprenons, et je comprend l'incompréhension! Ce n'est pas le montant de la vente qui doit être supérieur à 10 000€ mais le cumul du total des ventes mensuelles qui nous donne les palliers de rémunéation.

Mes commerciaux font de nombreuses ventes chaque mois et pour chaqu'une d'entre elles je souhaite avoir un montant de commission avec 3 critères de calcul 

Le montant de la vente sera multiplié par le % corespondant au pallier de rémunération qui lui est calculé sur le cumul des ventes en cours.

Exemple :
Le 15/11/2012 mon commercial fait une vente pour 2 000€ HT mais dans son cumul du mois de Novembre il en a déjà vendu pour 31 000€(sans les 2000€), j'appliquerai donc une commission de 6% sur les 2000€ et ainsi obtenir une valeur de commission pour cette affaire précise de 120€.

Me comprenez vous??

GI-L · Answer

Oui super ca a l'air de fonctionner, j'ai cependant essayé de copier la formule et l'étendre, ca ne fonction pas quand il y a un franchissement de pallie.

Je pense que la formule dans la colonne N est directement en lien avec cellle de la formule O

Pourriez-vous juste m'expliquer un peu le principe et le lien entre ces colonnes au cas j'ai a reproduire la formule!!!


EN TOUT CAS UN GRAND GRAND MERCI A VOUS

GI-L · Answer

Merci encore pour votre aide, le tableau est en place et tout fonctionne!!! 

A+

GI-L · Answer

Bonjour,

Vous avez cru vous être débarrassé de moi? Loupé!!! Désolé de vous solliciter de nouveaux mais en faisant la mise en place je m'aperçois d'une erreur dans le calcul des com.

Si mon commercial vend une première affaire a 30 000€ il devrait avoir une commission de 1200€ hors la formule me donne une commission a 1300€, je rencontre le même problème sur les palliers supérieurs.

http://cjoint.com/?3Lgjdfiykpl 

Merci de votre nouvelle aide.

Vaucluse · Answer

Essayez de commencer la 1° ligne avec une formule différente, soit celle ci en J14:

=SI(H14<=10000;H14*3%;SI(H14<=20000;300+(H14-10000)*4%;SI(H14<=30000;700+(H14-20000)*5%;SI(H14<=40000;1200+(H14-30000)*6%;1800+(H14-40000)*8%))))


Et vérifier comment fonctionne la suite.
Conserver la formule actuelle à partir de J15, après correction  toutefois , car dans votre modèle, la formule fait référence à 4 cellules au dessus au lieu de une
en fait en J15, vous devez faire référence pour la partie de formule dans la colonne J à J14 à la place de J12

GI-L · Answer

Ca fonctionne bien pour la formule de départ, j'ai également modifier la formule du dessous. Par contre, lorsque j'ai une vente d'un gros chantier la calcul de la commission pose un problème, exemple :

1ère vente à 1 000€ donc commission  a 30€
2ème vente à 35 000€ commission à 1 530€ (détail : 9000€*3% (1er palier - la première vente) + 10000*4% (2ème palier de 10 0000 à 20000) + 10000*5% (3ème palier de 20 000 à 30000) + 6000*6% (4ème palier de 30 0000 à 40000)
Hors avec le tableau et les formules il me calcul une commission a 1810€ soit 29 000€*5% + 6000*6%, IL NE PREND DONC PAS EN COMPTE LES PALLIERS.

Merci

Formule avec condition multiples pour commission

20 réponses

Discussions similaires