Johdatus Monte Carlo-simulointiin Excelissä

Huomautus: Microsoft haluaa tarjota sinulle ajantasaisinta ohjesisältöä mahdollisimman nopeasti omalla kielelläsi. Tämä sivu on käännetty automaation avulla, ja siinä saattaa olla kielioppivirheitä tai epätarkkuuksia. Tarkoitus on, että sisällöstä on sinulle hyötyä. Kertoisitko sivun alareunassa olevan toiminnon avulla, oliko tiedoista hyötyä? Tästä pääset helposti artikkelin englanninkieliseen versioon.

Tämä artikkeli on mukautettu Microsoft Excelin tietojen analysoinnista ja yritys mallinnuksesta Wayne L. Winston.

  • Kuka käyttää Monte Carlo-simulaatiota?

  • Mitä tapahtuu, kun kirjoitat soluun = Rand () solussa?

  • Miten voit simuloida erillisen satunnaismuuttujan arvoja?

  • Miten voit simuloida tavallisen satunnaismuuttujan arvoja?

  • Miten kortin yritys voi selvittää, kuinka monta korttia on tuotettava?

Halu amme arvioida tarkasti epävarmojen tapahtumien todennäköisyydet. Mikä on esimerkiksi todennäköisyys, että uuden tuotteen kassa Virroilla on positiivinen nettonykyarvo (NPV)? Mikä on sijoitus salkun riski tekijä? Monte Carlo-simulaation avulla voimme mallintaa tilanteita, jotka aiheuttavat epävarmuutta, ja toistaa ne tieto koneella tuhansia aikaa.

Huomautus:  Nimi Monte Carlo-simulaatio on perä isin 1930-ja 1940-luvun tieto kone simulaatioista, joilla arvioidaan todennäköisyyttä, että Atomi pommin räjäydeksi tarvittava ketju reaktio toimisi ongelmitta. Tähän työhön liittyvät fyysikot olivat isoja raha pelien kannattajia, joten he anto ivat simulaatioille koodin nimi Monte Carlo.

Seuraavissa viidessä luvussa on esimerkkejä siitä, miten voit käyttää Exceliä Monte Carlo-simulaatioiden suorittamiseen.

Monet yritykset käyttävät Monte Carlo-simulaatiota tärkeänä osana päätöksen teko prosessiaan. Seuraavassa on joitakin esimerkkejä.

  • General Motors, Proctor and Gamble, Pfizer, Bristol-Myers Squibb ja eli Lilly käyttävät simulointia, jonka avulla arvioidaan sekä keskimääräisen palautuksen että uusien tuotteiden riski tekijän. GM käyttää tätä tietoa, kun toimitus johtaja päättää, mitkä tuotteet tulevat markkinoille.

  • GM käyttää simulointia toimintoihin, kuten yrityksen netto tulojen ennustamiseen, rakenteellisten ja osto kustannusten ennakointiin sekä sen herkkyydestä erilaisiin riskeihin (kuten korko muutoksiin ja Valuutta kurssien vaihteluihin).

  • Lilly käyttää simulointia kunkin lääkkeen optimaalisen kapasiteetin määrittämiseen.

  • Proctor and Gamble käyttää simulointia mallin ja valuutta riskin suoja uksen optimaaliseen vaihtamiseen.

  • Sears käyttää simulointia määrittääkseen, kuinka monta yksikköä kustakin tuote rivistä on tilattava toimittajilta – esimerkiksi tämän vuoden tilattavissa olevien telakka housujen määrä.

  • Öljy-ja lääke yritykset käyttävät simulointia arvon "todellisia vaihto ehtoja", kuten projektin laajentamis-, sopimus-tai lykkäämis vaihtoehdon arvon.

  • Rahoitus suunnittelijat käyttävät Monte Carlo-simulaatiota parhaiden sijoitus strategioiden määrittämiseen asiakkaidensa eläkkeelle siirtymiseen.

Kun kirjoitat soluun kaavan = satunnaisluku () , saat luvun, joka vastaa yhtä todennäköisesti arvoa väliltä 0 – 1. Näin ollen noin 25 prosenttia ajasta sinun pitäisi saada luku, joka on pienempi tai yhtä suuri kuin 0,25; noin 10 prosenttia ajasta sinun pitäisi saada luku, joka on vähintään 0,90, ja niin edelleen. Jos haluat osoittaa, miten SATUNNAISLUKU-funktio toimii, Tutustu tiedostoon Randdemo. xlsx, joka näkyy kuvassa 60-1.

Kirjan kuva

Huomautus:  Kun avaat tiedoston Randdemo. xlsx, kuvassa 60-1 ei näy samoja satunnaislukuja. SATUNNAISLUKU-funktio laskee aina automaattisesti uudelleen luvut, jotka se luo, kun laskenta taulukko avataan tai kun laskenta taulukkoon syötetään uusia tietoja.

Kopioi ensin solusta C3 – C4: C402 kaava = Rand (). Sen jälkeen voit nimetä vaihtelu välin C3: C402 tiedot. Sen jälkeen voit määrittää sarakkeessa F 400 Satunnaislukujen (solu F2) Keski arvon ja laskea yhteen Laske. Jos-funktiolla luvut, jotka ovat väliltä 0 – 0,25, 0,25 ja 0,50, 0,50 ja 0,75 sekä 0,75 ja 1. Kun painat F9-näppäintä, satunnaisluvut lasketaan uudelleen. Huomaa, että 400-numeroiden Keski arvo on aina noin 0,5 ja että noin 25 prosenttia tuloksista on 0,25. Nämä tulokset ovat yhdenmukaisia satunnaisluvun määritelmän kanssa. Huomaa myös, että eri soluissa olevan satunnaisarvon luomat arvot ovat riippumattomia. Jos solussa C3 oleva satunnaisluku on esimerkiksi suuri luku (esimerkiksi 0,99), se ei kerro mitään muiden luotujen Satunnaislukujen arvoista.

Oletetaan, että kalenterin kysyntää säätelee seuraava erillinen satunnaismuuttuja:

Kysyntä

Todennäköisyys

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

Miten voimme valita, miten Excel toistaa tai simuloi tätä kalenterien kysyntää moneen kertaan? Temppu on yhdistää jokainen mahdollinen satunnaisluku-funktion arvo mahdolliseen kalenterien kysyntään. Seuraava tehtävä varmistaa, että 10 000-tilaus tapahtuu 10 prosenttia ajasta ja niin edelleen.

Kysyntä

Satunnaisluku määritetty

10 000

Pienempi kuin 0,10

20 000

Suurempi tai yhtä suuri kuin 0,10 ja pienempi kuin 0,45

40 000

Suurempi tai yhtä suuri kuin 0,45 ja pienempi kuin 0,75

60 000

Suurempi tai yhtä suuri kuin 0,75

Jos haluat esitellä kysynnän simuloinnin, Katso tiedostoa Disdistesim. xlsx, joka näkyy seuraavan sivun kuvassa 60-2.

Kirjan kuva

Simulaation avain on, jos haluat aloittaa haun satunnaisluvun avulla taulukko alueelta F2: G5 (nimeltään haku). Satunnaisluvut, joiden arvo on suurempi tai yhtä suuri kuin 0 ja pienempi kuin 0,10, tuottaa 10 000-arvon. satunnaisluvut, joiden arvo on suurempi tai yhtä suuri kuin 0,10 ja alle 0,45, saavat 20 000; satunnaisluvut, joiden arvo on suurempi tai yhtä suuri kuin 0,45 ja alle 0,75, saavat 40 000; satunnaisluvut, joiden arvo on suurempi tai yhtä suuri kuin 0,75, saavat 60 000. Voit luoda 400 satunnaislukuja kopioimalla kohteesta C3 – C4: C402 kaavan satunnaisluku (). Sen jälkeen voit luoda 400-kokeilu versioita tai Iteraatioita, jotka kopioidaan kohteesta B3 – B4: B402 kaava PHAKU (C3, haku, 2). Tämä kaava varmistaa, että satunnaisluku, joka on pienempi kuin 0,10, luo 10 000, minkä tahansa satunnaisluvun 0,10 ja 0,45 välillä aiheuttaa 20 000: n kysynnän ja niin edelleen. Käytä solu alueella F8: F11, Laske. Jos-funktion avulla voit määrittää 400-toistojen murto-osan jokaisesta kysynnästä. Kun painat F9-näppäintä uudelleen Satunnaislukujen laskemiseen, simuloitu todennäköisyys on lähellä oletettua kysynnän todennäköisyyksiä.

Jos kirjoitat soluun kaavan Norminv (Rand (), MU, Sigma), voit luoda simuloidun arvon normaalista satunnaismuuttujasta, jonka Keski arvo on mu ja Keski hajonta Sigma. Tämä toiminto on kuvattu tiedostossa Norm. xlsx, joka näkyy kuvassa 60-3.

Kirjan kuva

Oletetaan, että halu amme simuloida 400-kokeilu versioita tai Iteraatioita normaalijakauman satunnaismuuttujan kanssa, jonka Keski arvo on 40 000 ja 10 000. (Voit kirjoittaa nämä arvot soluihin E1 ja E2 sekä nimetä solut ja Sigmavastaavasti. ) Kaavan = Rand () kopioiminen C4 – C5: C403 luo 400 eri satunnaislukuja. Kopioiminen B4 – B5: B403 kaava Norminv (C4, Sigma) luo 400 eri koearvot tavallisesta satunnaismuuttujasta, jonka Keski arvo on 40 000, sekä 10 000. Kun painat F9-näppäintä Satunnaislukujen laskemiseen uudelleen, Keski arvo säilyy 40 000 ja Keski hajonta on lähellä 10 000.

Satunnaisluku xtarkoittaa, että kaava norminv (p, MU, Sigma) luo normaalin satunnaismuuttujan p. prosentti pisteen, jonka Keski arvo on mu ja standardi poikkeama Sigma. Esimerkiksi solussa C4 oleva satunnaisluku 0,77 (Katso kuva 60-3) Luo soluun B4 noin 75-prosenttisen prosentti pisteen normaalista satunnaismuuttujasta, jonka Keski arvo on 40 000, ja 10 000 Keski hajonta.

Tässä osiossa näet, miten Monte Carlo-simulaatiota voidaan käyttää päätöksen teko välineenä. Oletetaan, että ystävän päivä kortin kysyntää säätelee seuraava erillinen satunnaismuuttuja:

Kysyntä

Todennäköisyys

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

Tervehdys kortti myy $4,00, ja kunkin kortin tuotanto kustannukset ovat $1,50. Ylimääräiset kortit on hävitettävä hintaan, joka on $0,20 per kortti. Montako korttia on painettava?

Simuloidaan periaatteessa jokaista mahdollista tuotanto määrää (10 000, 20 000, 40 000 tai 60 000) monta kertaan (esimerkiksi 1000 toistoja). Sen jälkeen määritetään, mikä tilaus määrä tuottaa suurimman keskimääräisen tuoton 1000 iteraatioiden aikana. Voit etsiä tämän osan tiedot tiedostosta Valentine. xlsx, joka on esitetty kuvassa 60-4. Voit määrittää alueiden nimet soluihin B1: B11 soluihin C1: C11. Solu alueelle G3: H6 on määritetty nimi haku. Myynti hinta-ja hinta-parametrejamme syötetään soluihin C4: C6.

Kirjan kuva

Voit määrittää koetuotannon määrän (40 000 tässä esimerkissä) soluun C1. Luo seuraavaksi satunnaisluku soluun C2 kaavalla = Rand (). Kuten edellä on kuvattu, simuloit kortin kysyntää solussa C3 kaavan PHAKU-funktion avulla (satunnaisluku, haku, 2). (VHAKU-kaavassa satunnaisluku on soluun C3 määritetty solun nimi, ei SATUNNAISLUKU-funktio.)

Myytyjen yksiköiden määrä on pienemmän tuotannon määrän ja kysynnän. Solu C8 Laske tulomme kaavalla min (tuotettu, kysyntä) * unit_price. Solu C9 laskee kokonaistuotantokustannukset kaavalla tuotettu * unit_prod_cost.

Jos tuotamme enemmän kortteja kuin on kysyntää, jäljellä olevien yksikköjen määrä vastaa tuotantoa miinus kysyntää; muussa tapa uksessa mikään yksikkö ei ole jäänyt. Laskenta kulut lasketaan soluun C10 kaavalla unit_disp_cost * jos (produced>demand, tuotettu – kysyntä, 0). Lopuksi soluun C11 Laske voitomme tulona – total_var_cost – total_disposing_cost.

Haluaisimme tehokkaan tavan painaa F9-näppäintä monta kertaan (esimerkiksi 1000) kunkin tuotanto määrän osalta ja kunkin määrän oletetun tuoton. Tämä tilanne on sellainen, jossa kaksisuuntainen tieto taulukko tulee meidän pelastukseen. (Katso lisä tietoja tieto taulukoista kohdasta luku 15, "herkkyys analyysi tieto taulukoilla".) Tässä esimerkissä käytetty arvo taulukko näkyy kuvassa 60-5.

Kirjan kuva

Kirjoita solu alueella A16: A1015 numerot 1 – 1000 (vastaa 1000-kokeilu versioita). Yksi helppo tapa luoda nämä arvot on aloittaa kirjoittamalla 1 soluun A16. Valitse solu ja valitse sitten Aloitus -väli lehden muokkaaminen -ryhmässä täyttöja valitse sarjat , jolloin sarjat -valinta ikkuna tulee näkyviin. Kirjoita sarja -valinta ikkunan kuva 60-6-kohtaan vaiheen arvo 1 ja 1000-kohdan lopetus arvo. Valitse sarjat- kohdan alue-kohdassa sarakkeet -vaihto ehto ja valitse sitten OK. Numerot 1 – 1000 syötetään sarakkeeseen A, joka alkaa solusta A16.

Kirjan kuva

Seuraavaksi syötetään mahdollisia tuotanto määriä (10 000, 20 000, 40 000, 60 000) soluihin B15: E15. Halu amme laskea kunkin koenumeron (1 – 1000) ja kunkin tuotanto määrän tuoton. Viittasimme kaavan voittoon (laskettu soluun C11) arvo taulukon vasemmassa yläkulmassa olevaan soluun (A15) kirjoittamalla = C11.

Nyt voit huijata Exceliä simuloimalla kunkin tuotanto määrän kysynnän 1000 toistoja. Valitse taulukon alueet (A15: E1014) ja valitse sitten tiedot-väli lehden Data Työkalut-ryhmästä mitä jos-analyysi ja valitse sitten arvo taulukko. Jos haluat määrittää kaksisuuntaisen tieto taulukon, valitse tuotanto määrä (solu C1) rivin syöttö soluksi ja valitse mikä tahansa tyhjä solu (valitsin solu I14) sarakkeen syöttö soluksi. Kun olet napsauttanut OK, Excel simuloi 1000-kysyntä arvoja kunkin tilaus määrän osalta.

Jos haluat tietää, miksi tämä toimii, mieti arvo taulukon arvoja solu alueella C16: C1015. Excel käyttää kunkin solun kohdalla 20 000-arvoa solussa C1. C16 sarakkeen syöttö solun arvo 1 sijoitetaan tyhjään soluun ja satunnaisluku solussa C2 laskee uudelleen. Vastaava tuotto kirjataan sitten soluun C16. Sarakkeen solun syöte arvo 2 sijoitetaan tyhjään soluun ja satunnaisluku C2 uudelleen laskee uudelleen. Vastaava tuotto syötetään soluun C17.

Kun kopioit solusta mallin b13 C13: E13 kaavan Keski arvo (B16: B1015), laskenta laskee kunkin tuotanto määrän keskimääräisen simuloidun tuoton. Kopioimalla solusta B14 ja C14: E14 kaava Keski hajonta (B16: B1015), laskemme kunkin tilaus määrän simuloitujen voittojen vakio poikkeaman. Joka kerta kun painat F9-näppäintä, kunkin tilaus määrän kohdalla simuloidaan kysynnän 1000. 40 000-korttien tuottaminen tuottaa aina suurimman odotettavan tuoton. Siksi näyttää siltä, että 40 000-korttien tuottaminen on oikea päätös.

Riskin vaikutus päätöksemme     Jos olemme tuottaneet 20 000 40 000-korttien sijaan, odotimme voitto laskee noin 22 prosenttiin, mutta riski (mitattuna tulos Keski hajonta) laskee lähes 73 prosenttia. Jos siis riski on erittäin hyvä, 20 000-korttien tuottaminen saattaa olla oikea ratkaisu. 10 000-korttien tuottaminen on muuten aina 0-kortin Keski hajonta, koska jos tuotamme 10 000-kortteja, myymme niitä aina ilman jäljelle jääneitä.

Huomautus:  Tässä työkirjassa laskenta -asetuksena on Automaattinen, paitsi taulut. (Käytä laskenta-komentoa kaavat-väli lehden laskenta-ryhmässä.) Tämä asetus varmistaa, että tieto taulukkoa ei lasketa uudelleen, ellemme paina F9-näppäintä, mikä on hyvä idea, koska suuri arvo taulukko hidastaa työtäsi, jos se lasketaan uudelleen aina, kun kirjoitat jotain laskenta taulukkoon. Huomaa, että tässä esimerkissä, kun painat F9-näppäintä, keskimääräinen voitto muuttuu. Tämä johtuu siitä, että aina kun painat F9-näppäintä, kunkin tilaus määrän vaatimukset luodaan eri 1000 Satunnaislukujen välillä.

Keskimääräisen tuoton luottamus väli     Luonnollinen kysymys tässä tilanteessa on, mitä väliä olemme 95 prosenttia varma, että todellinen keskimääräinen voitto laskee? Tätä väliä kutsutaan keskimääräisen voiton 95 prosentin luottamus väli. Simuloinnin Keski arvon 95 prosentin luottamus väli lasketaan seuraavan kaavan avulla:

Kirjan kuva

Solu J11 laskee 95 prosentin luottamus välin alarajan keskimääräisen tuoton osalta, kun 40 000-kalentereita tuotetaan kaavan D13 – 1.96 * D14/neliö. Solu J12 Laske 95 prosentin luottamus välin yläraja kaavalla D13 + 1.96 * D14/neliö. Nämä lasku toimitukset näkyvät kuvassa 60-7.

Kirjan kuva

Olemme 95 prosenttia varmoja siitä, että Keski arvo, kun 40 000-kalentereita on tilattu, on välillä $56 687 ja $62 589.

  1. GMC-jälleenmyyjä uskoo, että 2005-edustajien kysyntä jakautuu yleensä 200 ja Keski hajonnan kautta 30. Hänen vastaanottavansa lähettilään on $25 000, ja hän myy lähettilään $40 000. Puolet kaikista lähettiläistä, joita ei myydä täydellä hinnalla, voidaan myydä $30 000. Hän harkitsee 200-, 220-, 240-, 260-, 280-ja 300-lähettilään tilaamista. Kuinka monta hänen pitäisi tilata?

  2. Pieni supermarket yrittää selvittää, kuinka monta kopiota People Magazinesta heidän pitäisi tilata viikoittain. He uskovat, että heidän vaatimuksensa on säännelty seuraavien erillisten satunnaismuuttujan kautta:

    Kysyntä

    Todennäköisyys

    15

    0,10

    20

    0,20

    25

    0,30

    30

    0,25

    35

    0,15

  3. Supermarket maksaa $1,00 jokaisesta kopio henkilöstä ja myy sen $1,95. Jokainen myymättä oleva kopio voidaan palauttaa $0,50. Kuinka monen kopion henkilön on oltava kaupan tilaus?

Tarvitsetko lisäohjeita?

Voit aina kysyä neuvoa Excel Tech Community -yhteisön asiantuntijalta, saada tukea yhteisön vastauksista tai ehdottaa uutta ominaisuutta tai parannusta Excel User Voice -sivustolla.

Kehitä Office-taitojasi
Tutustu koulutusmateriaaliin
Saat uudet ominaisuudet ensimmäisten joukossa
Liity Office Insider -käyttäjiin

Oliko näistä tiedoista hyötyä?

Kiitos palautteesta!

Kiitos palautteestasi! Näyttää siltä, että Office-tukiedustajamme avusta voi olla sinulle hyötyä.

×