Přepínání mezi různými sadami hodnot pomocí scénářů

Existují tři druhy analytických nástrojů What-If, které jsou součástí Excelu: Scénáře, Tabulky dat a Hledání cílů. Scénáře a tabulky dat přebírají sady vstupních hodnot a projekt dopředu, aby určily možné výsledky. Hledání cílů se liší od scénářů a tabulek dat v tom, že bere výsledek a projektuje zpět k určení možných vstupních hodnot, které tento výsledek generují.

Každý scénář může obsahovat až 32 hodnot proměnných. Pokud chcete analyzovat více než 32 hodnot a hodnoty představují pouze jednu nebo dvě proměnné, můžete použít tabulky dat. I když je tabulka dat omezena pouze na jednu nebo dvě proměnné (jednu pro vstupní buňku řádku a jednu pro vstupní buňku sloupce), tabulka dat může obsahovat tolik různých hodnot proměnných, kolik potřebujete. Scénář může mít maximálně 32 různých hodnot, ale můžete vytvořit libovolný počet scénářů.

Kromě těchto tří nástrojů můžete nainstalovat doplňky, které vám pomůžou provádět analýzu What-If, například 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.

Předpokládejme, že chcete vytvořit rozpočet, ale nejste si jisti svými výnosy. Pomocí scénářů můžete definovat různé možné hodnoty výnosů a pak přepínat mezi scénáři a provádět citlivostní analýzy.

Předpokládejme například, že vaším nejhorším scénářem rozpočtu je Hrubý výnos 50 000 USD a Náklady na prodané zboží 13 200 USD, takže hrubý zisk zůstane 36 800 USD. Pokud chcete tuto sadu hodnot definovat jako scénář, zadejte nejprve hodnoty do listu, jak je znázorněno na následujícím obrázku:

Buňka Změna obsahuje hodnoty, které zadáte, zatímco buňka Výsledek obsahuje vzorec založený na měněných buňkách (v této buňce B4 je vzorec =B2-B3).

Potom pomocí dialogového okna Správce scénářů uložíte tyto hodnoty jako scénář. Karta Data > What-If Analysis > Scenario Manager > Přidat.

V dialogovém okně Název scénáře pojmenujte scénář Nejhorší případ a určete, že buňky B2 a B3 jsou hodnoty, které se mezi jednotlivými scénáři mění. Pokud před přidáním scénáře vyberete na listu Možnost Změna buněk , Vloží správce scénářů buňky automaticky za vás, jinak je můžete zadat ručně nebo použít dialogové okno pro výběr buňky napravo od dialogového okna Změna buněk.

Ochrana – Můžete také chránit své scénáře, takže v části Ochrana zkontrolujte požadované možnosti, nebo zrušte jejich zaškrtnutí, pokud nechcete žádnou ochranu.

• Pokud chcete zabránit úpravám scénáře, když je list zamknutý, vyberte Možnost Zabránit změnám .

• Pokud chcete zabránit zobrazení scénáře při zamknutém listu, vyberte Skryté .

Teď předpokládejme, že vaším scénářem rozpočtu nejlepšího případu je hrubý výnos 150 000 USD a náklady na prodané zboží 26 000 USD, takže hrubý zisk bude 124 000 USD. Pokud chcete tuto sadu hodnot definovat jako scénář, vytvoříte jiný scénář, pojmenujete ho Nejlepší případ a zadáte různé hodnoty pro buňku B2 (150 000) a buňku B3 (26 000). Vzhledem k tomu, že hrubý zisk (buňka B4) je vzorec – rozdíl mezi Výnosy (B2) a Náklady (B3) – nezměníte buňku B4 pro scénář Nejlepší případ.

Po uložení se scénář zpřístupní v seznamu scénářů, které můžete použít v citlivostních analýzách. Vzhledem k hodnotám na předchozím obrázku by se hodnoty v listu změnily tak, aby vypadaly podobně jako na následujícím obrázku:

Může se stát, že máte všechny informace v jednom listu nebo sešitu potřebné k vytvoření všech scénářů, které chcete zvážit. Můžete ale chtít shromáždit informace o scénáři z jiných zdrojů. Předpokládejme například, že se pokoušíte vytvořit rozpočet společnosti. Můžete shromažďovat scénáře z různých oddělení, jako je prodej, mzdy, produkce, marketing a právní oddělení, protože každý z těchto zdrojů má jiné informace, které se dají použít při vytváření rozpočtu.

Tyto scénáře můžete shromáždit do jednoho listu pomocí příkazu Sloučit . Každý zdroj může poskytnout tolik hodnot buněk, kolik chcete, nebo tak málo. Můžete například chtít, aby každé oddělení dodalo odhady výdajů, ale potřebujete pouze odhady výnosů z několika z nich.

Když se rozhodnete sloučit, Správce scénářů načte průvodce Scénářem sloučení, který zobrazí seznam všech listů v aktivním sešitu a také všechny ostatní sešity, které jste v té době mohli otevřít. Průvodce vám řekne, kolik scénářů máte na každém zdrojovém listu, který vyberete.

Když shromáždíte různé scénáře z různých zdrojů, měli byste v každém sešitu použít stejnou strukturu buněk. Například výnosy můžou být vždy v buňce B2 a Výdaje můžou být vždy v buňce B3. Pokud pro scénáře z různých zdrojů použijete různé struktury, může být obtížné výsledky sloučit.

Pokud chcete porovnat několik scénářů, můžete vytvořit sestavu, která je shrnuje na stejné stránce. Sestava může zobrazit seznam scénářů vedle sebe nebo je prezentovat v Kontingenční tabulka.

Souhrnná sestava scénářů založená na předchozích dvou ukázkových scénářích by vypadala přibližně takto:

Všimněte si, že Excel automaticky přidal úrovně seskupení , které po kliknutí na různé selektory rozbalí a sbalí zobrazení.

Na konci souhrnné sestavy se zobrazí poznámka vysvětlující, že sloupec Aktuální hodnoty představuje hodnoty měnících se buněk v době vytvoření souhrnné sestavy scénářů a že buňky, které se pro každý scénář změnily, jsou zvýrazněné šedě.