Uvod v simulacijo Monte Carlo v Excelu

Opomba: Najnovejšo vsebino pomoči v vašem jeziku vam želimo zagotoviti v najkrajšem možnem času. Ta stran je bila prevedena z avtomatizacijo in lahko vsebuje slovnične napake ali nepravilnosti. Naš namen je, da bi bila vsebina za vas uporabna. Ali nam lahko na dnu te strani sporočite, ali so bile informacije za vas uporabne? Tukaj je angleški članek za preprosto referenco.

Ta članek je bil prilagojen iz Microsoft Excela Analysis Data in Business modeliranje s strani Wayne L. Winston.

  • Kdo uporablja simulacijo Monte Carlo?

  • Kaj se zgodi, ko vnesete = Rand () v celico?

  • Kako lahko simulirate vrednosti diskretne naključne spremenljivke?

  • Kako lahko simulirate vrednosti navadne naključne spremenljivke?

  • Kako lahko podjetje za voščilnice določi, koliko kartic naj ustvari?

Želeli bi natančno oceniti verjetnost negotovih dogodkov. Kakšna je verjetnost, da bodo denarni tokovi novega izdelka imeli pozitivno neto sedanjo vrednost (NPV)? Kakšen je dejavnik tveganja za naš investicijski portfelj? Simulacija Monte Carlo nam omogoča, da izkažemo situacije, ki predstavljajo negotovost, in jih nato predvajate v računalniku tisočkrat.

Opomba:  Simulacija imena Monte Carlo izhaja iz računalniških simulacij, izvedenih v obdobju 1930 in 1940, da oceni verjetnost, da bo verižna reakcija, ki je potrebna za eksplozijo atomske bombe, uspešno delovala. Fiziki, ki so bili vključeni v to delo, so bili veliki ljubitelji iger na srečo, zato so v simulacijah imenovali ime Monte Carlo.

V naslednjih petih poglavjih boste videli primere, kako lahko s programom Excel izvajate simulacije Monte Carlo.

Mnoga podjetja uporabljajo simulacijo Monte Carlo kot pomemben del procesa sprejemanja odločitev. Tukaj je nekaj primerov.

  • General Motors, Proctor and Gamble, Pfizer, Bristol-Myers Squibb in eli Lilly uporabite simulacijo za oceno povprečnega donosa in faktorja tveganja za nove izdelke. Pri GM-ju uporablja GENERALni direktor za določanje, kateri izdelki prihajajo na trg.

  • GM uporablja simulacijo za dejavnosti, kot so predvidevanje čistega dohodka za korporacijo, napovedovanje strukturnih in nakupnih stroškov ter določanje dovzetnosti za različne vrste tveganja (kot so spremembe obrestne mere in nihanja deviznih tečajev).

  • Lilly uporablja simulacijo za določanje optimalne zmogljivosti rastlin za vsako drogo.

  • Proctor and Gamble uporablja simulacijo za model in optimalno varovanje tujega valutnega tveganja.

  • Sears uporablja simulacijo, da določi, koliko enot posamezne linije izdelkov je treba naročiti pri dobaviteljih, na primer število parov hlač, ki jih je treba naročiti v letošnjem letu.

  • Naftna in druga podjetja uporabljajo simulacijo za vrednost» realne možnosti «, kot je na primer vrednost možnosti za razširitev, pogodbe ali preložitev projekta.

  • Finančni načrtovalci uporabljajo simulacijo Monte Carlo za določitev optimalnih naložbenih strategij za upokojitev svojih strank.

Ko vnesete formulo = Rand () v celici, dobite število, ki je enako verjetno, da bo prevzelo katero koli vrednost med 0 in 1. Tako je okoli 25 odstotkov časa treba dobiti število, ki je manjše ali enako 0,25; približno 10 odstotkov časa morate dobiti številko, ki je vsaj 0,90 in tako dalje. Če želite prikazati, kako deluje funkcija RAND, si oglejte datoteko Randdemo. xlsx, ki je prikazana na sliki 60-1.

Book Image

Opomba:  Ko odprete datoteko Randdemo. xlsx, ne boste videli istih naključnih števil, prikazanih na sliki 60-1. Funkcija RAND vedno samodejno preračuna števila, ki jih ustvari, ko je delovni list odprt ali ko so nove informacije vnesene na delovni list.

Najprej kopirajte iz celice C3 na C4: C402 formula = Rand (). Nato poimenujte obseg C3: C402 Data. Nato lahko v stolpcu F sledite povprečju naključnih števil 400 (celica F2) in uporabite funkcijo COUNTIF, da določite ulomke, ki so med 0 in 0,25, 0,25 in 0,50, 0,50 in 0,75 ter 0,75 in 1. Ko pritisnete tipko F9, se naključne številke znova izračunajo. Upoštevajte, da je povprečje številk 400 vedno približno 0,5 in da je približno 25 odstotkov rezultatov v intervalih 0,25. Ti rezultati so skladni z definicijo naključnega števila. Upoštevajte tudi, da so vrednosti, ki jih je ustvaril RAND v različnih celicah, neodvisne. Če je na primer naključna številka, ustvarjena v celici C3, Velika številka (na primer 0,99), nam pove nič o vrednosti drugih ustvarjenih naključnih števil.

Recimo, da je povpraševanje po koledarju urejeno s to diskretno naključno spremenljivko:

Zahtevo

verjetnost

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

Kako lahko Excel predvaja ali simulira to zahtevo za koledarje mnogokrat? Trik je v tem, da povežete vsako možno vrednost funkcije RAND z morebitno zahtevo za koledarje. Ta dodelitev zagotavlja, da bo zahteva 10.000 nastala 10 odstotkov časa in tako dalje.

Zahtevo

Dodeljena naKljučna številka

10.000

Manj kot 0,10

20.000

Večja ali enaka 0,10 in manjša od 0,45

40.000

Večja ali enaka 0,45 in manjša od 0,75

60.000

Večje ali enako kot 0,75

Če želite prikazati simulacijo povpraševanja, si oglejte datoteko Discretesim. xlsx, ki je prikazana na sliki 60-2 na naslednji strani.

Book Image

Ključ do simulacije je, da uporabite naključno število za začetek iskanja iz obsega tabele F2: G5 (imenovano Iskanje). NaKljučna števila, večja od ali enaka 0 in manjša od 0,10, bodo prinesla zahtevo 10.000; naključna števila, večja od ali enaka 0,10 in manjša od 0,45, bodo prinesla zahtevo 20.000; naključna števila, večja od ali enaka 0,45 in manjša od 0,75, bodo prinesla zahtevo 40.000; in naključna števila, večja od ali enaka 0,75, bodo prinesla zahtevo 60.000. 400 naključnih števil ustvarite tako, da kopirate od C3 do C4: C402 v formuli Rand (). Nato ustvarite preskuse v storitvi 400 ali iteracije, in sicer tako, da kopirate od B3 do B4: B402 formulo VLOOKUP (C3; lookup; 2). Ta formula zagotavlja, da poljubna naključna številka, ki je manjša od 0,10, ustvari zahtevo za 10.000, katero koli naključno število med 0,10 in 0,45 ustvari zahtevo 20.000 in tako dalje. V obsegu celic F8: F11 uporabite funkcijo COUNTIF, da določite delček naših iteracij 400, ki prinašajo vsako zahtevo. Ko pritisnete F9 za vnovično izračunavanje naključnih števil, so simulirane verjetnosti blizu pričakovanih verjetnosti povpraševanja.

Če v katero koli celico vnesete formulo NORMINV (Rand (), mu, Sigma), boste ustvarili simulirano vrednost navadne naključne spremenljivke s srednjo vrednostjo mu in standardnim odklonom Sigma. Ta postopek je prikazan v datoteki Normalsim. xlsx, ki je prikazana na sliki 60-3.

Book Image

Recimo, da želimo simulirati 400 poskusov ali iteracij za navadno naključno spremenljivko s srednjo vrednost 40.000 in standardnim odklonom 10.000. (Te vrednosti lahko vnesete v celice E1 in E2 in poimenujete te celice Mean in Sigma.) Kopiranje formule = Rand () od C4 do C5: C403 generira 400 različnih naključnih števil. Kopiranje iz B4 v B5: B403 formula NORMINV (C4, Mean, Sigma) generira 400 različnih preskusnih vrednosti iz navadne naključne spremenljivke s srednjo vrednostjo 40.000 in standardnim odklonom 10.000. Ko pritisnete tipko F9 za vnovično izračunavanje naključnih števil, je srednja vrednost še vedno blizu 40.000 in standardni odklon blizu 10.000.

V bistvu, za naključno število x, formula NORMINV (p, mu, Sigma) ustvari pth PERCENTILE navadne naključne spremenljivke s srednjo in standardnim odklonom Sigma. Na primer, naključno število 0,77 v celici C4 (glejte sliko 60-3) ustvari v celici B4 približno 77th PERCENTILE navadne naključne spremenljivke s srednjo številko 40.000 in standardnim odklonom 10.000.

V tem razdelku boste videli, kako je simulacija Monte Carlo mogoče uporabiti kot orodje za sprejemanje odločitev. Recimo, da je povpraševanje po voščilnicah za Valentinovo urejeno s to diskretno naključno spremenljivko:

Zahtevo

verjetnost

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

Voščilnica se proda za $4,00 in Spremenljivi stroški izdelave posamezne kartice so $1,50. Ostanke kartic je treba odstraniti pri stroških $0,20 na kartico. Koliko kartic je treba natisniti?

V bistvu simuliramo vsako morebitno proizvodno količino (10.000, 20.000, 40.000 ali 60.000) mnogokrat (na primer v primeru 1000). Nato določimo, katero količino naročila dobimo kot največji povprečni dobiček v 1000 iteracijah. Podatke za ta razdelek najdete v datoteki Valentine. xlsx, ki je prikazana na sliki 60-4. Določite imena obsegov v celicah B1: B11 v celice C1: C11. Obseg celic G3: H6 je dodeljen imenu iskanja. Parametri prodajne cene in stroškov so vneseni v celice C4: C6.

Book Image

V celico C1 lahko vnesete preskusno količino proizvodnje (40.000 v tem primeru). Nato ustvarite naključno število v celici C2 s formulo = Rand (). Kot je bilo že opisano, simulirate zahtevo za kartico v celici C3 s formulo VLOOKUP (Rand, LOOKUP, 2). (V formuli VLOOKUP je Rand ime celice, dodeljeno celici C3, in ne funkcija Rand.)

Število prodanih enot je manjše od naše količine proizvodnje in povpraševanja. V celici C8 Izračunajte naš prihodek s formulo min (proizvedeno, povpraševanje) * unit_price. V celici C9 izračunate skupne proizvodne stroške s formulo, ki je bila proizvedena * unit_prod_cost.

Če Izdelujemo več kartic, kot so povpraševanja, je število enot, ki ostanejo v enaki proizvodnji, zmanjšano na zahtevo; sicer nobena enota ni več na voljo. Stroške odtujitve izračunamo v celici C10 s formulo unit_disp_cost * IF (produced>demand, proizvedeno – Demand; 0). Končno, v celici C11 izračunavamo naš dobiček kot prihodek – total_var_cost-total_disposing_cost.

Najučinkovitejši način, da večkrat pritiskamo F9 (na primer 1000) za vsako količino proizvodnje in za vsako količino pričakujemo pričakovani dobiček. Ta situacija je tista, v kateri Dvosmerna podatkovna tabela prihaja na našo rešitev. (Glejte poglavje 15, "analiza občutljivosti s podatkovnimi tabelami" za podrobnosti o podatkovnih tabelah.) Podatkovna tabela, uporabljena v tem primeru, je prikazana na sliki 60-5.

Book Image

V obsegu celic A16: A1015 vnesite številke 1 – 1000 (ki ustrezajo našim 1000 poskusom). Te vrednosti lahko preprosto ustvarite tako, da začnete tako, da vnesete 1 v celico A16. Izberite celico in nato na zavihku osnovno v skupini Urejanje kliknite Polnilo in izberite nizi za prikaz pogovornega okna» nizi «. V pogovornem oknu niz , prikazan na sliki 60-6, vnesite vrednost koraka 1 in vrednost ustavitve 1000. V polju nizi v območju izberite možnost stolpci in nato kliknite v redu. Številke 1 – 1000 bodo vnesene v stolpec A, ki se začne v celici A16.

Book Image

Nato vnesemo naše možne proizvodne količine (10.000, 20.000, 40.000, 60.000) v celicah B15: E15. Dobiček moramo izračunati za vsako preskusno število (od 1 do 1000) in vsako količino proizvodnje. Sklicujemo se na formulo za dobiček (izračunano v celici C11) v zgornji levi celici naše podatkovne tabele (A15), tako da vnesete = C11.

Zdaj smo pripravljeni na Trick Excel v simulacijo 1000 iteracij povpraševanja za vsako količino proizvodnje. Izberite obseg tabele (A15: E1014), nato pa v skupini Podatkovna orodja na zavihku podatki kliknite kaj če analiza in nato izberite podatkovna tabela. Če želite nastaviti dvosmerno podatkovno tabelo, izberite našo količino proizvodnje (celica C1) kot vhodno celico vrstice in izberite katero koli prazno celico (izbrali smo celico I14) kot vhodno celico stolpca. Ko kliknete v redu, Excel simulira vrednosti zahteve za 1000 za vsako količino naročila.

Če želite razumeti, zakaj to deluje, razmislite o vrednostih, ki jih je postavila podatkovna tabela v obsegu celic C16: C1015. Za vsako od teh celic bo Excel uporabil vrednost 20.000 v celici C1. V C16 je vrednost vhodne celice stolpca» 1 «postavljena v prazno celico, naključno število pa se v celici C2 znova izračuna. Ustrezni dobiček se nato posname v celici C16. Nato je vhodna vrednost celice stolpca 2 nameščena v prazno celico, naključno število v celici C2 pa znova izračuna. Ustrezen dobiček je vnesen v celico C17.

S kopiranjem iz celice B13 v C13: E13 formula AVERAGE (B16: B1015), izračunamo povprečje simuliranega dobička za vsako proizvodno količino. S kopiranjem iz celice B14 v C14: E14 formula STDEV (B16: B1015), izračunamo standardni odklon našega simuliranega dobička za vsako količino naročila. Vsakič, ko pritisnete F9, se simulirajo 1000 iteracije povpraševanja za vsako količino naročila. Proizvodnja 40.000 kartic vedno donosi največji pričakovani dobiček. Zato se zdi, da je priprava 40.000 Cards pravilna odločitev.

Vpliv tveganja na našo odločitev     Če smo proizvedli 20.000 namesto kartic 40.000, naš pričakovani dobiček pade približno 22 odstotkov, toda naše tveganje (merjeno s standardnim odklonom dobička) pade skoraj 73 odstotka. Če smo zelo nenaklonjeni tveganju, je za proizvodnjo 20.000 kartic morda prava odločitev. Mimogrede, proizvodnja 10.000 kartic ima vedno standardni odklon 0 kartic, ker če izdelujemo 10.000 kartic, bomo vedno vse prodali brez kakršnih koli ostankov.

Opomba:  V tem delovnem zvezku je možnost izračuna nastavljena na samodejno, razen za tabele. (Uporabite ukaz izračun v skupini izračun na zavihku formule.) S to nastavitvijo zagotovite, da naša podatkovna tabela ne bo znova izračunana, razen če pritisnete tipko F9, kar je dobra ideja, saj bo velika podatkovna tabela upočasnila vaše delo, če se znova izračuna vsakič, ko vnesete nekaj v delovni list. Upoštevajte, da bo v tem primeru vsakič, ko pritisnete F9, sprememba povprečnega dobička. To se zgodi, ker vsakič, ko pritisnete F9, se za ustvarjanje zahtev za vsako količino naročila uporabi drugačno zaporedje 1000 naključnih števil.

Interval zaupanja za povprečni dobiček     Naravno vprašanje, ki ga je treba vprašati v tem primeru je, v kolikšnem intervalu smo 95 odstotka prepričani, da bo padec prave povprečnega dobička? Ta interval se imenuje odstotek intervala zaupanja 95 za povprečni dobiček. Interval zaupanja 95 za srednjo vrednost katerega koli simulacijskega izhoda je izračunan s to formulo:

Book Image

V celici J11 izračuna spodnjo omejitev za odstotek intervala zaupanja za 95 na povprečni dobiček, ko so 40.000 koledarji ustvarjeni s formulo D13 – 1.96 * D14/SQRT (1000). V celici J12 izračuna zgornjo omejitev za naš 95 odstotek intervala zaupanja s formulo D13 + 1.96 * D14/SQRT (1000). Ti izračuni so prikazani na sliki 60-7.

Book Image

Smo 95 odstotka prepričani, da je naš povprečni dobiček, ko so naročeni 40.000 koledarji med $56.687 in $62.589.

  1. Trgovec GMC meni, da bodo zahteve za odPoslance za 2005 običajno distribuirane s srednjo vrednost 200 in standardnim odklonom 30. Njegova cena prejemanja posla je $25.000, on pa proda odPoslanca za $40.000. Polovica vseh odPoslancev, ki niso bili prodani po celotni ceni, se lahko proda za $30.000. Razmišlja, da bi naročil 200, 220, 240, 260, 280 ali 300 odPoslance. Koliko jih je treba naročiti?

  2. Majhen supermarket poskuša ugotoviti, koliko kopij revije People bi morali naročiti vsak teden. Verjamejo, da je njihovo povpraševanje po osebah urejeno z naslednjimi diskretnimi naključnimi spremenljivkami:

    Zahtevo

    verjetnost

    15

    0,10

    20

    0,20

    25

    0,30

    30

    0,25

    35

    0,15

  3. Supermarket plača $1,00 za vsako kopijo oseb in jo proda za $1,95. Vsako neprodano kopijo je mogoče vrniti za $0,50. Koliko kopij ljudi naj bi bilo naročilo trgovine?

Potrebujete dodatno pomoč?

Kadar koli lahko zastavite vprašanje strokovnjaku v skupnosti tehničnih strokovnjakov za Excel, pridobite podporo skupnosti Answers ali predlagate novo funkcijo oziroma izboljšavo na spletnem mestu Excel User Voice.

Razširite poznavanje Officea
Oglejte si izobraževanje
Prvi dobite nove funkcije
Pridružite se programu Office Insider

Vam je bila informacija v pomoč?

Zahvaljujemo se vam za povratne informacije.

Zahvaljujemo se vam za povratne informacije. Videti je, da bi vam prišla prav pomoč enega od naših Officeovih agentov za podporo.

×