Analisi di sensibilità in Excel (foglio dati di esempio)

I processi nel campo della finanza sono sempre interconnessi: un fattore dipende da un altro e cambia con esso. Tieni traccia di queste modifiche e capisci cosa aspettarti in futuro, magari utilizzando le funzioni di Excel e i metodi del foglio di calcolo.

Ottenere più risultati con una tabella di dati

Le funzionalità del foglio dati sono elementi dell'analisi what-if, spesso eseguita tramite Microsoft Excel. Questo è il secondo nome per l'analisi di sensibilità.

Panoramica

Una tabella dati è un tipo di intervallo di celle che può essere utilizzato per risolvere problemi modificando i valori in alcune celle. Viene creato quando è necessario tenere traccia delle modifiche nei componenti della formula e ricevere aggiornamenti sui risultati, in base a tali modifiche. Scopriamo come utilizzare le tabelle di dati nella ricerca e di che tipo si tratta.

Nozioni di base sulle tabelle di dati

Esistono due tipi di tabelle di dati, differiscono per il numero di componenti. Devi compilare una tabella concentrandoti sul numero di valori uXNUMXbuXNUMXbche devi verificare con esso.

Gli statistici utilizzano una singola tabella di variabili quando è presente una sola variabile in una o più espressioni che può modificare il loro risultato. Ad esempio, viene spesso utilizzato insieme alla funzione PMT. La formula è studiata per calcolare l'importo del pagamento regolare e tiene conto del tasso di interesse specificato nel contratto. In tali calcoli, le variabili vengono scritte in una colonna e i risultati dei calcoli in un'altra. Un esempio di targa dati con 1 variabile:

Quindi, considera i piatti con 2 variabili. Sono utilizzati nei casi in cui due fattori influenzano il cambiamento in qualsiasi indicatore. Le due variabili possono finire in un'altra tabella associata al prestito, che può essere utilizzata per determinare il periodo ottimale di rimborso e l'importo della rata mensile. In questo calcolo è inoltre necessario utilizzare la funzione PMT. Un esempio di tabella con 2 variabili:

Creazione di una tabella di dati con una variabile

Considera il metodo di analisi utilizzando l'esempio di una piccola libreria con solo 100 libri in stock. Alcuni di loro possono essere venduti più costosi ($ 50), il resto costerà meno agli acquirenti ($ 20). Viene calcolato il reddito totale dalla vendita di tutti i beni: il proprietario ha deciso che avrebbe venduto il 60% dei libri a un prezzo elevato. Devi scoprire come aumenteranno le entrate se aumenti il ​​prezzo di un volume maggiore di beni - 70% e così via.

Fai attenzione! Il ricavo totale deve essere calcolato utilizzando una formula, altrimenti non sarà possibile compilare una tabella dati.

1. Seleziona una cella libera lontana dal bordo del foglio e scrivi la formula al suo interno: =Cella delle entrate totali. Ad esempio, se il reddito è scritto nella cella C14 (è indicata la designazione casuale), è necessario scrivere questo: =S14.
2. Scriviamo la percentuale del volume delle merci nella colonna a sinistra di questa cella – non al di sotto, questo è molto importante.
3. Selezioniamo l'intervallo di celle in cui si trovano la colonna percentuale e il collegamento al reddito totale.

4. Troviamo nella scheda “Dati” la voce “What if analysis” e facciamo clic su di essa – nel menu che si apre, selezioniamo l'opzione “Data table”.

5. Si aprirà una piccola finestra in cui è necessario specificare una cella con la percentuale di libri inizialmente venduti a prezzo elevato nella colonna "Sostituisci valori per righe in...". Questo passaggio viene eseguito per ricalcolare il ricavo totale tenendo conto della percentuale crescente.

Dopo aver cliccato sul pulsante “OK” nella finestra in cui sono stati inseriti i dati per la compilazione della tabella, nelle righe compariranno i risultati dei calcoli.

Aggiunta di una formula a una singola tabella di dati variabili

Da una tabella che ha aiutato a calcolare un'azione con una sola variabile, puoi creare uno strumento di analisi sofisticato aggiungendo una formula aggiuntiva. Deve essere inserito accanto a una formula già esistente – ad esempio, se la tabella è orientata alla riga, inseriamo l'espressione nella cella a destra di quella esistente. Quando l'orientamento della colonna è impostato, scriviamo la nuova formula sotto quella vecchia. Quindi, segui l'algoritmo:

1. Seleziona di nuovo l'intervallo di celle, ma ora dovrebbe includere la nuova formula.
2. Apri il menu di analisi "what if" e seleziona "Datasheet".
3. Aggiungiamo una nuova formula al campo corrispondente in righe o colonne, a seconda dell'orientamento del piatto.

Creare una tabella di dati con due variabili

L'inizio di tale tabella è leggermente diverso: è necessario inserire un collegamento alle entrate totali sopra i valori percentuali. Successivamente, eseguiamo questi passaggi:

1. Scrivi le opzioni di prezzo in una riga con un collegamento al reddito: una cella per ogni prezzo.
2. Seleziona un intervallo di celle.

3. Aprire la finestra della tabella dei dati, come quando si compila una tabella con una variabile, tramite la scheda "Dati" sulla barra degli strumenti.
4. Sostituisci nella colonna "Sostituisci valori con colonne in ..." una cella con un prezzo iniziale elevato.
5. Aggiungi una cella con la percentuale iniziale delle vendite di libri costosi alla colonna "Sostituisci i valori con le righe in ..." e fai clic su "OK".

Di conseguenza, l'intera tabella viene riempita con importi di possibili entrate con condizioni diverse per la vendita di beni.

Velocizzare i calcoli per fogli di lavoro contenenti tabelle di dati

Se hai bisogno di calcoli rapidi in una tabella di dati che non attivano un ricalcolo dell'intera cartella di lavoro, ci sono alcune cose che puoi fare per accelerare il processo.

1. Aprire la finestra delle opzioni, selezionare la voce “Formule” nel menu a destra.
2. Selezionare la voce “Automatico, ad eccezione delle tabelle dati” nella sezione “Calcoli nella cartella di lavoro”.

3. Ricalcoliamo manualmente i risultati nella tabella. Per fare ciò, seleziona le formule e premi il tasto F.

Altri strumenti per l'esecuzione dell'analisi di sensibilità

Ci sono altri strumenti nel programma per aiutarti a eseguire l'analisi della sensibilità. Automatizzano alcune azioni che altrimenti dovrebbero essere eseguite manualmente.

1. La funzione "Selezione parametro" è adatta se si conosce il risultato desiderato ed è necessario conoscere il valore di input della variabile per ottenere tale risultato.
2. "Cerca una soluzione" è un componente aggiuntivo per la risoluzione dei problemi. È necessario stabilire dei limiti e indicarli, dopodiché il sistema troverà la risposta. La soluzione è determinata modificando i valori.
3. L'analisi della sensibilità può essere eseguita utilizzando lo Scenario Manager. Questo strumento si trova nel menu di analisi what-if nella scheda Dati. Sostituisce i valori in più celle: il numero può raggiungere 32. Il dispatcher confronta questi valori in modo che l'utente non debba modificarli manualmente. Un esempio di utilizzo dello script manager:

Analisi di sensitività di un progetto di investimento in Excel

L'analisi what-if è particolarmente utile in situazioni in cui è richiesta la previsione, come gli investimenti. Gli analisti utilizzano questo metodo per scoprire come cambierà il valore delle azioni di una società a seguito di variazioni di alcuni fattori.

Metodo di analisi della sensibilità agli investimenti

Quando si analizza "e se" utilizzare l'enumerazione: manuale o automatica. L'intervallo di valori è noto e vengono sostituiti nella formula uno per uno. Il risultato è un insieme di valori. Scegli il numero appropriato da loro. Consideriamo quattro indicatori per i quali viene effettuata l'analisi di sensitività in ambito finanziario:

1. Valore Attuale Netto – Calcolato sottraendo l'importo dell'investimento dall'importo del reddito.
2. Tasso interno di rendimento/profitto: indica quanto profitto deve essere ricevuto da un investimento in un anno.
3. Il payback ratio è il rapporto tra tutti i profitti e l'investimento iniziale.
4. Indice di profitto scontato: indica l'efficacia dell'investimento.

Formula

La sensibilità di incorporamento può essere calcolata utilizzando questa formula: Modifica del parametro di uscita in % / Modifica del parametro di ingresso in %.

I parametri di output e di input possono essere i valori descritti in precedenza.

1. È necessario conoscere il risultato in condizioni standard.
2. Sostituiamo una delle variabili e monitoriamo le modifiche nel risultato.
3. Calcoliamo la variazione percentuale di entrambi i parametri rispetto alle condizioni stabilite.
4. Inseriamo le percentuali ottenute nella formula e determiniamo la sensibilità.

Un esempio di analisi di sensitività di un progetto di investimento in Excel

Per una migliore comprensione della metodologia di analisi, è necessario un esempio. Analizziamo il progetto con i seguenti dati noti:

1. Compila la tabella per analizzare il progetto su di essa.

2. Calcoliamo il flusso di cassa utilizzando la funzione OFFSET. Nella fase iniziale, il flusso è uguale agli investimenti. Successivamente, applichiamo la formula: =SE(OFFSET(Numero,1;)=2;SOMMA(Afflusso 1:Uscita 1); SOMMA(Afflusso 1:Uscita 1)+$B$ 5)
   Le designazioni delle celle nella formula possono essere diverse, a seconda del layout della tabella. Alla fine, viene aggiunto il valore dei dati iniziali: il valore di salvataggio.

3. Determiniamo il periodo per il quale il progetto ripagherà. Per il periodo iniziale utilizziamo questa formula: =SUMMOSAMENTE(G7: g17;»<0″). L'intervallo di celle è la colonna del flusso di cassa. Per ulteriori periodi, applichiamo questa formula: =Periodo iniziale+IF(Primo e.stream>0; Primo e.stream;0). Il progetto è al punto di pareggio in 4 anni.

4. Creiamo una colonna per i numeri di quei periodi in cui il progetto dà i suoi frutti.

5. Calcoliamo il ritorno sull'investimento. È necessario fare un'espressione in cui il profitto in un determinato periodo di tempo è diviso per l'investimento iniziale.

6. Determiniamo il fattore di sconto utilizzando questa formula: =1/(1+Disc.%) ^Numero.

7. Calcoliamo il valore attuale usando la moltiplicazione: il flusso di cassa viene moltiplicato per il fattore di sconto.

8. Calcoliamo PI (indice di redditività). Il valore attuale nel tempo è diviso per l'investimento all'inizio del progetto.

9. Definiamo il tasso di rendimento interno utilizzando la funzione IRR: =IRR(Intervallo di flusso di cassa).

Analisi della sensibilità agli investimenti utilizzando il foglio dati

Per l'analisi dei progetti nel campo degli investimenti, altri metodi sono più adatti di una tabella di dati. Molti utenti provano confusione durante la compilazione di una formula. Per scoprire la dipendenza di un fattore dalle modifiche degli altri, è necessario selezionare le celle corrette per l'immissione dei calcoli e per la lettura dei dati.

Analisi fattoriale e di dispersione in Excel con automazione del calcolo

Un'altra tipologia di analisi di sensibilità è l'analisi fattoriale e l'analisi della varianza. Il primo tipo definisce la relazione tra i numeri, il secondo rivela la dipendenza di una variabile dalle altre.

ANOVA in Excel

Lo scopo di tale analisi è di dividere la variabilità del valore in tre componenti:

1. Variabilità come risultato dell'influenza di altri valori.
2. Cambiamenti dovuti al rapporto dei valori che lo influenzano.
3. Cambiamenti casuali.

Eseguiamo l'analisi della varianza tramite il componente aggiuntivo di Excel "Analisi dei dati". Se non è abilitato, può essere abilitato nelle impostazioni.

La tabella iniziale deve seguire due regole: c'è una colonna per ogni valore e i dati in essa contenuti sono disposti in ordine crescente o decrescente. È necessario verificare l'influenza del livello di istruzione sui comportamenti in conflitto.

1. Trova lo strumento Analisi dei dati nella scheda Dati e apri la sua finestra. Nell'elenco è necessario selezionare l'analisi della varianza unidirezionale.

2. Completa le righe della finestra di dialogo. L'intervallo di input è costituito da tutte le celle, escluse le intestazioni e i numeri. Raggruppa per colonne. Mostriamo i risultati su un nuovo foglio.

Poiché il valore nella cella gialla è maggiore di uno, l'ipotesi può essere considerata errata: non esiste alcuna relazione tra istruzione e comportamento in conflitto.

Analisi fattoriale in Excel: un esempio

Analizziamo la relazione dei dati nel campo delle vendite: è necessario identificare i prodotti popolari e quelli impopolari. Informazioni iniziali:

1. Dobbiamo scoprire per quali beni la domanda è aumentata di più durante il secondo mese. Stiamo compilando una nuova tabella per determinare la crescita e il calo della domanda. La crescita si calcola con questa formula: =SE((Richiesta 2-Richiesta 1)>0; Domanda 2-Richiesta 1;0). Formula di diminuzione: =SE(Crescita=0; Domanda 1- Domanda 2;0).

2. Calcola la crescita della domanda di beni in percentuale: =SE(Crescita/Risultato 2 =0; Diminuzione/Risultato 2; Crescita/Risultato 2).

3. Facciamo un grafico per chiarezza: seleziona un intervallo di celle e crea un istogramma tramite la scheda "Inserisci". Nelle impostazioni, è necessario rimuovere il riempimento, questo può essere fatto tramite lo strumento Formatta serie di dati.

Analisi bidirezionale della varianza in Excel

L'analisi della varianza viene eseguita con diverse variabili. Consideralo con un esempio: devi scoprire quanto velocemente si manifesta la reazione a un suono di volume diverso negli uomini e nelle donne.

1. Apriamo "Analisi dei dati", nell'elenco è necessario trovare un'analisi della varianza a due vie senza ripetizioni.
2. Intervallo di input: celle che contengono dati (senza intestazione). Mostriamo i risultati su un nuovo foglio e facciamo clic su "OK".

Il valore F è maggiore di F-critico, il che significa che il pavimento influisce sulla velocità di reazione al suono.

Conclusione

In questo articolo è stata discussa in dettaglio l'analisi di sensibilità in un foglio di calcolo Excel, in modo che ogni utente sia in grado di comprendere i metodi della sua applicazione.