Introducere în simularea Monte Carlo în Excel

• Cine utilizează simularea Monte Carlo?

• Ce se întâmplă atunci când tastați =RAND() într-o celulă?

• Cum puteți simula valorile unei variabile aleatoare discrete?

• Cum puteți simula valorile unei variabile aleatoare normale?

• Cum poate o firmă de felicitări să determine câte felicitări să producă?

Dorim să estimăm cu exactitate probabilitățile evenimentelor incerte. De exemplu, care este probabilitatea ca fluxurile de numerar ale unui nou produs să aibă o valoare netă actualizată pozitivă (NPV)? Care este factorul de risc al portofoliului nostru de investiții? Simularea Monte Carlo ne permite să modeleze situații care prezintă incertitudine și apoi să le joace pe un computer de mii de ori.

În următoarele cinci capitole, veți vedea exemple cu modul în care puteți utiliza Excel pentru a efectua simulări Monte Carlo.

Multe companii folosesc simulare Monte Carlo ca o parte importantă a procesului lor de luare a deciziilor. Iată câteva exemple.

• General Motors, Proctor și Gamble, Pfizer, Bristol-Myers Squibb și Eli Lilly utilizează simularea pentru a estima atât rentabilitatea medie, cât și factorul de risc pentru noile produse. La GM, aceste informații sunt utilizate de către CEO pentru a determina produsele care intră pe piață.

• GM folosește simularea pentru activități precum prognozarea veniturilor nete pentru corporație, estimarea costurilor structurale și de achiziționare și determinarea susceptibilității sale la diferite tipuri de risc (cum ar fi modificările ratelor dobânzii și fluctuațiile cursului de schimb).

• Lilly foloseste simulare pentru a determina capacitatea optimă a plantelor pentru fiecare medicament.

• Proctor and Gamble folosesc simularea pentru a modela și optim riscul de schimb valutar.

• Sears folosește simularea pentru a determina câte unități din fiecare linie de produs ar trebui comandate de la furnizori - de exemplu, numărul de perechi de pantaloni Dockers care ar trebui comandați în acest an.

• Companiile de petrol și droguri folosesc simulare pentru a valoare "opțiuni reale", cum ar fi valoarea unei opțiuni de a extinde, contracta, sau amâna un proiect.

• Planificatori financiari folosesc simularea Monte Carlo pentru a determina strategii optime de investiții pentru pensionarea clienților lor.

Când tastați formula =RAND() într-o celulă, obțineți un număr care este la fel de probabil să își asume orice valoare între 0 și 1. Astfel, aproximativ 25 la sută din timp, ar trebui să obțineți un număr mai mic sau egal cu 0,25; aproximativ 10 la sută din timp ar trebui să obțineți un număr care este de cel puțin 0.90, și așa mai departe. Pentru a demonstra cum funcționează funcția RAND, aruncați o privire asupra Randdemo.xlsx fișierului, afișat în Figura 60-1.

Mai întâi, copiați din celula C3 în C4:C402 formula =RAND(). Apoi denumiți zona C3:C402 Date. Apoi, în coloana F, puteți să urmăriți media a 400 de numere aleatoare (celula F2) și să utilizați funcția COUNTIF pentru a determina fracțiile care sunt între 0 și 0,25, 0,25 și 0,50, 0,50 și 0,75 și 0,75 și 1. Când apăsați tasta F9, numerele aleatoare sunt recalculate. Observați că media a 400 de numere este întotdeauna de aproximativ 0,5 și că aproximativ 25% din rezultate sunt în intervale de 0,25. Aceste rezultate sunt în concordanță cu definiția unui număr aleator. De asemenea, rețineți că valorile generate de RAND în celule diferite sunt independente. De exemplu, dacă numărul aleator generat în celula C3 este un număr mare (de exemplu, 0,99), nu ne spune nimic despre valorile celorlalte numere aleatoare generate.

Să presupunem că cererea pentru un calendar este guvernată de următoarea variabilă aleatoare discretă:

Cerere	Probabilitate
10.000	0,10
20.000	0.35
40,000	0,3
60.000	0,25

Cum putem face ca Excel să joace sau să simuleze această cerere pentru calendare de mai multe ori? Trucul este de a asocia fiecare valoare posibilă a funcției RAND cu o cerere posibilă pentru calendare. Următoarea atribuire asigură o cerere de 10.000 de persoane în 10 procente din timp și așa mai departe.

Cerere	Număr aleator atribuit
10.000	Mai mic decât 0,10
20.000	Mai mare sau egal cu 0,10 și mai mic decât 0,45
40,000	Mai mare sau egal cu 0,45 și mai mic decât 0,75
60.000	Mai mare sau egal cu 0,75

Pentru a demonstra simularea cererii, priviți fișierul Discretesim.xlsx, afișat în figura 60-2 de pe pagina următoare.

Cheia simulării noastre este de a utiliza un număr aleator pentru a iniția o căutare din zona de tabel F2:G5 (denumită căutare). Numerele aleatoare mai mari sau egale cu 0 și mai mici decât 0,10 vor genera o cerere de 10.000; numerele aleatoare mai mari sau egale cu 0,10 și mai mici decât 0,45 vor genera o cerere de 20.000; numerele aleatoare mai mari sau egale cu 0,45 și mai mici decât 0,75 vor genera o cerere de 40.000; iar numerele aleatoare mai mari sau egale cu 0,75 vor genera o cerere de 60.000. Generați 400 de numere aleatoare copiind din C3 în C4:C402 formula RAND(). Apoi generați 400 de versiuni de încercare sau iterații ale cererii de calendar, copiind de la B3 la B4:B402 formula VLOOKUP(C3,căutare,2). Această formulă asigură că orice număr aleator mai mic decât 0,10 generează o cerere de 10.000, orice număr aleator între 0,10 și 0,45 generează o cerere de 20.000 și așa mai departe. În zona de celule F8:F11, utilizați funcția COUNTIF pentru a determina fracțiunea dintre cele 400 de iterații care produc fiecare cerere. Atunci când apăsați F9 pentru a recalcula numerele aleatoare, probabilitățile simulate sunt aproape de probabilitățile de cerere presupuse.

Dacă tastați în orice celulă formula NORMINV(rand(),mu,sigma), veți genera o valoare simulată a unei variabile aleatoare normale având o medie mu și o sigma deviație standard. Această procedură este ilustrată în fișierul Normalsim.xlsx, afișat în Figura 60-3.

Să presupunem că dorim să simulăm 400 de experimente sau iterații, pentru o variabilă aleatoare normală cu o medie de 40.000 și o abatere standard de 10.000. (Puteți să tastați aceste valori în celulele E1 și E2 și să denumiți aceste celule media , respectiv sigma.) Copierea formulei =RAND() din C4 în C5:C403 generează 400 de numere aleatoare diferite. Copierea de la B4 la B5:B403 formula NORMINV(C4,media,sigma) generează 400 de valori de încercare diferite față de o variabilă aleatoare normală cu o medie de 40.000 și o deviație standard de 10.000. Atunci când apăsați tasta F9 pentru a recalcula numerele aleatoare, media rămâne aproape de 40.000 și abaterea standard se apropie de 10.000.

În esență, pentru un număr aleator x, formula NORMINV(p,mu,sigma) generează a p-apercentilă a unei variabile aleatoare normale cu o medie mu și o sigma abatere standard. De exemplu, numărul aleator 0,77 din celula C4 (vedeți figura 60-3) generează în celula B4 aproximativ a 77-a percentilă a unei variabile aleatoare normale cu o medie de 40.000 și o abatere standard de 10.000.

În această secțiune, veți vedea cum poate fi utilizată simularea Monte Carlo ca instrument decizional. Să presupunem că cererea pentru o felicitare de Sfântul Valentin este guvernată de următoarea variabilă aleatoare discretă:

Cerere	Probabilitate
10.000	0,10
20.000	0.35
40,000	0,3
60.000	0,25

Felicitarea se vinde la 4,00 USD, iar costul variabil al producerii fiecărei felicitări este de $1,50. Cardurile rămase trebuie eliminate la prețul de 0,20 USD per card. Câte cărți de vizită trebuie imprimate?

Practic, simulăm fiecare posibilă cantitate de producție (10.000, 20.000, 40.000 sau 60.000) de mai multe ori (de exemplu, 1000 de iterații). Apoi determinăm cantitatea comandată cu profitul mediu maxim din cele 1000 de iterații. Puteți găsi datele pentru această secțiune în Valentine.xlsx fișier, afișate în Figura 60-4. Atribuiți numele zonelor din celulele B1:B11 celulelor C1:C11. Zonei de celule G3:H6 i se atribuie căutarea numelui. Parametrii noștri de preț de vânzare și de cost sunt inscriși în celulele C4:C6.

Puteți introduce o cantitate de producție de încercare (40.000 în acest exemplu) în celula C1. În continuare, creați un număr aleator în celula C2 cu formula =RAND(). După cum s-a descris anterior, simulați cererea pentru fișă în celula C3 cu formula VLOOKUP(rand,căutare,2). (În formula VLOOKUP, rand este numele celulei atribuite celulei C3, nu funcției RAND.)

Numărul de unități vândute este cu atât mai mic cu cât cantitatea de producție și cererea noastră sunt mai mici. În celula C8, ne calculați venitul cu formula MIN(produs,cerere)*unit_price. În celula C9, calculați costul total de producție cu formula produsă*unit_prod_cost.

Dacă producem mai multe cărți decât la cerere, numărul de unități rămase este egal cu producția minus cerere; în caz contrar, nu sunt rămase unități. Vom calcula costul de eliminare în celula C10 cu formula unit_disp_cost*IF(produs>cerere,produs-cerere,0). În cele din urmă, în celula C11, ne calculăm profitul ca venituri - total_var_cost-total_disposing_cost.

Am dori o modalitate eficientă de a apăsa F9 de mai multe ori (de exemplu, de 1000) pentru fiecare cantitate de producție și de a ține în raport profitul așteptat pentru fiecare cantitate. Această situație este una în care un tabel de date bidirecțional ne vine în ajutor. (Consultați Capitolul 15, "Analiza sensibilității cu tabelele de date", pentru detalii despre tabelele de date.) Tabelul de date utilizat în acest exemplu este afișat în Figura 60-5.

În zona de celule A16:A1015, introduceți numerele 1-1000 (corespunzătoare celor 1000 de încercări ale noastre). O modalitate simplă de a crea aceste valori este să începeți introducând 1 în celula A16. Selectați celula, apoi, pe fila Pornire din grupul Editare , faceți clic pe Umplere și selectați Serie pentru a afișa caseta de dialog Serie . În caseta de dialog Serie , afișată în Figura 60-6, introduceți valoarea pasului 1 și valoarea de oprire 1000. În zona Serie în , selectați opțiunea Coloane , apoi faceți clic pe OK. Numerele 1–1000 vor fi introduse în coloana A începând cu celula A16.

Apoi introducem posibilele cantități de producție (10.000, 20.000, 40.000, 60.000) în celulele B15:E15. Dorim să calculăm profitul pentru fiecare număr de încercare (de la 1 la 1000) și pentru fiecare cantitate de producție. Ne referim la formula pentru profit (calculată în celula C11) din celula din stânga sus a tabelului nostru de date (A15), introducând =C11.

Acum suntem gata să păcălim Excel pentru a simula 1000 de iterații ale cererii pentru fiecare cantitate de producție. Selectați zona de tabel (A15:E1014), apoi, în grupul Instrumente de date de pe fila Date, faceți clic pe Analiză circumstanțială, apoi selectați Tabel de date. Pentru a configura un tabel de date bidirecțional, alegeți cantitatea noastră de producție (celula C1) ca celulă de intrare rând și selectați orice celulă necompletată (am ales celula I14) ca Celulă de intrare coloană. După ce faceți clic pe OK, Excel simulează 1000 de valori de cerere pentru fiecare cantitate de comenzi.

Pentru a înțelege de ce funcționează acest lucru, luați în considerare valorile plasate de tabelul de date din zona de celule C16:C1015. Pentru fiecare dintre aceste celule, Excel va utiliza o valoare de 20.000 în celula C1. În C16, valoarea celulei de intrare a coloanei 1 este plasată într-o celulă necompletată, iar numărul aleator din celula C2 se recalculează. Profitul corespondent este apoi înregistrat în celula C16. Apoi, valoarea de intrare a celulei de coloană 2 este plasată într-o celulă necompletată și numărul aleator din C2 se recalculează din nou. Profitul corespondent este introdus în celula C17.

Copiind din celula B13 în C13:E13 formula AVERAGE(B16:B1015), calculăm profitul simulat mediu pentru fiecare cantitate de producție. Copiind din celula B14 în C14:E14 formula STDEV(B16:B1015), calculăm abaterea standard a profiturilor simulate pentru fiecare cantitate de comandă. De fiecare dată când apăsați F9, 1000 de iterații de cerere sunt simulate pentru fiecare cantitate de comandă. Producerea a 40.000 de carduri dă întotdeauna cel mai mare profit estimat. Prin urmare, se pare că producerea de 40.000 de carduri este decizia corectă.

Impactul riscului asupra deciziei noastre      Dacă producem 20.000 în loc de 40.000 de carduri, profitul așteptat scade cu aproximativ 22%, dar riscul nostru (măsurat prin abaterea standard a profitului) scade cu aproape 73%. Prin urmare, dacă suntem extrem de averse la risc, producând 20.000 de carduri ar putea fi decizia corectă. De altfel, producerea a 10.000 de carduri are întotdeauna o abatere standard de 0 cartele, deoarece, dacă producem 10.000 de carduri, vom vinde întotdeauna toate acestea, fără nici o stângaovers.

Interval de încredere pentru profitul mediu      O întrebare naturală de pus în această situație este, în ce interval suntem 95 la sută siguri că profitul mediu adevărat va scădea? Acest interval se numește intervalul de încredere de 95% pentru profitul mediu. Un interval de încredere de 95% pentru media rezultatelor simulării este calculat prin următoarea formulă:

În celula J11, calculați limita inferioară pentru intervalul de încredere de 95% pe profit mediu atunci când 40.000 de calendare sunt produse cu formula D13–1,96*D14/SQRT(1000). În celula J12, calculați limita superioară pentru intervalul de încredere de 95% cu formula D13+1,96*D14/SQRT(1000). Aceste calcule sunt afișate în Figura 60-7.

Suntem 95 la sută sigur că profitul nostru medie atunci când 40.000 calendare sunt ordonate este între 56.687 dolari și 62.589 dolari.

1. Un dealer GMC consideră că cererea pentru 2005 Trimisii vor fi distribuite în mod normal, cu o medie de 200 și abaterea standard de 30. Costul de a primi un trimis este de 25.000 de dolari, și vinde un trimis pentru 40.000 de dolari. Jumătate din toate trimisii nu vândute la preț întreg pot fi vândute pentru 30.000 dolari. El ia în considerare ordonarea a 200, 220, 240, 260, 280 sau 300 de trimisi. Câte ar trebui să comande?

2. Un supermarket mic este încercarea de a determina cât de multe copii de Persoane revista ar trebui să comanda în fiecare săptămână. Ei cred că cererea lor de Persoane este guvernată de următoarea variabilă aleatoare discretă:

   Cerere	Probabilitate
   15	0,10
   20	0.20
   25	0.30
   30	0,25
   35	0,15

3. Supermarket plătește 1.00 dolari pentru fiecare copie de Persoane și vinde-l pentru 1.95 dolari. Fiecare copie nevândută poate fi returnată la prețul de 0,50 USD. Câte copii de Persoane ar trebui să facă comanda din magazin?