Office
Se connecter

Introduction à 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é à partir de Microsoft Excel analyse et modélisation d’entreprise en Winston.

  • Qui utilise simulation de Monte Carlo ?

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

  • Comment vous pouvez en simuler valeurs d’une variable aléatoire discrète ?

  • Comment vous pouvez en simuler valeurs d’une variable aléatoire normale ?

  • Comment une société de carte de vœux déterminer le nombre de cartes pour produire ?

Nous aimerions précisément estimer les probabilités des événements incertains. Par exemple, quelle est la probabilité que les paiements d’un nouveau produit aura une valeur actuelle nette (VAN) positive ? Quel est le facteur de risque de notre portefeuille ? Simulation de Monte Carlo nous permet aux situations de modèle qui présentent incertitude et de les lire sur un ordinateur des milliers de fois.

Remarque : Le nom de simulation de Monte Carlo proviennent les simulations ordinateur effectuées pendant les années 1930 et 1940s pour évaluer la probabilité que la réaction en chaîne nécessaire pour une bombe atom à la détonation fonctionne correctement. Les physicists impliqués dans ce travail ont été volumineux ventilateurs de jeux d’argent, afin qu’ils a communiqué les simulations le nom de code Monte Carlo.

Dans les cinq chapitres, vous verrez des exemples de comment vous pouvez utiliser Excel pour effectuer des simulations Monte Carlo.

De nombreuses sociétés utilisent simulation de Monte Carlo comme une partie importante de leurs processus de décision. Voici quelques exemples.

  • Moteurs général, Proctor et risque, Pfizer, Bristol Myers Squibb et Eli Lilly utiliser simulation pour estimer le rendement moyen et le facteur de risque de nouveaux produits. En portables, ces informations sont utilisées par le PDG pour déterminer quels sont les produits fournis sur le marché.

  • Portables utilise simulation pour des activités telles que réaliser des prévisions revenu net pour l’entreprise, prédiction de coûts structurelles et d’achat et déterminer sa sensibilité à différents types de risque (par exemple, les modifications de taux d’intérêt et variations de taux de change).

  • Lilly utilise simulation pour déterminer la capacité usine optimale pour chaque médicaments.

  • Proctor et risque utilise simulation modéliser et de façon optimale couvrir risque de change.

  • Sears utilise simulation pour déterminer le nombre d’unités de chaque ligne de produit doit être organisée de fournisseurs, par exemple, le nombre de paires pantalon Dockers qui doit être commandé cette année.

  • Sociétés oil mises et médicaments utilisent simulation valeur « options réelles », tels que la valeur d’une option pour développer, contrat ou différer un projet.

  • Planificateurs financières utilisent simulation de Monte Carlo pour déterminer les stratégies d’investissement optimale de retraite leurs clients.

Lorsque vous tapez la formule = ALEA() dans une cellule, vous obtenez un nombre qui est également probable que leur appliquer n’importe quelle 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 pour cent de l’heure que vous devez obtenir un nombre qui est au moins 0,90 et ainsi de suite. Pour illustrer le fonctionne de la fonction ALEA, consultez le fichier Randdemo.xlsx, illustré Figure 60-1.

Image représentant un livre
Figure 60-1 illustrant la fonction RAND

Remarque : Lorsque vous ouvrez le fichier Randdemo.xlsx, vous ne verrez pas les mêmes nombres aléatoires indiqués dans la Figure 60-1. La fonction RAND recalcule toujours automatiquement les nombres qu'il génère l’ouverture d’une feuille de calcul ou de nouvelles informations sont entrées dans la feuille de calcul.

Tout d’abord, copiez la cellule C3 à C4:C402 la formule = ALEA(). Vous nommez la plage C3:C402 données. Puis, dans la colonne F, vous pouvez effectuer le suivi de la moyenne des nombres aléatoires 400 (cellule F2) et utilisez la fonction NB.Si pour déterminer les fractions sont compris 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ées. Notez que la moyenne des 400 nombres est toujours environ 0,5 et que près de 25 % des résultats sont dans des intervalles de 0,25. Ces résultats sont compatibles avec la définition d’un nombre aléatoire. Notez également que les valeurs générées par ALEA dans plusieurs cellules sont indépendantes. Par exemple, si le nombre aléatoire généré dans la cellule C3 est un grand nombre (par exemple, 0,99), il nous fournit aucune information sur les valeurs des autres nombres aléatoires.

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

À la demande

Probabilité

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

Comment pouvons-nous nous avons Excel diffuser des ou simuler, cette demande des calendriers autant de fois ? L’astuce consiste à associer chaque valeur possible de la fonction RAND avec une demande possible pour les calendriers. L’affectation suivante garantit qu’une demande de 10 000 se produire 10 pour cent du temps et ainsi de suite.

À la demande

Nombre aléatoire affectée

10 000

Inférieur à 0.10

20 000

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

40 000

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

60 000

Supérieur ou égal à 0,75

Pour illustrer la simulation de la demande, examinez le fichier Discretesim.xlsx, illustré Figure 60-2 dans la page suivante.

Image représentant un livre
Figure 60-2 simulation d’une variable aléatoire discrète

La clé à notre simulation consiste à utiliser un nombre aléatoire pour lancer une recherche à partir de la plage de la table F2:G5 (appelé liste de choix). Des nombres aléatoires supérieurs ou égales à 0 et inférieur à 0,10 produira une demande de 10 000 ; des nombres aléatoires supérieurs ou égales à 0,10 et inférieure à 0,45 produira une demande de 20 000 ; des nombres aléatoires supérieurs ou égales à 0,45 et inférieure à 0,75 produira une demande de 40 000 ; et des nombres aléatoires supérieurs ou égales à 0,75 produira une demande de 60 000. Vous générez des nombres aléatoires 400 en copiant C3 vers C4:C402 la formule ALEA (). Ensuite, vous générez 400 essais ou itérations, de la demande de calendrier en copiant B3 vers B4:B402 la formule VLOOKUP(C3,lookup,2). Cette formule garantit que n’importe quel nombre aléatoire inférieur à 0,10 génère une demande de 10 000, un 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 itérations produisant chaque à la demande. Quand nous appuyez sur F9 pour recalculer les nombres aléatoires, les probabilités simulées sont près de notre probabilités supposé à la demande.

Si vous tapez la formule NORMINV(rand(),mu,sigma)dans n’importe quelle cellule, vous allez générer une valeur d’une variable aléatoire normale ayant une moyenne mu et écart type sigmasimulée. Cette procédure est illustrée dans le fichier Normalsim.xlsx, dans la Figure 60-3.

Image représentant un livre
Figure 60-3 simulation d’une variable aléatoire normale

Supposons que nous voulons simuler 400 essais ou itérations, d’une variable aléatoire normale avec une moyenne de 40 000 et un écart type de 10 000. (Vous pouvez tapez les valeurs suivantes dans les cellules E1 et E2 et nommez ces cellules signifie et sigma, respectivement.) Copie la formule = ALEA() de C4 vers C5:C403 génère des nombres aléatoires différents 400. Copie de B4 vers B5:B403 la formule NORMINV(C4,mean,sigma) génère 400 différentes valeurs d’évaluation à partir d’une variable aléatoire normale avec une moyenne de 40 000 et un écart type de 10 000. Quand nous appuyez sur la touche F9 pour recalculer les nombres aléatoires, la moyenne reste près de 40 000 et l’écart-type près de 10 000.

Pour l’essentiel, pour un nombre aléatoire x, la formule NORMINV(p,mu,sigma) génère le pème centile d’une variable aléatoire normal avec une moyenne mu et un écart type sigma. Par exemple, le nombre aléatoire 0.77 dans la cellule C4 génère (voir Figure 60-3) dans la cellule B4 environ le centile 77th d’une variable aléatoire normal avec une moyenne de 40 000 et un écart type de 10 000.

Dans cette section, vous verrez comment simulation Monte Carlo peut être utilisée comme un outil de prise de décision. Supposons que la demande pour une carte de Saint-Valentin est régie par la variable aléatoire discrète suivante :

À la demande

Probabilité

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

La carte de vœux vend pour $4.00, et le coût variable de production chaque carte est $1.50. Cartes restants doivent être supprimés d’à un coût de 0,20 $ par carte. Combien de cartes doivent-elles être imprimées ?

En fait, nous simuler chaque quantité de production possible (10 000, 20 000, 40 000 ou 60 000) autant de fois (par exemple, 1000 répétitions). Ensuite, nous déterminer quelle est la quantité ordre donne le bénéfice moyen maximal sur les itérations 1000. Vous pouvez trouver les données pour cette section dans le fichier Valentine.xlsx, illustré Figure 60-4. Vous affectez les noms de plage de cellules B1:B11 aux cellules C1:C11. La plage de cellules G3:H6 est affectée à la recherchede nom. Notre prix de vente et les paramètres de coût sont entrés dans les cellules C4:C6.

Image représentant un livre
Simulation de carte de Saint-Valentin figure 60-4

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 compris dans la cellule C2 avec la formule = ALEA(). Comme décrit précédemment, vous simulez à la demande pour la carte dans la cellule C3 avec la formule VLOOKUP(rand,lookup,2). (Dans la formule RECHERCHEV, ALEA est le nom de cellule affecté à la cellule C3, pas la fonction ALEA.)

Le nombre d’unités vendues est le plus petit de notre quantité de production et à la demande. Dans la cellule C8, vous nos recettes contenant la formule de calcul MIN (produite, à la demande) * unit_price. Dans la cellule C9, vous calculez les frais de production totale avec la formule produites * unit_prod_cost.

Si nous produire plus sur les cartes que l’argument à la demande, le nombre d’unités restant est égal à production moins à la demande ; dans le cas contraire aucune unité ne restants. Nous notre coût disposition dans la cellule C10 contenant la formule de calcul unit_disp_cost * IF (produits > à la demande, produites – la demande, 0). Enfin, dans la cellule C11, nous calculer profits comme chiffre d’affaires – total_var_cost-total_disposing_cost.

Nous aimerions un moyen efficace pour appuyer sur F9 autant de fois (par exemple, 1000) pour chaque quantité de production et calculer notre profit prévu pour chaque quantité. Cette situation est une dans laquelle une table de données à double sens vient notre là pour vous aider. (Voir chapitre 15, « Critère de diffusion analyse avec les Tables de données, » pour plus d’informations sur les tables de données). La table de données utilisée dans cet exemple est indiquée Figure 60-5.

Image représentant un livre
Table de données à double sens figure 60-5 pour la carte de vœux simulation

Dans la plage de cellules A16:A1015, entrez un nombre 1 et 1 000 (correspondant à notre essais 1000). Un moyen facile de créer ces valeurs est de commencer en entrant 1 dans la cellule A16. Sélectionnez la cellule, puis sous l’onglet accueil dans le groupe Édition, cliquez sur remplissage et sélectionnez la série pour afficher la boîte de dialogue série. Dans la boîte de dialogue série illustrée Figure 60-6, entrez une valeur de seuil de 1 et une valeur arrêter de 1000. Dans la zone Dans la série, sélectionnez l’option de colonnes, puis cliquez sur OK. Les numéros 1 et 1 000 seront entrées dans la colonne de départ dans la cellule A16.

Image représentant un livre
Figure 60-6 à l’aide de la boîte de dialogue série pour remplir les nombres d’évaluation 1 et 1000

Nous Entrez ensuite notre quantités de production possible (10 000, 20 000, 40 000, 60 000) dans les cellules B15:E15. Nous voulons calculer la marge pour chaque numéro de version d’évaluation (1 à 1000) et chaque quantité de production. Nous faire référence à la formule dans un but lucratif (cellule calculée dans C11) dans la cellule supérieure gauche de notre table de données (A15) en entrant = C11.

Nous sommes maintenant prêts à astuce Excel en simuler 1000 répétitions de la demande pour chaque quantité de production. Sélectionnez la plage de table (A15:E1014), puis, dans le groupe Outils de données sous l’onglet données, cliquez sur analyse de scénarios quoi et puis sélectionnez la Table de données. Pour configurer une table de données à double sens, choisissez notre quantité de production (cellule C1) en tant que la cellule d’entrée de ligne et sélectionnez une cellule vide (nous avons choisi cellule I14) en tant que la cellule d’entrée en colonne. Après avoir cliqué sur OK, Excel simule 1000 des valeurs à la demande pour chaque quantité de commande.

Pour mieux comprendre pourquoi cela fonctionne, considérez les valeurs placées par la table de données dans la plage de cellules C16:C1015. Pour chacun 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 que C2 recalcule. Le résultat correspondant est ensuite enregistré dans la cellule C16. La valeur d’entrée de cellule de colonne de 2 est placée dans une cellule vide, puis le nombre aléatoire dans C2 recalcule à nouveau. Le résultat correspondant est entré dans la cellule C17.

En copiant à partir de la cellule B13 à C13:E13 la formule AVERAGE(B16:B1015), nous calculer la moyenne des bénéfices simulé pour chaque quantité de production. En copiant depuis B14 cellule vers la formule STDEV(B16:B1015)C14:E14, nous calcule l’écart type de nos bénéfices simulés pour chaque quantité de commande. Chaque fois que nous appuyez sur F9, 1000 répétitions de la demande sont simulées pour chaque quantité de commande. Fabrication de 40 000 cartes toujours donne le plus grand profit prévu. Par conséquent, il apparaît que production 40 000 cartes correspond à la décision appropriée.

L’Impact du risque sur notre décision     Si nous produit 20 000 au lieu de 40 000 cartes, notre profit prévu supprime environ 22 %, mais notre risque (exprimée par l’écart type de la marge) supprime presque 73 %. Par conséquent, si nous sont très réticents à risque, production 20 000 cartes peut-être la bonne décision. D’ailleurs, produisant 10 000 cartes toujours a un écart type de 0 cartes, car si nous produire 10 000 cartes, nous sera toujours vendre d'entre eux sans les leftovers.

Remarque : Dans ce classeur, l’option de calcul est définie à Automatique sauf pour les Tables. (Utilisez la commande de calcul dans le groupe calcul sous l’onglet Formules). Ce paramètre garantit que notre table de données n’est plus recalculée à moins que nous appuyez sur F9, ce qui est recommandé, car une table de données volumineux ralentir votre travail si elle recalcule chaque fois que vous tapez des caractères dans votre feuille de calcul. Notez que dans cet exemple, lorsque vous appuyez sur F9, la marge moyenne changera. Cela se produit car chaque fois que vous appuyez sur F9, une séquence différente de nombres aléatoires 1000 sert à générer des demandes pour chaque quantité de commande.

Intervalle de confiance pour signifie marge     Une question Natural Keyboard poser dans cette situation est, dans quel intervalle sommes-nous 95 % que la marge moyenne true se situe ? Cet intervalle est appelé l' intervalle de confiance de 95 % de profit moyenne. Un intervalle de confiance de 95 % de la moyenne de toutes les sorties simulation est calculé par la formule suivante :

Image représentant un livre

Dans la cellule J11, vous calculez la limite inférieure pour l’intervalle de confiance 95 % sur la marge moyenne lorsque 40 000 calendriers sont présentés avec la formule D13–1.96*D14/SQRT(1000). Dans la cellule J12, vous calculez la limite supérieure de notre intervalle de confiance 95 % contenant la formule D13+1.96*D14/SQRT(1000). Ces calculs sont affichés dans la Figure 60-7.

Image représentant un livre
Intervalle de confiance de 95 % figure 60 à 7 pour marge moyenne lorsque 40 000 calendriers sont triés

Nous sommes 95 % que que notre marge moyenne lorsque 40 000 calendriers sont triés est comprise entre $56,687 et $62,589.

  1. Un revendeur GMCH estime que la demande de 2005 envoyés sera normalement distribuée avec une moyenne de 200 et l’écart type de 30. Son coût de recevoir un envoi est 25 000 $, et il est vendu un envoi à 40 000 $. La moitié de tous les envoyés ne pas vendus au prix total peut être vendue pour 30 000 €. Il envisage le classement 200, 220, 240, 260, 280 ou 300 envoyés. Combien doit il commander ?

  2. Un petit supermarché tente de déterminer le nombre de copies de personnes magazines qu’ils doivent achètent chaque semaine. Ils pensent que leur à la demande pour les personnes est régie par la variable aléatoire discrète suivante :

    À la demande

    Probabilité

    15

    0,10

    20

    0,20

    25

    0,30

    30

    0,25

    35

    0,15

  3. Le supermarché rapporte 1,00 $ pour chaque copie de personnes et les vend pour $1.95. Chaque copie non vendu peut être renvoyée pour 0,50 $. Le nombre de copies des personnes doit le magasin ordre ?

Vous avez besoin d’une 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.

×