Introduzione alla simulazione Monte Carlo in Excel

Questo articolo è stato adattato da Microsoft Excel Data Analysis e Business Modeling di Wayne L. Winston.

  • Chi usa la simulazione Monte Carlo?

  • Cosa succede quando si digita = Rand () in una cella?

  • Come si possono simulare i valori di una variabile casuale discreta?

  • Come si possono simulare i valori di una normale variabile casuale?

  • Come può una società di biglietti di auguri determinare il numero di schede da produrre?

Desideriamo valutare accuratamente le probabilità di eventi incerti. Ad esempio, qual è la probabilità che i flussi di cassa di un nuovo prodotto abbiano un valore netto attuale positivo (van)? Qual è il fattore di rischio del nostro portafoglio investimenti? La simulazione di Montecarlo consente di modellare situazioni che presentano incertezza e quindi riprodurle in un computer migliaia di volte.

Nota:  Il nome simulazione Monte Carlo deriva dalle simulazioni computerizzate eseguite negli anni 1930 e 1940 per stimare la probabilità che la reazione a catena necessaria per la detonazione di una bomba atomica funzioni correttamente. I fisici coinvolti in questo lavoro sono stati grandi fan del gioco d'azzardo, quindi hanno dato alle simulazioni il nome in codice Montecarlo.

Nei prossimi cinque capitoli verrà visualizzato un esempio di come usare Excel per eseguire simulazioni di Montecarlo.

Molte aziende usano la simulazione Monte Carlo come parte importante del processo decisionale. Ecco alcuni esempi.

  • General Motors, Proctor and Gamble, Pfizer, Bristol-Myers Squibb e Eli Lilly usano la simulazione per stimare sia il rendimento medio che il fattore di rischio dei nuovi prodotti. In GM queste informazioni vengono usate dall'amministratore delegato per determinare quali prodotti vengono commercializzati.

  • GM usa una simulazione per attività come la previsione del reddito netto per la società, la previsione dei costi strutturali e di acquisto e la determinazione della suscettibilità a diversi tipi di rischio (ad esempio le variazioni dei tassi di interesse e le fluttuazioni del tasso di cambio).

  • Lilly USA una simulazione per determinare la capacità ottimale delle piante per ogni farmaco.

  • Proctor and Gamble usa una simulazione per modellare e proteggere in modo ottimale il rischio di valuta straniera.

  • Sears usa una simulazione per determinare quante unità di ogni linea di prodotti devono essere ordinate dai fornitori, ad esempio il numero di coppie di pantaloni ancorati da ordinare quest'anno.

  • Le compagnie petrolifere e farmaceutiche usano la simulazione per valorizzare "opzioni reali", ad esempio il valore di un'opzione per espandere, contrarre o posticipare un progetto.

  • I pianificatori finanziari usano la simulazione Monte Carlo per determinare strategie di investimento ottimali per la pensione dei clienti.

Quando si digita la formula = Rand () in una cella, si ottiene un numero che è ugualmente probabile che presupponga qualsiasi valore compreso tra 0 e 1. Quindi, circa il 25% del tempo, dovresti ottenere un numero minore o uguale a 0,25; circa il 10% del tempo necessario per ottenere un numero che è almeno 0,90 e così via. Per illustrare il funzionamento della funzione RAND, vedere il file Randdemo.xlsx, illustrato nella figura 60-1.

Immagine del manuale

Nota:  Quando si apre il file Randdemo.xlsx, non verranno visualizzati gli stessi numeri casuali visualizzati nella figura 60-1. La funzione RAND Ricalcola sempre automaticamente i numeri che genera quando viene aperto un foglio di lavoro o quando vengono immesse nuove informazioni nel foglio di lavoro.

Prima di tutto, copia dalla cella C3 alla C4: c402 la formula = Rand (). Si denominano quindi i datidell'intervallo C3: c402. Nella colonna F è quindi possibile tenere traccia della media dei numeri casuali di 400 (cella F2) e usare la funzione conta.se per determinare le frazioni tra 0 e 0,25, 0,25 e 0,50, 0,50 e 0,75 e 0,75 e 1. Quando si preme il tasto F9, i numeri casuali vengono ricalcolati. Si noti che la media dei numeri di 400 è sempre circa 0,5 e che circa il 25% dei risultati è in intervalli di 0,25. Questi risultati sono coerenti con la definizione di un numero casuale. Tieni inoltre presente che i valori generati da RAND in celle diverse sono indipendenti. Ad esempio, se il numero casuale generato nella cella C3 è un numero elevato (ad esempio 0,99), non viene indicato nulla sui valori degli altri numeri casuali generati.

Supponiamo che la richiesta di un calendario sia regolata dalla seguente variabile casuale discreta:

Domanda

Probabilità

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

In che modo è possibile riprodurre o simulare Excel, la richiesta di calendari più volte? Il trucco consiste nell'associare ogni possibile valore della funzione RAND a una possibile richiesta di calendari. L'assegnazione seguente garantisce che la richiesta di 10.000 si verifichi il 10% del tempo e così via.

Domanda

Numero casuale assegnato

10.000

Minore di 0,10

20.000

Maggiore o uguale a 0,10 e minore di 0,45

40.000

Maggiore o uguale a 0,45 e minore di 0,75

60.000

Maggiore o uguale a 0,75

Per illustrare la simulazione della domanda, vedere la Discretesim.xlsx di file, illustrata nella figura 60-2 nella pagina successiva.

Immagine del manuale

La chiave della simulazione consiste nell'usare un numero casuale per avviare una ricerca dall'intervallo di tabella F2: G5 (denominata Lookup). Un numero casuale maggiore o uguale a 0 e minore di 0,10 restituirà una richiesta di 10.000; un numero casuale maggiore o uguale a 0,10 e minore di 0,45 restituirà una richiesta di 20.000; un numero casuale maggiore o uguale a 0,45 e minore di 0,75 restituirà una richiesta di 40.000; e i numeri casuali maggiore o uguale a 0,75 restituiscono una richiesta di 60.000. Si generano numeri casuali di 400 copiando da C3 a C4: c402 la formula Rand (). Si generano quindi prove o iterazioni di 400 per la richiesta di calendario copiando da B3 a B4: B402 la formula VLOOKUP (C3; ricerca; 2). Questa formula garantisce che qualsiasi numero casuale inferiore a 0,10 genera una richiesta di 10.000, qualsiasi numero casuale compreso tra 0,10 e 0,45 genera una richiesta di 20.000 e così via. Nell'intervallo di celle F8: F11, usa la funzione conta.se per determinare la frazione delle nostre iterazioni di 400 ottenendo ogni richiesta. Quando premiamo F9 per ricalcolare i numeri casuali, le probabilità simulate si avvicinano alle probabilità della domanda assunta.

Se si digita in qualsiasi cella la formula inv (Rand (), MU, Sigma), si genererà un valore simulato di una normale variabile casuale avente una media MU e una deviazione standard Sigma. Questa procedura è illustrata nel file Normalsim.xlsx, illustrato nella figura 60-3.

Immagine del manuale

Supponiamo di voler simulare le prove o le iterazioni di 400 per una normale variabile casuale con una media di 40.000 e una deviazione standard di 10.000. Puoi digitare questi valori nelle celle E1 ed E2 e assegnare rispettivamente il nome alla media e alla Sigma. Copia della formula = Rand () da C4 a C5: C403 genera 400 numeri casuali diversi. Copia da B4 a B5: B403 la formula inv (C4, media, Sigma) genera 400 valori di prova diversi da una normale variabile casuale con una media di 40.000 e una deviazione standard di 10.000. Quando premiamo il tasto F9 per ricalcolare i numeri casuali, la media rimane vicina a 40.000 e la deviazione standard vicino a 10.000.

In sostanza, per un numero casuale x, la Formula inv (p, MU, Sigma) genera la p° percentile di una normale variabile casuale con una media MU e una deviazione standard Sigma. Ad esempio, il numero casuale 0,77 nella cella C4 (Vedi figura 60-3) genera nella cella B4 approssimativamente il 77th percentile di una normale variabile casuale con una media di 40.000 e una deviazione standard di 10.000.

In questa sezione vedrai come può essere usata la simulazione Monte Carlo come strumento decisionale. Supponiamo che la richiesta di una cartolina di San Valentino sia regolata dalla seguente variabile aleatoria discreta:

Domanda

Probabilità

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

Il biglietto di auguri viene venduto per $4,00 e il costo variabile della produzione di ogni scheda è $1,50. Le schede rimanenti devono essere eliminate a un costo di $0,20 per biglietto. Numero di schede da stampare

In pratica, simula ogni possibile quantità di produzione (10.000, 20.000, 40.000 o 60.000) molte volte (ad esempio, le iterazioni di 1000). Determiniamo quindi la quantità di ordine che restituisce il profitto medio massimo sulle iterazioni di 1000. I dati per questa sezione sono disponibili nel Valentine.xlsx file, illustrato nella figura 60-4. Si assegnano i nomi degli intervalli nelle celle B1: B11 alle celle C1: C11. All'intervallo di celle G3: H6 viene assegnata la ricercadel nome. Il prezzo di vendita e i parametri di costo vengono immessi nelle celle C4: C6.

Immagine del manuale

È possibile immettere una quantità di produzione di valutazione (40.000 in questo esempio) nella cella C1. Crea quindi un numero casuale nella cella C2 con la formula = Rand (). Come descritto in precedenza, è possibile simulare la richiesta della scheda nella cella C3 con la formula VLOOKUP (Rand, Lookup, 2). Nella formula VLOOKUP, Rand è il nome della cella assegnato alla cella C3, non la funzione Rand.

Il numero di unità vendute è il più piccolo della quantità e della domanda di produzione. Nella cella C8 puoi calcolare i nostri ricavi con la formula min (produced, Demand) * unit_price. Nella cella C9 viene calcolato il costo totale di produzione con la formula prodotta * unit_prod_cost.

Se produciamo più carte rispetto a quelle richieste, il numero di unità rimaste è uguale a produzione meno domanda; in caso contrario, non vengono lasciate più unità. Calcoliamo i costi di smaltimento nella cella C10 con la formula unit_disp_cost * if (prodotto>demand, produced-demand, 0). Infine, nella cella C11 calcoliamo il nostro profitto come ricavi-total_var_cost-total_disposing_cost.

Vorremmo un modo efficace per premere F9 molte volte (ad esempio 1000) per ogni quantità di produzione e calcolare il profitto previsto per ogni quantità. Questa situazione è quella in cui viene salvata una tabella dati a due vie. Vedere il capitolo 15 "analisi della sensitività con le tabelle dati" per informazioni dettagliate sulle tabelle dati. La tabella dati usata in questo esempio è illustrata nella figura 60-5.

Immagine del manuale

Nell'intervallo di celle A16: A1015 immettere i numeri da 1 a 1000 (corrispondenti alle prove di 1000). Un modo semplice per creare questi valori è iniziare immettendo 1 nella cella A16. Selezionare la cella e quindi nel gruppo modifica della scheda Home fare clic su riempimentoe selezionare serie per visualizzare la finestra di dialogo serie . Nella finestra di dialogo serie , illustrata nella figura 60-6, immettere un valore di passaggio 1 e un valore di interruzione di 1000. Nell'area serie in selezionare l'opzione colonne e quindi fare clic su OK. I numeri da 1 a 1000 verranno immessi nella colonna A a partire dalla cella A16.

Immagine del manuale

Immettiamo quindi le nostre possibili quantità di produzione (10.000, 20.000, 40.000, 60.000) nelle celle B15: e15. Vogliamo calcolare il profitto per ogni numero di versione di valutazione (da 1 a 1000) e ogni quantità di produzione. Si fa riferimento alla formula per profitto (calcolata nella cella C11) nella cella superiore sinistra della tabella dati (A15) immettendo = C11.

Ora siamo pronti per indurre Excel a simulare le iterazioni di 1000 della domanda per ogni quantità di produzione. Selezionare l'intervallo di tabella (A15: E1014) e quindi nel gruppo strumenti dati della scheda dati fare clic su cosa fare se analisi e quindi selezionare tabella dati. Per configurare una tabella dati bidirezionale, scegliere la quantità di produzione (cella C1) come cella di input della riga e selezionare una cella vuota (è stata selezionata la cella i14) come cella di input della colonna. Dopo aver fatto clic su OK, Excel simula i valori della richiesta di 1000 per ogni quantità di ordine.

Per capire il motivo per cui funziona, considera i valori inseriti dalla tabella dati nell'intervallo di celle C16: C1015. Per ognuna di queste celle, Excel utilizzerà un valore di 20.000 nella cella C1. In C16 il valore della cella di input della colonna 1 viene posizionato in una cella vuota e il numero casuale nella cella C2 viene ricalcolato. Il profitto corrispondente viene quindi registrato nella cella C16. Il valore di input della cella della colonna 2 viene quindi posizionato in una cella vuota e il numero casuale in C2 viene ricalcolato. Il profitto corrispondente viene immesso nella cella C17.

Copiando dalla cella B13 a C13: E13 la media della formula (B16: B1015), calcoliamo la media dei profitti simulati per ogni quantità di produzione. Copiando dalla cella B14 a C14: E14 la formula StDev (B16: B1015), calcoliamo la deviazione standard dei nostri profitti simulati per ogni quantità di ordine. Ogni volta che si preme F9, le iterazioni di 1000 della domanda vengono simulate per ogni quantità di ordine. La produzione di schede di 40.000 restituisce sempre il profitto previsto più grande. Di conseguenza, sembra che la produzione di schede di 40.000 sia la decisione appropriata.

Impatto del rischio sulla decisione      Se abbiamo prodotto 20.000 invece di 40.000 carte, il profitto previsto scende di circa il 22%, ma il rischio (misurato dalla deviazione standard del profitto) scende quasi il 73%. Pertanto, se siamo estremamente contrari al rischio, la produzione di carte di 20.000 potrebbe essere la decisione giusta. Per inciso, la produzione di schede di 10.000 ha sempre una deviazione standard di 0 carte, perché se produciamo 10.000 carte, venderemo sempre tutti senza avanzi.

Nota:  In questa cartella di lavoro l'opzione di calcolo è impostata su automatico tranne che per le tabelle. Usare il comando calcolo nel gruppo calcolo della scheda formule. Questa impostazione garantisce che la tabella dati non venga ricalcolata se non si preme F9, che è una buona idea perché una tabella di dati di grandi dimensioni rallenterà il lavoro se viene ricalcolata ogni volta che si digita qualcosa nel foglio di calcolo. Tieni presente che in questo esempio ogni volta che premi F9, il profitto medio cambierà. Questo avviene perché ogni volta che si preme F9, viene usata una sequenza diversa di numeri casuali di 1000 per generare richieste per ogni quantità di ordine.

Intervallo di confidenza per profitto medio      Una domanda naturale da porre in questa situazione è, in quale intervallo si 95 per cento che il profitto medio vero cadrà? Questo intervallo si chiama intervallo di confidenza di 95 per cento per profitto medio. Un intervallo di confidenza di 95 per cento per la media di qualsiasi output di simulazione viene calcolato dalla formula seguente:

Immagine del manuale

Nella cella J11 calcolare il limite inferiore per l'intervallo di confidenza di 95 per cento sul profitto medio quando vengono prodotti calendari 40.000 con la formula D13 – 1.96 * D14/sqrt (1000). Nella cella J12 calcolare il limite superiore per l'intervallo di confidenza di 95 per cento con la formula D13 + 1.96 * D14/sqrt (1000). Questi calcoli sono illustrati nella figura 60-7.

Immagine del manuale

Siamo 95 per cento sicuri che il profitto medio quando si ordinano i calendari di 40.000 è compreso tra $56.687 e $62.589.

  1. Un concessionario GMC ritiene che la domanda per gli inviati di 2005 venga distribuita normalmente con una media di 200 e una deviazione standard di 30. Il costo della ricezione di un inviato è $25.000 e vende un inviato per $40.000. La metà di tutti gli inviati non venduti a prezzo intero può essere venduta per $30.000. Egli sta valutando l'ordinazione degli inviati di 200, 220, 240, 260, 280 o 300. Quanti dovrebbero ordinare?

  2. Un piccolo supermercato sta provando a determinare il numero di copie della rivista people che dovrebbero ordinare ogni settimana. Credono che la loro domanda per gli utenti sia regolata dalla seguente variabile aleatoria discreta:

    Domanda

    Probabilità

    15

    0,10

    20

    0,20

    25

    0,30

    30

    0,25

    35

    0,15

  3. Il supermercato paga $1,00 per ogni copia di persone e la vende per $1,95. Ogni copia non venduta può essere restituita per $0,50. Quante copie di persone devono essere ordinate allo Store?

Servono altre informazioni?

È sempre possibile rivolgersi a un esperto nella Tech Community di Excel, ottenere supporto nella community Microsoft o suggerire una nuova caratteristica o un miglioramento in Excel UserVoice.

Serve aiuto?

Amplia le tue competenze su Office
Esplora i corsi di formazione
Ottieni in anticipo le nuove caratteristiche
Partecipa al programma Office Insider

Queste informazioni sono risultate utili?

Grazie per il feedback!

Grazie per il tuo feedback! Potrebbe essere utile metterti in contatto con uno dei nostri operatori del supporto di Office.

×