Modèle de facturation (b)

Voici l’explication sur la façon dont on doit calculer le prix de vente :

• le prix de vente est le prix de base, multiplié par l'index fournisseur (table B1:C2), plus une marge de 40% :
• il vaut donc 1,4*Base*Index fournisseur.

Ce que je vous propose, dans cet article, c’est de vous montrer une première formule en D5, la plus simple que nous puissions entrer pour répondre à la question. La voici :

=1,4*C5*si(E5=1;$C$1;$C$2)

Avant de lire la suite de cet article, réfléchissez bien et essayez d’identifier ce qui est la pire chose dans cette formule, et éventuellement la seconde chose la plus mauvaise.

En fait, nous allons procéder dans cet article comme un expert qui analyse la formule et essaye de l’améliorer étape par étape. Pour cela, il faut bien évidemment commencer par s’attaquer au problème le plus grave, d’où ma question.

Une première amélioration de la formule

Si vous avez répondu « C’est le fait que la formule ne marche qu’avec deux fournisseurs ! », vous aurez certes donné la réponse que j’entends le plus souvent lors de mes formations, mais ce n’est pas la bonne…

Le problème le plus grave provient du début de la formule : le 1,4 est ce que l’on appelle une constante cachée. Cela entraîne deux problèmes. Le premier est que l’utilisateur qui voit le 32,55 en D5 ne peut pas comprendre comment ce résultat a été obtenu s’il ne voit pas la formule. Le second est que, si l’on est amené à modifier la marge, par exemple parce que la concurrence devient plus sévère, il faudra modifier la formule.

La solution consiste à « externaliser » la marge, en entrant « Marge » en G1 et 40% en G2.

La formule de D5 devient : =(1+$G$2)*C5*si(E5=1;$C$1;$C$2).

Remarque – On peut noter que, pour améliorer l’auditabilité du modèle, il serait intelligent d’attribuer le nom Marge à G2 et de remplacer $G$2 dans la formule par Marge.

Seconde amélioration : fournisseurs supplémentaires

Je n’ai eu aucune hésitation pour identifier ce qu’il fallait faire pour la première amélioration, mais je suis un peu plus partagé pour ce qui est la seconde amélioration nécessaire. Ceci dit, la limitation par le SI() à deux fournisseurs n’est pas bonne, ce qui nous amène à la troisième formule :

=(1+$G$2)*C5*index($C$1:$C$2,E5)

Troisième amélioration : des factures exactes

Calculez dans une cellule combien seront facturées 100 unités du premier article. Vous constatez que vous obtenez 3.554,89 € au lieu des 3.555 € attendus.

C’est bien gênant car, ce modèle servant à produire des factures, il se doit d’être irréprochable. Il nous faut donc arrondir le prix de base. Selon votre gourmandise ou votre sens commercial, vous avez alors le choix pour cela entre Arrondi(), Arrondi.Sup et Arrondi.Inf.

Voici la formule : =arrondi((1+$G$2)*C5*index($C$1:$C$2,E5);2)

Une dernière amélioration…

Supposons que nous insérions deux lignes en dessous de la ligne 2 pour prendre en compte deux nouveaux fournisseurs. Nous constatons alors que les deux nouveaux fournisseurs ne sont pas pris en compte dans le tableau $C$1:$C$2.

La solution consiste à utiliser – dans le tableau original, donc avant l’insertion des deux lignes – la formule suivante qui ajoute une ligne banche en bas de la table :

=arrondi((1+$G$2)*C5*index($C$1:$C$3,E5);2)

Du coup, on peut insérer autant de nouveaux fournisseurs que l’on veut, la formule sera toujours bonne !

En guise de conclusion

Ce qui est impressionnant, c’est que – pour cette seule première formule – nous sommes parvenus à définir cinq formules avec quatre améliorations successives !