Monsieur Excel
Pour tout savoir faire sur Excel !

23 octobre 2017

Mes formations en décembre

Vous trouverez ci-dessous la liste des formations que je propose pour décembre 2017. Chacune de ces formations est limitée à huit participants, chaque participant venant avec son ordinateur.

  • Modélisation avec Excel (2 jours) : les mardis 12 et 19 décembre 2017.
  • Création de tableau de bord sous Excel : le jeudi 14 décembre 2017.
  • Modélisation du risque : le jeudi 21 décembre 2017.
Vous trouverez la description détaillée de ces formations en cliquant dans le ruban droit, dans le gros bouton « Prochaines formations ».

Les autres séminaires habituels ne sont pas proposés en inter lors de cette session. Toutes les formations proposées sont organisées en association avec la société EuroDécision.

Avec la formation « Modélisation avec Excel », je garantis de transformer en deux jours tout utilisateur moyen d'Excel en « power user », c'est-à-dire à l'amener au niveau des 5% des meilleurs utilisateurs d'Excel.

Avec la formation « Modélisation du risque », vous découvrirez comment utiliser Excel pour faire des simulations probabilistes (dites de Monte Carlo). Nous utiliserons à cet effet l’add-in Crystal Ball, le meilleur produit au monde dans cette catégorie.

La formation « Création de tableau de bord sous Excel » vous permettra, en une seule journée, de totalement maîtriser la création d’un tableau de bord personnalisé, comme vous pouvez le voir dans l’article
« Notre premier tableau de bord » du 22 mai 2013.

Chacune de mes formations peut être animée en intra dans votre entreprise et – le cas échéant – personnalisée grâce à l’analyse et à l’amélioration des modèles propres à votre entreprise.

Ne manquez pas cette occasion de découvrir tout cela de la bouche même de l’auteur de ce blog, qui partagera avec vous l’expérience qu'il acquise en développant plus de 1.000 modèles dans plus de 100 entreprises en 10 pays.

Quelques-unes de mes références de formation intra : Aéroports de Paris, Aérospatiale, Arianespace, Bouygues, Caisse des dépôts, CASE-Poclain, CCIP, Cegelec, CNES, CNET, EADS, EdF, Elf, Ernst & Young, Euroconsult, Finacor, France Telecom, Gaz de France, GIAT, IFP, Isochem, Lafarge, La Poste, Lilly France, Marsh, RTE, Sanofi, SIRIS, Texas Instruments, Tir Groupé, Total, Wabco, Walt Disney.

16 octobre 2017

Une étude de cas – Conclusion

Nous avons démarré le 22 août dernier une série d’articles sur le « Cas n°1 ». En fait, il s’agit d’une séquence relative à un petit problème d’optimisation de prix de vente qui nous a servi de véhicule pour vous présenter différentes façons de résoudre un problème d’optimisation.

Le 8 septembre, nous avons vu comment résoudre le problème par une recherche dichotomique, une méthode qui permet de converger rapidement vers la solution optimale.

Le 14 septembre, nous avons vu comment résoudre le problème en utilisant le solveur, un add-in d’optimisation fourni avec Excel mais qui n’est pas installé par défaut. Il faut donc l’installer en passant par la séquence FichierOptionsCompléments. Avec le solveur, on trouve bien sûr immédiatement le prix de vente optimal.

Ensuite, nous avons résolu le problème en utilisant des tables à 1 dimension (20 septembre) puis à 2 dimensions (27 septembre et 3 octobre).

Enfin,  le 9 octobre, nous avons procédé par simulation en tirant simplement au hasard une série de prix de départ pour voir lequel donnait la meilleure marge bénéficiaire. Ce qui est surprenant, c’est que cette approche qui n’exerce aucune intelligence, dans la mesure ou chaque nouvelle itération ne tient aucun cas des résultats des itérations antérieures, donne contre toute attente d’excellents résultats.

Cette série d’articles a été l’occasion pour nous de voir (ou revoir) un certain nombre d’outils de modélisation auquel Excel vous donne accès et – par la même occasion – de constater qu’il y a souvent dans Excel une grande variété d’approches possibles pour résoudre un problème donné.

Pour télécharger le fichier de cette série d’articles :

09 octobre 2017

Cas n°1 – Simulation

Nous avons vu dans les articles précédents plusieurs façons d’identifier le prix de vente qui maximisera la marge bénéficiaire. Il reste une approche tout à faire originale pour résoudre ce type de problème, et qui donne des résultats d’une qualité surprenante.

J’ai donc écit une petite macro qui tire 20 fois au hasard un prix de vente entre 100 € et 150  € et copie en colonnes E et F le prix de vente ainsi tiré au hasard et la marge bénéficiaire correspondante.

En F21, on identifie avec =max(F1:F20) le meilleur bénéfice des 20 tirages et, en E21, le prix de vente correspondant avec =index(E:E;equiv(F21;F:F;0)).

J’ai reporté en I21 et J21 le prix optimal et le bénéfice maximal. En H21, la formule =abs(J21-F21)/J21 calcule l’écart entre le meilleur bénéfice des 20 tirages et le bénéfice optimal.


Ce qui est vraiment surprenant, c’est qu’avec seulement 20 tirages totalement aléatoires, on parvienne à d’aussi bons résultats.

Ainsi, en faisant 10 exécutions consécutives de la simulation, le pire résultat avait un écart de seulement 0,30%, le second le plus mauvais de 0,12%, le troisième de 0,09% et les sept autres de 0,06% ou moins !

Un résultat impressionnant pour seulement 20 tirages au hasard, n’est-ce pas !

Certes, d'autres séries de 10 donnent des résultats un peu moins brillants, mais ils sont toujours excellents considérant qu'il n'y a que 20 itérations...

03 octobre 2017

Cas n°1 – Table de données (c)

Nous avons remarqué dans le dernier article que la table de données à 2 dimensions permettait de faire varier deux paramètres, mais – en revanche – ne pouvait être utilisée que pour analyser les valeurs d’un résultat.

Il y a une façon très esthétique de contourner cette limitation en utilisant une fonction assez peu connue des utilisateurs lambda, la fonction choisir().

La fonction choisir()

La syntaxe de cette fonction est =choisir(index;formule_1;formule_2 ;…).

index est un nombre allant de 1 à 254 ou une formule aboutissant à un tel nombre.

Quand index est égal à 1, le résultat est celui de formule_1 ; quand il vaut 2, c’est celui de formule_2, et ainsi de suite.

Je n’ai encore jamais testé cette fonction avec 254 arguments, et je ne suis pas sûr qu’Excel vous laisse saisir une formule de cette longueur. Si vous l’avez déjà tenté, n’hésitez pas à ajouter un commentaire en nous disant ce qui vous est arrivé…

Mise en place de la solution

Insérons une ligne au-dessus de la ligne 20, pour aérer un peu et créons en A20 une zone de validaton par liste à partir de $A$14:$A$18. Cela nous permet donc de sélectionner Ventes prévues, Coût variable total, Chiffre d'affaires, Coût produits vendus ou Marge bénéficiaire.

Il suffit alors d’entrer en A21 la formule =recherchev(A20;A14:B18;2;faux).

Nous avons donc à présent une table capable de calculer les résultats de cinq formules différentes en fonction de deux paramètres !


Remarque – Reste à résoudre un problème esthétique. Quand nous sélectionnons « Ventes prévues »,  les quantités vendues sont formatées en € !

Pour résoudre ce problème, il suffit d’utiliser le format personnalisé suivant pour l’intérieur de la table de données : [>10000]# ##0 €;# ##0. En effet, toutes les valeurs en € sont supérieures à 10.000 !