Office
Vpis

Uvod v simulacije 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.

V tem članku je povzet Microsoft Excel analize podatkov in modeliranje podjetja tako, da Wayne L. Winston.

  • Kdo so uporabniki simulacije Monte Carlo?

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

  • Kako lahko posnemate vrednosti nepovezan naključna spremenljivka?

  • Kako lahko posnemate vrednosti normalno naključna spremenljivka?

  • Kako lahko voščilnice podjetje določite, koliko kartice, da je rezultat?

Bi radi natančno oceno verjetnosti sprašuje dogodke. Na primer, kaj je verjetnost, da bo nov izdelek denarnih pretokov imajo pozitivno neto sedanjo vrednost (NPV)? Kaj je faktorju tveganja naše naložbe portfelja? Simulacije Monte Carlo nam omogoča modela situacijah, ki predstavitev negotovost in nato predvajate jih v računalniku tisoč krat.

Opomba: Ime simulacije Monte Carlo prihaja iz računalniške simulacije, ki se izvaja med 1930 in 1940s ocenite verjetnost, da bi odziv veriga, potrebne za za atom bomb eksplozivnosti uspešno delo. Fizikov, vključenih v to delo so bili veliki ventilatorji hazardiranje, tako, da so dali simulacije Monte Carloime kode.

V naslednjih sedem poglavij, boste videli primeri, kako lahko uporabite Excel za izvajanje Monte Carlo simulacije.

Veliko podjetja uporabiti simulacije Monte Carlo kot pomemben del postopka odločanja. Tukaj je nekaj primerov.

  • General Motors, Proctor in Gamble, Pfizer, Bristol Myers Squibb in Eli Lilly s simulacijo ocenite povprečno nazaj in faktorju tveganja novih izdelkov. Na spletnem mestu GM, te informacije se uporablja z direktor za določanje, kateri izdelki so na trgu.

  • GM uporablja simulacijo za dejavnosti, kot so predvidevanje neto dohodek za corporation, napovedovanje strukturne in nakupu stroški in določanje občutljivosti za različne vrste tveganja (na primer obrestna mera spremembe in exchange tečajev).

  • Lilly določi simulacijo tovarne za optimalno zmogljivost za vsako zdravila.

  • Proctor in Gamble uporablja simulacije modela in optimalno varovanje valutno tveganje.

  • Sears uporabi simulacije, da določite, koliko enot za vsako vrstico izdelka je treba razvrstiti od dobaviteljev, na primer število pare pristaniških hlače, ki naj se naloži to leto.

  • Olje in zdravila podjetja uporabite simulacijo vrednost »realne možnosti« , na primer vrednost možnost za razširitev, pogodbo, ali pa preložite projekta.

  • Finančne načrtovalce s simulacije Monte Carlo določite optimalno naložbe strategije za svoje stranke upokojitev.

Ko vnesete formulo =RAND() v celici, boste dobili število, ki je enako verjetno prevzeti katera koli vrednost med 0 in 1. Tako okoli 25 odstotkov časa, bi vam število manjše ali enako kot 0,25; 10 odstotkov časa, ki naj se število, ki ni vsaj 0,90 in tako naprej. In prikazali delovanje funkcije RAND, si oglejte datoteko Randdemo.xlsx, prikazano na sliki 60-1.

Book Image
Slika 60-1 dokazuje Funkcija RAND

Opomba: Ko odprete datoteko Randdemo.xlsx, ne boste videli isti naključna števila, ki je prikazano na sliki 60-1. Funkcija RAND vedno samodejno med preračunavanjem številke, ki jo ustvari, ko odprete delovni list ali ko vnesete nove podatke v delovnem listu.

Najprej kopirajte iz celice C3 C4:C402 formule =RAND(). Poimenujte obseg C3:C402 podatkov. Nato v stolpcu F, lahko sledite povprečje 400 naključna števila (celic F2) in s funkcijo COUNTIF določite ulomki, ki so med 0 in 0,25, 0,25 in 0,50, 0,50 in 0,75, in 0,75 in 1. Ko pritisnete tipko F9 znova izračunane naključna števila. Obvestilo o povprečja številk, 400 je vedno približno 0,5, in da okoli 25 odstotkov rezultatov v intervale 0,25. Ti rezultati so skladne z definicijo naključno število. Upoštevajte tudi, da so vrednosti, ki ustvari RAND v različnih celicah neodvisno. Na primer, če naključno število, ki so bile ustvarjene v celici C3 je večjega števila (na primer 0,99), nam pove nič o vrednosti drugih naključnih števil, ustvarjeni.

Recimo, da zahtevo za koledar, ki ga ti nepovezan naključna spremenljivka ureja:

Zahtevo

Verjetnost

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

Kako lahko Excel igrajo ali simulacijo, to zahtevo za koledarje večkrat? Trik je povežete vsakega možno vrednost Funkcija RAND z mogoče zahtevo za koledarje. To dodelitev zagotavlja, da bo zahtevo 10.000 pride do 10 odstotkov časa in tako dalje.

Zahtevo

Naključno število, ki so dodeljene

10.000

Manj kot 0,10

20.000

Večje ali enako kot 0,10 in manj kot 0,45

40.000

Večje ali enako kot 0,45 in manjši od 0,75

60.000

Večje ali enako kot 0,75

Za prikaz simulacije zahtevo, si oglejte datoteko Discretesim.xlsx, prikazano na sliki 60-2 na naslednji strani.

Book Image
Slika 60-2 simulacijo nepovezan naključna spremenljivka

Ključ do naše simulacije je, da uporabite naključno število in začnete iskanje iz tabele obsega F2:G5 (imenovan za iskanje). Naključna števila večji ali enak 0 in manjše od 0,10 donos zahtevo 10.000; naključna števila, večja od ali enaka 0,10 in manj kot 0,45 donos zahtevo 20.000; naključna števila, večja od ali enaka 0,45 in manjši od 0,75 donos zahtevo 40.000; in naključna števila večji ali enak 0,75 donos na zahtevo 60.000. Ustvarite 400 naključna števila tako, da kopirate iz C3 C4:C402 formule višavje(). Nato ustvarite 400 poskusov ali ponovitev zahtevo koledarja tako, da kopirate iz B3 B4:B402 formulo VLOOKUP(C3,lookup,2). Ta formula zagotavlja, da kateri koli naključno število manj kot 0,10 ustvari zahtevo 10.000, kateri koli naključno število med 0,10 in 0,45 ustvari zahtevo 20.000 in tako dalje. V obsegu F8:F11, uporabite funkcijo COUNTIF za določanje ulomek naše 400 ponovitev prinaša vsako zahtevo. Ko bomo pritisnite F9 za vnovični izračun naključna števila, simuliran verjetnosti so bližino naše domnevno zahtevo verjetnosti.

Če vnesete katero koli celico formule NORMINV(rand(),mu,sigma), ustvarite simuliran vrednost normalno naključna spremenljivka ima srednjo mu in standardni odklon sigma. V tem postopku je prikazano v datoteki Normalsim.xlsx, prikazano na sliki 60-3.

Book Image
Slika 60-3 simulacijo normalno naključna spremenljivka

Poglejmo recimo, da želimo simulacijo 400 poskusov ali ponovitev za navaden naključna spremenljivka z 40.000 srednjo vrednost in standardni odklon 10.000 €. (Lahko vnesite te vrednosti v celicah E1 in E2, in ime te celice pomeni in sigmaoziroma.) Kopiranje formule =RAND() iz C4 v C5:C403 ustvari 400 različnih naključna števila. Kopiranje iz B4 B5:B403 formulo NORMINV(C4,mean,sigma) ustvari 400 različnih preskusne vrednosti iz običajnih naključna spremenljivka z 40.000 srednjo vrednost in standardni odklon 10.000 €. Ko bomo pritisnite tipko F9 za vnovični izračun naključna števila, srednjo vrednost ostane bližino 40.000 in standardni odklon bližino 10.000.

V glavnem, za naključno število x, formule NORMINV(p,mu,sigma) ustvari pti percentil normalno naključna spremenljivka z srednjo mu in standardni odklon sigma. Na primer (glejte slika 60-3) ustvari naključno število 0,77 v celici C4 v celico B4 približno 77. percentil normalno naključna spremenljivka z 40.000 srednjo vrednost in standardni odklon 10.000 €.

V tem razdelku, boste videli, kako lahko uporabite Monte Carlo simulacije kot orodje za sprejemanje odločitev. Recimo, da zahtevo za Valentinovo kartice ureja to nepovezan naključna spremenljivka:

Zahtevo

Verjetnost

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

Voščilnice prodaja za 4,00 $ in spremenljivko stroškov proizvodnje posamezni kartici je 1,50 $. Ostanek kartic treba odstraniti po ceni 0,20 $ kartico. Koliko kartice naj natisnjene?

V bistvu, smo posnemate vsako proizvodnja količino (10.000 20.000, 40.000 in 60.000) večkrat (na primer 1000 ponovitev). Nato bomo določi, kateri vrstni red količina posredujeta največje povprečni dobiček več kot 1000 ponovitev. Za ta odsek v datoteki Valentine.xlsx, prikazano na sliki 60-4, lahko poiščete podatke. Obseg imena v celicah B1:B11 dodelite C1:C11 celice. Obseg celic G3:H6 je dodeljena ime za iskanje. Naše prodajno ceno in stroški parametrov, ki so vpisani v C4:C6 celice.

Book Image
Slika 60-4 Valentinovo kartice simulacijo

Preskus proizvodnje količina (40.000 v tem primeru) lahko vnesete v celico C1. Nato ustvarite naključno število v celici C2 formule =RAND(). Kot je opisano zgoraj, jih posnemate zahtevo za kartico v celici C3 s formulo VLOOKUP(rand,lookup,2). (V formulo VLOOKUP rand je ime celice, ki so dodeljeni celice C3, ne Funkcija RAND.)

Število prodanih enot, ki je manjša od naših količino proizvodnje in zahtevo. V celico C8 izračunate naše prihodek s formulo MIN (proizvaja, zahtevo) * unit_price. V celici C9, izračunate celotne proizvodnje stroški s formulo izdelanih * unit_prod_cost.

Če smo pridelek več kartic, kot so na zahtevo, število enot, ostane enaka proizvodnje minus zahtevo; v nasprotnem primeru so ostane brez enote. Izračun smo naše odstranjevanje stroškov v celico C10 s formulo unit_disp_cost * IF (izdelanih > zahtevo, izdelanih – zahtevo, 0). Na koncu v celici C11 smo izračunati naše dobiček kot prihodek – total_var_cost total_disposing_cost.

Bi radi učinkovit način, da pritisnete F9 večkrat (na primer 1000) za vsako količino proizvodnje in ujemajo naše pričakovani dobiček za vsako količino. To stanje je ena pri katerem dvosmerno podatkovna tabela je na naše reševanje. (Preberite poglavje 15, »Občutljivost analize s podatkovnimi tabelami« podrobnosti o podatkovne tabele.) Podatkovne tabele, ki se uporabljajo v tem primeru je prikazano na sliki 60-5.

Book Image
Slika 60-5 dvosmerno podatkovno tabelo za voščilnice simulacijo

Obseg celic A16:A1015 vnesite številke 1-1000 (ustreza naše 1000 poskusi). En preprost način za ustvarjanje te vrednosti, je, da začnete z vnosom 1 v celici A16. Izberite celico, in nato v oknu »Domov« v skupini Urejanje kliknite polniloin izberite niz za prikaz pogovornega okna niz . V pogovornem oknu niz , prikazano na sliki 60-6, vnesite vrednost koraka 1 in ustavitev vrednost 1000. V območju Nizov vstolpcih možnost izberite, in kliknite v redu. Števila 1 – 1000 bodo vnesli v stolpcu A se začne v celici A16.

Book Image
Slika 60-6 z nizom pogovornem oknu izpolnite preskusno številke 1 do 1000

Nato bomo vnesite naše proizvodnja količine (10.000 20.000, 40.000, 60.000) v celicah B15:E15. Želite, da izračunate dobiček za vsako preskusno številko (od 1 do 1000) in količino vsakega proizvodnje. Smo se nanašajo na formulo za dobiček (v izračunano celico C11) v zgornjo levo celico naše podatkovno tabelo (A15) z vnosom = C11.

Zdaj smo pripravljeni na trik Excel v simulacijo 1000 ponovitev zahtevo za vsako količino proizvodnje. Izberite obseg za tabelo (A15:E1014), in nato v skupini Podatkovna orodja na zavihku »podatki« kliknite Analiza» kaj če« in izberite podatkovno tabelo. Če želite nastaviti dvosmerno podatkovno tabelo, izberite količino naše proizvodnje (celica C1) kot celica vnosne vrstice in izberite katero koli prazno celico (smo izbrali celice I14) kot stolpec vhodni celici. Ko kliknete v redu, Excel simulira 1000 zahtevo vrednosti za vsako količino vrstnem redu.

Če želite razumeti, zakaj to deluje, razmislite o vrednosti, ki jih podatkovne tabele v obseg celic C16:C1015. Za vsako od teh celic, bo Excel uporabil 20.000 vrednost v celici C1. V C16, je vrednost Vhodna celica stolpca 1 postavljeno v prazno celico in naključno število v celici C2 izračuna. Ustreznih dobiček je nato zapisana v celici C16. Nato celico stolpca vhodno vrednost 2 je postavljeno v prazno celico in znova med preračunavanjem naključno število v celici C2. V celici C17 vnesete ustrezne dobiček.

Tako, da kopirate iz celice B13 za C13:E13 formulo AVERAGE(B16:B1015), smo izračunati povprečno simuliran dobiček za vsako količino proizvodnje. Tako, da kopirate iz celice B14 v C14:E14 formulo STDEV(B16:B1015), smo izračunati standardni odklon naše simuliran prihodkov vsako količino vrstnem redu. Vsakič, ko smo pritisnite F9, 1000 ponovitev zahtevo so simuliran za vsako količino vrstnem redu. Izdelavo 40.000 kartice vedno donos največji pričakovani dobiček. Zato je videti, da proizvodnjo 40.000 kartice ustrezna odločitev.

Vpliv tveganja našo sklepa     Če smo izdelanih 20.000 namesto 40.000 kartice, naše pričakovani dobiček izpusti približno 22 odstotkov, vendar naše tveganja (kot je glede na standardni odklon dobiček) izpusti skoraj 73 odstotkov. Zato, če smo zelo nenaklonjen tveganju, 20.000 kartice za proizvodnjo morda pravo odločitev. Sicer, proizvodnjo 10.000 kartic vedno je standardni odklon 0 kartic, ker če smo pridelek 10.000 kartic, smo vedno prodaja njih brez poljuben leftovers.

Opomba: V tem delovnem zvezku, možnosti izračuna nastavljen na Samodejno razen tabel. (Uporabite ukaz »izračun« v skupini izračun na zavihku formule.) Ta nastavitev zagotavlja, bo naše podatkovne tabele ne preračunajo, razen če smo pritisnite F9, kar je dobro, ker velike podatkovne tabele z bo upočasnite svojega dela, če ga izračuna vsakič natipkate nekaj, kar v delovnem zvezku. Upoštevajte, da v tem primeru, ko pritisnete F9, pomeni dobiček bo spremembo. To se zgodi, ker vsakič, ko pritisnete F9, drugo zaporedje 1000 naključnih števil, ki se uporablja za ustvarjanje zahteve za vsako količino vrstnem redu.

Interval zaupanja za pomeni dobiček     V naravnem vprašanje v tem primeru je, v katere intervala ali smo 95 odstotkov prepričajte, da velja srednjo dobiček uvrščen? V tem obdobju se imenuje 95 odstotkov interval zaupanja za srednjo dobiček. Interval zanesljivosti 95 odstotkov za srednjo vrednost simulacijo izhoda je izračunana po naslednji formuli:

Book Image

V celici J11 izračunate spodnjo mejo za interval zaupanja 95 odstotkov v srednjo dobiček, ko 40.000 koledarjev je izdelanih s formulo D13–1.96*D14/SQRT(1000). V celici J12 s težo izračunate zgornja meja naše interval zaupanja 95 odstotkov s formulo D13+1.96*D14/SQRT(1000). Ti izračuni so prikazani v sliki 60-7.

Book Image
Slika 60-7 95 odstotkov interval zaupanja za srednjo dobiček, ko 40.000 koledarjev razvrstitve

Smo 95 odstotkov naše srednjo dobiček, ko 40.000 koledarjev razvrstitve je med 56,687 $ in $62,589.

  1. GMC trgovec meni, da zahteva za 2005 odposlanci normalno porazdeljen s 200 srednjo vrednost in standardni odklon 30. Njegov strošek sprejemanja odposlanca je 25.000 in je prodaja odposlanca za 40.000. Polovico vseh odposlanci ne prodaja na polno ceno lahko prodal za 30.000 $. Namerava vrstnim redom 200, 220, 240, 260, 280 ali 300 odposlanci. Koliko je naročiti?

  2. Majhna trgovinah poskuša določite, koliko kopij ljudje revije se naročiti vsak teden. Menijo, da svoje zahtevo za ljudi, ki urejajo te nepovezan naključna spremenljivka:

    Zahtevo

    Verjetnost

    15

    0,10

    20

    0,20

    25

    0,30

    30

    0,25

    35

    0,15

  3. Trgovinah izplačuje 1,00 $ za vsako kopijo ljudi in prodaja za $1.95. Vsako neprodane kopijo lahko vrne za 0.50. Število kopij oseb, ki bi v trgovini vrstnem redu?

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.

×