Analiza sensibilității în Excel ne ajută să studiem incertitudinea în rezultatul modelului cu modificările variabilelor de intrare. În primul rând, efectuează testarea stresului a ipotezelor noastre modelate și conduce la informații cu valoare adăugată.
În contextul evaluării DCF, Analiza sensibilității în excel este utilă în special în finanțe pentru modelarea prețului acțiunilor sau a sensibilității evaluării la ipoteze precum ratele de creștere sau costul capitalului.
În acest articol, ne uităm la următoarea analiză de sensibilitate în Excel pentru modelarea DCF în mod profesional.
- Un tabel de date variabile
- Tabel de date cu două variabile
- Căutarea obiectivului
Cel mai important - Descărcați Analiza sensibilității în șablonul Excel
Aflați o variabilă și două variabile TABEL DE DATE Tipuri în Excel
Analiza sensibilității în Excel
# 1 - Analiza sensibilității tabelului de date cu o singură variabilă în Excel
Să luăm exemplul de finanțe (model de reducere a dividendelor) de mai jos pentru a-l înțelege în detaliu.
Creștere constantă DDM ne oferă valoarea justă a unei acțiuni ca valoare actuală a unui flux infinit de dividende crescând la o rată constantă.
Formula Gordon Growth este după cum urmează -
Unde:
- D1 = Valoarea dividendului care urmează să fie încasat anul viitor
- D0 = Valoarea dividendului primit în acest an
- g = Rata de creștere a dividendului
- Ke = Rata de reducere
Acum, să presupunem că vrem să înțelegem cât de sensibil este prețul acțiunilor față de randamentul așteptat (ke). Există două moduri de a face acest lucru -
- Drumul măgarului :-)
- Ce se întâmplă dacă Analiza
# 1 - Donkey Way
Analiza sensibilității în Excel folosind calea lui Donkey este foarte simplă, dar greu de implementat atunci când sunt implicate o mulțime de variabile.
Doriți să continuați să faceți aceste 1000 de presupuneri? Evident nu!
Aflați următoarea analiză de sensibilitate în tehnica excel pentru a vă salva de probleme.
# 2 - Utilizarea unui tabel de date variabil
Cel mai bun mod de a face analize de sensibilitate în excel este să folosiți tabele de date. Tabelele de date oferă o comandă rapidă pentru calcularea mai multor versiuni într-o singură operație și o modalitate de a vizualiza și compara rezultatele tuturor diferitelor variații împreună pe foaia de lucru. Mai jos sunt pașii pe care îi puteți urma pentru a implementa o analiză de sensibilitate unidimensională în excel.
Pasul 1 - Creați tabelul într-un format standard.
În prima coloană, aveți ipotezele de intrare. În exemplul nostru, intrările sunt rata de rentabilitate așteptată (ke). De asemenea, vă rugăm să rețineți că există un rând gol (colorat în albastru în acest exercițiu) sub titlul tabelului. Acest rând gol servește unui scop important pentru acest tabel de date unidimensional, pe care îl veți vedea la pasul 2.
Pasul 2 - Conectați intrarea și ieșirea de referință așa cum este prezentat instantaneul de mai jos.
Spațiul oferit de rândul gol este acum utilizat pentru a furniza intrarea (returnarea așteptată Ke) și formula de ieșire. De ce se face așa?
Vom folosi „Ce se întâmplă dacă analiză”, aceasta este o modalitate de a instrui excel că pentru Intrare (ke), formula corespunzătoare furnizată în partea dreaptă ar trebui folosită pentru a recalcula toate celelalte intrări.
Pasul 3 - Selectați instrumentul de analiză Ce se întâmplă pentru a efectua analiza de sensibilitate în Excel.
Este important de reținut că acest lucru este împărțit în doi pași.
- Selectați intervalul de masă începând din partea stângă, începând de la 10% până în colțul din dreapta jos al mesei.
- Faceți clic pe Date -> Ce se întâmplă dacă analiză -> Tabelele de date
Pasul 4 - Se deschide caseta de dialog Tabel de date.
Caseta de dialog caută două intrări - Intrare rând și Intrare coloană. Deoarece există doar o singură intrare Ke luată în considerare, vom furniza o singură intrare de coloană.
Pasul 5 - Legați intrarea coloanei
În cazul nostru, toate intrările sunt furnizate într-o coloană și, prin urmare, vom face legătura cu intrarea coloanei. Introducerea coloanei este legată de returnarea așteptată (Ke). Vă rugăm să rețineți că intrarea trebuie să fie legată de sursa originală și nu de cea care se află în interiorul tabelului 
Pasul 6 - Bucurați-vă de ieșire
# 2 - Analiza sensibilității tabelului de date cu două variabile în Excel
Tabelele de date sunt foarte utile pentru analiza sensibilității în excel, în special în cazul DCF. Odată stabilit un caz de bază, analiza DCF trebuie întotdeauna testată în diferite scenarii de sensibilitate. Testarea implică examinarea efectului incremental al diferitelor modificări ale ipotezelor (costul capitalului, ratele de creștere terminale, creșterea veniturilor mai mică, cerințe mai mari de capital etc.) asupra valorii juste a stocului.
Să luăm analiza sensibilității în excel cu un exemplu financiar al analizei fluxurilor de numerar reduse Alibaba.
Cu ipotezele de bază ale costului capitalului la 9% și rata de creștere constantă la 3% , am ajuns la o evaluare justă de 191,45 miliarde dolari.
Să presupunem acum că nu sunteți pe deplin de acord cu costul ipotezelor de capital sau cu ipotezele ratei de creștere pe care le-am luat în evaluarea IPO Alibaba. Poate doriți să modificați ipotezele și să accesați impactul asupra evaluărilor.
O modalitate este de a schimba manual ipotezele și de a verifica rezultatele fiecărei modificări. (cuvânt de cod - metoda măgarului!)
Cu toate acestea, suntem aici pentru a discuta despre o modalitate mult mai bună și eficientă de a calcula evaluarea utilizând analiza de sensibilitate în excel, care nu numai că economisește timp, ci ne oferă și o modalitate de a vizualiza toate detaliile de ieșire într-un format eficient.
Dacă efectuăm analiza What-if în excel într-un mod profesional pe datele de mai sus, atunci obținem următoarea ieșire.
- Aici, intrările de rând constau în modificări ale costului capitalului sau WACC (7% până la 11%)
- Intrările pe coloană constau în modificări ale ratelor de creștere (1% până la 6%)
- Punctul de intersecție este evaluarea Alibaba. De exemplu, utilizând cazul nostru de bază de 9% WACC și 3% rate de creștere, obținem evaluarea ca 191,45 miliarde dolari.
Cu acest fundal, să ne uităm acum la modul în care putem pregăti o astfel de analiză de sensibilitate în Excel folosind tabele de date bidimensionale.
Pasul 1 - Creați structura tabelului așa cum este prezentat mai jos
- Deoarece avem două seturi de ipoteze - Costul capitalului (WACC) și ratele de creștere (g), trebuie să pregătiți un tabel prezentat mai jos.
- Sunteți liber să schimbați intrările de rând și coloană. În loc de WACC, este posibil să aveți rate de creștere și invers.
Pasul 2 - Conectați punctul de intersecție la celula de ieșire.
Punctul de intersecție al celor două intrări trebuie utilizat pentru a lega ieșirea dorită. În acest caz, dorim să vedem efectul acestor două variabile (WACC și rata de creștere) asupra valorii capitalului propriu. Prin urmare, am legat celula care se intersectează cu ieșirea.
Pasul 3 - Deschideți tabelul de date bidimensional
- Selectați tabelul pe care l-ați creat
- Apoi faceți clic pe Date -> Ce se întâmplă dacă Analiză -> Tabelele de date
Pasul 4 - Furnizați intrările de rând și intrările de coloană.
- Intrarea pe rând este Costul capitalului sau Ke.
- Introducerea coloanei este rata de creștere.
- Vă rugăm să nu uitați să legați aceste intrări de la sursa de presupunere originală și nu de oriunde în interiorul tabelului.
Pasul 5 - Bucurați-vă de ieșire.
- Cele mai multe valori pesimiste ale producției se află în colțul din dreapta sus, unde Costul capitalului este de 11%, iar rata de creștere este de doar 1%
- Cea mai optimistă valoare IPO Alibaba este atunci când Ke este 7%, iar g este 6%
- Cazul de bază pe care l-am calculat pentru rate de creștere de 9% ke și 3% se află la mijloc.
- Această analiză de sensibilitate bidimensională din tabelul Excel oferă clienților o analiză ușoară a scenariilor, care economisește mult timp.
# 3 - Căutarea obiectivului pentru analiza sensibilității în Excel
- Comanda Căutare obiectiv este utilizată pentru a aduce o formulă la o anumită valoare
- Face acest lucru schimbând una dintre celulele la care face referire formula
- Goal Seek cere o referință de celulă care conține o formulă (celula Set). De asemenea, solicită o valoare, care este figura în care doriți ca celula să fie egală
- În cele din urmă, Goal Seek solicită modificarea unei celule pentru a duce celula Set la valoarea necesară
Să aruncăm o privire la DCF al evaluării IPO Alibaba.
După cum știm din DCF, că ratele de creștere și evaluarea sunt direct legate. Creșterea ratelor de creștere crește prețul acțiunii acțiunii.
Să presupunem că vrem să verificăm la ce rată de creștere va atinge prețul acțiunilor 80 USD?
Ca întotdeauna, putem face acest lucru manual modificând ratele de creștere pentru a vedea în continuare impactul asupra prețului acțiunilor. Acesta va fi din nou un proces obositor. Este posibil să trebuiască să introducem rate de creștere de multe ori pentru a ne asigura că prețul acțiunilor se potrivește cu 80 USD în cazul nostru.
Cu toate acestea, putem folosi o funcție precum Goal Seek în Excel pentru a rezolva acest lucru în pași simpli.
Pasul 1 - Faceți clic pe celula a cărei valoare doriți să o setați. (Celula Set trebuie să conțină o formulă)
Pasul 2 - Alegeți Instrumente, Căutare obiectiv din meniu și apare următoarea casetă de dialog:
- Comanda Căutare obiectiv sugerează automat celula activă ca celulă Set.
- Acest lucru poate fi suprapus cu o nouă referință de celulă sau puteți face clic pe celula corespunzătoare din foaia de calcul.
- Acum introduceți valoarea dorită pe care ar trebui să o atingă această formulă.
- Faceți clic în caseta „Pentru valoare” și introduceți valoarea pe care doriți să o egaleze formula selectată.
- În cele din urmă, faceți clic în caseta „Prin schimbarea celulei” și tastați sau faceți clic pe celula a cărei valoare poate fi modificată pentru a obține rezultatul dorit.
- Faceți clic pe butonul OK, iar foaia de calcul va modifica celula la o valoare suficientă pentru ca formula să vă atingă obiectivul.
Pasul 3 - Bucurați-vă de ieșire.
Goal Seek vă informează, de asemenea, că obiectivul a fost atins.
Concluzie
Analiza sensibilității în excel vă crește înțelegerea comportamentului financiar și operațional al companiei. După cum am învățat din cele trei abordări - Tabelele de date unidimensionale, Tabelele de date bidimensionale și Obiectivul căutării că analiza sensibilității este extrem de utilă în domeniul financiar, în special în contextul evaluărilor - DCF sau DDM.
Cu toate acestea, puteți obține o înțelegere la nivel macro a companiei și a industriei în general. Puteți dezvolta cazuri pentru a reflecta sensibilitatea evaluării la modificările ratelor dobânzii, recesiune, inflație, PIB etc. asupra evaluării. Gândirea și bunul simț ar trebui folosite în dezvoltarea unor cazuri de sensibilitate rezonabile și utile.
Ce urmează?
Dacă ați aflat ceva despre analiza sensibilității în Excel, vă rugăm să lăsați un comentariu mai jos. Spune-mi ce crezi. Multe mulțumiri și aveți grijă. Învățare fericită!
De asemenea, puteți consulta aceste articole de mai jos pentru a afla mai multe despre evaluări și finanțe corporative -
- Formula pentru sensibilitatea la preț
- Analiza riscurilor - Metode
- Analiză de echilibru Excel
- Analiza Excel Pareto
