Iepazīšanās ar Monte Carlo imitēšana programmā Excel

Piezīme.:  Mēs vēlamies sniegt jums visjaunāko palīdzības saturu jūsu valodā, cik vien ātri to varam. Šī lapa ir tulkota automatizēti, un tajā var būt gramatiskas kļūdas un neprecizitātes. Mūsu nolūks ir šo saturu padarīt jums noderīgu. Vai lapas lejasdaļā varat mums pavēstīt, vai informācija jums noderēja? Ērtai atsaucei šeit ir šis raksts angļu valodā .

Šajā rakstā pielāgoja no Microsoft Excel datu analīze un biznesa modelēšana , Wayne L. Winston.

  • Kas izmanto Monte Carlo imitēšana?

  • Kas notiek, ja ierakstāt šūnā gadījumskaitļa ?

  • Kā var imitēt diskrēto nejaušs mainīgais vērtības?

  • Kā var imitēt parasto nejaušs mainīgais vērtības?

  • Kā apsveikuma kartīte uzņēmums var noteikt, cik daudz kartes izveidošanai

Mēs vēlamies, lai precīzi novērtētu neskaidra notikumu varbūtību. Piemēram, kāda ir varbūtība, ka jaunu produktu naudas plūsmu ir pozitīva pašreizējā neto vērtība (NPV)? Kas ir mūsu investīciju portfeļa riska faktoram? Monte Carlo imitēšana mums ļauj modeļa situācijas, kas parādītu neskaidrības un pēc tam atskaņot tos datorā tūkstošu reižu skaitu.

Piezīme.: Nosaukums Monte Carlo imitēšana nāk no veikti 1930 un 1940 var aprēķināt varbūtību, ka ķēdes reakciju, kas nepieciešami atom bumbu detonēt darbosies sekmīgi datorsimulācijas. Iesaistīti šajā darbu fiziķi bija liels ventilatoriem spēļu, tāpēc iedeva simulācijas Monte Carlokoda nosaukumu.

Nākamās četras nodaļās, redzēsit piemēri, kā varat izmantot Excel, lai veiktu Monte Carlo simulācijas.

Daudzi uzņēmumi izmanto Monte Carlo modelēšana kā svarīga daļa ir to lēmumu pieņemšanas process. Tālāk ir sniegti daži piemēri.

  • Vispārīgi dzinējiem, Proctor un risks, Pfizer, Bristol Myers Squibb un ES Lilija izmantošana imitēšana novērtēt gan vidējā peļņa un riska faktoram jaunu produktu. Pie GM, šī informācija tiek izmantota vadītājs, lai noteiktu, kura produktos tirgus.

  • GM lieto imitēšana, veicot darbības, piemēram, neto ienākumi prognozēšanas Corporation, paredzēšanu strukturālo un iegādes izmaksas un noteiktu tās jutība dažāda veida riska (piemēram, procentu likme izmaiņas un valūtas kursa izmaiņām).

  • Lilija imitēšana izmanto, lai noteiktu katra narkotikām optimālu augu ietilpību.

  • Proctor un risks izmanto modelēšana modelis un optimāli nodrošinātos ārvalstu valūtas risku.

  • Sears imitēšana izmanto, lai noteiktu, cik vienību katrā rindā produkta jāsakārto no piegādātājiem — piemēram, aizturēšanas bikses, kas jāsakārto šogad pāru skaitu.

  • Ziemeļvējš un narkotikām uzņēmumiem izmantojiet imitēšana vērtību "reālā opcijas", piemēram, opcijas, lai izvērstu, līgumu vai atlikt projekta vērtību.

  • Finanšu plānotājus izmantojiet Monte Carlo imitēšana optimālu investīciju stratēģijas noteikšanai izņemšana no apgrozības saviem klientiem.

Kad ierakstāt formulu gadījumskaitļa šūnā, jums ir skaitlis, kas ir vienādi varētu pieņemt jebkura vērtība diapazonā no 0 līdz 1. Tādējādi ap 25 procentos gadījumu, jums vajadzētu skaitli mazāka vai vienāda ar 0,25; ap 10 procentos gadījumu, jums jāsaņem skaitli, kas ir vismaz 0,90 utt. Lai parādītu, kā darbojas funkcija RAND, apskatiet failu Randdemo.xlsx, parādīts attēlā 60-1.

Book Image
60-1. attēls demonstrē funkcijas RAND

Piezīme.: Kad atverat failu Randdemo.xlsx, netiks rādīti vienā nejauši izvēlētus skaitļus, kas parādīts attēlā 60-1. Funkcijas RAND vienmēr automātiski tiek pārrēķināta tas rada atverot darblapu vai ievadot jauno informāciju darblapas skaitļus.

Vispirms iekopēt šūnā C3 C4:C402 formulas gadījumskaitļa. Pēc tam piešķiriet nosaukumu C3:C402 datudiapazonu. Pēc tam kolonnā F, varat izsekot 400 nejauši izvēlētus skaitļus (šūna F2) vidējo vērtību un izmantojiet funkciju COUNTIF, lai noteiktu daļskaitļus, kas ir starp 0 un 0,25, 0,25 un 0,50 0,50 un 0,75, un 0,75 un 1. Kad nospiežat taustiņu F9, tiek pārrēķinātas nejauši izvēlētus skaitļus. Paziņojums, ka 400 skaitlisko vērtību vidējā vērtība vienmēr ir aptuveni 0,5, un ka aptuveni 25 procenti rezultāti ir 0,25 intervālos. Šie rezultāti saskan ar gadījumskaitli definīcija. Ņemiet arī vērā to vērtības ģenerē RAND dažādās šūnās ir neatkarīga. Piemēram, ja nejauši izvēlēts skaitlis ģenerētas šūnā C3 ir ļoti daudz (piemēram, 0,99), tā stāsta mums nekāda informācija par citiem nejauši izvēlētus skaitļus, kas ģenerēti vērtībām.

Pieņemsim, ka pieprasījums pēc kalendāra attiecas šādi diskrēto nejaušs mainīgais:

Pieprasījuma

Varbūtība

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

Kā var būt Excel parādīties vai simulētu, šo pieprasījumu kalendāru reižu? Lai to izdarītu, ir katras iespējamās vērtības funkcijas RAND piesaistīšana iespējamo pieprasījuma kalendāru. Šādi piešķiršanas nodrošina, ka pieprasījuma 10 000 rodas 10 procentos gadījumu, un tā tālāk.

Pieprasījuma

Gadījumskaitlis, kas ir piešķirts

10 000

Mazāks par 0,10

20 000

Lielāks par vai vienāds ar 0.10 un mazāk nekā 0.45

40 000

Lielāks par vai vienāds ar 0,45 un mazāka par 0,75

60 000

Lielāks par vai vienāds ar 0,75

Lai parādītu pieprasījuma modelēšana, aplūkojiet faila Discretesim.xlsx, nākamajā lapā parādīto skaitli 60-2.

Book Image
60-2. attēls imitēt diskrēto nejaušs mainīgais

Mūsu imitēšana vislabāk izmantot nejaušu skaitli, lai aktivizētu uzmeklēšanas no tabulas diapazona F2:G5 (kurus sauc par uzmeklēšanas). Nejauši izvēlētus skaitļus, kas ir lielāks par vai vienāds ar 0 un mazāka par 0.10 sniegs pieprasījuma 10 000; nejauši izvēlētus skaitļus, kas ir lielāks par vai vienāds ar 0.10 un mazāk nekā 0.45 sniegs pieprasījuma 20 000; nejauši izvēlētus skaitļus, kas ir lielāks par vai vienāds ar 0,45 un mazāka par 0,75 sniegs pieprasījumu 40 000; un nejauši izvēlētus skaitļus, kas ir lielāks par vai vienāds ar 0,75 būs 60 000 pieprasījumu. Jūs ģenerēt 400 nejauši izvēlētus skaitļus, kopējot no C3 C4:C402 formulas funkcijas RAND (). Jūs tam ģenerēt 400 mēģinājumi vai atkārtojumiem, kalendāra pieprasījumu, kopējot no B3 B4:B402 VLOOKUP(C3,lookup,2)formulu. Šī formula nodrošina, ka jebkuru izlases numuru mazāk nekā 0.10 rada pieprasījumu 10 000, jebkura gadījumskaitli starp 0.10 un 0.45 rada pieprasījumu 20 000, un tā tālāk. Šūnas diapazonā, kas F8:F11, lai noteiktu mūsu 400 atkārtojumiem, iegūstot katra pieprasījuma daļu, izmantojiet funkciju COUNTIF. Nospiežot F9, lai to pārrēķinātu nejauši izvēlētus skaitļus, simulētu varbūtības ir tuvu mūsu pieņemtā pieprasījuma varbūtību.

Ja rakstāt uz jebkuras šūnas formulas NORMINV(rand(),mu,sigma), tiks ģenerēt simulētu parasto gadījuma mainīgais, kam vidējais mu un standartnovirze sigmavērtību. Šī procedūra ir attēlota failā Normalsim.xlsx, parādīts attēlā 60-3.

Book Image
Attēlā 60-3, imitēt parasto nejaušs mainīgais

Pieņemsim, mēs vēlamies, lai simulētu 400 mēģinājumi vai atkārtojumiem, attiecībā uz parasto nejaušs mainīgais ar 40 000 vidējais un standartnovirze ir 10 000. (Varat ierakstiet šīs vērtības šūnās E1 un E2, un piešķiriet nosaukumu šīs šūnas nozīmē un sigma..) Gadījumskaitļa formulas kopēšana no C4 C5:C403 ģenerē 400 citu nejauši izvēlētus skaitļus. Kopēšana no B4 B5:B403 formulu NORMINV(C4,mean,sigma) ģenerē 400 dažādas izmēģinājuma vērtības no parasto nejaušs mainīgais ar 40 000 vidējais un standartnovirze ir 10 000. Nospiežot taustiņu F9, lai pārrēķinātu nejauši izvēlētus skaitļus, vidējā aritmētiskā paliek tuvu 40 000 un standartnovirzei tuvu 10 000.

Būtībā izlases numuru x, formulas NORMINV(p,mu,sigma) ģenerē pto procentili parasto nejaušs mainīgais vidējais mu un standartnovirze sigma. Piemēram, gadījumskaitli 0.77 šūnas C4 (skatiet attēlā 60-3) ģenerē šūnā B4 aptuveni 77. procentile parasto nejaušs mainīgais ar 40 000 vidējais un standartnovirze ir 10 000.

Šajā sadaļā, redzēsit, kā Montekarlo modelēšana var izmantot kā lēmumu pieņemšanas rīks. Pieņemsim, ka pieprasījums pēc Valentīna dienas apsveikuma kartīti ar pārvalda šādi diskrēto nejaušs mainīgais:

Pieprasījuma

Varbūtība

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

Apsveikuma kartīte pārdod 4,00 $, un mainīgais izmaksas katra karte ir 1,50 $. Leftover kartes jābūt atbrīvoties no 0,20 $ kartes maksa. Cik daudz kartes jādrukā?

Būtībā, mēs imitēt katram iespējams ražošanas daudzums (10 000, 20 000, 40 000 vai 60 000) vairākas reizes (piemēram, 1000 atkārtojumiem). Mēs nosakiet, kuru daudzums, tiek iegūts maksimālais vidējā peļņa vairāk nekā 1000 atkārtojumiem. Šajā sadaļā parādīti attēlā 60-4 failu Valentine.xlsx, varat atrast datus. Tiek piešķirta C1:C11 šūnu diapazona šūnas B1:B11 vārdi. Šūnu diapazona G3:H6 tiek piešķirts nosaukums uzmeklēšanas. Mūsu pārdošanas cenas un izmaksas parametri tiek ievadīti šūnu C4:C6.

Book Image
Skaitlis 60 4 Valentīna dienas kartes imitēšana

Varat ievadīt izmēģinājuma ražošanas daudzums (40 000 šajā piemērā) šūnā C1. Pēc tam izveidojiet nejaušu skaitli šūnā C2 ar formulas gadījumskaitļa. Kā aprakstīts iepriekš, jūs imitēt pieprasījuma šūnā C3 ar formulas VLOOKUP(rand,lookup,2)karte. (VLOOKUP formulas rand ir piešķirti šūnā C3, nevis funkcijas RAND šūnas nosaukums.)

Pārdoto vienību skaits ir mazākā no mūsu ražošanas daudzums un pieprasījuma. Šūnā C8 aprēķināt mūsu ieņēmumi ar formulu MIN (ražo, pieprasījuma) * unit_price. Šūnā C9 aprēķiniet kopējo ražošanas izmaksas ar formulu izgatavoti * unit_prod_cost.

Ja mēs gaidītos vairāk kartes, nekā ir pieprasījums, vienību skaits palikuši ir vienāda ar ražošanas mīnus pieprasījumu; Pretējā gadījumā ir palikuši bez vienības. Mēs aprēķinātu mūsu rīcībā izmaksas šūnā C10 ar formulu unit_disp_cost * IF (izgatavoti > pieprasījumu, izgatavoti — pieprasījumu, 0). Visbeidzot šūnā C11 mēs Aprēķiniet mūsu peļņas kā ieņēmumu — total_var_cost total_disposing_cost.

Mēs vēlamies efektīvs veids, lai par katru ražošanas daudzumu, nospiediet taustiņu F9 vairākas reizes (piemēram, 1000) un saskan mūsu paredzamo peļņu, par katru daudzumu. Šāda situācija ir viens divvirzienu datu tabulas nāk mūs atbrīvot. (Skatīt detalizētu informāciju par datu tabulas Nodaļa 15 "Sensitivitātes analīzes ar datu tabulām".) Šajā piemērā izmantota datu tabula tiek parādīts skaitlis 60-5.

Book Image
Apsveikuma kartīte imitēšana skaitlis 60-5 divvirzienu datu tabulu

Šūnu diapazons A16:A1015, ievadiet skaitļus 1-1000 (atbilst mūsu 1000 mēģinājumi). Vienkāršs veids, kā izveidot šīs vērtības, ir sākt, ievadot šūnā A16 1 . Atlasiet šūnu, un pēc tam cilnes Sākums grupā rediģēšana noklikšķiniet uz Aizpildījumsun atlasiet sērijas , lai atvērtu dialoglodziņu sēriju . Dialoglodziņā sērijas parādīts attēlā 60-6, ievadiet soļa vērtība 1 un pārtraukt vērtība ir 1000. Apgabalā Sērijas, atlasiet opciju kolonnas un pēc tam noklikšķiniet uz Labi. Numuri 1 – 1000 tiks iekļautas kolonnas šūnā A16 sākuma.

Book Image
60 – 6. attēls sēriju, izmantojot dialoglodziņu, lai aizpildītu izmēģinājuma skaitli no 1 līdz 1000

Nākamā mēs ievadiet mūsu iespējamo produkcijas daudzums (10 000, 20 000, 40 000, 60 000) šūnu B15:E15. Mēs vēlamies, lai aprēķinātu peļņas katra izmēģinājuma versijas numurs (no 1 līdz 1000) un katrs ražošanas daudzums. Mēs skatiet formula peļņa (aprēķināt, šūna C11) mūsu datu tabulu (A15) augšējā kreisajā šūnā ierakstot = C11.

Tagad mēs esam gatavi izdarītu Excel vērā imitēt 1000 atkārtojumiem pieprasījuma attiecībā uz katru ražošanas daudzumu. Atlasiet tabulas diapazonu (A15:E1014), un pēc tam cilnes Dati grupā Datu rīki noklikšķiniet uz kāda analīzi un pēc tam atlasiet datu tabulu. Lai iestatītu divvirzienu datu tabulu, izvēlieties mūsu ražošanas daudzums (šūna C1) kā rindas ievades šūna un atlasiet tukšu šūnu (mēs izvēlējāmies šūnu I14) kā kolonnas ievades šūna. Pēc noklikšķināšanas uz Labi, programma Excel simulē 1000 pieprasījuma vērtības par katru pasūtījumu daudzumu.

Lai saprastu, kāpēc tas darbojas, apsveriet iespēju ievieto datus tabulas šūnu diapazonā C16:C1015 vērtības. Attiecībā uz katru no šīm šūnām, Excel izmantos 20 000 vērtība šūnā C1. C16, kolonnas ievades šūna vērtību 1 novietots tukšu šūnu un nejauši izvēlēts skaitlis šūnā C2 pārrēķina. Atbilstošā peļņas pēc tam tiek ierakstīta C16 šūnā. Pēc tam kolonnas šūnas ievades vērtība 2 ievieto tukšu šūnu un atkal tiek pārrēķināta gadījumskaitli šūnā C2. Atbilstošā peļņas tiek ievadīta C17 šūnā.

Kopējot no šūnā B13 C13:E13 formulu AVERAGE(B16:B1015), mēs aprēķinātu vidējo simulētu peļņu, par katru ražošanas daudzumu. Kopējot no šūnas B14 C14:E14 formulu STDEV(B16:B1015), mēs standartnovirzi mūsu simulētu peļņu, par katru pasūtījumu daudzumu. Ikreiz, kad mēs nospiediet taustiņu F9, 1000 atkārtojumiem pieprasījuma simulētas par katru pasūtījumu daudzumu. Ražo 40 000 kartes vienmēr tiek iegūts lielākais paredzamie peļņas. Tāpēc šķiet, ka ražo 40 000 kartes pareizu lēmumu.

Mūsu lēmumu risku ietekme     Ja mēs izgatavoti 20 000 vietā 40 000 kartes, mūsu paredzamie peļņas tiek noņemtas aptuveni 22 procenti, bet mūsu riska (pēc tādiem peļņas standartnovirzi) tiek noņemtas gandrīz 73 procenti. Tāpēc, ja mēs esam ļoti negatīvās risku, ražo 20 000 kartes varētu būt pareizo lēmumu. Nejauši, ražo 10 000 kartes vienmēr ir standartnovirzi 0 kartes, jo ja mēs gaidītos 10 000 kartes, mēs vienmēr pārdot visas no tām bez jebkura ēdiena.

Piezīme.: Šajā darbgrāmatā aprēķinu opcija ir iestatīta uz Automātiski, izņemot tabulas. (Izmantojiet komandu aprēķinu, cilnes Formulas grupā aprēķins.) Šis iestatījums nodrošina, ka mūsu datu tabula netiek pārrēķinātas, ja vien mēs nospiediet taustiņu F9, kas ir ieteicams, jo lielu datu tabulas palēnina darba, ja tas tiek pārrēķināta ikreiz, kad rakstāt kaut ko, ko savā darblapā. Ņemiet vērā, ka šajā piemērā ikreiz, kad nospiežat taustiņu F9, vidējā peļņa tiks mainīts. Tas notiek tāpēc, ka ikreiz, kad nospiežat taustiņu F9, citā secībā 1000 nejauši izvēlētus skaitļus tiek izmantota, lai ģenerētu prasības attiecībā uz katru pasūtījumu daudzumu.

Ticamības intervālu nozīmē peļņas     Dabiski jautājums lūgt šajā situācijā ir, par kādu intervālu esam 95 procentiem pārliecināts, ka patiess vidējā peļņa kļūs? Šis intervāls tiek dēvēta par 95 procentiem peļņas vidējā ticamības intervālu. Vidējā vērtība ir jebkura imitēšana izvades 95 procentu ticamības intervālu tiek aprēķināta ar šādu formulu:

Book Image

Šūnā J11 aprēķināt apakšējo robežu 95 procentu ticamības intervālu uz vidējais peļņu, kad 40 000 kalendāru ir izgatavoti ar formulas D13–1.96*D14/SQRT(1000). Šūnā J12 svarā aprēķināt mūsu 95 procentu ticamības intervālu ar formulu D13+1.96*D14/SQRT(1000)augšējā robeža. Šie aprēķini tiek rādītas skaitlis 60-7.

Book Image
Skaitlis 60 7 95 procentu ticamības intervālu vidējais peļņas, kad ir sakārtotas 40 000 kalendārus

Mēs esam 95 procentiem pārliecināts, ka mūsu vidējais peļņas, kad ir sakārtotas 40 000 kalendārus ir starp $56,687 un $62,589.

  1. GMC izplatītāja uzskata, ka pieprasījuma 2005 sūtņu tiks normāli sadalīts ar 200 vidējais un standartnovirze ir 30. Viņa saņem sūtnis izmaksas ir $ 25 000, un viņš pārdod sūtnis $ 40 000. Puse no visiem sūtņu pārdots pilnu cenu var pārdot $ 30 000. Viņš ir vērā pasūtīšanas 200, 220, 240, 260, 280 vai 300 sūtņu. Cik viņš būtu kārtībā?

  2. Maza lielveikals mēģina noteikt eksemplāru personas žurnāls tie būtu kārtībā katru nedēļu. Uzskatāt, ka to pieprasījums personām attiecas šādi diskrēto nejaušs mainīgais:

    Pieprasījuma

    Varbūtība

    15

    0,10

    20

    0,20

    25

    0,30

    30

    0,25

    35

    0,15

  3. Lielveikalā maksā 1,00 par katru personu kopiju un pārdod to $1,95. Nepārdotās kopijās var atgriezt $0.50. Cik kopiju personām būtu veikalā secībā?

Vai nepieciešama papildu palīdzība?

Vienmēr varat pajautāt speciālistiem Excel Tech kopienā, saņemt atbalstu Answers kopienā vai ieteikt jaunu līdzekli vai uzlabojumu vietnē Excel User Voice.

Paplašiniet savas Office prasmes
Iepazīties ar apmācību
Esiet pirmais, kas saņem jaunās iespējas
Pievienoties Office Insider programmai

Vai šī informācija bija noderīga?

Paldies par jūsu atsauksmēm!

Paldies par atsauksmēm! Šķiet, ka varētu būt noderīgi sazināties ar kādu no mūsu Office atbalsta aģentiem.

×