Comment créer un tableau de comparaison des offres ?

Dans cet exercice je vais vous montrer comment établir un tableau de comparaison des offres, en se basant sur les réponses des fournisseurs à un appel d'offres lancé par un hôtel qui désire renouveler les chaises de ses 150 chambres.

Ce tableau de comparaison des offres va permettre à notre hôtel de sélectionner le fournisseur qui propose le prix le moins cher. Ce fournisseur doit aussi respecter le critère de livrer la commande dans un délai qui ne dépasse pas les 30 jours.

Tout d’abord, j’ai préparé ce fichier (que vous pouvez télécharger ici) dans lequel vous pouvez voir, en plus de mon tableau de comparaison des offres, une petite partie intitulée Besoins et critères concernant ce que demande l’hôtel.

Et dans le tableau qui se trouve en dessous et qui porte le nom de Réponses des fournisseurs, j'ai saisi les données collectées des réponses des fournisseurs reçues par l’hôtel. Ces données vont me servir à remplir mon tableau de comparaison des offres et d’en faire une étude comparative afin de sélectionner le fournisseur qui présente la meilleure offre pour mon hôtel.

Entamons le remplissage de notre tableau de comparaison des offres :

Pour le prix unitaire, vous pouvez soit copier les prix à partir du tableau Réponses des fournisseurs soit créer une petite formule qui va introduire ces valeurs d’une façon automatique.

Sélectionnez donc les trois cellules Prix unitaire puis tapez =G9, validez ensuite par Ctrl+Entrée.

Calculer le montant total HT :

Le montant total = le prix unitaire x le nombre de chaises

De la même manière et pour insérer cette formule dans les trois cellules du Montant Total HT, sélectionnez ces dernières puis saisissez =B6*$G$3, validez enfin par Crtl+Entrée.

Remarque : N’oubliez pas de figer la cellule G3 qui contient le nombre de chaises.

Insérer le taux de remise

Vous remarquez sans doute que dans le tableau des réponses des fournisseurs, le fournisseur F2 ne fournit aucune remise, alors que les deux autres proposent leurs remises mais suivant quelques conditions :

• Pour le fournisseur F1 :

La remise de 6%  est applicable à partir de 100 chaises commandées, ce que nous allons traduire sous forme d’une formule utilisant la fonction SI :

- Sélectionnez donc la cellule B8 puis tapez cette formule : =SI(G3>=G11;G10;"")

- G3 contient le nombre de chaises commandées et qui est supérieur à 100 déjà saisi dans G11.

- Dans ce cas, Excel va afficher alors 6% se trouvant dans la cellule G10.

• Pour le fournisseur F2 :

Il propose une remise qui varie en fonction du nombre de chaises commandées :

5% à partir de 80 chaises et 10% si la quantité passe à 100 chaises ou plus.

Voici la formule à insérer dans la cellule D8 :

=SI(G3>=I13;I12;SI(G3>=I11;I10;""))

J’explique :

Si le nombre de chaises contenu dans G3 est supérieur ou égal à 100 se trouvant dans I13, le taux 10% sera affiché, sinon, on demande à Excel de vérifier si cette quantité commandée est supérieure ou égale à 80 que contient I11 et d’afficher donc 5% se trouvant dans I10.

Si aucune condition n’est respectée, alors Excel n’affichera rien.

Le nombre de chaises dépasse 100, alors le taux 10% est affiché.

Calculer le montant de remise :

Le montant de remise = le montant total x le taux de remise

Sélectionnez les trois cellules B9, C9 et D9 puis tapez  =B7*B8 et validez par Ctrl+Entrée.

Note : appliquez le format monétaire ou comptabilité pour mettre en forme les montants calculés.

Calculer le Net commercial

C’est le montant total HT – le montant de remise

Sélectionnez la plage de cellules B10:D10 puis tapez ceci =B7-B9 et validez ensuite par Ctrl+Entrée.

Insérer le taux d’escompte

Copiez tout simplement les valeurs à partir du tableau des réponses des fournisseurs ou sélectionnez les trois cellules de B11 à D11 puis tapez =G14 et validez par Ctrl+Entrée.

Calculer le montant d’escompte

Il égale à : Net commercial x taux d’escompte

Pour faire rapidement comme d’habitudes, sélectionnez la plage de cellules B12:D12 puis saisissez la formule suivante : =B10*B11, validez ensuite par Ctrl+Entrée

Calculer le Net financier

le Net financier= Net commercial - montant d’escompte

Sélectionnez les cellules du Net financier puis insérez cette formule =B10-B12, validez ensuite par Ctrl+Entrée.

Saisir le frais de transport :

Le fournisseur F2 propose de livrer la marchandise gratuitement contrairement aux deux autres.

Copiez donc le frais correspondant au fournisseur F1, quant au fournisseur F3 vous avez besoin de calculer ce frais en fonction du pourcentage qu’il précise (1% du Net commercial).

Pour cela, sélectionnez la cellule D14 et saisissez la formule suivante : =D10*1%

Calculer le Net HT

le Net HT = le Net financier + frais de livraison

Sélectionnez donc les trois cellules qui vont afficher le Net HT puis saisissez la formule =SOMME(B13:B14), validez ensuite par Ctrl+Entrée.

Insérer les données du délai de livraison et de la garantie

Sélectionnez la plage de cellules B16:D17 et insérez cette petite formule =G16 puis validez par Ctrl+Entrée.

Si vous souhaitez afficher les nombres des jours suivis du mot "jours", procédez comme suit :

• Sélectionnez les cellules contenant ces nombres puis cliquez sur la flèche située dans le coin inférieur droit du groupe Nombre.
• Dans la fenêtre qui apparaît cliquez sur Personnalisée
• Dans la zone Type tapez 0" jours" et cliquez enfin sur le bouton OK.

Suivez la même démarche si vous souhaitez également afficher le mot « mois » devant le nombre de mois de la garantie.

Choisir le fournisseur approprié

Pour ce faire, nous allons analyser ces deux données essentielles : Net HT et Délai de livraison.

Pour notre hôtel, ce qui est favorable pour lui c’est :

• Le Net HT le moins élevé,
• Un délai de livraison le plus bref qui ne dépasse pas les 30 jours prédéfinis.

Comme vous pouvez le remarquez, bien entendu, et d’après les résultats obtenus, le fournisseur F1 la ramène parce que son offre est la moins chère (30 629,70 Euros) , en plus, il est le seul qui propose de livrer la commande dans un délai plus rapide  (15 jours ) par rapport aux autres fournisseurs.

Pour traduire ce travail sous Excel, vous pouvez procéder ainsi :

• Afficher le nom du fournisseur proposant le prix le plus petit :

Sélectionnez la cellule B19, puis tapez la formule suivante :

=SI(B15=MIN($B$15:$D$15);B5;"")

Premièrement Excel va vérifier si le montant Net HT du Fournisseur F1 égale au montant le plus petit de ceux obtenus, si c’est le cas ; il affiche le nom de ce fournisseur contenu dans la cellule B5, sinon Excel n’affichera rien.

Puis on copie la formule dans les deux cellules C19 et D19.

• Afficher le nom du fournisseur qui va livrer la commande rapidement :

Sélectionnez la cellule B20, et tapez la formule suivante :

=SI(ET(B16<=$G$4;B16=MIN($B$16:$D$16));B5;"")

Dans cette formule, nous avons deux conditions à vérifier :

Le délai de livraison ne doit pas dépasser les 30 jours (B16<=$G$4) et s'il est le plus petit parmi les trois proposés par nos fournisseurs (B16=MIN($B$16:$D$16)).

J’ai utilisé dans ma formule la fonction ET pour tester ces deux conditions.

Si les deux conditions sont respectées, Excel affichera le nom du fournisseur F1 qui se trouve dans la cellule B5, sinon il n’affichera rien.

Copiez la formule dans les deux autres cellules C20 et D20.

D’après les résultats obtenus, j’arrive à décider que le fournisseur F1 est celui que l’hôtel va choisir pour lui passer la commande.

Voilà donc, vous avez suivi comment réaliser un tableau de comparaisons des offres sous Excel, en vous basant sur un exemple d’un hôtel qui a voulu commander 150 chaises pour ses 150 chambres selon des critères précis, et vous avez vu comment faire pour arriver à choisir parmi trois fournisseurs celui le plus approprié. 

Bien évidemment, il existe d'autres exemples de choix de fournisseurs tel que le choix effectué à l'aide de critères pondérés.

PARTAGEZ PARTAGEZ PARTAGEZ