L'analisi di sensitività in excel ci aiuta a studiare l'incertezza nell'output del modello con i cambiamenti nelle variabili di input. Principalmente esegue prove di stress delle nostre ipotesi modellate e porta a informazioni a valore aggiunto.

Nel contesto della valutazione DCF, l'analisi di sensitività in Excel è particolarmente utile in finanza per modellare il prezzo delle azioni o la sensibilità della valutazione a ipotesi come i tassi di crescita o il costo del capitale.

In questo articolo, esaminiamo professionalmente la seguente analisi di sensibilità in Excel per la modellazione DCF.

La cosa più importante: scarica l'analisi della sensibilità nel modello Excel

Impara una variabile e due tipi di TABELLA DATI variabili in Excel

Analisi della sensibilità in Excel

# 1 - Analisi della sensibilità della tabella di dati a una variabile in Excel

Prendiamo l'esempio finanziario (modello di sconto sui dividendi) di seguito per comprendere questo in dettaglio.

La crescita costante DDM ci fornisce il valore equo di un'azione come valore attuale di un flusso infinito di dividendi che crescono a un tasso costante.

La formula di Gordon Growth è come sotto -

Dove:

D1 = Valore del dividendo da ricevere l'anno prossimo
D0 = Valore del dividendo ricevuto quest'anno
g = tasso di crescita del dividendo
Ke = tasso di sconto

Ora, supponiamo di voler capire quanto sia sensibile il prezzo del titolo rispetto al rendimento atteso (ke). Ci sono due modi per farlo -

Asino :-)
E se analisi

# 1 - Donkey Way

L'analisi della sensibilità in Excel utilizzando la via di Donkey è molto semplice, ma difficile da implementare quando sono coinvolte molte variabili.

Vuoi continuare a fare questo dato 1000 ipotesi? Ovviamente no!

Impara la seguente analisi di sensibilità nella tecnica Excel per salvarti dai guai.

# 2 - Utilizzo di una tabella di dati variabili

Il modo migliore per eseguire analisi di sensibilità in Excel è utilizzare le tabelle dati. Le tabelle di dati forniscono una scorciatoia per il calcolo di più versioni in un'unica operazione e un modo per visualizzare e confrontare i risultati di tutte le diverse varianti insieme sul foglio di lavoro. Di seguito sono riportati i passaggi che è possibile seguire per implementare un'analisi di sensibilità unidimensionale in Excel.

Passaggio 1: creare la tabella in un formato standard

Nella prima colonna, hai le ipotesi di input. Nel nostro esempio, gli input sono il tasso di rendimento atteso (ke). Inoltre, tieni presente che c'è una riga vuota (colorata in blu in questo esercizio) sotto l'intestazione della tabella. Questa riga vuota ha uno scopo importante per questa tabella di dati unidimensionale che vedrai nel passaggio 2.

Passaggio 2: collegare l'ingresso e l'uscita di riferimento come mostrato nell'istantanea di seguito.

Lo spazio fornito dalla riga vuota viene ora utilizzato per fornire l'input (ritorno previsto Ke) e la formula di output. Perché è fatto così?

Useremo "What if Analysis", questo è un modo per indicare a Excel che per Input (ke), la formula corrispondente fornita a destra dovrebbe essere utilizzata per ricalcolare tutti gli altri input.

Passaggio 3: selezionare lo strumento di analisi what-if per eseguire l'analisi della sensibilità in Excel

È importante notare che questo è suddiviso in due passaggi

Seleziona l'intervallo della tabella partendo dal lato sinistro a partire dal 10% fino all'angolo inferiore destro della tabella.
Fare clic su Dati -> What if Analysis -> Data Tables

Passaggio 4: si apre la finestra di dialogo della tabella dei dati.

La finestra di dialogo cerca due input: Input di riga e Input di colonna. Poiché è in esame un solo input Ke, forniremo un input di singola colonna.

Passaggio 5: collegare l'input della colonna

Nel nostro caso, tutto l'input è fornito in una colonna e quindi ci collegheremo all'input della colonna. L'input della colonna è collegato al rendimento atteso (Ke). Si noti che l'ingresso deve essere collegato dalla sorgente originale e non da quella che si trova all'interno della tabella

Passaggio 6: goditi l'output

# 2 - Analisi della sensibilità della tabella dati a due variabili in Excel

Le tabelle di dati sono molto utili per l'analisi di sensibilità in Excel soprattutto nel caso di DCF. Una volta stabilito un caso di base, l'analisi DCF dovrebbe sempre essere testata in vari scenari di sensibilità. Il test comporta l'esame dell'effetto incrementale di vari cambiamenti nelle ipotesi (costo del capitale, tassi di crescita terminali, minore crescita dei ricavi, requisiti di capitale più elevati, ecc.) Sul valore equo del titolo.

Prendiamo l'analisi della sensibilità in Excel con un esempio finanziario di Alibaba Discounted Cash Flow Analysis.

Con le ipotesi di base del costo del capitale al 9% e un tasso di crescita costante al 3% , siamo arrivati alla valutazione equa di $ 191,45 miliardi.

Supponiamo ora che tu non sia completamente d'accordo con le ipotesi sul costo del capitale o le ipotesi sul tasso di crescita che ho preso nella valutazione IPO di Alibaba. Potresti voler modificare le ipotesi e accedere all'impatto sulle valutazioni.

Un modo è modificare manualmente i presupposti e controllare i risultati di ogni modifica. (parola in codice - metodo asino!)

Tuttavia, siamo qui per discutere un modo molto migliore ed efficiente per calcolare la valutazione utilizzando l'analisi della sensibilità in Excel che non solo fa risparmiare tempo ma ci fornisce anche un modo per visualizzare tutti i dettagli di output in un formato efficace.

Se eseguiamo l'analisi What-if in Excel in modo professionale sui dati di cui sopra, otteniamo il seguente output.

Qui, gli input di riga consistono in variazioni del costo del capitale o del WACC (dal 7% all'11%)
Gli input di colonna consistono in variazioni nei tassi di crescita (dall'1% al 6%)
Il punto di intersezione è Alibaba Valuation. Per es. utilizzando il nostro caso base del 9% di WACC e di tassi di crescita del 3%, otteniamo una valutazione di $ 191,45 miliardi.

Con questo background, vediamo ora come possiamo preparare un'analisi di sensibilità di questo tipo in Excel utilizzando tabelle di dati bidimensionali.

Passaggio 1: creare la struttura della tabella come indicato di seguito

Poiché abbiamo due serie di ipotesi: costo del capitale (WACC) e tassi di crescita (g), è necessario preparare una tabella riportata di seguito.
Sei libero di cambiare gli input di riga e colonna. Invece del WACC, potresti avere tassi di crescita e viceversa.

Passaggio 2: collegare il punto di intersezione alla cella di output.

Il punto di intersezione dei due ingressi dovrebbe essere utilizzato per collegare l'uscita desiderata. In questo caso, vogliamo vedere l'effetto di queste due variabili (WACC e tasso di crescita) sul valore dell'equity. Quindi, abbiamo collegato la cella intersecante all'output.

Passaggio 3: aprire la tabella dati bidimensionale

Seleziona la tabella che hai creato
Quindi fare clic su Dati -> E se analisi -> Tabelle dati

Passaggio 4: fornire gli input di riga e gli input di colonna.

L'input di riga è il costo del capitale o Ke.
L'input della colonna è il tasso di crescita.
Ricordarsi di collegare questi input dalla fonte di ipotesi originale e non da qualsiasi parte all'interno della tabella

Passaggio 5: goditi l'output.

I valori di output più pessimistici si trovano nell'angolo in alto a destra dove il costo del capitale è dell'11% e il tasso di crescita è solo dell'1%
Il valore IPO di Alibaba più ottimistico è quando Ke è 7% eg è 6%
Il caso di base che abbiamo calcolato per tassi di crescita del 9% ke e del 3% si trova nel mezzo.
Questa analisi di sensibilità bidimensionale nella tabella Excel fornisce ai clienti un'analisi dello scenario semplice che consente di risparmiare molto tempo.

# 3 - Ricerca obiettivo per analisi di sensibilità in Excel

Il comando Cerca obiettivo viene utilizzato per portare una formula a un valore specifico
Lo fa modificando una delle celle a cui fa riferimento la formula
Goal Seek richiede un riferimento di cella che contenga una formula (la cella Imposta). Chiede anche un valore, che è la cifra che vuoi che la cella sia uguale
Infine, Goal Seek chiede di modificare una cella per portare la cella Set al valore richiesto

Diamo uno sguardo al DCF di Alibaba IPO Valuation.

Come sappiamo da DCF, i tassi di crescita e la valutazione sono direttamente correlati. L'aumento dei tassi di crescita aumenta il prezzo delle azioni del titolo.

Supponiamo di voler verificare a quale tasso di crescita il prezzo delle azioni toccherà $ 80?

Come sempre, possiamo farlo manualmente modificando i tassi di crescita per continuare a vedere l'impatto sul prezzo delle azioni. Anche questo sarà un processo noioso, potremmo dover inserire tassi di crescita molte volte per assicurarci che il prezzo delle azioni corrisponda a $ 80 nel nostro caso.

Tuttavia, possiamo utilizzare una funzione come Goal Seek in Excel per risolvere questo problema in semplici passaggi.

Passaggio 1: fare clic sulla cella di cui si desidera impostare il valore. (La cella Imposta deve contenere una formula)

Passaggio 2: selezionare Strumenti, Ricerca obiettivo dal menu e verrà visualizzata la seguente finestra di dialogo:

Il comando Cerca obiettivo suggerisce automaticamente la cella attiva come cella Imposta.
Questo può essere sovrascritto con un nuovo riferimento di cella oppure puoi fare clic sulla cella appropriata nel foglio di calcolo.
Ora inserisci il valore desiderato che questa formula dovrebbe raggiungere.
Fare clic all'interno della casella "Al valore" e digitare il valore che si desidera che la formula selezionata sia uguale
Infine, fai clic all'interno della casella "Cambiando cella" e digita o fai clic sulla cella il cui valore può essere modificato per ottenere il risultato desiderato
Fare clic sul pulsante OK e il foglio di calcolo modificherà la cella su un valore sufficiente affinché la formula raggiunga il tuo obiettivo.

Passaggio 3: goditi l'output.

Goal Seek ti informa anche che l'obiettivo è stato raggiunto

Conclusione

L'analisi di sensibilità in Excel aumenta la tua comprensione del comportamento finanziario e operativo dell'azienda. Come abbiamo appreso dai tre approcci - Tabelle dati unidimensionali, Tabelle dati bidimensionali e Ricerca obiettivo, che l'analisi della sensibilità è estremamente utile nel campo finanziario, specialmente nel contesto delle valutazioni - DCF o DDM.

Tuttavia, puoi anche ottenere una comprensione a livello macro dell'azienda e del settore in generale. È possibile sviluppare casi per riflettere la sensibilità della valutazione alle variazioni dei tassi di interesse, recessione, inflazione, PIL, ecc. Sulla valutazione. Il pensiero e il buon senso dovrebbero essere impiegati nello sviluppo di casi di sensibilità ragionevoli e utili.

Cosa succederà?

Se hai imparato qualcosa sull'analisi della sensibilità in Excel, lascia un commento qui sotto. Fatemi sapere cosa ne pensate. Molte grazie e attenzione. Buon apprendimento!

Puoi anche dare un'occhiata a questi articoli di seguito per saperne di più su valutazioni e finanza aziendale -