Che cos’è la simulazione Monte Carlo?

La simulazione Monte Carlo è un processo di utilizzo delle curve di probabilità per determinare la probabilità di un risultato. Si può grattarsi la testa qui e dire… “Hey Rick, una curva di distribuzione ha una matrice di valori. Quindi, come posso determinare esattamente la probabilità di un risultato?”E meglio ancora, come faccio a farlo in Microsoft Excel senza alcun componente aggiuntivo specialepensò che non avresti mai chiesto.Questo viene fatto eseguendo la simulazione migliaia di volte e analizzando la distribuzione dell’output. Ciò è particolarmente importante quando si analizza l’output di diverse curve di distribuzione che si alimentano l’una nell’altra.Esempio:

• # di unità vendute può avere una curva di distribuzione
• moltiplicata per il prezzo di mercato, che può avere un’altra curva di distribuzione
• meno i salari variabili che hanno un’altra curva
• ecc., ecc.

Una volta che tutte queste distribuzioni sono mescolate, l’output può essere piuttosto complesso. L’esecuzione di migliaia di iterazioni (o simulazioni) di queste curve può fornire alcune informazioni. Ciò è particolarmente utile per analizzare il rischio potenziale di una decisione.

Descrivi Monte Carlo

Quando descrivo la simulazione di Monte Carlo, mi riferisco spesso ai giochi di guerra del film 1980, dove un giovane Mathew Broderick (prima di Ferris Bueller) è un hacker che usa il suo modem dial up per hackerare i computer del Pentagono e iniziare la Guerra mondiale 3. Piu ‘ o meno. Ha poi avuto i computer del Pentagono fare molte simulazioni dei giochi Tic Tac Toe per insegnare al computer che nessuno sarà una guerra nucleare-e salvare il mondo nel processo.Grazie Ferris. Sei un eroe.Ecco un assaggio del film per mostrarti il grande Monte Carlo in azione. Presumo che trascurerai la politica, l’uomo imbarazzante che abbraccia e, naturalmente, Dabney Coleman.

Curve di distribuzione

Esistono varie curve di distribuzione che è possibile utilizzare per impostare la simulazione Monte Carlo. E queste curve possono essere scambiate in base alla variabile. Microsoft non ha una formula chiamata “Do Monte Carlo Simulation” nella barra dei menu 🙂

Distribuzione uniforme

In una distribuzione uniforme, c’è uguale probabilità ovunque tra il minimo e il massimo. Una distribuzione uniforme si presenta come un rettangolo.

Distribuzione normale (gaussiana)

Questa è anche la curva a forma di campana standard. Questa formula di simulazione Monte Carlo è caratterizzata da una distribuzione uniforme su ciascun lato (mediana e media sono uguali – e nessuna asimmetria). Le code della curva vanno all’infinito. Quindi questa potrebbe non essere la curva ideale per i prezzi delle case, dove alcune case di fascia alta aumentano la media (media) ben al di sopra della mediana, o nei casi in cui vi è un minimo o un massimo difficile. Un esempio di questo può essere il salario minimo nel vostro locale. Si prega di notare che il nome della funzione varia a seconda della versione.

Lognormal Distribuzione

Una distribuzione in cui il logaritmo è normalmente distribuito con la media e la deviazione standard. Quindi l’installazione è simile alla distribuzione normale, ma si noti che le variabili mean e standard_dev sono destinate a rappresentare il logaritmo.

Distribuzione di Poisson

Questa è probabilmente la distribuzione più sottoutilizzata. Per impostazione predefinita, molte persone utilizzano una curva di distribuzione normale quando Poisson è una misura migliore per i loro modelli. Poisson è meglio descritto quando c’è una grande distribuzione vicino all’inizio che si dissipa rapidamente in una lunga coda su un lato. Un esempio di ciò potrebbe essere un call center, in cui non si risponde alle chiamate prima del secondo ZERO. Seguito dalla maggior parte delle chiamate risposto nei primi 2 intervalli (diciamo 30 e 60 secondi) con un rapido calo di volume e una lunga coda, con pochissime chiamate risposto in 20 minuti (presumibilmente).

Lo scopo qui non è quello di mostrare ogni distribuzione possibile in Excel, in quanto ciò non rientra nell’ambito di questo articolo. Piuttosto per assicurarti di sapere che ci sono molte opzioni disponibili per la tua simulazione Monte Carlo. Non cadere nella trappola di presumere che una curva di distribuzione normale sia la giusta misura per tutta la modellazione dei dati. Per trovare più curve, per andare le funzioni statistiche all’interno della cartella di lavoro di Excel e indagare. Se avete domande, li pongono nella sezione commenti qui sotto.

Costruire il modello

Per questo set up assumeremo una distribuzione normale e 1.000 iterazioni.

Variabili di input

L’installazione presuppone una distribuzione normale. Una distribuzione normale richiede tre variabili; probabilità, media e deviazione standard. Affronteremo la media e la deviazione standard nel nostro primo passo. Presumo un problema di previsione finanziaria che consiste in entrate, spese variabili e fisse. Dove le entrate meno le spese variabili meno le spese fisse è uguale a profitto. Le spese fisse sono costo affondato in impianti e attrezzature, quindi non si assume alcuna curva di distribuzione. Le curve di distribuzione sono considerate per le entrate e le spese variabili.

Prima simulazione

L’esempio seguente indica le impostazioni per le entrate. La formula può essere copiata e incollata nella cella D6 per le spese variabili. Per entrate e spese noi la NORMA di funzione.INV () dove i parametri sono:

• Probabilità = la funzione RAND () per ottenere un numero casuale in base agli altri criteri all’interno della distribuzione.
• Media = La media utilizzata nella Fase 1. Per le entrate è C3.
• Deviazione standard = La deviazione standard utilizzata nel passaggio 1. Per le entrate è C4

Poiché RAND () viene utilizzato come probabilità, viene generata una probabilità casuale all’aggiornamento. Useremo questo a nostro vantaggio nella fase successiva.

1.000 Simulazioni

Esistono diversi modi per eseguire 1.000 o più variazioni. L’opzione più semplice è prendere la formula dal passaggio n.2 e renderla assoluta. Quindi copiare e incollare 1.000 volte. È semplice, ma non molto elegante. E se Ferris Bueller può salvare il mondo mostrando un nuovo gioco Tic Tac Toe a un computer, allora possiamo ravvivare anche questa analisi. Avventuriamoci nel mondo dei tavoli.

• Per prima cosa vogliamo creare uno schema per una tabella. Lo facciamo elencando i numeri da 1 a 1.000 in righe. Nell’immagine di esempio qui sotto, l’elenco dei numeri inizia in B12.
• nella colonna successiva, nella cella C12, faremo riferimento alla prima iterazione.

• Quindi evidenzia l’area in cui vogliamo ospitare le 1.000 iterazioni
• Seleziona Dati > Tabelle di dati
• Per la cella di input della colonna: seleziona una cella vuota. Nel file di download, la cella D11 è selezionata
• Seleziona OK

• Una volta selezionato OK dal passaggio precedente, viene inserita una tabella che autopopola le 1.000 simulazioni

Statistiche di riepilogo

Una volta eseguite le simulazioni, è il momento di raccogliere statistiche di riepilogo. Questo può essere fatto in diversi modi. In questo esempio ho usato la funzione COUNTIF () per determinare la percentuale di simulazioni non redditizie e la probabilità di un profitto superiore a $1 milioni. Come previsto, la probabilità di maggiore di $1M si aggira intorno al 50%. Questo perché abbiamo usato curve di distribuzione normali che sono uniformemente distribuite intorno alla media, che era $1M. La probabilità di perdere denaro è del 4,8%. Questo è stato raccolto utilizzando la funzione COUNTIF () per contare le simulazioni che erano inferiori a zero e dividendo per le 1.000 iterazioni totali.

Scarica il Download

Nel video qui sopra, Oz chiede i vari usi per la simulazione Monte Carlo. Per cosa l’hai usato? Ci sono esempi specifici che puoi condividere con il gruppo? Se è così, lasciare una nota qui sotto nella sezione commenti. Inoltre, sentiti libero di iscriverti alla nostra newsletter, in modo da poter rimanere aggiornato come nuovo Excel.TV spettacoli sono annunciati. Mi lascia un messaggio qui sotto per rimanere in contatto.