Manažerská informatika
Domů  |  3MA112  |  3MA191  |  3MA381  |  3MA382  |  3MA481   
Osnova cvičení Úvodní informace Podklady ke cvičením Semestrální práce Samostatné aktivity Hodnocení Odkazy

Nástroje Excelu pro citlivostní analýzu

Zpracovala Andrea Marešová

Cílem této práce je seznámení se třemi hlavními nástroji Excelu používanými k analýze citlivosti (Tabulka dat, Hledání řešení, Scénáře) a vyzkoušet jejich aplikaci na příkladech. V první části je vymezena teorie týkající se citlivostní analýzy. Dále je vysvětleno, k čemu konkrétní nástroj slouží, jaké se používají vstupy, jaké jsou výstupy. V druhé části jsou zadány příklady a podrobný popis postupu jejich řešení. Pro každý nástroj jsou uvedeny dva příklady, které na sebe mnohdy navazují. Všechny příklady se týkají spoření a vycházejí z podobných příkladů, na které lze narazit při studiu literatury k tomuto tématu.

Teoretický základ

Citlivostní analýza

Citlivostní analýza ikonaPomocí citlivostní analýzy se zjišťuje, jak se změna na vstupu projeví na výsledku, výstupu ze vzorce. Je to tedy proces změny hodnot v buňkách s cílem zjistit, jak tyto změny ovlivní výstup vzorců na listu. Excel nabízí tři hlavní druhy nástrojů pro citlivostní analýzu zobrazené na kartě Data - Datové nástroje - Analýza hypotéz:- Scénáře, Tabulka dat a Hledání řešení. Realizaci citlivostní analýzy umožňují i další doplňky, které lze přidat do panelu nástrojů na kartu Data, např. doplněk Řešitel. Doplněk Řešitel je podobný funkci Hledání řešení, ale lze v něm pracovat s více proměnnými. Pro tvorbu pokročilejších modelů se dá použít doplněk Analytická sada. [1]

Tabulka dat

Tabulka dat shrnuje vliv jedné nebo dvou proměnných na vzorce, které je používají [2]. Existují dva typy datových tabulek - tabulka dat s jednou proměnnou a tabulka dat se dvěma proměnnými. Tabulku dat nelze použít pro více než dvě proměnné (jedna vstupní buňka je vždy na řádku, druhá ve sloupci), ale lze ji aplikovat na libovolný počet různých hodnot proměnných. Pro analýzu více než dvou proměnných se využívají scénáře, které mohou obsahovat maximálně 32 proměnných. Těchto scénářů se dá vytvořit libovolné množství.

Tabulka dat s jednou proměnnou

Pomocí analýzy založené na jedné vstupní proměnné se zjišťuje, jak se změna této proměnné projeví ve výsledných hodnotách jednoho vzorce nebo několika vzorců (či funkcí) [3]. Na vstupu je tabulka dat, která na prvním řádku (v záhlaví) obsahuje vzorec (vzorce, funkci, funkce) a v prvním sloupci před vzorci obsahuje množinu vstupních hodnot. Ve vzorci musí být uvedena substituční buňka, která odkazuje na libovolnou hodnotu prvního sloupce tabulky dat (na proměnnou).

Substituční buňka může být i prázdná, důležité je, že se nachází mimo tabulku dat. Substituční buňka pak vstupuje do dialogového okna Tabulka dat do políčka Vstupní buňka sloupce. Po zadání příkazu Tabulka dat na kartě Data - Datové nástroje - Analýza hypotéz a vyplnění příslušných políček, Excel do výsledkové oblasti doplní složený vzorec

{=TABELOVAT(;adresa_vstupní_buňky)}

Buňky ve výsledkové oblasti nelze měnit. Pro úpravu buněk se musí využít kopírování celé tabulky, přičemž kromě hodnot se kopírují i formáty. Jestliže je v tabulce dat na prvním řádku množina vstupních hodnot a v prvním sloupci jsou vzorce, pak do dialogového okna Tabulka dat vyplníme políčko Vstupní buňka řádku. Do výsledkové oblasti se vyplní složený vzorec

{=TABELOVAT(adresa_vstupní_buňky;)}

Místo souřadnic buněk vstupujících do vzorce lze buňky nejprve pojmenovat a pak tyto názvy ve vzorci použít. Výsledková oblast se dá pro lepší názornost výstupů podmíněně formátovat.

Tabulka dat se dvěma proměnnými

Pomocí analýzy založené na dvou vstupních proměnných se zjišťuje, jak se změna obou proměnných projeví ve výsledných hodnotách jednoho vzorce (či funkce) [3]. Na vstupu je tabulka dat, která na průsečíku prvního řádku a prvního sloupce obsahuje analyzovaný vzorec. V prvním řádku jsou hodnoty jedné proměnné, v prvním sloupci jsou hodnoty druhé proměnné. Vzorec musí zahrnovat dvě substituční buňky, které odkazují na libovolné hodnoty v prvním sloupci a v prvním řádku. Substituční buňka sloupce vstupuje do dialogového okna Tabulka dat do políčka Vstupní buňka sloupce a substituční buňka řádku do políčka Vstupní buňka řádku. Ve výsledkové oblasti se vyplní složený vzorec

{=TABELOVAT(adresa_vstupní_buňky;adresa_vstupní_buňky)}.

Hledání řešení

Hledání řešení je metoda změny jedné proměnné ve vzorci za účelem získání určitého požadovaného výsledku. Pomáhá řešit případy, u kterých je znám výsledek, tím způsobem, že mění předchozí hodnoty tak, aby bylo dosaženo požadovaného výsledku. K hledání řešení je nutné uvést tři údaje do dialogového okna příkazu Hledání řešení v kartě Data - Datové nástroje - Analýza hypotéz: Nastavená buňka, Cílová hodnota, Měněná buňka.

K dosažení řešení se používá iterační metoda, která upravuje hodnotu měněné buňky tak dlouho, dokud cílová buňka neodpovídá požadované hodnotě. Probíhající výpočet lze zastavit nebo zrušit. Po vykonání 100 iterací nebo po dosažení rozdílu menšího než zadaná změna 0,001 se proces hledání řešení zastaví. Pro změnu těchto omezujících parametrů lze nastavit v dialogovém okně Možnosti aplikace Excel v kategorii Vzorce v sekci Možnosti výpočtu políčko Nejvyšší počet iterací a Maximální změna.

Nastavení iterací

Jestliže trvá výpočet příliš dlouho, lze výpočet přerušit tlačítkem Pozastavit, dál lze pokračovat ve výpočtu tlačítkem Pokračovat nebo tlačítkem Krok se dá posunout o jednu iteraci dopředu.

Může se stát, že funkce Hledání řešení žádné řešení nenajde, zobrazí se dialogové okno s textem: „Pro buňku XY nebylo nalezeno řešení“. V tom případě lze zvýšit počet iterací, zmenšit přesnost výpočtu nebo řešení prostě neexistuje. Řešení nemusí být jen jedno (např. 5 a -5 pro kvadratické funkce). V takovém případě pro jeden žádoucí kladný výsledek zadáme dostatečně velkou kladnou hodnotu do měněné buňky.

Hledání řešení je omezeno jen na změnu jedné buňky, ale problém může vyžadovat změnu více buněk nebo omezení hodnoty buněk na určitý rozsah. V takových případech se použije Řešitel [4].

Scénáře

Scénářem se rozumí pojmenovaná množina hodnot buněk. Každá množina hodnot je jeden scénář. Umožňují v jedné tabulce zobrazit různé kombinace dat a tak nahradit mnoho tabulek [3]. Jinými slovy scénář označuje skupinu měnících se buněk, v nichž můžeme vytvořit a uložit sady vstupních hodnot a výsledků. Každý scénář sleduje Správce scénářů na kartě Data ve skupině Datové nástroje v položce Analýza hypotéz. Správce scénáře ukládá pro každý scénář název scénáře, komentář, stav příznaků ochrany a skrytí buněk a změnu odkazů na buňky a data [4].

Pro vytvoření nového scénáře se v dialogovém okně Správce scénářů stiskne tlačítko Přidat. Vyplní se Název scénáře, který musí být pro každou množinu hodnot jiný a maximálně do 25 znaků. Do políčka Měněné buňky se odkáže na vybrané buňky, které budou v jednotlivých scénářích obsaženy. Tyto buňky mohou obsahovat číslo, text nebo datum, případně i vzorec. Při použití vzorce Excel upozorní, že po odkliknutí OK budou vzorce převedeny na výsledné hodnoty. Po odklepnutí OK se zobrazí dialogové okno Hodnoty scénáře, kde si lze překontrolovat, zda jsme správně zadali hodnoty měněných buněk, lze je změnit. Excel omezuje vybrat v rámci jednoho scénáře pouze 32 měněných buněk, pro větší výběr je nutné vytvořit více scénářů.

Tlačítkem Odstranit se vybraný scénář odstraní, tento scénář již nelze vrátit zpět. Tlačítkem Upravit lze upravit název scénáře a měněné buňky. Políčko Zámek nabízí možnost Neumožnit změny, čímž se zamezí dodatečným úpravám scénáře, a možnost Skrýt, kterou zamezíme nabídce vybraného scénáře ve Správci scénáře, což se ale projeví až po zamčení listu. Úprava je zaznamenána v Komentáři, který zobrazuje datum vytvoření a úprav a jméno uživatele, komentář může mít až 255 znaků. Tlačítkem Zobrazit se promítnou hodnoty právě vybraného scénáře do měněných buněk. Tlačítkem Sloučit lze přidat scénáře z jiného listu se stejnými oblastmi měněných buněk. Tlačítkem Souhrn se zobrazí dialogové okno Zpráva scénáře, kde lze zadat typ zprávy (zpráva scénáře, kontingenční tabulka) a výsledné buňky. Po potvrzení se vytvoří na zvláštním listě Zpráva scénáře obsahující měněné buňky v nastavených variantách a také v aktuálních hodnotách.

Pro lepší orientaci je vhodné si měněné buňky ještě před vytvořením scénáře pojmenovat. Scénáře má význam vytvářet pro buňky, jejichž hodnoty se mění. Ve verzi Excel 2007 nelze vytvořit panel nástrojů pro práci se scénáři, lze jej ale přidat do panelu nástrojů Rychlý přístup.

Praktická část - příklady

Zdrojová data

Zdrojová data Zdroj pro všechny příklady

Tabulka dat s jednou proměnou

Zadání

  1. Jak se bude měnit výše zůstatku na účtu v závislosti na změně roční úrokové míry, jestliže nyní máte na účtu 100 000 Kč a chcete si spořit 3 roky?
  2. Ke svým 100 000 Kč si budete každý měsíc ukládat 1 000 Kč. Jak se změní zůstatek na účtu (počítejte s měsíční úrokovou mírou)?

Postup

Vyplníme pomocnou tabulku

Do tabulky dat se sloupcovou orientací vložíme vzorce pro budoucí hodnotu pro první i druhý úkol

Označíme oblast E11:G32 a vložíme příkaz Tabulka dat (Data - Datové nástroje - Analýza hypotéz). Do políčka Vstupní buňka sloupce doplníme odkaz na substituční buňku B11. Do tabulky se doplní matice vzorců {=TABELOVAT(;B11)}.

Pro tabulku s řádkovou orientací vyplníme stejné vzorce.

Označíme oblast J11:U13 a vložíme příkaz Tabulka dat. Do políčka Vstupní buňka řádku doplníme odkaz na substituční buňku B11. Do tabulky se doplní matice vzorců {=TABELOVAT(B11;)}.

Tabulka dat se 2 proměnnými

Zadání

  1. Jak se bude měnit výše zůstatku na účtu v závislosti na změně úrokové míry a délky spoření, jestliže nyní máte na účtu 100 000 Kč?
  2. Jak dlouho si budete muset spořit, abyste dosáhli 10% hranice výnosnosti z nynějšího zůstatku na účtu, jestliže spoříte při 2% roční úrokové míře?

Postup

Vyplníme pomocnou tabulku

Pojmenujeme buňky, které budou vstupovat do vzorce

Do průsečíku prvního řádku a sloupce vložíme vzorec s využitím pojmenovaných oblastí

=BUDHODNOTA(i;n;a;PV;1)

Označíme oblast F11:Q41 a vložíme příkaz Tabulka dat. Do políčka Vstupní buňka řádku doplníme odkaz na substituční buňku C11. Do políčka Vstupní buňka sloupce doplníme odkaz na substituční buňku C12. Do tabulky se doplní matice vzorců {=TABELOVAT(C11;C12)}.

Výsledkovou oblast G12:Q41 podmíněně naformátujeme. Na kartě Domů - Styly zvolíme příkaz Podmíněné formátování:

Podmíněné formátování

Podmíněné formátování barevně ukazuje hranici výhodnosti - při 2% úrokové míře musíme spořit alespoň 5 let.

Hledání řešení

Zadání

  1. Kolik musíte měsíčně pod dobu 3 let ukládat, abyste při úrokové míře 2 % naspořili 100 000 Kč?
  2. Kolik musíte na začátku úrokového období vložit do banky, pokud chcete mít po 3 letech na termínovaném účtu 100 000 Kč při úrokové míře 2 %, přičemž úroky jsou zdaněny částkou 15 %?

Postup

Vyplníme tabulku s pomocí finanční funkce BUDHODNOTA

=BUDHODNOTA(B9/12;B10*12;B11;B8;1)

Vložíme příkaz Hledání řešení (Data - Datové nástroje - Analýza hypotéz)

Měsíčně musíme ukládat 2 693,10 Kč. To lze zjistit i použitím funkce

=PLATBA(B9/12;B10*12;B8;100000;1).

Vyplníme druhou tabulku s pomocí funkce BUDHODNOTA a jednoduchých vzorců

=BUDHODNOTA(M9;M10;M11;M8;1)

Vložíme příkaz Hledání řešení a vyplníme:

Na začátku musíme uložit 95 055,60 Kč.

Scénáře

Zadání

V současné době máte na účtu 100 000 Kč. Kolik budete mít po 3 letech za předpokladu těchto variant:

Na účtu máte 100 000 Kč. Nasimulujte budoucí hodnoty po 3 letech pro tato rozpětí úrokových měr:

Využijte tabulku na listu Tabulka_dat_2p.

Postup

Vyplníme tabulku s pomocí funkce BUDHODNOTA

=BUDHODNOTA(B11/12;B12*12;B13;B10;1)

Pro lepší orientaci pojmenujeme buňky ve sloupci B

Vložíme příkaz Správce scénářů (Data - Datové nástroje - Analýza hypotéz) a klepneme na tlačítko Přidat

Přidáme další dvě varianty tak, že nejprve změníme hodnoty v tabulce (úroková míra, splátka), a pak stejným způsobem přidáme scénáře pomocí tlačítka Přidat, stačí změnit pouze název, měněné buňky jsou stejné.

Pro vytvoření Zprávy scénáře stiskneme tlačítko Souhrn

Pro vytvoření Kontingenční tabulky stiskneme Souhrn

Výsledky ve Zprávě scénáře či Kontingenční tabulce lze zjistit i pouhým rozkopírováním všech buněk tabulky.

Využijeme list Tabulka_dat_2p a vložíme příkaz Správce scénářů, přidáme 3 scénáře (pesimistická, nejpravděpodobnější, optimistická varianta) pro měněné buňky (G11:Q11).

Pro vytvoření Zprávy scénáře či Kontingenční tabulky stiskneme Souhrn a zadáme i výsledné buňky (budoucí hodnoty pro 3. rok z výsledkové oblasti - G14:Q14).

Použité zdroje a další informace

[1] MS: Úvod do citlivostní analýzy

[2] STINSON Craig: Mistrovství v Microsoft Office Excel 2003. Brno: CP Books 2005

[3] BROŽ, Milan: Microsoft Excel 2007: podrobná uživatelská příručka. Brno: Computer Press 2007

[4] MARTIN Mindy C.: Excel 2000, II.díl. Praha: Grada 2000

LibOl: Ekonomické funkce – Citlivostní analýza – Vícenásobné operace

LibOl: Finanční funkce – Hledání (Hledat) řešení

KORVINY, Petr, SLANINOVÁ, Kateřina: MS Excel XP: Hledání řešení, Citlivostní analýza, Scénáře

© 2007–2013 Miroslav Lorenc

Převzetí, publikování, kopírování, či jiné užití než pro osobní potřebu je možné pouze s písemným svolením autora.