Monsieur Excel
Pour tout savoir faire sur Excel !

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...

1 Commentaire(s):

  • Bonjour Monsieur Excel,

    J'ai testé la macro (je l'ai réécrite toute seule comme un grand !) et je note qu'il y a un décalage entre l'écriture dans le feuille et le calcul aléatoire. Peut-être le mieux serait de conserver les résultats dans un tableau VBA et d'affecter ses valeurs à la fin à la plage de données correspondante. C'est donc normal que vos tests donnent des résultats significatifs, pour parler un langage statistique puisqu'il y a plus de 20 itérations au cours de l’exécution de la macro ...
    Peut-être aussi au niveau de la macro, passer au mode XlcalculationManual en début de macro et revenir au mode Automatic ou Semi-Automatic à la fin de la macro. En tout cas, c'est ce que j'ai fait et les résultats sont aussi significatifs que les vôtres. Peut-être étiez-vous déjà en mode manuel ...

    Juste ma petite contribution !

    By Blogger Stefyu Thechosenone, sur 9:34 PM  

Enregistrer un commentaire

<< Accueil