Analýza citlivosti v Excelu (ukázka datového listu)

Procesy v oblasti financí jsou vždy propojeny – jeden faktor závisí na druhém a mění se s ním. Sledujte tyto změny a pochopte, co lze v budoucnu očekávat, například pomocí funkcí Excelu a tabulkových metod.

Získání více výsledků pomocí datové tabulky

Funkce datových listů jsou prvky analýzy typu what-if – často prováděné prostřednictvím aplikace Microsoft Excel. Toto je druhý název pro analýzu citlivosti.

O programu

Datová tabulka je typ rozsahu buněk, které lze použít k řešení problémů změnou hodnot v některých buňkách. Vytváří se, když je potřeba sledovat změny ve složkách vzorce a přijímat aktualizace výsledků podle těchto změn. Pojďme zjistit, jak používat datové tabulky ve výzkumu a jaké jsou typy.

Základy datových tabulek

Existují dva typy datových tabulek, liší se počtem komponent. Musíte sestavit tabulku se zaměřením na počet hodnot uXNUMXbuXNUMXb, které s ní potřebujete zkontrolovat.

Statistici používají tabulku s jednou proměnnou, pokud je v jednom nebo více výrazech pouze jedna proměnná, která může změnit jejich výsledek. Často se například používá ve spojení s funkcí PMT. Vzorec je určen pro výpočet výše pravidelné splátky a zohledňuje úrokovou sazbu uvedenou ve smlouvě. Při takových výpočtech se proměnné zapisují do jednoho sloupce a výsledky výpočtů do jiného. Příklad datového štítku s 1 proměnnou:

Dále zvažte desky se 2 proměnnými. Používají se v případech, kdy změnu libovolného ukazatele ovlivňují dva faktory. Tyto dvě proměnné mohou skončit v další tabulce spojené s úvěrem, pomocí které lze určit optimální dobu splácení a výši měsíční splátky. V tomto výpočtu je také potřeba použít funkci PMT. Příklad tabulky se 2 proměnnými:

Vytvoření datové tabulky s jednou proměnnou

Zvažte metodu analýzy na příkladu malého knihkupectví s pouze 100 knihami na skladě. Některé z nich lze prodat dráž (50 USD), zbytek bude stát kupující méně (20 USD). Počítá se celkový příjem z prodeje veškerého zboží – majitel se rozhodl, že 60 % knih prodá draze. Musíte zjistit, jak se zvýší tržby, když zdražíte větší objem zboží – 70 % a tak dále.

Dávej pozor! Celkový příjem je nutné vypočítat pomocí vzorce, jinak nebude možné sestavit datovou tabulku.

1. Vyberte volnou buňku daleko od okraje listu a napište do ní vzorec: =Buňka celkových příjmů. Pokud je například příjem zapsán v buňce C14 (je uvedeno náhodné označení), musíte napsat toto: =S14.
2. Procento objemu zboží zapíšeme do sloupce nalevo od této buňky – ne pod něj, to je velmi důležité.
3. Vybereme rozsah buněk, kde se nachází sloupec procent a odkaz na celkový příjem.

4. Na záložce „Data“ najdeme položku „Co když analýza“ a klikneme na ni – v nabídce, která se otevře, vybereme možnost „Tabulka dat“.

5. Otevře se malé okno, kde musíte ve sloupci „Nahradit hodnoty řádky v…“ zadat buňku s procentem knih původně prodaných za vysokou cenu. Tento krok se provádí za účelem přepočtu celkových příjmů s přihlédnutím k rostoucímu procentu.

Po kliknutí na tlačítko „OK“ v okně, kde byly zadány údaje pro sestavení tabulky, se v řádcích objeví výsledky výpočtů.

Přidání vzorce do tabulky dat jedné proměnné

Z tabulky, která pomohla vypočítat akci pouze s jednou proměnnou, můžete vytvořit sofistikovaný analytický nástroj přidáním dalšího vzorce. Musí se zadat vedle již existujícího vzorce – například pokud je tabulka řádkově orientovaná, zapíšeme výraz do buňky napravo od stávajícího. Když je nastavena orientace sloupce, zapíšeme nový vzorec pod starý. Dále postupujte podle algoritmu:

1. Znovu vyberte oblast buněk, ale nyní by měla obsahovat nový vzorec.
2. Otevřete nabídku analýzy „co kdyby“ a vyberte „Datový list“.
3. Do odpovídajícího pole přidáme nový vzorec v řádcích nebo sloupcích v závislosti na orientaci desky.

Vytvořte datovou tabulku se dvěma proměnnými

Začátek takové tabulky je mírně odlišný – nad procentuální hodnoty je potřeba umístit odkaz na celkový výnos. Dále provedeme tyto kroky:

1. Cenové varianty napište do jednoho řádku s odkazem na příjem – jedna buňka pro každou cenu.
2. Vyberte rozsah buněk.

3. Otevřete okno datové tabulky, jako při sestavování tabulky s jednou proměnnou – přes záložku „Data“ na nástrojové liště.
4. Ve sloupci „Nahradit hodnoty sloupci v …“ nahraďte buňku s počáteční vysokou cenou.
5. Přidejte buňku s počátečním procentem prodeje drahých knih do sloupce „Nahradit hodnoty řádky v…“ a klikněte na „OK“.

Tím se celá tabulka zaplní částkami možných příjmů s různými podmínkami prodeje zboží.

Zrychlete výpočty pro pracovní listy obsahující datové tabulky

Pokud potřebujete rychlé výpočty v datové tabulce, které nespouštějí přepočet celého sešitu, existuje několik věcí, které můžete udělat pro urychlení procesu.

1. Otevřete okno možností, v nabídce vpravo vyberte položku „Formulas“.
2. Vyberte položku „Automaticky, kromě datových tabulek“ v části „Výpočty v sešitu“.

3. Pojďme si výsledky v tabulce přepočítat ručně. Chcete-li to provést, vyberte vzorce a stiskněte klávesu F.

Další nástroje pro provádění analýzy citlivosti

V programu jsou další nástroje, které vám pomohou provést analýzu citlivosti. Automatizují některé akce, které by se jinak musely provádět ručně.

1. Funkce „Výběr parametrů“ je vhodná, pokud je znám požadovaný výsledek a pro získání takového výsledku potřebujete znát vstupní hodnotu proměnné..
2. „Hledat řešení“ je doplněk pro řešení problémů. Je potřeba nastavit limity a ukázat na ně, načež systém najde odpověď. Řešení se určí změnou hodnot.
3. Analýzu citlivosti lze provést pomocí Správce scénářů. Tento nástroj se nachází v nabídce what-if analysis na kartě Data. Dosazuje hodnoty v několika buňkách – počet může dosáhnout 32. Dispečer tyto hodnoty porovnává, aby je uživatel nemusel ručně měnit. Příklad použití správce skriptů:

Citlivostní analýza investičního projektu v Excelu

Analýza what-if je zvláště užitečná v situacích, kdy je vyžadováno předpovídání, jako jsou investice. Analytici pomocí této metody zjišťují, jak se změní hodnota akcií společnosti v důsledku změn některých faktorů.

Metoda analýzy citlivosti investic

Při analýze „co kdyby“ použijte výčet – ruční nebo automatický. Rozsah hodnot je znám a do vzorce se dosazují jedna po druhé. Výsledkem je soubor hodnot. Vyberte si z nich příslušné číslo. Podívejme se na čtyři ukazatele, pro které se v oblasti financí provádí analýza citlivosti:

1. Čistá současná hodnota – Vypočítá se odečtením výše investice od výše příjmu.
2. Vnitřní míra návratnosti / zisk – udává, kolik zisku je potřeba získat z investice za rok.
3. Poměr návratnosti je poměr všech zisků k počáteční investici.
4. Index diskontovaného zisku – udává efektivitu investice.

Vzorec

Citlivost vkládání lze vypočítat pomocí tohoto vzorce: Změna výstupního parametru v % / Změna vstupního parametru v %.

Výstupní a vstupní parametry mohou být hodnoty popsané výše.

1. Musíte znát výsledek za standardních podmínek.
2. Nahradíme jednu z proměnných a sledujeme změny ve výsledku.
3. Vypočítáme procentuální změnu obou parametrů vzhledem ke stanoveným podmínkám.
4. Získaná procenta vložíme do vzorce a určíme citlivost.

Příklad citlivostní analýzy investičního projektu v Excelu

Pro lepší pochopení metodologie analýzy je zapotřebí příklad. Pojďme analyzovat projekt s následujícími známými údaji:

1. Vyplňte tabulku a analyzujte na ní projekt.

2. Cash flow vypočítáme pomocí funkce OFFSET. V počáteční fázi se tok rovná investicím. Dále použijeme vzorec: =IF(POSUN(Číslo,1;)=2;SUM(Přítok 1:Odtok 1); SUM(Přítok 1:Odtok 1)+$B$ 5)
   Označení buněk ve vzorci se mohou lišit v závislosti na rozložení tabulky. Na závěr se přičte hodnota z výchozích údajů – záchrana.

3. Určujeme dobu, za kterou se projekt vyplatí. Pro počáteční období používáme tento vzorec: =SUMMESLY(G7: G17;»<0″). Rozsah buněk je sloupec peněžního toku. Pro další období použijeme tento vzorec: =Počáteční období+IF(První e.stream>0; První e.tok;0). Projekt je na hranici rentability za 4 roky.

4. Vytvoříme sloupec pro čísla těch období, kdy se projekt vyplatí.

5. Počítáme návratnost investice. Je nutné vytvořit výraz, kde se zisk v určitém časovém období vydělí počáteční investicí.

6. Diskontní faktor určíme pomocí tohoto vzorce: =1/(1+Disk.%) ^Číslo.

7. Současnou hodnotu vypočítáme pomocí násobení – peněžní tok se vynásobí diskontním faktorem.

8. Spočítejme si PI (index ziskovosti). Současná hodnota v čase se vydělí investicí na začátku projektu.

9. Definujme vnitřní míru návratnosti pomocí funkce IRR: =IRR(Rozsah peněžních toků).

Analýza citlivosti investic pomocí datového listu

Pro analýzu projektů v oblasti investic jsou vhodnější jiné metody než datová tabulka. Mnoho uživatelů zažívá zmatek při sestavování vzorce. Chcete-li zjistit závislost jednoho faktoru na změnách ostatních, musíte vybrat správné buňky pro zadávání výpočtů a pro čtení dat.

Faktorová a disperzní analýza v Excelu s automatizací výpočtů

Další typologií citlivostní analýzy je faktorová analýza a analýza rozptylu. První typ definuje vztah mezi čísly, druhý odhaluje závislost jedné proměnné na ostatních.

ANOVA v Excelu

Účelem takové analýzy je rozdělit variabilitu hodnoty do tří složek:

1. Variabilita v důsledku vlivu jiných hodnot.
2. Změny v důsledku vztahu hodnot, které jej ovlivňují.
3. Náhodné změny.

Pojďme provést analýzu rozptylu pomocí doplňku Excelu „Analýza dat“. Pokud není povoleno, lze jej povolit v nastavení.

Počáteční tabulka se musí řídit dvěma pravidly: pro každou hodnotu je jeden sloupec a data v ní jsou uspořádána vzestupně nebo sestupně. Je třeba prověřit vliv úrovně vzdělání na chování v konfliktu.

1. Najděte nástroj Analýza dat na kartě Data a otevřete jeho okno. V seznamu musíte vybrat jednosměrnou analýzu rozptylu.

2. Vyplňte řádky dialogového okna. Vstupním intervalem jsou všechny buňky, kromě záhlaví a čísel. Seskupit podle sloupců. Výsledky zobrazíme na novém listu.

Vzhledem k tomu, že hodnota ve žluté buňce je větší než jedna, lze předpoklad považovat za nesprávný – mezi výchovou a chováním v konfliktu není žádný vztah.

Faktorová analýza v Excelu: příklad

Pojďme analyzovat vztah dat v oblasti prodeje – je potřeba identifikovat oblíbené a neoblíbené produkty. Prvotní informace:

1. Musíme zjistit, po jakém zboží vzrostla poptávka během druhého měsíce nejvíce. Sestavujeme novou tabulku pro zjištění růstu a poklesu poptávky. Růst se vypočítá pomocí tohoto vzorce: =IF((Poptávka 2-Poptávka 1)>0; Poptávka 2-Poptávka 1;0). Vzorec snížení: =IF(Růst=0; Poptávka 1 - Poptávka 2;0).

2. Vypočítejte růst poptávky po zboží v procentech: =IF(Růst/Výsledek 2 =0; Snížení/Výsledek 2; Růst/Výsledek 2).

3. Udělejme si pro přehlednost graf – vyberte rozsah buněk a vytvořte histogram přes záložku „Vložit“. V nastavení musíte odstranit výplň, to lze provést pomocí nástroje Formát datové řady.

Obousměrná analýza rozptylu v Excelu

Analýza rozptylu se provádí s několika proměnnými. Zvažte to na příkladu: musíte zjistit, jak rychle se reakce na zvuk různé hlasitosti projevuje u mužů a žen.

1. Otevřeme „Analýzu dat“, v seznamu musíte najít obousměrnou analýzu rozptylu bez opakování.
2. Interval vstupu – buňky, které obsahují data (bez hlavičky). Výsledky zobrazíme na novém listu a klikneme na „OK“.

Hodnota F je větší než F-kritická, což znamená, že podlaha ovlivňuje rychlost reakce na zvuk.

Proč investovat do čističky vzduchu?

V tomto článku byla podrobně probrána analýza citlivosti v tabulce Excel, takže každý uživatel bude schopen porozumět metodám její aplikace.