Analýza citlivosti v programe Excel nám pomáha študovať neistotu výstupu modelu so zmenami vo vstupných premenných. Vykonáva predovšetkým záťažové testovanie našich modelovaných predpokladov a vedie k poznatkom o pridanej hodnote.
V súvislosti s oceňovaním DCF je Analýza citlivosti v programe Excel obzvlášť užitočná vo financiách na modelovanie ceny akcií alebo citlivosti ocenenia na predpoklady, ako sú miera rastu alebo náklady na kapitál.
V tomto článku sa profesionálne pozrieme na nasledujúcu analýzu citlivosti v programe Excel pre model DCF.
- Jedna tabuľka premenných údajov
- Tabuľka s dvoma premennými údajmi
- Hľadanie gólu
Najdôležitejšie - Stiahnite si analýzu citlivosti v šablóne programu Excel
Naučte sa jednu premennú a dve premenné DATA TABLE Typy v programe Excel
Analýza citlivosti v programe Excel
# 1 - Analýza citlivosti tabuľky s premennými údajmi v programe Excel
Zoberme si nižšie uvedený príklad Financie (dividendový zľavový model), aby sme tomuto podrobne porozumeli.
Neustály rast DDM nám dáva reálnu hodnotu akcie ako súčasnú hodnotu nekonečného toku dividend rastúcich konštantnou rýchlosťou.
Gordonov vzorec rastu je uvedený nižšie -
Kde:
- D1 = hodnota dividendy, ktorá sa má vyplatiť budúci rok
- D0 = hodnota dividendy prijatej v tomto roku
- g = miera rastu dividendy
- Ke = diskontná sadzba
Teraz predpokladajme, že chceme pochopiť, aká je cena akcií citlivá na očakávaný výnos (ke). Existujú dva spôsoby, ako to urobiť -
- Somársky spôsob :-)
- Čo keď Analýza
# 1 - Donkey Way
Analýza citlivosti v programe Excel pomocou Donkeyho spôsobu je veľmi jednoduchá, ale je ťažké ju implementovať, ak je zapojených veľa premenných.
Chcete pokračovať v vytváraní týchto 1000 predpokladov? Očividne nie!
Naučte sa nasledujúcu analýzu citlivosti v technike programu Excel, aby ste sa zachránili pred problémami.
# 2 - Použitie jednej tabuľky premenných údajov
Najlepším spôsobom, ako vykonať analýzu citlivosti v programe Excel, je použiť dátové tabuľky. Tabuľky s údajmi poskytujú skratku na výpočet viacerých verzií v jednej operácii a spôsob, ako zobraziť a porovnať výsledky všetkých rôznych variácií v pracovnom hárku. Ďalej sú uvedené kroky, ktoré môžete vykonať pri implementácii jednorozmernej analýzy citlivosti v programe Excel.
Krok 1 - Vytvorte tabuľku v štandardnom formáte.
V prvom stĺpci máte vstupné predpoklady. V našom príklade sú vstupmi očakávaná miera návratnosti (ke). Upozorňujeme tiež, že pod nadpisom tabuľky je prázdny riadok (v tomto cvičení sfarbený do modra). Tento prázdny riadok slúži dôležitému účelu tejto tabuľky jednorozmerných údajov, ktorú uvidíte v kroku 2.
Krok 2 - Prepojte referenčný vstup a výstup podľa obrázka uvedeného nižšie.
Priestor poskytnutý prázdnym riadkom sa teraz používa na poskytnutie vstupu (očakávaný návrat Ke) a výstupného vzorca. Prečo sa to robí takto?
Použijeme „Čo ak analýza“, to je spôsob, ako inštruovať program Excel, že pre vstup (ke) by sa mal na prepočet všetkých ostatných vstupov použiť zodpovedajúci vzorec uvedený na pravej strane.
Krok 3 - Vyberte nástroj What-if Analysis a vykonajte analýzu citlivosti v programe Excel.
Je dôležité poznamenať, že je to rozdelené do dvoch krokov.
- Vyberte rozsah tabuľky od ľavej strany, od 10% do pravého dolného rohu tabuľky.
- Kliknite na Údaje -> Čo ak analýza -> Tabuľky údajov
Krok 4 - Otvorí sa dialógové okno Tabuľka údajov.
Dialógové okno vyhľadáva dva vstupy - vstup riadkov a vstup stĺpcov. Pretože sa uvažuje iba s jedným vstupom Ke, poskytneme vstup pre jeden stĺpec.
Krok 5 - Prepojte vstup do stĺpca
V našom prípade sú všetky vstupy poskytované v stĺpci, a preto odkazujeme na vstup do stĺpca. Vstup do stĺpca je prepojený s očakávaným výnosom (Ke). Upozorňujeme, že vstup by mal byť prepojený z pôvodného zdroja a nie z toho, ktorý je v tabuľke 
Krok 6 - Užite si výstup
# 2 - Analýza citlivosti tabuľky s dvoma premennými údajmi v programe Excel
Dátové tabuľky sú veľmi užitočné pre analýzu citlivosti v exceli, najmä v prípade DCF. Po zistení základného prípadu by sa mala DCF analýza vždy testovať podľa rôznych scenárov citlivosti. Testovanie zahŕňa preskúmanie prírastkového účinku rôznych zmien v predpokladoch (náklady na kapitál, terminálne miery rastu, nižší rast výnosov, vyššie kapitálové požiadavky atď.) Na reálnu hodnotu akcií.
Vezmime si analýzu citlivosti v exceli s finančným príkladom analýzy diskontovaných peňažných tokov Alibaba.
Pri základných predpokladoch nákladov na kapitál ako 9% a konštantnom tempe rastu na úrovni 3% sme dospeli k spravodlivej hodnote 191,45 miliárd dolárov.
Predpokladajme, že teraz úplne nesúhlasíte s predpokladmi nákladov na kapitál alebo predpokladmi rýchlosti rastu, ktoré som vzal do úvahy pri hodnotení IPO v Alibabe. Možno budete chcieť zmeniť predpoklady a získať prístup k vplyvu na ocenenia.
Jedným zo spôsobov je manuálna zmena predpokladov a kontrola výsledkov každej zmeny. (kódové slovo - metóda somára!)
Sme tu však, aby sme diskutovali o oveľa lepšom a efektívnejšom spôsobe výpočtu ocenenia pomocou analýzy citlivosti v programe Excel, ktorý nielen šetrí čas, ale poskytuje nám aj spôsob, ako vizualizovať všetky výstupné podrobnosti v efektívnom formáte.
Ak vykonáme analýzu What-if v exceli profesionálnym spôsobom na vyššie uvedených dátach, dostaneme nasledujúci výstup.
- Riadkové vstupy tu pozostávajú zo zmien v nákladoch na kapitál alebo WACC (7% až 11%)
- Vstupy do stĺpcov pozostávajú zo zmien rýchlostí rastu (1% až 6%)
- Priesečníkom je Alibaba Valuation. Napríklad, ak použijeme náš základný prípad 9% WACC a 3% mieru rastu, dostaneme ocenenie ako 191,45 miliárd dolárov.
Na tomto pozadí sa teraz pozrime, ako môžeme pripraviť takúto analýzu citlivosti v programe Excel pomocou dvojrozmerných údajových tabuliek.
Krok 1 - Vytvorte štruktúru tabuľky, ako je uvedené nižšie
- Pretože máme dve sady predpokladov - náklady na kapitál (WACC) a miery rastu (g), musíte si pripraviť nasledujúcu tabuľku.
- Môžete voľne prepínať vstupy do riadkov a stĺpcov. Namiesto WACC môžete mať rýchlosť rastu a naopak.
Krok 2 - Prepojte priesečník s výstupnou bunkou.
Priesečník dvoch vstupov by sa mal použiť na prepojenie požadovaného výstupu. V tomto prípade chceme vidieť vplyv týchto dvoch premenných (WACC a rýchlosť rastu) na hodnotu vlastného imania. Preto sme prepojili pretínajúcu sa bunku s výstupom.
Krok 3 - Otvorte tabuľku dvojrozmerných údajov
- Vyberte tabuľku, ktorú ste vytvorili
- Potom kliknite na Údaje -> Čo ak analýza -> Dátové tabuľky
Krok 4 - Zadajte vstupy do riadkov a do stĺpcov.
- Vstupom do riadku sú náklady na kapitál alebo Ke.
- Vstupom do stĺpca je rýchlosť rastu.
- Nezabudnite prepojiť tieto vstupy z pôvodného zdroja predpokladov, a nie z ktoréhokoľvek miesta v tabuľke.
Krok 5 - Užite si výstup.
- Najpesimistickejšie výstupné hodnoty sú v pravom hornom rohu, kde sú náklady na kapitál 11% a miera rastu iba 1%.
- Najoptimistickejšia hodnota IPlib Alibaba je, keď Ke je 7% ag je 6%
- Základný prípad, ktorý sme vypočítali pre 9% ke a 3% mieru rastu, leží uprostred.
- Táto dvojrozmerná analýza citlivosti v tabuľke programu Excel poskytuje klientom ľahkú analýzu scenárov, ktorá šetrí veľa času.
# 3 - Hľadanie cieľa pre analýzu citlivosti v programe Excel
- Príkaz Hľadať cieľ sa používa na uvedenie jedného vzorca do konkrétnej hodnoty
- Robí to zmenou jednej z buniek, na ktoré odkazuje vzorec
- Cieľové hľadanie žiada o odkaz na bunku, ktorá obsahuje vzorec (bunka Nastaviť). Tiež žiada o hodnotu, čo je údaj, ktorý chcete, aby sa bunka rovnala
- Nakoniec Goal Seek požiada o zmenu bunky, aby sa bunka Set dostala na požadovanú hodnotu
Pozrime sa na DCF Alibaba IPO Valuation.
Ako vieme z DCF, tempo rastu a ocenenie priamo súvisia. Zvyšujúca sa miera rastu zvyšuje cenu akcií akcií.
Predpokladajme, že si chceme skontrolovať, akou rýchlosťou rastu sa cena akcií dotkne 80 dolárov?
Ako vždy, môžeme to urobiť manuálne zmenou mier rastu, aby sme naďalej videli vplyv na cenu akcií. Bude to opäť zdĺhavý proces. Možno budeme musieť zadávať miery rastu mnohokrát, aby sme sa uistili, že cena akcií sa v našom prípade zhoduje s 80 USD.
Na vyriešenie tohto problému v jednoduchých krokoch však môžeme použiť funkciu ako Goal Seek in Excel.
Krok 1 - Kliknite na bunku, ktorej hodnotu chcete nastaviť. (Bunka Nastaviť musí obsahovať vzorec)
Krok 2 - Vyberte Nástroje, Vyhľadať cieľ z ponuky a zobrazí sa nasledujúce dialógové okno:
- Príkaz Goal Seek automaticky navrhne aktívnu bunku ako bunku Set.
- Toto je možné prepísať novým odkazom na bunku, alebo môžete kliknúť na príslušnú bunku v tabuľke.
- Teraz zadajte požadovanú hodnotu, ktorú by tento vzorec mal dosiahnuť.
- Kliknite dovnútra poľa „Na hodnotu“ a zadajte hodnotu, ktorej sa chcete rovnať vo vybranom vzorci.
- Nakoniec kliknite do poľa „Zmenou bunky“ a zadajte alebo kliknite na bunku, ktorej hodnotu je možné zmeniť, aby ste dosiahli požadovaný výsledok.
- Kliknite na tlačidlo OK a tabuľka zmení bunku na hodnotu dostatočnú na to, aby vzorec dosiahol váš cieľ.
Krok 3 - Užite si výstup.
Goal Seek vás tiež informuje, že cieľ bol dosiahnutý.
Záver
Analýza citlivosti v programe Excel zvyšuje vaše pochopenie finančného a prevádzkového správania podniku. Ako sme sa dozvedeli z troch prístupov - tabuľky jednorozmerných údajov, tabuľky dvojrozmerných údajov a cieľové hľadanie, analýza citlivosti je mimoriadne užitočná v oblasti financií, najmä v súvislosti s oceňovaním - DCF alebo DDM.
Môžete však tiež získať prehľad o spoločnosti a priemysle všeobecne na makroúrovni. Môžete vytvoriť prípady, ktoré pri oceňovaní odrážajú citlivosť ocenenia na zmeny úrokových sadzieb, recesie, inflácie, HDP atď. Pri vývoji rozumných a užitočných prípadov citlivosti by sa mal používať rozum a zdravý rozum.
Čo ďalej?
Ak ste sa dozvedeli niečo o analýze citlivosti v programe Excel, zanechajte prosím komentár nižšie. Daj mi vedieť čo si myslíš. Veľká vďaka a buďte opatrní. Šťastné učenie!
Môžete sa tiež pozrieť na tieto články, kde sa dozviete viac o oceneniach a podnikových financiách -
- Vzorec pre cenovú citlivosť
- Analýza rizika - metódy
- Analýza rovnovážneho bodu programu Excel
- Excel Paretova analýza
