Introducere în simularea Monte Carlo în Excel

Notă: Dorim să vă oferim cel mai recent conținut de ajutor, cât mai rapid posibil, în limba dvs. Această pagină a fost tradusă automatizat și poate conține erori gramaticale sau inexactități. Scopul nostru este ca acest conținut să vă fie util. Vă rugăm să ne spuneți dacă informațiile v-au fost utile, în partea de jos a acestei pagini. Aici se află articolul în limba engleză, ca să îl puteți consulta cu ușurință.

Acest articol a fost adaptat din analize de date și modelare de firme din Microsoft Excel de Wayne L. Winston.

  • 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 aleatorii separate?

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

  • Cum poate o firmă de felicitare să determine câte cărți să produceți?

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

Notă:  Simularea numelui Monte Carlo provine din simulările computerizate efectuate în anii 1930 și 1940 pentru a estima probabilitatea ca reacția în lanț necesară pentru o bombă atomică să detoneze să funcționeze cu succes. Fizicienii implicați în acest lucru au fost mari fani ai jocurilor de noroc, așa că au dat simulărilor numele de cod Monte Carlo.

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

Multe companii utilizează simularea Monte Carlo ca parte importantă a procesului decizional. Iată câteva exemple.

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

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

  • Lilly folosește simularea pentru a determina capacitatea optimă a plantelor pentru fiecare medicament.

  • Proctor și Gamble utilizează simularea pentru a modela și pentru a acoperi optim riscul valutar din străinătate.

  • Sears utilizează simularea pentru a determina câte unități din fiecare linie de produs ar trebui să fie comandate de la furnizori-de exemplu, numărul de perechi de pantaloni de andocare care ar trebui să fie ordonați anul acesta.

  • Companiile petroliere și farmaceutice utilizează simularea pentru a evalua valoarea "Opțiuni reale", cum ar fi valoarea unei opțiuni pentru a extinde, a contracta sau a amâna un proiect.

  • Planificatorii financiari utilizează simularea Monte Carlo pentru a determina strategii de investiții optime pentru retragerea clienților.

Atunci când tastați formula = rand () într-o celulă, veți obține un număr care este la fel de probabil să își asume orice valoare între 0 și 1. Astfel, în aproximativ 25 de procente din timp, ar trebui să obțineți un număr mai mic sau egal cu 0,25; aproximativ 10 procente 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 modul în care funcționează funcția RAND, aruncați o privire la fișierul Randdemo. xlsx, afișat în Figura 60-1.

Book Image

Notă:  Atunci când deschideți fișierul Randdemo. xlsx, nu veți vedea aceleași numere aleatoare afișate în Figura 60-1. Funcția RAND recalculează automat numerele pe care le generează atunci când se deschide o foaie de lucru sau când se introduc informații noi în foaia de lucru.

Mai întâi, copiați din celula C3 în C4: C402 formula = rand (). Apoi, denumiți zona C3: dateC402. Apoi, în coloana F, puteți să urmăriți media numerelor aleatoare 400 (celula F2) și să utilizați funcția COUNTIF pentru a determina fracțiunile cuprinse între 0 și 0,25, 0,25 și 0,50, 0,50 și 0,75 și 0,75 și 1. Atunci când apăsați tasta F9, numerele aleatoare sunt recalculate. Observați că media numerelor 400 este întotdeauna de aproximativ 0,5, iar aproximativ 25 de procente din rezultate sunt la intervale de 0,25. Aceste rezultate sunt compatibile 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), acesta nu ne spune nimic despre valorile altor numere aleatoare generate.

Să presupunem că solicitarea pentru un calendar este reglementată de următoarea variabilă aleatoare separată:

Cerere

Probabilitate

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

Cum putem face ca Excel să se redea sau să simuleze această cerere pentru calendare de mai multe ori? Trucul este să asociați fiecare valoare posibilă a funcției RAND cu o posibilă cerere pentru calendare. Următoarea atribuire asigură faptul că o cerere de 10.000 va avea loc la 10% 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, uitați-vă la fișierul Discretesim. xlsx, afișat în Figura 60-2 pe pagina următoare.

Book Image

Cheia pentru simularea noastră este să utilizăm un număr aleator pentru a iniția o căutare din zona de tabel F2: G5 (denumită Lookup). 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 numere aleatoare 400 prin copierea de la C3 la C4: C402 formula rand (). Apoi generați teste 400 sau iterații, de solicitare a calendarului, prin copierea de la B3 la B4: B402 formulei VLOOKUP (C3, Lookup, 2). Această formulă asigură faptul că un 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 din iterațiile 400 care generează fiecare solicitare. Când apăsați F9 pentru a recalcula numerele aleatoare, probabilitățile simulate sunt aproape de probabilitățile cerute de noi.

Dacă tastați în orice celulă formula NORMINV (rand (), Mu, Sigma), veți genera o valoare simulată a unei variabile aleatoare normale, cu o medie Mu și abaterea standard Sigma. Această procedură este ilustrată în fișierul Normalsim. xlsx, afișat în Figura 60-3.

Book Image

Să presupunem că vrem să simulăm testele 400 sau iterațiile, 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 și, respectiv, Sigma.) Copierea formulei = rand () de la C4 la C5: C403 generează 400 de numere aleatoare diferite. Copierea de la B4 la B5: B403 formulei NORMINV (C4, mean, Sigma) generează 400 diferite valori de încercare dintr-o variabilă aleatoare normală, cu o medie de 40.000 și o abatere 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 de aproape de 10.000.

În esență, pentru un număr aleator x, formula NORMINV (p, Mu, Sigma) generează procentul pal unei variabile aleatoare normale, cu o medie Mu și o abatere standard Sigma. De exemplu, numărul aleator 0,77 din celula C4 (Consultați Figura 60-3) generează în celula B4 aproximativ 77 de procente dintr-o variabilă aleatoare normală, cu o medie de 40.000 și o abatere standard de 10.000.

În această secțiune veți vedea cum se poate utiliza simularea Monte Carlo ca instrument de luare a deciziilor. Să presupunem că solicitarea pentru o felicitare de Valentine ' s Day este reglementată de următoarea variabilă aleatoare separată:

Cerere

Probabilitate

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

Felicitarea se vinde pentru $4,00, iar costul variabil al producției fiecărei cărți este $1,50. Cardurile rămase trebuie eliminate la un cost de $0,20 per fișă. Câte cărți ar trebui să fie imprimate?

Practic, simulăm de mai multe ori fiecare cantitate posibilă de producție (10.000, 20.000, 40.000 sau 60.000) (de exemplu, 1000 iterații). Apoi determinăm ce cantitate de comandă generează profitul mediu maxim peste iterațiile 1000. Puteți găsi datele pentru această secțiune în fișierul Valentine. xlsx, afișat în Figura 60-4. Atribuiți numele de zone din celulele B1: balasa la celulele C1: C11. Zona de celule G3: H6 este atribuită Căutarenume. Prețul nostru de vânzări și parametrii de cost sunt inscriși în celulele C4: C6.

Book Image

Puteți introduce o cantitate de producție de încercare (40.000 în acest exemplu) în celula C1. Apoi, creați un număr aleator în celula C2 cu formula = rand (). Așa cum s-a descris anterior, simulați solicitarea cardului din celula C3 cu formula VLOOKUP (rand, Lookup, 2). (În formula VLOOKUP, rand este numele de celulă atribuit celulei C3, nu funcția rand.)

Numărul de unități vândute este mai mic decât cantitatea și oferta noastră de producție. În celula C8, calculați veniturile noastre cu formula min (produs, cerere) * unit_price. În celula C9, calculați costul total al producției cu formula produsă * unit_prod_cost.

Dacă producem mai multe cărți decât sunt solicitate, numărul de unități rămase peste egal cu producția minus cerere; altfel, nu mai rămân unități. Ne calculăm costurile de eliminare în celula C10 cu formula unit_disp_cost * if (produced>demand, produs – Demand, 0). În cele din urmă, în celula C11, ne calculăm profitul ca venit – total_var_cost-total_disposing_cost.

Am dori o modalitate eficientă de a apăsa F9 de mai multe ori (de exemplu, 1000) pentru fiecare cantitate de producție și pentru a corespunde profitului așteptat pentru fiecare cantitate. Această situație este una în care un tabel de date cu două căi vine în ajutorul nostru. (Consultați capitolul 15, "analiză de sensibilitate cu tabele de date", pentru detalii despre tabelele de date.) Tabelul de date utilizat în acest exemplu este afișat în Figura 60-5.

Book Image

În zona de celule A16: A1015, introduceți numerele 1 – 1000 (corespunzând testelor 1000). O modalitate simplă de a crea aceste valori este să începeți prin a introduce 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 o valoare pas pentru 1 și o valoare de oprire de 1000. În zona serie din , 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.

Book Image

Apoi vom introduce cantitățile de producție posibile (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 fiecare cantitate de producție. Ne referim la formula pentru profit (calculată în celula C11) în 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 iterațiile 1000 ale cererii pentru fiecare cantitate de producție. Selectați zona de tabel (A15: E1014), apoi, în grupul Instrumente de date din fila date, faceți clic pe ce analiză IF, apoi selectați tabel de date. Pentru a configura un tabel de date cu două căi, alegeți cantitatea de producție (celula C1) ca celulă de intrare rând și selectați orice celulă necompletată (am ales celule I14) ca celulă de intrare coloană. După ce faceți clic pe OK, Excel simulează valorile de solicitare 1000 pentru fiecare cantitate de comandă.

Pentru a înțelege de ce funcționează, luați în considerare valorile plasate de tabelul de date în 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ă și numărul aleator din celula C2 recalculează. Profitul corespunzător este apoi înregistrat în celula C16. Apoi, valoarea de intrare în celula de coloană 2 este plasată într-o celulă necompletată, iar numărul aleator din C2 recalculează din nou. Profitul corespunzător este introdus în celula C17.

Prin copierea din B13 celulei în C13: E13a medie a formulei (B16: B1015), calculăm profitul mediu simulat pentru fiecare cantitate de producție. Prin copierea din B14 celulei î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 iterații ale cererii sunt simulate pentru fiecare cantitate de comandă. Producția de cărți 40.000 produce întotdeauna cel mai mare profit așteptat. Prin urmare, se pare că producția de cărți 40.000 este decizia corectă.

Impactul riscului asupra deciziei noastre     Dacă am produs 20.000 în locul cărților 40.000, profitul nostru așteptat scade aproximativ 22 de procente, dar riscul nostru (măsurat prin abaterea standard a profitului) scade aproape 73%. Prin urmare, dacă nu ne putem opune riscului, producția de cărți 20.000 poate fi decizia corectă. Întâmplător, producția de cărți 10.000 are întotdeauna o abatere standard de 0 cărți, deoarece, dacă producem 10.000 de cărți, le vom vinde mereu fără resturi.

Notă:  În acest registru de lucru, opțiunea de calcul este setată la Automatic, cu excepția tabelelor. (Utilizați comanda calcul din grupul calcul din fila formule.) Această setare asigură faptul că tabelul nostru de date nu se va recalcula dacă nu apăsați F9, ceea ce este o idee bună, deoarece un tabel de date mare vă va încetini lucrul dacă se calculează de fiecare dată când tastați ceva în foaia de lucru. Rețineți că, în acest exemplu, de fiecare dată când apăsați F9, profitul mediu se va modifica. Acest lucru se întâmplă deoarece de fiecare dată când apăsați F9, se utilizează o altă secvență de numere aleatoare 1000 pentru a genera solicitări pentru fiecare cantitate de comandă.

Interval de încredere pentru profitul mediu     O întrebare firească pentru a întreba în această situație este, în ce interval suntem 95 la sută siguri că adevăratul profit va cădea? Acest interval se numește intervalul de încredere 95% pentru profitul mediu. Un interval de încredere de 95 la sută pentru media oricărui rezultat al simulării este calculat prin următoarea formulă:

Book Image

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

Book Image

Suntem 95 la sută siguri că profitul nostru mediu atunci când sunt comandate calendare 40.000 este între $56.687 și $62.589.

  1. Un dealer GMC consideră că cererea pentru trimișii 2005 va fi distribuită în mod normal cu o medie de 200 și abaterea standard de 30. Costul său de a primi un emisar este $25.000, iar el vinde un emisar pentru $40.000. Jumătate din toți trimișii care nu sunt vânduți la prețul integral pot fi vânduți pentru $30.000. El are în vedere comanda comenzilor 200, 220, 240, 260, 280 sau 300. Câte ar trebui să comande?

  2. Un mic supermarket încearcă să determine numărul de copii ale revistei People pe care ar trebui să le comande în fiecare săptămână. Aceștia consideră că solicitarea lor pentru persoane este reglementată de următoarea variabilă aleatoare separată:

    Cerere

    Probabilitate

    15

    0,10

    20

    0,20

    25

    0,30

    30

    0,25

    35

    0,15

  3. Supermarketul plătește $1,00 pentru fiecare copie a persoanelor și o vinde pentru $1,95. Fiecare copie nevândută poate fi returnată pentru $0,50. Câte copii ale persoanelor trebuie să facă comanda magazin?

Aveți nevoie de ajutor suplimentar?

Puteți întreba întotdeauna un expert de la Excel Tech Community, puteți obține asistență de la comunitatea Answers sau puteți sugera o caracteristică nouă sau o îmbunătățire pe Excel UserVoice.

Extindeți-vă competențele Office
Explorați instruirea
Fiți primul care obține noile caracteristici
Alăturați-vă utilizatorilor Office Insider

Au fost utile aceste informații?

Vă mulțumim pentru feedback!

Vă mulțumim pentru feedback! Se pare că ar fi util să luați legătura cu unul dintre agenții noștri de asistență Office.

×