Creazione di una simulazione Monte Carlo utilizzando Excel

Una simulazione Monte Carlo può essere sviluppata utilizzando Microsoft Excel e un gioco di dadi. La simulazione Monte Carlo è un metodo numerico matematico che utilizza disegni casuali per eseguire calcoli e problemi complessi. Oggi è ampiamente utilizzato e svolge un ruolo chiave in vari campi come finanza, fisica, chimica ed economia.

Simulazione Monte Carlo

Il metodo Monte Carlo è stato inventato da Nicolas Metropolis nel 1947 e cerca di risolvere problemi complessi usando metodi casuali e probabilistici. Il termine "Monte Carlo" deriva dall'area amministrativa di Monaco popolarmente conosciuta come un luogo in cui le élite europee giocano d'azzardo. Usiamo il metodo Monte Carlo quando il problema è troppo complesso e difficile da eseguire mediante il calcolo diretto. Un gran numero di iterazioni consente una simulazione della distribuzione normale.

Il metodo di simulazione Monte Carlo calcola le probabilità di integrali e risolve equazioni differenziali parziali, introducendo così un approccio statistico al rischio in una decisione probabilistica. Sebbene esistano molti strumenti statistici avanzati per creare simulazioni Monte Carlo, è più semplice simulare la legge normale e la legge uniforme utilizzando Microsoft Excel e aggirare le basi matematiche.

Per la simulazione Monte Carlo, isoliamo un numero di variabili chiave che controllano e descrivono il risultato dell'esperimento, quindi assegniamo una distribuzione di probabilità dopo l'esecuzione di un gran numero di campioni casuali. Facciamo una partita a dadi come modello.

Game of Dice

Ecco come ruota il gioco dei dadi:

• Il giocatore lancia tre dadi con 6 facce 3 volte.

• Se il totale dei 3 tiri è 7 o 11, il giocatore vince.

• Se il totale dei 3 tiri è: 3, 4, 5, 16, 17 o 18, il giocatore perde.

• Se il totale è qualsiasi altro risultato, il giocatore gioca ancora e tira di nuovo i dadi.

• Quando il giocatore lancia di nuovo i dadi, il gioco continua allo stesso modo, tranne per il fatto che il giocatore vince quando il totale è uguale alla somma determinata nel primo round.

Si consiglia inoltre di utilizzare una tabella di dati per generare i risultati. Inoltre, sono necessari 5.000 risultati per preparare la simulazione Monte Carlo.

Passaggio 1: Eventi di lancio dei dadi

Innanzitutto, sviluppiamo una serie di dati con i risultati di ciascuno dei 3 dadi per 50 tiri. Per fare ciò, si propone di utilizzare la funzione "RANDBETWEEN (1, 6)". Pertanto, ogni volta che facciamo clic su F9, generiamo una nuova serie di risultati del rollio. La cella "Risultato" è la somma totale dei risultati dei 3 tiri.

Passaggio 2: gamma di risultati

Quindi, dobbiamo sviluppare una serie di dati per identificare i possibili risultati per il primo turno e i successivi. C'è un intervallo di dati a 3 colonne. Nella prima colonna, abbiamo i numeri da 1 a 18. Queste cifre rappresentano i possibili risultati dopo aver tirato i dadi 3 volte: il massimo è 3 * 6 = 18. Noterai che per le celle 1 e 2, i risultati sono N / A poiché è impossibile ottenere 1 o 2 usando 3 dadi. Il minimo è 3.

Nella seconda colonna, sono incluse le possibili conclusioni dopo il primo round. Come indicato nella dichiarazione iniziale, il giocatore vince (Win) o perde (Lose) oppure rigioca (Re-roll), a seconda del risultato (il totale di 3 tiri di dado).

Nella terza colonna, sono registrate le possibili conclusioni per i round successivi. Possiamo ottenere questi risultati usando la funzione "IF". Questo assicura che se il risultato ottenuto è equivalente al risultato ottenuto nel primo round, vinciamo, altrimenti seguiamo le regole iniziali del gioco originale per determinare se ri-tiriamo i dadi.

Passaggio 3: conclusioni

In questo passaggio, identifichiamo il risultato dei tiri da 50 dadi. La prima conclusione può essere ottenuta con una funzione di indice. Questa funzione cerca i possibili risultati del primo round, la conclusione corrispondente al risultato ottenuto. Ad esempio, quando otteniamo 6 giochiamo di nuovo.

Si possono ottenere i risultati di altri tiri di dado, usando una funzione "OR" e una funzione indice annidata in una funzione "IF". Questa funzione dice a Excel, "Se il risultato precedente è vincere o perdere", smettere di lanciare i dadi perché una volta che abbiamo vinto o perso abbiamo finito. Altrimenti, andiamo alla colonna delle seguenti possibili conclusioni e identifichiamo la conclusione del risultato.

Passaggio 4: Numero di tiri di dado

Ora, determiniamo il numero di tiri di dado richiesti prima di perdere o vincere. Per fare ciò, possiamo usare una funzione "COUNTIF", che richiede ad Excel di contare i risultati di "Re-roll" e aggiungere il numero 1 ad esso. Aggiunge uno perché abbiamo un round extra e otteniamo un risultato finale (vincere o perdere).

Passaggio 5: simulazione

Sviluppiamo una gamma per tenere traccia dei risultati di diverse simulazioni. Per fare ciò, creeremo tre colonne. Nella prima colonna, una delle cifre incluse è 5.000. Nella seconda colonna, cercheremo il risultato dopo 50 tiri di dado. Nella terza colonna, il titolo della colonna, cercheremo il numero di lanci di dadi prima di ottenere lo stato finale (vincere o perdere).

Quindi, creeremo una tabella di analisi della sensibilità utilizzando i dati delle caratteristiche o la tabella Dati tabella (questa sensibilità verrà inserita nella seconda tabella e nelle terze colonne). In questa analisi di sensibilità, il numero di eventi da 1 a 5.000 deve essere inserito nella cella A1 del file. In effetti, si potrebbe scegliere qualsiasi cella vuota. L'idea è semplicemente quella di forzare un ricalcolo ogni volta e quindi ottenere nuovi tiri di dado (risultati di nuove simulazioni) senza danneggiare le formule in atto.

Passaggio 6: probabilità

Finalmente possiamo calcolare le probabilità di vincere e perdere. Lo facciamo usando la funzione "COUNTIF". La formula conta il numero di "vincere" e "perdere", quindi si divide per il numero totale di eventi, 5.000, per ottenere la rispettiva proporzione dell'uno e dell'altro. Finalmente vediamo che la probabilità di ottenere un risultato Win è del 73, 2% e quindi di ottenere un risultato Lose è del 26, 8%.