A Monte Carlo-szimuláció bemutatása az Excelben

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

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

• Hogyan szimulálhatók a különálló véletlenszerű változók értékei?

• Hogyan szimulálható egy normál véletlenszerű változó értékei?

• Hogyan határozhatja meg egy üdvözlőlap-cég, hogy hány kártyát kell előállítania?

Szeretnénk pontosan megbecsülni a bizonytalan események valószínűségét. Mi a valószínűsége például annak, hogy egy új termék pénzáramlása pozitív nettó jelenérték (NPV) lesz? Mi a kockázati tényező a befektetési portfóliónkban? A Monte Carlo szimuláció lehetővé teszi számunkra, hogy olyan helyzeteket modellezhessünk, amelyek bizonytalanságot jeleznek, majd több ezer alkalommal lejátszhatjuk őket egy számítógépen.

A következő öt fejezetben példákat láthat arra, hogyan hajthat végre Monte Carlo-szimulációkat az Excel használatával.

Számos vállalat használja a Monte Carlo szimulációt a döntéshozatali folyamat fontos részeként. Íme néhány példa.

• A General Motors, a Proctor és a Gamble, a Pfizer, a Bristol-Myers a Squibb és az Eli Lilly szimulációval becsüli meg az új termékek átlagos megtérülését és kockázati tényezőjét. A GM-nél a vezérigazgató ezt az információt arra használja fel, hogy meghatározza, mely termékek kerülnek forgalomba.

• A GM szimulációt használ olyan tevékenységekhez, mint a vállalat nettó jövedelmének előrejelzése, a strukturális és vásárlási költségek előrejelzése, valamint a különböző típusú kockázatokra való érzékenységének meghatározása (például kamatlábváltozások és árfolyam-ingadozások).

• Lilly szimulációval határozza meg az egyes gyógyszerek optimális növénykapacitását.

• A Proctor és a Gamble szimulációt használ a devizakockázat modellezésére és optimális fedezetére.

• A sears szimulációt használ annak meghatározására, hogy az egyes terméksorok hány egységét kell megrendelni a szállítóktól– például az idén megrendelendő Dockers nadrágpárok számát.

• Az olaj- és gyógyszeripari vállalatok szimulációval értékelik a "valós lehetőségeket", például egy projekt bővítésére, szerződésére vagy elhalasztására vonatkozó lehetőség értékét.

• A pénzügyi tervezők Monte Carlo szimulációval határozzák meg az ügyfelek nyugdíjazásához szükséges optimális befektetési stratégiákat.

Amikor egy cellába beírja az =RAND() képletet, olyan számot kap, amely valószínűleg 0 és 1 közötti értéket feltételez. Így az idő körülbelül 25 százalékában 0,25-nél kisebb vagy egyenlő számot kell kapnia; körülbelül 10 százaléka az idő kell kapnia egy számot, amely legalább 0,90, és így tovább. A RAND függvény működésének bemutatásához tekintse meg a 60-1. ábrán látható fájl Randdemo.xlsx.

Először másolja a C3 cellából a C4:C402 cellába az =RAND()képletet. Ezután adja a tartománynak a C3:C402 Data nevet. Ezután az F oszlopban nyomon követheti a 400 véletlenszerű szám (F2 cella) átlagát, és a DARABTELI függvénnyel meghatározhatja a 0 és 0,25, 0,25 és 0,50, 0,50 és 0,75, valamint 0,75 és 1 közötti törteket. Az F9 billentyű lenyomásakor a véletlenszerű számok újra lesznek számítva. Figyelje meg, hogy a 400 szám átlaga mindig körülbelül 0,5, az eredmények körülbelül 25 százaléka pedig 0,25 intervallumban van. Ezek az eredmények összhangban vannak egy véletlenszerű szám definíciójával. Azt is vegye figyelembe, hogy a RAND által a különböző cellákban létrehozott értékek függetlenek. Ha például a C3 cellában generált véletlenszerű szám nagy szám (például 0,99), akkor semmit sem árul el a többi létrehozott véletlenszerű szám értékéről.

Tegyük fel, hogy egy naptár igényét a következő diszkrét véletlenszerű változó szabályozza:

Kereslet	Valószínűség:
10 000	0,10
20 000	0.35
40,000	0,3
60 000	0,25

Hogyan tudja az Excel sokszor lejátszani vagy szimulálni ezt az igényt a naptárakra vonatkozóan? A trükk az, hogy a RAND függvény minden lehetséges értékét társítja egy lehetséges naptárigényhez. A következő hozzárendelés biztosítja, hogy a 10 000-et igénylő igények az idő 10 százalékában fordulnak elő, és így tovább.

Kereslet	Hozzárendelt véletlenszerű szám
10 000	Kevesebb, mint 0,10
20 000	0,10-nél nagyobb vagy egyenlő, 0,45-nél kisebb
40,000	0,45-nél nagyobb vagy egyenlő, 0,75-nél kisebb
60 000	0,75-nél nagyobb vagy egyenlő

Az igény szimulációjának szemléltetéséhez tekintse meg a fájl Discretesim.xlsx, amely a következő oldalon, a 60-2. ábrán látható.

A szimuláció kulcsa az, hogy véletlenszerű számmal kezdeményezzük a kereséseket az F2:G5 táblatartományból ( név: keresés). A 0-nál nagyobb vagy azzal egyenlő és 0,10-nél kisebb véletlenszerű számok 10 000 keresletet eredményeznek; a 0,10-nél nagyobb vagy azzal egyenlő és 0,45-nél kisebb véletlenszerű számok 20 000 keresletet eredményeznek; a 0,45-nél nagyobb vagy azzal egyenlő és 0,75-nél kisebb véletlenszerű számok 40 000 keresletet eredményeznek; és a 0,75-nél nagyobb vagy azzal egyenlő véletlenszerű számok 60 000 keresletet eredményeznek. 400 véletlenszerű számot hoz létre a C3-ból a C4:C402-be másolva a RAND() képletet. Ezután a B3-ból a B4:B402-be másolja az FKERES(C3,keresés,2) képletet, így 400 próba vagy iteráció jön létre a naptárigényből. Ez a képlet biztosítja, hogy a 0,10-nél kisebb véletlenszerű számok 10 000-et, a 0,10 és 0,45 közötti véletlenszerű számok pedig 20 000-et követelnek meg stb. Az F8:F11 cellatartományban a DARABTELI függvénnyel állapítsa meg az egyes igényeket eredményező 400 iteráció törtrészét. Amikor az F9 billentyű lenyomásával újraszámítjuk 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 bármely cellába beírja a NORMINV(rand(),mu,sigma) képletet, akkor egy normál véletlenszerű változó szimulált értékét fogja generálni, amelynek középértéke mu és szórás szigma. Ez az eljárás a 60-3. ábrán látható Normalsim.xlsx fájlban látható.

Tegyük fel, hogy 400 kísérletet vagy iterációt szeretnénk szimulálni egy 40 000 középértékkel és 10 000 szórással rendelkező normál véletlenszerű változóhoz. (Ezeket az értékeket beírhatja az E1 és az E2 cellába, és a középértékeket és a szigmát is elnevezheti.) Az =RAND() képlet C4-ről C5:C403-ra másolása 400 különböző véletlenszerű számot eredményez. A B4-ből a B5:B403-ba másolva a NORMINV(C4,középérték,szigma) képlet 400 különböző próbaértéket hoz létre egy normál véletlenszerű változóból 40 000 középértékkel és 10 000 szórással. Amikor az F9 billentyű lenyomásával újraszámítjuk a véletlenszerű számokat, a középérték közel 40 000, a szórás pedig közel 10 000 marad.

Lényegében egy x véletlenszerű szám esetében a NORMINV(p,mu,sigma) képlet egy normál véletlenszerű változó p-edikpercentilisét hozza létre átlagos mu és szórási szigmával. Például a C4 cellában lévő 0,77 véletlenszerű szám (lásd a 60-3. ábrát) a B4 cellában egy 40 000 középértékkel és 10 000 szórással rendelkező normál véletlenszerű változó 77. percentilisét hozza létre.

Ebben a szakaszban megtudhatja, hogyan használható a Monte Carlo szimuláció döntéshozatali eszközként. Tegyük fel, hogy a Valentin-napi kártya igénylését a következő diszkrét véletlenszerű változó szabályozza:

Kereslet	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 4,00 dollárért értékesít, és az egyes kártyák előállításának változó költsége 1,50 dollár. A fennmaradó kártyákat kártyánként 0,20 USD áron kell megsemmisíteni. 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) sokszor szimulálunk (például 1000 iterációt). Ezután meghatározzuk, hogy melyik rendelési mennyiség eredményezi a maximális átlagos nyereséget az 1000 iterációhoz képest. Ennek a szakasznak az adatait a fájl Valentine.xlsx találja, a 60-4. ábrán látható módon. A B1:B11 cellatartományneveket a C1:C11 cellához rendelheti. A névkeresés a G3:H6 cellatartományhoz van rendelve. Az értékesítési ár és a költségparaméterek a C4:C6 cellába kerülnek.

A C1 cellába beírhat egy próbatermelési mennyiséget (ebben a példában 40 000-et). Ezután hozzon létre egy véletlenszerű számot a C2 cellában az =RAND()képlettel. Ahogy korábban említettem, a C3 cellában lévő kártya iránti keresletet az FKERES(futtatás,keresés,2)képlettel szimulálhatja. (Az FKERES képletben a rand a C3 cellához rendelt cellanév, nem pedig a RAND függvény.)

Az eladott egységek száma a termelési mennyiség és a kereslet kisebb. A C8 cellában a bevételt a MIN(előállított,kereslet)*unit_price képlettel számítja ki. A C9 cellában a teljes termelési költséget az előállított*unit_prod_cost képlettel számítja ki.

Ha a keresletnél több kártyát állítunk elő, akkor a hátrahagyott egységek száma egyenlő a termelés mínusz a keresletgel; ellenkező esetben nem maradnak meg az egységek. Az ártalmatlanítási költséget a C10 cellában számítjuk ki a unit_disp_cost*HA(>kereslet,előállított-kereslet,0) képlettel. Végü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ódot az F9 többszöri lenyomására (például 1000) az egyes gyártási mennyiségekre, és az egyes mennyiségek várható nyereségére. Ebben a helyzetben egy kétirányú adattábla áll a segítségünkre. (Az adattáblákkal kapcsolatos részletekért lásd a "Bizalmassági elemzés adattáblákkal" című 15. fejezetet.) Az ebben a példában használt adattábla a 60-5. ábrán látható.

Az A16:A1015 cellatartományban adja meg az 1–1000 számot (amely megfelel az 1000 kísérletnek). Az értékek létrehozásának egyik egyszerű módja, ha először beírja az 1 értéket az A16 cellába. Jelölje ki a cellát, majd a Szerkesztés csoport Kezdőlap lapján kattintson a Kitöltés gombra, majd válassza az Adatsor lehetőséget az Adatsor párbeszédpanel megjelenítéséhez. A 60-6. ábrán látható Adatsor párbeszédpanelen adja meg az 1 lépés értékét és az 1000-et. Az Adatsor a következőben területen válassza az Oszlopok lehetőséget, majd kattintson az OK gombra. Az 1–1000 számot a rendszer az A oszlopba írja be az A16 cellától kezdve.

A következő lépésben a B15:E15 cellába írjuk be a lehetséges gyártási mennyiségeket (10 000, 20 000, 40 000, 60 000). Minden egyes próbaszám (1–1000) és az egyes termelési mennyiségek nyereségét szeretnénk kiszámítani. Az adattábla (A15) bal felső cellájában az =C11 beírásával hivatkozunk a nyereség képletére (amely a C11 cellában van kiszámítva).

Most már készen állunk arra, hogy az Excelt az egyes termelési mennyiségek 1000 iterációjának szimulálására szimuláljuk. Jelölje ki a táblázattartományt (A15:E1014), majd az Adatok lap Adateszközök csoportjában kattintson a Lehetőségelemzés elemre, majd válassza az Adattábla lehetőséget. Kétirányú adattábla beállításához válassza ki a termelési mennyiséget (C1 cellát) sorbemeneti cellaként, és jelöljön ki egy üres cellát (az I14-es cellát választottuk) oszlopbemeneti cellaként. Az OK gombra kattintás után az Excel 1000 keresleti értéket szimulál minden rendelési mennyiséghez.

Ennek megértéséhez vegye figyelembe az adattábla által a C16:C1015 cellatartományban elhelyezett értékeket. Ezen cellák mindegyikéhez az Excel 20 000 értéket fog használni a C1 cellában. A C16-ban az 1 oszlop bemeneti cellaértéke egy üres cellába kerül, és a C2 cellában lévő véletlenszerű szám újraszámolódik. A megfelelő nyereséget ezután a C16 cellában rögzítik. Ezután a 2-es oszlopcella bemeneti értéke egy üres cellába kerül, és a C2 cellában lévő véletlenszerű szám újraszámít. A megfelelő nyereséget a C17 cellába kell beírni.

A B13 cellából a C13:E13 cellába az ÁTLAG(B16:B1015) képlet másolásával kiszámítjuk az egyes termelési mennyiségek szimulált átlagos nyereségét. A B14 cellából a C14:E14 cellába másolva az STDEV(B16:B1015) képletet az egyes rendelési mennyiségek szimulált nyereségének szórását számítjuk ki. Az F9 billentyű lenyomása esetén minden rendelési mennyiséghez 1000 iterációt szimulál a rendszer. A 40 000 kártya előállítása mindig a legnagyobb várható nyereséget eredményezi. Ezért úgy tűnik, hogy 40 000 kártya előállítása a megfelelő döntés.

A kockázat hatása a döntésünkre      Ha 40 000 kártya helyett 20 000-et állítunk elő, a várható nyereségünk körülbelül 22 százalékkal csökken, de a kockázatunk (a nyereség szórásával mérve) majdnem 73 százalékkal csökken. Ezért, ha rendkívül ellentétesek vagyunk a kockázattal, 20 000 kártya létrehozása lehet a helyes döntés. Egyébként a 10 000 kártya előállítása mindig 0-s szórással rendelkezik, mert ha 10 000 kártyát állítunk elő, akkor az összeset mindig maradék nélkül értékesítjük.

Átlagos nyereség megbízhatósági intervalluma      Természetes kérdés, hogy ebben a helyzetben milyen intervallumban vagyunk 95 százalék biztos abban, hogy az igazi átlagos nyereség csökkenni fog? Ezt az intervallumot az átlagos nyereség 95%-os megbízhatósági intervallumának nevezzük. A szimulációs kimenetek középértékének 95%-os megbízhatósági intervallumát a következő képlet számítja ki:

A J11 cellában az átlagos nyereség 95%-os megbízhatósági intervallumának alsó határát számítja ki, ha 40 000 naptár jön létre a D13–1,96*D14/SQRT(1000) képlettel. A J12 cellában a 95%-os megbízhatósági intervallum felső korlátját a D13+1,96*D14/SQRT(1000) képlettel számítja ki. Ezek a számítások a 60-7. ábrán láthatók.

95%-os biztosak vagyunk abban, hogy a 40 000 naptár megrendelése esetén az átlagos nyereség 56 687 és 62 589 dollár között van.

1. Egy GMC-kereskedő úgy véli, hogy a 2005-ös küldöttek iránti kereslet általában 200 középértékkel és 30 szórással lesz elosztva. Az ára 25 000 dollár, és elad egy követet 40 000 dollárért. A nem teljes áron eladott küldöttek fele 30 000 dollárért értékesíthető. Azt fontolgatja, hogy 200, 220, 240, 260, 280 vagy 300 küldöttet rendel. Hányat rendeljen?

2. Egy kis szupermarket megpróbálja meghatározni, hogy hány példányban Kapcsolatok magazinnak hetente kell rendelnie. Úgy vélik, hogy a Kapcsolatok iránti igényüket a következő diszkrét véletlenszerű változó szabályozza:

   Kereslet	Valószínűség:
   15	0,10
   20	0.20
   25	0.30
   30	0,25
   35	0,15

3. A szupermarket 1,00 dollárt fizet minden Kapcsolatok példányáért, és 1,95 dollárért értékesíti. Minden el nem adott példány 0,50 dollárért adható vissza. Hány példányban kell Kapcsolatok az áruházi rendelésnek?