Questo articolo è stato adattato da Microsoft Excel Data Analysis and Business Modeling di Wayne L. Winston.
Panoramica
- Chi utilizza la simulazione Monte Carlo?
- Cosa succede quando si digita =CASUALE() in una cella?
- Come si possono simulare i valori di una variabile casuale discreta?
- Come si possono simulare i valori di una normale variabile aleatoria?
- Come può un'azienda di biglietti di auguri determinare quanti biglietti produrre?
Vorremmo stimare con precisione le probabilità di eventi incerti. Ad esempio, qual è la probabilità che i flussi di cassa di un nuovo prodotto abbiano un valore attuale netto (VAN) positivo? Qual è il fattore di rischio del nostro portafoglio di investimenti? La simulazione Monte Carlo ci consente di modellare situazioni che presentano incertezza e quindi riprodurle su un computer migliaia di volte.
Nota
Il nome simulazione Monte Carlo deriva dalle simulazioni al computer eseguite negli anni '30 e '40 per stimare la probabilità che la reazione a catena necessaria per l'esplosione di una bomba atomica funzionasse con successo. I fisici coinvolti in questo lavoro erano grandi fan del gioco d'azzardo, quindi diedero alle simulazioni il nome in codice Monte Carlo.
Nei prossimi cinque capitoli vedrai esempi di come puoi utilizzare Excel per eseguire simulazioni Monte Carlo.
Chi utilizza la simulazione Monte Carlo?
Molte aziende utilizzano la simulazione Monte Carlo come parte importante del loro processo decisionale. Ecco alcuni esempi.
- General Motors, Proctor and Gamble, Pfizer, Bristol-Myers Squibb ed Eli Lilly utilizzano la simulazione per stimare sia il rendimento medio che il fattore di rischio dei nuovi prodotti. In GM, queste informazioni vengono utilizzate dal CEO per determinare quali prodotti vengono immessi sul mercato.
- 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 determinazione della sua suscettibilità a diversi tipi di rischio (come le variazioni dei tassi di interesse e le fluttuazioni dei tassi di cambio).
- Lilly utilizza la simulazione per determinare la capacità ottimale dell'impianto per ciascun farmaco.
- Proctor and Gamble utilizza la simulazione per modellare e coprire in modo ottimale il rischio di cambio.
- Sears utilizza la simulazione per determinare quante unità di ciascuna linea di prodotti devono essere ordinate dai fornitori, ad esempio il numero di paia di pantaloni Dockers che dovrebbero essere ordinati quest'anno.
- Le compagnie petrolifere e farmaceutiche usano la simulazione per valutare le "opzioni reali", come il valore di un'opzione per espandere, contrarre o posticipare un progetto.
- I pianificatori finanziari utilizzano la simulazione Monte Carlo per determinare le strategie di investimento ottimali per la pensione dei loro clienti.
Cosa succede quando si digita =CASUALE() in una cella?
Quando si digita la formula =CASUALE() in una cella, si ottiene un numero che con la stessa probabilità assume qualsiasi valore compreso tra 0 e 1. Pertanto, circa il 25% delle volte, dovresti ottenere un numero inferiore o uguale a 0.25; Circa il 10% delle volte si dovrebbe ottenere un numero che sia almeno 0,90 e così via. Per illustrare il funzionamento della funzione CASUALE, esaminare la Randdemo.xlsx di file, illustrata nella Figura 60-1.
Nota
Quando si apre il file Randdemo.xlsx, non verranno visualizzati gli stessi numeri casuali mostrati nella Figura 60-1. La funzione CASUALE ricalcola sempre automaticamente i numeri che genera quando si apre un foglio di lavoro o quando vengono immesse nuove informazioni nel foglio di lavoro.
Copiare prima di tutto dalla cella C3 a C4:C402 la formula =CASUALE(). Assegnare quindi un nome all'intervallo C3:C402 Dati. Quindi, nella colonna F è possibile tenere traccia della media dei 400 numeri casuali (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 noti che la media dei 400 numeri è sempre approssimativamente 0,5 e che circa il 25% dei risultati sono a intervalli di 0,25. Questi risultati sono coerenti con la definizione di numero casuale. Si noti anche che i valori generati da CASUALE in celle diverse sono indipendenti. Ad esempio, se il numero casuale generato nella cella C3 è un numero elevato (ad esempio, 0,99), non ci dice nulla sui valori degli altri numeri casuali generati.
Come si possono simulare i valori di una variabile casuale discreta?
Si supponga che la richiesta di un calendario sia governata dalla seguente variabile casuale discreta:
| Domanda | Probabilità |
|---|---|
| 10.000 | 0,10 |
| 20.000 | 0,35 |
| 40,000 | 0,3 |
| 60.000 | 0,25 |
Come è possibile fare in modo che Excel riproduca o simuli più volte questa richiesta di calendari? Il trucco consiste nell'associare ogni possibile valore della funzione CASUALE 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.
| 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 dimostrare la simulazione della domanda, esaminare la Discretesim.xlsx del file, illustrata nella Figura 60-2 nella pagina successiva.
La chiave per la nostra simulazione è usare un numero casuale per avviare una ricerca dall'intervallo di tabelle F2:G5 (chiamato ricerca). Numeri casuali maggiori o uguali a 0 e minori di 0,10 produrranno una domanda di 10.000; numeri casuali maggiori o uguali a 0,10 e minori di 0,45 produrranno una domanda di 20.000; numeri casuali maggiori o uguali a 0,45 e minori di 0,75 produrranno una domanda di 40.000; e numeri casuali maggiori o uguali a 0,75 produrranno una domanda di 60.000. Si generano 400 numeri casuali copiando da C3 a C4:C402 la formula CASUALE(). Vengono quindi generate 400 prove, o iterazioni, della richiesta di calendario copiando da B3 a B4:B402 la formula CERCA.VERT(C3;ricerca;2). Questa formula garantisce che qualsiasi numero casuale minore di 0,10 generi una domanda di 10.000, qualsiasi numero casuale compreso tra 0,10 e 0,45 generi una domanda di 20.000 e così via. Nell'intervallo di celle F8:F11, usare la funzione CONTA.SE per determinare la frazione delle 400 iterazioni che producono ogni richiesta. Quando premiamo F9 per ricalcolare i numeri casuali, le probabilità simulate sono vicine alle nostre probabilità di domanda ipotizzate.
Come si possono simulare i valori di una normale variabile aleatoria?
Se si digita in una cella qualsiasi la formula INV(NORMINV(),mu,sigma), si genererà un valore simulato di una variabile casuale normale avente una media mu e una deviazione standard sigma. Questa procedura è illustrata nella Normalsim.xlsx del file, mostrata nella Figura 60-3.
Supponiamo di voler simulare 400 prove, o 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 denominare queste celle rispettivamente mean e sigma. Copiando la formula =CASUALE() da C4 a C5:C403 vengono generati 400 numeri casuali diversi. Copiando da B4 a B5:B403 la formula INV.NORM(C4,media,sigma) genera 400 diversi valori di prova da una variabile casuale normale 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 vicina a 10.000.
Essenzialmente, per un numero casuale x, la formula INV.NORM(p,mu,sigma) genera il pesimo percentile di una variabile casuale normale con una media mu e una deviazione standard sigma. Ad esempio, il numero casuale 0,77 nella cella C4 (vedere Figura 60-3) genera nella cella B4 circa il 77° percentile di una variabile casuale normale con una media di 40.000 e una deviazione standard di 10.000.
Come può un'azienda di biglietti di auguri determinare quanti biglietti produrre?
In questa sezione, vedrai come la simulazione Monte Carlo può essere utilizzata come strumento decisionale. Supponiamo che la domanda di un biglietto di San Valentino sia governata dalla seguente variabile casuale 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 di produzione di ogni biglietto è di $ 1,50. Le carte rimanenti devono essere smaltite al costo di $ 0,20 per carta. Quante carte devono essere stampate?
Fondamentalmente, simuliamo ogni possibile quantità di produzione (10.000, 20.000, 40.000 o 60.000) molte volte (ad esempio, 1000 iterazioni). Quindi determiniamo quale quantità di ordine produce il profitto medio massimo nelle 1000 iterazioni. I dati per questa sezione si trovano nella Valentine.xlsx file, mostrata nella Figura 60-4. I nomi degli intervalli nelle celle B1:B11 vengono assegnati alle celle C1:C11. All'intervallo di celle G3:H6 viene assegnato il nome di ricerca. I parametri relativi al prezzo di vendita e al costo vengono immessi nelle celle C4:C6.
Nella cella C1 è possibile immettere una quantità di produzione di prova (40.000 in questo esempio). Quindi, creare un numero casuale nella cella C2 con la formula =CASUALE(). Come descritto in precedenza, è possibile simulare la richiesta della carta nella cella C3 con la formula CERCA.VERT(CASUALE;CERCA;2). Nella formula CERCA.VERT CASUALE è il nome della cella assegnato alla cella C3, non la funzione CASUALE.
Il numero di unità vendute è il minore tra la nostra quantità e domanda di produzione. Nella cella C8 calcolare i ricavi con la formula MIN(prodotto,domanda)*unit_price. Nella cella C9 è possibile calcolare il costo di produzione totale con la formula prodotto*unit_prod_cost.
Se produciamo più carte di quelle richieste, il numero di unità rimaste è uguale alla produzione meno la domanda; altrimenti non rimangono unità. Calcoliamo il nostro costo di smaltimento nella cella C10 con la formula unit_disp_cost*SE(domanda prodotta,prodotta>–domanda,0). Infine, nella cella C11 viene calcolato il profitto calcolato come ricavo - total_var_cost-total_disposing_cost.
Vorremmo un modo efficiente per premere F9 molte volte (ad esempio, 1000) per ogni quantità di produzione e conteggiare il nostro profitto atteso per ogni quantità. Questa è una situazione in cui viene in nostro soccorso una tabella dati bidirezionale. (Vedi Capitolo 15, "Analisi di sensibilità con le tabelle dati", per i dettagli sulle tabelle dati.) La tabella dati utilizzata in questo esempio è illustrata nella Figura 60-5.
Nell'intervallo di celle A16:A1015, immettere i numeri da 1 a 1000 (corrispondenti alle nostre 1000 prove). Un modo semplice per creare questi valori consiste nell'immettere 1 nella cella A16. Selezionare la cella, quindi nel gruppo Modifica della scheda Home fare clic su Riempimento e selezionare Serie per visualizzare la finestra di dialogo Serie. Nella finestra di dialogo Serie , mostrata nella Figura 60-6, immettere un valore di passo pari a 1 e un valore di arresto pari a 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.
Immettere quindi le possibili quantità di produzione (10.000, 20.000, 40.000, 60.000) nelle celle B15:E15. Vogliamo calcolare il profitto per ogni numero di prova (da 1 a 1000) e per ogni quantità di produzione. Per fare riferimento alla formula profitto (calcolata nella cella C11) riportata nella cella superiore sinistra della tabella dati (A15) immettere =C11.
Ora siamo pronti per ingannare Excel nella simulazione di 1000 iterazioni della domanda per ogni quantità di produzione. Selezionare l'intervallo di tabella (A15:E1014), quindi nel gruppo Strumenti dati della scheda Dati fare clic su Analisi simulazione e quindi selezionare Tabella dati. Per impostare una tabella dati bidirezionale, scegliere la quantità di produzione (cella C1) come cella di input riga e selezionare qualsiasi cella vuota (abbiamo scelto la cella I14) come cella di input colonna. Dopo aver fatto clic su OK, Excel simula 1000 valori di domanda per ogni quantità dell'ordine.
Per informazioni sul funzionamento di questa impostazione, considerare i valori inseriti dalla tabella dati nell'intervallo di celle C16:C1015. Per ognuna di queste celle, Excel userà il valore 20.000 nella cella C1. In C16 il valore della cella di input della colonna pari a 1 viene inserito 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 colonna 2 viene quindi inserito in una cella vuota e il numero casuale in C2 viene nuovamente ricalcolato. Il profitto corrispondente viene immesso nella cella C17.
Copiando dalla cella B13 a C13:E13 la formula MEDIA(B16:B1015), viene calcolato il profitto medio simulato per ogni quantità di produzione. Copiando dalla cella B14 a C14:E14 la formula DEV.ST(B16:B1015), viene calcolata la deviazione standard dei profitti simulati per ogni quantità di ordine. Ogni volta che premiamo F9, vengono simulate 1000 iterazioni della domanda per ogni quantità di ordine. Produrre 40.000 carte produce sempre il massimo profitto atteso. Pertanto, sembra che produrre 40.000 carte sia la decisione corretta.
L'impatto del rischio sulla nostra decisione Se abbiamo prodotto 20.000 invece di 40.000 carte, il nostro profitto atteso scende di circa il 22%, ma il nostro rischio (misurato dalla deviazione standard del profitto) scende di quasi il 73%. Pertanto, se siamo estremamente contrari al rischio, produrre 20.000 carte potrebbe essere la decisione giusta. Per inciso, produrre 10.000 carte ha sempre una deviazione standard di 0 carte perché se produciamo 10.000 carte, le venderemo sempre tutte senza avanzi.
Nota
In questa cartella di lavoro l'opzione Calcolo è impostata su Automatico tranne che per le tabelle. (Utilizzare il comando Calcolo nel gruppo Calcolo della scheda Formule). Questa impostazione fa in modo che la tabella dati non venga ricalcolata a meno che non si prema F9, il 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 accade perché ogni volta che si preme F9, viene utilizzata una sequenza diversa di 1000 numeri casuali per generare richieste per ogni quantità di ordine.
Intervallo di confidenza per il profitto medio Una domanda naturale da porsi in questa situazione è: in quale intervallo siamo sicuri al 95% che cadrà il vero profitto medio? Questo intervallo è chiamato intervallo di confidenza al 95% per il profitto medio. Un intervallo di confidenza del 95% per la media di qualsiasi output di simulazione viene calcolato con la seguente formula:
Nella cella J11 calcolare il limite inferiore per l'intervallo di confidenza al 95% sul profitto medio quando vengono prodotti 40.000 calendari con la formula D13–1,96*D14/SQRT(1000). Nella cella J12 è possibile calcolare il limite superiore per l'intervallo di confidenza al 95% con la formula D13+1,96*D14/RADQ(1000). Questi calcoli sono mostrati nella Figura 60-7.
Siamo sicuri al 95% che il nostro profitto medio quando vengono ordinati 40.000 calendari è compreso tra $ 56.687 e $ 62.589.
Problemi
Un concessionario GMC ritiene che la domanda di Envoy 2005 sarà normalmente distribuita con una media di 200 e una deviazione standard di 30. Il suo costo per ricevere un inviato è di $ 25.000 e vende un inviato per $ 40.000. La metà di tutti gli inviati non venduti a prezzo pieno può essere venduta per $ 30.000. Sta considerando di ordinare 200, 220, 240, 260, 280 o 300 inviati. Quanti ne avrebbe dovuti ordinare?
Un piccolo supermercato sta cercando di determinare quante copie della rivista People dovrebbero ordinare ogni settimana. Credono che la loro domanda di People sia governata dalla seguente variabile casuale discreta:
Domanda Probabilità 15 0,10 20 0,20 25 0.30 30 0,25 35 0,15 Il supermercato paga $ 1,00 per ogni copia di People e la vende a $ 1,95. Ogni copia invenduta può essere restituita per $ 0,50. Quante copie di People dovrebbe ordinare il negozio?
Servono altre informazioni?
È sempre possibile rivolgersi a un esperto della Tech Community di Excel o ottenere supporto nelle Community.