Vytváření scénářů ve vašem finančním modelu

Nyní, když jste určili své základní předpoklady, které odrážejí, jak si myslíte, že si podnik povede, chcete ve svém finančním modelu spustit scénáře nejhoršího a nejlepšího případu. Nejen, že chcete vidět, jak si myslíte, že si podnik povede, ale také chcete vidět, jak si podnik povede, pokud bude horší, než se očekávalo, nebo lépe, než se očekávalo.

Spouštění více scénářů je velmi důležitou součástí finančního modelování – někteří by řekli, že je to celý smysl finančního modelování – protože umožňuje uživateli změřit různé výsledky, pokud se určité předpoklady nakonec liší. Protože nikdo nevidí do budoucnosti a předpoklady jsou vždy špatné, je důležité být schopen vidět, co se stane s výstupy, když se změní hlavní předpoklady.

Protože jste tento integrovaný finanční model sestavili tak, že všechny výpočty jsou propojeny buď s buňkami vstupních předpokladů, nebo s jinými částmi finančních výkazů, měly by jakékoli změny předpokladů dobře procházet celým modelem. Důkazem je ovšem pudink.

Zadání předpokladů scénáře

Vraťme se nyní k pracovnímu listu Předpoklady a věříte, že hlavními hnacími silami ziskovosti vaší kavárny bude průměrný počet šálků, které prodáte za den, a nájem, který zaplatíte. Domníváte se, že snížení prodaných šálků za den o 20 šálků a zvýšení nájemného o 10 procent je rozumným nejhorším scénářem a zvýšení prodaných šálků za den o 20 šálků a snížení nájemného o 10 procent je rozumným nejlepším scénářem.

Úplně nahoře v listu Předpoklady zadejte vstupní předpoklady scénáře.

Vstupní předpoklady scénáře.

Vytvoření rozevíracího pole

Rozhodli jste se pro své předpoklady scénáře, takže nyní musíte vytvořit rozevírací seznam, který bude řídit vaši analýzu scénáře. Máte plně funkční finanční model, takže chcete mít možnost snadno přepínat mezi scénáři, abyste viděli, jak se výstupy mění v reálném čase. Rozbalovací pole scénáře můžete umístit na kteroukoli z finančních výkazů, ale pro tento příklad jej umístíte na začátek výkazu zisku a ztráty.

Následuj tyto kroky:

Přejděte na list IS Cash Flow a vyberte buňku B1.

Vyberte Ověření dat v části Datové nástroje na pásu dat.

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

Z rozevíracího seznamu Povolit vyberte Seznam.

Slova Nejlepší, Základní a Nejhorší můžete zadat přímo do pole, ale nejlepší je propojit je se zdrojem pro případ, že překlepnete hodnotu.

Do pole Zdroj zadejte = a potom klikněte na list Předpoklady a zvýrazněte názvy scénářů Nejhorší, Základní, Nejlepší.

Váš vzorec v poli Zdroj by nyní měl být =Předpoklady!$B$2:$D$2.

Klepněte na tlačítko OK.

Vraťte se do buňky B1 na listu IS Cash Flow a otestujte, že rozevírací seznam funguje podle očekávání a poskytuje možnosti Nejlepší, Základní a Nejhorší.

V rozevíracím seznamu nastavte prozatím na Base.

Vytvoření funkce scénáře

Musíte upravit své vstupní předpoklady pro počet prodaných šálků za den a měsíční nájem tak, aby se se změnou rozevíracího pole na listu IS Cash Flow měnily vstupní předpoklady na odpovídající scénář. Když je například na listu IS Cash Flow vybráno Nejlepší, hodnota v buňce B9 na listu Předpoklady by měla být 140 a hodnota v buňce B23 by měla být 1 080 $. To by mělo být provedeno pomocí vzorce.

Mnoho různých funkcí často dosáhne stejných nebo podobných výsledků. Jakou funkci použijete, je na vás jako na finančním modeláři, ale nejlepším řešením bude to, které provede požadovanou funkci nejčistším a nejjednodušším způsobem, aby ostatní pochopili, co jste udělali a proč.

V tomto případě existuje několik možností, které můžete použít: příkaz HLOOKUP, SUMIF nebo IF. Příkaz IF, který je vnořenou funkcí, je nejobtížnější na sestavení a je méně škálovatelný. Pokud se zvýší počet možností scénáře, je obtížnější rozšířit možnost příkazu IF. V tomto případě jsem se rozhodl použít HLOOKUP s těmito kroky.

Následuj tyto kroky:

Vyberte buňku B9 a stiskněte tlačítko Vložit funkci na kartě Vzorce nebo vedle řádku vzorců.

Vyhledejte HLOOKUP, stiskněte Go a klepněte na OK.

Zobrazí se dialogové okno HLOOKUP.

Klikněte na pole Lookup_value a vyberte rozevírací pole na listu IS Cash Flow.

Toto je kritérium, které řídí HLOOKUP.

Stisknutím klávesy F4 uzamknete odkaz na buňku.

Do pole Table_array musíte zadat pole, které používáte pro HLOOKUP. Všimněte si, že vaše kritéria se musí objevit v horní části rozsahu.

Vyberte rozsah, který je tabulkou scénářů v horní části — jinými slovy B2:D4 — a stisknutím F4 uzamkněte odkazy na buňky.

Odkazy na buňky se změní na $B$2:$D$4.

Do pole Row_index_num zadejte číslo řádku, 2.

Do pole Range_lookup zadejte nulu nebo nepravdu, protože hledáte přesnou shodu.

Zkontrolujte, zda vaše dialogové okno vypadá stejně jako na obrázku níže.

Klepněte na tlačítko OK.

Vzorec v buňce B9 je =HLOOKUP('IS Cash Flow'!B1,$B$2:$D$4,2,0) s vypočítaným výsledkem 120.

Proveďte stejnou akci v buňce B23 se vzorcem =HLOOKUP('IS Cash Flow'!$B$1,$B$2:$D$4,3,0) .

Místo opětovného vytváření celého vzorce jednoduše zkopírujte vzorec z buňky B9 do buňky B23 a změňte odkaz na řádek z 2 na 3. Zkopírováním buňky změníte formátování čísla, takže budete muset změnit měnu symbol zpět na $ znovu.

Vraťte se do listu IS Cash Flow a změňte rozevírací seznam na Nejlepší.

Zkontrolujte, zda se vaše předpoklady pro průměrný počet prodaných šálků za den a měsíční nájem v pracovním listu Předpoklady odpovídajícím způsobem změnily. Poháry se změní na 140 a nájemné na 1 080 $.

Nyní je důležitým testem zjistit, zda je bilance stále vyrovnaná!

Vraťte se do listu Rozvaha a ujistěte se, že vaše kontrola chyb je stále nulová.

Znovu rozevírací seznam otestujte jeho změnou na nejhorší.
Poháry se změní na 100 a nájemné bude 1 320 $. Znovu zkontrolujte kontrolu chyb na listu Rozvaha.

Vytvoření scénáře pomocí HLOOKUP.

Gratulujeme! Váš zcela integrovaný finanční model spolu s analýzou scénářů je nyní kompletní! Kopii hotového modelu si můžete stáhnout v souboru 1002.xlsx .