Úvod do citlivostní analýzy

Nástroje pro citlivostní analýzu v Excelu umožňují používat několik různých sad hodnot v jednom nebo více vzorcích k prozkoumání nejrůznějších výsledků.

Provedením citlivostní analýzy můžete například vytvořit dva rozpočty, přičemž každý z nich předpokládá určitou výši výnosů. Nebo můžete zadat výsledek, který má vzorec vypočítat, a pak zjistit, jaké sady hodnot zajistí daný výsledek. Excel nabízí několik různých nástrojů, které vám pomůžou provést typ analýzy podle vašich potřeb.

Toto jsou jenom základní informace o těchto nástrojích. Pro každý z nich jsou k dispozici odkazy na témata nápovědy.

Citlivostní analýza je proces, při kterém se provádějí změny hodnot v buňkách za účelem zjištění, jak dané změny ovlivní výsledky vzorců na listu.

Excel nabízí tři typy nástrojů pro citlivostní analýzu: scénáře, hledání řešení a tabulky dat. Scénáře a tabulky dat fungují tak, že na základě sad vstupních hodnot se určují možné výsledky. Tabulka dat pracuje jenom s jednou nebo dvěma proměnnými, ale umožňuje pro tyto proměnné použít mnoho různých hodnot. Scénář může obsahovat více proměnných, ale umožňuje použít maximálně 32 hodnot. Hledání řešení se od scénářů a tabulek dat liší v tom, že na základě výsledku se určují možné vstupní hodnoty, které zajistí daný výsledek.

K těmto třem nástrojům si můžete nainstalovat ještě doplňky, které pomáhají při provádění citlivostní analýzy, třeba doplněk Řešitel. Doplněk Řešitel se podobá nástroji Hledání řešení, ale umožňuje použít více proměnných. Pomocí úchytu a různých příkazů, které jsou integrované v Excelu, můžete rovněž vytvářet prognózy.

Pro pokročilejší modely můžete použít doplněk Analytické nástroje.

Scénář je sada hodnot, které Excel ukládá a může automaticky v buňkách na listu nahrazovat. Na listu je možné vytvořit a uložit různé skupiny hodnot a pak přepnutím na některý z těchto nových scénářů zobrazit různé výsledky.

Předpokládejme například, že máte dva scénáře pro rozpočet: Nejhorší varianta a Nejlepší varianta. Pomocí Správce scénářů můžete vytvořit oba scénáře na jednom listu a pak mezi nimi přepínat. V každém scénáři můžete určit měněné buňky a hodnoty, které se mají použít. Při přepínání mezi scénáři se výsledná buňka mění podle různých hodnot měněných buněk.

1. Měněné buňky

2. Výsledná buňka

1. Měněné buňky

2. Výsledná buňka

Pokud má několik uživatelů v samostatných sešitech určité informace, které chcete ve scénářích použít, můžete tyto sešity shromáždit a jejich scénáře sloučit.

Až vytvoříte nebo shromáždíte všechny scénáře, které potřebujete, můžete vytvořit souhrnnou zprávu scénářů, která bude obsahovat informace z těchto scénářů. Zpráva scénářů zobrazuje všechny informace o scénářích v jedné tabulce na novém listu.

Poznámka: Zprávy scénářů se automaticky nepřepočítávají. Když ve scénáři změníte hodnoty, ve stávající souhrnné zprávě se tyto změny nezobrazí. Místo toho je potřeba vytvořit novou souhrnnou zprávu.

Pokud znáte požadovaný výsledek ze vzorce, ale nejste si jistí, jakou vstupní hodnotu vzorec k získání tohoto výsledku vyžaduje, můžete použít funkci Hledání cílů . Předpokládejme například, že si potřebujete půjčit nějaké peníze. Víte, jakou částku si chcete půjčit, jak dlouho chcete půjčku splácet a kolik si můžete dovolit splácet každý měsíc. Pomocí hledání řešení můžete zjistit, jaká úroková sazba vám zajistí cíl půjčky.

Hledání řešení

Buňky B1, B2 a B3 jsou hodnoty pro částku půjčky, délku období a úrokovou sazbu.

Buňka B4 zobrazí výsledek vzorce =PMT(B3/12;B2;B1).

Poznámka: Hledání řešení funguje jenom s jednou proměnnou vstupní hodnotou. Pokud chcete určit více než jednu vstupní hodnotu, například částku půjčky a výši měsíční splátky půjčky, použijte místo toho doplněk Řešitel. Další informace o doplňku Řešitel najdete v části Příprava prognóz a pokročilé obchodní modely a pod odkazy v části Viz také.

Pokud máte vzorec, který používá jednu nebo dvě proměnné, nebo několik vzorců, které používají společnou proměnnou, můžete pomocí tabulky dat zobrazit všechny výsledky na jednom místě. Použití tabulek dat usnadňuje okamžité prozkoumání rozsahu možností. Zaměřujete se jenom na jednu nebo dvě proměnné, a proto jsou výsledky srozumitelné a dají se snadno sdílet ve formátu tabulky. Pokud je pro daný sešit povolený automatický přepočet, data v tabulkách dat se okamžitě přepočítají, takže data budou pořád aktuální.

Analýza hypotéčních úvěrů

Buňka B3 obsahuje vstupní hodnotu. Buňky C3, C4 a C5 jsou hodnoty, které Excel nahradí na základě hodnoty zadané v B3.

Tabulka dat nemůže obsahovat více než dvě proměnné. Pokud chcete analyzovat více než dvě proměnné, můžete použít scénáře. Tabulka dat je sice omezená jenom na jednu nebo dvě proměnné, může ale používat tolik různých hodnot proměnných, kolik potřebujete. Scénář může obsahovat maximálně 32 různých hodnot, ale můžete vytvořit tolik scénářů, kolik potřebujete.

Pokud chcete připravit prognózy, můžete pomocí Excelu automaticky vygenerovat budoucí hodnoty, které budou založené na stávajících datech, nebo automaticky vygenerovat extrapolované hodnoty, které budou založené na výpočtech lineárního nebo růstového trendu.

Pomocí úchytu nebo příkazu Řady můžete vyplnit řadu hodnot, které odpovídají jednoduchému lineárnímu trendu nebo exponenciálnímu růstovému trendu. K rozšíření na komplexní a nelineární data je možné použít funkce listu nebo nástroj pro regresní analýzu v doplňku Analytické nástroje.

Nástroj Hledání řešení sice může používat jenom jednu proměnnou, ale pomocí doplňku Řešitel můžete provádět zpětnou projekci pro více proměnných. Řešitel umožňuje najít optimální hodnotu pro vzorec v jedné buňce (která se nazývá cílová buňka) na listu.

Řešitel pracuje se skupinou buněk, které souvisejí se vzorcem v cílové buňce. Řešitel upraví hodnoty v zadaných měněných buňkách ( označované jako upravitelné buňky) a vytvoří výsledek, který zadáte ze vzorce cílové buňky. Pomocí omezení můžete omezit hodnoty, které může Řešitel použít v modelu, a omezení mohou odkazovat na jiné buňky, které ovlivňují vzorec cílové buňky.