Põhisisu juurde

Monte Carlo simulatsiooni tutvustus Excelis

See artikkel on kohandatud Microsoft Exceli andmete analüüsist ja Ärimodelleerimist Wayne L. Winstoni kaudu.

  • Kes kasutab Monte Carlo simulatsiooni?

  • Mis juhtub, kui tipite lahtrisse = rand () ?

  • Kuidas saab simuleerida üksiku juhusliku muutuja väärtusi?

  • Kuidas saab simuleerida tavaliste juhuslike muutujate väärtusi?

  • Kuidas saab tervituse kaardi ettevõte määrata, mitu kaarti soovite toota?

Soovime täpselt hinnata ebakindlate sündmuste tõenäosust. Millisel juhul on tõenäosus, et uuel toote rahavoogudel on positiivne neto praegune väärtus (NPV)? Mis on meie investeeringute portfelli riskitegur? Monte Carlo simulatsioon võimaldab meil kujundada olukordi, mis kujutavad endast ebakindlust ja mängivad neid arvutis tuhandeid kordi.

Märkus.:  Nimi Monte Carlo simulatsioon pärineb arvuti simulatsioonid, mis viidi läbi 1930 ja 1940, et hinnata tõenäosust, et aatomi pommi plahvatamiseks vajalik ahelreaktsiooni oleks edukalt toiminud. Selle tööga seotud füüsikud olid hasartmängude suured fännid, nii et nad andsid simulatsiooni nime Monte Carlo.

Järgmisest viiest peatükist leiate näited selle kohta, kuidas Excelis saab kasutada Monte Carlo simulatsioone.

Paljud ettevõtted kasutavad Monte Carlo simulatsiooni olulise osana otsuste tegemise protsessist. Siin on mõned näited.

  • General Motors, Proctor ja Gamble, Pfizer, Bristol-Myers Squibb ja ELi Lilly kasutavad simulatsiooni, et hinnata nii keskmise tasuvuse kui ka uute toodete riskitegurit. Kell GM, seda teavet kasutab tegevjuht, et kindlaks teha, millised tooted tulevad turule.

  • GM kasutab simulatsiooni selliste tegevuste jaoks nagu ettevõtte puhastulu prognoosimine, struktuuri-ja ostudokumentide prognoosimine ning selle tundlikkuse määramine erinevatele riskidele (nt intressimäärade muutmine ja vahetuskursi kõikumised).

  • Lilly kasutab simulatsiooni, et määrata kindlaks iga uimasti optimaalne sisseseade maht.

  • Proctor and Gamble kasutab simulatsiooni, et saada mudel ja optimaalselt maandada välisvaluuta riski.

  • Sears kasutab simulatsiooni, et määrata kindlaks, mitu ühikut iga tootesari peaks tarnijatest tellima (nt nende paaride arv, kes peaks sel aastal tellima).

  • Nafta ja narkootikumide firmad kasutavad simulatsiooni väärtuseks "reaalsed suvandid" (nt väärtuse laiendamiseks, lepinguks või projekti edasi lükkamiseks).

  • Finantsplaneerijad kasutavad Monte Carlo simulatsiooni, et määrata kindlaks optimaalne investeerimisstrateegia klientide pensionipõlveks.

Kui tipite lahtrisse valemi = rand () , saate numbri, mis võib sama tõenäoliselt olla vahemikus 0 – 1. Seega, umbes 25 protsenti ajast, tuleks saada arv väiksem või võrdne 0,25; umbes 10 protsenti ajast tuleks saada number, mis on vähemalt 0,90, jne. Funktsiooniga RAND töötamise demonstreerimiseks Heitke pilk faili Randdemo. xlsx, mida kuvatakse joonisel 60-1.

Book Image

Märkus.:  Kui avate Randdemo. xlsx, ei kuvata samu juhuslikke numbreid, mis on näidatud joonisel 60-1. Funktsioon RAND arvutab alati automaatselt selle, kuidas see töölehe avamisel genereeritakse, või kui töölehele sisestatakse uus teave.

Esmalt kopeerige lahtrist C3 kuni C4: C402 valem = rand (). Seejärel nimetage vahemik C3: C402 andmed. Seejärel saate veerus F jälgida 400 juhuslike arvude (lahter F2) keskmist ja kasutada funktsiooni COUNTIF, et määrata, millised fraktsioonid on vahemikus 0 – 0,25, 0,25 ja 0,50, 0,50 ja 0,75 ning 0,75 ja 1. Kui vajutate klahvi F9, arvutatakse juhuslikud numbrid ümber. Pange tähele, et 400 arvude Keskmine on alati umbes 0,5 ja umbes 25 protsenti tulemustest on 0,25. Need tulemused on kooskõlas juhusliku arvu määratlusega. Pange tähele, et RANDi poolt eri lahtrites loodud väärtused on sõltumatud. Kui lahtris C3 genereeritud juhuslik arv on näiteks suur arv (nt 0,99), ei anna see meile midagi teiste loodud juhuslike arvude väärtuste kohta.

Oletame, et kalendri nõudluse reguleerib järgmine diskreetne juhuslik muutuja.

Nõudlus

Tõenäosus

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

Kuidas on võimalik, et Excel mängib või simuleerib mitu korda kalendrit? Trikk on selle funktsiooni RAND iga võimaliku väärtuse seostamine võimalike kalendrite nõudluse vajadusega. Järgmine ülesanne tagab, et 10 000 on 10% ajast jne.

Nõudlus

Määratud on suvaline number

10 000

Väiksem kui 0,10

20 000

Suurem kui 0,10 või sellega võrdne ning väiksem kui 0,45

40 000

Suurem kui 0,45 või sellega võrdne ning väiksem kui 0,75

60 000

Suurem kui 0,75 või sellega võrdne

Nõudluse simuleerimiseks vaadake Discretesim. xlsx, mida kuvatakse järgmisel leheküljel joonisel 60-2.

Book Image

Meie simulatsiooni võtmeks on kasutada juhuslikku numbrit, et algatada otsing tabeli vahemikus F2: G5 (nimega Lookup). Juhuslikud numbrid, mis on suuremad kui 0 või sellega võrdsed ja väiksemad kui 0,10, toovad nõudmise 10 000; juhuslikud numbrid, mis on suuremad kui 0,10 või sellega võrdsed ning väiksem kui 0,45, toovad kaasa 20 000 nõudmise; juhuslikud numbrid, mis on suuremad kui 0,45 või sellega võrdsed ning väiksem kui 0,75, toovad kaasa 40 000 nõudmise; ja juhuslikud arvud, mis on suuremad kui 0,75 või võrdsed, toovad kaasa 60 000 nõudmise. 400 juhuslike arvude genereerimiseks saate kopeerida C3-lt C4-C402: valem rand (). Seejärel genereerite kalendri nõudluse 400 prooviversioonid või Iteratsioonid, kopeerides B3-st B4-i: B402 valem VLOOKUP (C3, lookup, 2). See valem tagab, et mis tahes juhuslik arv, mis on väiksem kui 0,10, tekitab nõudmise 10 000, mis tahes juhuslik arv vahemikus 0,10 ja 0,45, tekitab nõudluse 20 000 jne. Lahtri vahemikus F8: F11 kasutage funktsiooni COUNTIF, et teha kindlaks, kui suur osa meie 400 Iteratsioonid annavad iga nõudluse. Kui vajutate juhuslike arvude ümberarvutamiseks klahvi F9, on simuleeritud tõenäosused meie eeldatava nõudluse tõenäosuse lähedal.

Kui tipite suvalisele lahtrile valemi funktsioon NORMINV (rand (), mu, Sigma), genereeritakse tavalise juhusliku muutuja simuleeritud väärtus, millel on Keskmine mu ja standardhälve Sigma. Seda toimingut illustreerib failis Normalsim. xlsx, mis on näidatud joonisel 60-3.

Book Image

Oletame, et soovite simuleerida 400 prooviversioonid või Iteratsioonid tavalise juhusliku muutuja puhul, mille keskväärtus on 40 000 ja mille standardhälve on 10 000. (Saate tippida need väärtused lahtrites E1 ja E2 ning nimetada need lahtrid keskväärtus ja Sigmavastavalt.) Valemi = rand () kopeerimine C4-st C5-ile: C403 genereerib 400 erinevat juhuslikku numbrit. Kopeerimine B4-st B5-ni: B403 valemit funktsioon NORMINV (C4, keskväärtus, Sigma) genereerib 400 eri prooviversioone tavalisest juhusliku muutujast, mille keskväärtus on 40 000 ja mille standardhälve on 10 000. Kui vajutate juhuslike arvude ümberarvutamiseks klahvi F9, jääb Keskmine 40 000 ja standardhälve lähedane 10 000-ni.

Praktiliselt juhusliku arvu xkorral genereerib valem funktsioon NORMINV (p, mu; Sigma) tavalise juhusliku muutuja p-protsentiili, mille keskväärtus on ja standardhälve muSigma. Näiteks juhuslik arv 0,77 lahtris C4 (vt joonis 60-3) genereerib lahtris B4 ligikaudu 77th protsentiili, mille keskväärtus on 40 000 ja standardhälve 10 000.

Selles jaotises saate teada, kuidas saab Monte Carlo simulatsiooni kasutada otsuste tegemise tööriistana. Oletame, et Ystävänpäivä kaardi nõudluse reguleerib järgmine diskreetne juhuslik muutuja:

Nõudlus

Tõenäosus

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

Õnnitluskaardid müüb $4,00 eest ning iga kaardi tootmise muutuv kulu on $1,50. Allesjäänud kaardid tuleb võõrandada hinnaga $0,20 eurot kaardi kohta. Mitu kaarti peaks printima?

Põhimõtteliselt simuleerime iga võimalikku toodangu kogust (10 000, 20 000, 40 000 või 60 000) mitu korda (nt 1000 Iteratsioonid). Seejärel määratleme, millises järjekorras kogus annab 1000 iteratsioonide suurima keskmise kasumi. Selle jaotise andmed leiate failis Valentine. xlsx, mis on näidatud joonisel 60-4. Määrake vahemiku nimed lahtrites B1: B11 lahtritele C1: C11. Lahtrivahemik G3: H6 on määratud nimi Lookup. Meie müügihind ja kulude parameetrid sisestatakse lahtrites C4: C6.

Book Image

Lahtris C1 saate sisestada prooviversiooni tootmise koguse (40 000 selles näites). Järgmiseks looge lahtris C2 juhuslik arv valemiga = rand (). Nagu varem kirjeldatud, simuleerite nõudlust kaardi järele lahtris C3 valemiga VLOOKUP (rand, lookup, 2). (Funktsiooni VLOOKUP valemis on rand lahtri nimi, mis on määratud lahtrile C3, mitte funktsiooniga rand.)

Müüdavate ühikute arv on väiksem meie toodangu kogusest ja nõudlusest. Lahtris C8 arvutate meie tulud valemiga min (toodetud, nõudluse) * unit_price. Jaotises lahtri C9 arvutatakse tootmise kogukulud, mille valem on toodetud * unit_prod_cost.

Kui toodame rohkem kaarte, kui on nõudlus, võrdub üle jäänud ühikute arv, mis on väiksem kui nõudlus; Vastasel juhul pole üksusi üle jäänud. Arvutame soetusmaksumuse lahtris C10 koos valemiga unit_disp_cost * kui (toodetud>nõudluse, toodetud-Demand, 0). Lõpuks arvutame kasumiaruandes meie kasumit tuludena – total_var_cost-total_disposing_cost. C11.

Soovime, et oleks tõhus viis klahvi F9 vajutada mitu korda (nt 1000) iga toodangu koguse kohta ja prognoosida meie eeldatavat kasumit iga koguse kohta. Selline olukord on see, kus kahesuunaline andmetabel tuleb meie päästmisele. Andmetabelite üksikasjade kohta leiate teavet artiklist 15 "tundlikkuse analüüs andmetabelitega". Selles näites kasutatav andmetabel on kujutatud joonisel 60-5.

Book Image

Sisestage lahtrivahemiku a16: A1015 numbrid 1 – 1000 (vastab meie 1000 Prooviversioonidele). Üks lihtne viis nende väärtuste loomiseks on alustada, kui sisestate lahtris a16 1 . Valige lahter ja klõpsake menüü Avaleht jaotises redigeerimine nuppu täideja valige sari , et kuvada dialoogiboks sari . Sisestage dialoogiboksi sari joonisel 60-6 väärtus 1 ja Stopp-väärtus 1000. Valige jaotises sari suvand veerud ja seejärel klõpsake nuppu OK. Arvud 1 – 1000 sisestatakse veergu A, alustades lahtrist a16.

Book Image

Järgmisena sisestame oma võimalikud kogused (10 000, 20 000, 40 000, 60 000) rakkude B15: E15. Soovime arvutada iga prooviversiooni numbrit (1 kuni 1000) ja iga toodangu kogust. Meie andmetabeli (a15) ülemises vasakpoolses lahtris (arvutatud lahtris C11) leiate valemiga = C11.

Nüüd oleme valmis teesklema, et Excel simuleerib iga koguse nõudluse 1000 iteratsiooni. Valige tabeli vahemik (a15: E1014) ja seejärel klõpsake menüü andmed jaotises andmed nuppu analüüsi ja seejärel valige andmetabel. Kahesuunalise andmetabeli häälestamiseks valige veeru sisestamise lahtrina meie toodangu kogus (lahter C1) ja valige tühi lahter (valisime lahtri I14). Pärast nupu OK klõpsamist simuleerib Excel 1000 nõudluse väärtusi iga tellimuserea puhul.

Kui soovite aru saada, miks see toimib, arvestage, et väärtused on paigutatud tabelisse lahtrivahemik C16: C1015. Iga sellise lahtri korral kasutab Excel lahtris C1 20 000 väärtust. Rakenduses C16 paigutatakse veeru Input lahtri väärtuseks 1 tühja lahtrisse ja lahtris C2 olev juhuslik arv arvutatakse ümber. Vastav kasum kirjendatakse seejärel lahtris C16. Seejärel paigutatakse veeru lahtri Input väärtuseks 2 tühja lahtrisse ja uuesti arvutatakse juhuslik arv lahtris C2. Vastav kasum sisestatakse lahtris C17.

Kui kopeerite lahtrist B13 C13: E13 (B16: B1015), arvutame iga tootmismahu keskmise simuleeritud kasumi. Kopeerides lahtrist B14 C14: E14 valem STDEV (B16: B1015), arvutame iga tellimuserea simuleeritud kasumi standardhälbe. Iga kord, kui vajutate klahvi F9, simuleeritakse 1000 iteratsiooni nõudluse iga tellimuserea kohta. 40 000 kaartide tootmine annab alati suurima eeldatava kasumi. Seetõttu tundub, et 40 000 kaartide tootmine on õige otsus.

Riski mõju meie otsusele     Kui me toodetud 20 000 asemel 40 000 kaardid, meie eeldatav kasum langeb umbes 22 protsenti, kuid meie risk (mõõdetuna standardhälbega kasumit) langeb peaaegu 73 protsenti. Seega, kui oleme äärmiselt tõrksad, võib 20 000 kaartide koostamine olla õige otsus. Muide, 10 000 kaartide tootmisel on alati standardhälve 0 kaarti, sest kui toodame 10 000 kaarti, siis müüme neid alati ilma jäänud kasutamata.

Märkus.:  Selles töövihikus on arvutamise suvand seatud automaatseks peale tabelite. (Kasutage menüü Valemid jaotise kalkulatsioon käsku arvutus.) See säte tagab, et meie andmetabel ei arvutata ümber, välja arvatud juhul, kui vajutate klahvi F9, mis on hea mõte, kuna suur andmetabel vähendab teie tööd, kui see arvutatakse iga kord, kui tipite töölehe midagi. Pange tähele, et selles näites muutuvad keskmised kasumid iga kord, kui vajutate klahvi F9. See juhtub, sest iga kord, kui vajutate klahvi F9, kasutatakse iga tellimuserea vajaduste genereerimiseks erinevaid 1000 juhuslikke numbreid.

Keskmise kasumi usaldusvahemik     Loomuliku küsimuse küsida selles olukorras on, millises intervallis me 95 protsenti kindel, et tegelik keskmine kasum langeb? Selle intervalli nimi on 95 protsenti usaldusvahemik keskmise kasumi korral. Iga simulatsiooni väljundi keskväärtuse 95 protsentide usaldusvahemik arvutatakse järgmise valemi abil.

Book Image

Lahtris J11 arvutatakse keskmise kasumi intervalli 95 protsenti usaldusvahemik, kui 40 000 kalendrid on toodetud valemiga D13 – 1.96 * D14/sqrt (1000). Lahtris J12 arvutate meie 95 protsenti usaldusvahemik ülempiiri, mille valem on D13 + 1.96 * D14/sqrt (1000). Need arvutused kuvatakse joonisel 60-7.

Book Image

Oleme 95 protsenti kindlad, et meie Keskmine kasum, kui 40 000 kalendrid on tellitud, on vahemikus $56 687 ja $62 589.

  1. GMC diiler usub, et 2005 saadikute nõudlust jagatakse tavaliselt keskmisega 200 ja standardhälve 30. Saadikute vastuvõtmise kulud on $25 000 ja ta müüb saadiku $40 000. Pool kõigist saadikutest, keda ei müüda täie hinnaga, saab $30 000 eest müüa. Ta kaalub 200, 220, 240, 260, 280 või 300 saadikute tellimist. Mitu korda peaks ta seda tegema?

  2. Väike supermarket püüab teha kindlaks, kui palju koopiaid inimestest ajakirja nad peaksid iga nädal kordama. Nad usuvad, et nende nõudlust inimeste järele reguleerib järgmine diskreetne juhuslik muutuja.

    Nõudlus

    Tõenäosus

    15

    0,10

    20

    0,20

    25

    0,30

    30

    0,25

    35

    0,15

  3. Supermarket maksab $1,00 iga eksemplari eest ja müüb selle $1,95 eest. Iga müümata koopia saab tagastada $0,50. Kui palju koopiaid inimestest peaks poe järjestuses olema?

Kas vajate rohkem abi?

Võite oma küsimuse alati esitada mõnele Exceli tehnikakogukonna eksperdile, otsida abi vastustefoorumist või soovitada mõnd uut funktsiooni või täiustust Exceli User Voice’i lehel.

Märkus.:  See leht on tõlgitud automaatselt ning sellel võib leiduda grammatikavigu ja ebatäpsusi. Tahame, et sellest sisust oleks teile abi. Andke meile teada, kui see teave oli teile abiks. Soovi korral saate ingliskeelset artiklit lugeda siit.​

Täiendage Office'i kasutamise oskusi
Tutvuge koolitusmaterjalidega
Kasutage uusi funktsioone enne teisi
Liituge Office Insideri programmiga

Kas sellest teabest oli abi?

Täname tagasiside eest!

Täname tagasiside eest! Tundub, et võiksime teid kokku viia ühega meie Office'i tugiagentidest, kes aitab teil probleemi lahendada.

×