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 dans Excel. 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
La simulation que nous vous expliquerons dans cet article, repose sur un jeu de données. 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 donc répondre à la question à l’aide d’une simulation Monte-Carlo dans Excel. 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, pour élaborer une simulation Monte-Carlo dans Excel, sans l’aide du VBA, 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.
É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).
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.
Étape 3 : Conclusions de la simulation Monte-Carlo dans Excel
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.
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”.
Vous devez évaluer une entreprise, analyser un projet d’investissement et/ou analyser les états financiers d’une entreprise ? Maîtrisez les grands concepts et méthodes de calcul appropriées avec nos formations en finance corporative. |
|---|
..
Étape 4 : Nombre de roulements de dés
Dans un quatrième temps, notre simulation Monte-Carlon dans Excel exige 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).
É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 :
- 1ère : on inscrira les chiffres de 1 à 5000.
- 2e : dans le titre de la colonne, on ira chercher le résultat obtenu après 50 roulements de dés.
- 3e : 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.
Pour éviter d’utiliser la programmation VBA pour réaliser notre simulation Monte-Carlo dans Excel, 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 dans Excel, 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échargez gratuitement le fichier Excel pour vous pratiquer
Voyez le tout en action dans cette vidéo
Bon visionnement !
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
Bravo. C’est interessant. MErci