Analýza citlivosti v aplikaci Excel | Tabulka proměnných dat jedna a dvě

Analýza citlivosti v aplikaci Excel nám pomáhá studovat nejistotu výstupu modelu se změnami vstupních proměnných. Primárně provádí zátěžové testování našich modelovaných předpokladů a vede k pohledům s přidanou hodnotou.

V souvislosti s oceňováním DCF je Analýza citlivosti v aplikaci Excel obzvláště užitečná ve financích pro modelování ceny akcií nebo citlivosti ocenění na předpoklady, jako jsou míry růstu nebo náklady na kapitál.

V tomto článku se profesionálně podíváme na následující analýzu citlivosti v aplikaci Excel pro DCF modelování.

    Nejdůležitější - Stáhněte si analýzu citlivosti v šabloně aplikace Excel

    Naučte se jednu proměnnou a dvě proměnné DATA TABLE Typy v aplikaci Excel

    Analýza citlivosti v aplikaci Excel

    # 1 - Analýza citlivosti tabulky s jednou proměnnou dat v aplikaci Excel

    Vezměme si níže příklad Finance (model slev z dividend), abychom tomuto detailu porozuměli podrobně.

    Konstantní růst DDM nám dává reálnou hodnotu akcie jako současnou hodnotu nekonečného toku dividend, které rostou konstantní rychlostí.

    Gordonův vzorec růstu je uveden níže -

    Kde:

    • D1 = hodnota dividendy, která má být přijata příští rok
    • D0 = hodnota dividendy přijatá v tomto roce
    • g = tempo růstu dividendy
    • Ke = diskontní sazba

    Nyní předpokládejme, že chceme pochopit, jak citlivá je cena akcií s ohledem na očekávaný výnos (ke). Existují dva způsoby, jak toho dosáhnout -

    • Oslí cesta :-)
    • Co když analýza

    # 1 - Donkey Way

    Analýza citlivosti v aplikaci Excel pomocí Donkeyho způsobu je velmi přímočará, ale je obtížné ji implementovat, pokud je zahrnuto mnoho proměnných.

    Chcete v tom pokračovat s ohledem na 1000 předpokladů? Je zřejmé, že ne!

    Naučte se následující analýzu citlivosti v aplikaci Excel, abyste se zachránili před potížemi.

    # 2 - Použití jedné tabulky proměnných dat

    Nejlepší způsob, jak provést analýzu citlivosti v aplikaci Excel, je použít datové tabulky. Datové tabulky poskytují zkratku pro výpočet více verzí v jedné operaci a způsob, jak zobrazit a porovnat výsledky všech různých variant společně v listu. Níže jsou kroky, které můžete provést při implementaci jednorozměrné analýzy citlivosti v aplikaci Excel.

    Krok 1 - Vytvořte tabulku ve standardním formátu

    V prvním sloupci máte vstupní předpoklady. V našem příkladu jsou vstupy očekávaná míra návratnosti (ke). Pamatujte také, že pod nadpisem tabulky je prázdný řádek (v tomto cvičení zbarvený modře). Tento prázdný řádek slouží důležitému účelu této tabulky jednorozměrných dat, kterou uvidíte v kroku 2.

    Krok 2 - Propojte referenční vstup a výstup podle níže uvedeného snímku.

    Prostor poskytnutý prázdným řádkem se nyní používá k zadání vstupu (očekávaný návrat Ke) a výstupního vzorce. Proč se to dělá takhle?

    Použijeme „What if Analysis“, to je způsob, jak instruovat Excel, že pro Input (ke) by měl být použit odpovídající vzorec uvedený na pravé straně k přepočítání všech ostatních vstupů.

    Krok 3 - Vyberte nástroj What-if Analysis k provedení analýzy citlivosti v aplikaci Excel

    Je důležité si uvědomit, že je to rozděleno do dvou kroků

    • Vyberte rozsah tabulky počínaje od levé strany počínaje od 10% do pravého dolního rohu tabulky.
    • Klikněte na Data -> Co když analýza -> Datové tabulky

    Krok 4 - Otevře se dialogové okno Tabulka dat.

    Dialogové okno vyhledává dva vstupy - řádek a sloupec. Vzhledem k tomu, že je zvažován pouze jeden vstup Ke, poskytneme vstup pro jeden sloupec.

    Krok 5 - Propojte vstup sloupce

    V našem případě je veškerý vstup poskytován ve sloupci, a proto odkazujeme na vstup do sloupce. Vstup do sloupce je spojen s očekávaným výnosem (Ke). Vezměte prosím na vědomí, že vstup by měl být propojen z původního zdroje a nikoli z toho, který je uvnitř tabulky

    Krok 6 - Užijte si výstup

    # 2 - Analýza citlivosti tabulky se dvěma proměnnými daty v aplikaci Excel

    Datové tabulky jsou velmi užitečné pro analýzu citlivosti v Excelu, zejména v případě DCF. Jakmile je stanoven základní případ, měla by být analýza DCF vždy testována v různých scénářích citlivosti. Testování zahrnuje zkoumání přírůstkového účinku různých změn v předpokladech (náklady na kapitál, terminální míry růstu, nižší růst výnosů, vyšší kapitálové požadavky atd.) Na reálnou hodnotu akcií.

    Vezměme si analýzu citlivosti v aplikaci Excel s finančním příkladem analýzy diskontovaných peněžních toků Alibaba.

    Se základními předpoklady o nákladech na kapitál 9% a konstantním tempem růstu 3% jsme dospěli ke spravedlivé hodnotě 191,45 miliardy USD.

    Předpokládejme nyní, že plně nesouhlasíte s předpoklady o nákladech na kapitál nebo s předpoklady o rychlosti růstu, které jsem použil při ocenění IPO v Alibaba. Možná budete chtít změnit předpoklady a získat přístup k dopadu na ocenění.

    Jedním ze způsobů je ruční změna předpokladů a kontrola výsledků každé změny. (kódové slovo - oslí metoda!)

    Jsme tu však, abychom diskutovali o mnohem lepším a efektivnějším způsobu výpočtu ocenění pomocí analýzy citlivosti v aplikaci Excel, který nejen šetří čas, ale také nám poskytuje způsob, jak vizualizovat všechny výstupní podrobnosti v efektivním formátu.

    Pokud provedeme analýzu What-if v excelu profesionálním způsobem na výše uvedených datech, získáme následující výstup.

    • Zde se řádkové vstupy skládají ze změn v nákladech na kapitál nebo WACC (7% až 11%)
    • Vstupy do sloupců spočívají ve změnách temp růstu (1% až 6%)
    • Průsečík je Alibaba Valuation. Např. pomocí našeho základního případu 9% WACC a 3% tempa růstu dostaneme ocenění jako 191,45 miliardy $.

    Na tomto pozadí se nyní podívejme, jak můžeme připravit takovou analýzu citlivosti v aplikaci Excel pomocí dvourozměrných datových tabulek.

    Krok 1 - Vytvořte strukturu tabulky, jak je uvedeno níže
    • Protože máme dvě sady předpokladů - náklady na kapitál (WACC) a míry růstu (g), musíte si připravit níže uvedenou tabulku.
    • Můžete přepínat vstupy do řádků a sloupců. Místo WACC můžete mít rychlost růstu a naopak.

    Krok 2 - Propojte průsečík s výstupní buňkou.

    Průsečík těchto dvou vstupů by měl být použit k propojení požadovaného výstupu. V tomto případě chceme vidět vliv těchto dvou proměnných (WACC a rychlost růstu) na hodnotu vlastního kapitálu. Proto jsme propojili protínající se buňku s výstupem.

    Krok 3 - Otevřete tabulku dvourozměrných dat
    • Vyberte tabulku, kterou jste vytvořili
    • Poté klikněte na Data -> Co když Analýza -> Datové tabulky
    Krok 4 - Zadejte řádkové vstupy a sloupcové vstupy.
    • Zadáním řádku jsou náklady na kapitál nebo Ke.
    • Vstupem do sloupce je rychlost růstu.
    • Nezapomeňte tyto vstupy propojit z původního zdroje předpokladů, nikoli z kteréhokoli místa v tabulce

    Krok 5 - Užijte si výstup.
    • Většina pesimistických výstupních hodnot leží v pravém horním rohu, kde jsou náklady na kapitál 11% a míra růstu pouze 1%
    • Nejoptimističtější hodnota IPlib Alibaba je, když Ke je 7% ag je 6%
    • Základní případ, který jsme vypočítali pro 9% ke a 3% tempa růstu, leží uprostřed.
    • Tato dvourozměrná analýza citlivosti v tabulce aplikace Excel poskytuje klientům snadnou analýzu scénářů, která šetří spoustu času.

    # 3 - Hledání cíle pro analýzu citlivosti v aplikaci Excel

    • Příkaz Hledat cíl se používá k převedení jednoho vzorce na konkrétní hodnotu
    • Dělá to změnou jedné z buněk, na které odkazuje vzorec
    • Goal Seek požádá o odkaz na buňku, která obsahuje vzorec (buňka Set). Požádá také o hodnotu, což je číslo, které chcete, aby se buňka rovnala
    • Nakonec Goal Seek požádá o změnu buňky, aby se buňka Set dostala na požadovanou hodnotu

    Pojďme se podívat na DCF Alibaba IPO Valuation.

    Jak víme z DCF, že tempo růstu a ocenění přímo souvisejí. Zvyšující se tempo růstu zvyšuje cenu akcií akcií.

    Předpokládejme, že chceme zjistit, jakou rychlostí růstu se cena akcií dotkne 80 $?

    Jako vždy to můžeme udělat ručně změnou tempa růstu, abychom nadále viděli dopad na cenu akcií. Bude to opět zdlouhavý proces, možná budeme muset mnohokrát zadávat míry růstu, abychom zajistili, že se cena akcií v našem případě shoduje s 80 USD.

    K vyřešení tohoto problému v jednoduchých krocích však můžeme použít funkci jako Goal Seek in Excel.

    Krok 1 - Klikněte na buňku, jejíž hodnotu chcete nastavit. (Buňka Set musí obsahovat vzorec)

    Krok 2 - Vyberte Nástroje, Vyhledat cíl z nabídky a zobrazí se následující dialogové okno:
    • Příkaz Goal Seek automaticky navrhne aktivní buňku jako buňku Set.
    • To lze přepsat novým odkazem na buňku nebo můžete kliknout na příslušnou buňku v tabulce.
    • Nyní zadejte požadovanou hodnotu, kterou by tento vzorec měl dosáhnout.
    • Klikněte do pole „Na hodnotu“ a zadejte hodnotu, kterou chcete, aby se vámi vybraný vzorec rovnal
    • Nakonec klikněte do pole „Změnou buňky“ a zadejte nebo klikněte na buňku, jejíž hodnotu lze změnit, abyste dosáhli požadovaného výsledku
    • Klikněte na tlačítko OK a tabulka změní buňku na hodnotu dostatečnou k tomu, aby vzorec dosáhl vašeho cíle.

    Krok 3 - Užijte si výstup.

    Goal Seek vás také informuje, že cíle bylo dosaženo

    Závěr

    Analýza citlivosti v aplikaci Excel zvyšuje vaše chápání finančního a provozního chování podniku. Jak jsme se naučili ze tří přístupů - One Dimensional Data Tables, Two Dimensional Data Tables, a Goal Seek, že analýza citlivosti je v oblasti financí nesmírně užitečná, zejména v kontextu ocenění - DCF nebo DDM.

    Můžete však také získat přehled o společnosti a průmyslu obecně na makroúrovni. Můžete vyvinout případy odrážející citlivost ocenění na změny úrokových sazeb, recese, inflace, HDP atd. Na ocenění. Při vývoji rozumných a užitečných případů citlivosti je třeba použít myšlení a zdravý rozum.

    Co dál?

    Pokud jste se dozvěděli něco o analýze citlivosti v aplikaci Excel, zanechte prosím komentář níže. Dej mi vědět, co si myslíš. Mnohokrát děkuji a dávejte pozor. Šťastné učení!

    Níže se můžete také podívat na tyto články, kde se dozvíte více o ocenění a podnikových financích -

    • Vzorec pro cenovou citlivost
    • Analýza rizik - metody
    • Analýza zvratu aplikace Excel
    • Excel Paretova analýza
    • <