Uvod u Monte Karlo simulaciju u programu Excel

Napomena:  Želimo da vam što pre pružimo najnoviji sadržaj pomoći čim na vašem jeziku. Ova stranica je prevedena automatski i može da sadrži gramatičke greške ili netačnosti. Naš cilj je da ovaj sadržaj bude koristan. Možete li nam na dnu ove stranice saopštiti da li su vam informacije bile od koristi? Ovo je članak na engleskom jeziku za brzu referencu.

Ovaj članak je prilagođen iz Microsoft Excel analiza podataka i modelovanje Business tako što ćete Wayne L. Winston.

  • Ko to koristi Monte Karlo simulacija?

  • Šta se dešava kada otkucate =RAND() u ćeliji?

  • Kako možete simulirati vrednosti diskretni nasumična promenljiva?

  • Kako možete simulirati vrednosti od normalne nasumična promenljiva?

  • Kako da čestitki preduzeća određuju koliko kartice da biste dobili?

Hteli bi da precizno procenu verovatnoće neizvesna događaja. Na primer, šta je verovatnoća da će novi proizvod novčanih tokova imaju na pozitivne sadašnju neto vrednost (funkcija NPV)? Šta je to faktoru rizika od naših investicija portfolija? Monte Karlo simulacija omogućava nam modela situacije koje predstavljanje neizvesnost i zatim ih odigrati na računaru hiljada puta.

Napomena: Ime Monte Karlo simulacija potiče iz računara simulacije obaviti tokom 1930 i 1940-ih za procenu verovatnoću da se povezale potrebne za krenite za aktiviranje uspešno raditi. Fiziиari uključene u ovaj posao su voleli kocke, pa su dali simulacija Monte Karlokôd ime.

U sledećih pet poglavlja, videćete primera kako možete da koristite Excel da biste izvršili Monte Karlo simulacije.

Mnoge kompanije koristite Monte Karlo simulacija kao važan deo njihovog odlučivanja procesa. Evo nekoliko primera.

  • GM Proktor i kocka, Fajzer, Bristol Majersom Squibb i Lilly Ilaj koristite simulaciju za procenu prosečna povratak i faktoru rizika novih proizvoda. U GM, ove informacije koriste CEO da biste utvrdili koji proizvodi dolaze za reklamiranje.

  • GM koristi simulaciju za aktivnosti kao što su predviđanja neto prihod za korporaciju, prognoziranje strukturnu i nabavke troškove i određivanje njegove podložnost za različite vrste rizika (kao što su promene kamatna stopa i promene kurs).

  • Lili koristi simulaciju da biste utvrdili optimalne biljke kapacitet za svaki lek.

  • Proktor i kocka koristi simulaciju da modela i optimalno izbegao razmeni rizik.

  • Sears koristi simulaciju da bi odredila koliko će jedinica za svaki red proizvoda bi trebalo naručiti od dobavljača – na primer, broj parova luиkim radnicima pantalone koje bi trebalo naručiti ove godine.

  • Oil i lek preduzeća koriste simulaciju na vrednost „pravi opcije”, kao što je vrednost u polju za potvrdu proširi, ugovor ili odlaganje projekta.

  • Finansijske planeri koristite Monte Karlo simulacija da biste utvrdili optimalne ulaganje strategije za svoje klijente penzioni.

Kada otkucate formule =RAND() u ćeliji, možete dobiti broj koje jednako verovatno da preuzme bilo koja vrednost između 0 i 1. Stoga, 25% vremena, trebalo bi da dobijete broj manje od ili jednako 0,25; 10 procenata vremena treba da dobijete broj koji nije bar 0,90 i tako dalje. Da bi se pokazalo kako funkcioniše funkcija RAND, pogledajte datoteku Randdemo.xlsx, što je prikazano na slici 60-1.

Book Image
Slika 60 1 demonstrira funkcija RAND

Napomena: Kada otvorite datoteku Randdemo.xlsx, nećete videti isti nasumične brojeve, što je prikazano na slici 60-1. Funkcija RAND uvek automatski ponovo izračunava brojeve generiše kada se radni list otvori ili kada nove informacije su uneti u radni list.

Prvo, kopirajte iz ćelije C3 C4:C402 formule =RAND(). Zatim možete ime opsega C3:C402 podataka. Zatim, u koloni F, možete pratiti prosek 400 nasumične brojeve (ćelija F2) i koristite funkciju COUNTIF da biste utvrdili razlomke koje se nalaze između 0 i 0,25, 0,25 i 0,50, 0,50 i 0,75, i od 0,75 i 1. Kada pritisnete taster F9, nasumične brojeve ponovnog izračunavanja. Obaveštenje da prosečne vrednosti 400 brojeva je uvek približno 0,5, i da su oko 25% od rezultata u intervalu od 0,25. Ovi rezultati su u skladu sa definiciju nasumični broj. Takođe imajte u vidu da vrednosti generiše RAND u različitim ćelijama nezavisni. Na primer, ako nasumični broj generisan u ćeliji C3 je veliki broj (na primer, 0.99), govori nam ništa o vrednosti od drugih nasumične brojeve koje generiše.

Ako zahtev za kalendar se upravlja sledeće diskretni nasumična promenljiva:

Zahtev

Verovatnoća

10.000

0,10

20.000

0,35

40 000

0,3

60.000

0,25

Kako da imamo Excel odigrati ili simulirati taj zahtev za kalendare više puta? Posao je želite da dodelite svaki mogući vrednost funkcije RAND moguće zahtev za kalendare. Sledeći zadatak osigurava da zahtev od 10.000 10% vremena da se jave, i tako dalje.

Zahtev

Nasumični broj koji je dodeljen

10.000

Manje od 0.10

20.000

Veće od ili jednako 0.10 i manja od 0.45

40 000

Veće od ili jednako 0.45 i manja od 0,75

60.000

Veće od ili jednako od 0,75

Da bi se pokazalo simulaciju zahtev, pogledajte datoteku Discretesim.xlsx, što je prikazano na slici 60-2 na sledećoj stranici.

Book Image
Slika 60 2 simulirajući diskretni nasumična promenljiva

Ključ ka simulaciju, jeste da koristite nasumični broj da biste pokrenuli pretraživanje iz tabela opseg F2:G5 (pod nazivom " Pronalaženje"). Nasumične brojeve veće od ili jednak 0 i manji od 0.10 će dobit zahtev od 10.000; nasumične brojeve veće od ili jednako 0.10 i manji od 0.45 će dobit zahtev od 20.000; nasumične brojeve veće od ili jednako 0.45 i manji od 0,75 će dobit zahtev od 40 000; i nasumične brojeve veće od ili jednako 0,75 će dobit zahtev od 60 000. Generisanje 400 nasumične brojeve tako što ćete kopirati iz C3 C4:C402 formule RAND(). Zatim generišete 400 probe ili iteracija zahtev kalendar tako što ćete kopirati iz B3 B4:B402 formula VLOOKUP(C3,lookup,2). Ova formula osigurava da nasumični broj manje od 0.10 generiše zahtev od 10.000, sve nasumični broj između 0.10 i 0.45 generiše zahtev od 20.000 i tako dalje. U opsegu ćelija F8:F11, koristite funkciju COUNTIF za određivanje razlomak naše 400 ponavljanja susretljivo svaki zahtev. Kada smo taster F9 za ponovno izračunavanje nasumične brojeve, simulirani verovatnoće su blizu naše verovatnoće Pretpostavljena zahtev.

Ako otkucate na bilo koju ćeliju formule NORMINV(rand(),mu,sigma), će generisati simulirani vrednost normalne nasumična promenljiva imate srednje mu vrednost i standardnu devijaciju sigma. Ova procedura je Ilustrovani u datoteci Normalsim.xlsx, što je prikazano na slici 60-3.

Book Image
Slika 60 3 simulirajući normalan nasumična promenljiva

Recimo da želimo da biste simulirali 400 probe ili iteracija za normalan nasumična promenljiva sa 40 000 srednju vrednost i standardnu devijaciju od 10.000. (Možete unesite sledeće vrednosti u ćelijama E1 i E2 i ime ove ćelije znače i sigma, odnosno.) Kopiranje formule =RAND() sa C4 C5:C403 generiše 400 različite nasumične brojeve. Kopiranje iz B4 B5:B403 formula NORMINV(C4,mean,sigma) generiše 400 različite probne vrednosti od normalne nasumična promenljiva sa 40 000 srednju vrednost i standardnu devijaciju od 10.000. Kada smo pritisnite taster F9 za ponovno izračunavanje nasumične brojeve, srednju ostaje blizu 40 000 vrednost i standardnu devijaciju blizu 10.000.

U suštini, za na nasumični broj x, formule NORMINV(p,mu,sigma) generiše ppercentil normalan nasumična promenljiva sa srednje mu i standardnu devijaciju sigma. Na primer, nasumični broj 0.77 u ćeliji C4 (pogledajte slici 60-3) generiše u ćeliji B4 približno 77 percentil normalan nasumična promenljiva sa 40 000 srednju vrednost i standardnu devijaciju od 10.000.

U ovom odeljku, videćete kako Montecarlo simulacija može koristiti kao alatka za odlučivanje. Ako zahtev za dan zaljubljenih kartice se upravlja sledeće diskretni nasumična promenljiva:

Zahtev

Verovatnoća

10.000

0,10

20.000

0,35

40 000

0,3

60.000

0,25

Čestitki prodaje za $4.00, a promenljive trošak proizvodnje svakoj kartici je $1.50. Preostali kartice mora dostupni, po ceni od $0,20 po kartice. Koliko karata trebalo da budu odštampane?

U osnovi, možemo simulirati količinu svaki mogući proizvodnje (10.000, 20.000, 40 000 ili 60 000) više puta (na primer, 1000 iteracija). Zatim ćemo određuju koji količinu daje maksimalnu prosečna profita preko 1000 iteracija. Možete da pronađete podatke za ovaj odeljak u datoteci Valentine.xlsx, što je prikazano na slici 60-4. Opseg imena u ćelijama B1:B11 dodeliti C1:C11 ćelije. Opseg ćelija G3:H6 dodeljuje ime za pronalaženje. Naš prodajne cene i cene parametara su uneti u ćelije C4:C6.

Book Image
Slika 60-4 dan zaljubljenih kartica simulaciju

Možete uneti količinu probne proizvodnje (40 000 u ovom primeru) u ćeliji C1. Zatim, kreirajte nasumični broj u ćeliji C2 formule =RAND(). Kao što je prethodno opisano, možete simulirati zahtev za karticu u ćeliji C3 pomoću formule VLOOKUP(rand,lookup,2). (U formuli VLOOKUP rand je ime ćelije dodeljeno ćelije C3, ne funkcije RAND.)

Broj prodatih je manja od naših količinu proizvodnje i zahtev. U ćeliji C8, naš prihod sa formulom izračunate MIN (proizvedene, zahtev) * unit_price. U ćeliji C9, ukupni troškovi proizvodnje sa formulom izračunate proizvodi * unit_prod_cost.

Ako pravimo više karata nego u zahtev, broj jedinica ostalo jednako proizvodnje minus zahtev; u suprotnom nema jedinice su preostale. Nismo računanje naše rashod trošak u ćeliju C10 sa formulom unit_disp_cost * IF (proizvodi > zahtev, proizvode – zahtev, 0). Na kraju, u ćeliji C11, možemo da izračunate profit kao prihod – total_var_cost-total_disposing_cost.

Voleli bismo efikasan način za pritisnite taster F9 više puta (na primer, 1000) za svaku količinu proizvodnje i beleži očekivani profit za svaku količinu. Ovo je nešto u kojoj dvosmerna podatke tabele dolazi da nam spasi. (Pogledajte poglavlje 15, „Osetljivosti analize sa podacima tabele”, detalje o tabele podataka). Tabela podataka u ovom primeru je prikazana na slici 60-5.

Book Image
Slika 60-5 dvosmerna podataka tabelu za čestitke simulaciju

U opsegu ćelija A16:A1015, unesite brojeve 1 – 1000 (koja odgovara naše 1000 probe). Jedan jednostavan način za kreiranje ove vrednosti jeste da počnete tako što ćete uneti 1 u ćeliji A16. Izaberite ćeliju, zatim na kartici Početak u grupi Uređivanje , kliknite na dugme popunii izaberite stavku grupe da biste prikazali dijalog grupe . U dijalogu grupu , što je prikazano na slici 60-6, unesite vrednosti korak 1 i zaustavi vrednost od 1000. U oblasti za Grupu u izaberite opciju kolona i zatim kliknite na dugme u redu. Brojevi 1 – 1000 će biti unete u kolonu otvorena u ćeliji A16.

Book Image
Slika 60 6 pomoću grupu u dijalogu da biste popunili probne brojevima od 1 do 1000

Zatim ćemo unesite naše moguće proizvodnje količine (10.000 20.000, 40 000, 60 000) u ćelijama B15:E15. Želimo da biste izračunali profita za svaki broj za probnu verziju (od 1 do 1000) i količinu svaki proizvodnje. Zovemo je formula za profit (izračunate u ćeliji C11) u gornju levu ćeliju od naših tabelu podataka (A15) tako što ćete uneti = C11.

Sada smo spremni da trik Excel u simulirajući 1000 iteracija zahtev za svaku količinu proizvodnje. Izaberite opseg tabele (A15:E1014), a zatim u grupi Alatke za podatke na kartici "Podaci", kliknite na dugme analiza "Šta ako", a zatim izaberite tabelu sa podacima. Da biste podesili tabelu dvosmerna podataka, odaberite naše količinu proizvodnje (ćelija C1) kao red unosa ćelije i izaberite bilo koju praznu ćeliju (smo izabrali ćeliju I14) kao kolonu unosa ćelije. Pošto kliknete na dugme u redu, Excel simulira 1000 zahtev vrednosti za svaku količinu.

Da biste razumeli zašto to funkcioniše, razmislite o vrednosti postavio tabela podataka u opsegu ćelija C16:C1015. Za svaki od ovih ćelija, Excel će koristiti vrednost od 20.000 u ćeliju C1. U C16, vrednost ulazna ćelija kolone 1 je smeštena u praznu ćeliju i nasumični broj u ćeliji C2 ponovnog izračunavanja. U ćeliji C16 zatim snima odgovarajućih profita. Izaberite kolone ćelije ulazne vrednosti od 2 se stavlja u praznu ćeliju i ponovo ponovo izračunava nasumični broj u ćeliji C2. Odgovarajuća profita se unosi u ćeliji C17.

Kopiranjem iz ćelije B13 C13:E13 formula AVERAGE(B16:B1015), možemo izračunati prosek simulirani profita za svaku količinu proizvodnje. Kopiranjem iz ćelije B14 C14:E14 formula STDEV(B16:B1015), možemo da izračunate standardnu devijaciju naše simulirani profita za svaku količinu. Svaki put kada mi pritisnite taster F9, 1000 iteracija zahtev su simulirani za svaku količinu. Proizvodi 40 000 kartice uvek daje najveći očekivani profit. Stoga, izgleda da proizvodi 40 000 kartice ispravne odluke.

Uticaj rizika na odluku     Ako smo proizveli 20.000 umesto 40 000 kartice, očekivani profit ispušta otprilike 22 procenata, ali rizik (meren standardnu devijaciju profita) ispušta skoro 73%. Stoga, ako izuzetno volite da rizika, proizvodi 20.000 kartice možda ispravnu odluku. Usput, proizvodi 10.000 kartice uvek ima standardna devijacija od 0 kartice jer ako pravimo 10.000 kartice, uvek će prodajemo sve te bez sve ostalo.

Napomena: U ovoj radnoj svesci, opciju izračunavanja je podešen na Automatsko osim za tabele. (Koristite komandu izračunavanja u grupi izračunavanja na kartici "formule"). Ova postavka obezbeđuje da tabelu sa podacima će obračuna osim ako smo pritisnite taster F9, što je dobra ideja jer tabele velike podataka će usporiti rad ako ga izračunava svaki put kada otkucate nešto što u radni list. Imajte na umu da u ovom primeru, svaki put kada pritisnete taster F9, srednje dobit koja će se promeniti. To se dešava ako svaki put kada pritisnete taster F9, drugi niz 1000 nasumične brojeve se koristi za generisanje zahteve za svaku količinu.

Interval pouzdanosti za znače profita     Prirodni pitanje za u ovakvoj situaciji je u koje intervala smo da li će biti ispunjen prosek profita 95%? Ovaj period se zove 95% interval pouzdanosti za prosek profita. 95% intervala pouzdanosti za srednju sve simulacija izlaz je izračunati pomoću sledeće formule:

Book Image

U ćeliji J11, izračunajte donja granica za interval pouzdanosti 95% na prosek profita kada 40 000 kalendare proizvedeno pomoću formule D13–1.96*D14/SQRT(1000). U ćeliji J12, izračunate gornje ograničenje za interval pouzdanosti naše 95% sa formulom D13+1.96*D14/SQRT(1000). Ove izračunavanja prikazane su u slici 60-7.

Book Image
Slika 60 7 95% interval pouzdanosti za prosek profita kada su raspoređeni 40 000 kalendara

Radimo 95 procenata da je prosek profit kada 40 000 kalendari su raspoređeni između 56,687 $ a $62,589.

  1. Trgovac GMC smatra da zahtev za 2005 izaslanici biti normalnu raspodelu sa 200 srednju vrednost i standardnu devijaciju od 30. Njegov trošak prima e predstavnik zajednice je 25 000 dolara i prodaje na predstavnik zajednice za 40 000. Polovina sve izaslanici ne prodaju po punu cenu može da se prodaje za $30.000. Uzima u obzir naručivanje 200, 220, 240, 260, 280 ili 300 izaslanici. Koliko bi trebalo da je nalog?

  2. Mala prodavnice pokušava da utvrdi koliko kopija osobe magazin oni bi trebalo da naručite svake sedmice. Veruju njihov zahtev za osobe upravlja korišćenjem sledećih diskretni nasumična promenljiva:

    Zahtev

    Verovatnoća

    15

    0,10

    20

    0,20

    25

    0.30

    30

    0,25

    35

    0,15

  3. U prodavnici plaća $1,00 za svaku kopiju osobe i prodaje za od 1.95 $. Svaka neprodate kopija može da se vrati za $0,50. Koliko kopija osoba koje bi trebalo da skladište nalog?

Potrebna vam je dodatna pomoć?

Možete uvek da postavite pitanje stručnjaku u zajednici Excel Tech Community, dobijete podršku u zajednici Answers community ili predložite novu funkciju ili poboljšanje na sajtu Excel User Voice.

Razvijte Office veštine
Istražite obuku
Prvi nabavite nove funkcije
Pridružite se Office Insider korisnicima

Da li su vam ove informacije koristile?

Hvala vam na povratnim informacijama!

Hvala za povratne informacije! Zvuči da će biti od pomoći ako vas povežemo sa našim agentima Office podrške.

×