See artikkel on kohandatud Microsoft Excel andmeanalüüsi ja ärimudelite põhjal. Wayne L. Winston.

  • Kes? kasutab Monte Carlo simulatsiooni?

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

  • Kuidas jäljendada diskreetse juhusliku muutuja väärtusi?

  • Kuidas saab jäljendada tavalise juhusliku muutuja väärtusi?

  • Kuidas saab tervituskaardifirma määrata, mitu kaarti koostada?

Soovime täpselt hinnata ebamääraste sündmuste tõenäosust. Milline on näiteks tõenäosus, et uue toote rahavoogudel on positiivne nüüdispuhasväärtus (NPV)? Milline on meie investeerimisportfelli riskitegur? Monte Carlo simulatsioon võimaldab meil mudeldada olukordi, mis on ebamäärased, ja seejärel esitada need arvutis tuhandeid kordi.

Märkus.:  Nimi Monte Carlo simulatsioon pärineb 1930. ja 1940. aastatel tehtud arvutisimulatsioonidelt, et hinnata tõenäosust, et aatomipommi detoneerimiseks vajalik ahelreaktsioon töötab edukalt. Selles töös osalevad füüsikud olid suured kihlveofännid, seega andsid nad simulatsioonidele koodinime Monte Carlo.

Järgmises viies peatükis näete näiteid selle kohta, kuidas kasutada Excel Monte Carlo simulatsioone.

Paljud ettevõtted kasutavad Monte Carlo simulatsiooni oma otsustusprotsessi olulise osana. Siin on mõned näited.

  • General Motors, Proctor ja Gamble, Pfizer, Bristol-Myers Squibb ja Eli Lilly kasutavad simulatsiooni, et hinnata nii uute toodete keskmist tasuvust kui ka riskitegurit. GM-is kasutab tegevjuhid seda teavet, et teha kindlaks, millised tooted turule tulevad.

  • GM kasutab simulatsiooni selliste tegevuste jaoks nagu ettevõtte netosissetuleku prognoosimine, struktuuri- ja ostukulude prognoosimine ning selle tundlikkuse määratlemine erinevat tüüpi riskide suhtes (nt intressimäära muutused ja valuutakursside kõikumine).

  • Iga ravimi optimaalse taimemahu määramiseks kasutab Lilly simulatsiooni.

  • Proctor ja Gamble kasutavad simulatsiooni, et modelleerida ja optimaalselt maandada valuutariske.

  • Sears kasutab simulatsiooni, et teha kindlaks, kui palju iga tooterea ühikuid tuleks tarnijatelt tellida (nt dokipükste paaride arv, mis tuleks sel aastal tellida).

  • Nafta- ja narkoettevõtted kasutavad simulatsiooni, et väärtusda "tegelikke valikuid", näiteks projekti laiendamise, lepingu sõlmimise või edasilükkamise võimaluse väärtust.

  • Finantsplaneerijad kasutavad Monte Carlo simulatsiooni, et määrata klientide pensionilejäämiseks optimaalsed investeerimisstrateegiad.

Kui tipite lahtrisse valemi =RAND(), saate arvu, mis eeldab võrdselt mis tahes väärtust vahemikus 0–1. Seega peaks umbes 25% ajast saama arvu, mis on väiksem või võrdne väärtusega 0,25; umbes 10 protsenti ajast, mil peaksite saama arvu, mis on vähemalt 0,90 jne. Funktsiooni RAND toimimise demonstreerimiseks vaadake failivormingut, Randdemo.xlsx joonisel 60-1.

Book Image

Märkus.:  Kui avate faili Randdemo.xlsx, ei kuvata joonisel 60-1 samu juhuslikke arve. Funktsioon RAND arvutab töölehe avamisel või töölehele sisestamisel genereeritud arvud automaatselt ümber.

Esmalt kopeerige lahtrist C3 lahtrisse C4:C402 valem =RAND(). Seejärel nimetage vahemik C3:C402 Andmed. Seejärel saate veerus F jälgida 400 juhusliku arvu keskmist (lahter F2) ja funktsiooni COUNTIF abil määrata murdarvud vahemikus 0–0,25, 0,25 ja 0,50, 0,50 ja 0,75 ning 0,75 ja 1. Klahvi F9 vajutamisel arvutatakse juhuslikud arvud ümber. Pange tähele, et 400 arvu keskmine on alati umbes 0,5 ja umbes 25% tulemitest on 0,25- intervallidega. Need tulemid on kooskõlas juhusliku arvu määratlusega. Võtke arvesse ka seda, et erinevates lahtrites RAND-i loodud väärtused on sõltumatud. Näiteks kui lahtris C3 genereeritud juhuslik arv on suur arv (nt 0,99), ei räägi see meile mitte midagi muude genereeritud juhuslike arvude väärtustest.

Oletagem, et kalendri nõudlust 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 saaksime Excel või jäljendada seda nõudlust kalendrite järele mitu korda? Trikiks on seostada funktsiooni RAND kõik võimalikud väärtused võimaliku nõudega kalendrite järele. Järgmine ülesanne tagab, et 10 000-ne nõue toimub 10 protsenti ajast jne.

Nõudlus

Määratud juhuslik arv

10 000

Väiksem kui 0,10

20 000

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

40,000

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

60 000

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

Nõudluse simulatsiooni näitamiseks vaadake failinime, Discretesim.xlsx järgmisel lehel joonisel 60–2.

Book Image

Meie simulatsiooni võti on kasutada juhuslikku arvu, et algatada otsing tabelivahemikust F2:G5 (nimega otsing). Juhuslik arv, mis on suurem kui 0 või sellega võrdne ja väiksem kui 0,10, annab nõude 10 000; juhuslik arv, mis on suurem kui 0,10 või sellega võrdne ja väiksem kui 0,45, annab nõude 20 000; juhuslikud arvud, mis on suuremad või võrdsed väärtusega 0,45 ja väiksemad kui 0,75, annavad nõude 40 000; ja juhuslikud arvud, mis on suuremad kui 0,75 või sellega võrdsed, annavad tulemuseks 60 000 nõude. Genereerite 400 juhuslikku arvu, kopeerides valemi RAND()valemist C3 lahtrisse C4:C402. Seejärel saate luua 400 kalendrinõudluse proovikatset või iteratsiooni, kopeerides valemist B3 lahtrisse B4:B402 valemi VLOOKUP(C3;lookup;2). See valem tagab, et suvaline juhuslik arv, mis on väiksem kui 0,10, genereerib nõude 10 000, suvaline arv vahemikus 0,10 kuni 0,45 genereerib 20 000 jne. Kasutage lahtrivahemikus F8:F11 funktsiooni COUNTIF, et määrata iga nõudlust andvate 400 iteratsiooni murdosa. Kui vajutame juhuslike arvude ümberarvutamiseks klahvi F9, on simuleeritud tõenäosused meie eeldatavatele nõudluse tõenäosustele lähedased.

Kui tipite mis tahes lahtrisse valemi NORMINV(rand(),mu,sigma),genereerite tavalise juhusliku muutuja simuleeritud väärtuse, millel on keskmine mu ja standardhälve sigma. Seda protseduuri illustreerib Normalsim.xlsx joonisel 60-3.

Book Image

Oletagem, et soovime jäljendada 400 katset või iteratsiooni tavalise juhusliku muutuja jaoks, mille keskmine on 40 000 ja standardhälve on 10 000. (Saate need väärtused tippida lahtritesse E1 ja E2 ning määrata neile lahtritele vastavalt keskmise ja sigma.) Valemi =RAND() kopeerimine C4-st C5:C403-le genereerib 400 erinevat juhuslikku arvu. B4-st B5:B403 kopeerimisel genereerib valem NORMINV(C4;keskmine;sigma) tavalisest juhuslikust muutujast 400 erinevat prooviväärtust keskmisega 40 000 ja standardhälbega 10 000. Kui vajutame juhuslike arvude ümberarvutamiseks klahvi F9, jääb keskmine 40 000-le ja standardhälve on 10 000 lähedal.

Juhusliku arvu xkorral genereerib valem NORMINV(p;mu,sigma) tavalise juhusliku muutuja pth protsentiili keskmise mu ja standardhälbega sigma. Näiteks lahtris C4 genereerib juhuslik arv 0,77 (vt joonis 60–3) lahtris B4 umbes tavalise juhusliku muutuja 77. protsentiili keskmisega 40 000 ja standardhälbega 10 000.

Selles jaotises näete, kuidas Saab Monte Carlo simulatsiooni kasutada otsustamisvahendina. Oletagem, et sõbrapäevakaardi nõudlust 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

Tervituskaart müüb 4,00 $ eest ja iga kaardi tootmise muutuvkulu on 1,50 $. Üle jäänud kaardid tuleb kõrvaldada hinnaga 0,20 $ kaardi kohta. Mitu kaarti tuleks printida?

Põhimõtteliselt jäljendame iga võimalikku tootmiskogust (10 000, 20 000, 40 000 või 60 000) mitu korda (nt 1000 iteratsiooni). Seejärel määratleme, milline tellimuse kogus annab maksimaalse keskmise kasumi 1000 iteratsiooniga. Selle jaotise andmed leiate failifailist, Valentine.xlsx joonisel 60–4. LahtriteS B1:B11 saate lahtritele C1:C11 määrata vahemikunimed. Lahtrivahemikule G3:H6 määratakse nimeotsing. Meie müügihinna- ja kuluparameetrid sisestatakse lahtritesse C4:C6.

Book Image

Lahtrisse C1 saate sisestada proovitootmise koguse (selles näites 40 000). Seejärel looge lahtris C2 juhuslik arv valemiga =RAND(). Nagu eespool kirjeldatud, simuleerite lahtris C3 oleva kaardi nõudlust valemiga VLOOKUP(rand,lookup;2). (Valemis VLOOKUP on rand lahtrile C3 määratud lahtri nimi, mitte funktsioon RAND.)

Müüdud ühikute arv on väiksem meie tootmismahust ja -nõudlusest. Lahtris C8 arvutate meie tulu valemiga MIN(toodetud,nõudlus)*unit_price. Lahtris C9 tuleb arvutada kogu tootmiskulu valemiga, mis on toodetud*unit_prod_cost.

Kui toodame rohkem kaarte, kui on nõudmisel, võrdub üle jäänud ühikute arv tootmisega miinus nõudlus; vastasel korral ei jätta ühtegi ühikut üle. Arvutame oma kõrvaldamismaksumuse lahtris C10 valemiga unit_disp_cost*IF(toodetud>,toodetud–nõudlus;0). Lahtris C11 arvutame oma kasumi tuluna – total_var_cost-total_disposing_cost.

Soovime tõhusat võimalust vajutada F9-d mitu korda (nt 1000) iga tootmiskoguse kohta ja oma eeldatavat kasumit iga koguse kohta. Selline olukord on selline, kus meie päästmiseks tuleb kahetine andmetabel. (Andmetabelite kohta leiate lisateavet peatükist 15 "Tundlikkusanalüüs andmetabelitega".) Selles näites kasutatud andmetabel on kujutatud joonisel 60–5.

Book Image

Sisestage lahtrivahemikku A16:A1015 arvud 1–1000 (vastab meie 1000 proovile). Üks lihtne viis nende väärtuste loomiseks on alustada lahtrisse A16 1 sisestamisega. Valige lahter ja seejärel klõpsake menüü Avaleht jaotises Redigeerimine nuppu Täide javalige dialoogiboksi Sari kuvamiseks Sari. Sisestage dialoogiboksis Sari joonisel 60–6 kujutatud sammväärtus 1 ja 1000 lõpetamisväärtus. Valige jaotises Sarjas väärtusVeerud ja seejärel klõpsake nuppu OK. Arvud 1–1000 sisestatakse veergu A alates lahtrist A16.

Book Image

Järgmisena sisestame lahtritesse B15:E15 oma võimalikud tootmiskogused (10 000, 20 000, 40 000, 60 000). Soovime arvutada iga proovinumbri (1–1000) ja iga tootmiskoguse kasumi. Viitame oma andmetabeli (A15) ülemises vasakpoolses lahtris olevale kasumivalemile (arvutatud lahtris C11), sisestades väärtuse =C11.

Nüüd oleme valmis petma Excel iga tootmiskoguse 1000 iteratsiooni. Valige tabelivahemik (A15:E1014) ja seejärel klõpsake menüü Andmed jaotises Andmeriistad nuppu Mida teha, kui analüüs ja seejärel valige Andmetabel. Kaherealise andmetabeli häälestamiseks valige meie tootmiskogus (lahter C1) reasisestuslahtina ja valige veerusisestuslahtina mis tahes tühi lahter (valisime lahtri I14). Pärast nupu OK klõpsamist Excel simuleerib iga tellimuse koguse jaoks 1000 nõudeväärtust.

Selleks et mõista, miks see toimib, kaaluge väärtusi, mille andmetabel paigutab lahtrivahemikku C16:C1015. Iga lahtri puhul Excel lahtris C1 väärtust 20 000. Lahtris C16 paigutatakse veerusisestuslaht 1 tühja lahtrisse ja lahtris C2 juhuslik arv arvutatakse ümber. Seejärel salvestatakse vastav kasum lahtrisse C16. Seejärel paigutatakse veerulahtise sisendväärtus 2 tühja lahtrisse ja juhuslik arv lahtris C2 arvutatakse uuesti ümber. Vastav kasum sisestatakse lahtrisse C17.

Kopeerides lahtrist B13 lahtrisse C13:E13 valemi AVERAGE(B16:B1015),arvutame iga tootmiskoguse keskmise simuleeritud kasumi. Kopeerides lahtrist B14 lahtrisse C14:E14 valemi STDEV(B16:B1015),arvutame iga tellimuse koguse simuleeritud kasumi standardhälbe. Iga kord, kui vajutame klahvi F9, simuleerib iga tellimuse kogus 1000 iteratsiooni. 40 000 kaardi tootmine annab alati suurima oodatava kasumi. Seetõttu näib, et 40 000 kaardi tootmine on õige otsus.

Riski mõju meie otsusele      Kui tootsime 40 000 kaardi asemel 20 000 kaarti, langeb meie oodatav kasum umbes 22%, kuid meie risk (mõõdetud kasumi standardhälbega) langeb peaaegu 73 protsenti. Seega, kui oleme väga riskantsed, võib 20 000 kaardi tootmine olla õige otsus. Juhuslikult on 10 000 kaardi tootmine alati standardhälbega 0 kaarti, kuna kui toodame 10 000 kaarti, müüme neid kõiki alati ilma üleliidemata.

Märkus.:  Selles töövihikus on suvandi Arvutus väärtuseks seatud Automaatne, v.a tabelite puhul. (Kasutage menüü Valemid jaotises Arvutamine käsku Arvutamine.) See säte tagab, et meie andmetabel ei arvuta ümber enne, kui vajutame klahvi F9, mis on hea mõte, kuna suur andmetabel aeglustab teie tööd, kui see arvutatakse ümber iga kord, kui töölehele midagi tipite. Pange tähele, et selles näites muutub keskmine kasum alati, kui vajutate klahvi F9. See juhtub seetõttu, et iga kord, kui vajutate klahvi F9, kasutatakse iga tellimuse koguse jaoks nõuete genereerimiseks erinevat 1000 juhuarvu.

Keskmise kasumi usaldusvahemik      Selles olukorras on loomulik küsimus, millisesse intervalli me oleme 95% kindlad, et tegelik keskmine kasum langeb? Seda intervalli nimetatakse keskmise kasumi 95- protsendilise usaldusvahemikuks. Mis tahes simulatsiooniväljundi keskmise usaldusvahemik 95% arvutatakse järgmise valemiga:

Book Image

Lahtris J11 arvutatakse keskmise kasumi 95-protsendilise usaldusvahemiku alampiir, kui koostatakse valemiga D13–1,96*D14/SQRT(1000) 40 000 kalendrit. Lahtris J12 arvutatakse meie 95-protsendilise usaldusvahemiku ülempiir valemiga D13+1,96*D14/SQRT(1000). Need arvutused on esitatud joonisel 60-7.

Book Image

Oleme 95% kindlad, et meie keskmine kasum on 40 000 kalendri tellimisel vahemikus 56 687 kuni 62 589 $.

  1. GMO edasimüüja on veendunud, et 2005. aasta saadikute nõudlus jaotatakse tavaliselt keskmisega 200 ja standardhälve on 30. Tema 25 000 $ eest saab 40 000 dollari eest saadiku. Pooled saadustest, mida ei müüda täishinnaga, saab müüa 30 000 $ eest. Ta kaalub 200, 220, 240, 260, 280 või 300 saadiku tellimist. Kui palju peaks ta tellima?

  2. Väike pood proovib kindlaks teha, mitu eksemplari inimesed peaksid iga nädal tellima. Nad arvavad, et nende inimeste nõudmist 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. Selvehall maksab iga inimese eksemplari eest 1,00 $ ja müüb selle 1,95 $ eest. Iga lahendamata koopia saab tagastada 0,50 $ eest. Mitu eksemplari peaks talletamisjärjestuses olema?

Kas vajate rohkem abi?

Kui teil on küsimusi, saate need esitada Exceli tehnikakogukonnafoorumis, kus teile vastavad asjatundjad, või teistele kasutajatele Answersi kogukonnafoorumis.

Kas vajate veel abi?

Täiendage oma oskusi
Tutvuge koolitusmaterjalidega
Kasutage uusi funktsioone enne teisi
Liitu Microsofti Insideri programmis osalejad

Kas sellest teabest oli abi?

Kui rahul te tõlkekvaliteediga olete?
Mis mõjutas teie kasutuskogemust?

Täname tagasiside eest!

×