Introduzione a Monte la simulazione 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.

In questo articolo è stato adattato da analisi dei dati di Microsoft Excel e modelli di Business Winston.

  • Gli utenti che utilizzano Monte la simulazione?

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

  • Come è possibile simulare valori di una variabile casuale distinte?

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

  • Come una società biglietto di auguri possa determinare quante carte per produrre?

Si desidera valutare con precisione la probabilità di certi eventi. Ad esempio, che cos'è la probabilità che i flussi di cassa un nuovo prodotto avrà un valore attuale netto positivo (NPV)? Che cos'è il fattore di rischio del portfolio investimento? Monte la simulazione consente a situazioni di modello che presentano incertezza e quindi riprodurli in un computer migliaia di volte.

Nota: Il nome Monte la simulazione provengono da simulazioni computer eseguite durante la 1930s e 1940s per stimare la probabilità che la reazione a catena necessaria per un bomb atom alla detonazione funzioni correttamente. Physicists per il corretto funzionamento sono state grande consumatore di scommesse, in modo fornita simulazioni il nome di codice Monte Carlo.

In avanti cinque capitoli, verrà visualizzato esempi di come è possibile utilizzare Excel per eseguire simulazioni Monte Carlo.

Molte aziende utilizzano Monte la simulazione un importante durante il processo decisionale. Ecco alcuni esempi.

  • Motori generale, Proctor e Gamble, Pfizer, Squibb Myers Bristol ed Eli Lilly utilizzare simulazione per stimare rendimento medio e il fattore di rischio di nuovi prodotti. In compilato, queste informazioni vengono usate per il CEO per determinare quali prodotti provengono sul mercato.

  • Compilato utilizzati simulazione per le attività, ad esempio la previsione reddito netto per l'azienda, la previsione dei costi strutturali e di acquisti e determinare la sensibilità a diversi tipi di rischio (ad esempio le modifiche tasso di interesse e fluttuazioni dei).

  • Lilly utilizza simulazione per determinare la capacità di una pianta ottimale per ogni farmaco.

  • Proctor e Gamble utilizza simulazione per modellare e in modo ottimale salvaguardarsi rischi di cambio.

  • SEARS simulazione viene utilizzata per determinare il numero di unità di ogni riga di prodotto deve essere ordinato presso fornitori, ad esempio, il numero di coppie di pantaloni Dockers ordinati anno corrente.

  • Società Oil e il utilizzare simulazione al valore "Opzioni reali", ad esempio il valore di un'opzione per espandere, contratto o posticipare un progetto.

  • Pianificazioni finanziarie utilizzano Monte la simulazione strategie di investimento ottimale per il fondo pensione dei clienti.

Quando si digita la formula = casuale () in una cella, viene restituito un numero che è identico che può avere un valore compreso tra 0 e 1. In questo modo, intorno al 25% di tempo, è necessario ottenere un numero minore o uguale a 0.25; 10% del tempo che è necessario ottenere un numero che sono alternative almeno 0,90 e così via. Per dimostrare come funziona la funzione RAND, esaminare il file Randdemo.xlsx, illustrato nella figura 60-1.

Immagine del manuale
Figura che illustra la funzione RAND con 1 a 60

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

Prima di tutto, copiare la cella C3 a C4:C402 la formula = casuale (). È quindi il nome dell'intervallo C3:C402 dati. Quindi, nella colonna F, è possibile tenere traccia la media dei numeri casuali 400 (cella F2) e utilizzare la funzione CONTA.Se per determinare le frazioni sono compresi 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, vengono ricalcolati numeri casuali. Si noti che la media dei 400 numeri sia sempre circa 0,5 e intorno al 25% dei risultati sono 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), fornisce alcuna informazione sui valori di altri numeri casuali generati.

Si supponga che la domanda per un calendario è disciplinata dalla variabile casuale distinte seguente:

Richiesta

Probabilità

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

Come possiamo abbiamo Excel riprodotta o simulare, questa domanda calendari per tutte le volte? Il trucco sta per associare ogni valore possibile della funzione casuale con una richiesta possibili per i calendari. L'assegnazione seguente garantisce che una richiesta di 10.000 verrà eseguita del 10% del tempo e così via.

Richiesta

Numero casuale

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 inferiore a 0,75

60.000

Maggiore o uguale a 0,75

Per illustrare la simulazione della domanda, esaminare il file Discretesim.xlsx, illustrato nella figura 60-2 nella pagina successiva.

Immagine del manuale
Simulare una variabile casuale distinte figura 60-2

Il tasto per la simulazione consiste nell'usare un numero casuale per avviare una ricerca nell'intervallo tabella F2:G5 (denominato ricerca). Numero casuale maggiore o uguale a 0 e minore di 0,10 comporterà una richiesta di 10.000; numero casuale maggiore o uguale a 0,10 e minore di 0,45 comporterà una richiesta di 20.000; numero casuale maggiore o uguale a 0,45 e inferiore a 0,75 comporterà una richiesta di 40.000; e numeri casuali maggiori o uguali a 0,75 comporterà una richiesta di 60.000. È generare numeri casuali 400 copiando dal C3 C4:C402 formula casuale (). È quindi generare 400 prove o iterazioni della domanda calendario copiando da B3 a B4:B402 VLOOKUP(C3,lookup,2)la formula. Questa formula garantisce che un numero casuale minore di 0,10 genera una richiesta di 10.000, un numero casuale compreso tra 0,10 e 0,45 genera una richiesta di 20.000 e così via. Nell'intervallo di celle F8:F11, utilizzare la funzione CONTA.Se per determinare il numero di 400 iterazioni restituendo ogni richiesta. Quando si preme F9 per ricalcolare i numeri casuali, probabilità simulata sono vicino la probabilità di presunto richiesta.

Se si digita in una cella qualsiasi formula NORMINV(rand(),mu,sigma), verrà generato un valore di una variabile casuale normale con una media di mu e deviazione standard sigmasimulato. Questa procedura viene illustrata nel file Normalsim.xlsx, illustrato nella figura 60-3.

Immagine del manuale
Nella figura 60-3 simulare una variabile casuale normale

Si supponga che vogliamo simulare 400 prove o iterazioni di una variabile casuale normale con una media di 40.000 e una deviazione standard di 10.000. (È possibile digitare i valori nelle celle E1 ed E2 e assegnare un nome a queste celle significa e sigma, rispettivamente.) Copiare la formula = casuale () da C4 a C5:C403 genera 400 diversi numeri casuali. Copiando B4 a B5:B403 la formula NORMINV(C4,mean,sigma) genera 400 diversi valori di valutazione di una variabile casuale normale 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 vicino 40.000 e la deviazione standard vicino 10.000.

In pratica, per un numero casuale x, la formula NORMINV(p,mu,sigma) genera pesimo dato 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 percentile circa 77th di una variabile casuale normale con una media di 40.000 e una deviazione standard di 10.000.

In questa sezione, verrà visualizzato come simulazione Monte Carlo può essere utilizzata come uno strumento decisionale. Si supponga che la domanda per un biglietto di San Valentino è disciplinata dalla variabile casuale distinte seguente:

Richiesta

Probabilità

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

Biglietto d'auguri venduto per $4.00, e il costo variabile di produzione ogni scheda è $1,50. È necessario eliminare schede rimasti al costo di 0,20 dollari per scheda. Stampare il numero di carte?

In pratica, simulato ogni quantità di produzione possibili (10.000, 20.000, 40.000 o 60.000) tutte le volte (ad esempio 1000 iterazioni). È quindi determinare quali quantità ordine restituisce il massimo profitto medio su iterazioni 1000. È possibile trovare i dati per la sezione nel file Valentine.xlsx, illustrato nella figura 60-4. Per assegnare i nomi di intervallo di celle B1:B11 a C1:C11 celle. L'intervallo di celle G3:H6 viene assegnato il nome ricerca. Il prezzo di vendita e parametri dei costi vengono immessi in celle C4: C6.

Immagine del manuale
Simulazione biglietto di San Valentino figura 60-4

È possibile immettere una quantità di produzione di prova (40.000 in questo esempio) nella cella C1. Creare un numero casuale nella cella C2 con la formula = casuale (). Come descritto in precedenza, consente di simulare la domanda per la scheda nella cella C3 con la formula VLOOKUP(rand,lookup,2). (Nella formula Cerca. vert casuale è il nome della cella assegnato alla cella C3, non la funzione casuale)

Il numero di unità vendute è il valore minore di quantità di produzione e richiesta. Nella cella C8, si calcolano i ricavi con la formula MIN (prodotta, domanda) * prezzo unitario. Nella cella C9, per calcolare il costo totale di produzione con la formula prodotti * unit_prod_cost.

Se si producono altre schede quelle della domanda, il numero di unità rimasti produzione uguale a meno richiesta; in caso contrario non unità rimangono nel sistema. Viene calcolato il costo di eliminazione nella cella C10 con la formula unit_disp_cost * se (prodotto > domande, prodotto-domanda, 0). Infine, nella cella C11, viene calcolato il profitto come ricavi-total_var_cost-total_disposing_cost.

Si preferisce un modo efficace per premere F9 tutte le volte (ad esempio 1000) per ogni quantità di produzione e calcolare il profitto previsto per ogni quantità. Questa situazione corrisponde a uno in cui è disponibile una tabella dati bidirezionale per il salvataggio. (Per informazioni dettagliate sulle tabelle dati, vedere il capitolo 15 "Riservatezza analisi con tabelle dati,"). La tabella di dati utilizzata in questo esempio è illustrata nella figura 60-5.

Immagine del manuale
Tabella dati bidirezionale in figura 60-5 per la simulazione del biglietto

Nell'intervallo di celle A16:A1015 immettere i numeri 1 – 1000 (corrispondente al nostro prove 1000). Un modo facile per creare questi valori è necessario immettere 1 nella cella A16. Selezionare la cella, quindi nella scheda Home nel gruppo Modifica 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 incremento di 1 e un valore smettere di 1000. Nell'area Serie In selezionare l'opzione di colonne e quindi fare clic su OK. I numeri 1 – 1000 sarà immesso nella colonna inizio nella cella A16.

Immagine del manuale
Figura 60-6 utilizzando la finestra di dialogo serie di riempimento tra i numeri di versione di valutazione 1 e 1000

È quindi immettere la quantità di produzione possibili (10.000, 20.000, 40.000, 60.000) nelle celle B15:E15. Si desidera calcolare il margine per ogni numero di versione di valutazione (da 1 a 1000) e ogni quantità di produzione. Si fa riferimento alla formula per profitto (calcolato nella cella C11) nella cella superiore sinistra della tabella di dati (A15) immettendo = C11.

Si sta per essere trucco Excel in simulazione 1000 iterazioni della domanda per ogni quantità di produzione. Selezionare l'intervallo di tabella (A15:E1014) e quindi nel gruppo Strumenti dati nella scheda dati, fare clic su analisi di possibili e quindi selezionare la tabella dati. Per configurare una tabella dati bidirezionale, scegliere la quantità di produzione (cella C1) come la cella di Input per riga e selezionare una cella vuota (abbiamo scelto cella I14) come la cella di Input per colonna. Dopo aver fatto clic su OK, Excel consente di simulare 1000 valori richiesta per ogni ordine.

Per comprendere perché questo metodo funziona, considerare i valori inseriti tramite la tabella di dati nell'intervallo di celle C16:C1015. Per ognuna di queste celle, Excel verrà utilizzato un valore di 20.000 nella cella C1. In C16, il valore della cella di input per colonna pari a 1 viene inserito in una cella vuota e il numero casuale nella cella che C2 Ricalcola. Il profitto corrispondente quindi è registrato nella cella C16. Quindi il valore di input di cella colonna pari a 2 viene inserito in una cella vuota e il numero casuale nella cella C2 Ricalcola nuovamente. Il profitto corrispondente viene immesso nella cella C17.

Copiando dalla cella B13 a C13:E13 la formula AVERAGE(B16:B1015), verrà eseguito il calcolo profitto simulato Media per ogni quantità di produzione. Copiando dalla cella B14 in C14:E14 STDEV(B16:B1015)la formula, è la deviazione standard di simulata utili per ogni ordine. Ogni volta che si preme F9, 1000 iterazioni della domanda sono simulate per ogni ordine. La creazione di 40.000 biglietti sempre restituisce il massimo profitto previsto. Pertanto, sembra che produrre 40.000 schede decisioni appropriate.

L'impatto del rischio sulla decisione     Se viene prodotta 20.000 anziché 40.000 schede, il profitto previsto inserisce circa 22%, ma il rischio (misurati sulla base la deviazione standard di profitto) inserisce quasi 73%. Pertanto, se sono estremamente propensione al rischio, la creazione di biglietti di 20.000 potrebbe essere la decisione giusta. A proposito, la creazione di biglietti di 10.000 sempre ha una deviazione standard pari a 0 schede perché se si producono 10.000 schede, è sempre vendita tutti gli elementi senza qualsiasi cibo.

Nota: In questa cartella di lavoro, l'opzione di calcolo è impostata su Automatico ad eccezione di tabelle. (Usare il comando di calcolo nel gruppo calcolo della scheda formule). Questa impostazione garantisce che la tabella di dati non verrà ricalcolate a meno che non si premere F9, che è consigliabile perché una tabella di dati di grandi dimensioni rallentano verso il basso il lavoro se essa viene ricalcolata ogni volta che si digita qualcosa che nel foglio di lavoro. Si noti che in questo esempio, quando si preme F9, il profitto medio verrà modificati. Infatti, ogni volta che si preme F9, una diversa sequenza di numeri casuali 1000 verrà usata per generare le richieste di ogni ordine.

Intervallo di confidenza per indicare profitto     Una domanda naturale in questa situazione è in quali intervallo siamo che il profitto medio true corrisponderà del 95%? Questo intervallo è l' intervallo di confidenza del 95% per profitto Media. Un intervallo di confidenza del 95% della media di un tipo di output simulazione viene calcolato mediante la seguente formula:

Immagine del manuale

Nella cella J11 per calcolare il limite minimo per l'intervallo di confidenza del 95% sul margine Media quando 40.000 calendari creati con la formula D13–1.96*D14/SQRT(1000). Nella cella J12 per calcolare il valore massimo per l'intervallo di confidenza del 95% con la formula D13+1.96*D14/SQRT(1000). Questi calcoli vengono visualizzati nella figura 60-7.

Immagine del manuale
Intervallo di confidenza del 95% figura 60-7 per profitto Media quando vengono ordinati 40.000 calendari

Siamo che sia il profitto medio quando vengono ordinati 40.000 calendari tra $56,687 e $62,589 del 95%.

  1. Un rivenditore GMC si ritiene che la domanda per inviati 2005 verrà distribuita normalmente con una media di 200 e la deviazione standard pari a 30. Il costo di ricevere un invio è $25.000 e vende un invio per $40.000. Metà di tutti gli inviati non ha venduti prezzi completo può essere venduta per $30.000. Sta valutando l'ordinamento 200, 220, 240, 260, 280 o 300 inviati. Quanti deve aver ordine?

  2. Un piccolo supermercato sta tentando di stabilire il numero di copie delle persone rivista deve ordinano ogni settimana. Si ritiene che loro domanda per le persone è disciplinata dalla variabile casuale distinte seguente:

    Richiesta

    Probabilità

    15

    0,10

    20

    0,20

    25

    0,30

    30

    0,25

    35

    0,15

  3. Supermercato paga $1.00 per ogni copia delle persone e venduto per $1.95. Ogni copia non venduto può essere restituito per $0,50. Il numero di copie delle persone deve l'archivio ordine?

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.

×