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 Microsoft Excel analiza datelor și modelarea Business 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 să simulați valorile de o variabilă aleatoare discrete?

  • Cum puteți să simulați valorile de o variabilă aleatoare normală?

  • Cum pot determina o felicitare firmă câte cărți pentru a genera?

Am dori pentru a estima corect probabilitatile nesigur evenimente. De exemplu, care este probabilitatea că un produs nou de fluxuri de numerar va avea o pozitiv valoarea netă actualizată (NPV)? Ce este factor de risc portofoliului investiție? Simularea Monte Carlo ne permite să model situații care prezintă incertitudine și apoi redați-le pe un computer mii de câte ori.

Notă: Numele simularea Monte Carlo vine de la computer simulări efectuate în timpul 1930 și 1940 pentru a estima probabilitatea ca reacție lanț necesare pentru o bombă atom pentru a detona vi se potrivește cu succes. Fizicienii implicate în acest lucru au fost ventilatoarele mare de joc, astfel încât acestea v-a oferit simulări numele codului Monte Carlo.

În continuare cinci capitole, veți vedea exemple de cum puteți utiliza Excel pentru a efectua simulări Monte Carlo.

Multe companii utilizați simularea Monte Carlo ca o parte importantă a lor procesul decizional. Iată câteva exemple.

  • Motoare generale, Proctor și Gamble, Pfizer, Bristol-Myers Squibb și Eli Lilly utilizează simulare pentru a estima atât rentabilitate medie și factorului de risc de produse noi. La GM, aceste informații este utilizat de CEO pentru a afla ce produse ajuns promovarea.

  • GM utilizează simulare pentru activități, cum ar fi prognoza profitul pentru corporation, estimarea costurilor structurale și achiziție și determinarea sensibilitatea sa la diferite tipuri de risc (cum ar fi rata dobânzii modificările și ale cursului exchange).

  • Lilly utilizează simulare pentru a determina capacitatea unei plante optimă pentru fiecare droguri.

  • Procter and Gamble utilizează simulare să modelați și optim asigura schimb riscul.

  • Sears utilizează simulare pentru a determina cât de multe unități de fiecare linie de produs trebuie ordonate la furnizori, de exemplu, numărul de perechi de pantaloni Dockers care ar trebui să fie ordonate anul acesta.

  • Ulei și droguri companii utilizează simulare valoarea "Opțiuni reale,", cum ar fi valoarea o opțiune pentru a extinde, a unui contract sau amânarea unui proiect.

  • Financiare planificatoare utilizarea simularea Monte Carlo pentru a determina optimă investiție strategii pentru clienții lor pensionare.

Atunci când tastați formulă =RAND() într-o celulă, primiți un număr care este la fel de probabil să preia orice valoare între 0 și 1. Astfel, aproximativ 25 % din cazuri, trebuie să obțineți un număr mai mare sau egal cu 0,25; în jurul 10 % din cazuri care ar trebui să obțineți un număr care este cel puțin 0.90 și etc. Pentru a demonstra cum funcționează funcția RAND, vedeți fișierul Randdemo.xlsx, afișat în imagine 60-1.

Book Image
Figura 60-1 să demonstreze funcția RAND

Notă: Atunci când deschideți fișierul Randdemo.xlsx, nu vor vedea numere aleatoare același indicat în Figura 60-1. Funcția RAND recalculează întotdeauna automat numerele pe care le generează atunci când o foaie de lucru este deschis sau atunci când informații noi este introdusă în foaia de lucru.

Mai întâi, copiați din celula C3 C4:C402 formulă =RAND(). Apoi denumiți zona C3:C402 date. Apoi, în coloana F, puteți urmări media 400 numere aleatoare (celule F2) și utilizați funcția COUNTIF pentru a determina fracții aflate î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, numere aleatoare sunt recalculate. Notificare că media aritmetică a numerelor 400 este întotdeauna aproximativ 0,5, că aproximativ 25 % din rezultatele sunt în intervale de 0,25. Aceste rezultate sunt compatibile cu definiția un număr aleator. De asemenea, rețineți că valorile generate de RAND din diferite celule sunt independente. De exemplu, dacă număr aleator generat în celula C3 este un număr mare (de exemplu, 0.99), ne spune nimic despre valorile din alte numere aleatoare generate.

Să presupunem că cerere pentru un calendar este incidența variabilă aleatoare discrete următoarele:

Cerere

Probabilitate

10.000

0,10

20.000

0,35

40,000

0,3

60.000

0,25

Cum pot avem Excel redarea sau simula, această cerere pentru calendare de câte ori? Secretul este să se asocieze fiecărei valori posibile ale funcției RAND cu o cerere posibile pentru calendare. Următoarele atribuire asigură faptul că o cerere de 10.000 va apărea 10 % din cazuri, și așa mai departe.

Cerere

Un număr aleator atribuite

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ă de 0,75

60.000

Mai mare sau egal cu 0,75

Pentru a demonstra simulare cerere, căutați fișierul Discretesim.xlsx, afișat în imagine 60-2 pe pagina următoare.

Book Image
Figura 60-2 simularea o variabilă aleatoare discrete

Cheia nostru simulare este să utilizați un număr aleator pentru a iniția o căutare din zona de tabel F2:G5 (denumită Căutare). Numere aleatoare mai mare sau egal cu 0 și mai mică decât 0.10 va avea ca rezultat o cerere de 10.000; numere aleatoare mai mare sau egal cu 0.10 și mai mică decât 0.45 va avea ca rezultat o cerere de 20.000; numere aleatoare mai mare sau egal cu 0.45 și mai mică de 0,75 va avea ca rezultat o cerere 40.000; și numere aleatoare mai mare sau egal cu 0,75 va avea ca rezultat o cerere 60.000. Puteți genera 400 numere aleatoare copiind din C3 C4:C402 formulă RAND(). Apoi puteți genera 400 încercări sau iteraţii, a calendarului cerere copiind din B3 B4:B402 formula VLOOKUP(C3,lookup,2). Această formulă asigură că orice mai puțin număr aleator mai 0.10 generează o cerere de 10.000, orice număr aleator între 0.10 și 0.45 generează o cerere de 20.000 etc. În zona de celule F8:F11, utilizați funcția COUNTIF pentru a determina fracțiunea nostru iteraţii 400 din care se poate fiecare cerere. Atunci când am apăsați F9 pentru a recalcula numere aleatoare, probabilităţi simulată sunt aproape nostru probabilităţi presupusă cerere.

Dacă tastați în orice celulă formulă NORMINV(rand(),mu,sigma), va genera o valoare simulată o variabilă aleatoare normală, având o medie um și abaterea standard sigma. Această procedură este ilustrat în fișierul Normalsim.xlsx, în Figura 60-3.

Book Image
Figura 60-3 simularea o variabilă aleatoare normal

Să presupunem că dorim să simulați 400 încercări sau iteraţii, pentru o variabilă aleatoare normale cu o medie a 40.000 și o deviație standard de 10.000. (Puteți tastați aceste valori în celulele E1 și E2 și denumiți aceste celule media și sigma, respectiv.) Copiați formule =RAND() C4 la C5:C403 generează 400 numere aleatoare diferite. Copiați B4 la B5:B403 formula NORMINV(C4,mean,sigma) generează 400 diferite încercare valorile dintr-o variabilă aleatoare normale cu o medie a 40.000 și o deviație standard de 10.000. Atunci când am apăsați tasta F9 pentru a recalcula numere aleatoare, media rămâne aproape 40.000 și abaterea standard a aproape de 10.000.

În esență, pentru un număr aleator x, formulă NORMINV(p,mu,sigma) generează pa percentilă a o variabilă aleatoare normale cu o medie um și o deviație standard sigma. De exemplu, număr aleator 0,77 în celula C4 (Consultați Figura 60-3) generează în celula B4 aproximativ 77 percentila o variabilă aleatoare normale cu o medie a 40.000 și o deviație standard de 10.000.

În această secțiune, veți vedea cum simulare Monte Carlo poate fi utilizată ca instrument decizional. Să presupunem că cerere pentru o carte de ziua de Sfântul este incidența variabilă aleatoare discrete următoarele:

Cerere

Probabilitate

10.000

0,10

20.000

0,35

40,000

0,3

60.000

0,25

Felicitare vinde pentru $4.00, iar costul variabilă produc fiecare fișă este $1.50. Cărți rămase trebuie să fie eliminate la un cost de $0.20 pe fișă. Cât de multe fișe ar trebui să fie imprimat?

Practic, vom simula fiecare cantitatea de producție posibile (10.000, 20.000, 40,000 sau 60.000) multe ori (de exemplu, 1000 iteraţii). Apoi vom determinați ce cantitate de comandă reprezintă profit medie maxim peste 1000 de iteraţii. Puteți găsi datele pentru această secțiune în fișierul Valentine.xlsx, în Figura 60-4. Atribuiți numele zonă de celule B1:B11 C1:C11 celule. Zona de celule G3:H6 este atribuită de nume de Căutare. Prețul de vânzare și costul parametrii noastre sunt introduse în celule C4:C6.

Book Image
Figura 60-4 ziua de Sfântul Cartea simulare

Puteți să introduceți o încercare de producție cantitate (40.000 în acest exemplu) în celula C1. În continuare, creați un număr aleatoriu din celula C2 cu formulă =RAND(). După cum este descris anterior, puteți simulați cerere pentru cartea de vizită din celula C3 cu formulă VLOOKUP(rand,lookup,2). (În formule VLOOKUP, rand este numele celulei atribuite celula C3, nu funcția RAND).

Numărul de unități vândute este cea mai mică de producție cantitate și cerere noastre. În celula C8, puteți calcula veniturile noastre cu formula MIN (produs, cerere) * unit_price. În celula C9, puteți calcula costul total de producție cu formula produse * unit_prod_cost.

Dacă am produce mai multe fișe decât sunt în cerere, numărul de unități rămase de producție este egal cu minus cerere; în caz contrar, fără unități sunt stânga peste. Se calculează nostru uzate cost în celula C10 cu formula unit_disp_cost * IF (produse > cerere, produse-cerere, 0). În cele din urmă, în celula C11, se calculează profitul nostru ca venituri-total_var_cost-total_disposing_cost.

Am dori o modalitate eficientă a apăsați F9 multe ori (de exemplu, 1000) pentru fiecare cantitate de producție și corespund profitul nostru așteptate pentru fiecare cantitate. Această situație este una în care un tabel de date bidirecțională vine să ne salveze. (Consultați capitolul 15, "Sensibilitate analiza cu tabelele de date," pentru detalii despre tabelele de date). Tabel de date utilizate în acest exemplu este afișat în imagine 60-5.

Book Image
Figura 60-5 tabel de date cu două sensuri pentru felicitare simulare

În zona de celule A16:A1015, introduceți numerele 1-1000 (corespunzător noastre încercări 1000). O modalitate simplă de a crea aceste valori este să începeți prin introducerea 1 în celula A16. Selectați celula, apoi pe pornire fila în grupul Editare , faceți clic pe umplereși selectați serie pentru a afișa caseta de dialog serie . În caseta de dialog serie , în Figura 60-6, introduceți valoarea Pasul 1 și o valoare oprire de 1000. În zona Serie în , selectați opțiunea de coloane și apoi faceți clic pe OK. Numere 1 – 1000 va fi introduse în coloana o începând de la celula A16.

Book Image
Figura 60-6 utilizând seria caseta pentru a completa numerele de încercare 1 la 1000 de dialog

În continuare, vom intra noastre cantitățile de producție posibile (10.000 de 20.000, 40.000, 60.000) în celule B15:E15. Dorim să calculeze profit pentru fiecare număr de încercare (1 la 1000) și fiecare cantitate de producție. Ne referim la formula pentru profit (calculate în celula C11) în celula din stânga sus al nostru tabel de date (15) prin introducerea = C11.

Am acum sunt gata să secretul Excel în simularea repetări 1000 de cerere pentru fiecare cantitate de producție. Selectați zona de tabel (A15:E1014), apoi, în grupul Instrumente date, pe fila date, faceți clic pe ce dacă analiza și apoi selectați tabel de date. Pentru a configura un tabel de date bidirecțională, alegeți noastre cantitatea de producție (celula C1) ca celula de intrare rând și selectați orice celulă necompletată (am ales celulă I14) ca celula de intrare coloană. După ce faceți clic pe OK, Excel simulează 1000 cerere valorile pentru fiecare cantitate de comandă.

Pentru a înțelege ce funcționează, luați în considerare valorile amplasate de tabel 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 din celula de intrare coloana 1 este plasat într-o celulă necompletată și număr aleatoriu din celula C2 este recalculată. Profitul corespunzătoare apoi este înregistrat în celula C16. Apoi coloana celula valori de intrare de 2 este plasat într-o celulă necompletată și număr aleatoriu din C2 recalculează din nou. Profitul corespunzătoare este introdus în celulă C17.

Copiind din celula B13 C13:E13 formula AVERAGE(B16:B1015), se calculează profit mediu simulată pentru fiecare cantitate de producție. Copiind din celula B14 C14:E14 formula STDEV(B16:B1015), se calculează abaterea standard a profiturile noastre simulată pentru fiecare cantitate de comandă. De fiecare dată când vă apăsați F9, repetări 1000 de cerere sunt simulată pentru fiecare cantitate de comandă. Produc 40 000 cărți întotdeauna reprezintă cea mai mare marjă așteptat. Prin urmare, se pare că produc 40 000 cărți este decizie potrivite.

Impactul riscului pe nostru decizie     Dacă am produs 20.000 în loc de cărți de 40 000, profitul nostru așteptate picături aproximativ 22 la sută, dar riscul nostru (măsurat de abaterea standard a profitului) scade aproape 73 procent. Prin urmare, dacă suntem extrem o problemă la risc, producerea de cărți de 20.000 poate fi dreptul de decizie. Altfel, produc 10.000 cărți întotdeauna are o abatere standard de cărți de 0, deoarece dacă vom produce 10.000 cărți, vom vinde întotdeauna toate acestea fără orice leftovers.

Notă: În acest registru de lucru, opțiunea de calcul este setată la Automat cu excepția tabelelor. (Utilizați comanda de calcul în grupul calcul, pe fila formule.) Această setare asigură că nostru tabel de date nu va recalcularea dacă am apăsați F9, care este o idee bună, deoarece un tabel de date mari va încetini lucru dacă se recalculează de fiecare dată când tastați un termen în foaia de lucru. Rețineți că în acest exemplu, ori de câte ori vă apăsați F9, media profitului va modifica. Acest lucru se întâmplă deoarece de fiecare dată când apăsați F9, o secvență diferite de 1000 de numere aleatoare este utilizat pentru a genera cererile pentru fiecare cantitate de comandă.

Intervalul de încredere pentru media profitului     O întrebare natural pentru a cere în această situație este, în intervalul care sunt noi 95 %-vă că adevărat media profitului să se încadreze? Acest interval se numește 95 la sută intervalul de încredere pentru media profitului. Un interval de încredere de 95 % pentru media orice ieșire simulare este calculată după următoarea formulă:

Book Image

În celula J11, calcula limita inferioară pentru intervalul de încredere de 95 % pe media profitului atunci când 40 000 calendare sunt produse cu formulă D13–1.96*D14/SQRT(1000). În celula J12, puteți calcula limita superioară pentru noastre intervalul de încredere de 95 % cu formula D13+1.96*D14/SQRT(1000). Aceste calcule sunt afișate în Figura 60-7.

Book Image
Figura 60-7 95 la sută intervalul de încredere pentru media profitului atunci când sunt ordonate 40 000 calendare

Am sunt 95 %-vă că este profitul nostru media atunci când sunt ordonate 40 000 calendarelor între $56,687 și $62,589.

  1. Un distribuitor GMC consideră că cerere pentru 2005 trimişii va fi normal distribuit cu o medie de 200 și abaterea standard a 30. Costul său de a primi un reprezentant al este $25.000 și a vinde un reprezentant al pentru $40.000. Jumătate din toate trimişii nu vândute la un preț completă pot fi vândute pentru 30.000 dolari. El în vedere ordonarea 200, 220, 240, 260, 280 sau 300 trimişii. Cât de multe ar trebui să el comanda?

  2. Un mic supermarket este încercarea de a determina cât de multe copii ale persoanelor revista când ar trebui să comanda fiecare săptămână. Când considerați că activarea lor cerere pentru persoane este sub incidența variabilă aleatoare discrete următoarele:

    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 a persoane și vinde pentru $1.95. Fiecare copie nevândute pot fi returnate pentru $0.50. Numărul de copii de persoane care ar trebui să magazinul ordine?

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.

×