Calcul de commission par tranches

Résolu
Bonjour,

J'ai déjà lu plusieurs articles qui concernent cette question, mais je n'arrive pas à l'appliquer à mon cas précis.
Si quelqu'un pouvait m'aider ce serait cool.

Je cherche donc à calculer des commissions. Le taux de commission est fonction du C.A. annuel atteint. La difficulté est quand la commission est répartie sur 2 tranches de C.A.
J'explique : Quand j'ai atteint 80.000€ de CA, je change de taux de commission.
Prenons un cas, précis :

J'ai atteint à 75.000€ de C.A.
Je génère 8.000€ de C.A.
J'atteins donc 83.000€ de C.A.
Ma commission est donc calculée ainsi : 5.000€ x 22% + 3.000€ x 26%
Et c'est à ce moment là que je ne sais plus comment écrire ma formule.
De mémoire il faut mixer les fonctions recherchev et si.

Merci à celles et ceux qui pourront m'aider.

Tominou64

6 réponses

  • Votes
  • Date
  • Date décroissante
Résumé de la discussion

Calcul des commissions lorsque le chiffre d'affaires atteint des paliers et que le taux varie selon la tranche; la difficulté réside dans le calcul lorsque le CA franchit un seuil. Des échanges proposent des méthodes Excel, notamment des combinaisons de VLOOKUP et IF pour répartir le calcul entre les deux tranches et prendre en compte le CA au calcul. Plusieurs réponses présentent des exemples concrets et des fichiers Excel, avec ajustements sur le recalcul et le format des taux, afin de refléter les différents paliers et les ventes annulées. En cas d'annulation d'une vente, la pérennité du calcul peut être assurée par une colonne dédiée et une macro qui recalculerait l'ensemble des commissions sans toucher aux données historiques.

Bobot (l’IA à votre service)
  1. Contributeur
    Regarde dans https://www.cjoint.com/c/FKnrrQewCv1 onglet "yg".
    Je ne sais pas si c'est clair.
    1. Le lien ne fonctionne plus
    2. Génial :)
      Merci beaucoup yg_be :D
    3. @Tominou64 
      Bonjour Tominou64,

      Tu as écris : « Le calcul de la colonne commission est illogique. » ; or c'est normal
      si yg_be a strictement suivi les remarques incluses dans ton fichier joint, car ces
      remarques ne sont pas entièrement conformes au principe d'application d'un
      barème par tranches ! La 2ème solution que je t'ai proposée applique bien ce
      principe, et je pense que les résultats que j'ai obtenus sont corrects.

      Je te propose de lire mon message #26, qui te sera sûrement très utile,
      et je compte sur toi pour me donner ton avis.

      Cordialement.  😊
       
  2. Contributeur
    Envoie-nous ton fichier, nous pourrons le compléter.
    1. Contributeur
      Le taux de la commission dépend du CA global, pas du CA de chaque vendeur?
      Tu demandes de l'aide pour calculer les colonnes E, F et G, c'est exact?
    2. Oui, c'est tout à fait ça. la commission dépend du C.A. global.
      Effectivement j'ai besoin d'aide pour calculer les colonnes E et F, la colonne G étant une simple multiplication (E x F)
    3. Contributeur
      C'est plus simple de ne pas faire plusieurs lignes pour chaque vente, même si la commission est calculée sur deux taux. Tu n'as pas d'objection si je fais ainsi?
    4. En fait c'est déjà ce que j'ai fait.
      Mais si une vente est annulée alors je dois supprimer la ligne et tout mon tableau est faux.
      C'est pour ça que je cherche la formule qui le calcul tout seul.
      Je sais le faire à un époque.
      De mémoire, il faut faire une comparaison de la ligne "total commission", au tableau des tranches de CA et du taux correspondant avec recherchev, puis faire le calcul avec si.
  3. Bonjour

    Un exemple
    http://www.cjoint.com/c/FKnn4cCTzjb
    Cordialement
    1. Bonjour, merci pour cette réponse rapide. Mais j'ai du mal expliquer mon besoin.

      En fait le choix du taux de commission est fonction du C.A. atteint au moment du calcul de la nouvelle vente.
      PS : Comment faire pour ajouter mon tableau dans le commentaire ?
  4. Bonjour Tominou64,

    Je te propose ce fichier Excel 2007.

    Dis-moi ce que tu en penses, et si ça te convient.
    N'hésites pas à demander plus d'infos si besoin.

    Si ton problème est réglé, merci d'aller en haut de page
    pour cliquer sur « Marquer comme résolu ».

    Cordialement.  😊
    1. Merci @albkan, mais ça ne répond pas à mon problème car ça ne tient pas compte des conditions. Merci pour ton aide en tout cas.
      J'espère que @yg_be arrivera à compléter mon tableau.
  5. Re Bonjour

    Le problème de ton tableau c'est que le trimestre suivant enD2
    tu vas afficher le résultat de D13 et ainsi de suite à chaque trimestre.
    Est-ce bien cela?
    1. Oui c'est bien ça. En fait je ferais D2 = $D$13 TRI_PRECEDENT
    2. @Tominou64Donc comment calculer les comme puisque les conditions en colonne I
      ne seront plus les mêmes
    3. @Tominou64Je rajouterais qu'elles seront les conditions des trimestres suivants
    4. Une fois que j'ai atteint 100.000ې de C.A alors le taux de commission reste 35%.
      Ma difficulté est vraiment les passages de 80.000€ à 100.000€
  6. Bonsoir Tominou64,

    La solution que je t'ai donnée précédemment était avant
    de voir ton fichier joint, de ton message #5 ; je n'avais
    donc pas toutes les données utiles.

    J'ai refais entièrement l'exercice depuis ce fichier joint,
    et je te propose donc cette seconde solution.

    ----------------------------------------

    1) Faute d'orthographe corrigée en H1 et B2 : ATTEINT au lieu de
        ATTEIND ; eh non, désolé de te décevoir, mais ce n'est pas
        interchangeable comme les Dupond et Dupont !  😏

    ----------------------------------------

    2) J'ai amélioré la formule de G3 (recopiée jusque G12) :
        =SI(E3>0;E3;D3)*F3

    ----------------------------------------

    3) Le calcul de la commission étant plus complexe que prévu,
        j'ai préféré laisser tomber l'idée de le faire via une formule,
        et je l'ai fait par une fonction VBA qui est dans Module1 ;
        fais < Alt >< F11 > pour la voir, puis < Alt >< F11 > pour
        revenir sur ta feuille Excel. Tous les commentaires en vert
        de la macro doivent t'aider à comprendre le mécanisme.
        N'hésites pas à me demander plus d'infos si besoin.

        Attention : même s'il est très légèrement différent de tes
        indications, tu verras que le mécanisme utilisé respecte
        bien celui à faire pour un barème par tranches !

    ----------------------------------------

    Dis-moi ce que tu en penses, et si ça te convient.
    N'hésites pas à demander une adaptation si nécessaire.

    Si ton problème est réglé, merci d'aller en haut de page
    pour cliquer sur « Marquer comme résolu ».

    Cordialement.  😊
     
    1. J'utilise Excel sur Mac, peut-être cela change-t-il quelque chose ?
      J'imagine que pour utiliser du VBA tu as du beaucoup bosser, merci beaucoup.
      Cependant, dans la colonne "commission" est inscrit =commission(cellule) et c'est un chiffre en dizaines de milliards qui apparait (et je ne gagne pas tant ;)
      Peut-être dois-je faire une manipulation quelconque pour activer la macro ?
      Merci
    2. @Tominou64Je ne connais pas Excel sur Mac, seulement sur PC ; ce que tu indiques n'est
      pas arrivé sur mon Excel 2007 ; places-toi sur la cellule en question, fais < F2 >
      pour passer en mode modification de cellule, ne change rien et valide par
      < Entrée >, puis dis-moi si tu as un résultat plus cohérent. Si oui : c'est réglé ;
      sinon : c'est peut-être une histoire de format des pourcentages ? Tu peux
      aussi faire < Ctrl >< F9 > pour forcer le recalcul de la feuille. À te lire.
    3. ça ne change rien :(
    4. @Tominou64 
      D'abord, je corrige une erreur que j'ai écris : pour forcer le recalcul de la feuille
      avec mon Excel 2007, ce n'est pas < Ctrl >< F9 > mais : < Ctrl >< Alt >< F9 >
       
      Dans la colonne I "Commission", j'ai ces résultats de I3 à I12 :

      12 540,00 €
      15 180,00 €
      16 060,00 €
      17 380,00 €
      18 640,00 €
      23 750,00 €
      27 950,00 €

      Soit un total de 131 500,00 €

      Comme tu as écris : « c'est un chiffre en dizaines de milliards », je pense que
      tu dois avoir ces mêmes nombres × 100. Pour les 4 taux de F17 à F20, si je
      met un format Standard, j'ai : 0,22 ; 0,26 ; 0,32 ; 0,35. Je crois que si toi tu
      mets un format Standard, tu auras sur ton Excel Mac : 22 ; 26 ; 32 ; 35.

      Donc soit tu changes ces 4 nombres pour mettre 0,22 ; 0,26 ; 0,32 ; 0,35 ;
      soit il faut laisser tel que et changer ainsi la macro (supprime tout le code
      précédent et fais un copier coller de ce nouveau code VBA) :

      
      Option Explicit
      
      
      Function Commission(CA As Currency) As Currency
        Dim comX As Currency, com1 As Currency, com2 As Currency, com3 As Currency
        If CA < [E17] Then             ' CA < 80 000
          comX = CA * [F17] / 100      ' CA × 22 %
        Else                           ' CA >= 80 000
          com1 = [E17] * [F17] / 100   ' 80 000 × 22 % = 17 600
          If CA < [E18] Then           ' CA < 90 000
            ' 17 600 + (84 000 - 80 000) × 26 %
            comX = com1 + (CA - [E17]) * [F18] / 100
          Else                         ' CA >= 90 000
            ' (90 000 - 80 000) × 26 % = 10 000 × 26 % = 2 600
            com2 = ([E18] - [E17]) * [F18] / 100
            If CA < [E19] Then         ' CA < 100 000
              ' 17 600 + 2 600 + (95 000 - 90 000) × 32 %
              comX = com1 + com2 + (CA - [E18]) * [F19] / 100
            Else                       ' CA >= 100 000
              ' (100 000 - 90 000) × 32 % = 10 000 × 32 % = 3 200
              com3 = ([E19] - [E18]) * [F19] / 100
              ' 17 600 + 2 600 + 3 200 + (113 000 - 100 000) × 35 %
              ' 23 400 + (13 000 × 35%) = 23 400 + 4 550 = 27 950
              comX = com1 + com2 + com3 + (CA - [E19]) * [F20] / 100
            End If
          End If
        End If
        Commission = comX
      End Function
      
      

      Tu verras que j'ai ajouté « / 100 » après chaque [F17] à [F20].
      À te lire pour savoir si ça règle ton problème.