Modèle de facturation (d)

Je vous ai laissés à la fin de l’article précédent avec la formule suivante pour la cellule I5 :

=rept("*";(H5-F5)/5)

Nous avons constaté qu’il y avait une erreur quand le stock disponible était supérieur au stock mini. Nous apprenons ainsi une chose de plus sur la fonction rept() : on ne peut pas répéter quoi que ce soit un nombre négatif de fois.

La première formule sérieuse pour I5 est donc la formule suivante :

=si(H5>F5;rept("*";(H5-F5)/5);"")

Les erreurs ont disparu, mais il reste un problème car le nombre d’étoiles n’est pas toujours le bon, il y a parfois une étoile manquante. Cela tient à ce que la fonction rept() prend la partie entière de l’argument. Il faut donc utiliser la formule suivante pour résoudre le problème :

=si(H5>F5;rept("*";(H5-F5+4)/5);"")

Voilà un cas intéressant ! Nous avons parfaitement bien résolu le problème en ajoutant 4/5, ce qui fait qu’une unité d’écart nous donnant 1/5, la somme des deux fera 1 (une étoile). Cette solution, parfaite sur le plan mathématique, nous pose un nouveau problème : que vous en penser les utilisateurs moins compétents qui ne comprendront pas l’astuce de cette solution ?

C’est un problème auquel, en tant que consultant, je suis souvent confronté. Il faut parfois choisir une solution moins efficace, et/ou moins jolie, mais que les utilisateurs auront plus de chance à comprendre. Voici donc une telle solution :

=si(H5>F5;rept("*";arrondi.sup((H5-F5)/5;0)); "")

Peut-on encore améliorer cette solution ? Certes, en remplaçant le si() par un max() : =rept("*";max(0; arrondi.sup((H5-F5)/5;0)))

Remarque – Quand j’audite les modèles de mes clients, j’ai souvent l’occasion d’améliorer leurs formules en remplaçant des si() par d’autres fonctions. Le record que j’ai vu, c’est un client qui utilisait une formule du genre =si(a>b;a;b), a et b étant des formules complexes. Alors qu’il suffisait d’écrire =max(a;b) …

En, conclusion, vous venons de voir quatre formules consécutives améliorant la formule originale qui, de plus, donnait parfois un résultat manquant d’une étoile... J’ai représenté en commentaire, sur la copie d’écran, les trois formules qui donnent le résultat attendu.

Modèle de facturation (c)

Voici la suite des instructions pour les formules des colonnes G et I :

• les stocks sont valorisés au prix de vente ;
• en colonne I, affichez un indicateur de l'urgence de commande : * s'il vous manque
  (Min stock - Stock) de 1 à 5 unités, ** pour 6 à 10, *** pour 11 à 15 unités...

En G5, une simple formule suffit pour calculer la valeur du stock au prix de vente. La voici :

=D5*F5

La fonction Rept

Pour la colonne I, c’est plus délicat, comme nous l’avons expliqué ci-dessus. A côté de l’énoncé (dans le fichier Excel que vous avez téléchargé), un conseil apparaît. Vous y découvrez que plusieurs étoiles sont affichées à l’aide de la formule =REPT("+";A39).

Si vous êtes comme la quasi-totalité des utilisateurs d’Excel, vous n’avez jamais entendu parler de cette fonction. Quand on découvre ainsi une nouvelle fonction, il est bon de la creuser un peu pour voir comment elle a été programmée. Vous ne savez pas a priori si l’argument peut ne pas être entier et – dans ce cas – s’il prend la partie entière de A39 ou s’il arrondit à l’entier le plus proche.

Entrez donc 3,2 en A39 et vous obtenez +++ en A40. C’est un premier point : les valeurs non entières sont acceptées. Entrez à présent 3,8 et vous constatez que le résultat n’a pas changé. En conclusion, la fonction REPT utilise donc la partie entière de l’argument.

Remarque – C’est en faisant ainsi des tests sur des fonctions d’Excel que je découvrais que j’ai pu apprendre à en tirer le maximum et – dans quelques cas (quand même rares) – à imaginer des utilisations pour ces fonctions qui n’étaient même pas documentées !

Certes, nous aurions aussi pu, et cela a probablement été votre intuition, créer une table avec les valeurs 0, 5, 10, … dans la première colonne et *, **, ***, … dans la seconde pour faire un recherchev() ou – mieux encore – une simple recherche() mais, grâce à la fonction rept(), cela n’est même plus nécessaire.

Une première formule pour la colonne I

Nous sommes à présent prêts pour entrer une première formule en I5. Suite à ce que nous venons de voir, et sachant qu’il faut obtenir une étoile pour chaque groupe de 5 unités manquantes, nous pouvons essayer la formule suivante :

=rept("*";(H5-F5)/5)

Cela donne une erreur mais, si nous reproduisons vers le bas, nous constatons qu’il n’y en a pas quand le stock est insuffisant. Et que, dans ce cas, on obtient toujours le résultat attendu, à une étoile près dans certains cas.

Nous verrons dans le prochain article diverses étapes pour améliorer cette formule…

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 !

Modèle de facturation (a)

En tant que consultant, je crée pour mes clients des modèles Excel en leur faisant du « cousu main ». Mais mon activité ne se limite pas à la création de modèles : je fais aussi de l’audit/amélioration de modèles existants. Les raisons possibles pour ce genre d’intervention sont multiples.

Réparation d’un modèle dont on a perdu le contrôle

Cela m’est arrivé pour une grande banque. Elle menait une série d’investissements routiers en association avec une banque italienne et une banque allemande. Des cadres des trois banques avaient chacun effectué des modifications sans se concerter avec leurs partenaires des deux autres pays.

On en était arrivé à ce que le classeur avait des liens avec des classeurs que personne ne savait localiser, et à ce qu’un certain nombre de formules affichent des erreurs que les cadres ne parvenaient pas à corriger. Le modèle était devenu totalement incontrôlable et je représentais la dernière chance pour le réparer.

Amélioration d’un modèle ne donnant pas satisfaction

J’ai eu avec deux clients, une grande société pharmaceutique et un fournisseur de téléphonie, le même genre de problème. Un modèle qui leur servait de façon quotidienne pour le suivi de gestion et était arrivé, à force d’enrichissements successifs aboutissant – par la force des choses – à alourdir progressivement le modèle, à devenir très lent en calcul.

Dans les deux cas, le temps ce calcul du modèle était de l’ordre de 2 minutes. Certes, me direz-vous, cela n’empêche pas l’utilisation du modèle. Mais il faut reconnaître que ce n’est pas pratique.

Dans les deux cas, je suis parvenu à diviser par 100 le temps de calcul, c’est-à-dire a obtenir le calcul en une seconde environ.

Réduction drastique du nombre d’onglets et/ou de classeurs

Pour plusieurs de mes clients, le nombre d’onglets d’un classeur a été réduit de façon drastique, ce qui bien évidemment apporte un gain de type win/win à l’ensemble du classeur : temps de calcul, taille mémoire, fiabilité,…

Pour une grande société pharmaceutique, j’ai réussi à réunir en un seul classeur tous les classeurs (plus de 150) utilisés au départ pour la gestion de son plan clinique.

Test sur un modèle concret

Dans cet esprit, je vous propose de voir, sur un exemple concret, comment on peut créer des formules originales puis, comme pourrait le faire un consultant, améliorer les formules pas à pas pour les rendre plus efficaces : plus justes, plus facilement auditables, plus compactes…

Il s’agit d’un mini-modèle de suivi de stocks et de facturation. Les explications sur l’objectif à atteindre sont fournies dans la colonne K.

Voici le lien pour charger le fichier :

https://drive.google.com/file/d/1V9mW-XKfkPoWFDuG97J_n2sm-1J66cZE/view