Comment fixer une condition de "limite" aux matrices d'une sommeprod

GUILLAUME1980

XLDnaute Nouveau
Bonjour à tous,

Je réalise actuellement un tableau de données permettant le suivi des propositions commerciales réalisées par une société ainsi que la rétribution des commerciaux.

Une formule me pose problème, afin de faciliter la compréhension du problème, je vous décris ci-dessous le fonctionnement du tableau.

Règles de fonctionnement du tableau suivi d'affaire:

1) Le CHARGE d' AFFAIRE réalise une étude et soumet une proposition chiffrée au client (Colonne F)

2) Si l'offre est acceptée par le client, le marché est attribué, on valide alors en colonne G et le montant de l'offre vient s'ajouter au C.A. réalisé par le CHARGE D'AFFAIRE (Colonne I)

nota: Il peut y avoir un delta en plus ou moins-value entre le montant de l'offre effectuée et celui du marché signé, on répercute cet écart via la colonne H

3) La rémunération des CHARGE D'AFFAIRE est divisée en 2 parties, une partie "fixe" et une partie "variable", les colonnes de l'encadré rouge calculent la rémunération des chargés d'affaire selon les règles suivantes:

3.1) PARTIE FIXE: Lorsque le CHARGE D'AFFAIRE réalise une ÉTUDE, il perçoit x % du montant HT de l'ÉTUDE, qu'il remporte ou non le marché; soit: HONORAIRES Colonne L = Colonne I OU F si I=0 x Colonne K

- x = 0.50% si volume MARCHE/Volume ÉTUDES (colonne J) = 0%

- x = (volume MARCHE/Volume ÉTUDES)*5% si volume MARCHE/Volume ÉTUDES (colonne J) < 10%

- x = 0.50% si volume MARCHE/Volume ÉTUDES (colonne J) > 10%

Enfin, le volume MARCHE/Volume ÉTUDES (colonne J) < 10% est un seuil à considérer comme un objectif minimum (ici fixé arbitrairement à 10%) qui permet de réduire proportionnellement la partie fixe (à considérer comme une avance sur commission)

Si un chargé d'affaire descend en dessous de cet objectif des 10% de volume MARCHE/Volume ÉTUDES, cela permet de "freiner" son avancement dans le but de ne pas trop s'éloigner de son taux de rémunération maximum (ici 6.8% de son CA/ÉTUDES en colonne T

3.2) PARTIE VARIABLE: Lorsque le CHARGE D'AFFAIRE réalise une ÉTUDE et qu'il remporte le MARCHE, une COMMISSIONS variable lui est attribuée en complément des HONORAIRES déjà perçus, il perçoit alors y % du montant HT du MARCHE (ou C.A.)

- Le montant des HONORAIRES + COMMISSION ne peuvent excéder 6.8% du C.A. réalisé par le CHARGE D'AFF.

- "y" doit être < 6.80% avec prise en compte des HONORAIRES déjà perçus pour les ÉTUDES infructueuses par l'intermédiaire de la colonne M qui détermine le cumul en % de la part que représente les HONORAIRES/C.A. par chargé d'affaire, par conséquent y = 6.8% - %HONORAIRES/C.A. (colonne M)

Mon problème se situe donc ici, le %HONORAIRES/C.A. (colonne M) doit se recalculer après chaque MARCHE attribué, puisque lorsqu'un MARCHE est remporté "y" est fonction du % de la SOMME des HONORAIRES/C.A. et prend en compte les ÉTUDES infructueuses réalisées précédemment qui n’ont pas donné lieu à un marché.

Si le chargé d'affaire réalise un grand nombre d'études infructueuses, ce qui induit une augmentation du %HONORAIRES/C.A. qui peut alors dépasser les 6.80%, ce dépassement devra être régulé lors du prochain marché conclut, ce qui explique la variation de "y", en d'autres termes, y est fonction du %HONORAIRES/CA qui peuvent être considérés comme une avance qui permet au chargé d'affaire d'être rémunéré pour son travail même lorsque les étude qu'il réalise ne se concluent pas systématiquement par la signature d'un marché.

Dernière remarque, le % des HONORAIRES+COMMISSIONS/CA peut être contrôler en colonne T, il croit au fil des ÉTUDES réalisées jusqu’à l'obtention d'un MARCHE, là il se stabilise à 6.80%, puis il croit à nouveau...puis se stabilise dès lors qu'un nouveau MARCHE est conclut...la COMMISSION est donc fonction des HONORAIRES...

EXEMPLE :

Si on démarre l'année à 0, envisageons qu’un chargé d'affaire réalise 6 études infructueuses, "x = 0.50% si volume MARCHE/Volume ÉTUDES (colonne J) = 0%" lui permet d'obtenir une rémunération à considérer comme une avance sur commissions...

En effet, dès qu'il aura remporté son 1er marché, le calcul du % de commissions viendra retrancher les honoraires déjà perçus afin de "caper" la rémunération globale (honoraires+commissions) à 6.8% du C.A. qu'il aura réalisé (c'est ici que se situe mon problème, ajouter une "limite" à la formule sommeprod en colonne M ou le CUMUL des honoraires déjà perçus doit se limiter en dessous de la précédente régulation, autrement dit, au précédent marché remporté!)

Je vous joins ci-dessous le lien pour télécharger le l'extrait du fichier, j'ai volontairement modifié les sommeprod des cellules en vert de la colonne M afin d’obtenir le résultat escompté et d'illustrer la notion de "limite" à la sommeprod...

http://legrand-etudes.perso.sfr.fr/SUIVI_AFFAIRES_2012_DEMO3.zip


Merci par avance pour vos éventuelles propositions.

Guillaume
 

Dugenou

XLDnaute Barbatruc
Re : Comment fixer une condition de "limite" aux matrices d'une sommeprod

Bonjour Guillaume, le forum,

Il te faut faire un calcul pour décaler les cellules L3 et I3 dans le calcul de la colonne M.
je regarde cela aujourd'hui
Si tu pouvais placer le fichier zippé ici en ligne ce serait plus simple pour ceux qui seraient intéressés par la discussion.
Cordialement