Passer directement au contenu principal

Présentation de la simulation de Monte-Carlo dans Excel

Remarque : Nous faisons de notre mieux pour vous fournir le contenu d’aide le plus récent aussi rapidement que possible dans votre langue. Cette page a été traduite automatiquement et peut donc contenir des erreurs grammaticales ou des imprécisions. Notre objectif est de faire en sorte que ce contenu vous soit utile. Pouvez-vous nous indiquer en bas de page si ces informations vous ont aidé ? Voici l’article en anglais à des fins de référence aisée.

Cet article a été adapté de l' analyse des données et de la modélisation d'entreprise Microsoft Excel par Wayne L. Winston.

  • Qui utilise la simulation Monte Carlo?

  • Que se passe-t-il lorsque vous tapez = Alea () dans une cellule?

  • Comment simuler des valeurs d'une variable aléatoire discrète?

  • Comment simuler des valeurs d'une variable aléatoire normale?

  • Comment une société de carte de vœux peut-elle déterminer le nombre de cartes à produire?

Nous aimerions évaluer de manière précise les probabilités d'événement incertain. Par exemple, quelle est la probabilité que les flux de trésorerie d'un nouveau produit aient une valeur actuelle nette (Van)? Quel est le facteur de risque de notre portefeuille d'investissements? La simulation de Monte Carlo nous permet de modéliser des situations présentant une incertitude et de les lire sur un ordinateur à des milliers de fois.

Remarque :  Le nom de la simulation de Monte Carlo provient des simulations informatiques effectuées lors du 1930s et de 1940s pour évaluer la probabilité que la réaction de la chaîne nécessaire pour une bombe atomique fonctionne correctement. Le Physicists impliqué dans ce poste a été un grand éventail de jeux de hasard, donc il a donné à la simulation le nom de code Monte Carlo.

Dans les cinq prochains chapitres, vous trouverez des exemples de la façon dont vous pouvez utiliser Excel pour effectuer des simulations de Monte-Carlo.

De nombreuses sociétés utilisent la simulation Monte Carlo comme partie importante du processus décisionnel. Voici quelques exemples.

  • Les moteurs généraux, les proctors et les risques, Pfizer, Bristol-Myers Squibb et Eli Lilly utilisent la simulation pour évaluer à la fois le rendement moyen et le facteur de risque de nouveaux produits. Sur GM, ces informations sont utilisées par le PDG pour déterminer quels produits sont disponibles sur le marché.

  • La fonction GM utilise la simulation pour les activités telles que la prévision du revenu net de la société, la prévision des coûts structurels et d'achat, ainsi que sa sensibilité à différentes sortes de risques (par exemple, le taux d'intérêt et les fluctuations des taux de change).

  • Lilly utilise la simulation pour déterminer la capacité idéale pour chaque médicament.

  • Proctor et risque utilise la simulation pour modéliser et assurer une couverture optimale des risques d'échange.

  • Sears utilise la simulation pour déterminer le nombre d'unités de chaque ligne de produit qui doivent être classées auprès des fournisseurs (par exemple, le nombre de paires de pantalon d'arrimeurs qui doivent être commandées cette année).

  • Les entreprises d'huile et de drogues utilisent la simulation pour évaluer les «options réelles», comme la valeur d'une option de développement, de contrat ou de report d'un projet.

  • Les planificateurs financiers utilisent la simulation Monte Carlo pour déterminer les stratégies d'investissement optimales pour la mise hors service de leurs clients.

Lorsque vous tapez la formule = Alea () dans une cellule, vous obtenez un nombre qui est également susceptible de supposer toute valeur comprise entre 0 et 1. Par conséquent, environ 25% du temps, vous devez obtenir un nombre inférieur ou égal à 0,25; environ 10% du temps, vous devez obtenir un nombre d'au moins 0,90, et ainsi de suite. Pour montrer le fonctionnement de la fonction Alea, observez le fichier Randdemo. xlsx, présenté dans la figure 60-1.

Image représentant un livre

Remarque :  Lorsque vous ouvrez le fichier Randdemo. xlsx, vous ne verrez pas les mêmes numéros aléatoires présentés dans la figure 60-1. La fonction Alea recalcule automatiquement les nombres générés lors de l'ouverture d'une feuille de calcul ou lorsque de nouvelles informations sont entrées dans la feuille de calcul.

Tout d'abord, copiez de la cellule C3 vers C4: C402 la formule = Alea (). Nommez ensuite la plage C3: C402 données. Ensuite, dans la colonne F, vous pouvez effectuer le suivi de la moyenne des nombres aléatoires 400 (cellule F2) et utiliser la fonction NB.Si pour déterminer les fractions entre 0 et 0,25, 0,25 et 0,50, 0,50 et 0,75, et 0,75 et 1. Lorsque vous appuyez sur la touche F9, les nombres aléatoires sont recalculés. Notez que la moyenne des nombres 400 est toujours approximativement 0,5, et qu'environ 25% des résultats sont représentées par des intervalles de 0,25. Ces résultats sont cohérents avec la définition d'un nombre aléatoire. Notez également que les valeurs générées par Alea dans différentes cellules sont indépendantes. Par exemple, si le nombre aléatoire généré dans la cellule C3 est un grand nombre (0,99, par exemple), il n'indique aucune information sur les valeurs des autres nombres aléatoires générés.

Supposons que la demande d'un calendrier soit régie par la variable aléatoire discrète suivante:

Besoins

probabilité

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

Comment Excel peut-il être lu ou simuler une telle demande pour les calendriers de plusieurs fois? Le pli consiste à associer chaque valeur possible de la fonction Alea à une demande possible de calendriers. Le devoir suivant vous permet de vérifier qu'une demande de 10 000 aura 10 pour cent du temps, et ainsi de suite.

Besoins

Numéro aléatoire affecté

10 000

Inférieur à 0,10

20 000

Supérieur ou égal à 0,10 et inférieur à 0,45

40 000

Supérieur ou égal à 0,45 et inférieur à 0,75

60 000

Supérieur ou égal à 0,75

Pour montrer la simulation de la demande, observez le fichier Discretesim. xlsx, présenté dans la figure 60-2 sur la page suivante.

Image représentant un livre

Pour effectuer une recherche à partir de la plage de tableaux F2 (nommée recherche), il est essentiel d'utiliser un nombre aléatoire. Le nombre aléatoire supérieur ou égal à 0 et inférieur à 0,10 entraîne une demande de 10 000; le nombre aléatoire supérieur ou égal à 0,10 et inférieur à 0,45 entraîne une demande de 20 000; le nombre aléatoire supérieur ou égal à 0,45 et inférieur à 0,75 entraîne une demande de 40 000; et les nombres aléatoires supérieurs ou égaux à 0,75 produiront une demande d' 60 000. Vous générez des nombres aléatoires 400 en effectuant une copie de C3 vers C4: C402 la formule Alea (). Vous pouvez ensuite générer des essais ou des itérations 400 de la demande de calendrier en les copiant à partir de B3 vers B4: B402 la formule RECHERCHEV (C3, recherche, 2). Cette formule vérifie que tout nombre aléatoire inférieur à 0,10 génère une demande d' 10 000, tout nombre aléatoire compris entre 0,10 et 0,45 génère une demande de 20 000, et ainsi de suite. Dans la plage de cellules F8: F11, utilisez la fonction NB.Si pour déterminer la fraction de nos 400 d'itérations produisant chaque demande. Lorsque nous appuyons sur la touche F9 pour recalculer les nombres aléatoires, les probabilités simulées sont proches des probabilités de la demande présumées.

Si vous tapez dans n'importe quelle cellule la formule Loi. normale (ALEA (), MU, Sigma), vous générerez une valeur simulée d'une variable aléatoire normale ayant une moyenne MU et un écart type Sigma. Cette procédure est illustrée dans le fichier Normalsim. xlsx, présenté dans la figure 60-3.

Image représentant un livre

Imaginons que nous voulons simuler des versions d'évaluation d' 400, ou des itérations, pour une variable aléatoire aléatoire avec une moyenne de 40 000 et un écart type de 10 000. (Vous pouvez taper ces valeurs dans les cellules E1 et E2 et nommer ces cellules et Sigma, respectivement.) Copie de la formule = Alea () de C4 vers C5: C403 génère des nombres aléatoires différents de 400. Copie de B4 vers B5: B403 la formule Loi. la formule Loi. normale (C4, moyenne, Sigma) génère des valeurs d'évaluation différentes d'une variable aléatoire normale dont la moyenne est 40 000 et un écart type de 10 000. Lorsque vous appuyez sur la touche F9 pour recalculer les nombres aléatoires, la moyenne reste proche de 40 000 et l'écart type est proche de 10 000.

Pour une valeur aléatoire x, la formule Loi. f, Loi. f, Loi . f génère le énième centile d'une variable aléatoire normale ayant une moyenne MU et un écart type Sigma. Par exemple, le nombre aléatoire 0,77 dans la cellule C4 (voir figure 60-3) génère dans la cellule B4 approximativement le centile 77th d'une variable aléatoire normale ayant une moyenne de 40 000 et un écart type d' 10 000.

Dans cette section, vous allez découvrir comment la simulation de Monte Carlo peut être utilisée en tant qu'outil décisionnel. Supposez que la demande de carte de la Saint-Valentin soit régie par la variable aléatoire discrète suivante:

Besoins

probabilité

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

La carte de vœux est commercialisée pour $4,00, et le coût variable de la production de chaque carte est $1,50. Les cartes inversées doivent être disposées au coût de $0,20 par carte. Combien de cartes doivent être imprimées?

Fondamentalement, nous simulons chacune des quantités de production possibles (10 000, 20 000, 40 000 ou 60 000) à de nombreux moments (par exemple, 1000 itérations). Puis nous Déterminez la quantité commandée qui génère le bénéfice maximal pour les itérations 1000. Vous pouvez trouver les données pour cette section dans le fichier Valentine. xlsx, présenté dans la figure 60-4. Vous attribuez les noms de plage dans les cellules B1: B11 aux cellules C1: C11. La plage de cellules G3: H6 est affectée à la recherchede nom. Nos paramètres de prix de vente et de coût sont entrés dans les cellules C4: C6.

Image représentant un livre

Vous pouvez entrer une quantité de production d'évaluation (40 000 dans cet exemple) dans la cellule C1. Ensuite, créez un nombre aléatoire dans la cellule C2 avec la formule = Alea (). Comme décrit précédemment, vous simulez une demande pour la carte dans la cellule C3 avec la formule RECHERCHEV (ALEA, recherche, 2). (Dans la formule RECHERCHEV, Alea est le nom de la cellule affectée à la cellule C3, et non la fonction ALEA.)

Le nombre d'unités vendues est inférieur à la quantité de production et à la demande. Dans la cellule C8, vous calculez nos revenus à l'aide de la formule min (produits, Demand) * unit_price. Dans la cellule C9, vous calculez le coût total de production à l'aide de la formule produits * unit_prod_cost.

Si nous produisons plus de cartes que la demande, le nombre d'unités restantes par rapport à la production moins la demande est d'égal à égal. Sinon, il n'y a pas d'unités. Nous calculons nos coûts de suppression dans la cellule C10 en utilisant la formule unit_disp_cost * si (produced>demand, produit-Demand, 0). Enfin, dans la cellule C11, nous calculons nos bénéfices en tant que revenus-total_var_cost-total_disposing_cost.

Nous aimerions avoir besoin d'appuyer sur F9 à plusieurs reprises (1000, par exemple) pour chaque quantité de production et de calculer la rentabilité prévue pour chaque quantité. Il s'agit d'une table de données à sens unique, qui fait partie de votre sauvetage. (Pour plus d'informations sur les tables de données, rePortez-vous au chapitre 15, «analyse de sensibilité avec les tables de données». La table de données utilisée dans cet exemple est illustrée dans la figure 60-5.

Image représentant un livre

Dans la plage de cellules A16: A1015, entrez les nombres 1 à 1 000 (correspondant aux essais 1000). Pour créer ces valeurs, il est possible de commencer par entrer 1 dans la cellule A16. Sélectionnez la cellule, puis sous l'onglet Accueil , dans le groupe édition , cliquez sur remplissage, puis sélectionnez série pour afficher la boîte de dialogue série . Dans la boîte de dialogue séries , présentée dans la figure 60-6, entrez une valeur de pas de 1 et une valeur d'arrêt de 1000. Dans la zone série en , sélectionnez l'option colonnes , puis cliquez sur OK. Les nombres 1-1 000 seront entrés dans la colonne A à partir de la cellule A16.

Image représentant un livre

Nous allons ensuite entrer les quantités de production possibles (10 000, 20 000, 40 000, 60 000) dans les cellules B15: E15. Nous voulons calculer la rentabilité de chaque numéro d'essai (1 à 1000) et chaque quantité de production. Nous faisons référence à la formule de profit (calculée dans la cellule C11) dans la cellule supérieure gauche de notre table de données (A15) en entrant = C11.

Nous sommes désormais prêts à tromper Excel pour simuler des itérations 1000 de la demande pour chaque quantité de production. Sélectionnez la plage de tableaux (A15: E1014), puis dans le groupe outils de données sous l'onglet données, cliquez sur l'analyse, puis sélectionnez table de données. Pour configurer une table de données bidirectionnelle, choisissez la quantité de production (cellule C1) comme cellule d'entrée en ligne, puis sélectionnez n'importe quelle cellule vide (nous avons choisi cellule I14) comme cellule d'entrée en colonne. Après avoir cliqué sur OK, Excel simule les valeurs de la demande 1000 pour chaque quantité de commandes.

Pour mieux comprendre l'utilité de cela, prenez en compte les valeurs placées dans la table de données dans la plage de cellules C16: C1015. Pour chacune de ces cellules, Excel utilise une valeur de 20 000 dans la cellule C1. Dans C16, la valeur de la cellule d'entrée en colonne de 1 est placée dans une cellule vide et le nombre aléatoire dans la cellule C2 est recalculé. Le profit correspondant est alors enregistré dans la cellule C16. La valeur entrée dans la cellule de la colonne de la cellule 2 est alors placée dans une cellule vide, et le nombre aléatoire dans C2 est recalculé. Le profit correspondant est entré dans la cellule C17.

En effectuant une copie de la cellule B13 sur C13: E13 la formule moyenne (B16: B1015), nous calculons le bénéfice simulé moyen pour chaque quantité de production. En effectuant une copie de la cellule B14 sur C14: E14 la formule ECARTYPE (B16: B1015), nous calculons l'écart type de nos revenus simulés pour chaque quantité de commande. Chaque fois que vous appuyez sur F9, 1000 itérations de la demande sont simulées pour chaque quantité de commandes. La génération de cartes 40 000 génère toujours le plus grand bénéfice attendu. Par conséquent, il semble que la génération de cartes 40 000 est la décision appropriée.

L'impact du risque sur notre décision     Si nous avons créé 20 000 au lieu de cartes 40 000, notre bénéfice attendu passe approximativement 22%, mais le risque (calculé par l'écart type du profit) chute presque 73%. Par conséquent, s'il s'agit d'un problème de plus en plus risqué, la production de cartes 20 000 peut être la bonne décision. Dans le cas d'un problème, la génération de cartes 10 000 a toujours un écart type de 0 cartes, car si nous produisons 10 000 cartes, nous en vendons toujours tous sans reste.

Remarque :  Dans ce classeur, l'option de calcul est définie sur automatique saufdans les tableaux. (Utilisez la commande calcul dans le groupe calcul de l'onglet formules.) Ce paramètre permet de s'assurer que notre table de données ne sera pas recalculée sauf si vous appuyez sur la touche F9, ce qui peut être une bonne idée du fait qu'une grande table de données ralentira votre travail à chaque fois que vous tapez du texte dans votre feuille de calcul. Notez que dans cet exemple, chaque fois que vous appuyez sur F9, le profit moyen sera modifié. Ce problème survient parce que chaque fois que vous appuyez sur F9, une séquence distincte de 1000 numéros aléatoires est utilisée pour générer des demandes pour chaque quantité de commande.

Intervalle de confiance pour la rentabilité moyenne     Il y a une question naturelle à poser dans cette situation, dans quel intervalle est-ce que nous pouvons nous être à l'aide de 95%, car le véritable profit est-il possible? Cet intervalle est appelé intervalle de confiance de 95% pour le résultat moyen. Un intervalle de confiance de 95% pour la moyenne d'une sortie de simulation est calculé par la formule suivante:

Image représentant un livre

Dans la cellule J11, vous calculez la limite inférieure de l'intervalle de confiance de 95% pour le profit moyen lorsque les calendriers 40 000 sont produits à l'aide de la formule D13 – 1.96 * D14/sqrt (1 000). Dans la cellule J12, vous calculez la limite supérieure de votre intervalle de confiance de 95% avec la formule D13 + 1.96 * D14/sqrt (1 000). Ces calculs sont décrits dans la figure 60-7.

Image représentant un livre

Nous sommes de 95%, car le classement des calendriers 40 000 est compris entre $56 687 et $62 589.

  1. Un commerçant GMC estime que la demande d'envoi de 2005 sera normalement distribuée avec une moyenne de 200 et un écart type de 30. Le coût de la réception d'un envoi est de $25 000, et il vend un envoi pour $40 000. La moitié de tous les envoi non vendus au prix total peuvent être vendus pour $30 000. Il envisage de commander 200, 220, 240, 260, 280 ou 300. Combien dois-je commander?

  2. Un petit supermarché tente de déterminer le nombre de copies du magazine de personnes qu'il doit commander chaque semaine. Ils pensent que la demande de personnes est régie par la variable aléatoire discrète suivante:

    Besoins

    probabilité

    15

    0,10

    20

    0,20

    1,25

    0,30

    trente

    0,25

    35

    0,15

  3. Le supermarché paie $1,00 pour chaque copie de personnes et la vend pour $1,95. Vous pouvez renvoyer chaque copie invendue pour $0,50. Combien de copies de personnes doivent être commandées par le Windows Store?

Besoin d’aide supplémentaire ?

Vous pouvez toujours consulter un expert de la communauté technique Excel, obtenir une assistance dans la communauté Answers ou suggérer une nouvelle fonctionnalité ou une amélioration sur le forum Excel User Voice.

Développez vos compétences dans Office
Découvrez des formations
Accédez aux nouvelles fonctionnalités en avant-première
Rejoignez le programme Office Insider

Ces informations vous ont-elles été utiles ?

Nous vous remercions pour vos commentaires.

Merci pour vos commentaires. Il serait vraisemblablement utile pour vous de contacter l’un de nos agents du support Office.

×