Jak vytvořit rozbalovací scénáře ve vašem finančním modelu

Nejběžněji používanou metodou vytváření scénářů je použití kombinace vzorců a rozevíracích polí. Ve finančním modelu vytvoříte tabulku možných scénářů a jejich vstupů a propojíte názvy scénářů s rozevíracím polem vstupní buňky. Vstupy modelu jsou propojeny s tabulkou scénářů. Pokud byl model sestaven správně se všemi vstupy procházejícími do výstupů, pak se výsledky modelu změní, protože uživatel vybere různé možnosti z rozevíracího seznamu.

Rozbalovací boxy ověření dat se používají pro řadu různých účelů ve finančním modelování, včetně analýzy scénářů.

Použití ověření dat k modelování scénářů ziskovosti

Stáhnout soubor 0801.xlsx . Otevřete jej a vyberte kartu označenou 8-1-start.

Jak to bylo modelováno, vstupy jsou seřazeny ve sloupci B. Analýzu citlivosti můžete provést jednoduše změnou jednoho ze vstupů – například změňte počet zákazníků na operátora volání v buňce B3 ze 40 na 45. vidět, jak se mění všechna závislá čísla. Toto by byla analýza citlivosti, protože měníte pouze jednu proměnnou. Místo toho budete v tomto cvičení úplné analýzy scénáře měnit více proměnných najednou, takže budete muset udělat více než ručně vyladit několik čísel.

Chcete-li provést analýzu scénáře pomocí rozbalovacích polí ověření dat, postupujte takto:

Vezměte stažený model a vyjměte a vložte popisy ze sloupce C do sloupce F. Můžete to udělat tak, že zvýrazníte buňky C6:C8, stisknete Ctrl+X, vyberete buňku F6 a stisknete Enter.

Vstupy v buňkách B3 až B8 jsou aktivním rozsahem, který řídí model a tak to zůstane. Musí se z nich však stát vzorce, které se mění v závislosti na rozevíracím seznamu, který vytvoříte.

Zkopírujte rozsah ve sloupci B napříč do sloupců C, D a E.

Můžete to udělat tak, že zvýrazníte B3:B8, stisknete Ctrl+C, vyberete buňky C3:E3 a stisknete Enter. Tyto částky budou stejné pro každý scénář, dokud je nezměníte.

V řádku 2 zadejte tituly nejlepším případě , základního scénáře , a nejhorší případ.

Nastavení modelu pro analýzu scénářů.

Všimněte si, že vzorce stále odkazují na vstupy ve sloupci B, jak můžete vidět výběrem buňky C12 a stisknutím klávesové zkratky F2.

Upravte vstupy pod každým scénářem.

Můžete vložit cokoliv, co považujete za pravděpodobné, ale aby se čísla shodovala s čísly v tomto příkladu, zadejte hodnoty. Sloupec B zatím ignorujte.

Vstupy pro analýzu scénářů.

Nyní musíte přidat rozevírací seznam v horní části, který bude řídit vaše scénáře. Nezáleží na tom, kam přesně rozbalovací pole umístíte, ale mělo by být na místě, které lze snadno najít, obvykle v horní části stránky.

Do buňky E1 zadejte název Scénář .

Vyberte buňku F1 a změňte formátování na vstup, aby uživatel viděl, že tato buňka je upravitelná.

Nejjednodušší způsob, jak toho dosáhnout, je provést tyto kroky:

Klepněte na jednu z buněk, které jsou již naformátovány jako vstup, například na buňku E3.

Stiskněte ikonu Format Painter v části Schránka na levé straně karty Domů. Váš kurzor se změní na štětec.

Vyberte buňku F1 a vložte formátování.

Format Painter je obvykle pro jednorázové použití. Poté, co vyberete buňku, štětec zmizí z kurzoru. Pokud chcete, aby se aplikace Format Painter stala „lepící“ a aplikovala se na více buněk, poklepejte na ikonu, když ji vyberete na kartě Domů.

Nyní v buňce F1 vyberte Ověření dat v části Datové nástroje na kartě Data.

Zobrazí se dialogové okno Ověření dat.

Na kartě Nastavení změňte rozevírací seznam Povolit na Seznam, pomocí myši vyberte rozsah =$C$2:$E$2 a klikněte na OK.

Vytváření rozevíracích scénářů ověřování dat.

Klikněte na rozevírací pole, které se nyní zobrazuje vedle buňky F1, a vyberte jeden ze scénářů (například Základní případ).

Použití vzorců na scénáře

Buňky ve sloupci B stále řídí model a je třeba je nahradit vzorci. Před přidáním vzorců byste však měli změnit formátování buněk v rozsahu, aby bylo vidět, že obsahují vzorce, nikoli pevně zadaná čísla. Následuj tyto kroky:

Vyberte buňky B3:B8 a vyberte barvu výplně ze skupiny Písmo na kartě Domů.

Změňte barvu výplně na bílé pozadí.

Je velmi důležité rozlišovat mezi vzorci a vstupními buňkami v modelu. Každému uživateli, který otevírá model, musíte dát jasně najevo, že buňky v tomto rozsahu obsahují vzorce a neměly by být přepsány.

Nyní je třeba nahradit pevně zakódované hodnoty ve sloupci B vzorcem, které se budou měnit se změnou rozevíracího seznamu. Můžete to udělat pomocí řady různých funkcí; HLOOKUP, vnořený příkaz IF, IFS a SUMIF postačí. Přidejte vzorce podle následujících kroků:

Vyberte buňku B3 a přidejte vzorec, který změní hodnotu v závislosti na tom, co je v buňce F1.

Zde je vzorec, který bude pod různými možnostmi:

• =HLOOKUP($F$1,$C$2:$E$8,2,0)

  Všimněte si, že s tímto řešením musíte při kopírování vzorce změnit číslo indexu řádku z 2 na 3 a tak dále. Místo toho můžete ve třetím poli použít funkci ROW takto: =HLOOKUP($F$1,$C$2:$E$8,ROW(A3)-1,0)

• =IF($F$1=$C$2,C3,IF($F$1=$D$2,D3,E3))
• =IFS($F$1=$C$2,C3,$F$1=$D$2,D3,$F$1=$E$2,E3)
• =SUMIF($C$2:$E$2,$F$1,C3:E3)

  Jako vždy je na výběr několik různých možností a nejlepší řešení je to nejjednodušší a nejsnáze pochopitelné. Kterákoli z těchto funkcí vytvoří přesně stejný výsledek, ale nutnost změnit číslo indexu řádku ve HLOOKUP není robustní a přidání ROW může být pro uživatele matoucí. Vnořený příkaz IF je složitý na sestavení a následování, a přestože je nová funkce IFS navržena tak, aby zjednodušila vnořenou funkci IF, je stále poněkud nepraktická. SUMIF je poměrně jednoduché sestavit a sledovat a lze jej snadno rozšířit, pokud budete v budoucnu potřebovat přidat další scénáře.

  Všimněte si, že IFS je nová funkce, která je k dispozici pouze s nainstalovanými Office 365 a Excel 2016 nebo novějšími. Pokud tuto funkci použijete a někdo otevře tento model v předchozí verzi Excelu, může vzorec zobrazit, ale nebude ho moci upravovat.

Zkopírujte vzorec v buňce B3 ve sloupci dolů.

Dokončená analýza scénáře.

Při použití běžného kopírování a vkládání ztratíte veškeré formátování. Je důležité zachovat formátování modelu, abyste na první pohled viděli, které vstupy jsou v dolarových hodnotách, procentech nebo zákaznických číslech. Chcete-li zachovat formátování, použijte Vložit vzorce. Můžete se k němu dostat tak, že zkopírujete buňku do schránky, zvýrazníte cílový rozsah, kliknete pravým tlačítkem a vyberete ikonu Vložit vzorce pro vložení pouze vzorců a ponecháte formátování nedotčené.

Nyní k té zábavnější části! Je čas otestovat funkčnost scénáře v modelu.

Klikněte na buňku F1, změňte rozevírací seznam a sledujte, jak se mění výstupy modelu při přepínání mezi různými scénáři.