Simulazione Monte Carlo in Excel: guida completa

Perché usare Excel per la simulazione Monte Carlo?

Una volta scelto di implementare una simulazione Monte Carlo, hai a disposizione diversi strumenti, come Excel, Python, R, SAS e MATLAB, per aiutarti nelle simulazioni.

Il fattore più importante da considerare, soprattutto quando implementi per la prima volta una simulazione Monte Carlo, è la tua familiarità complessiva con lo strumento. Excel è uno degli strumenti più utilizzati nel mondo del lavoro, il che significa che molte persone conoscono già le sue operazioni di base. Questo riduce i tempi di formazione ed elimina la necessità di imparare un nuovo software da zero.

Excel offre anche strumenti semplici da usare per creare grafici e diagrammi, utili per visualizzare i risultati delle simulazioni. Inoltre, sono disponibili diversi componenti aggiuntivi potenti per Excel, che ne potenziano la capacità di eseguire simulazioni Monte Carlo complesse.

Tuttavia, vale anche la pena notare che per simulazioni più avanzate, in particolare quelle che richiedono la gestione di grandi dataset o l'esecuzione di un numero molto elevato di simulazioni, strumenti più specializzati diversi da Excel potrebbero essere più appropriati.

Funzioni chiave di Excel per Monte Carlo

Successivamente, esploreremo due funzioni essenziali di Excel: RAND() e NORM.INV(), coprendone sintassi, parametri e casi d'uso tipici. Queste funzioni aiutano a generare numeri casuali e a definire distribuzioni di probabilità, aspetti fondamentali di ogni simulazione.

La funzione RAND()

RAND() genera un numero casuale maggiore o uguale a 0 e minore di 1. I numeri sono distribuiti uniformemente, il che significa che qualsiasi numero all'interno dell'intervallo specificato ha uguale probabilità di verificarsi.

La sintassi di RAND() è la seguente:

RAND()

La funzione RAND() non richiede argomenti. Si usa semplicemente come RAND().

Nel contesto della simulazione Monte Carlo, RAND() può essere utilizzata per simulare il verificarsi di eventi casuali o per variare gli input nel tuo modello.

La funzione NORM.INV()

Mentre RAND() genera numeri casuali uniformi, NORM.INV() viene utilizzata per generare numeri casuali da una distribuzione normale, requisito comune in una simulazione Monte Carlo. Questa funzione restituisce l'inverso della distribuzione cumulativa normale per una media e una deviazione standard specificate.

La sintassi della funzione NORM.INV() è la seguente:

NORM.INV(probability, mean, standard_deviation)

I parametri sono:

• probability: una probabilità corrispondente alla distribuzione normale, che deve essere un valore compreso tra 0 e 1. In genere è generata dalla funzione RAND().

• mean: la media aritmetica della distribuzione normale.

• standard_deviation: la deviazione standard della distribuzione normale, una misura di quanto i numeri siano dispersi attorno alla media.

La NORM.INV() viene utilizzata per trasformare i numeri casuali uniformi generati dalla funzione RAND() in numeri che seguono una specifica distribuzione normale. Questo è utile per modellare variabili che ci si aspetta mostrino una variabilità naturale seguendo una curva normale.

Ora che abbiamo tutti i blocchi costruttivi, le funzioni e i concetti alla base di una simulazione Monte Carlo, implementiamone una in Microsoft Excel.

Implementare una simulazione Monte Carlo in Microsoft Excel: un esempio

Considera uno scenario in cui sei un data analyst che lavora in una dinamica azienda di elettronica di consumo e ti è stato affidato il compito di valutare la fattibilità finanziaria del lancio di un nuovo fitness tracker indossabile.

Il mercato per questi dispositivi è competitivo e la domanda dei consumatori può essere molto variabile, influenzata da tendenze stagionali, efficacia del marketing e azioni dei concorrenti. Inoltre, i costi associati alla produzione di questi dispositivi sono soggetti a fluttuazioni a causa di cambiamenti nei costi dei materiali e incertezze nella supply chain.

Hai deciso di utilizzare la simulazione Monte Carlo in Excel per affrontare queste sfide. Ritieni che questo approccio ti aiuterà a stimare la potenziale redditività in diversi scenari, consentendo all'azienda di prendere decisioni informate su strategie di prezzo, volumi di produzione e investimenti di marketing.

Hai anche analizzato dati passati di lanci di prodotti simili e studi di mercato nel settore dell'elettronica di consumo. Da questa analisi hai ricavato alcune metriche che informeranno la tua simulazione:

• Una domanda media di 10.000 unità per i nuovi dispositivi entro il primo anno dal lancio, con una deviazione standard di 2.000 unità, a riflettere l'incertezza nell'adozione da parte dei consumatori.
• Il prezzo di vendita unitario varia tipicamente tra 50 $ e 70 $, a seconda dei prezzi dei concorrenti e della saturazione del mercato.
• Il costo unitario, influenzato dalla volatilità dei prezzi dei materiali e dall'efficienza produttiva, è in media di 30 $ per unità con una deviazione standard di 5 $.

Questi dati storici costituiscono le ipotesi di base dei parametri della tua simulazione, aiutando a creare una simulazione che rifletta in modo più accurato le condizioni di mercato attuali.

I passaggi che potresti seguire per implementare la simulazione Monte Carlo per questo esempio specifico sono i seguenti:

Passaggio 1: imposta il tuo foglio Excel

Per prima cosa, prepara il foglio di lavoro Excel includendo colonne per ciascuna variabile e una colonna per l'utile calcolato.

Ecco come apparirebbe inizialmente:

Impostazione del foglio Excel.

Passaggio 2: inserisci le formule per le variabili

In ogni riga, inserirai formule per generare valori casuali per domanda, prezzo di vendita e costo in base alle distribuzioni che hai identificato:

• Domanda: distribuzione normale (media = 10.000 unità, deviazione standard = 2.000 unità)
• Prezzo di vendita: distribuzione uniforme (da 50 $ a 70 $)
• Costo: distribuzione normale (media = 30 $, deviazione standard = 5 $)

Per inserire queste formule una alla volta, seleziona la cella A2 e digita quanto segue:

=NORM.INV(RAND(), 10000, 2000)

L'equazione sopra crea una distribuzione normale con una data media e deviazione standard come segue:

Creazione della distribuzione per la domanda.

Successivamente, seleziona la cella B2 e digita quanto segue:

=50 + (70-50) * RAND()

L'equazione sopra crea una distribuzione uniforme tra 50 $ e 70 $ per il prezzo di vendita come segue:

Creazione della distribuzione per il prezzo di vendita.

Seleziona la cella C2 e digita quanto segue:

=NORM.INV(RAND(), 30, 5)

L'equazione sopra, simile a quella della domanda, crea una distribuzione normale con una data media e deviazione standard come segue:

Creazione della distribuzione per il costo.

Passaggio 3: calcola la variabile dipendente

Ora, calcola l'utile, che è la variabile dipendente, per ciascuna simulazione usando la formula nella colonna D:

=(B2 - C2) * A2

Calcolo dell'utile.

Passaggio 4: compila verso il basso per simulare più scenari

Finora abbiamo creato una singola simulazione. Estendiamola a più simulazioni, diciamo a mille.

Seleziona le celle da A2 a D2 e trascina il quadratino di riempimento (un piccolo quadrato nell'angolo in basso a destra della selezione) verso il basso per copiare le formule su quante righe vuoi simulare (ad esempio, 1000 righe per 1000 simulazioni).

Apparirà qualcosa di simile:

Creazione delle simulazioni.

Passaggio 5: analizza i risultati

Dopo aver eseguito le simulazioni, puoi analizzare i risultati utilizzando funzioni statistiche come minimo, massimo, media e deviazione standard. Non esitare a consultare rapidamente l'Excel cheat sheet per rinfrescare la memoria sulle funzioni integrate di Excel che useremo a breve.

Per trovare l'utile medio atteso ogni mese, digita quanto segue in una cella, ad esempio G6:

=AVERAGE(D2:D1001)

Per trovare l'utile minimo atteso ogni mese, digita quanto segue in una cella, ad esempio G7:

=MIN(D2:D1001)

Per trovare l'utile massimo atteso ogni mese, digita quanto segue in una cella, ad esempio G8:

=MAX(D2:D1001)

Per trovare la deviazione standard dell'utile, digita quanto segue in una cella, ad esempio G9:

=STDEV.P(D2:D1001)

Una volta eseguito, il foglio Excel dovrebbe apparire così:

Analisi dei risultati della simulazione.

Possiamo interpretare i risultati stimati e le implicazioni per il lancio del prodotto come segue:

• La media degli utili rappresenta l'utile atteso dal lancio del nuovo fitness tracker. Suggerisce che, in media, ogni esecuzione della simulazione prevede che potremmo aspettarci circa 298.278,67 $ di utile. Questo valore è utile come stima centrale della redditività date le ipotesi.
• Un utile minimo di 67.598,78 $ è l'utile più basso osservato in tutte le nostre simulazioni. Indica lo scenario peggiore nelle ipotesi del tuo modello, che è comunque in utile ma significativamente inferiore alla media. Ciò potrebbe essere dovuto a una domanda particolarmente bassa o a condizioni di costo sfavorevoli in quella specifica simulazione.
• Un utile massimo di 641.955,42 $ rappresenta lo scenario migliore, in cui la domanda e il prezzo erano probabilmente ai massimi e i costi ai minimi in tutte le simulazioni. Questo mostra il potenziale upside se le condizioni risultassero molto favorevoli.

Dato l'ampio intervallo tra gli utili minimo e massimo e la consistente deviazione standard, c'è un notevole rischio finanziario associato al lancio del nuovo prodotto.

I decisori dovrebbero valutare se l'azienda è a suo agio con questo livello di incertezza e con il potenziale di utili inferiori alla media.

Inoltre, anche se è opzionale, ti invitiamo a creare visualizzazioni come istogrammi per avere una comprensione visiva dei risultati delle simulazioni.

Tecniche per migliorare le simulazioni Monte Carlo in Excel

Quando riesegui la stessa simulazione di cui sopra, puoi osservare una leggera differenza nei calcoli, come mostrato di seguito:

Risultati della simulazione variabili.

Questo perché i valori della simulazione originale possono cambiare tra le iterazioni, influenzando le stime risultanti. Sebbene la variazione sia piccola, quando il valore stimato cambia, nella mente dei decisori può sorgere un dubbio sull'accuratezza e l'affidabilità della simulazione.

Esploriamo alcune tecniche avanzate che potremmo utilizzare per migliorare l'accuratezza e l'affidabilità delle simulazioni.

Aumentare il numero di simulazioni

Eseguire un numero maggiore di simulazioni aiuta a mediare le fluttuazioni casuali e fornisce una stima più stabile e accurata degli esiti.

Per l'esempio sopra, possiamo aumentare il numero di esecuzioni della simulazione (ad esempio, da 1.000 a 10.000 o più), soprattutto quando si hanno parametri altamente variabili.

Determinare il numero "giusto" di simulazioni dipende da diversi fattori.

Più il modello è complesso (cioè più variabili e più ampia è la gamma delle loro interazioni), più simulazioni sono in genere necessarie per catturare tutti i possibili esiti e garantire che i risultati non siano dovuti al caso.

Se gli input hanno un'elevata variabilità o sono fortemente asimmetrici, saranno necessarie più simulazioni per stimare accuratamente le code (valori estremi) delle distribuzioni degli esiti.

Per analisi più dettagliate, in particolare in finanza o nel risk management, non è raro eseguire da 10.000 a 100.000 simulazioni. Questo intervallo è tipicamente utilizzato per garantire risultati solidi in vari scenari e input. Ovviamente, come accennato in precedenza, per analisi su larga scala Excel non è sempre la scelta migliore, meglio R o Python.

Raffinare le distribuzioni di input

L'accuratezza delle simulazioni dipende in gran parte da quanto bene le distribuzioni di probabilità in input riflettano la vera incertezza e il comportamento delle variabili sottostanti. Nel nostro esempio sopra, abbiamo ipotizzato una distribuzione normale per domanda e costo e una distribuzione uniforme per il prezzo di vendita.

Inoltre, potremmo analizzare dati storici più completi per parametrizzare meglio le distribuzioni. Possiamo comprendere meglio i comportamenti di costo, prezzo e domanda rispetto a fattori esterni basandoci sull'input di esperti di dominio. Possiamo anche considerare l'uso di distribuzioni come lognormale, beta o gamma o creare distribuzioni personalizzate basate su dati empirici.

Condurre un'analisi di sensibilità

Questa analisi viene svolta per capire quali variabili in input hanno l'impatto più significativo sull'output, variando sistematicamente ciascun input mentre si tengono costanti gli altri.

Nel nostro esempio sopra, possiamo tenere costanti due variabili e cambiare la distribuzione di una per comprendere le variazioni nelle stime. Quindi, ripetere lo stesso processo per le altre due variabili una alla volta. Alla fine, questa tecnica aiuta a capire su quale variabile concentrare gli sforzi per migliorare l'accuratezza.

Applicare iterativamente le tecniche di cui sopra e analizzarne i risultati può portare a esiti più accurati e affidabili.

Conclusione

Questo tutorial ti ha introdotto alla simulazione Monte Carlo e ai concetti statistici rilevanti. Dopo aver presentato le funzioni di Excel pertinenti, il tutorial ha fornito una guida passo passo per implementare la simulazione Monte Carlo in Excel usando un esempio reale.

Infine, hai imparato alcune buone pratiche e tecniche avanzate per garantire che i tuoi risultati siano più accurati e affidabili.

Se sei particolarmente interessato a implementare la simulazione Monte Carlo sopra descritta usando altri strumenti come Python o R, queste due risorse ti saranno utili:

• Simulazioni Monte Carlo in R
• Simulazioni Monte Carlo in Python

In alternativa, se preferisci restare sul familiare Microsoft Excel e vuoi affinare le tue competenze con questo strumento ampiamente adottato, dai un'occhiata al nostro percorso Excel Fundamentals.