Introduzione alla simulazione di Monte Carlo in Excel

• Who usa la simulazione Monte Carlo?

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

• Come si simulano i valori di una variabile casuale discreta?

• Come si simulano i valori di una normale variabile casuale?

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

Vorremmo stimare con precisione le probabilità di eventi incerti. Ad esempio, qual è la probabilità che i flussi di cassa di un nuovo prodotto avranno un valore attuale netto positivo (VAN)? Qual è il fattore di rischio del nostro portfolio di investimenti? La simulazione di Monte Carlo ci consente di modellare situazioni che presentano incertezze e di riprodurle in un computer migliaia di volte.

Nei cinque capitoli successivi verranno illustrati alcuni 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 ed 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 usa la simulazione per attività come la previsione dell'utile netto per l'azienda, la previsione dei costi strutturali e di acquisto e la determinazione della sua suscettibilità a diversi tipi di rischio, ad esempio variazioni dei tassi di interesse e fluttuazioni dei tassi di cambio.

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

• Proctor e Gamble usa la simulazione per modellare e coprire in modo ottimale il rischio di cambio.

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

• Le aziende del settore dell'olio e della droga usano la simulazione per il valore di "opzioni reali", ad esempio il valore di un'opzione per espandere, contrare o posticipare un progetto.

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

Quando si digita la formula =CASUALE() in una cella, si ottiene un numero che assumerà ugualmente qualsiasi valore compreso tra 0 e 1. Pertanto, circa il 25% del tempo, si dovrebbe ottenere un numero minore o uguale a 0,25. circa il 10% del tempo in cui si dovrebbe ottenere un numero di almeno 0,90 e così via. Per illustrare il funzionamento della funzione CASUALE, esaminare il Randdemo.xlsx file, illustrato nella figura 60-1.

Prima di tutto, copiare dalla cella C3 a C4:C402 la formula =CASUALE(). Assegnare quindi all'intervallo il nome C3:C402 Data. 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 tra 0 e 0,25, 0,25 e 0,50, 0,50 e 0,75 e 0,75 e 1. Quando si preme F9, i numeri casuali vengono ricalcolati. Si noti che la media dei 400 numeri è sempre di 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. Si noti inoltre 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 indica nulla sui valori degli altri numeri casuali generati.

Si supponga che la domanda di un calendario sia regolata dalla variabile casuale discreta seguente:

Domanda	Probabilità
10.000	0,10
20.000	0.35
40,000	0,3
60.000	0,25

Come è possibile Excel, o simulare, questa richiesta di calendari molte volte? Il trucco è associare ogni possibile valore della funzione CASUALE a una possibile richiesta di calendari. L'assegnazione seguente assicura che una domanda 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 dimostrare la simulazione della domanda, esaminare il Discretesim.xlsx file, illustrato nella figura 60-2 nella pagina successiva.

La chiave della simulazione è usare un numero casuale per avviare una ricerca dall'intervallo di tabella F2:G5 (ricerca denominata). I numeri casuali maggiori o uguali a 0 e minori di 0,10 producono una richiesta di 10.000; i numeri casuali maggiori o uguali a 0,10 e minori di 0,45 producono una richiesta di 20.000; numeri casuali maggiori o uguali a 0,45 e minori di 0,75 producono una domanda di 40.000; e i numeri casuali maggiori o uguali a 0,75 producono una domanda di 60.000. Per generare 400 numeri casuali, copiare da C3 a C4:C402 la formula CASUALE(). Si generano quindi 400 prove, o iterazioni, della domanda del calendario copiando da B3 a B4:B402 la formula CERCA.V(C3;ricerca;2). Questa formula assicura 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 rendono ogni domanda. Quando si preme F9 per ricalcolare i numeri casuali, le probabilità simulate sono vicine alle probabilità di domanda presunte.

Se si digita in una cella la formula NORMINV(rand(),mu;sigma),si genererà un valore simulato di una normale variabile casuale con una media mu e una deviazione standard sigma. Questa procedura è illustrata nel file Normalsim.xlsx, illustrato 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 assegnare a queste celle rispettivamente il nome media e sigma. La copia della formula =CASUALE() da C4 a C5:C403 genera 400 numeri casuali diversi. Copiando da B4 a B5:B403 la formula NORMINV(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 F9 per ricalcolare i numeri casuali, la media rimane vicina a 40.000 e la deviazione standard vicino a 10.000.

Essenzialmente, per un numero casuale x,la formula NORMINV(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 la figura 60-3) genera nella cella B4 circa il 77° percentile di una normale variabile casuale con una media di 40.000 e una deviazione standard di 10.000.

In questa sezione verrà illustrato come usare la simulazione Monte Carlo come strumento decisionale. Si supponga che la domanda di una carta di San Valentino 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

Il biglietto di auguri viene venduto per $ 4,00 e il costo variabile per la produzione di ogni biglietto è di $ 1,50. Le carte rimanenti devono essere smaltite al costo di $ 0,20 per carta. Quante schede devono essere stampate?

In sostanza, viene simulata ogni quantità di produzione possibile (10.000, 20.000, 40.000 o 60.000) molte volte, ad esempio 1000 iterazioni. Quindi si determina quale quantità dell'ordine produce il profitto medio massimo rispetto alle 1000 iterazioni. I dati di questa sezione sono riportati nel Valentine.xlsx file, illustrato nella figura 60-4. Assegnare i nomi degli intervalli nelle celle B1:B11 alle celle C1:C11. All'intervallo di celle G3:H6 viene assegnato il nome lookup. I parametri relativi a prezzo di vendita e costo vengono immessi nelle celle C4:C6.

È possibile immettere una quantità di produzione di valutazione (40.000 in questo esempio) nella cella C1. Creare quindi un numero casuale nella cella C2 con la formula =CASUALE(). Come descritto in precedenza, è possibile simulare la domanda della scheda nella cella C3 con la formula CERCA.V(rand;lookup,2). Nella formula CERCA.V, rand è il nome della cella assegnato alla cella C3, non alla funzione CASUALE.

Il numero di unità vendute è minore della quantità e della domanda di produzione. Nella cella C8 i ricavi vengono calcolati con la formula MIN(prodotto;domanda)*unit_price. Nella cella C9 si calcola il costo totale di produzione con la formula prodotto*unit_prod_cost.

Se produciamo più schede di quelle della domanda, il numero di unità rimasto è uguale alla produzione meno la domanda; in caso contrario, non viene lasciata alcuna unità. Il costo di dismissione viene calcolato nella cella C10 con la formula unit_disp_cost*SE(prodotto>domanda;prodotto-domanda;0). Infine, nella cella C11 il profitto viene calcolato come ricavi, total_var_cost-total_disposing_cost.

Vorremmo un modo efficiente per premere F9 più volte (ad esempio, 1000) per ogni quantità di produzione e in modo da ottenere il profitto previsto per ogni quantità. Questa situazione è quella in cui viene salvata una tabella dati bidirede. Per informazioni dettagliate sulle tabelle dati, vedere il capitolo 15 "Analisi della riservatezza con le tabelle dati". La tabella dati usata in questo esempio è illustrata nella figura 60-5.

Nell'intervallo di celle A16:A1015 immettere i numeri da 1 a 1000 (corrispondenti alle versioni di valutazione 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 pari a 1 e un valore di interruzione 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 quantità di produzione possibili (10.000, 20.000, 40.000 e 60.000) nelle celle B15:E15. Si vuole calcolare il profitto per ogni numero di prova (da 1 a 1000) e per 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 i Excel a simulare 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 Analisi e quindi selezionare Tabella dati. Per impostare una tabella di dati bidiredirei, scegliere la quantità di produzione (cella C1) come cella di input della riga e selezionare una cella vuota (è stata scelta la cella I14) come cella di input della colonna. Dopo aver fatto clic su OK, Excel 1000 valori della domanda per ogni quantità dell'ordine.

Per capire perché funziona, considerare i valori inseriti dalla tabella dati nell'intervallo di celle C16:C1015. Per ognuna di queste celle, Excel il valore 20.000 nella cella C1. In C16 il valore della cella di input della colonna 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. Quindi il valore di input della cella della colonna 2 viene inserito 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 formula MEDIA(B16:B1015)si calcola il profitto simulato medio 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 si preme F9, vengono simulate 1000 iterazioni della domanda per ogni quantità dell'ordine. Produrre 40.000 carte produce sempre il profitto previsto più grande. Pertanto, sembra che produrre 40.000 schede sia la decisione giusta.

L'impatto del rischio sulla nostra decisione      Se sono stati prodotti 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 di quasi il 73%. Pertanto, se siamo estremamente avversi ai rischi, la decisione giusta potrebbe essere la produzione di 20.000 schede. Tra l'altro, la produzione di 10.000 carte ha sempre una deviazione standard di 0 carte perché se produciamo 10.000 carte, le venderemo sempre tutte senza residui.

Intervallo di confidenza per il profitto medio      Una domanda naturale da porsi in questa situazione è: in quale intervallo siamo sicuri del 95% che il profitto medio reale cada? Questo intervallo è denominato intervallo di confidenza del 95% per il profitto medio. Un intervallo di confidenza del 95% per la media di qualsiasi output di simulazione viene calcolato dalla formula seguente:

Nella cella J11 si calcola il limite inferiore per l'intervallo di confidenza del 95% sul profitto medio quando vengono prodotti 40.000 calendari con la formula D13–1,96*D14/SQRT(1000). Nella cella J12 si calcola il limite superiore per l'intervallo di confidenza del 95% con la formula D13+1,96*D14/RT.SQ(1000). Questi calcoli sono illustrati nella figura 60-7.

Siamo certi del 95% che il profitto medio di 40.000 calendari ordinati sia compreso tra $ 56.687 e $ 62.589.

1. Un rivenditore GMC ritiene che la domanda per gli inviati 2005 sarà normalmente distribuita con una media di 200 e una deviazione standard di 30. Il costo per la ricezione di un inviato è di $ 25.000 e vende un inviato per 40.000 dollari. La metà di tutti gli inviati non venduti a prezzo pieno può essere venduta per $ 30.000. Sta valutando di ordinare 200, 220, 240, 260, 280 o 300 inviati. Quanti deve ordinare?

2. Un piccolo supermercato sta cercando di determinare quante copie della rivista People devono ordinare ogni settimana. Ritengono che la domanda di Persone sia disciplinata dalla seguente variabile casuale 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 People e la vende per $ 1,95. Ogni copia invenduto può essere restituita per $ 0,50. Quante copie di Persone devono essere ordinate dall'archivio?