tableau Excel de notation des offres par la formule "Avantages"

[claude pomero]

Raffali2 m'a demandé dans le fil sur la méthode "avantages" s'il était possible d'utiliser excel pour noter les offres reçues avec cette formule.

Je vous propose le tableau Excel de notation avec le lien suivant:

http://www.cotref.com/medias/files/tableau-simulation-formule-avantages-07-2016-v1.2.xlsx

et son mode d'emploi avec le lien suivant:

http://www.cotref.com/medias/files/mode-d-emploi-formule-avantages.pdf

J'ai mis un exemple pour bien préciser les unités à employer:  les points pour les % de pondération et la valeur décimale pour la notation, qui permet de déterminer la note choisie quel que soit le barème de notation que vous utilisez. Bien sûr, vous pouvez effacer cet exemple pour obtenir le tableau vierge.

J'ai placé en bas du tableau le résultat que vous obtenez si vous utilisez la formule actuelle la plus utilisée:  (prix le moins disant/prix à noter) x pondération du prix  + note du prix non modifiée par l'écart de prix avec le moins disant.

Vous constaterez à l'usage que les écarts de notation entre les 2 formules sont d'autant plus importants que les différences de prix et/ou de notes sont importants.

Je vous remercie de vos critiques ou remarques sur l'emploi de ce tableau

[nb1987]

Le tableau est bien fait, mais il confirme qu'avec l'application de la méthode avantage la pondération annoncée est altérée ce que le JA peut sanctionner.
En effet, la pondération appliquée à la société Roger Martin n'est pas de 60% pour le C1, 20% pour le C2 et 20% pour le C3 mais de 60% pour le C1, d'environ 15,85% (20*P0/P) pour le C2 et d'environ 15,85% (20*P0/P) pour le C3.

[speedy]

mais alors çà ne ferait pas 100% ....

[claude pomero]

Le tableau est bien fait, mais il confirme qu'avec l'application de la méthode avantage la pondération annoncée est altérée ce que le JA peut sanctionner.
En effet, la pondération appliquée à la société Roger Martin n'est pas de 60% pour le C1, 20% pour le C2 et 20% pour le C3 mais de 60% pour le C1, d'environ 15,85% (20*P0/P) pour le C2 et d'environ 15,85% (20*P0/P) pour le C3.

la remarque est très intéressante!
Il faut noter d'abord que la pondération appliquée est bien celle annoncée (20% pour C2, par exemple).  Ce critères est affecté par la note donnée à l'offre de roger Martin (ici 0.8, ce qui donne 16 points à ce critère).
On vérifie ensuite que la note qualité des offres est bien le total de ces notes qualité,  ici 88 points dans la ligne orange.
Mais la règlementation indique d'attribuer le marché à l'offre économiquement la plus avantageuse, c'est à dire celle qui obtient le meilleur rapport qualité/prix (voir directives européennes ou le guide des prix de la DAJ):  il convient donc de diviser cette note qualité par le prix de l'offre correspondante, ce que fait la formule avantages.  Le résultat de cette division est la ligne verte du tableau.
Ce n'est donc pas la note qualité qui est altérée, mais son résultat qui est divisé par le prix.

je pense que vous admettrez que si deux offres obtiennent les mêmes points en qualité, comme ici 88 points; et qu'une est d'un prix double de l'autre; que l'offre économiquement la plus avantageuse est la moins chère?
Ici, le  rapport qualité/ prix de l'offre la plus chère serait de la moitié de l'offre la moins chère...

[hpchavaz]

La formule ligne 24  :
=SI(B3="";"";(MIN($B$3:$F$3)/B3)*B22)
ne couvre pas la totalité des colonnes.
idem pour la formule de la ligne 27 qui serait sans doute plus simple à comprendre si elle était :
=SI(B3="";"";((MIN($B$3:$EZ$3)/B3)*B4)+(B9+B12+B15+B18+B21))

[nb1987]

Je maintiens que, dans la note globale pondérée utilisée pour déterminer le classement du candidat, la pondération de C2 et C3 n'est pas de 20%, mais bien de (20*P0/P)%.

Ainsi, avec l'application de la méthode « avantages » pour l'offre de « Roger martin » la note pondérée de C2 n'est pas 16 mais de 12,68.

Par ailleurs, seule l'offre la moins onéreuse est notée sur 100 avec une pondération conforme au RC.

Avec l'application de la méthode « avantages », on a :

= Note globale pondérée « Roger Martin »

=Note C1*Pondération C1 + Note C2*Pondération C2 + Note C3 * Pondération C3

= (1*210000/265000)*60+0,8*(20*210000/265000)+0,6*(20*210000/265000)

= 0,7924*60+0,8*15,8490+0,6*15,8490

= 47,55 + 12,68 + 9,51

= 69,74

[hpchavaz]

Je maintiens que, dans la note globale pondérée utilisée pour déterminer le classement du candidat, la pondération de C2 et C3 n'est pas de 20%, mais bien de (20*P0/P)%.

Cela est vrai ou faux selon le point de vue.

Il faut bien comprendre que la pondération n'a de sens que dans le contexte d'une méthode d'aggrégation donnée(*) .

• Si l'on raisonne dans le cadre d'une aggrégation du type P + Q (**), comme à l'école, votre affirmation est vraie.


• Si l'on raisonne dans le cadre d'une aggrégation du type P/Q, ratio qualité prix, votre affirmation est fausse.

*) et d'une méthode de notation donnée, mais c'est une autre histoire.
**) ce qui est votre cas

[claude pomero]

Je maintiens que, dans la note globale pondérée utilisée pour déterminer le classement du candidat, la pondération de C2 et C3 n'est pas de 20%, mais bien de (20*P0/P)%.

Ainsi, avec l'application de la méthode « avantages » pour l'offre de « Roger martin » la note pondérée de C2 n'est pas 16 mais de 12,68.

Par ailleurs, seule l'offre la moins onéreuse est notée sur 100 avec une pondération conforme au RC.

Avec l'application de la méthode « avantages », on a :

= Note globale pondérée « Roger Martin »

=Note C1*Pondération C1 + Note C2*Pondération C2 + Note C3 * Pondération C3

= (1*210000/265000)*60+0,8*(20*210000/265000)+0,6*(20*210000/265000)= 69,74

j'ai du mal à comprendre votre argumentation:
la note de roger martin sur C3 est bien de 16 comme l'indique la case B9...
votre calcul de cette offre par la méthode avantages correspond bien au résultat affiché en B 24?
Vous avez bien appliqué la formule, qui applique la réduction de notation à l'ensemble des critères en les multipliant par Po/P

[claude pomero]

La formule ligne 24  :
=SI(B3="";"";(MIN($B$3:$F$3)/B3)*B22)
ne couvre pas la totalité des colonnes.
idem pour la formule de la ligne 27 qui serait sans doute plus simple à comprendre si elle était :
=SI(B3="";"";((MIN($B$3:$EZ$3)/B3)*B4)+(B9+B12+B15+B18+B21))

merci de ces remarques; je suis en train de corriger le tableau

[nb1987]

j'ai du mal à comprendre votre argumentation:
la note de roger martin sur C3 est bien de 16 comme l'indique la case B9...
votre calcul de cette offre par la méthode avantages correspond bien au résultat affiché en B 24?
Vous avez bien appliqué la formule, qui applique la réduction de notation à l'ensemble des critères en les multipliant par Po/P

60% de 0,7924 + 20% de 0,8 + 20% de 0,6 ne sera jamais égal à 69,74, ainsi l'application de votre méthode vient bien altérer la pondération affichée puisque pour obtenir la note globale pondéré de 69,74 vous avez fait en définitive le calcul suivant : 60% de 0,7924 + 15,8490% de 0,8 + 15,8490% de 0,6.

[claude pomero]

60% de 0,7924 + 20% de 0,8 + 20% de 0,6 ne sera jamais égal à 69,74, ainsi l'application de votre méthode vient bien altérer la pondération affichée puisque pour obtenir la note globale pondéré de 69,74 vous avez fait en définitive le calcul suivant : 60% de 0,7924 + 15,8490% de 0,8 + 15,8490% de 0,6.

vous parlez sans arrêt d'altération de la pondération: pourtant le calcul de la note de l'offre fait bien intervenir les pondérations de chaque critère multipliés par leur note obtenue par l'offre; on trouve trois critères: le critère réalisation du cahier des charges (appelé critère prix) et les critères 2 et 3.
La seule particularité est que ce critère cahier des charges étant obligatoirement réalisé par les OE, il est donc noté 10/10.
On obtient ainsi la note qualité de l'offre (ici 88 points), que l'on divise par son prix pour obtenir le rapport qualité/prix conformément à la règlementation. la multiplication par le prix le moins cher ne sert qu'à créer un barème de notation entre zéro et 100, ce qui paraît commode.
Ce qui semble vous choquer, c'est de diviser aussi les critères 2 et 3 par le rapport des prix Po/P:  ceci vient de la volonté de calculer le vrai rapport qualité'prix et c'est le seul moyen d'y arriver!

[nb1987]

Méthode « avantages » = Po/P× (Points du prix ×1/1+Points de C2 × (Note de P)/1+ Points de C3 × (Note de P)/1))
Méthode « avantages » = P0/P x Points du prix × 1/1+  P0/P x Points de C2 × (Note de P)/1  +  P0/P x Points de C3 × (Note de P)/1)

Méthode "classique" = P0/P x Points du prix × 1/1+  Points de C2 × (Note de P)/1  +   Points de C3 × (Note de P)/1)

Méthode « avantages » = Méthode « classique » avec altération de la pondération de C2 et C3 du fait de l'application du coefficient P0/P

vous parlez sans arrêt d'altération de la pondération: pourtant le calcul de la note de l'offre fait bien intervenir les pondérations de chaque critère multipliés par leur note obtenue par l'offre; on trouve trois critères: le critère réalisation du cahier des charges (appelé critère prix) et les critères 2 et 3.
Effectivement, la pondération intervient mais elle n'est jamais la même pour C2 et C3 à cause de l'application du coefficient P0/P à la pondération initiale.

La seule particularité est que ce critère cahier des charges étant obligatoirement réalisé par les OE, il est donc noté 10/10.
La note du C1 n'est pas de 1, mais de P0/P x 1, à laquelle vous appliquez la pondération comme dans la méthode « classique ».

On obtient ainsi la note qualité de l'offre (ici 88 points), que l'on divise par son prix pour obtenir le rapport qualité/prix conformément à la règlementation. la multiplication par le prix le moins cher ne sert qu'à créer un barème de notation entre zéro et 100, ce qui paraît commode.
Le développement de votre formule démontre que les offres ne sont pas notées sur 100 points mais sur (60+20*(Po/P)+20*(Po/P)) points par conséquent seule l'offre la moins chère est notée sur 100.

Ce qui semble vous choquer, c'est de diviser aussi les critères 2 et 3 par le rapport des prix Po/P:  ceci vient de la volonté de calculer le vrai rapport qualité'prix et c'est le seul moyen d'y arriver!
Vous multipliez par Po/P. Ainsi, plus l'écart entre l'offre la moins chère et l'offre à noter est grand plus les critères techniques sont neutralisés, le critère prix devenant le critère unique de sélection

[hpchavaz]

Méthode "classique" = P0/P x Points du prix × 1/1+  Points de C2 × (Note de P)/1  +   Points de C3 × (Note de P)/1)

Méthode « avantages » = Méthode « classique » avec altération de la pondération de C2 et C3 du fait de l'application du coefficient P0/P

A vouloitr analyser une méthode en Q/P comme si elle était en P+Q, vous soulevez nécessairement des difficultés,(*).

La seule question à se poser est de savoir si une méthode en Q/P est conforme à la réglementation.


*) de façon similaire, je pourrais poser que par principe la notation doit être linéraire et ancrée a priori et j'en déduirais que la méthode classique dénature la pondérantion. Mais, même si j'en suis convaincu rien ne me permet de l'affirmer sauf peut être les difficultés que l'on recontre avec la méthode classique en cas d'offres variables.

[claude pomero]

La seule particularité est que ce critère cahier des charges étant obligatoirement réalisé par les OE, il est donc noté 10/10.
La note du C1 n'est pas de 1, mais de P0/P x 1, à laquelle vous appliquez la pondération comme dans la méthode « classique ».

-Vous confondez encore la note d'un critère et la méthode d'attribution du marché: la note du critère prix est bien de 1 (soit 10/10); la méthode d'attribution proposée est de diviser la note qualité globale de l'offre par son prix, et donc aussi la note donnée au critère "prix" qui en fait partie; elle est conforme à la règlementation qui demande d'utiliser le rapport Q/P pour trouver l'offre la plus avantageuse.

On obtient ainsi la note qualité de l'offre (ici 88 points), que l'on divise par son prix pour obtenir le rapport qualité/prix conformément à la règlementation. la multiplication par le prix le moins cher ne sert qu'à créer un barème de notation entre zéro et 100, ce qui paraît commode.
Le développement de votre formule démontre que les offres ne sont pas notées sur 100 points mais sur (60+20*(Po/P)+20*(Po/P)) points par conséquent seule l'offre la moins chère est notée sur 100.

-Les offres sont bien notées sur 100 points: c'est un BAREME de notation, qui va de 100 points (quand l'offre la plus chère obtient aussi les notes maximum aux critères qualité), jusqu'à zéro (pondération du prix nulle et aucun point obtenu aux critères qualité).  Les autres offres , soit plus chères soit moins bien notées, ne peuvent pas obtenir 100 points.

Ce qui semble vous choquer, c'est de diviser aussi les critères 2 et 3 par le rapport des prix Po/P:  ceci vient de la volonté de calculer le vrai rapport qualité'prix et c'est le seul moyen d'y arriver!
Vous multipliez par Po/P. Ainsi, plus l'écart entre l'offre la moins chère et l'offre à noter est grand plus les critères techniques sont neutralisés, le critère prix devenant le critère unique de sélection

c'est effectivement l'application du rapport qualité prix: si une offre est 30% supérieure à une autre, sa note qualité est multipliée par 1/1.30 =0.77. c'est ce qui permet de comparer des offres avec des mêmes valeurs de points de qualité (voir l'exemple de l'achat de bicyclette dans les documents précédents, où l'on voit bien que de ne pas appliquer une réduction à la note qualité mène à une impasse...).
Une offre à 100 €  qui obtient 50 points de qualité voit la valeur de chaque point de notation à 2 €;  une autre offre de 200 € qui obtient aussi 50 points de notation voit la valeur de chaque point à 4 €.
Les deux offres ont la même note qualité, pourtant on voit bien que la première est plus avantageuse...

Comment comparer les deux offres?
Il faut que la valeur du point soit la même: il faut donc multiplier la note qualité par le rapport Po/P, soit ici 100/200 = 0.5.
La valeur des 50 points  pour la deuxième offre sont donc ramenés à 25 €, qui perdra puisque la première est restée à 50 points...
C'est ce que propose la formule avantages.

[nb1987]

c'est effectivement l'application du rapport qualité prix: si une offre est 30% supérieure à une autre, sa note qualité est multipliée par 1/1.30 =0.77. c'est ce qui permet de comparer des offres avec des mêmes valeurs de points de qualité (voir l'exemple de l'achat de bicyclette dans les documents précédents, où l'on voit bien que de ne pas appliquer une réduction à la note qualité mène à une impasse...).

Ce n'est pas la note que vous réduisez mais ça pondération.

En effet, dans votre exemple de la bicyclette, le candidat B obtient une note prix de 0.01/10 pondérée à 40% et une note technique de 10/10 pondérée à 60%.

Toutefois, avec votre formule, vous indiquez qu'il a obtenu une note globale pondérée de 0.1/100.

Ce résultat est donné en prenant  40% de la note prix et (60*P0/P)% de la note technique au lieu de 60% comme annoncé à B. Dans cette situation la pondération du critère technique est clairement neutralisée.

En revanche, si vous estimez que c'est la note technique qui est affectée, cela veut dire que dans votre exemple le candidat B n'a pas comme vous l'écrivez 10/10 en valeur technique mais 0.01/10 (=10/10*P0/P).

Dans ce cas, la pondération n'est pas dénaturée mais il y a de grande chance pour que le JA considère que la méthode de notation utilisée a privé de sa portée le critère technique, puisque la meilleure offre technique se voit attribuer une note de 0.