Monsieur Excel
Pour tout savoir faire sur Excel !

11 octobre 2015

Un modèle de pharmacie (b)

Reprenons le modèle de pharmacie présenté dans le dernier article. Nous avions défini 12 hypothèses, les cellules en fond vert de l’article précédent, et une prévision, la cellule B16, en fond bleu.

Ces fonds sont mis en couleurs automatiquement par Crystal Ball, et vous pouvez en modifier la couleur par défaut. Nous ne vous le conseillons pas : en effet, quand on échange avec d’autres utilisateurs des modèles Excel utilisant Crystal Ball, il est bien pratique de pouvoir identifier d’un coup d’œil les hypothèses et les prévisions.

Par défaut, Crystal Ball propose l’exécution de 5.000 itérations, nombre que vous pouvez aisément modifier avant le lancement de la simulation.

A titre indicatif, si vous essayez d’évaluer par simulation un pourcentage de l’ordre de 50%, l’écart-type est alors de 1/(2*racine(5000)). On a donc 95% de chances que le pourcentage p réel soit dans la fourchette (p – 1,43% ; p + 1,43%). En effet, avec la loi normale, qui s’applique à ce genre de situation, l’intervalle de probabilité à 95% se situe entre entre p-2s et p+2s).

Remarque – La plupart des journalistes vous racontent donc des craques quand il font quasiment des thèses de doctorat sur le fait qu’un politicien a baissé ou monté de 2 points dans les sondages. La plupart des sondages politiques sont basés sur 1.000 personnes, auquel car la fourchette d’évaluation à 95% d’un pourcentage p est (p-3,16% ; p+3,16%). La fourchette d’évaluation étant donc de plus de 6 points de pourcentage, on ne peut pas dire grand-chose de significatif sur une hausse ou une baisse de deux points !

La simulation entière, pour les 5.000 itérations, prend 5 secondes sur mon ordinateur. Autant dire que si j’avais développé un modèle sans Crystal Ball, avec ou sans VBA, j’en serais loin.  En outre, Crystal Ball vous fournit un graphe de prévision (cf. ci-dessous) ainsi que des statistiques de toutes sortes.


Avant qu’il n’existe des outils tels que Crystal Ball, de nombreuses entreprises, pour mieux appréhender des résultats aléatoires, se contentaient – il y en a encore qui ne font pas mieux aujourdh’ui ! – d’évaluer trois résultats : hypothèse basse – hypothèse probable – hypothèse haute.

Le graphe ci-dessus nous en apprend beaucoup plus ! Nous découvrons ainsi de façon très claire qu’il y a trois groupes de résultats. Le groupe le plus à droite (avec lui-même trois sous-groupes correspondant à l’année de sortie), celui qui se situe à droite de la moyenne, correspond au rang d’arrivée n°1 sur le marché.

Autre indication intéressante : il est de cas, comme celui-ci, où le résultat moyen a la particularité de ne correspondre à aucune réalité. Ici, la moyenne de 2,2 M€ n’est jamais atteinte par une observation… Sans simulation, il aurait été très difficile de percevoir la distribution des résultats affichée par le graphe de prévision !

4 Commentaire(s):

  • Ce commentaire a été supprimé par l'auteur.

    By Blogger medhi bourgeois, sur 8:20 AM  

  • Bonjour,

    En Planification Projet, le PMI définit également une règle de "hypothèse basse – hypothèse probable – hypothèse haute" pour définir la durée que l'on attribue à une activité(règle des 4 points : (HB+HH+4*HP)/6).
    Avez-vous déjà eu à travailler sur la mise en place d'un système statistique pour la détermination de durée dans des plannings ?

    By Anonymous Bourgeois_M, sur 8:22 AM  

  • Je n'ai pas travaillé dans ce cadre. Ceci dit, utiliser la formule (HB+HH+4*HP)/6) ne résout aucun problème de fond. Si on le fait, on continue à prendre des décisions sur 3 points seulement.

    Cette approche ne permet pas de bien appréhender la réalité et n'autorise aucune analyse de sensibilité.

    J'estime que la simulation probabiliste (avec ou sans Crystal Ball) est bien plus riche. J'ai aidé au moins 20 entreprises à développer des modèles avec Crystal Ball, et ce dans des domaines très variés. Pour n'en citer que quelques-unes avec lesquelles j'ai travaillé sur plusieurs projets : Aéroports de Paris, Arianespace, Caisse des dépôts, CNES, Electricité de France, France Telecom, MBDA, Sanofi.

    By Blogger Hervé Thiriez, sur 9:23 PM  

  • Je suis tout à fait d'accord avec vous, d'ou ma question car l’approche probabiliste est très intéressante mais difficile à mettre en place et je me posais la question si vous aviez réussi dans ce cadre particulier.
    En tout cas je vous remercie pour vos articles qui ne cessent de nous enrichir.

    By Anonymous Bourgeois_M, sur 9:52 PM  

Enregistrer un commentaire

<< Accueil