Uvod u Monte Carlo simulacije u programu Excel

Napomena:  Željeli bismo vam pružiti najnoviji sadržaj pomoći što je brže moguće i to na vašem jeziku. Ova je stranica strojno prevedena te može sadržavati gramatičke pogreške ili netočnosti. Naša je namjera da vam ovaj sadržaj bude koristan. Možete li nam pri dnu ove stranice javiti jesu li vam ove informacije bile korisne? Kao referencu možete pogledati i članak na engleskom jeziku .

U ovom se članku je prilagoditi tako da Wayne L. Winston iz Microsoft Excel analize i Modeliranje tvrtke . Posjetite Microsoft Learning da biste saznali više o ovom adresara.

  • Tko koristi Monte Carlo simulacije?

  • Što se događa kada je u ćeliju upišete =RAND() ?

  • Kako vam zamjenu vrijednosti pojedinačnog slučajna varijabla?

  • Kako vam zamjenu vrijednosti u običnom slučajna varijabla?

  • Kako čestitku poduzeća možete odrediti koliko karata čime se dobiva?

Željeli bismo točno procjenu vjerojatnosti sigurni događaja. Na primjer, što je vjerojatnost da će novi proizvod novčanih tokova imati na pozitivne čistu sadašnju vrijednost (NPV)? Što je faktor rizika naš portfelja ulaganja? Monte Carlo simulacije omogućuje nam situacijama modela koji izlaganje nesigurnosti i reproducirati ih na računalu tisućica puta.

Napomena: Naziv Monte Carlo simulacije dolazi iz simulations računala izvesti tijekom na 1930s i 1940s za procjenu vjerojatnost da izvršavanju lanac koja su potrebna za programa atom bomb da biste detonate daju uspješno. Koji je uključen u to funkcioniralo physicists su veliki sporta kockanje, tako da ih dao na simulations Monte CarloKodni naziv.

U pet poglavlja, vidjet ćete Primjeri kako Excel možete koristiti za izvođenje Monte Carlo simulations.

Mnoge tvrtke Monte Carlo simulacije upotrijebili važan dio njihove postupak odlučivanja. Evo nekoliko primjera.

  • Općenito Motors, Proctor i ljubav kocka, Pfizer, Samoborska Myers Squibb i Eli Lilly pomoću simulacije za procjenu average povrat i faktor rizik od novih proizvoda. Pri GM, ti podaci koristi u Izvršni da biste utvrdili koje je proizvode Dođite na oglašavanje.

  • GM koristi simulacije aktivnosti kao što su predviđanje Neto dobit za tvrtka, procjenjivanje strukturnih i nabavljanja troškove i određivanje njegov susceptibility za različite vrste rizika (kao što su kamatna stopa promjene i prilagođavati razlikama tečaj).

  • Lilly koristi simulacije za određivanje kapacitet optimalnih biljke za svaki zloupotrebu.

  • Proctor i ljubav kocka koristi simulacije modela i optimalnog hedge vanjski exchange rizika.

  • Sears koristi simulacije da biste odredili koliko će jedinica svakog retka proizvoda konačnom iz dobavljače – na primjer, broj parova trousers Dockers koje trebaju biti poredana ove godine.

  • ULJE i zloupotrebu tvrtke pomoću simulacije vrijednost "realni mogućnosti", kao što su vrijednost mogućnost da biste proširili, ugovor ili odgoditi projekta.

  • Financijske Planera omogućuje određivanje strategije optimalnih ulaganja za svoje klijente umirovljenje Monte Carlo simulacije.

Kad u ćeliju upišete formulu =RAND() , prikazuje se broj koji je jednako vjerojatno pretpostavlja da sve vrijednosti između 0 i 1. Dakle, oko 25 posto vrijeme vam broja manja od ili jednaka 0,25; oko 10 posto vremena trebali biste dobiti broj koji je barem 0.90 i tako dalje. Da bismo pokazali kako funkcionira funkcija RAND, pogledajte datoteku Randdemo.xlsx, prikazan na slici 60-1.

Book Image
Slika 60-1 takvi RAND (funkcija)

Napomena: Kada otvorite datoteku Randdemo.xlsx, nećete vidjeti isti slučajne brojeve prikazane na slici 60-1. Funkcija RAND uvijek automatski ponovno izračunava brojeva generira prilikom otvaranja radnog lista ili prilikom unosa novih podataka na radni list.

Najprije kopirati iz ćelije C3 C4:C402 formule =RAND(). Zatim naziv raspona C3:C402 podataka. Nakon toga u stupcu F, možete pratiti prosjek 400 slučajne brojeve (ćelija F2) i koristite funkciju COUNTIF da biste odredili razlomke koji su između 0 i 0,25, 0,25 0.50, 0.50 i 0,75, a 0,75 i 1. Kada pritisnete tipku F9, izračunavaju se slučajne brojeve. Obavijest o prosječne vrijednosti 400 brojeva je uvijek približno 0,5, a da oko 25 posto rezultata u intervale 0,25. Ovi rezultati su u skladu s definicijom slučajni broj. Primijetite da su vrijednosti generira RAND u različitim ćelijama neovisno. Ako, na primjer, ako generira slučajni broj u ćeliji C3 velik broj (na primjer, 0.99), govori nam ništa o vrijednostima u slučajne brojeve koje generira.

Pretpostavimo da sljedeće samostalni slučajna varijabla uređena zahtjev za kalendar:

Zahtjev

Vjerojatnost

10 000

0,10

20.000

0.35

40.000

0,3

60 000

0,25

Kako mogu imamo Excel reproducirati ili kao zamjenu, ovaj zahtjev za kalendare više puta? Trik je želite pridružiti svaku moguću vrijednost funkcije RAND moguće zahtjev za kalendare. Sljedeće dodjele osigurava da zahtjev od 10 000 pojaviti 10 posto vremena i tako dalje.

Zahtjev

Slučajni broj dodijeljeno

10 000

Manje od 0.10

20.000

Veće od ili jednako 0.10 i manje od 0.45

40.000

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

60 000

Veće od ili jednako 0,75

Da bismo pokazali simulacije zahtjev, pogledajte datoteku Discretesim.xlsx, prikazan na slici 60-2 na sljedećoj stranici.

Book Image
Slika 60-2 simulaciju samostalni slučajna varijabla

Ključ za naše simulacije jest korištenje slučajni broj da biste započeli pretraživanja iz tablica raspon F2:G5 (pod nazivom pretraživanja). Slučajne brojeve veće od ili jednak 0 i manji od 0.10 će yield zahtjev od 10 000; slučajne brojeve veće od ili jednako 0.10 i manji od 0.45 će yield zahtjev od 20 000; slučajne brojeve veće od ili jednako 0.45 i manji od 0,75 će yield zahtjev od 40.000; i slučajne brojeve veće od ili jednako 0,75 će prinos na zahtjev 60.000. Generiranje 400 slučajne brojeve kopiranjem iz C3 da biste C4:C402 formule RAND(). Zatim generirati 400 pokušaji ili ponavljanja kalendara zahtjev kopiranjem iz B3 da biste B4:B402 formula VLOOKUP(C3,lookup,2). Ova formula osigurava da slučajni broj manji od 0.10 generira zahtjev od 10 000, slučajni broj između 0.10 i 0.45 generira zahtjev 20 000 i tako dalje. U rasponu ćelija F8:F11, koristite funkciju COUNTIF da biste odredili razlomak naš 400 iteracija prinos svaki zahtjev. Kada smo tipku F9 pokrenite ponovni slučajne brojeve, Simulirani vjerojatnosti su blizu naš vjerojatnosti pretpostavljenom zahtjev.

Ako upišete u bilo kojoj ćeliji formule NORMINV(rand(),mu,sigma), generirat će vrijednost Simulirani normalni slučajna varijabla pojavljuju na srednje mu i standardnu devijaciju sigma. Ovaj postupak je podržali datoteke Normalsim.xlsx, prikazan na slici 60-3.

Book Image
Slika 60-3 simulaciju normalni slučajna varijabla

Pretpostavimo da želite kao zamjenu za 400 pokušaji ili iteracija normalni slučajna varijabla s 40.000 srednju vrijednost i standardnu devijaciju 10 000. (Možete upisati te vrijednosti u ćelijama E1 i E2 i naziv te ćelije znače i sigma.) Kopiranje formule =RAND() s C4 C5:C403 generira 400 različite slučajne brojeve. Kopiranje s B4 B5:B403 formula NORMINV(C4,mean,sigma) generira 400 različite probne vrijednosti iz normalni slučajna varijabla s 40.000 srednju vrijednost i standardnu devijaciju 10 000. Kada smo pritisnite tipku F9 da biste ponovno izračunali slučajne brojeve, srednja vrijednost ostaje blizu 40.000 i standardnu devijaciju blizu 10 000.

Zapravo, za slučajni broj xformule NORMINV(p,mu,sigma) generira pti percentil normalni slučajna varijabla s srednje mu i standardnu devijaciju sigma. Na primjer, slučajni broj 0.77 u ćelija C4 (pogledajte slika 60-3) generira u ćeliju B4 približno 77th percentil normalni slučajna varijabla s 40.000 srednju vrijednost i standardnu devijaciju 10 000.

U ovom odjeljku, vidjet ćete kako se mogu koristiti SMS Karlo simulacije kao alat za donošenje odluka. Pretpostavimo da zahtjev za Valentinovo karticu uređena sljedeće samostalni slučajna varijabla:

Zahtjev

Vjerojatnost

10 000

0,10

20.000

0.35

40.000

0,3

60 000

0,25

Čestitki prodaje za $4.00, a varijabla trošak proizvodnje svakoj kartici je $1.50. Preostalo kartice morate Rashodovano uz mjesečnu naknadu $0,20 po kartice. Koliko karata treba ispisati?

Zapravo, ne možemo kao zamjenu za svaki mogući radnog količina (10 000, 20 000, 40.000 ili 60.000) više puta (na primjer, iteracija 1000). Zatim utvrđujemo koje naručivanje rezultira Maksimalna prosjeka dobiti preko 1000 ponavljanja. Možete pronaći podatke za ovu sekciju u datoteci Valentine.xlsx, prikazan na slici 60-4. Nazivi raspona u ćelije B1:B11 dodijeliti C1:C11 ćelije. Raspon ćelija G3:H6 dodjeljuje naziv pretraživanja. Naš prodajna cijena i trošak Parametri se unose u C4:C6 ćelije.

Book Image
Slika 60-4 Valentinovo kartica simulacije

Možete unijeti količinu za probno razdoblje proizvodnje (40.000 u ovom primjeru) u ćeliju C1. Nakon toga stvorite slučajni broj u ćeliji C2 formule =RAND(). Na prethodno opisani način zamjenu zahtjev za tu karticu u ćeliji C3 formule VLOOKUP(rand,lookup,2). (U formule VLOOKUP rand je naziv ćelije dodijeljene ćelije C3, ne funkcija RAND).

Broj prodanih jedinica je manja je od naše količinu proizvodnje i zahtjev. U ćeliju C8 izračunati naš prihoda koja sadrži formulu MIN (proizvedeno, zahtjev) * unit_price. U ćeliju C9, izračunati ukupni trošak proizvodnje s formulom Proizvodi * unit_prod_cost.

Ako ne možemo proizvesti više karata nego u zahtjev, broj jedinica zaostale jednako radnog minus zahtjev; u suprotnom se ne jedinice zaostale. Ne možemo izračunati naš rashoda cijena u ćeliju C10 koja sadrži formulu unit_disp_cost * IF (proizvodi > zahtjev, proizvodi – zahtjev, 0). Naposljetku, u ćeliji C11 smo izračunati naš dobiti kao prihod – total_var_cost-total_disposing_cost.

Želimo učinkovito pritisnite F9 više puta (na primjer, 1000) za svaki radni količinu i bilježi naš očekivani dobiti za svaku količinu. U ovom slučaju je u kojem se dvosmjerni podatkovne tablice dolazi naš stiže pomoć. (Pogledajte poglavlja 15, "Osjetljivost analize pomoću podatkovne tablice," detalje o podatkovne tablice). Podaci u tablici u ovom primjeru koriste je prikazan na slici 60-5.

Book Image
Slika 60-5 dvosmjerni podatkovne tablice za čestitke simulacije

U rasponu ćelija A16:A1015, unesite brojeve 1 – 1000 (odgovara naš pokušaji 1000). Jedan jednostavan način da biste stvorili te vrijednosti jest da biste pokrenuli tako da unesete 1 u ćeliji A16. Odaberite ćeliju, zatim na kartici Polazno u grupi Uređivanje , kliknite Ispunai odaberite niz da biste prikazali dijaloški okvir niz . U dijaloškom okviru niza u prikazan na slici 60-6, unesite vrijednost korak 1 i prekid vrijednosti 1000. U području Niza u odaberite mogućnost stupaca , a zatim u redu. Brojevi od 1 – 1000 bit će unesena u stupcu s početkom u ćeliji A16.

Book Image
Slika 60-6 pomoću dijaloškog okvira niz za ispunjavanje probne verzije brojevi od 1 do 1000

Sljedeće smo unesite naš moguće radnog količine (10 000, 20 000, 40.000, 60.000) u B15:E15 ćelije. Želimo da biste izračunali dobiti za sve brojeve za probno razdoblje (od 1 do 1000) i svaki radni količina. Ne možemo pogledajte formulu za dobiti (izračunatoj u ćeliji C11) u gornju lijevu ćeliju naš podatkovnu tablicu (A15) tako da unesete = C11.

Ne možemo spremni za trik programa Excel u simulaciju 1000 ponavljanja zahtjev za svaki radni količinu. Odaberite raspon tablice (A15:E1014) i zatim u grupi Alati podataka na kartici Podaci kliknite What analizu, a zatim podatkovna tablica. Da biste postavili dvosmjerni podatkovne tablice, odaberite naš količinu proizvodnje (ćelija C1) kao unos ćelija retka, a zatim odaberite bilo koju praznu ćeliju (smo odabrali ćeliju I14) kao unos ćeliju stupca. Nakon što kliknete u redu, Excel simulira 1000 zahtjev vrijednosti za svaki naručivanje.

Da biste shvatili zašto to funkcionira, razmislite o vrijednosti koja je postavila podatkovnu tablicu u raspon ćelija C16:C1015. Za svaki od tih ćelija, Excel će koristiti vrijednost od 20 000 u ćeliji C1. U C16, vrijednost Ulazna ćelija stupca 1 nalazi se u praznu ćeliju i slučajni broj u ćeliji C2 ponovnog izračuna. Odgovarajući dobiti zatim naveden u ćeliji C16. Stupac ćelija ulazni vrijednost 2 nalazi se u praznu ćeliju, a slučajni broj u ćeliji C2 ponovno ponovnog izračuna. U ćeliju C17 nije unesena odgovarajuća dobiti.

Tako da kopirate iz ćelije B13 C13:E13 formula AVERAGE(B16:B1015)ćemo izračun prosjeka dobiti Simulirani za svaki radni količinu. Tako da kopirate iz ćelije B14 C14:E14 formula STDEV(B16:B1015)ćemo izračunati standardna devijacija otpornosti na našem Simulirani dobiti za svaku naručivanje. Svaki put kada smo pritisnite F9, za svaku naručivanje Simulirani su 1000 ponavljanja zahtjev. Uvijek proizvodnje 40,000 kartice rezultira najveću očekivani dobiti. Dakle, pojavljuje se proizvodnje 40,000 kartice je proper odluka.

Utjecaj rizika na našem odluka     Ako smo proizvodi 20 000 umjesto 40,000 kartice, naš očekivani dobiti izostavlja otprilike 22 postotak, ali naš rizika (mjeri se standardna devijacija otpornosti dobiti) izostavlja gotovo 73 posto. Stoga ako smo iznimno averse rizika proizvodnje 20 000 kartice možda desnom odluka. Incidentally, proizvodnje 10 000 kartice uvijek ima standardna devijacija otpornosti na 0 kartice jer ako smo proizvesti 10 000 kartice, će uvijek prodajemo svi oni bez sve ostaci.

Napomena: U ovoj radnoj knjizi mogućnosti izračuna postavljen na Automatski tablica za sve osim za. (Pomoću naredbe za izračun u grupi izračun vrpce programa Excel.) Ta postavka jamči da naš podatkovna tablica će ne ponovni izračun osim ako ih ne možemo pritisnite F9, na koje je dobro jer velike podatkovne tablice će usporiti rad ako ga ponovno izračunava svaki put kad nešto unesete na radni list programa. Imajte na umu da u ovom primjeru svaki put kada pritisnete F9, Srednje dobit će se promijeniti. To se događa jer se svaki put kada pritisnete F9, drugi niz 1000 slučajne brojeve služi za generiranje zahtjeva za svaki naručivanje.

Interval pouzdanosti za znače dobiti     Prirodnim pitanje zatražiti u tom slučaju, u koje interval su je smo 95 posto li true srednje dobit će pasti? Interval zove 95 posto interval pouzdanosti za srednje dobiti. 95 posto interval pouzdanosti za srednju vrijednost bilo kakav izlaz simulacije je izračunati po sljedeću formulu:

Book Image

U ćeliju J11 izračunati donju granicu za interval pouzdanosti 95 posto na srednje dobiti kada 40,000 kalendara proizvedeno s formule D13–1.96*D14/SQRT(1000). U ćeliju J12 izračunati gornju granicu za naše interval pouzdanosti 95% pomoću formule D13+1.96*D14/SQRT(1000). Te izračune prikazane su u slici 60-7.

Book Image
Slika 60-7 95 posto interval pouzdanosti za srednje dobiti kada su poredane 40,000 kalendara

Radimo nalazi li se naš srednje dobiti kada su poredane 40,000 kalendara između 56,687 $ a $62,589 95 posto.

  1. GMC zastupnika koje misli da zahtjev za 2005 Envoys će biti normalno raspodijeljen 200 srednju vrijednost i standardnu devijaciju broja 30. Svoj trošak primati programa Envoy je $25.000, a on je Envoy prodaje za $40.000. Polovica sve Envoys ov po cijelog cijeni moguće je prodao za $30.000. On odlučuje redoslijed 200, 220, 240, 260, 280 ili 300 Envoys. Koliko je potrebno je naručiti?

  2. Mali supermarket pokušava utvrditi koliko primjeraka osobe časopis ih je potrebno naručiti svaki tjedan. Oni smatrate njihov zahtjev za osobe uređena sljedeće samostalni slučajna varijabla:

    Zahtjev

    Vjerojatnost

    15

    0,10

    20

    0,20

    25

    0.30

    30

    0,25

    35

    0,15

  3. Na supermarket daje $1,00 za svaku kopiju osobe i prodaje za $1.95. Svaku unsold kopiju može vratiti za $0.50. Koliko primjeraka osoba treba spremište redoslijed?

Treba li vam dodatna pomoć?

Postavite pitanje stručnjaku u tehničkoj zajednici za Excel, zatražite podršku u zajednici za odgovore ili predložite novu značajku ili poboljšanje na forumu za Excel User Voice.

Proširite svoje vještine korištenja sustava Office
Istražite osposobljavanje

Jesu li vam ove informacije bile korisne?

Hvala vam na povratnim informacijama!

Hvala vam na povratnim informacijama! Čini se da bi vam pomoglo kad bismo vas povezali s nekim od naših agenata podrške za Office.

×