Simulation Monte Carlo dans Excel, pas à pas

Publié le 15 septembre 2014
par Sophie Marchand M.Sc., CPA, CGA, MVP
Simulation Monte Carlo

Simulation Monte Carlo dans Excel, pas à pas

Encore aujourd’hui, je souhaite survoler avec vous une question d’un ancien examen de ModelOff. Il s’agit d’une question portant sur les probabilités, à laquelle on doit répondre par le biais d’une simulation Monte Carlo. Cet article sert à décortiquer, pas à pas, la démarche suggérée par l’équipe de ModelOff. Si vous désirez en savoir plus sur les simulations Monte Carlo, dans un contexte d’affaires, inscrivez-vous sans tarder à notre formation Finance corporative – Analyse de projets d’investissements.

.

Question : Jeu de dés

Voici comment fonctionne le jeu de dés en question:

  • Le joueur brasse 3 dés (chaque dé a 6 côtés)
  • Si le total des 3 dés est de : 3, 4, 5, 16, 17 ou 18, le joueur perd
  • Si le total des 3 dés est de: 7 ou 11, le joueur gagne
  • Si le total est n’importe quel autre résultat, le joueur brasse les dés à nouveau
  • Lorsque le joueur brasse les dés à nouveau, le jeu se poursuit de la même façon, à l’exception que le joueur gagne dès que le total obtenu est identique au total obtenu au premier tour

On demande de répondre à la question à l’aide d’une simulation Monte Carlo.  De plus, il faut assumer que:

  • les dés sont brassés tout au plus 50 fois
  • 5000 résultats sont nécessaires pour préparer la simulation Monte Carlo

On recommande également d’utiliser une table de données pour générer les résultats.

.

Étape 1: 50 roulements de dés

Dans un premier temps, il s’agit d’élaborer une plage de données avec les résultats de chacun des 3 dés pour les 50 roulements de dés. Pour ce faire, on propose d’utiliser la fonction Alea(1,6) ou Randbetween(1,6). Ainsi, à chaque fois que l’on cliquera sur F9, on obtiendra de nouveaux résultats.

Simulation Monte Carlo

.

Étape 2 : Conclusions possibles

Dans un deuxième temps, il s’agit d’élaborer une plage de données pour identifier les résultats possibles aux 1er tour et aux tours suivants. On propose une plage de données à 3 colonnes. Dans la première colonne, on retrouve les chiffres 1 à 18. Ces chiffres représentent les résultats possibles, suite à un roulement de dés. Vous noterez que pour les chiffres 1 et 2, les conclusions sont N/A puisqu’il est impossible d’obtenir 1 ou 2 à l’aide de 3 dés! Dans la deuxième colonne, on inscrit les conclusions possibles après le premier tour. Tel que stipulé dans l’énoncé de départ, soit on gagne (Win), soit on perd (Lose) ou soit on rejoue (Reroll), en fonction du résultat obtenu (total des 3 dés).

Simulation Monte Carlo

Dans la 3ième colonne, on inscrit les conclusions possibles aux tours suivants. On peut obtenir ces résultats à l’aide d’une fonction Si ou If. Celle-ci fait en sorte que si le résultat obtenu aux tours suivants est équivalent au résultat obtenu au premier tour, on gagne, sinon, on est soumis aux règles du jeu initiales.

Simulation Monte Carlo

.

Étape 3: Conclusions obtenues

Dans un troisième temps, il s’agit d’identifier les conclusions des 50 roulements de dés. On peut obtenir la première conclusion, à l’aide d’une fonction Index. Cette fonction cherche dans les résultats possibles du 1er tour, la conclusion correspondant au résultat obtenu. Par exemple, lorsqu’on obtient 6, comme c’est le cas dans l’image ci-bas, et bien on rejoue.

Simulation Monte Carlo

.

On peut obtenir les conclusions des autres roulements de dés, à l’aide d’une fonction Ou ou Or et d’une fonction Index, imbriquées dans une fonction Si ou If. Cette fonction dit à Excel: “Si le résultat précédent est “Win” ou “Loose”, remettre le même résultat (car une fois qu’on a gagné, on a définitivement gagné et une fois qu’on a perdu, on a définitivement perdu!), sinon, se rendre dans la colonne des conclusions possibles aux tours suivants et identifier la conclusion du résultat obtenu”.

Simulation Monte Carlo

..

Étape 4 : Nombre de roulements de dés

Dans un quatrième temps, il s’agit de déterminer le nombre de roulements de dés nécessaires avant de perdre ou de gagner. Pour ce faire, on peut utiliser une fonction Nb.si ou Countif, qui demande à Excel de faire le décompte des résultats “rejouer” et d’additionner 1. On additionne 1, puisque c’est au tour suivant, qu’on obtiendra un résultat final (victoire ou défaite).

Simulation Monte Carlo

.

Étape 5: Zone de simulation

Dans un cinquième temps, on propose d’élaborer une zone pour retracer les résultats des différentes simulations. Pour ce faire, on créera 3 colonnes. Dans la première colonne, on inscrira les chiffres de 1 à 5000. Dans la deuxième colonne, dans le titre de la colonne, on ira chercher le résultat obtenu après 50 roulements de dés. Dans la troisième colonne, dans le titre de la colonne, on ira chercher le nombre de roulements de dés nécessaires avant d’obtenir un statut (victoire ou défaite), soit la cellule c10, présentée plus haut.

Simulation Monte Carlo

Ensuite, il s’agira de créer une table de sensibilité à l’aide de la fonctionnalité Table de données ou Data table (on insérera cette table de sensibilité dans la deuxième et la troisième colonnes). Si vous avez besoin de réviser le fonctionnement de la fonctionnalité Table de données ou Data table, je vous invite à relire l’article Analyses de sensibilité avec Excel. Dans cette anlayse de sensibilité, on indiquera que les chifres de 1 à 5000 (soit les données en colonne), doivent, tour à tour, être insérés dans la cellule A1 du fichier. En réalité, on pourrait choisir n’importe quelle cellule vide. L’idée est simplement de forcer un recalcul à chaque fois et ainsi obtenir de nouveaux roulements de dés (résultats de nouvelles simulations), sans abîmer les formules en place.

.

Étape 6: Probabilités

Dans un sixième temps, on pourra finalement calculer les probabilités de gagner et de perdre. On propose de le faire à partir de la fonction Nb.si ou Countif. La formule fait le décompte des “win” et des “loose” et les divise par 5000, pour obtenir la proportion respective de l’un et de l’autre.

Simulation Monte Carlo

.

Simulation Monte Carlo dans un contexte d’affaires

 Si vous souhaitez développer vos compétences en simulations Monte Carlo dans un contexte d’affaires, je vous invite à vous inscrire à notre formation Finance corporative: Analyse de projets d’investissement.

.

Télécharger le fichier: Simulation Monte Carlo.

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

1 réflexion sur “Simulation Monte Carlo dans Excel, pas à pas”

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top