Création d’une simulation Monte Carlo à l’aide d’Excel

Une simulation de Monte Carlo peut être développée en utilisant Microsoft Excel et un jeu de dés. La simulation de Monte Carlo est une méthode mathématique numérique qui utilise des tirages au sort pour effectuer des calculs et résoudre des problèmes complexes. Aujourd’hui, elle est largement utilisée et joue un rôle clé dans divers domaines tels que la finance, la physique, la chimie et l’économie.

Simulation de Monte Carlo

La méthode de Monte Carlo a été inventée par John von Neumann et Stanislaw Ulam dans les années 1940 et cherche à résoudre des problèmes complexes en utilisant des méthodes aléatoires et probabilistes. Le terme Monte Carlo désigne la zone administrative de Monaco, connue de tous comme un lieu où les élites européennes jouent.

La méthode de simulation de Monte Carlo calcule les probabilités pour les intégrales et résout les équations différentielles partielles, introduisant ainsi une approche statistique du risque dans une décision probabiliste. Bien qu’il existe de nombreux outils statistiques avancés pour créer des simulations de Monte Carlo, il est plus facile de simuler la loi normale et la loi uniforme à l’aide de Microsoft Excel et de contourner les fondements mathématiques.

Quand utiliser la simulation de Monte Carlo

Nous utilisons la méthode de Monte Carlo lorsqu’un problème est trop complexe et difficile à résoudre par calcul direct. L’utilisation de la simulation peut aider à apporter des solutions à des situations qui s’avèrent incertaines. Un grand nombre d’itérations permet de simuler la distribution normale. Elle peut également être utilisée pour comprendre comment le risque fonctionne et pour appréhender l’incertitude des modèles de prévision.

Comme indiqué ci-dessus, la simulation est souvent utilisée dans de nombreuses disciplines différentes, notamment la finance, les sciences, l’ingénierie et la gestion de la chaîne d’approvisionnement, en particulier dans les cas où il y a beaucoup trop de variables aléatoires en jeu. Par exemple, les analystes peuvent utiliser les simulations de Monte Carlo pour évaluer les produits dérivés, y compris les options, ou pour déterminer les risques, notamment la probabilité qu’une entreprise ne puisse pas honorer ses dettes.

Jeu de dés

Pour la simulation de Monte Carlo, nous isolons un certain nombre de variables clés qui contrôlent et décrivent le résultat de l’expérience, puis nous attribuons une distribution de probabilité après avoir effectué un grand nombre d’échantillons aléatoires. Afin de démontrer, prenons un jeu de dés comme modèle. Voici comment se déroule le jeu de dés :

– Le joueur lance trois fois trois dés qui ont six faces.

– Si le total des trois lancers est de 7 ou 11, le joueur gagne.

– Si le total des trois lancers est : trois, quatre, cinq, 16, 17 ou 18, le joueur perd.

– Si le total est différent, le joueur joue à nouveau et relance les dés.

– Lorsque le joueur lance à nouveau les dés, le jeu continue de la même manière, sauf que le joueur gagne lorsque le total est égal à la somme déterminée au premier tour.

Il est également recommandé d’utiliser un tableau de données pour générer les résultats. De plus, 5 000 résultats sont nécessaires pour préparer la simulation de Monte Carlo.

Étape 1 : Événements du lancer de dés

Tout d’abord, nous développons une série de données avec les résultats de chacun des trois dés pour 50 lancers. Pour ce faire, il est proposé d’utiliser la fonction « RANDBETWEEN(1,6) ». Ainsi, chaque fois que nous cliquons sur F9, nous générons un nouvel ensemble de résultats de lancer. La cellule « Résultat » est la somme totale des résultats des trois lancements.

Étape 2 : Gamme de résultats

Ensuite, nous devons élaborer une série de données pour identifier les résultats possibles du premier cycle et des cycles suivants. Il existe une gamme de données en trois colonnes. Dans la première colonne, nous avons les chiffres de 1 à 18. Ces chiffres représentent les résultats possibles après avoir lancé les dés trois fois : Le maximum étant 3 x 6 = 18. Vous noterez que pour les cellules un et deux, les résultats sont N/A puisqu’il est impossible d’obtenir un un ou un deux en utilisant trois dés. Le minimum est de trois.

Dans la deuxième colonne, les conclusions possibles après le premier tour sont incluses. Comme indiqué dans la déclaration initiale, soit le joueur gagne (Win) ou perd (Lose), soit il rejoue (Re-roll), en fonction du résultat (le total des trois dés lancés).

Dans la troisième colonne, les conclusions possibles des cycles suivants sont enregistrées. Nous pouvons obtenir ces résultats en utilisant la fonction « IF ». Celle-ci garantit que si le résultat obtenu est équivalent au résultat obtenu au premier tour, nous gagnons, sinon nous suivons les règles initiales du jeu original pour déterminer si nous relançons les dés.

Étape 3 : Conclusions

Dans cette étape, nous identifions le résultat des 50 dés lancés. La première conclusion peut être obtenue à l’aide d’une fonction d’index. Cette fonction recherche les résultats possibles du premier tour, la conclusion correspondant au résultat obtenu. Par exemple, lorsque nous lançons un six, nous jouons à nouveau.

On peut obtenir les résultats d’autres jets de dés, en utilisant une fonction « OR » et une fonction d’index imbriquée dans une fonction « IF ». Cette fonction indique à Excel : « Si le résultat précédent est Gagnant ou Perdant », arrêtez de lancer les dés car une fois que nous avons gagné ou perdu, c’est fini. Sinon, nous passons à la colonne des conclusions possibles suivantes et nous identifions la conclusion du résultat.

Étape 4 : Nombre de rouleaux de dés

Maintenant, nous déterminons le nombre de jets de dés nécessaires avant de perdre ou de gagner. Pour ce faire, nous pouvons utiliser une fonction « COUNTIF », qui nécessite qu’Excel compte les résultats du « Re-roll » et y ajoute le chiffre un. Il ajoute un parce que nous avons un tour supplémentaire, et nous obtenons un résultat final (victoire ou défaite).

Étape 5 : Simulation

Nous développons une gamme permettant de suivre les résultats de différentes simulations. Pour ce faire, nous allons créer trois colonnes. Dans la première colonne, un des chiffres inclus est 5 000. Dans la deuxième colonne, nous chercherons le résultat après 50 lancers de dés. Dans la troisième colonne, le titre de la colonne, nous chercherons le nombre de jets de dés avant d’obtenir le statut final (victoire ou défaite).

Ensuite, nous créerons un tableau d’analyse de sensibilité en utilisant les données de caractéristiques ou le tableau des données de tableau (cette sensibilité sera insérée dans le deuxième tableau et la troisième colonne). Dans cette analyse de sensibilité, les nombres d’événements de un à 5 000 doivent être insérés dans la cellule A1 du fichier. En fait, on peut choisir n’importe quelle cellule vide. L’idée est simplement de forcer un recalcul à chaque fois et d’obtenir ainsi de nouveaux jets de dés (résultats de nouvelles simulations) sans endommager les formules en place.

Étape 6 : Probabilité

Nous pouvons enfin calculer les probabilités de gagner et de perdre. Pour ce faire, nous utilisons la fonction « COUNTIF ». La formule compte le nombre de « gagnants » et de « perdants » puis divise par le nombre total d’événements, 5 000, pour obtenir la proportion respective de l’un et de l’autre. On voit finalement que la probabilité d’obtenir un résultat « Gagnant » est de 73,2% et celle d’obtenir un résultat « Perdant » est donc de 26,8%.