Monte Carlo-szimuláció – bevezetés az Excel alkalmazásban

Megjegyzés: Szeretnénk, ha minél gyorsabban hozzáférhetne a saját nyelvén íródott súgótartalmakhoz. Ez az oldal gépi fordítással lett lefordítva, ezért nyelvtani hibákat és pontatlanságokat tartalmazhat. A célunk az, hogy ezek a tartalmak felhasználóink hasznára váljanak. Kérjük, hogy a lap alján írja meg, hogy hasznos volt-e az Ön számára az itt található információ. Az eredeti angol nyelvű cikket itt találja.

Ez a cikk a Microsoft Excel adatelemzési és üzleti modellezési verziójában Wayne L. Winston által lett kialakítva.

  • Ki használja a Monte Carlo-szimulációt?

  • Mi történik, amikor beírja a = Rand () képletet egy cellába?

  • Hogyan lehet szimulálni egy diszkrét véletlenszerű változó értékét?

  • Hogyan lehet szimulálni a normál véletlenszerű változó értékét?

  • Hogyan állapítható meg, hogy egy üdvözlőlap-cég hány kártyát tud készíteni?

Szeretnénk pontosan megbecsülni a bizonytalan események valószínűségét. Például annak a valószínűsége, hogy egy új termék cash flow-je pozitív nettó (NMÉ) értékkel fog rendelkezni? Milyen kockázati tényezője van a befektetési portfóliónak? A Monte Carlo-szimuláció lehetővé teszi, hogy olyan helyzetekben modellezje a helyzetet, amely bizonytalanságot okoz, majd egy számítógépen több alkalommal játssza le őket.

Megjegyzés:  A Monte Carlo nevű szimuláció az 1930-as és az 1940-es évek során végzett számítógép-szimulációkat tartalmazza, és megbecsüli annak valószínűségét, hogy az Atom-bomba robbanása után a folyamat sikeres volt. Az ebben a munkában részt vevő fizikusok nagy rajongói voltak a szerencsejátékokban, így a szimulációk a Monte Carlo-as kódot adták át.

A következő öt fejezetben láthatja, hogy miként végezheti el a Monte Carlo-as szimulációk használatát az Excelben.

Sok cég a Monte Carlo-szimulációt a döntéshozatali folyamat fontos részeként használja. Íme néhány példa.

  • A General Motors, a Proctor és a Gamble, a Pfizer, a Bristol-Myers Squibb és az Eli Lilly szimulálja a szimulációt az új termékek átlagos visszatérési és kockázati tényezőjének becsléséhez. A GM-ben ezt az információt a VEZÉRIGAZGATÓ használja fel annak meghatározásához, hogy mely termékek kerülnek piacra.

  • A GM az olyan tevékenységek szimulációját használja, mint például a cég nettó bevételének előrejelzése, a szerkezeti és a beszerzési költségek előrejelzése, valamint a különböző típusú kockázatok (például a kamatlábak és az Árfolyam-ingadozások) érzékenységének meghatározása.

  • A Lilly szimulálja a szimulációt az egyes gyógyszerek optimális üzemi kapacitásának meghatározásához.

  • A Proctor és a Gamble szimulációs modellt használ a deviza-és az optimális fedezeti kockázat kiszámításához.

  • A Sears szimulációval meghatározhatja, hogy a termékek hány darabját kell megrendelni a szállítóktól (például az ebben az évben megrendelhető nadrágok számát).

  • A kőolaj és a gyógyszergyárak a szimulációt használják "valódi beállítások" értékre, például a projekt kibontására, szerződésére vagy elhalasztására szolgáló lehetőség értékére.

  • A pénzügyi tervezők Monte Carlo-szimulációval határozzák meg, hogy az ügyfelek nyugdíjazása milyen optimális befektetési stratégiákat használjon.

Ha beírja a = Rand () képletet egy cellába, akkor egy olyan számot kap, amely egyenlő valószínűséggel a 0 és az 1 közötti értéket veszi figyelembe. Így az idő 25 százalékában a 0,25-nál kisebb vagy azzal egyenlő számot kell beszereznie. az időpont körülbelül 10 százalékában legalább 0,90-es számot kell beszereznie, és így tovább. A VÉL függvény működésének szemléltetéséhez tekintse meg a Randdemo. xlsx fájlt, amely a 60-1 ábrán látható.

Book Image

Megjegyzés:  Ha megnyitja a Randdemo. xlsx fájlt, nem jelenik meg a 60-1 ábra által megjelenített véletlenszerű számok. A RAND függvény minden esetben automatikusan újraszámítja a munkalap megnyitásakor vagy az új adatoknak a munkalapra történő beírásakor megjelenő számokat.

Először másoljon a C3 cellából a C4 cellába: C402 a = Rand ()képletet. Ezután a C3: C402 adatotkell elneveznie. Ezután az F oszlopban nyomon követheti a 400-beli véletlenszerű számok átlagát (F2 cella), a DARABTELI függvénnyel pedig meghatározhatja a 0 és az 0,25, a 0,25 és a 0,50, az 0,50 és a 0,75 és az 0,75 és az 1 közötti törteket. Az F9 billentyű lenyomásakor a program újraszámítja a véletlenszerű számokat. Figyelje meg, hogy a 400-számok átlaga mindig körülbelül 0,5, és a találatok körülbelül 25 százaléka az 0,25 intervallumokban van. Ezek az eredmények összhangban vannak egy véletlenszerű szám meghatározásával. Azt is megfigyelheti, hogy a RAND által a különböző cellákban generált értékek függetlenek. Ha például a C3 cellában generált véletlen szám nagy szám (például 0,99), akkor semmit sem mond a többi véletlenszerűen generált szám értékéről.

Tegyük fel, hogy a naptár iránti keresletet az alábbi diszkrét véletlenszerű változó szabályozza:

Igény

Valószínűség:

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

Hogyan lehet kijátszani az Excelt, vagy szimulálni a naptárak iránti igényt sokszor? A trükk az, hogy a RAND függvény minden lehetséges értékét társítani szeretné a naptárak esetleges igényével. A következő feladat biztosítja, hogy az 10 000 igénye az idő 10 százalékát fogja bekövetkezni, és így tovább.

Igény

Véletlenszerű szám kiosztva

10 000

Kisebb, mint 0,10

20 000

Nagyobb vagy egyenlő 0,10, és kisebb, mint 0,45

40 000

Nagyobb vagy egyenlő 0,45, és kisebb, mint 0,75

60 000

Nagyobb vagy egyenlő 0,75

A kereslet szimulációjának szemléltetéséhez tekintse meg a Discretesim. xlsx fájlt, amely a következő lapon látható 60-2 ábrán látható.

Book Image

A szimuláció kulcsa az F2: G5 (névvel ellátott Keresés) táblázatból való keresés kezdeményezésére szolgáló véletlenszerű szám használata. A 0 és a 0,10-nál kisebb véletlenszerű számok az 10 000 iránti keresletet eredményeznek; az 0,10-nél nagyobb vagy azzal egyenlő véletlenszerű számok és a 0,45-nál kisebb értékű számok az 20 000 keresletét fogják eredményezni; az 0,45-nél nagyobb vagy azzal egyenlő véletlenszerű számok és a 0,75-nál kisebb értékű számok az 40 000 keresletét fogják eredményezni; az 0,75-nél nagyobb vagy egyenlő véletlenszerű számok az 60 000 iránti keresletet eredményeznek. A 400 véletlenszerű számokat a C3 – C4 képletből másolhatja: a C402 () függvényt. Ezt követően 400-kísérleteket vagy ismétléseket hoz létre a naptári keresletről a B3-ról B4-re történő másolással: B402 a képlet FKERES (C3; keres; 2). Ez a képlet biztosítja, hogy a 0,10-nál kisebb véletlenszerű számok az 10 000-as keresletet, a 0,10 és a 0,45 közötti véletlenszerű számok pedig az 20 000 iránti keresletet és így tovább. Az F8: F11 cellatartományban használja a DARABTELI függvényt, amellyel megállapítható, hogy az 400 közelítései milyen részét adják minden igénynek. Amikor az F9 billentyű lenyomásával újraszámítja a véletlenszerű számokat, a szimulált valószínűségek közel vannak a feltételezett keresleti valószínűségekhez.

Ha olyan cellát ír be, amely a inverz (Rand (), MU, szigma)képletet adja meg, akkor a normál véletlenszerű változó szimulált értékét adja eredményül, amelynek a középértéke és szórása a szigma. Ezt az eljárást a Normalsim. xlsx fájlban, az 60-3 ábra szemlélteti.

Book Image

Tegyük fel, hogy szimulálni szeretné az 400-próbákat, vagy az ismétléseket egy normál véletlenszerű változóra, amelynek középértéke az 40 000 és az 10 000 szórása. (Az E1 és az E2 cellába beírhatja ezeket az értékeket, és a középérték és a szigmanevet is használhatja.) Másolja a = Rand () képletEt a C4-től a C5-ig: a 400 különböző véletlenszerű számokat hoz létre a C403. Másolás B4-től B5-ig: B403 a képlet inverz (C4, középérték, szigma) a 400 különböző próbaverzióit számítja ki egy normál véletlenszerű változóból az 40 000 középértéke és az 10 000 szórása között. Amikor az F9 billentyű lenyomásával újraszámítja a véletlenszám-számokat, a középérték a 40 000 és az 10 000-hoz közel lévő szórás között marad.

Az xvéletlenszám- inverz a (p, MU, szigma) képlet egy normál véletlenszerű változó pth percentilis értékét számítja ki egy középértéket és egy középértéket, a Sigmaközépértéket és egy szórást. A C4 cellában lévő 0,77 (lásd a 60-3 ábrát) például a B4 cellában körülbelül a normál véletlenszerű változó 77th százalékát, az 40 000 középértékét és a 10 000 szórását számítja ki.

Ebből a szakaszból megtudhatja, hogy miként használhatja a Monte Carlo-szimulációt döntéshozó eszközként. Tegyük fel, hogy a Valentin-napi kártya iránti keresletet az alábbi diszkrét véletlenszerű változó szabályozza:

Igény

Valószínűség:

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

Az üdvözlőlap értékesíti a $4,00-ot, és az egyes kártyák előállításának változó költsége $1,50. A maradék kártyákat kártyával kell eltüntetni, ha az $0,20 ára kártyával rendelkezik. Hány kártyát kell kinyomtatni?

Alapvetően minden lehetséges termelési mennyiséget (10 000, 20 000, 40 000 vagy 60 000) szimulálhat többször (például 1000 iterációk). Ezután határozzuk meg, hogy melyik rendelési mennyiség adja meg a maximális átlagos nyereséget a 1000-iterációk során. Az ebben a szakaszban szereplő információkat a Valentine. xlsx fájlban találja, az 60-4 ábrán látható módon. A B1: B11 cellákban lévő tartományneveket a C1: C11 cellákba kell rendelni. A G3: H6 cellatartomány a név keresésenevet adja. Az értékesítési ár és a költség paraméterek a C4: C6 cellákban szerepelnek.

Book Image

A C1 cellába beírhatja a próba-termelési mennyiséget (az 40 000 ebben a példában). Ezután hozzon létre egy véletlenszerű számot a C2 cellában a = Rand ()képlettel. A korábban ismertetett módon szimulálja a C3 cellában lévő kártya keresletét az FKERES (Rand, keres, 2)képlettel. (Az FKERES képletben a Rand a C3 cellához rendelt cella neve, a Rand függvényt nem.)

Az eladott egységek száma a termelés mennyiségének és keresletének kisebbje. A C8 cellában a min (termelt, kereslet) * unit_priceszámítja ki a bevételt. A C9 cellában a teljes termelési költséget számítja ki a * unit_prod_costképlettel.

Ha több kártyát is gyártunk, mint amennyit igénybe veszünk, akkor az egységek száma az egyenlő termelés mínusz a kereslet részen marad; egyéb esetekben nem maradnak meg egységek. Kiszámítjuk a C10 cellában lévő kivezetési költséget a * Ha (produced>demand, termelt – kereslet, 0) képlet unit_disp_cost. Végezetül a C11 cellában bevételként számítjuk ki a nyereséget – total_var_cost – total_disposing_cost.

Szeretnénk egy hatékony módszert az F9 billentyű többszöri lenyomására (például 1000) minden termelési mennyiség esetén, és az egyes mennyiségekre vonatkozóan minden egyes mennyiség esetében meg kell határozni a várható nyereséget. Ebben a helyzetben egy kétirányú adattáblázat jön létre a mentéshez. (Lásd a 15., "az adatTáblákhoz tartozó érzékenységi elemzés" című témakört, az adattáblákról bővebben.) Az ebben a példában használt adattábla az 60-5 ábrán látható.

Book Image

A cellatartomány A16: A1015 mezőbe írja be az 1 – 1000 (az 1000-kísérleteknek megfelelő) számokat. Ezekkel az értékekkel hozhat létre egy egyszerű módszert az első lépés a A16 cellába való beírásával. Jelölje ki a cellát, majd a Kezdőlap lap Szerkesztés csoportjában kattintson a kitöltésgombra, és válassza a sorozat lehetőséget az adatsor párbeszédpanel megjelenítéséhez. Az adatsor párbeszédpanelen, az 60-6 ábra mezőben adja meg az 1 és az 1000 leállítási értékét. Az adatsor területen jelölje be az oszlopok választógombot, majd kattintson az OKgombra. Az első oszlopban az 1 – 1000 számot kell beírni a A16 cellától kezdve.

Book Image

Ezután adjuk meg a lehetséges mennyiségeket (10 000, 20 000, 40 000, 60 000) a cellák B15: E15. Az egyes próbaverziók (1-től 1000-ig) és minden gyártási mennyiség eredményének kiszámítását szeretné használni. A következő képlettel hivatkozhatunk az adattábla (A15) bal felső cellájába (a cellában a C11 számított) eredményre.

Most már készen is áll arra, hogy az Excel szimulálja a 1000 ismétlését az egyes termelési mennyiségekre vonatkozóan. Jelölje ki a táblázat tartományát (A15: E1014), majd az adatlapon az adatEszközök csoportban kattintson a mi a teendő, majd az adatTábla elemre. Ha kétirányú adattáblát szeretne beállítani, válassza ki a gyártási mennyiséget (C1) a sor bemeneti cellájába, és jelöljön ki egy üres cellát (választottuk a cella I14) az oszlop bemeneti cellájába. Miután az OK gombra kattintott, az Excel szimulálja a 1000 igény szerinti értékét az egyes rendelési mennyiségekhez.

Ha meg szeretné érteni, hogy miért működik, fontolja meg az adattábla értékeinek értékét a C16: C1015. Mindegyik cellában az Excel a 20 000 értékét fogja használni a C1 cellában. Az C16-ban az 1 oszlop bemeneti cellájának értéke üres cellába kerül, és a C2 cellában lévő véletlenszerű szám újraszámítja a képletet. A megfelelő nyereséget ezután rögzítjük a C16 cellában. Ezután az oszlop cellája beviteli értékét egy üres cellába helyezi, a C2-es véletlenszám-számot pedig ismét újraszámítja a program. A megfelelő nyereséget a C17 cellába kell beírni.

A cella B13 a C13-ba való másolásával: a képlet átlaga (B16: B1015)a értéket E13 átlagát számítja ki az egyes termelési mennyiségekre. A B14 tetőterasszal a C14-ra történő másolással: a képletek szórása (B16: B1015)E14 számítja ki az egyes rendelési mennyiségekre szimulált nyereségük szórását. Minden alkalommal, amikor lenyomja az F9 billentyűt, az 1000 minden egyes rendelési mennyiséghez szimulálja a keresletet. Az 40 000-kártyák gyártása mindig a legnagyobb várható nyereséget adja eredményül. Ezért úgy tűnik, hogy a 40 000-kártyák gyártása a megfelelő döntés.

A kockázat hatása a döntésre     Ha 40 000-kártya helyett a 20 000-ot gyártotta, a várható haszon körülbelül 22%-kal, a kockázat (a nyereség szórásával mérve) pedig csaknem 73 százalékra csökken. Ezért, ha nagyon idegenkedik a kockázat, a 20 000-kártyák gyártása lehet a helyes döntés. Mellesleg a 10 000-kártyáinak előállításakor a 0 kártya szórása mindig fennáll, mert ha 10 000-kártyákat készítünk, mindig minden maradék nélkül fogjuk eladni.

Megjegyzés:  Ebben a munkafüzetben a számítási beállítás a táblák kivételével automatikusértékre van állítva. (Használja a számítási parancsot a képletek lap számítás csoportjában.) Ez a beállítás gondoskodik arról, hogy az adattábla ne legyen újraszámítva, hacsak nem nyomja le az F9 billentyűt, ami jó ötlet, mert a nagy adattábla a munkalapokra történő beíráskor minden alkalommal lelassítja a munkáját. Fontos tudni, hogy ebben a példában az F9 billentyű lenyomásakor a Mean profit fog megváltoznak. Ez azért történik, mert minden alkalommal, amikor megnyomja az F9 billentyűt, az 1000 egy másik sorozata van, amellyel az egyes rendelési mennyiségekhez igényeket hozhat létre.

Az átlagos haszon megbízhatósági intervalluma     Egy természetes kérdés, amely a helyzet, hogy milyen intervallumban van mi az 95 százalékos értéke, hogy az igaz átlagos haszon fog esni? Ezt az intervallumot az 95 százalékos megbízhatósági intervalluma jelenti az átlagos eredményhez. A 95 százalékos megbízhatósági intervalluma az alábbi képlettel számítja ki a szimulációs kimenet középértékét:

Book Image

A J11 cellában a 95 százalékos megbízhatósági intervallumának alsó határértékét számítja ki, ha 40 000-naptárakat készítenek a D13 – 1.96 * D14/SQRT (1000)képlettel. A J12 cellában a 95 százalékos megbízhatósági intervallumának felső korlátját számítja ki a D13 + 1.96 * D14/SQRT (1000)képlettel. Ezek a számítások a 60-7 Ábraban jelennek meg.

Book Image

95 százalékban tartjuk, hogy az 40 000-es naptárak megrendelése esetén az átlagos haszon a $56 687 és a $62 589 között legyen.

  1. A GMC viszonteladó úgy véli, hogy az 2005-beli küldöttek iránti keresletet általában a 200 és a szórás 30 értékkel osztják el. A megbízottja befogadójának költségei $25 000, és $40 000 megbízottat ad el. Az $30 000-ra nem adható meg teljes áron a teljes áron eladott megbízottak fele. A 200, az 220, a 240, az 260, az 280 vagy a 300-beli küldöttek megrendelését fontolgatja. Hányan kell megrendelnie?

  2. Egy kis szupermarketben próbál meg megállapítani, hogy a személyek magazin hány példányt kell hetente rendeznie. Úgy vélik, hogy a személyek iránti keresletet a következő diszkrét véletlenszerű változó szabályozza:

    Igény

    Valószínűség:

    15

    0,10

    20

    0,20

    25

    0,30

    30

    0,25

    35

    0,15

  3. A szupermarket minden egyes példánynál $1,00 fizet , és az $1,95-re értékesíti. Minden eladatlan példányt visszaadhat az $0,50-hoz. Hány példányban kell lennie az áruháznak?

További segítségre van szüksége?

Bármikor segítséget kérhet az Excel technikai közösségétől és az Answers-közösségtől, az Excel User Voice webhelyen pedig új funkciókra vagy fejlesztésekre tehet javaslatot.

Office-jártasság bővítése
Oktatóanyagok megismerése
Új szolgáltatások listájának lekérése
Részvétel az Office Insider programban

Hasznos volt az információ?

Köszönjük a visszajelzését!

Köszönjük visszajelzését. Jobbnak látjuk, ha az Office egyik támogatási szakemberéhez irányítjuk.

×