Ievads par Monte Carlo simulēšanu 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ā.

Šis raksts ir pielāgots no Microsoft Excel datu analīzes un biznesa modelēšanas ar Wayne L. Winston.

  • Kas izmanto Monte Carlo simulāciju?

  • Kas notiek, ja šūnā rakstāt = Rand () ?

  • Kā var simulēt diskrēta nejauša mainīgā vērtības?

  • Kā simulēt parasta nejauša mainīgā vērtības?

  • Kā apsveikuma kartīšu kompānija var noteikt, cik karšu ir jāražo?

Mēs vēlamies precīzāk novērtēt nenoteiktu notikumu iespējamību. Piemēram, kāda ir varbūtība, ka jauna produkta naudas plūsmām būs pozitīva neto pašreizējā vērtība (NPV)? Kāds ir mūsu investīciju portfeļa riska faktors? Monte Carlo simulācija ļauj mums modelēt situāciju, kas rada neskaidrību, un pēc tam atskaņojiet to datorā ar tūkstošiem reižu.

Piezīme.:  Nosaukums Monte Carlo simulācija ir no datora simulācijām, kas veiktas laikā 1930 un 1940, lai novērtētu varbūtību, ka ķēdes reakcija ir nepieciešama, lai varētu detonēt atoma bumbu. Šajā darbā iesaistītie fiziķi bija lieli azartspēļu fani, tāpēc tie piešķīra simulācijas koda nosaukumu Monte Carlo.

Nākamajās piecās nodaļās redzēsit piemērus par to, kā varat izmantot programmu Excel, lai veiktu Monte Carlo simulācijas.

Daudzi uzņēmumi izmanto Monte Carlo simulāciju kā svarīgu lēmumu pieņemšanas procesu. Tālāk ir sniegti daži piemēri.

  • General Motors, Proctor and Gamble, Pfizer, Bristol-Myers Squibb un Eli Lilly izmantojiet simulāciju, lai novērtētu gan vidējo peļņu, gan jauno produktu riska faktoru. Pie GM šo informāciju izPILDDIREKTORs izmanto, lai noteiktu, kuri produkti nonāk tirgū.

  • GM izmanto simulāciju darbībām, piemēram, prognozējot neto ienākumus uzņēmumam, prognozējot strukturālās un iegādes izmaksas, kā arī nosakot tās uzņēmību pret dažādiem riska veidiem (piemēram, procentu likmju izmaiņas un valūtas kursa svārstības).

  • Lilija izmanto simulāciju, lai noteiktu optimālo ražotnes jaudu katrai narkotikai.

  • Proctor and Gamble izmanto simulāciju, lai modelētu un optimāli nodrošinātu ārvalstu valūtas risku.

  • Sears izmanto simulāciju, lai noteiktu, cik katra produkta rindas vienību ir jākārto no piegādātājiem, piemēram, to dokošanas bikšu skaitu, kas ir jāpasūta šogad.

  • Naftas un narkotiku uzņēmumi izmanto simulāciju, lai novērtētu vērtību "reālās opcijas", piemēram, opcijas vērtību, lai izvērstu, slēgtu vai atliktu projektu.

  • Finanšu plānošanas speciālisti izmanto Monte Carlo simulāciju, lai noteiktu optimālas investīciju stratēģijas to klientiem.

Ierakstot formulu = Rand () šūnā, tiek parādīts skaitlis, kas arī var pieņemt jebkuru vērtību no 0 līdz 1. Tādējādi aptuveni 25 procenti no laika ir jāiegūst skaitlis, kas ir mazāks vai vienāds ar 0,25; aptuveni 10 procenti no laika, kad jums jāiegūst skaitlis, kas ir vismaz 0,90, un tā tālāk. Lai parādītu, kā darbojas funkcija RAND, aplūkojiet failu Randdemo. xlsx, kas parādīts 60-1 attēlā.

Book Image

Piezīme.:  Atverot failu Randdemo. xlsx, neredzēsit tos pašus gadījumskaitļu skaitļus, kas tiek rādīti 60-1 attēlā. Funkcija RAND vienmēr automātiski pārrēķina skaitļus, kas tiek ģenerēti, kad darblapa tiek atvērta vai tiek ievadīta jauna informācija darblapā.

Vispirms kopējiet no šūnas C3 līdz C4: C402 formulu = Rand (). Pēc tam nosauciet diapazonu C3: C402 dati. Pēc tam kolonnā F varat reģistrēt 400 gadījumskaitļu vidējo vērtību (šūna F2) un izmantot funkciju COUNTIF, lai noteiktu daļskaitļus, kas ir starp 0 un 0,25, 0,25 un 0,50, 0,50 un 0,75, kā arī 0,75 un 1. Nospiežot taustiņu F9, tiek pārrēķināti Nejaušie numuri. Ņemiet vērā, ka 400 skaitļu vidējā vērtība vienmēr ir aptuveni 0,5, un aptuveni 25 procenti rezultātu ir intervālos no 0,25. Šie rezultāti atbilst gadījuma skaitļa definīcijai. Ņemiet arī vērā, ka funkcijas RAND ģenerētās vērtības dažādās šūnās ir neatkarīgas. Piemēram, ja šūnā C3 ģenerēts gadījumskaitlis ir liels skaitlis (piemēram, 0,99), tas mums neko nerāda par to, ko ģenerējuši citi gadījumskaitļu numuri.

Pieņemsim, ka kalendāra pieprasījumu regulē šāds diskrēts 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 izmantot programmu Excel vai simulēt šo kalendāru pieprasījumu daudz reižu? Triks ir piesaistīt katru iespējamās funkcijas RAND vērtību ar iespējamu kalendāru pieprasījumu. Tālāk sniegtajā uzdevumā tiek nodrošināts, ka 10 000 pieprasījums notiks 10 procenti no laika un tā tālāk.

Pieprasījuma

Piešķirtais gadījumskaitlis

10 000

Mazāks par 0,10

20 000

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

40 000

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

60 000

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

Lai demonstrētu pieprasījuma simulāciju, skatiet failu Discretesim. xlsx, kas redzams nākamajā lapā 60-2. attēls.

Book Image

Mūsu simulācijas atslēga ir izmantot gadījumskaitli, lai uzsāktu uzmeklēšanu no tabulas diapazona F2: G5 (ar nosaukumu Lookup). Nejauši skaitļi, kas ir lielāki vai vienādi ar 0, un mazāks par 0,10, dos pieprasījumu no 10 000. nejaušiem skaitļiem, kas ir lielāki vai vienādi ar 0,10 un mazāki par 0,45, tiek pieprasīta 20 000; nejaušiem skaitļiem, kas ir lielāki vai vienādi ar 0,45 un mazāki par 0,75, tiek pieprasīta 40 000; un dažādi skaitļi, kas ir lielāki par vai vienādi ar 0,75, iegūst pieprasījumu no 60 000. 400 nejaušus skaitļus var ģenerēt, kopējot no C3 līdz C4: C402 formulas rands (). Pēc tam ģenerējiet 400 izmēģinājumu vai atkārtojumus kalendāra pieprasījumam, kopējot no B3 līdz B4: B402 formulu VLOOKUP (C3, LOOKUP, 2). Izmantojot šo formulu, tiek nodrošināts, ka jebkurš Gadījumskaitlis, kas ir mazāks par 0,10, ģenerē 10 000 pieprasījumu, jebkurš Gadījumskaitlis no 0,10 līdz 0,45 izraisa 20 000 pieprasījumu un tā tālāk. Šūnu diapazonā F8: F11 izmantojiet funkciju COUNTIF, lai noteiktu, cik lielā daļa no mūsu 400 atkārtojumiem ir katram pieprasījumam. Kad nospiežat taustiņu F9, lai pārrēķinātu nejauši izvēlētus skaitļus, Simulētās varbūtības ir tuvas mūsu pieņemtajām pieprasījuma varbūtībām.

Ja ierakstāt jebkuru šūnu, formula NORMINV (Rand (), mu, Sigma), ģenerēs parasta gadījuma mainīgā vērtību, kurai ir vidējais mu un standartnovirze Sigma. Šī procedūra ir attēlota failu Normalsim. xlsx formātā, kas parādīts 60-3 attēlā.

Book Image

Pieņemsim, ka mēs vēlamies simulēt 400 mēģinājumus vai iterācijas parastam gadījuma mainīgajam, ar vidējo 40 000 un standartnovirzi 10 000. (Šīs vērtības var ierakstīt šūnās E1 un E2, un attiecīgi nosaukt šīs šūnas un Sigma.) Formulas = Rand () kopēšana no C4 uz C5: C403 ģenerē 400 dažādus gadījumskaitļu numurus. Kopēšana no B4 līdz B5: B403 formula NORMINV (C4, vidējais, Sigma) ģenerē 400 dažādas izmēģinājuma vērtības no parasta gadījuma mainīgā ar vidējo 40 000 un standartnovirzi 10 000. Kad nospiežat taustiņu F9, lai pārrēķinātu nejauši izvēlētus skaitļus, vidējais paliek tuvs 40 000 un standartnovirze ir tuva 10 000.

Būtībā, ja tiek parādīts nejaušs skaitlis x, formula NORMINV (p, mu, Sigma) rada normāla gadījuma mainīgu vērtību ar vidējo mu un standartnovirzi Sigma. Piemēram, gadījumskaitli 0,77 šūnā C4 (skatīt attēlu 60-3) šūnā B4 tiek ģenerēta aptuveni 77. plenārā procentīle no parasta gadījuma mainīgā ar vidējo vērtību 40 000 un standartnovirze ir 10 000.

Šajā sadaļā redzēsit, kā Monte Carlo simulāciju var izmantot kā lēmumu pieņemšanas rīku. Pieņemsim, ka Valentīna dienas kartes pieprasījumu regulē šāds diskrēts 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 par $4,00, un mainīgās izmaksas par katras kartītes ražošanu ir $1,50. Atliekās kārtis ir jāiznīcina ar maksu par $0,20 katrai kartei. Cik kārtis ir jāizdrukā?

Būtībā mēs simulējam katru iespējamo ražošanas apjomu (10 000, 20 000, 40 000 vai 60 000) vairākkārt (piemēram, 1000 iterācijās). Pēc tam mēs noteiksim, kurš pasūtījuma daudzums iegūst maksimālo vidējo peļņu 1000 iterācijās. Šīs sadaļas datus varat atrast failā Valentine. xlsx, kas redzams 60-4 attēlā. Diapazonu nosaukumi ir jāpiešķir šūnās B1: B11 līdz šūnām C1: C11. Šūnu diapazons G3: H6 tiek piešķirts nosaukums Lookup. Mūsu pārdošanas cenas un izmaksu parametri ir ievadīti šūnās C4: C6.

Book Image

Šūnā C1 varat ievadīt izmēģinājuma ražošanas apjomu (40 000 šajā piemērā). Pēc tam izveidojiet nejaušu skaitli šūnā C2 ar formulu = Rand (). Kā iepriekš aprakstīts, varat simulēt kartes pieprasījumu šūnā C3 ar formulu VLOOKUP (Rand, LOOKUP, 2). (VLOOKUP formulā rands ir šūnas nosaukums, kas piešķirts šūnā C3, nevis funkcija Rand.)

Pārdoto vienību skaits ir mazāks par mūsu ražošanas apjomu un pieprasījumu. Šūnā C8 jūs aprēķiniet mūsu ieņēmumus, izmantojot formulu min (radīts, pieprasījums) * unit_price. Šūnā C9 aprēķiniet kopējās ražošanas izmaksas ar formulu, kas ražota * unit_prod_cost.

Ja ražojam vairāk karšu, nekā ir pieprasīts, atlikušais vienību skaits ir vienāds ar ražošanas apjomu mīnus. Pretējā gadījumā nepaliek neviena vienība. Mēs aprēķinām mūsu apglabāšanas izmaksas šūnā C10 ar formulu unit_disp_cost * IF (produced>demand, saražot-Demand, 0). Visbeidzot, šūnā C11 mēs aprēķinām mūsu peļņu kā ieņēmumus — total_var_cost-total_disposing_cost.

Mēs vēlamies efektīvi nospiest taustiņu F9 (piemēram, 1000) katram ražošanas apjomam un atbilst mūsu sagaidāmajai peļņai par katru daudzumu. Šī situācija ir tāda, kurā divu veidu datu tabula ir mūsu glābšanai. (Detalizētu informāciju par datu tabulām skatiet rakstā jutības analīze ar datu tabulām). Šajā piemērā izmantotā datu tabula ir parādīta 60-5 attēlā.

Book Image

Šūnu diapazonā A16: A1015 ievadiet skaitļus 1 – 1000 (atbilst mūsu 1000 izmēģinājumiem). Viens vienkāršs veids, kā izveidot šīs vērtības, ir sākt, ievadot 1 šūnā A16. Atlasiet šūnu un pēc tam cilnes Sākums grupā rediģēšana noklikšķiniet uz Aizpildījums un pēc tam atlasiet sēriju , lai tiktu parādīts dialoglodziņš sērija . Dialoglodziņā sērijas , kas parādīts attēlā 60-6, ievadiet soļa vērtību 1 un 1000 apturēšanas vērtību. Apgabalā sērijas , atlasiet opciju kolonnas un pēc tam noklikšķiniet uz Labi. Skaitļi no 1 līdz 1000 tiks ievadīti kolonnā A, sākot ar šūnu A16.

Book Image

Pēc tam mēs norādīsim mūsu iespējamos ražošanas apjomus (10 000, 20 000, 40 000, 60 000) šūnās B15: E15. Mēs vēlamies aprēķināt peļņu katram izmēģinājuma numuram (no 1 līdz 1000) un katram ražošanas apjomam. Mēs atsaucamies uz peļņas formulu (aprēķināta šūnā C11) mūsu datu tabulas augšējā kreisajā šūnā (A15), ievadot = C11.

Tagad esam gatavi izvilināt Excel uz imitējot 1000 iterāciju par katru ražošanas apjomu. Atlasiet tabulu diapazonu (A15: E1014) un pēc tam cilnes Dati grupā Datu rīki noklikšķiniet uz kā rīkoties, ja analīze un pēc tam atlasiet datu tabula. Lai iestatītu divvirzienu datu tabulu, izvēlieties mūsu ražošanas apjomu (šūna C1) kā rindas ievades šūnu un atlasiet jebkuru tukšu šūnu (izvēlējāties šūnu I14) kā kolonnas ievades šūnu. Pēc noklikšķināšanas uz Labi, Excel simulē 1000 pieprasījuma vērtības katram pasūtījuma daudzumam.

Lai izprastu, kāpēc tas darbojas, apsveriet datu tabulas ievietotās vērtības šūnu diapazonā C16: C1015. Katrai no šīm šūnām Excel izmanto 20 000 vērtību šūnā C1. Programmā C16 kolonnas ievades šūnas vērtība 1 tiek ievietota tukšā šūnā un šūnā C2 tiek parādīts gadījumskaitlis. Attiecīgā peļņa pēc tam tiek ierakstīta šūnā C16. Pēc tam kolonnas šūnas ievades vērtība 2 ir ievietota tukšā šūnā, un gadījumskaitlis atkārtoti tiek pārrēķināts. Atbilstošā peļņa ir ievadīta šūnā C17.

Kopējot no šūnu B13 uz C13:13 formulu AVERAGE (B16: B1015), mēs aprēķinām vidējo simulēto peļņu katram ražošanas apjomam. Kopējot no šūnu B14 uz C14: E14 formulā STDEV (B16: B1015), mēs aprēķinām mūsu Simulētās peļņas standartnovirzi katram pasūtījuma daudzumam. Katru reizi, kad tiek nospiests taustiņš F9, 1000 pēc pieprasījuma tiek simulēts katram pasūtījumu daudzumam. Veidojot 40 000 kārtis, tās vienmēr iegūst lielāko gaidāmo peļņu. Tādējādi tiek parādīts, ka 40 000 karšu izveide atbilst pareizajam lēmumam.

Riska ietekme uz mūsu lēmumu     Ja mēs ražojām 20 000, nevis 40 000 kartes, mūsu paredzētā peļņa samazināsies aptuveni 22 procenti, taču mūsu riski (ko mēra pēc peļņas standartnovirzes), samazinās gandrīz 73 procenti. Tāpēc, ja mums ir ļoti nelabvēlīgas attiecībā uz risku, ir iespējams pieņemt pareizo lēmumu par 20 000 kartēm. Nejauši, ražojot 10 000 kartes, vienmēr ir 0 kāršu standartnovirze, jo, ja izveidojam 10 000 kartes, mēs vienmēr to pārdosim bez atlikumu.

Piezīme.:  Šajā darbgrāmatā aprēķināšanas opcija ir iestatīta uz automātiski, izņemot tabulās. (Izmantojiet komandu aprēķins cilnes Formulas grupā aprēķins.) Šis iestatījums nodrošina, ka mūsu datu tabula netiks pārrēķināta, ja vien nebūsim spiests taustiņu F9, jo tā ir laba ideja, jo liela datu tabula palēnina darbu, ja tā tiek pārrēķināta ikreiz, kad kaut ko ierakstāt savā darblapā. Ņemiet vērā, ka šajā piemērā, kad nospiežat taustiņu F9, vidējā peļņa mainīsies. Tas notiek tāpēc, ka ikreiz, kad nospiežat taustiņu F9, tiek izmantota atšķirīga 1000 gadījumskaitļu numerācijas secība.

Ticamības intervāls vidējai peļņai     Šajā situācijā ir dabisks jautājums, par kuru intervālu mēs esam 95 procenti, pārliecināti, ka patiesais vidējais ienesīgums kritīsies? Šo intervālu dēvē par 95 procentu ticamības intervālu vidējai peļņai. 95 procentu ticamības intervālu visām simulācijas izvadē vidējam aprēķina ar šādu formulu:

Book Image

Šūnā J11, izmantojot formulu D13-1.96 * D14/SQRT (1000), varat aprēķināt zemāko ticamības intervāla limitu par 95 procentiem. Šūnā J12 aprēķina maksimālo ticamības intervālu, izmantojot formulu D13 + 1.96 * D14/SQRT (1000). Šie aprēķini ir parādīti 60-7 attēlā.

Book Image

Mēs esam 95 procenti, pārliecināti, ka mūsu vidējā peļņa, kad tiek sakārtoti 40 000 kalendāri, ir starp $56 687 un $62 589.

  1. GMC tirgotājs uzskata, ka 2005 sūtņu pieprasījums parasti tiek izplatīts ar vidējo 200 un standartnovirze ir 30. Šī sūtņa saņemšanas izmaksas ir $25 000, un viņš pārdod sūtni for $40 000. Par $30 000 ir iespējams pārdot pusi no visiem sūtņiem, kas nav pārdoti par pilnu cenu. Viņš apsver 200, 220, 240, 260, 280 vai 300 sūtņu pasūtīšanu. Cik viņam ir jākārto?

  2. Mazs lielveikals cenšas noteikt, cik daudz personu Magazine kopiju tie ir jākārto katru nedēļu. Viņi uzskata, ka viņu pieprasījums cilvēkiem ir pakļauts tālāk norādītajam diskrētam gadījuma mainīgajam:

    Pieprasījuma

    Varbūtība

    15

    0,10

    20

    0,20

    25

    0,30

    30

    0,25

    35

    0,15

  3. Lielveikals maksā $1,00 katrai personu kopijai un pārdod to programmai $1,95. Visi Nepārdotie eksemplāri var tikt atgriezti $0,50. Cik lietotāju kopijām ir jābūt krātuves pasūtījumam?

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.

×