Introduzione alla simulazione Montecarlo in Excel

Nota: Riteniamo importante fornire il contenuto della Guida più recente non appena possibile, nella lingua dell'utente. Questa pagina è stata tradotta automaticamente e potrebbe contenere errori di grammatica o imprecisioni. L'intento è quello di rendere fruibile il contenuto. Nella parte inferiore della pagina è possibile indicare se le informazioni sono risultate utili. Questo è l'articolo in lingua inglese per riferimento.

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

  • Chi usa la simulazione Montecarlo?

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

  • Come è possibile simulare i valori di una variabile casuale distinta?

  • Come è possibile simulare i valori di una variabile casuale normale?

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

Vorremmo stimare accuratamente le probabilità di eventi incerti. Ad esempio, qual è la probabilità che i flussi di cassa di un nuovo prodotto dispongano di un valore corrente netto positivo (van)? Qual è il fattore di rischio del nostro portafoglio investimenti? La simulazione di Monte Carlo ci consente di modellare situazioni che presentano incertezza e quindi di riprodurle su 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 possa funzionare correttamente. I fisici coinvolti in questo lavoro sono stati grandi fan del gioco d'azzardo, quindi hanno dato le simulazioni con il nome in codice Montecarlo.

Nei cinque capitoli successivi, vedrai esempi di come usare Excel per eseguire simulazioni di Monte Carlo.

Molte aziende usano la simulazione Montecarlo 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 dal CEO per determinare quali prodotti vengono commercializzati.

  • GM utilizza la simulazione per attività come la previsione dell'utile netto per la società, la previsione dei costi strutturali e di acquisto e la sua suscettibilità a diversi tipi di rischio (ad esempio le variazioni dei tassi di interesse e le fluttuazioni del tasso di cambio).

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

  • Proctor e Gamble usano la simulazione per modellare e coprire in modo ottimale il rischio di valuta estera.

  • Sears usa la simulazione per determinare il numero di unità di ogni linea di prodotti da ordinare dai fornitori, ad esempio il numero di paia di pantaloni per i Dockers da ordinare quest'anno.

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

  • Le pianificazioni finanziarie usano la simulazione Montecarlo per determinare le strategie di investimento ottimali per il pensionamento dei clienti.

Quando si digita la formula = Rand () in una cella, si ottiene un numero che è ugualmente probabile che assuma qualsiasi valore compreso tra 0 e 1. Quindi, circa il 25% delle volte, dovresti avere un numero minore o uguale a 0,25; circa il 10% del tempo necessario per ottenere un numero che sia 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, gli stessi numeri casuali visualizzati nella figura 60-1 non verranno visualizzati. La funzione RAND Ricalcola sempre automaticamente i numeri che genera quando un foglio di lavoro viene aperto o quando vengono immesse nuove informazioni nel foglio di lavoro.

Prima di tutto, copiare dalla cella C3 alla C4: c402 la formula = Rand (). Quindi, devi assegnare un nome all'intervallo C3: c402 dati. 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 comprese 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 noterà che la media dei numeri di 400 è sempre approssimativamente 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. Nota anche che i valori generati da RAND in celle diverse sono indipendenti. Ad esempio, se il numero casuale generato nella cella C3 è un numero grande (ad esempio, 0,99), non ci indica nulla sui valori degli altri numeri casuali generati.

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

Richiesta

Probabilità

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

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

Richiesta

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 il file Discretesim. xlsx, illustrato nella figura 60-2 nella pagina successiva.

Immagine del manuale

La chiave per la nostra simulazione è usare un numero casuale per avviare una ricerca dall'intervallo di tabella F2: G5 (chiamata ricerca). I numeri casuali maggiore o uguale a 0 e minore di 0,10 restituiranno una richiesta di 10.000; i numeri casuali maggiori o uguali a 0,10 e minori di 0,45 restituiranno una domanda di 20.000; i numeri casuali maggiori o uguali a 0,45 e minori di 0,75 restituiranno una domanda di 40.000; e i numeri casuali maggiori o uguali a 0,75 restituiranno una domanda di 60.000. Per generare numeri casuali di 400, copiare da C3 in C4: c402 la formula Rand (). Vengono quindi generate prove di 400, o iterazioni, della richiesta di calendario copiando da B3 in B4: B402 la formula VLOOKUP (C3; ricerca; 2). Questa formula garantisce che un numero casuale inferiore a 0,10 generi 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 usare la funzione conta.se per determinare la frazione delle iterazioni di 400 che produce ogni richiesta. Quando si preme F9 per ricalcolare i numeri casuali, le probabilità simulate si avvicinano alle probabilità di domanda presunta.

Se digiti in qualsiasi cella la formula inv (Rand (), MU, Sigma), dovrai generare un valore simulato di una variabile casuale normale con 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 di 400 o le iterazioni per una variabile casuale normale con una media di 40.000 e una deviazione standard di 10.000. È possibile digitare questi valori nelle celle E1 ed E2 e assegnare rispettivamente un nome a queste celle e Sigma. Copiando la 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 si preme il tasto F9 per ricalcolare i numeri casuali, la media resta vicina a 40.000 e la deviazione standard si avvicina a 10.000.

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

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

Richiesta

Probabilità

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

La scheda di saluto viene venduta per $4,00 e il costo variabile per la produzione di ogni scheda è $1,50. Le carte rimanenti devono essere eliminate al costo di $0,20 per biglietto da visita. Numero di schede da stampare

In pratica, vengono simulate diverse volte ogni possibile quantità di produzione (10.000, 20.000, 40.000 o 60.000), ad esempio le iterazioni di 1000. Determineremo quindi la quantità di ordine che restituisce il profitto medio massimo rispetto alle iterazioni di 1000. È possibile trovare i dati per questa sezione nel file Valentine. xlsx, 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 prova (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 e non la funzione Rand.

Il numero di unità vendute è inferiore alla quantità e alla domanda di produzione. Nella cella C8 vengono calcolati i ricavi con la formula min (produced, Demand) * unit_price. Nella cella C9 calcola il costo totale di produzione con la formula * 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 ci sono unità rimaste. Calcoliamo i costi di eliminazione nella cella C10 con la formula unit_disp_cost * if (produced>demand, produced – demand; 0). Infine, nella cella C11 calcoliamo i nostri profitti come ricavi – total_var_cost-total_disposing_cost.

Vorremmo un modo efficiente 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 di 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 Immetti i numeri da 1 a 1000 (corrispondenti ai nostri test di 1000). Un modo semplice per creare questi valori è iniziare inserendo 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 per il 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. Si vuole calcolare il profitto per ogni numero di prova (da 1 a 1000) e ogni quantità di produzione. Si fa riferimento alla formula per il profitto (calcolata nella cella C11) nella cella superiore sinistra della tabella dati (A15) immettendo = C11.

Ora siamo pronti per ingannare Excel in simulando 1000 iterazioni della domanda per ogni quantità di produzione. Selezionare l'intervallo di tabelle (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, scegliendo 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 perché funziona, tenere presente che i valori inseriti dalla tabella di 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 di 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. Quindi il valore di input della cella di colonna di 2 viene posizionato in una cella vuota e viene ricalcolato il numero casuale in C2. 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 St (B16: B1015), calcoliamo la deviazione standard dei profitti simulati per ogni quantità di ordine. Ogni volta che si preme F9, vengono simulate le iterazioni di 1000 per ogni quantità di ordine. La produzione di schede 40.000 restituisce sempre il profitto previsto più grande. Di conseguenza, sembra che la decisione corretta sia quella di produrre carte 40.000.

Impatto del rischio sulla nostra decisione     Se abbiamo prodotto 20.000 invece di 40.000 carte, il nostro profitto previsto scende circa il 22%, ma il nostro rischio (misurato dalla deviazione standard del profitto) scende quasi al 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 10.000 ha sempre una deviazione standard di 0 carte perché, se produciamo 10.000 carte, ne venderemo sempre tutte senza alcun avanzo.

Nota:  In questa cartella di lavoro l'opzione di calcolo è impostata su automatico ad eccezione delle tabelle. Usare il comando Calcola 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 dati di grandi dimensioni rallenterà il lavoro se viene ricalcolata ogni volta che si digita qualcosa nel foglio di lavoro. Si noti che in questo esempio ogni volta che si preme F9, il profitto medio cambierà. Questo problema si verifica perché ogni volta che si preme F9 viene usata una sequenza diversa di 1000 numeri casuali per generare richieste per ogni quantità di ordine.

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

Immagine del manuale

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

Immagine del manuale

Siamo 95 per cento che i nostri profitti medi quando vengono ordinati i calendari 40.000 sono compresi tra $56.687 e $62.589.

  1. Un concessionario GMC ritiene che la domanda per gli inviati di 2005 venga normalmente distribuita 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. Sta valutando l'ordinazione di 200, 220, 240, 260, 280 o 300 inviati. Quanti dovrebbero ordinare?

  2. Un piccolo supermercato sta cercando di determinare il numero di copie della rivista che devono ordinare ogni settimana. Credono che la loro richiesta di persone sia regolata dalla seguente variabile aleatoria discreta:

    Richiesta

    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. Per $0,50 è possibile restituire ogni copia non venduta. Quante copie di persone devono essere ordinate nello 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.

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.

×